Click on the banner to learn about and purchase my database training on Azure

Analysis Services - Creating Your First Multidimensional Cube in the Star Model (Star Schema)

Views: 9.046
Reading Time: 12 minutes

Hello everybody!
How are you ?

In this post I will demonstrate how to create your first multidimensional cube in the star schema. This post ended up being done without the idealization that I usually do, because I was writing another blog post and I needed to create a simple Analysis Services cube for the demonstration and I understood it as an opportunity to talk a bit about BI too, subject that I work in my day by day and really, I hardly write about. So I decided to create this post demonstrating the step-by-step for creating this cube.

Some BI Concepts

Multidimensional modeling

Multidimensional modeling, or dimensional modeling as it is sometimes called, is the database modeling technique for assisting Data Warehouse queries from a variety of perspectives. Multidimensional view enables the most intuitive use for analytical processing by the Online Analytical Processing (OLAP) tools.

Every dimensional modeling has two essential elements: the Facts tables and the Dimensions tables. Both are mandatory and have complementary characteristics within a Data Warehouse.

Online Analytical Processing (OLAP)

OLAP has a set of techniques for handling the data contained in the multidimensional view of the Data Warehouse. OLAP tools can be of different types: MOLAP, ROLAP, or HOLAP.

Multidimensional OLAP (MOLAP) is the type of tool that uses multidimensional database structure. Relational OLAP (ROLAP) uses the relational data architecture, where the database has the traditional structure. Already the hybrid OLAP (HOLAP) is the junction of the previous two, using the best aspects and features of each of the two types.

Dimensions

The Dimensions are the descriptors of the data from Fato. It has the qualitative character of the information and the “one to many” relationship with the Fact table. It is the Dimension that allows the visualization of information from different aspects and perspectives. Ex: Customer registration, payment methods registration, etc.

Sizes:

Measures are numerical values ​​that represent Fact and the performance of a business indicator associated with dimensions. Ex: Sales Amount, Sales Amount, Average Ticket, etc.

Fatos

The Facts contains the metrics. It has the quantitative character of the descriptive information stored in the Dimensions. It is where business events are stored and has a “many to one” relationship with the peripheral tables (Dimension). It is a table that has only the measurements and the ID's that are linked to the dimensions to display the complete information regarding the data that will be viewed.

Multidimensional modeling models

Dimensional modeling has two models: the star schema model and the snow flake model. Each with different applicability depending on the specificity of the problem.

The dimensions of the star model are denormalized, unlike snow flake, which partially has normalization. The relational structure differs from the multidimensional structure mainly due to normalization, little redundancy and the frequency of supported updates. The multidimensional structure typically has table denormalization, high redundancy, and supports much shorter data refresh times than a conventional relational structure.

Star model:

Snowflake Model:

Generating Dimensions and Facts (ETL)

In this part of cube creation, I will generate the dimensions and facts with random data. Of course this data is for demonstration purposes only. In a real scenario, random data should not be used in BI analyzes to create multidimensional cubes, as the analysis would not have a real value.

For the random generation of dates I used the function fncRand (), available in post SQL Server - Msg 443 Invalid use of a side-effecting operator 'rand' within a function.

Viewing the random mass of tests:

Creating the cube in Visual Studio (Data Tools)

Formerly known as Business Intelligence Development Studio (BIDS) or Visual Studio Shell, Microsoft Data Tools has been incorporated into Visual Studio as of the 2015 release and is now part of a single product and is now just a Visual Studio plugin that can be downloaded accessing this link.

To start, open SQL Server Data Tools 2015 and from the File> New> Project menu and select the project type “Analysis Services Multidimensional and Datamining Project”.

Creating a new data source

View content
Right-click on the “Data Sources” folder and select the “New Data Source…” option

On the screen that will open, click on the “New…” button to create a new data source.

Enter the name of the server \ instance, your login user and the database where the data is located.

Note that after clicking on the “OK” button, you will be returned to the previous screen, which now has the data filled in.

In this screen you can define how the Analysis Services cube will connect to the database. I usually use an AD user with permissions on the database and Analysis Services or a service account.

Finally, define the name of the newly created data source.

Creating a new Data Source View

View content
Now let's create a new datasource view to add the physical tables to our Cube structure.

Right-click on the “Data Source Views” folder and select the “New Data Source View…” option.

Select the data source from the list of previously created Datasources.

In this window, define how logical relationships will be created.

Select the dimensions and facts that will make up this cube and click the marked button to add the physical tables in the Cube.

This screen should look like this after selecting the objects

Enter the name of the Data Source View you want to complete creation.

After Data Source View is created, you can create logical relationships by clicking the Foreign Key column in the Fact and dragging to the Primary Key column in the dimension. After completing the action, if there is no Foreign Key created in the database, you will see this alert message:

You can create the “Yes” button to complete the creation of the logical relationship.

After you create this relationship of all dimensions, your datasource view will look like this:

Relationship between Fact and Dimension

Creating a new cube

View content

Right-click on the “Cubes” folder and select the “New Cube ..” option

In this screen you can define how your cube (s) will be created. As I have already created the Fact table, I will choose the first option.

You should now select the Fact table to form the Measure Group of your cube.

In this screen you can use the F2 key to rename your Cube measurements from Fact.

Here you can choose which dimensions you want to import into Cube and rename these dimensions.

Finally, you can define the name of the cube.

After the cube is created, this will be the new structure of your cube:

Managing Permissions

View content

Right-click on the “Roles” folder and select the “New Role…” option

In the screen that opened, you can set the permissions of the created Role (The role name can only be changed in the Properties screen)

In the "Membership" tab you can define who are the users who are in this role

In the “Data Sources” tab, we can define the way of access to the datasource by Role users

In this “Cubes” tab, you can define whether Role members will have access to use the Cube in Analysis Services

In the tab “Cell Data” we can define the permissions at the row (cell) level, using MDX expressions

In the “Dimensions” tab, you can define permissions at the dimension level by Role users.

In the “Dimension Data” tab, we can define which dimension lines the members of this role can view, using MDX expressions

After you have made all permission settings, you can change your role name using the F2 key or the rename object option.

You can browse the dimensions and add more fields from your physical table to your dimensions so that you can view them in the Cube.

Remember that this permission is only in the database. To manage Analysis Services Administrator permissions, you must open Analysis Services from SQL Server Management Studio, select the “Analysis Services” option

Now right-click on the Analysis Services icon and select the “Properties” option

In the Analysis Services properties screen, click on the “Security” tab and you're done. In this screen you can configure who are the administrators of Analysis Services, being able to create / delete / change all the cubes of the instance.

Creating the Time Dimension

View content
To create the time dimension, just right-click on the “Dimensions” folder and select the “New Dimensions ..” option

Select the type of time table. Select the option “Generate a time table in the datasource”, so that the table is created physically in the datasource.

In this screen, you can set the date calendar period and the granularity level of the dates.

Here you can set whether your calendar will be the default template or custom, with a different start period than the default.

Finally, select the name of the Time dimension and check the option “Generate schema now” to create the table in the database

Select the datasource view you want to use to create the Time dimension table in the database.

Leave the default options selected, according to the print below, and click the “Next” button

In this screen you can view and define the naming conventions of the table that will be created.

Summary of Changes to Be Made

Table created in database

Remember to edit the Datasource view and add the relationship between the date in the Fact table and the primary key in the Time table.

The relationship should look like this

Note: If the date of your Fact table is DATETIME with time, you will need to remove the time from the date column of your Fact table or create a new column without the time because the Time dimension has no time, as shown below, and this will cause JOIN to fail correctly (Will only return DATETIME column records with time zero)

Processing the cube and publishing to Analysis Services

View content
Before starting processing the Cube, we need to define the Deploy URL of our Cube. To do this, right click on the solution and select the option “Properties…”

Navigate to the “Deployment” tab and correctly fill in the name of your Analysis Services instance in the Target> Server field.

Now that we have finished creating the Cube objects, we can process it, which is to publish the Cube to the Analysis Services server and update the data. To do this, right click on the Cube and select the option “Process…”

After the processing request, you will see the alert message below. You can click on the “Yes” button.

Screen indicating that Deploy succeeded

In this screen you can choose the processing mode and process database data for Analysis Services

Processing Types:

  • Process Default: Performs the least effort possible (with the least amount of tasks) to process the structure and data. The server converts this option to the one that best suits your environment right now. Applicable: All Objects
  • Process full: Processes all structure and data, deleting and recreating objects. This means that processing discards everything that exists and re-creates the analytical structure and then processes the data for this structure. If any new attributes are added to the dimension, a process full should roll. Applicable: All Objects
  • Process Update: When any attribute changes occur in the dimension, whether adding, deleting or updating, this process must be performed. It has enough intelligence to process only the differential of the structure and data that are new, but it is slower (to apply “intelligence”). Applicable: Dimension
  • Process Data: Discards all stored data and processes all data again, ignoring any changes in structure and indexes. The focus is only on the data. Applicable: Dimension, Cube, Measure and Partition
  • Process Add (Incremental): Processes only new data, ignoring existing data as well as any new changes to structure or indexes. Applicable: Dimension and Partition

After clicking on the “Run” button, our cube is processed and available for consultation.

Remember: Any changes you make to the Cube will need to be reprocessed. If you do a Process Full on one dimension, you will need to perform a Process Full on the Cube as well.

That's it folks!
Hug!