Federated Data Source
- 6 minutes to read
A federated data source combines multiple data sources (except OLAP) in one.
Provide Data Sources for Federation
You need to configure at least one data source to create a federated data source from it. Then define the Source object for each data source you want to federate and create a SourceNode object from it. The code below creates source nodes for SQL, Excel, Object, and JSON data sources:
// Configure data sources.
DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQLite Data Source");
// ...
DashboardObjectDataSource objDataSource = new DashboardObjectDataSource();
// ...
DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("ExcelDS");
// ...
DashboardJsonDataSource jsonDataSource = new DashboardJsonDataSource("JSON Data Source");
// ...
// Specify sources.
Source sqlSource = new Source("sqlite", sqlDataSource, "SQLite Orders");
Source objectSource = new Source("SalesPersonDS", objDataSource);
Source excelSource = new Source("excel", excelDataSource);
Source jsonSource = new Source("json", jsonDataSource);
// Specify source nodes.
SourceNode sqlSourceNode = new SourceNode(sqlSource, "SQLite Orders");
SourceNode excelSourceNode = new SourceNode(excelSource, "ExcelDS");
SourceNode objectSourceNode = new SourceNode(objectSource, "ObjectDS");
SourceNode jsonSourceNode = new SourceNode(jsonSource, "JsonDS");
The next step is to create a query where you specify how to federate the sources.
Create a Federated Query
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.
Join
To create a Join query, create a SelectNode object. Add the SelectNode object to the FederationDataSourceBase.Queries collection.
// sqlSource, objectSource, and excelSource are specified earlier.
DashboardFederationDataSource federationDataSource = new DashboardFederationDataSource("Federated Data Source (JOIN)");
SelectNode joinQuery =
sqlSource.From()
.Select("OrderDate", "ShipCity", "ShipCountry")
.Join(excelSource, "[excelSource.OrderID] = [sqlSource.OrderID]")
.Select("CategoryName", "ProductName", "Extended Price")
.Join(objectSource, "[objectSource.Country] = [excelSource.Country]")
.Select("Country", "UnitPrice")
.Build("Join query");
federationDataSource.Queries.Add(joinQuery);
Union and UnionAll
To create Union or UnionAll queries, configure the UnionNode object and add it to the FederationDataSourceBase.Queries collection.
// sqlSource and excelSource are specified earlier.
DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (UNION)");
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);
Transformation
To transform a data source with complex columns, add the TransformationNode objects to the FederationDataSourceBase.Queries collection. Create the TransformationRule object that contains transformation rules and add it to the node’s TransformationNode.Rules collection. Use the TransformationRule.Unfold and TransformationRule.Flatten properties to specify a type of transformation. The code below shows how to set different types of transformation:
// jsonSource is specified earlier.
DashboardFederationDataSource federationDS = new DashboardFederationDataSource("Federated Data Source (Transformation)");
TransformationNode flattenNode = new TransformationNode(jsonSourceNode) {
Alias = "Unfold",
Rules = { new TransformationRule { ColumnName = "Products", Alias = "Product", Unfold = false, Flatten = true } }
};
TransformationNode unfoldNode = new TransformationNode(jsonSourceNode) {
Alias = "Unfold",
Rules = { new TransformationRule { ColumnName = "Products", Alias = "Product", Unfold = true, Flatten = false } }
};
TransformationNode unfoldFlattenNode = new TransformationNode(jsonSourceNode) {
Alias = "Unfold and Flatten",
Rules = { new TransformationRule { ColumnName = "Products", Unfold = true, Flatten = true } }
};
federationDS.Queries.Add(flattenNode);
federationDS.Queries.Add(unfoldNode);
federationDS.Queries.Add(unfoldFlattenNode);
Calculated Fields
Add the CalculatedField object to the CalculatedFields collection to create a calculated field.
// ...
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");
federationDataSource.Queries.Add(mainQueryCreatedByNodeBuilder);
federationDataSource.CalculatedFields.Add("FDS", "[Weight] * [Extended Price] / 100", "Score");
Dashboard Data Source Wizard
Users can use the Dashboard Data Source Wizard to create a new federated data source based on existing data sources.
See the following topic for details: Specify Data Source Settings for a Federated Data Source.
Example
The example shows how to make a federated data source available to users in the Web Dashboard application. The federated data source combines SQL, Extract, Object, and JSON data sources with different rule types.