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

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.

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:

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:

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.

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)

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.