dashboard-402458-web-dashboard-integrate-dashboard-component-aspnet-web-forms-dashboard-control-register-default-data-sources-federated-data-source.md
A federated data source combines multiple data sources (except OLAP) in one.
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");
' Configure data sources.
Dim sqlDataSource As New DashboardSqlDataSource("SQLite Data Source")
' ...
Dim objDataSource As New DashboardObjectDataSource()
' ...
Dim excelDataSource As New DashboardExcelDataSource("ExcelDS")
' ...
Dim jsonDataSource As New DashboardJsonDataSource("JSON Data Source")
' ...
' Specify sources.
Dim sqlSource As New Source("sqlite", sqlDataSource, "SQLite Orders")
Dim objectSource As New Source("SalesPersonDS", objDataSource, "")
Dim excelSource As New Source("excel", excelDataSource, "")
Dim jsonSource As New Source("json", jsonDataSource, "")
' Specify nodes.
Dim sqlSourceNode As New SourceNode(sqlSource, "SQLite Orders")
Dim excelSourceNode As New SourceNode(excelSource, "ExcelDS")
Dim objectSourceNode As New SourceNode(objectSource, "ObjectDS")
Dim jsonSourceNode As New SourceNode(jsonSource, "JsonDS")
The next step is to create a query where you specify how to federate the sources.
You can create federated queries of the following types:
JoinCombines rows from two or more sources based on a clause. In code, create a SelectNode instance.Union and Union AllThe 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.TransformationIf 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.
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);
' sqlSource, objectSource, and excelSource are specified earlier.
Dim federationDS As New DashboardFederationDataSource("Federated Data Source (JOIN)")
Dim joinQuery As SelectNode = 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)
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);
' sqlSource and excelSource are specified earlier.
Dim federationDS As New DashboardFederationDataSource("Federated Data Source (UNION)")
Dim sqlSource As New Source("sqlite", sqliteDataSource, "SQLite Orders")
Dim excelSource As New Source("excel", exceldataSource)
Dim queryUnionAll As UnionNode = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite").UnionAll(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel")).Build("OrdersUnionAll")
Dim queryUnion As UnionNode = 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)
Add the TransformationNode objects to the FederationDataSourceBase.Queries collection to transform a data source with complex columns. 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 the type of transformation. The code below shows how to set different transformation types:
// 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);
' jsonSource is specified earlier.
Dim federationDS As New DashboardFederationDataSource("Federated Data Source (Transformation)")
Dim sourceNode As New SourceNode(jsonSource)
Dim flattenRule = New TransformationRule
With flattenRule
.ColumnName = "Products"
.Unfold = False
.Flatten = True
End With
Dim flattenNode As New TransformationNode(jsonSourceNode)
With flattenNode
.Alias = "Unfold"
.Rules.Add(flattenRule)
End With
Dim unfoldRule = New TransformationRule
With unfoldRule
.ColumnName = "Products"
.Unfold = True
.Flatten = False
End With
Dim unfoldNode As New TransformationNode(jsonSourceNode)
With unfoldNode
.Alias = "Unfold"
.Rules.Add(unfoldRule)
End With
Dim unfoldFlattenRule = New TransformationRule
With unfoldFlattenRule
.ColumnName = "Products"
.Alias = "Product"
.Unfold = True
.Flatten = True
End With
Dim unfoldFlattenNode As New TransformationNode(jsonSourceNode)
With unfoldFlattenNode
.Alias = "Unfold and Flatten"
.Rules.Add(unfoldFlattenRule)
End With
federationDS.Queries.Add(flattenNode)
federationDS.Queries.Add(unfoldNode)
federationDS.Queries.Add(unfoldFlattenNode)
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");
' ...
Dim mainQueryCreatedByNodeBuilder As SelectNode = 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")
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.
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.