Federated Data Source
- 8 minutes to read
A federated data source integrates multiple data sources and provides uniform data access with a federated query.
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.
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 displays the available data sources.
Select the Query Type:
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.
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. Columns included in the query are displayed in the bottom pane where you can configure their settings.
Union
Combines rows from two or more tables into a single data set and removes duplicate rows in merged tables. You can only create a union query for data sources that contain similar columns with the same names. Data types of such columns should be implicitly converted.
Drag-and-drop the data sources to combine them in one. Unlike the join type, you cannot specify which columns to include in the union query. Columns included in the query are displayed in the bottom pane where you can specify the aliases of the newly created columns.
Union All
Operates in the same manner as Union, but duplicates rows from different tables when they contain the same data.
When you are finished, click OK.
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.
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:
Create a 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 or UNIONS.
For Join:
- Specify the SelectNode.Root and add join elements to the SelectNode.SubNodes collection. Join combines rows according to the specified join condition and set JoinType.
For Union:
- Specify the SelectNode.Root and add union elements to the SelectNode.SubNodes collection. Specify the UnionType.
Add the SelectNode object to the FederationDataSourceBase.Queries collection as a federated query.
Note
Example on GitHub: 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.
Create a 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
Example on GitHub: 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.
- Create a 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.
- Use the Union(QueryNode, QueryNode, UnionType) method to combine rows from two or more queries into a single data set.
- Specify the UnionType.
- Use Build(String) to finalize the chain of methods used for a UnionNode.
- Add the final set to the Queries collection as a new query.
- 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;
}
Limitations
- You cannot create a federated data source from OLAP data sources.
- You cannot import calculated fields from data sources to a federated query.