icCube Walkthrough (Sales)

In this walkthrough, we are going to create our very first cubes, dimensions and measures using the icCube engine. For this purpose we are going to use an example of Sales data. This schema is available on icCube when you download icCube's Community version. 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 straightforward process, simply click on the "+" button.

 

 

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, relational data, among many others. For sake of simplicity, we are going to add flat files and in memory files in this walkthrough. Select your according data source and click next.

 

Depending on the data source type, you will need to either select the directory path, or, 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 the Connection' button found either as a right click on the list of your sources or if you click on one of them, this button is found on the top right menu. If all goes well, you should be able to see a confirmation screen as the image shown 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 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.

 

 

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. Note that you also have a search bar allowing you to quickly limit the list of tables to those of interest only in case of large databases. Select the tables and click finish.

 

 

Clicking on 'Data Tables' you can see the tables that have been added.

 

ETL (Transformations)

An ETL layer, called "Transformations", allows for creating new data tables that are the result of transforming/combining existing data tables. Common transformations are available such as Union, Join, Columns to Rows, etc, as well as more advanced as the Java code allowing to define your own transformation logic using Java language.

 

Creating Dimensions

Dimension is a basic unit when we take into consideration multi-dimensional analysis. Dimensions provide the context to the analysis being done, in other words, the business concepts that define all objects of your data, e.g. Time with its Years, Months and Days, or Continents with its Countries and Cities for example. 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 Levels" 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 Levels" and "Time" dimensions in the walkthrough.

 

 

So, let's start by creating a multi-level dimension. We will create a dimension for the customers' geography. A multilevel dimension is composed of hierarchies and levels. You can use the wizard that analyzes the table and proposes a hierarchical structure, but for this walkthrough, we'll show a dimension creation from scratch. 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.

 

 

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

 

The last step is to create the level. Create a new level and name it "Region". 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. Add the other levels of this hierarchical dimension: Country and City. 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 one 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. A magic wand is available that proposes a mapping for the links.

 

 

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 and loading the Schema

The schema must be deployed and loaded by icCube before a MDX query can be run on the cube. To deploy and load your schema, locate the Deployment tab.

 

 

Click the "Deploy & Load" button, and once the deployment is complete you will be shown the final screen that shows that the schema is now available in the Admin application.

 

 

You can as well deploy and load the schema separately. He's how to do it.

First Deploy the schema. Once you deploy, 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 "Admin" application as shown below.

 

 

Clicking on the "Admin" application will take you to the monitoring application where you can a) load/unload schema b) check for errors. Find your schema under Schemas > Unloaded, use the search bar if needed.

 

 

Now, click right on the schema and "Load" or click on the schema name and find the "Load" button on the top right menu.

 

Press "Ok" 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.

Your schema is now ready to be used!

Note that you can as well reload a schema that was previously loaded. Go to Schemas > Loaded and click on "Full Reload" of the given schema.

You can either start creating dashboards on the Reporting application (walkthrough), or use the MDX console to test your cube and queries, as detailed below.

 

Testing the Cube

After loading the schema, navigate to the MDX console to test the cube. The MDX console displays the cube in the form of dimensions and measures on the left and an editor pane for writing and testing your MDX expressions. Objects can be dragged from the left pane to quickly build an MDX expression and you may use the auto-completion feature (Crtl-Space).

 

 

Creating an MDX expression

Type in "SELECT" in the editor and then drag the "Amount" measure into the editor and append "ON 0". This part of the expression tells the icCube engine to select the "Amount" measure on the 0-axis.

 

Type in a "," and drag the level "Region" and append "ON 1". This part of the expression tells the icCube engine to select the level "Region" 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.

 

Run the query and your expression and results should look similar to the following.

 

 

Note the you have an MDX patented debugger on icCube (bug icon). It is quite helpful when queries or formulas are starting to get complex!

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. 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 X 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 the Semantic Layer, click on the corresponding dimension X and select the hierarchy which you wish to set as the default one. Check the "Default" check box for the hierarchy. This must be done for all dimensions to avoid this "The dimension X 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 Measures

A calculated measure is a mathematical calculation on one or more existing metrics. Create your calculated metric on the Schema's Script preceded by "CREATE MEASURE" as shown below.

 

 

It is highly advised to test your calculated measure on the MDX console before putting it in production on the schema. Simply go to the MDX console and use "WITH MEASURE" followed by your new calculation definition to test it is working as expected.

 

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 server, we created an interactive analytical dashboard in Excel based on cube data from icCube server, as shown on the screen shot below. The beauty of such analytical dashboard and reports in Excel is that none of the MDX processing is done in Excel itself but rather the heavy lifting of MDX 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.

Next chapter: Walkthrough Real-Time.