How to: Bind a Dashboard to a Federated Data Source
- 7 minutes to read
This example demonstrates how to bind a dashboard to a federated data source created at runtime.
The key object in the federated data source is a federated query. This example uses two methods to create two identical federated queries.
Method 1: Data Federation API
The first approach uses Data Federation API. It has 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.
Method 2: SelectNodeBuilder object
The second approach uses the DevExpress.DataAccess.DataFederation.SelectNodeBuilder object to create a federated data source in code. It has 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.
Result
The DashboardFederationDataSource that contains two federated queries in its Queries collection is added to the Dashboard.DataSources collection. It is available for binding as any other dashboard data source.
The Query Builder that displays the created federated query is shown in the following picture:
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;
}
// ...
}
}