In my previous article, I explained how to create a date dimension in the SSAS multidimensional model. In this article, you will see how to create a date dimension in a tabular model. The tabular model uses an approach more similar to Excel to build the model, so most users will feel more at home with the Analytics Service (SSAS) tabular model than with the SSAS multidimensional model although there are a few limitations with the tabular model. Tabular projects and multidimensional projects have several differences as I’ll be demonstrating in this article, I will call your attention for them.

The initial solution

Exactly as in the first article in this series, I have published a Visual Studio solution with a single project and a tabular model that is already started: The tabular model with a fact table, ‘FactInternetSales’, and some dimension tables, ‘Promotion’, ‘Customer’, ‘Sales Territory’ and ‘Product’.

You will need to download the initial solution and open it in Visual Studio to follow the step-by-step guide presented in this article. You can download the solution here.

After you have opened the solution, you will need to correct two configurations before starting to create the date dimension.

You need to …

• Point the project inside the solution to the server that will be used to process the tabular model.
• Correct the connection to the data source, pointing to the server where you installed ‘AdventureworksDW’ database.

Tabular Model projects use a workspace database as a temporary area while designing the model in Visual Studio, so we don’t need to deploy the model to test it: All the data processing is done in the workspace database and only when the model is ready do we publish it to production. Although tabular projects can be developed using the workspace server that you are about to configure, multidimensional projects doesn’t have this concept and you always need to deploy the project to test it.

You can find the workspace database and server configuration in the ‘Model.bim’ file properties. Select ‘Model.bim’ file in the ‘Solution Explorer’ window and you will notice the properties in the ‘Properties’ window. The workspace database name is generated automatically, but you need to change the ‘Workspace Server’ property to point to a SSAS server installed in tabular mode.

After configuring the ‘Workspace Server’ property, you can configure the connection in the project using the following steps:

1. Click in ‘Model’ menu-> ‘Existing Connections’

   

2. In ‘Existing Connections’ window Select the existing connection (‘SqlServer WIN2SQL014 AdventureWorksDW2014’) and click in ‘Edit’ button

   

3. In ‘Edit Connection’ window, change the connection information, pointing to the server where you installed ‘AdventureWorksDW’ database

   

4. Click ‘Save’ button
5. Click ‘Close’ button in ‘Existing Connections’ window
6. Select ‘Model’ -> ‘Process’ -> ‘Process All’ on the menu

Create Order Date Dimension

Let’s start the creation of our date dimension

1. Click on ‘Model’ menu-> ‘Existing Connections’
2. Select the existing connection and click ‘Open’ button
3. Insert the credential for the database

Notice that is very easy to make a mistake here. If you use ‘Model’ menu -> ‘Import from data source’ you will end up with several connections to the same data source. Since the connection already exists and you would like only to select additional tables, you need to use ‘Existing Connections’ menu item.

In the first step of ‘Table Import Wizard’, keep ‘Select from a list of tables’ selected and click ‘Next’ button In ‘Tables and Views’ grid, select ‘DimDate’ table In ‘Friendly Name’, change the name to ‘Date’

Click the button ‘Preview & Filter’ In ‘Preview Selected Table’ step, uncheck the following Fields: ‘SpanishDayNameofWeek’, ‘FrenchDayNameofWeek’, ‘SpanishMonthName’, ‘FrenchMonthName’, ‘DayNumberOfYear’

Click the ‘Ok’ button Click the ‘Finish’ button in ‘Table Import Wizard’ Click the ‘Close’ button in the ‘Importing’ step of ‘Table Import’ wizard

At this point, you can see the ‘Date’ table in the model, but it is disconnected from the other tables. We need to create a relation between the fact table, ‘Internet Sales’, and the ‘Date’ table.

Drag the ‘OrderDateKey’ field in the ‘Internet Sales’ table to the ‘DateKey’ in the ‘Date’ table Right-Click the ‘Date’ table, select ‘Rename’ in the context menu and rename the table as ‘Order Date’.

Create the calendar hierarchies

The date dimension allows us to aggregate the fact information by month, year, semester and many other options. In our example, the fact information is the amount of sales, so we can see the total of sales by month, semesters and many other options.

These aggregation options are hierarchical. We can aggregate by year and month, for example, creating a hierarchy.

When using Excel to access the cube, the users can, of course, build their own hierarchies by dragging individual attributes to a pivot table, one by one. Besides being a boring, repetitive task for the user, this can also lead to errors if the user drags the attributes in an order that makes no sense.

Our objective while designing the dimension is to make things easier for the user by creating useful pre-defined hierarchies.

Firstly, we must decide what hierarchies will we create and how to configure them.

You will notice that, for the date dimension, a single hierarchy isn’t enough. For example, we cannot mix the fiscal year with calendar year. We also cannot mix month with weeks. We will need several hierarchies in our date dimension.

Firstly, let’s rename the fields to a friendlier name:

1. Double-click the ‘CalendarYear’ field in the ‘Order Date’ table and rename it to ‘Year’
2. Double-click the ‘EnglishMonthName’ field in the ‘Order Date’ table and rename it to ‘Month’
3. Double-click the ‘FullDateAlternateKey’ field in the ‘Order Date’ table and rename it to ‘Day’

   

Now let’s create our first hierarchy:

Right-click ‘Year’ field in ‘Order Date’ table and select ‘Create Hiearchy’ menu item

Type ‘Simple Calendar’ as the name of the hierarchy Drag ‘Month’ field in ‘Order Date’ table to the hierarchy, below ‘Year’ Drag ‘Day’ field in ‘Order Date’ table to the hierarchy, below ‘Month’

Right-click the ‘Year’ field in the ‘Order Date’ table and click the ‘Hide from client tools’ menu item Right-click the ‘Day’ field in the ‘Order Date’ table and click the ‘Hide from client tools’ menu item

The ‘Year’ and ‘Day’ fields only need to be available inside the hierarchy:

Year: This is the first attribute in the hierarchy, so we don’t need this attribute to be visible. If we drag the ‘Calendar Year’attribute to a pivot table or drag the hierarchy, we will see the same information, so we can hide the ‘Calendar Year’attribute.

Day: The days are the last level, the leaf level of our dimension. It ‘s not usual to aggregate the sales by days; it results in too many detail lines.

On the other hand, the ‘Month’ field will be very useful outside the hierarchy, so we can aggregate the sales by months across several years.

Let’s test the new hierarchy in Excel:

1. Click the ‘Excel’ button on the toolbar
2. 
3. Click ‘Ok’ button in ‘Analyze in Excel’ window
4. In ‘Excel’, if the alert bar appears, click ‘Enable’ button on the alert bar
5. If not already there, click inside the pivot table area in the spreadsheet, so the ‘Pivot Table Fields’ area will appear at the right side of the screen
6. In ‘Pivot Table Fields’ area, ‘Internet Sales’ fact table, select ‘Amount’ and ‘Cost’ fields
7. In ‘Pivot Table Fields’ area, ‘Order Date’ dimension, select ‘Simple Calendar’ hierarchy

   

8. Open 2011 year and notice the months out of order

   

9. In the ‘Pivot Table Fields’ area, uncheck the ‘Simple Calendar’ hierarchy under the ‘Order Date’ dimension
10. In the ‘Pivot Table Fields’ area, open the ‘More Fields’ folder under the ‘Order Date’ dimension
11. In the Pivot Table Fields’ area, check the ‘Month’ attribute under the ‘More Fields’ folder

Since we let ‘Month’ field visible, we can aggregate information by month, across several years. On the other hand, we also see the months out of order.

The server is ordering the rows by the name of the month and we need the order by the number of the month. Let’s correct this problem and go ahead creating the hierarchies:

1. Switch back to Visual Studio window
2. Select ‘Month’ field in ‘Order Date’ table
3. In properties window, change ‘Sort by Column’ property to ‘MonthNumberOfYear’ field

   

4. Right click ‘MonthNumberOfYear’ field and select ‘Hide From Client Tools’ menu item

   

5. Switch to Excel window
6. On the toolbar, click ‘Refresh’ button -> ‘Refresh All’

   

Now the month are ordered correctly.

Tabular x Multidimensional

At this point you are probably noticing several differences between these steps and what we did in the article about the multidimensional model.

• You don’t have configure attribute relationships, the tabular model get in charge of this.
• You can use the same attribute in a hierarchy and outside the hierarchy, as we just did with ‘Month’ field.
• There isn’t a ‘NameColumn’ property as in the multidimensional model, so the options to configure the fields are less flexible, but this isn’t exactly a problem, we just need to select always the field that has the name to use and configure the ‘Sort by Column’ property as we just did.

By the way, since we can call ‘Order Date’ as table or dimension, we can call ‘Month’ as field or attribute.

Discretization of the Days

Each month has around thirty days. If we display them all, it means that there will be too many detail lines in the pivot table, making the visualization much less useful. It would be better to find a way to group the days below the month level. How can we create clusters among the thirty days in a month?

We can aggregate the days using groups of ten days. It will result in two groups of ten days and one final varying group for each month. We will have fewer detail lines and the user will be able to identify which part of the month get the best sales result. For example, it would be easy to identify if the most amount of sales happens in the beginning or in the end of the month.

Let ‘s call these groups as ‘Decans’ (yes, I found this name in astrology). We need to create a calculated member named ‘Decans’ to classify each record in 1st, 2nd or 3rd decan.

This kind of grouping of a level that doesn’t have a natural grouping is called discretization. We are discretizing the day level in the hierarchy.

Let’s start the steps do create the ‘Decans ‘:

1. Switch the visualization to grid by clicking the ‘Grid’ button at lower right corner of ‘Model.bim’ window

   

Select ‘Order Date’ tab

After the last column, double click ‘Add Column’ title Type ‘Decan’ as the name of the new column and press enter

Click the first empty cell in ‘Decan’ column Type the following expression:

This expression uses the DAX syntax to decide if the row is from the first, second or third ‘Decan’. DAX is the syntax used for the tabular model to create expressions for calculated fields, measures or to do queries.

Switch the visualization to diagram view by clicking ‘Diagram’ button at lower right corner of ‘Model.bim’ window

Drag the ‘Decan’ field in ‘Order Date’ table to ‘Simple Calendar’ hierarchy, between ‘Month’ and ‘Day’ fields

Right-click ‘DayNumberOfMonth’ field and click ‘Hide from client tools’menu item

We don’t need ‘DayNumberOfMonth’ field anymore, so we can hide it. It was included exactly to make this formula easier.

Probably you have noticed the ‘Decan’ field in a strange position, under the the ‘Simple Calendar’ hierarchy. If you close the ‘Model.bim’ window and open it again, double-clicking ‘Model.bim’ item in ‘Solution Explorer’ window and returning to diagram view, ‘Decan’ field will be in the right position, above the ‘Simple Calendar’ hierarchy.

Let’s test the ‘Decan’ attribute:

1. Switch to the Excel window
2. On the toolbar, click the ‘Refresh’ button -> ‘Refresh All’
3. In the ‘Pivot Table Fields’ area, under the ‘More Fields’ folder under the ‘Order Date’ dimension, uncheck the ‘Month’ field
4. Check the ‘Simple Calendar’ field under the ‘Order Date’ dimension in the ‘Pivot Table Fields’ area
5. In the pivot table, expand ‘2011’ year
6. Under the ‘2011’ year, expand the ‘March’ month

   

Creating the Full Calendar hierarchy

We haven’t used the information about semester and quarter yet. Now it’s time to create a bigger hierarchy using these fields.

Both fields, ‘CalendarQuarter’ and ‘CalendarSemester’ are numeric. If we use these fields in our hierarchy, we will have two levels with numeric descriptions. This will be confusing, as in the image below.

We can create calculated fields to name these values and avoid this problem. For example, we can name the semesters as ‘1st Semester’ and ‘2nd Semester’. We need two calculated fields: ‘Semester’ and ‘Quarter’.

In DAX, we have the function SWITCH that we can use to “translate” the numeric value to a string name of the semesters and quarters.

DAX formula for the ‘Semester’ field:

DAX formula for the ‘Quarter’ field:

Let’s create both fields:

1. Click the ‘Grid’ button in bottom-right corner of ‘Model.bim’ window to switch the visualization to grid.
2. Select ‘Order Date’ tab
3. After the last column, double click ‘Add Column’ title
4. Type ‘Semester’ as the name of the new column
5. Select the first empty cell in the new ‘Semester’ column
6. Type the DAX formula for the ‘Semester’ field

   

7. After the last column, double click ‘Add Column’ title
8. Type ‘Quarter’ as the name of the new column
9. Type the DAX formula for the ‘Quarter’ field

   

   

10. Switch to the diagram view using the ‘Diagram’ button at the bottom-right corner of the ‘Model.bim’ window
11. Right-click the ‘Year’ field in the ‘Order Date’ table and click the ‘Create new hierarchy’ menu item
12. Type ‘Full Calendar’ as the name of the new hierarchy
13. Drag the ‘Semester’ field from the ‘Order Date’ table to the ‘Full Calendar’ hierarchy, below the ‘Year’ field.
14. Drag the ‘Quarter’ field from ‘Order Date’ table to ‘Full Calendar’ hierarchy, below the ‘Semester’ field
15. Drag the ‘Month’ field from ‘Order Date’ table to ‘Full Calendar’ hierarchy, below the ‘Quarter’ field
16. Drag the ‘Decan’ field from ‘Order Date’ table to ‘Full Calendar’ hierarchy, below ‘Month’ field
17. Drag the ‘Day’ field from ‘Order Date’ table to ‘Full Calendar’ hierarchy, below the ‘Decan’ field
18. Right-Click the ‘CalendarSemester’ field in ‘Order Date’ table and click the ‘Hide from client tools’ menu item
19. Right-Click the ‘CalendarQuarter’ field in ‘Order Date’ table and click the ‘Hide from client tools’ menu item

    

Semester Calendar

Several fields like ‘Semester’, ‘Quarter’, ‘Month’ and ‘Decan’ for example, are very useful for analysis across the years. It’s useful to know which semester, quarter, month or decan has higher or lower sales amount, independent of the year.

We can create one hierarchy for these fields to make thing even easier for the user, let’s call this hierarchy ‘Semester Calendar’.

Follow these steps:

1. Right-click the ‘Semester’ field in the ‘Order Date’ table and click the ‘Create hierarchy’ menu item.

   

2. Type ‘Semester Calendar’ as the name of the new hierarchy
3. Drag ‘Quarter’ field in ‘Order Date’ table to ‘Semester Calendar’ hierarchy, below ‘Semester’ field
4. Drag ‘Month’ field in ‘Order Date’ table to ‘Semester Calendar’ hierarchy, below ‘Quarter’ field
5. Drag ‘Decan’ field in ‘Order Date’ table to ‘Semester Calendar’ hierarchy, below ‘Month’ field

   

6. Right click ‘Semester’ field in ‘Order Date’ table and click ‘Hide from client tools’ menu item
7. Right-click ‘Quarter’ field in ‘Order Date’ table and click ‘Hide from client tools’ menu item
8. Right-click ‘Month’ field in ‘Order Date’ table and click ‘Hide from client tools’ menu item

Since we already have the hierarchy, we don’t need these fields to be visible outside the hierarchy anymore. The exception is the ‘Decan’ field: Inside the hierarchy it’s always attached to ‘Month’ field, so if we would like to analyze the amount of sales by decan independent of the month we will need to keep this field visible.

Testing in Excel

1. Switch to Excel window
2. On the toolbar, click ‘Refresh’ button -> ‘Refresh All’
3. On ‘Pivot Table Fields’, inside ‘Order Date’ dimension, notice the three hierarchies we already created

   

4. Check the ‘Full Calendar’ hierarchy, uncheck all others hierarchies and notice the result in the pivot table

   

5. Check the ‘Semester Calendar’ hierarchy, uncheck all others hierarchies and notice the result in the pivot table

   

Week Hierarchy

The ‘ Order Date ‘ table has a field named ‘ Week Number of Year ‘. Some companies do analysis with the week, so we can create hierarchies using Week. On the other hand, we shouldn ‘t include Semester, Quarter and Month in hierarchies with week, because about ten weeks in the year are part of two different months.

The week hierarchy will be, basically, the ‘Year’, ‘WeekNumberofYear’ and ‘EnglishDayNameOfWeek’ attributes.

Let’s create this new hierarchy using the following steps:

1. Right-click ‘WeekNumberofYear’ field in ‘Order Date’ table and rename it to ‘Week’
2. Right-click ‘EnglishDayNameOfWeek’ field in ‘Order Date’ table and rename it to ‘WeekDay’
3. Right-click ‘Year’ field in ‘Order Date’ table and click ‘Create Hiearchy’ menu item

   

4. Type ‘Week Calendar’ as the name of the hierarchy
5. Drag the ‘Week’ field in ‘Order Date’ table to the ‘Week Calendar’ hierarchy, below ‘Year’ field
6. Drag the ‘WeekDay’ field in ‘Order Date’ table to the ‘Week Calendar’ hierarchy, below ‘Week’ field

   

Test the hierarchy in Excel:

1. Switch to Excel window
2. Click ‘Refresh’ button -> ‘Refresh All’ in the toolbar
3. In ‘Pivot Table Fields’ area, expand ‘Order Date’ dimension and notice the new hierarchy we just created, ‘Week Calendar’
4. Uncheck all hierarchies in ‘Order Date’ dimension and check ‘Week Calendar’ hierarchy

   

Notice the hierarchy in the pivot table

You will notice the ‘Weekday’ field completely out of order. It’s been ordered alphabetically by the weekday name. We already had this problem before with the ‘Month’ field and the solution is the same: We need to configure ‘Sort by Column’ property of ‘WeekDay’ field.

1. Switch back to Visual Studio window, if not already there
2. Select ‘WeekDay’ field in ‘Order Date’ table
3. In ‘Properties’ window, change the value of the property ‘Sort by Column’ to ‘DayNumberofWeek’

   

4. Right-click ‘DayNumberofWeek’ field in ‘Order Date’ table and click ‘Hide from client tools’ menu item
5. Switch to Excel window
6. Click ‘Refresh’ button -> ‘Refresh All’
7. In pivot table, expand ‘2011’ year
8. In the pivot table, expand any week below ‘2011’ year
9. Now the weekdays are in the correct order

   

Fiscal Hierarchies

The next step is to create hierarchies for the fiscal calendar. The ‘Order Date’ has several fields that allow us to create fiscal calendar hierarchies. We need two hierarchies: ‘Simple Fiscal Calendar’ and ‘Full Fiscal Calendar’, following the same pattern that we created with ‘Simple Calendar’ and ‘Full Calendar’. We don ‘t need a ‘Fiscal Semester Calendar’ hierarchy, because would be the same as ‘Semester Calendar’ hierarchy we already have.

The creation of these hierarchies follows the same steps we used before, it’s the same as the calendar hierarchies, so let’s summarize the steps:

1. We will need ‘FiscalYear’ field in ‘Order Date’ table, both hierarchies starts with this field
2. We will need two new named calculations, ‘Fiscal Semester Name’ and ‘Fiscal Quarter Name’. The DAX instruction will be the same we used for ‘Semester’ and ‘Quarter’, only changing the fields.

   

   

   

3. We will use the same ‘Month’, ‘Decan’ and ‘Day’ fields
4. We can hide ‘FiscalSemester’, ‘FiscalQuarter’, ‘Fiscal Semester Name’, ‘Fiscal Quarter Name’ and ‘FiscalYear’ fields

   

   

Using Perspectives to control Hierarchy visualization

Perspectives are like views for an entire cube. A perspective determines which parts of a cube a user can view when using the perspective.

For some users the excess of hierarchies can be confusing. We can use perspectives to create different views of the cube, simplifying the user work.

Perspectives are available in Business Intelligence edition and Enterprise Edition, but not in Standard Edition.

In our example, we have plenty of possibilities for creating perspectives of the cube. Let’s try the following combination:

Simple Perspective: This perspective can have the ‘Simple Calendar’ hierarchy and ‘Semester Calendar’ hierarchy

Extended Perspective: The same as the simple perspective plus the ‘Full Calendar’ hierarchy

Fiscal Perspective: This perspective will contain the ‘Simple Fiscal Calendar’ and ‘Full Fiscal Calendar’hierarchies, ‘Semester Calendar’ and ‘Week Calendar’hierarchies

Let’s create the perspectives:

1. Click ‘Model’ menu -> ‘Perspectives’ -> ‘Create and Manage’

   

2. In ‘Perspectives’ window, click ‘New Perspective’ button

   

3. Type ‘Simple Perspective’ as the name of the new perspective
4. Check the checkbox in the ‘Tables’ line, below ‘Simple Perspective’ column. This will check all the checkboxes
5. Click the ‘-‘ button besides the ‘Customers’ table, this will collapse the table names
6. Click the ‘+’ button besides the ‘Order Date’ table, this will expand the table fields and hierarchies
7. Uncheck all hierarchies other than ‘Simple Calendar’ and ‘Semester Calendar’

   

8. Click ‘New Perspective’ button
9. Type ‘Extended Perspective’ as the name of the new perspective
10. Check the checkbox in the ‘Tables’ line, below ‘Extended Perspective’ column. This will check all the checkboxes
11. Click the ‘-‘ button besides the ‘Customers’ table, this will collapse the table names
12. Click the ‘+’ button besides the ‘Order Date’ table, this will expand the table fields and hierarchies
13. Uncheck all hierarchies other than ‘Simple Calendar’, ‘Semester Calendar’ and ‘Full Calendar’
14. Click ‘New Perspective’ button
15. Type ‘Fiscal Perspective’ as the name of the new perspective
16. Check the checkbox in the ‘Tables’ line, below ‘Fiscal Perspective’ column. This will check all the checkboxes
17. Click the ‘-‘ button besides the ‘Customers’ table, this will collapse the table names
18. Click the ‘+’ button besides the ‘Order Date’ table, this will expand the table fields and hierarchies
19. Uncheck all hierarchies other than ‘Simple Fiscal Calendar’, ‘Semester Calendar’, ‘Full Fiscal Calendar’ and ‘Week Calendar’

    

Test the Perspectives

You will notice in the toolbar a combobox named ‘Select Perspective’. If you choose a perspective in the combobox, the model will display only the elements that are included in the perspective.

When you click the ‘Excel’ button in the toolbar and open the ‘Analyze in Excel’ window, you have the option to choose the perspective that will be opened in Excel, turning available only the items included in the perspective.

The Date Table

Our last, but not least, task is to specify the table as ‘Date Table’.

There are some analysis we can do using DAX queries that needs the dimension type specified. For example, there is a function named ‘ParallelPeriod’ that allow us to analyze periods across the time, for example, the same month across several years.

For this function, ‘ParallelPeriod’, and several others can work properly, there must be a table marked as being of type ‘Date’.

It’s easy to do this: We only need to use ‘Table’ menu -> ‘Date’ menu -> ‘Mark as Date Table’ menu item

In the ‘Mark as Date Table’ window, we only need to keep the ‘Day’ field selected, because it’s the date type field with unique values.

Conclusion

Implementing a date dimension in the SSAS tabular mode is much simpler than in the SSAS multidimensional mode, but it has certain limitations as you will have noticed in this article.

The power pivot add-on in Excel is also useful for building tabular models. It’s very similar to Visual Studio and useful for self-service BI.