Skip to main content
A newer version of this page is available. .

Federated Data Source

  • 7 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.

Tip

Demo: Data Federation

Requires installation of Universal Subscription. Download

Create a Federated Data Source with the Data Source Wizard

To create a federated data source with the Data Source Wizard, provide at least one data source to be used to create a federated data source and perform the following steps:

  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 appears that displays the available data sources.

    QueryBuilder-New-Data-Federation

  4. Drag-and-drop the data sources, specify the joins in the invoked Join Editor and check the columns to include them in the query. When you are finished, click OK.

    Data-Federation-Querybuilder

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

    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 the 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.

Note

The complete sample project How to Bind a Dashboard to a Federated Data Source Created at Runtime is available in the DevExpress Examples repository.

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 the 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.

Note

The complete sample project How to Bind a Dashboard to a Federated Data Source Created at Runtime is available in the DevExpress Examples repository.

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 the 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;
}