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.
Overview
You can create a federated data source if a dashboard contains at least one data source. Create a federated query where you specify how to federate existing data sources.
You can create federated queries of the following types:
Join
Combines rows from two or more tables based on a clause. In code, create a SelectNode instance.
Union and Union All
The Union query combines rows from two or more tables into one data set and removes duplicate rows in merged tables. 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.
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
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.
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.
The Data Source Browser displays the newly created federated 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 supply 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.
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.
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;
}