icCube Walkthrough (Telecommunication Company)

In this walkthrough we are going to create our very first cubes, dimensions and measures using the icCube OLAP engine. For this purpose we are going to use an example of an imaginary telecommunication company. Let's start.

Creating a Schema

A schema in icCube is like an independent project. You start by creating a schema before you can add dimensions and measures. We will begin by creating a new schema, which is a fairly straight forward process as illustrated: select "Standard Schema", input its name and click finish.

Creating a Datasource

Having created the schema the foremost thing that should be created is a data source. A data source is a pointer to the database where your data (which needs to be analyzed) is stored. There are a number of data source options provided by icCube which include file based data, in memory data and of course the relational data. We are going to connect to the relational database in this walk through. Select "Relational Database" on the create data source screen and click next.

You will be taken to the next screen where you will be asked to enter database connectivity information such as the database driver, database host, user name, password and the database that you want to connect to.

Enter the correct information and click the test connection button given at the top of the window. If all goes well you should be able to see a confirmation screen like below. In case the screen doesn't come up and an error is shown you should check the database connectivity settings.

Adding Table(s) for Analysis

Having successfully connected to the database, now it's time to import some tables into icCube in order to setup our OLAP cube. You can import tables and/or views. It's advisable to prepare your views prior to importing them into icCube. To import the tables or views click the "Add Table" button. You can also create custom SQL when importing tables hence in fact creating a view within icCube. For this walkthrough we have prebuilt views created in the database therefore we will select only existing entities.

Once you click next based on the database that you are connected to, you should see a screen similar to the one below. On this screen you can add and remove tables from icCube. Notice that you also have the capability to filter your tables, so for large databases you would be able to quickly limit the list of tables to those of interest only. Select the tables and click finish.

Clicking on view you can see the tables that have been added. You might mistake this window as the screen where the joins between tables will be created but this is not the case. The joins will be created while defining cubes and facts.

Creating Dimensions

Dimension is a basic unit when we take into consideration OLAP analysis. Dimensions provide the context to the analysis being done. To create a new dimension in icCube click the "Create Dimension" button as shown below.

You will be presented with the initial dimension creation screen. There are a number of available strategies for creating new dimensions. There is the traditional "Multi level" method in which dimension members are arranged in a hierarchical structure. Also available are other methods such as "Parent/Child", "Path" etc. We will be focusing on "Multi level" and "Time" dimensions in the walkthrough.

So, let's start by creating a multi-level dimension. We will create a dimension for the Call Type. A multilevel dimension is comprised of hierarchies and levels. Before we can create the hierarchies or levels we must set up initial dimension properties. We need to specify a name for the dimension and select the table from which the dimension values will be populated. The "Is Fact Indexing" option which should be checked in most cases specifies that the dimension will be joined with a fact table.

After setting the basic options we can create the hierarchies. In case of this dimension we will create only one hierarchy but more then one may be added to a single dimension. Add a new hierarchy and name it "Call Type".

The last step is to create the level. Create a new level and name it "Call Type". Here you will need to select the dimension name field which is a descriptive field and the key column(s) which will be used to join to the fact table. Also, if you want you can change the sort order of the dimension values by fiddling with the order by column options. Now that our dimension is created we will next add it to a cube and test the dimension.

Creating a Cube

In simple words, a cube combines the dimensions and measures to provide meaningful analysis. We created the dimension for our cube already, in this step we will create the cube facts and a measure. Let's start by creating a new cube. The new cube screen requires you to input the cube name and the fact table in which the measures will be created. All the dimensions (which have fact indexing check) also appear in the column mapping section of the new cube screen.

Let's perform the column mapping which will effectively join the dimensions to the fact table. Each dimension appears in the column mapping screen similar to show below. There are two columns against each dimension in which you select the columns on which the join will be created. You should select the primary key column from the dimension table and the foreign key column from the fact table.

Once done with the column mapping your screen should look similar to the following screen.

Creating a Measure

A measure can be created by clicking on the measure node under the cube and then clicking the "Add Measure" button. Creating a measure has never been simpler. All you need to do is provide a measure name, the column and the aggregation function.

Deploying the Cube/Schema

The schema must be deployed and loaded by icCube before an OLAP query can be run on the cube. The following steps will take you through the deployment of the schema. Locate the "Schema Manager" icon as shown below and click on it.

Click the deploy button from the resulting screen and select to deploy on localhost:8282.

You will be shown a series of dialogs as shown below. Once the deployment is complete you will be shown the final screen that shows that the schema is now available in the monitoring application.

Loading the Schema

The final step in making the cube available is to load the schema. In this step the schema file is validated and the cube is populated from the database. Locate the "Monitoring" link as shown below.

Clicking the "Monitoring" link will take you to the monitoring application where you can a) load/unload schema b) check for errors. Locate the name of schema that you created and click the "Load Schema" or "Reload Schema" button.

Say "Yes" to the resulting screen. You may run into errors but they are covered in a later section. If all goes well you will see the schema in a loaded state.

Testing the Cube

After loading the schema navigate to the MDX editor to test the cube. The MDX editor displays the cube in the form of dimension and measures on the left and a editor pane for writing MDX expressions. Objects can be dragged from the left pane to quickly build a MDX expression and you may use the auto-completion feature (ctrl-space).

Creating an MDX expression

Type in "SELECT " in the editor and then drag the "Call Type" level into the editor and append " ON 0". This part of the expression tells the OLAP engine to select the "Call Type" dimension values on the 0-axis.

Type in a "," and drag the measure "Call Count" and append " ON 1". This part of the expression tells the OLAP engine to select the measure values on the 1-axis.

Complete the expression by typing " FROM " and dragging the cube name. You can also enter '[' after the FROM and check the auto-completion with ctrl-space.

Your expression and results should look similar to the following.

If you have come this far then you have a functional icCube environment and a cube that can be used in any MDX compliant tool such as MS-Office etc. But there is more to cube development and debugging, some of which is covered below.

No Default Hierarchy

When creating a cube if you encounter an error stating "The dimension xyz has no default hierarchy" then it means that the dimension needs to be modified a bit. The default hierarchy tells the engine which hierarchy is linked with the facts (cube data).

Go back to dimension editor and select one of the hierarchies. Check the "default" check box for the hierarchy. This must be done for all dimensions to avoid the "The dimension xyz has no default hierarchy" error.

Synchronizing Database Changes

It's likely that the design of the underlying tables may change after the tables have been imported into icCube. These changes may be addition of new columns, renaming of columns, change of data types amongst others. In this case it's necessary to synchronize the database connection so that the change is reflected in icCube. To synchronize navigate to the "Data Sources" node, select your data source and click the synchronize button as shown below.

If new columns have been added they will appear as shown below. You must check all columns that you want to be available in icCube. Also if you want to change the data type you can use the same window to edit the data types.

Adding Calculated Measure

A calculated measure is a mathematical calculation on one or more existing metric. The calculated metric can be created by clicking the "Add Calculated Measure" button as shown below.

Care must be taken to properly enclosed the measure names in square brackets otherwise an error may occur. Similarly the calculated metric name must also be enclosed in square brackets.

Excel Interactive Dashboard with icCube

Please, follow the instructions detailed here to setup a pivot table within Excel.

After following the above connectivity steps and adding pivot tables using the icCube OLAP server, we created an interactive analytical dashboard in Excel based on cube data from icCube server, the screen shot of which is below. The beauty of such analytical dashboard and reports in Excel is that none of the OLAP processing is done in Excel itself but rather the heavy lifting of OLAP processing is done by the icCube engine and the Excel dashboard is concerned only with the final results.

Both the schema and the data of this example are available for download here.