Federated Data Source

  • 9 minutes to read

A federated data source integrates different data sources and provides uniform data access with a federated query. OLAP data sources are not supported.

Run Demo: Data Federation

Overview

You can create a federated data source if a dashboard contains at least one data source. To do this, create a query where you specify how to federate existing data sources. The federated query can be one of the following types:

Join

Combines rows from two or more tables based on a column they share. The join type specifies records that have matching values in both tables.

Union

Combines rows from two or more tables into one data set and removes duplicate rows in merged tables. You can only create a union query for data sources that contain columns with the same name. Data types of such columns should be implicitly converted.

Union All

Operates like Union, but duplicates rows from different tables when they contain the same data.

Transformation

Unfolds complex objects and flattens them in a simple data set. The dashboard does not support data source fields that store collections so you need to transform the data source into a new flattened data table.

Create a Federated Data Source with the Data Source Wizard

You can create a federated data source with the Data Source Wizard only if a dashboard contains at least one data source.

  1. Click the New Data Source button in the Data Source ribbon tab.

    NewDataSourceButtonRibbonDataFederation

  2. On the first page of the invoked Data Source Wizard dialog, select Data Federation and click Next.

    Datasourcewizard-Federation-Start

  3. The Query Builder dialog displays the available data sources.

    QueryBuilder-New-Data-Federation

  4. Select the Query Type:

    Join

    Drag-and-drop the data sources, specify the related column to create the relationship between tables, and select the columns you want to include in the query. Use * (All Columns) to get a complete list of the columns from a table. Columns included in the query are displayed in the bottom pane where you can configure their settings.

    Data-Federation-Query-Builder

    Union

    Double-click the data sources you want to combine. Unlike Join queries, you cannot select individual columns. Columns included in the query are displayed in the bottom pane where you can specify their aliases.

    Union All

    Double-click the data sources you want to combine. A UnionAll query duplicates rows from different tables even if they contain the same data. Columns included in the query are displayed in the bottom pane where you can specify their aliases.

    Transformation

    Select a data source/query that contains columns you want to transform. Select the Transform checkbox next to the column you want to unfold and flatten. The bottom pane allows you to specify the aliases for the generated columns.

    Click OK to create a query.

  5. The Data Source Browser displays the newly created Federation Data Source.

    The image below displays a new Federation Data Source 1 with a joined SQlite Orders query.

    Data-Federation-Data-Source-Browser

    The query name is the same as the root table's name in the query builder. Click the Rename button in the Query group on the Data Source ribbon tab to change the query name.

Create a Federated Data Source in Code

The DashboardFederationDataSource class retrieves data from the federated query and supplies the dashboard with data.

To create the federated data source, make sure that the Dashboard.DataSources collection contains supported data sources.

The key object in the federated data source is a federated query created with standard or fluent syntax.

NOTE

You can create a union query for data sources that contain the same number of columns. The data type of such columns should be implicitly converted.

Standard Syntax

The UnionNode and SelectNode classes provide data with a Select and Union queries.

The code snippet below uses standard syntax to create a federated data source in code. It involves the following steps:

  1. Create a Source object for each data source integrated in the federated data source.

  2. Create a new SelectNode object to start building a federated query.

  3. Create a SourceNode object for each data source.

  4. Create and add expressions to the SelectNode.Expressions collections. Each expression defines a data column in the resulting dataset.

  5. Combine rows from data sources with JOINS or UNIONS.

    For Join:

    For Union:

  6. Add the SelectNode object to the FederationDataSourceBase.Queries collection as a federated query.

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

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.DataFederation;
using DevExpress.DataAccess.Excel;
using DevExpress.DataAccess.Sql;
// ...
    DashboardFederationDataSource federationDS = new DashboardFederationDataSource();
    Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
    Source excelSource = new Source("excel", exceldataSource, "");
    Source objectSource = new Source("SalesPersonDS", objectDataSource, "");

    SelectNode mainQueryCreatedByApi = new SelectNode();

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

    mainQueryCreatedByApi.Root = root;
    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 = root });
    mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCity", Node = root });
    mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCountry", Node = root });
    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]"));

    federationDS.Queries.Add(mainQueryCreatedByApi);

Fluent Syntax

The SelectNodeBuilder and UnionNodeBuilder classes provide Fluent interface methods to build a federated query.

The code snippet below uses the SelectNodeBuilder to create a federated data source in code.

  1. Create a Source object for each data source integrated in the federated data source.

  2. Use the Source.From extension method to access the SelectNodeBuilder and create a SelectNode object.

  3. Add the SelectNode object to the FederationDataSourceBase.Queries collection as a federated query.

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

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.DataFederation;
using DevExpress.DataAccess.Excel;
using DevExpress.DataAccess.Sql;
// ...
    DashboardFederationDataSource federationDS = new DashboardFederationDataSource();
    Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
    Source excelSource = new Source("excel", exceldataSource, "");
    Source objectSource = new Source("SalesPersonDS", objectDataSource, "");

    SelectNode mainQueryCreatedByNodeBuilder =
        sqlSource.From()
        .Select("OrderDate", "ShipCity", "ShipCountry")
        .Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]")
            .Select("CategoryName", "ProductName", "Extended Price")
            .Join(objectSource, "[SalesPersonDS.SalesPerson] = [excel.Sales Person]")
                .Select("SalesPerson", "Weight")
                .Build("FDS-Created-by-NodeBulder");

    federationDS.Queries.Add(mainQueryCreatedByNodeBuilder);

The code snippet below demonstrates how to use UnionType to create a federated data source from two Excel files. The UnionNodeBuilder's Fluent API is used to construct a federated query.

  1. Create a Source object for each data source integrated in the federated data source.
  2. Use the Source.From extension method to access the SelectNodeBuilder and create a SelectNode object.
  3. Use the Union(QueryNode, QueryNode, UnionType) method to combine rows from two or more queries into a single data set.
  4. Specify the UnionType.
  5. Use Build(String) to finalize the chain of methods used for a UnionNode.
  6. Add the final set to the Queries collection as a new query.
  7. Use the Fill method to retrieve data from integrated data sources.
using DevExpress.DataAccess.DataFederation;
using DevExpress.DataAccess.Excel;
using System.Linq;

//...

static ExcelDataSource CreateExcelDataSource(string fileName, string worksheetName) {
  var excelDataSource = new ExcelDataSource {
      FileName = fileName,
      SourceOptions = new ExcelSourceOptions {
          SkipEmptyRows = true,
          SkipHiddenColumns = true,
          SkipHiddenRows = true,
          ImportSettings = new ExcelWorksheetSettings { WorksheetName = worksheetName }
      }
  };
    return excelDataSource;
}

public static FederationDataSource CreateFederationDataSource() {
    Source salesPerson1 = new Source("SalesPerson", CreateExcelDataSource("../../../SalesPerson.xlsx", "Data"));
    Source salesPerson2 = new Source("SalesPerson2", CreateExcelDataSource("../../../SalesPerson.xlsx", "Data2"));
    var query =
        salesPerson1.From().Select("CategoryName", "Country", "Sales Person", "OrderDate", "OrderID", "Discount", "Extended Price").Build()
        .Union(
            salesPerson2.From().Select("CategoryName", "Country", "Sales Person", "OrderDate", "OrderID", "Discount", "Extended Price").Build(),
            UnionType.Union
        )
        .Build("UnionTable");
    FederationDataSource federation = new FederationDataSource();
    federation.Queries.Add(query);
    federation.Fill();
    return federation;
}