It not a rare case when we need to create the Cube in run time rather than in design time. This article will explain you just how do that.
When creating the Cube structure in run time there are two main scenarios depending on your particular needs:
- The Cube and Grid objects are created in design time.
- Both Cube and Grid are created in run time.
Variant #1: Cube and Grid Objects Created in Design Time
The simplest way would be to create the Cube and Grid objects in design time, the Grid.CubeID property references to the Cube, and Cube.DataSourceID property references to the correct dataset type (not an object, mind you, because in it will be up to you to fill the dataset with data).
To begin with put both the Cube and the Grid on the web form (by default their names will be TOLAPCube1 and TOLAPGrid1 correspondently), set TOLAPGrid1.CubeID property to TOLAPCube1. Then create the typed dataset schema in your web project - for example named "Northwind.xsd", and set the TOLAPCube1.DataSourceID property to "DataSet: Northwind". That's all you need to do in design time. In the end your web page and solution should look like this:
And the properties of the TOLAPCube1 control are:
Again, note that the DataSourceID property is set to the type of the dataset, not the object.
The next thing is to write a code that creates the Cube in run time. It's best to place this code in the Page_Init event handler in your web page.
Note.To avoid errors in the components' life cycle, we strongly recommend you to stick to the following rules. In Page_Init page event handler you must:
- create the Cube or/and the Grid;
- assign TOLAPGrid.CubeID property;
- assign different event handlers for the Cube/Grid.
All other operations with the Cube/Grid can be performed in the ASP.NET web controls event handlers (for example Button_Click) or in Page_Load page event handler.
protected void Page_Init(object sender, EventArgs e)
{
// CreateCube1 does all the work to create the cube in runtime
CreateCube1();
}
private void CreateCube1()
{
// The method assumes that both the cube and the grid were created in desing time
// So here we just set up the cube structure and activate the cube
TOLAPCube cube = TOLAPCube1;
cube.OnCalculateField += cube_OnCalculateField;
// Only need to create the cube on the first web page call.
if (!IsPostBack)
{
// Here we assume that the cube's DataSet property has been assigned in design time
InitCubeStructure(cube);
// ... and open the cube
cube.Active = true;
}
}
private void InitCubeStructure(TOLAPCube cube)
{
Northwind d = cube.DataBase as Northwind;
if (d == null)
throw new ApplicationException("The cube's DataSet property must be assigned before setting up the structure");
// Create dimensions and hierarchies
cube.AddHierarchy("Shippers", d.Shippers, "CompanyName", "", "Shippers");
// Make 3 hierarchies in the "Products" dimension: "Products", "Categories", and "Suppliers"
TCubeHierarchy H1 = cube.AddHierarchy("Products", d.Products, "ProductName", "", "Products");
TCubeHierarchy H2 = cube.AddHierarchy("Products", d.Categories, "CategoryName", "", "Categories");
TCubeHierarchy H3 = cube.AddHierarchy("Products", d.Suppliers, "CompanyName", "", "Suppliers");
// Make two composite (multilevel) hierarchies
cube.MakeUpCompositeHierarchy("Products", "Products by categories", new TCubeHierarchy[] { H2, H1 });
cube.MakeUpCompositeHierarchy("Products", "Products by suppliers", new string[] { "Suppliers", "Products" });
// Add BI time hierarchies: "Year", "Quarter", "Month"...
cube.AddBIHierarchy("Time", d.Orders, "Year", "OrderDate", TBIMembersType.ltTimeYear);
cube.AddBIHierarchy("Time", d.Orders, "Quarter", "OrderDate", TBIMembersType.ltTimeQuarter);
cube.AddBIHierarchy("Time", d.Orders, "Month", "OrderDate", TBIMembersType.ltTimeMonthLong);
// ... and combine them into a single "Date" hierarchy
cube.MakeUpCompositeHierarchy("Time", "Date", new string[] { "Year", "Quarter", "Month" });
// The two lines add the calculated hierarchy "Employee Name" into the "Employees" dimension:
// The "Employee Name" column must be calculated in the TOLAPCube1.OnCalculateField even handler
cube.AddCalculatedColumn(d.Employees, "Employee Name", typeof(String));
cube.AddHierarchy("Employees", d.Employees, "Employee Name", "ReportsTo", "Employees");
// just the same thing might have been done with a single line of code:
// cube.AddCalculatedHierarchy("Employees", d.Employees, typeof(string), "Employee Name");
cube.AddHierarchy("Customers", d.Customers, "CompanyName", "", "Customers");
// Add two measures: "Quantity" and "Sales"
cube.AddMeasure(d.Order_Details, "Quantity");
// The "Sales" column must be calculated in the TOLAPCube1.OnCalculateField even handler
cube.AddCalculatedMeasure(d.Order_Details, typeof(double), "Sales");
}
private void cube_OnCalculateField(object Sender, TCalculateFieldArgs e)
{
// Here we calculate two columns: "Employees"."Employee Name" and "Order Details"."Sales"
if (e.ThisTable("Employees"))
{
e.Row["Employee Name"] = e.Row["FirstName"] + " " + e.Row["LastName"];
}
if (e.ThisTable("Order Details"))
{
e.Row["Sales"] = Convert.ToDouble(e.Row["Quantity"]) * Convert.ToDouble(e.Row["UnitPrice"]) * (1 - Convert.ToDouble(e.Row["Discount"]));
}
}
First and foremost, the code of creating the Cube is run only once on the first web page request. This is indicated by the IsPostBack page property. The following code creates the desired dimensions, hierarchies and measures in the Cube. You can search through the documentation for the details on particular methods. Note that in this example we have several calculated objects: the hierarchy "Employee Name" and the measure "Sales". When you define a calculated object like hierarchy or measure the Cube automatically creates calculated columns within the corresponding tables. The values of the calculated columns must be computed in the TOLAPCube.OnCalculateField event handler. For more details see " Calculated Fields. Using TOLAPCube.OnCalculateField event" .
What we need to point out here that in fact we do not create the dataset ourselves; instead the Cube does it for us. As you remember the initial condition was that the Cube.DataSourceID would be assigned to "DataSet: Northwind" in design time. When this property is assigned with the type (not object), the Cube creates the real dataset itself, and then creates all needed table adapters to fill the dataset. Thus we don't have to create the dataset ourselves, which is the greatest advantage of using dataset type (not object) as a data source.
Initial Grid State
The code works just fine, however it only creates the Cube structure, it doesn't lay out any cube items in the Grid. So now you have the Cube ready for use but you will have to locate the hierarchies and measures in the Grid. To set up the initial state of the Grid you can use some of the its methods.
The best place for this code would be the Page_Load event on your web page:
protected void Page_Load(object sender, EventArgs e)
{
InitGrid(TOLAPGrid1);
}
private void InitGrid(TOLAPGrid grid)
{
// Only do for the first web page request
if (!IsPostBack)
{
// Set up the grid configuration (may not do it but the grid initially will be empty in this case)
// Find the "Products by categories" hierarchy ...
THierarchy H = grid.Dimensions.FindHierarchyByDisplayName("Products by categories");
// ... and if found place it in the Rows area
if (H != null) grid.PivotingLast(H, TLayoutArea.laRow);
// Place the "Date" hierarchy in the Columns area
H = grid.Dimensions.FindHierarchyByDisplayName("Date");
if (H != null) grid.PivotingLast(H, TLayoutArea.laColumn);
// Make the "Sales" measure visible
TMeasure M = grid.Measures.FindByDisplayName("Sales");
if (M != null) M.Visible = true;
}
}
So, we have just placed the hierarchies and measures we need where they should be. The code is obviously clear, but you may refer to the documentation for details.
How to Speed up the Process?
There is one thing we can speed up this algorithm's work. The idea is that the Cube doesn't actually need to have the dataset filled up to get open. Instead it may use the IDataReader or IDbCommand interface for every table to fetch data from. For details see Creating the data source for OLAPCube, but in our example you may use the following code to make the cube operate faster:
namespace NorthwindTableAdapters
{
public partial class CategoriesTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class SuppliersTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class ShippersTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class OrdersTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class EmployeesTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class ProductsTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class CustomersTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
public partial class Order_DetailsTableAdapter
{
public System.Data.OleDb.OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}
}
Applying this code will be enough. When you define the DbCommand property the Cube won't fill up the data set but use the ExecuteReader method of the IDbCommand interface to get the IDataReader for a particular table instead.
When you write this code, remember to place it to the module located in the same directory as the dataset, otherwise the Visual Studio environment will not be able to compile it. For example, if the Northwind.xsd dataset is in the App_Code ASP.NET directory (which is recommended and in most cases true), then you can add the ...\App_Code\Northwind.cs module into your project and write the code there.
Variant #2: Both Cube and Grid Created in Run Time
In this case we do not have anything created in design time. So we have to create everything in run time.
protected void Page_Init(object sender, EventArgs e)
{
CreateCube2();
}
private TOLAPCube RuntimeCube;
private TOLAPGrid RuntimeGrid;
private void CreateCube2()
{
// Create the cube
RuntimeCube = new TOLAPCube();
RuntimeCube.ID = "CUBEID";
form1.Controls.Add(RuntimeCube);
// Create the grid
RuntimeGrid = new TOLAPGrid();
RuntimeGrid.ID = "GRIDID";
form1.Controls.Add(RuntimeGrid);
RuntimeGrid.CubeID = "CUBEID";
TOLAPCube cube = RuntimeCube;
cube.OnCalculateField += cube_OnCalculateField;
if (!IsPostBack)
{
// Before we create the cube structure we need to create and assigne the dataset
cube.DataSet = CreateDataSet1();
InitCubeStructure(cube);
cube.Active = true;
}
}
private Northwind CreateDataSet1()
{
// Create the dataset
Northwind d = new Northwind();
// Also need to create all table adapters in order to fill up the tables
NorthwindTableAdapters.Order_DetailsTableAdapter Order_DetailsAdapter = new NorthwindTableAdapters.Order_DetailsTableAdapter();
NorthwindTableAdapters.CustomersTableAdapter CustomersAdapter = new NorthwindTableAdapters.CustomersTableAdapter();
NorthwindTableAdapters.ProductsTableAdapter ProductsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();
NorthwindTableAdapters.EmployeesTableAdapter EmployeesAdapter = new NorthwindTableAdapters.EmployeesTableAdapter();
NorthwindTableAdapters.OrdersTableAdapter OrdersAdapter = new NorthwindTableAdapters.OrdersTableAdapter();
NorthwindTableAdapters.ShippersTableAdapter ShippersAdapter = new NorthwindTableAdapters.ShippersTableAdapter();
NorthwindTableAdapters.SuppliersTableAdapter SuppliersAdapter = new NorthwindTableAdapters.SuppliersTableAdapter();
NorthwindTableAdapters.CategoriesTableAdapter CategoriesAdapter = new NorthwindTableAdapters.CategoriesTableAdapter();
// Fill all the tables in the dataset
Order_DetailsAdapter.Fill(d.Order_Details);
CustomersAdapter.Fill(d.Customers);
ProductsAdapter.Fill(d.Products);
EmployeesAdapter.Fill(d.Employees);
OrdersAdapter.Fill(d.Orders);
ShippersAdapter.Fill(d.Shippers);
SuppliersAdapter.Fill(d.Suppliers);
CategoriesAdapter.Fill(d.Categories);
return d;
}
First the CreateCube2() method creates the Cube and Grid and inserts them into the form's Controls collection. Never try to insert the runtime controls into the Page's Controls collection! All ASP.NET controls created in run time must be inserted into the Controls collection of the form or any other container inside the form. Then the method calls on CreateDataSet1() method to create the dataset to use, and fill it with data.
Initial Grid State
To set up the initial Grid state we use the same method InitGrid described above, but this time for the Grid that has been created in run time:
protected void Page_Load(object sender, EventArgs e)
{
InitGrid(RuntimeGrid);
}
How to Speed up the Process?
Of course, our example will work just fine, however could we use the IDataReader or IDbCommand interface to make the Cube work faster? It is possible but in a slightly different way, then in the variant #1.
The difference is due to the fact that the Cube cannot detect the DbCommand or DataReader properties in the table adapters in runtime as in design time, when the dataset type information is available. But this is no problem, because we can set the IDbCommand interface for direct use:
private Northwind CreateDataSet2(TOLAPCube cube)
{
// Create the dataset
Northwind d = new Northwind();
// Also need to create all table adapters in order to fill up the tables
NorthwindTableAdapters.Order_DetailsTableAdapter Order_DetailsAdapter = new NorthwindTableAdapters.Order_DetailsTableAdapter();
NorthwindTableAdapters.CustomersTableAdapter CustomersAdapter = new NorthwindTableAdapters.CustomersTableAdapter();
NorthwindTableAdapters.ProductsTableAdapter ProductsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();
NorthwindTableAdapters.EmployeesTableAdapter EmployeesAdapter = new NorthwindTableAdapters.EmployeesTableAdapter();
NorthwindTableAdapters.OrdersTableAdapter OrdersAdapter = new NorthwindTableAdapters.OrdersTableAdapter();
NorthwindTableAdapters.ShippersTableAdapter ShippersAdapter = new NorthwindTableAdapters.ShippersTableAdapter();
NorthwindTableAdapters.SuppliersTableAdapter SuppliersAdapter = new NorthwindTableAdapters.SuppliersTableAdapter();
NorthwindTableAdapters.CategoriesTableAdapter CategoriesAdapter = new NorthwindTableAdapters.CategoriesTableAdapter();
// Instead of filling the table we just point the IDbCommand interface with the method AddDataTable
cube.AddDataTable(d.Order_Details, Order_DetailsAdapter.DbCommand);
cube.AddDataTable(d.Customers, CustomersAdapter.DbCommand);
cube.AddDataTable(d.Products, ProductsAdapter.DbCommand);
cube.AddDataTable(d.Employees, EmployeesAdapter.DbCommand);
cube.AddDataTable(d.Orders, OrdersAdapter.DbCommand);
cube.AddDataTable(d.Shippers, ShippersAdapter.DbCommand);
cube.AddDataTable(d.Suppliers, SuppliersAdapter.DbCommand);
cube.AddDataTable(d.Categories, CategoriesAdapter.DbCommand);
return d;
}
Here we do not fill the dataset; instead we call on the AddDataTable method with the IDbCommand interface as a second parameter. This makes the Cube use this interface when retrieving the table's rows, just as through the DbCommand property in design time. Of course, it still implies DbCommand property must be written.
Conclusion
These two scenarios cover the most aspects of creating the Cube in run time. However they can be easily mixed to achieve the best result. For example, you may want to create the Cube in design time to avoid creating the dataset, and the Grid in run time. Or vice-versa: create only the Grid in design time if you have the dynamic datasets.