DashboardFederationDataSource Class

A federated data source that retrieves data from different data sources.

Namespace: DevExpress.DashboardCommon

Assembly: DevExpress.Dashboard.v23.2.Core.dll

NuGet Package: DevExpress.Dashboard.Core

Declaration

C#
VB.NET

public class DashboardFederationDataSource :
    FederationDataSourceBase,
    IDashboardDataSource,
    IDashboardComponent,
    IComponent,
    IDisposable,
    ISupportInitialize,
    ISupportPrefix,
    IDashboardDataSourceInternal,
    ICloneable<DashboardFederationDataSource>,
    IAssignable<DashboardFederationDataSource>,
    IQueryDataSource<QueryNode>,
    IExternalSchemaConsumer

Public Class DashboardFederationDataSource
    Inherits FederationDataSourceBase
    Implements IDashboardDataSource,
               IDashboardComponent,
               IComponent,
               IDisposable,
               ISupportInitialize,
               ISupportPrefix,
               IDashboardDataSourceInternal,
               ICloneable(Of DashboardFederationDataSource),
               IAssignable(Of DashboardFederationDataSource),
               IQueryDataSource(Of QueryNode),
               IExternalSchemaConsumer

Remarks

The DashboardFederationDataSource class is a federated data source that integrates different data sources and provides uniform data access with a federated query. You can create a federated data source if a dashboard contains at least one data source. A federated data source does not support OLAP data sources.

Note

In Async mode, if the FederationDataSource includes the DashboardObjectDataSource, make sure that the DashboardObjectDataSource gets data in the AsyncDataLoading event handler and do not call the DashboardObjectDataSource.Fill and FederationDataSource.Fill methods.

You can create federated queries of the following types:

Join: Combines rows from two or more sources based on a clause. In code, create a SelectNode instance.
Union and Union All: The Union query combines rows from two or more sources into one data set and removes duplicate rows in the merged source. 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. In code, create a UnionNode instance and specify UnionNode.Type as Union/UnionAll.
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. In code, create a TransformationNode instance.

Run Demo: Data Federation

Example

This example demonstrates how to bind a dashboard to a federated data source created at runtime. The following data sources are used to create the Data Federation:

The example demonstrates the following query types you can use to federate existing data sources:

Join
Union and UnionAll
Transformation

Add the created DashboardFederationDataSource instance to the Dashboard.DataSources collection. The data source obtains its data from federated queries contained in the Queries collection.

View Example: How to Bind a Dashboard to a Federated Data Source Created at Runtime

Create Join Query Type

C#
VB.NET

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.DataFederation;
using DevExpress.XtraEditors;
// ...
namespace DataFederationExample {
    public partial class Form1 : XtraForm {
    // ...
        private static DashboardFederationDataSource CreateFederatedDataSourceJoin(DashboardSqlDataSource sqliteDataSource, DashboardExcelDataSource exceldataSource, DashboardObjectDataSource objectDataSource) { DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (JOIN)");
            Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
            Source excelSource = new Source("excel", exceldataSource, "");
            Source objectSource = new Source("object", objectDataSource, "");

            #region Use API to join SQL, Excel, and Object Data Sources in a Query
            SelectNode mainQueryCreatedByApi = new SelectNode();

            mainQueryCreatedByApi.Alias = "FDS-Created-by-API";
            SourceNode sqlSourceNode = new SourceNode(sqlSource, "SQLite Orders");
            SourceNode excelSourceNode = new SourceNode(excelSource, "ExcelDS");
            SourceNode objectSourceNode = new SourceNode(objectSource, "ObjectDS");

            mainQueryCreatedByApi.Root = sqlSourceNode;
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "SalesPerson", Node = objectSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "Weight", Node = objectSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "CategoryName", Node = excelSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ProductName", Node = excelSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "OrderDate", Node = sqlSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCity", Node = sqlSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCountry", Node = sqlSourceNode });
            mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "Extended Price", Node = excelSourceNode });
            mainQueryCreatedByApi.SubNodes.Add(new JoinElement(excelSourceNode, JoinType.Inner, "[ExcelDS.OrderID] = [SQLite Orders.OrderID]"));
            mainQueryCreatedByApi.SubNodes.Add(new JoinElement(objectSourceNode, JoinType.Inner, "[ObjectDS.SalesPerson] = [ExcelDS.Sales Person]"));
            #endregion

            #region Use NodedBuilder to join SQL, Excel, and Object Data Sources in a Query
            SelectNode mainQueryCreatedByNodeBuilder =
                sqlSource.From()
                .Select("OrderDate", "ShipCity", "ShipCountry")
                .Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]")
                    .Select("CategoryName", "ProductName", "Extended Price")
                    .Join(objectSource, "[object.SalesPerson] = [excel.Sales Person]")
                        .Select("SalesPerson", "Weight")
                        .Build("FDS-Created-by-NodeBulder");
            #endregion

            federationDS.Queries.Add(mainQueryCreatedByApi);
            federationDS.Queries.Add(mainQueryCreatedByNodeBuilder);

            federationDS.CalculatedFields.Add("FDS-Created-by-NodeBulder", "[Weight] * [Extended Price] / 100", "Score");

            federationDS.Fill(new DevExpress.Data.IParameter[0]);
            return federationDS;
        }
        // ...
    }
}

Imports DevExpress.DashboardCommon
Imports DevExpress.DataAccess.DataFederation
Imports DevExpress.XtraEditors
' ...
Namespace DataFederationExample
    Partial Public Class Form1
        Inherits XtraForm
' ...
        Private Shared Function CreateFederatedDataSourceJoin(ByVal sqliteDataSource As DashboardSqlDataSource, ByVal exceldataSource As DashboardExcelDataSource, ByVal objectDataSource As DashboardObjectDataSource) As DashboardFederationDataSource
            Dim federationDS As New DashboardFederationDataSource("Federated Data Source (JOIN)")
            Dim sqlSource As New Source("sqlite", sqliteDataSource, "SQLite Orders")
            Dim excelSource As New Source("excel", exceldataSource, "")
            Dim objectSource As New Source("object", objectDataSource, "")

            #Region "Use API to join SQL, Excel, and Object Data Sources in a Query"
            Dim mainQueryCreatedByApi As New SelectNode()

            mainQueryCreatedByApi.Alias = "FDS-Created-by-API"
            Dim sqlSourceNode As New SourceNode(sqlSource, "SQLite Orders")
            Dim excelSourceNode As New SourceNode(excelSource, "ExcelDS")
            Dim objectSourceNode As New SourceNode(objectSource, "ObjectDS")

            mainQueryCreatedByApi.Root = sqlSourceNode
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "SalesPerson", .Node = objectSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "Weight", .Node = objectSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "CategoryName", .Node = excelSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "ProductName", .Node = excelSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "OrderDate", .Node = sqlSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "ShipCity", .Node = sqlSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "ShipCountry", .Node = sqlSourceNode})
            mainQueryCreatedByApi.Expressions.Add(New SelectColumnExpression() With {.Name = "Extended Price", .Node = excelSourceNode})
            mainQueryCreatedByApi.SubNodes.Add(New JoinElement(excelSourceNode, JoinType.Inner, "[ExcelDS.OrderID] = [SQLite Orders.OrderID]"))
            mainQueryCreatedByApi.SubNodes.Add(New JoinElement(objectSourceNode, JoinType.Inner, "[ObjectDS.SalesPerson] = [ExcelDS.Sales Person]"))
            #End Region

            #Region "Use NodedBuilder to join SQL, Excel, and Object Data Sources in a Query"
            Dim mainQueryCreatedByNodeBuilder As SelectNode = sqlSource.From().Select("OrderDate", "ShipCity", "ShipCountry").Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]").Select("CategoryName", "ProductName", "Extended Price").Join(objectSource, "[object.SalesPerson] = [excel.Sales Person]").Select("SalesPerson", "Weight").Build("FDS-Created-by-NodeBulder")
            #End Region

            federationDS.Queries.Add(mainQueryCreatedByApi)
            federationDS.Queries.Add(mainQueryCreatedByNodeBuilder)

            federationDS.CalculatedFields.Add("FDS-Created-by-NodeBulder", "[Weight] * [Extended Price] / 100", "Score")

            federationDS.Fill(New DevExpress.Data.IParameter(){})
            Return federationDS
        End Function
' ...
    End Class
End Namespace

Create Transformation Query Type

C#
VB.NET

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.DataFederation;
using DevExpress.XtraEditors;
// ...
namespace DataFederationExample {
    public partial class Form1 : XtraForm {
    // ...
        private static DashboardFederationDataSource CreateFederatedDataSourceTransform(DashboardJsonDataSource jsonDataSource) {
            DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (Transformation)");
            Source jsonSource = new Source("json", jsonDataSource, "");
            SourceNode sourceNode = new SourceNode(jsonSource);

            TransformationNode defaultNode = new TransformationNode(sourceNode) {
                Alias = "Default",
                Rules = { new TransformationRule { ColumnName = "Products", Unfold = false, Flatten = false } }
            };

            TransformationNode flattenNode = new TransformationNode(sourceNode) {
                Alias = "Flatten",
                Rules = { new TransformationRule { ColumnName = "Products", Alias = "Product", Unfold = true, Flatten = true } }
            };

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

            federationDS.Queries.Add(defaultNode);
            federationDS.Queries.Add(flattenNode);
            federationDS.Queries.Add(unfoldNode);

            return federationDS;
        }
        // ...
    }
}

Imports DevExpress.DashboardCommon
Imports DevExpress.DataAccess.DataFederation
Imports DevExpress.XtraEditors
' ...
Namespace DataFederationExample
    Partial Public Class Form1
        Inherits XtraForm
' ...
        Private Shared Function CreateFederatedDataSourceTransform(ByVal jsonDataSource As DashboardJsonDataSource) As DashboardFederationDataSource
            Dim federationDS As New DashboardFederationDataSource("Federated Data Source (Transformation)")
            Dim jsonSource As New Source("json", jsonDataSource, "")
            Dim sourceNode As New SourceNode(jsonSource)

            Dim defaultNode As New TransformationNode(sourceNode)
            defaultNode.Alias = "Default"
            defaultNode.Rules.Add(New TransformationRule With {.ColumnName = "Products", .Unfold = False, .Flatten = False})

            Dim flattenNode As New TransformationNode(sourceNode)
            flattenNode.Alias = "Flatten"
            flattenNode.Rules.Add(New TransformationRule With {.ColumnName = "Products", .Alias = "Product", .Unfold = True, .Flatten = True})

            Dim unfoldNode As New TransformationNode(sourceNode)
            unfoldNode.Alias = "Unfold"
            unfoldNode.Rules.Add(New TransformationRule With {.ColumnName = "Products", .Alias = "Product", .Unfold = True, .Flatten = False})

            federationDS.Queries.Add(defaultNode)
            federationDS.Queries.Add(flattenNode)
            federationDS.Queries.Add(unfoldNode)

            Return federationDS
        End Function
' ...
    End Class
End Namespace

Create Union and UnionAll Query Types

C#
VB.NET

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.DataFederation;
using DevExpress.XtraEditors;
// ...
namespace DataFederationExample {
    public partial class Form1 : XtraForm {
    // ...
        private static DashboardFederationDataSource CreateFederatedDataSourceUnion(DashboardSqlDataSource sqliteDataSource, DashboardExcelDataSource exceldataSource) {
            DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (UNION)");
            Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
            Source excelSource = new Source("excel", exceldataSource);

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

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


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

            federationDS.Fill(new DevExpress.Data.IParameter[0]);
            return federationDS;
        }
        // ...
    }
}

Imports DevExpress.DashboardCommon
Imports DevExpress.DataAccess.DataFederation
Imports DevExpress.XtraEditors
' ...
Namespace DataFederationExample
    Partial Public Class Form1
        Inherits XtraForm
' ...
        Private Shared Function CreateFederatedDataSourceUnion(ByVal sqliteDataSource As DashboardSqlDataSource, ByVal exceldataSource As DashboardExcelDataSource) As DashboardFederationDataSource
            Dim federationDS As New DashboardFederationDataSource("Federated Data Source (UNION)")
            Dim sqlSource As New Source("sqlite", sqliteDataSource, "SQLite Orders")
            Dim excelSource As New Source("excel", exceldataSource)

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

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


            federationDS.Queries.Add(queryUnionAll)
            federationDS.Queries.Add(queryUnion)

            federationDS.Fill(New DevExpress.Data.IParameter(){})
            Return federationDS
        End Function
' ...
    End Class
End Namespace