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

Views: 7.107
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

Dimensions are the descriptors of data derived from Fact. It has the qualitative character of information and one-to-many relationship with the Fact table. It is the Dimension that allows the visualization of information by various 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 contain the metrics. It has the quantitative character of descriptive information stored in the Dimensions. This is where business occurrences are stored and has a many-to-one relationship with peripheral tables (Dimension). It is a table that has only the measurements and IDs that bind to the dimensions to display the complete information regarding the data to 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 get started, open SQL Server Data Tools 2015 and from the File> New> Project menu and select the “Analysis Services Multidimensional and Datamining Project” project type.

Creating a new data source

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

In the screen that will open, click 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 the "OK" button, you will return to the previous screen, which now has the data filled.

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

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 access role.

In the tab “Data Sources”, we can define how Role users access the datasource

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

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

In the “Dimensions” tab, you can set dimension level permissions 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 on the database only. To manage Analysis Services Administrator permissions, you must open Analysis Services through 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 the "Security" tab and you're done. In this screen you can configure who the Analysis Services administrators are, and you can create / delete / change all instance cubes.

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 time table type. Select the "Generate a time table in the datasource" option to have the table physically created 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 Time dimension name and check the “Generate schema now” option 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, as shown 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 beginning Cube processing, we need to define our cube's Deploy URL. 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 are done creating Cube objects, we can process it, which is publishing the Cube to the Analysis Services server and updating 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 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, either by adding deleting or updating, this process should be performed. It has sufficient intelligence to process only the differential of structure and data that is new, but 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 the "Run" button, our cube is Processed and available for Queries.

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!