Creating a Cube using the Cube Wizard

Overview

A Cube acts as an OLAP database to the subscribers who need to query data from an OLAP data store. A Cube is the main object of a SSAS solution where the majority of fine tuning, calculations, aggregation design, storage design, defining relationship and a lot of other configurations are developed. We will create a cube using our dimension and fact tables.

Explanation

Right-click the Cube folder and select “New Cube”, and it will invoke the Cube Wizard. In the first screen you need to select one of the methods of creating a Cube. We already have our dimensions ready, and schema is already designed to contain dimension and fact tables. So we will select the option of “Use existing tables”.

In the next screen, we need to select the tables which will be used to create measure groups. We already have a DSV which has fact tables in the schema. So we will use this as shown in the below screenshot.

In the next screen, we need to select the tables which will be used to create measure groups. We already have a DSV which has fact tables in the schema. So we will use this as shown in the below screenshot.

In this screen you need to select any existing dimensions. We have created three dimensions and we will include all of these dimensions as shown below.

In the next screen, we can select if we want to create any additional new dimensions from the tables available in the DSV. We do not want to create any more dimensions, so unselect any selected tables as shown below and move to the next screen.

Finally you need to name your cube, which is the last step of the wizard before your cube is created. Name it something appropriate like “Sales Cube” as shown below.

Now your cube should have been created and if your cube editor is open you should find different tabs to configure and design various features and aspects of the cube. If you look carefully in the below screenshot, you will find FactInternetSales and FactResellerSales measure groups. Also you will find Sales Territory and Product dimension, but Date dimension is missing. Both fact tables have multiple fields referencing the Date Key from the Date dimension.

 BIDS intelligently creates three dimensions from the Date dimension and names them to the name of the field which is referenced from the Date dimension. So you will find three compounds of Date dimension – Ship Date, Due Date and Order Date dimensions. These are known as role-playing dimensions.