Federated Data Source
- 5 minutes to read
A federated data source integrates different data sources and provides uniform data access with a federated query.
Federated data source supports the following data source types:
Create a Federated Data Source with the Data Source Wizard
Note
The dashboard should have at least one SQL, Object or Excel data source already connected to it. Otherwise, the Data Federation option is not available in the Data Source Wizard.
To create a federated data source with the Data Source Wizard, perform the following steps:
Click the New Data Source button in the Data Source ribbon tab.
On the first page of the invoked Data Source Wizard dialog, select Data Federation and click Next.
The Query Builder dialog appears that displays the available data sources.
Drag-and-drop the data sources, specify the joins and check the columns to include them in the query. When you are finished, click OK.
The Data Source Browser displays the newly created Federation Data Source:
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. To create a federated query, use one of the following approaches:
Use Data Federation API
This code snippet uses the Data Federation API to create a federated data source in code. It involves the following steps:
Create the Source object for each data source integrated in the federated data source.
Create a new SelectNode object to start building a federated query.
Create a SourceNode object for each data source.
Create and add expressions to the SelectNode.Expressions collections. Each expression defines a data column in the resulting dataset.
Combine rows from data sources with JOINS. Specify the SelectNode.Root and add join elements to the SelectNode.SubNodes collection.
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);
Use SelectNodeBuilder
This code snippet uses the DevExpress.DataAccess.DataFederation.SelectNodeBuilder to create a federated data source in code. It involves the following steps:
Create the Source object for each data source integrated in the federated data source.
Use the Source.From extension method to access the SelectNodeBuilder and create a SelectNode object.
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);