private static DashboardFederationDataSource CreateFederatedDataSource(DashboardSqlDataSource sqlDS,
DashboardExcelDataSource excelDS, DashboardObjectDataSource objDS, DashboardJsonDataSource jsonDS)
{
DashboardFederationDataSource federationDataSource = new DashboardFederationDataSource("Federated Data Source");
Source sqlSource = new Source("sqlSource", sqlDS, "SQL Orders");
Source excelSource = new Source("excelSource", excelDS, "");
Source objectSource = new Source("objectSource", objDS, "");
SourceNode jsonSourceNode = new SourceNode(new Source("json", jsonDS, ""));
// 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
UnionNode queryUnionAll = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite")
.UnionAll(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel"))
.Build("OrdersUnionAll");
queryUnionAll.Alias = "Union query";
UnionNode queryUnion = sqlSource.From().Select("OrderID", "OrderDate").Build("OrdersSqlite")
.Union(excelSource.From().Select("OrderID", "OrderDate").Build("OrdersExcel"))
.Build("OrdersUnion");
queryUnion.Alias = "UnionAll query";
federationDataSource.Queries.Add(queryUnionAll);
federationDataSource.Queries.Add(queryUnion);
// Transformation
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
} }
};
federationDataSource.Queries.Add(unfoldNode);
federationDataSource.Queries.Add(unfoldFlattenNode);
return(federationDataSource);
}
public void InitializeDashboard()
{
Dashboard dashboard = new Dashboard();
DashboardSqlDataSource sqliteDataSource = CreateSQLiteDataSource();
dashboard.DataSources.Add(sqliteDataSource);
DashboardExcelDataSource exceldataSource = CreateExcelDataSource();
dashboard.DataSources.Add(exceldataSource);
DashboardObjectDataSource objectDataSource = CreateObjectDataSource();
dashboard.DataSources.Add(objectDataSource);
DashboardFederationDataSource federatedDS = CreateFederatedDataSource(sqliteDataSource, exceldataSource, objectDataSource);
dashboard.DataSources.Add(federatedDS);
PivotDashboardItem pivot = new PivotDashboardItem();
pivot.DataMember = "FDS-Created-by-NodeBulder";
pivot.DataSource = federatedDS;
pivot.Rows.AddRange(new Dimension("CategoryName"), new Dimension("ProductName"));
pivot.Columns.Add(new Dimension("SalesPerson"));
pivot.Values.Add(new Measure("Extended Price"));
ChartDashboardItem chart = new ChartDashboardItem();
chart.DataSource = federatedDS;
chart.DataMember = "FDS-Created-by-NodeBulder";
chart.Arguments.Add(new Dimension("SalesPerson"));
chart.Panes.Add(new ChartPane());
SimpleSeries theSeries = new SimpleSeries(SimpleSeriesType.Bar);
theSeries.Value = new Measure("Score");
chart.Panes[0].Series.Add(theSeries);
dashboard.Items.AddRange(pivot, chart);
dashboard.RebuildLayout();
dashboard.LayoutRoot.Orientation = DashboardLayoutGroupOrientation.Vertical;
dashboardDesigner1.Dashboard = dashboard;
}
private static DashboardFederationDataSource CreateFederatedDataSource(DashboardSqlDataSource sqliteDataSource, DashboardExcelDataSource exceldataSource, DashboardObjectDataSource objectDataSource)
{
DashboardFederationDataSource federationDS = new DashboardFederationDataSource();
Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
Source excelSource = new Source("excel", exceldataSource, "");
Source objectSource = new Source("SalesPersonDS", objectDataSource, "");
#region Use-API-to-create-a-query
SelectNode mainQueryCreatedByApi = new SelectNode();
mainQueryCreatedByApi.Alias = "FDS-Created-by-API";
SourceNode root = new SourceNode(sqlSource, "SQLite Orders");
SourceNode excelSourceNode = new SourceNode(excelSource, "ExcelDS");
SourceNode objectSourceNode = new SourceNode(objectSource, "ObjectDS");
mainQueryCreatedByApi.Root = root;
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 = root
});
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression()
{
Name = "ShipCity", Node = root
});
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression()
{
Name = "ShipCountry", Node = root
});
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-create-a-query
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-Created-by-NodeBulder");
#endregion
federationDS.Queries.Add(mainQueryCreatedByNodeBuilder);
federationDS.Queries.Add(mainQueryCreatedByApi);
federationDS.CalculatedFields.Add("FDS-Created-by-NodeBulder", "[Weight] * [Extended Price] / 100", "Score");
federationDS.Fill(new DevExpress.Data.IParameter[0]);
return(federationDS);
}
