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 sources based on a clause. In code, create a SelectNode instance.
- Union and Union All
- The Union query combines rows from two or more sources into one data set and removes duplicate rows in the merged source. 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.
Create a New Federated 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.
Select the Query Type
The Query Builder dialog displays the available data sources you can use to create queries.
Join
Drag-and-drop the data sources you want to join to the diagram pane, 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 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.
Copy Calculated Fields
The Data Source Wizard invokes the following page if one of the selected data sources contains calculated fields. Drag or double-click the calculated field you want to copy to the resulting federated data source. You can specify the name of the copied calculated field in the left Grid.
See the Result
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 has the same name as the root table 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
This example demonstrates how to bind a dashboard to a federated data source created at runtime. The following data sources are used to create the Data Federation:
- SQL data source connected to the SQLite database
- Excel data source
- Object data source
- JSON Data Source
The example demonstrates the following query types you can use to federate existing data sources:
- Join
- Union and UnionAll
- Transformation
Add the created DashboardFederationDataSource instance to the Dashboard.DataSources collection. The data source obtains its data from federated queries contained in the Queries collection.
Create Join Query Type
using DevExpress.DashboardCommon;
using DevExpress.DataAccess.DataFederation;
using DevExpress.XtraEditors;
// ...
namespace DataFederationExample {
public partial class Form1 : XtraForm {
// ...
private static DashboardFederationDataSource CreateFederatedDataSourceJoin(DashboardSqlDataSource sqliteDataSource, DashboardExcelDataSource exceldataSource, DashboardObjectDataSource objectDataSource) { DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (JOIN)");
Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
Source excelSource = new Source("excel", exceldataSource, "");
Source objectSource = new Source("object", objectDataSource, "");
#region Use API to join SQL, Excel, and Object Data Sources in a Query
SelectNode mainQueryCreatedByApi = new SelectNode();
mainQueryCreatedByApi.Alias = "FDS-Created-by-API";
SourceNode sqlSourceNode = new SourceNode(sqlSource, "SQLite Orders");
SourceNode excelSourceNode = new SourceNode(excelSource, "ExcelDS");
SourceNode objectSourceNode = new SourceNode(objectSource, "ObjectDS");
mainQueryCreatedByApi.Root = sqlSourceNode;
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 = sqlSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCity", Node = sqlSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCountry", Node = sqlSourceNode });
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]"));
#endregion
#region Use NodedBuilder to join SQL, Excel, and Object Data Sources in a Query
SelectNode mainQueryCreatedByNodeBuilder =
sqlSource.From()
.Select("OrderDate", "ShipCity", "ShipCountry")
.Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]")
.Select("CategoryName", "ProductName", "Extended Price")
.Join(objectSource, "[object.SalesPerson] = [excel.Sales Person]")
.Select("SalesPerson", "Weight")
.Build("FDS-Created-by-NodeBulder");
#endregion
federationDS.Queries.Add(mainQueryCreatedByApi);
federationDS.Queries.Add(mainQueryCreatedByNodeBuilder);
federationDS.CalculatedFields.Add("FDS-Created-by-NodeBulder", "[Weight] * [Extended Price] / 100", "Score");
federationDS.Fill(new DevExpress.Data.IParameter[0]);
return federationDS;
}
// ...
}
}
Create Transformation Query Type
using DevExpress.DashboardCommon;
using DevExpress.DataAccess.DataFederation;
using DevExpress.XtraEditors;
// ...
namespace DataFederationExample {
public partial class Form1 : XtraForm {
// ...
private static DashboardFederationDataSource CreateFederatedDataSourceTransform(DashboardJsonDataSource jsonDataSource) {
DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (Transformation)");
Source jsonSource = new Source("json", jsonDataSource, "");
SourceNode sourceNode = new SourceNode(jsonSource);
TransformationNode defaultNode = new TransformationNode(sourceNode) {
Alias = "Default",
Rules = { new TransformationRule { ColumnName = "Products", Unfold = false, Flatten = false } }
};
TransformationNode flattenNode = new TransformationNode(sourceNode) {
Alias = "Flatten",
Rules = { new TransformationRule { ColumnName = "Products", Alias = "Product", Unfold = true, Flatten = true } }
};
TransformationNode unfoldNode = new TransformationNode(sourceNode) {
Alias = "Unfold",
Rules = { new TransformationRule { ColumnName = "Products", Alias = "Product", Unfold = true, Flatten = false } }
};
federationDS.Queries.Add(defaultNode);
federationDS.Queries.Add(flattenNode);
federationDS.Queries.Add(unfoldNode);
return federationDS;
}
// ...
}
}
Create Union and UnionAll Query Types
using DevExpress.DashboardCommon;
using DevExpress.DataAccess.DataFederation;
using DevExpress.XtraEditors;
// ...
namespace DataFederationExample {
public partial class Form1 : XtraForm {
// ...
private static DashboardFederationDataSource CreateFederatedDataSourceUnion(DashboardSqlDataSource sqliteDataSource, DashboardExcelDataSource exceldataSource) {
DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (UNION)");
Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
Source excelSource = new Source("excel", exceldataSource);
UnionNode queryUnionAll = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite")
.UnionAll(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel"))
.Build("OrdersUnionAll");
UnionNode queryUnion = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite")
.Union(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel"))
.Build("OrdersUnion");
federationDS.Queries.Add(queryUnionAll);
federationDS.Queries.Add(queryUnion);
federationDS.Fill(new DevExpress.Data.IParameter[0]);
return federationDS;
}
// ...
}
}