Dashboard for MVC - How to Register a Federated Data Source

This example registers the DashboardFederationDataSource from the following set of data sources:

DashboardSqlDataSource (the SQLite database)
DashboardExcelDataSource
DashboardObjectDataSource
DashboardJsonDataSource

Files to Look At

DashboardConfig.cs (VB: DashboardConfig.vb)

The federated data source is stored in the in-memory storage (DataSourceInMemoryStorage) and is available from the Add Data Source dialog. Note that when you add a federated data source to a dashboard, all data sources used in the federated data source are also added to the dashboard.

This example demonstrates the following query types you can use to create a data federation:

Join
Combines rows from two or more tables based on a clause. The join type specifies records that have matching values in both tables.
Union and UnionAll
The Union query combines rows from two or more tables into one data set and removes duplicate rows in merged tables. The UnionAll query does the same, except it doesn't remove duplicated rows. You can create a union query for data sources if data types of their columns are implicitly converted.
Transformation
If a data source contains a complex column (an object), you can transform its properties to display them as separate columns in a flattened view. If one of the data columns is an array, you can unfold its values and display a new data row for every element of the array. When you unfold the column, you can flatten it and create a flattened view

Documentation

Examples

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

MVC_DataFederationExample/App_Start/DashboardConfig.cs(vb)

C#
VB.NET

C#
using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using DevExpress.DashboardWeb.Mvc;
using DevExpress.DataAccess.DataFederation;
using DevExpress.DataAccess.Excel;
using DevExpress.DataAccess.Json;
using DevExpress.DataAccess.Sql;
using System;
using System.Web.Hosting;
using System.Web.Routing;

namespace MVC_DataFederationExample {
    public static class DashboardConfig {
        public static void RegisterService(RouteCollection routes) {
            routes.MapDashboardRoute("dashboardControl", "DefaultDashboard");

            DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards");
            DashboardConfigurator.Default.SetDashboardStorage(dashboardFileStorage);

            DashboardConfigurator.PassCredentials = true;

            // Uncomment this string to allow end users to create new data sources based on predefined connection strings.
            //DashboardConfigurator.Default.SetConnectionStringsProvider(new DevExpress.DataAccess.Web.ConfigFileConnectionStringsProvider());

            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

            // Configures an SQL data source.
            DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", "NWindConnectionString");
            SelectQuery query = SelectQueryFluentBuilder
                .AddTable("Orders")
                .SelectAllColumnsFromTable()
                .Build("SQL Orders");
            sqlDataSource.Queries.Add(query);

            // Configures an Object data source.
            DashboardObjectDataSource objDataSource = new DashboardObjectDataSource("Object Data Source");
            objDataSource.DataId = "odsInvoices";

            // Configures an Excel data source.
            DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");
            excelDataSource.ConnectionName = "excelSales";
            excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/SalesPerson.xlsx");
            excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Data"));

            // Configures a JSON data source.
            DashboardJsonDataSource jsonDataSource = new DashboardJsonDataSource("JSON Data Source");
            jsonDataSource.ConnectionName = "jsonCategories";
            Uri fileUri = new Uri(HostingEnvironment.MapPath(@"~/App_Data/Categories.json"), UriKind.RelativeOrAbsolute);
            jsonDataSource.JsonSource = new UriJsonSource(fileUri);

            // Registers a Federated data source.
            dataSourceStorage.RegisterDataSource("federatedDataSource", CreateFederatedDataSource(sqlDataSource,
                excelDataSource, objDataSource, jsonDataSource).SaveToXml());

            DashboardConfigurator.Default.SetDataSourceStorage(dataSourceStorage);
            DashboardConfigurator.Default.DataLoading += DataLoading;
            DashboardConfigurator.Default.ConfigureDataConnection += Default_ConfigureDataConnection;
        }

        private static void Default_ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {
            if (e.ConnectionName == "excelSales") {
                (e.ConnectionParameters as ExcelDataSourceConnectionParameters).FileName = HostingEnvironment.MapPath(@"~/App_Data/SalesPerson.xlsx");
            }
            else if (e.ConnectionName == "jsonCategories") {
                UriJsonSource uriSource = (e.ConnectionParameters as JsonSourceConnectionParameters).JsonSource as UriJsonSource;
                uriSource.Uri = new Uri(HostingEnvironment.MapPath(@"~/App_Data/Categories.json"), UriKind.RelativeOrAbsolute);
            }
        }

        private static void DataLoading(object sender, DataLoadingWebEventArgs e) {
            if (e.DataId == "odsInvoices") {
                e.Data = Invoices.CreateData();
            }
        }

        private static DashboardFederationDataSource CreateFederatedDataSource(DashboardSqlDataSource sqlDS,
            DashboardExcelDataSource excelDS, DashboardObjectDataSource objDS, DashboardJsonDataSource jsonDS) {

            DashboardFederationDataSource federationDataSource = new DashboardFederationDataSource("Federated Data Source");

            Source sqlSource = new Source("sqlSource", sqlDS, "SQL Orders");
            Source excelSource = new Source("excelSource", excelDS, "");
            Source objectSource = new Source("objectSource", objDS, "");
            SourceNode jsonSourceNode = new SourceNode(new Source("json", jsonDS, ""));

            // Join
            SelectNode joinQuery =
            sqlSource.From()
            .Select("OrderDate", "ShipCity", "ShipCountry")
            .Join(excelSource, "[excelSource.OrderID] = [sqlSource.OrderID]")
                .Select("CategoryName", "ProductName", "Extended Price")
                .Join(objectSource, "[objectSource.Country] = [excelSource.Country]")
                    .Select("Country", "UnitPrice")
                    .Build("Join query");
            federationDataSource.Queries.Add(joinQuery);

            // Union and UnionAll
            UnionNode queryUnionAll = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite")
                .UnionAll(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel"))
                .Build("OrdersUnionAll");
            queryUnionAll.Alias = "Union query";

            UnionNode queryUnion = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite")
                .Union(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel"))
                .Build("OrdersUnion");
            queryUnion.Alias = "UnionAll query";

            federationDataSource.Queries.Add(queryUnionAll);
            federationDataSource.Queries.Add(queryUnion);

            // Transformation
            TransformationNode unfoldNode = new TransformationNode(jsonSourceNode) {
                Alias = "Unfold",
                Rules = { new TransformationRule { ColumnName = "Products", Alias = "Product", Unfold = true, Flatten = false } }
            };

            TransformationNode unfoldFlattenNode = new TransformationNode(jsonSourceNode) {
                Alias = "Unfold and Flatten",
                Rules = { new TransformationRule { ColumnName = "Products", Unfold = true, Flatten = true } }
            };

            federationDataSource.Queries.Add(unfoldNode);
            federationDataSource.Queries.Add(unfoldFlattenNode);

            return federationDataSource;
        }
    }
}

Visual Basic
Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb
Imports DevExpress.DashboardWeb.Mvc
Imports DevExpress.DataAccess.DataFederation
Imports DevExpress.DataAccess.Excel
Imports DevExpress.DataAccess.Json
Imports DevExpress.DataAccess.Sql
Imports System
Imports System.Web.Hosting
Imports System.Web.Routing

Namespace MVC_DataFederationExample
    Public NotInheritable Class DashboardConfig

        Private Sub New()
        End Sub

        Public Shared Sub RegisterService(ByVal routes As RouteCollection)
            routes.MapDashboardRoute("dashboardControl", "DefaultDashboard")

            Dim dashboardFileStorage As New DashboardFileStorage("~/App_Data/Dashboards")
            DashboardConfigurator.Default.SetDashboardStorage(dashboardFileStorage)

            DashboardConfigurator.PassCredentials = True

            ' Uncomment this string to allow end users to create new data sources based on predefined connection strings.
            'DashboardConfigurator.Default.SetConnectionStringsProvider(new DevExpress.DataAccess.Web.ConfigFileConnectionStringsProvider());

            Dim dataSourceStorage As New DataSourceInMemoryStorage()

            ' Configures an SQL data source.
            Dim sqlDataSource As New DashboardSqlDataSource("SQL Data Source", "NWindConnectionString")
            Dim query As SelectQuery = SelectQueryFluentBuilder.AddTable("Orders").SelectAllColumnsFromTable().Build("SQL Orders")
            sqlDataSource.Queries.Add(query)

            ' Configures an Object data source.
            Dim objDataSource As New DashboardObjectDataSource("Object Data Source")
            objDataSource.DataId = "odsInvoices"

            ' Configures an Excel data source.
            Dim excelDataSource As New DashboardExcelDataSource("Excel Data Source")
            excelDataSource.ConnectionName = "excelSales"
            excelDataSource.FileName = HostingEnvironment.MapPath("~/App_Data/SalesPerson.xlsx")
            excelDataSource.SourceOptions = New ExcelSourceOptions(New ExcelWorksheetSettings("Data"))

            ' Configures a JSON data source.
            Dim jsonDataSource As New DashboardJsonDataSource("JSON Data Source")
            jsonDataSource.ConnectionName = "jsonCategories"
            Dim fileUri As New Uri(HostingEnvironment.MapPath("~/App_Data/Categories.json"), UriKind.RelativeOrAbsolute)
            jsonDataSource.JsonSource = New UriJsonSource(fileUri)

            ' Registers a Federated data source.
            dataSourceStorage.RegisterDataSource("federatedDataSource", CreateFederatedDataSource(sqlDataSource, excelDataSource, objDataSource, jsonDataSource).SaveToXml())

            DashboardConfigurator.Default.SetDataSourceStorage(dataSourceStorage)
            AddHandler DashboardConfigurator.Default.DataLoading, AddressOf DataLoading
            AddHandler DashboardConfigurator.Default.ConfigureDataConnection, AddressOf Default_ConfigureDataConnection
        End Sub

        Private Shared Sub Default_ConfigureDataConnection(ByVal sender As Object, ByVal e As ConfigureDataConnectionWebEventArgs)
            If e.ConnectionName = "excelSales" Then
                TryCast(e.ConnectionParameters, ExcelDataSourceConnectionParameters).FileName = HostingEnvironment.MapPath("~/App_Data/SalesPerson.xlsx")
            ElseIf e.ConnectionName = "jsonCategories" Then
                Dim uriSource As UriJsonSource = TryCast((TryCast(e.ConnectionParameters, JsonSourceConnectionParameters)).JsonSource, UriJsonSource)
                uriSource.Uri = New Uri(HostingEnvironment.MapPath("~/App_Data/Categories.json"), UriKind.RelativeOrAbsolute)
            End If
        End Sub

        Private Shared Sub DataLoading(ByVal sender As Object, ByVal e As DataLoadingWebEventArgs)
            If e.DataId = "odsInvoices" Then
                e.Data = Invoices.CreateData()
            End If
        End Sub

        Private Shared Function CreateFederatedDataSource(ByVal sqlDS As DashboardSqlDataSource, ByVal excelDS As DashboardExcelDataSource, ByVal objDS As DashboardObjectDataSource, ByVal jsonDS As DashboardJsonDataSource) As DashboardFederationDataSource
            Dim federationDataSource As New DashboardFederationDataSource("Federated Data Source")

            Dim sqlSource As New Source("sqlSource", sqlDS, "SQL Orders")
            Dim excelSource As New Source("excelSource", excelDS, "")
            Dim objectSource As New Source("objectSource", objDS, "")
            Dim jsonSourceNode As New SourceNode(New Source("json", jsonDS, ""))

            ' Join
            Dim joinQuery As SelectNode = sqlSource.From().Select("OrderDate", "ShipCity", "ShipCountry").Join(excelSource, "[excelSource.OrderID] = [sqlSource.OrderID]").Select("CategoryName", "ProductName", "Extended Price").Join(objectSource, "[objectSource.Country] = [excelSource.Country]").Select("Country", "UnitPrice").Build("Join query")
            federationDataSource.Queries.Add(joinQuery)

            ' Union and UnionAll
            Dim queryUnionAll As UnionNode = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite").UnionAll(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel")).Build("OrdersUnionAll")
            queryUnionAll.Alias = "Union query"

            Dim queryUnion As UnionNode = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite").Union(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel")).Build("OrdersUnion")
            queryUnion.Alias = "UnionAll query"

            federationDataSource.Queries.Add(queryUnionAll)
            federationDataSource.Queries.Add(queryUnion)

            ' Transformation
            Dim unfoldRule = New TransformationRule
            With unfoldRule
                .ColumnName = "Products"
                .Unfold = True
                .Flatten = False
            End With
            Dim unfoldNode As New TransformationNode(jsonSourceNode)
            With unfoldNode
                .Alias = "Unfold"
                .Rules.Add(unfoldRule)
            End With

            Dim unfoldFlattenRule = New TransformationRule
            With unfoldFlattenRule
                .ColumnName = "Products"
                .Alias = "Product"
                .Unfold = True
                .Flatten = True
            End With
            Dim unfoldFlattenNode As New TransformationNode(jsonSourceNode)
            With unfoldFlattenNode
                .Alias = "Unfold and Flatten"
                .Rules.Add(unfoldFlattenRule)
            End With

            federationDataSource.Queries.Add(unfoldNode)
            federationDataSource.Queries.Add(unfoldFlattenNode)

            Return federationDataSource
        End Function
    End Class
End Namespace

DevExpress Support Team

last modified 4 years ago

Modified: December 8, 2020 2:05 PM

Platform: Business Intelligence

Product: Dashboard for MVC

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.