Federated Data Source

• Feb 27, 2023
• 4 minutes to read

A federated data source combines multiple data sources (except OLAP) in one.

Run Demo: Data Federation

Provide Data Sources for Federation

Note that you must 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

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);

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.

View Example