In this article by Derek Wilson, the author of the book Tabular Modeling with SQL Server 2016 Analysis Services Cookbook, you will learn the following recipes:

• Opening an existing model
• Importing data
• Modifying model relationships
• Modifying model measures
• Modifying model columns
• Modifying model hierarchies
• Creating a calculated table
• Creating key performance indicators (KPIs)
• Modifying key performance indicators (KPIs)
• Deploying a modified model

(For more resources related to this topic, see here.)

Once the new data is loaded into the model, we will modify various pieces of the model, including adding a new Key Performance Indicator.

Next, we will perform calculations to see how to create and modify measures and columns.

Opening an existing model

We will open the model. To make modifications to your deployed models, we will need to open the model in the Visual Studio designer.

How to do it…

1. Open your solution, by navigating to File | Open | Project/Solution.

   

2. Then select the folder and solution Chapter3_Model and select Open.
3. Your solution is now open and ready for modification.

How it works…

Visual Studio stores the model as a project inside of a solution. In Chapter 3 we created a new project and saved it as Chapter3_Model. To make modifications to the model we open it in Visual Studio.

Importing data

The crash data has many columns that store the data in codes. In order to make this data useful for reporting, we need to add description columns. In this section, we will create four code tables by importing data into a SQL Server database. Then, we will add the tables to your existing model.

Getting ready

In the database on your SQL Server, run the following scripts to create the four tables and populate them with the reference data:

1. Create the Major Cause of Accident Reference Data table:

   CREATE TABLE [dbo].[MAJCSE_T](
   
     [MAJCSE] [int] NULL,
   
     [MAJOR_CAUSE] [varchar](50) NULL
   
   ) ON [PRIMARY]

2. Then, populate the table with data:

   INSERT INTO MAJCSE_T
   
   VALUES
   
   (20, 'Overall/rollover'),
   
   (21, 'Jackknife'),
   
   (31, 'Animal'),
   
   (32, 'Non-motorist'),
   
   (33, 'Vehicle in Traffic'),
   
   (35, 'Parked motor vehicle'),
   
   (37, 'Railway vehicle'),
   
   (40, 'Collision with bridge'),
   
   (41, 'Collision with bridge pier'),
   
   (43, 'Collision with curb'),
   
   (44, 'Collision with ditch'),
   
   (47, 'Collision culvert'),
   
   (48, 'Collision Guardrail - face'),
   
   (50, 'Collision traffic barrier'),
   
   (53, 'impact with Attenuator'),
   
   (54, 'Collision with utility pole'),
   
   (55, 'Collision with traffic sign'),
   
   (59, 'Collision with mailbox'),
   
   (60, 'Collision with Tree'),
   
   (70, 'Fire'),
   
   (71, 'Immersion'),
   
   (72, 'Hit and Run'),
   
   (99, 'Unknown')

3. Create the table to store the lighting conditions at the time of the crash:

   CREATE TABLE [dbo].[LIGHT_T](
   
     [LIGHT] [int] NULL,
   
     [LIGHT_CONDITION] [varchar](30) NULL
   
   ) ON [PRIMARY]

4. Now, populate the data that shows the descriptions for the codes:

   INSERT INTO LIGHT_T
   
   VALUES
   
   (1, 'Daylight'),
   
   (2, 'Dusk'),
   
   (3, 'Dawn'),
   
   (4, 'Dark, roadway lighted'),
   
   (5, 'Dark, roadway not lighted'),
   
   (6, 'Dark, unknown lighting'),
   
   (9, 'Unknown')

5. Create the table to store the road conditions:

   CREATE TABLE [dbo].[CSRFCND_T](
   
     [CSRFCND] [int] NULL,
   
     [SURFACE_CONDITION] [varchar](50) NULL
   
   ) ON [PRIMARY]

6. Now populate the road condition descriptions:

   INSERT INTO CSRFCND_T
   
   VALUES
   
   (1, 'Dry'),
   
   (2, 'Wet'),
   
   (3, 'Ice'),
   
   (4, 'Snow'),
   
   (5, 'Slush'),
   
   (6, 'Sand, Mud'),
   
   (7, 'Water'),
   
   (99, 'Unknown')

7. Finally, create the weather table:

   CREATE TABLE [dbo].[WEATHER_T](
   
     [WEATHER] [int] NULL,
   
     [WEATHER_CONDITION] [varchar](30) NULL
   
   ) ON [PRIMARY]
   
   Then populate the weather condition descriptions.
   
   INSERT INTO WEATHER_T
   
   VALUES
   
   (1, 'Clear'),
   
   (2, 'Partly Cloudy'),
   
   (3, 'Cloudy'),
   
   (5, 'Mist'),
   
   (6, 'Rain'),
   
   (7, 'Sleet, hail, freezing rain'),
   
   (9, 'Severe winds'),
   
   (10, 'Blowing Sand'),
   
   (99, 'Unknown')

You now have the tables and data required to complete the recipes in this chapter.

How to do it…

1. From your open model, change to the Diagram view in model.bim.
2. Navigate to Model | Import from Data Source then select Microsoft SQL Server on the Table Import Wizard and click on Next.
3. Set your Server Name to Localhost and change the Database name to Chapter3 and click on Next.
4. Enter your admin account username and password and click on Next.
5. You want to select from a list of tables the four tables that were created at the beginning.

   

6. Click on Finish to import the data.

How it works…

This recipe opens the table import wizard and allows us to select the four new tables that are to be added to the existing model. The data is then imported into your Tabular Model workspace. Once imported, the data is now ready to be used to enhance the model.

Modifying model relationships

We will create the necessary relationships for the new tables. These relationships will be used in the model in order for the SSAS engine to perform correct calculations.

How to do it…

1. Open your model to the diagram view and you will see the four tables that you imported from the previous recipe.

   

2. Select the CSRFCND field in the CSRFCND_T table and drag the CSRFCND table in the Crash_Data table.
3. Select the LIGHT field in the LIGHT_T table and drag to the LIGHT table in the Crash_Data table.
4. Select the MAJCSE field in the MAJCSE_T table and drag to the MAJCSE table in the Crash_Data table.
5. Select the WEATHER field in the WEATHER_T table and drag to the WEATHER table in the Crash_Data table.

How it works…

Each table in this section has a relationship built between the code columns and the Crash_Data table corresponding columns. These relationships allow for DAX calculations to be applied across the data tables.

Modifying model measures

Now that there are more tables in the model, we are going to add an additional measure to perform quick calculations on data. The measure will use a simple DAX calculation since it is focused on how to add or modify the model measures.

How to do it…

1. Open the Chapter 3 model project to the Model.bim folder and make sure you are in grid view.
2. Select the cell under Count_of_Crashes and in the fx bar add the following DAX formula to create Sum_of_Fatalities:

   Sum_of_Fatalities:=SUM(Crash_Data[FATALITIES])

   Then, hit Enter to create the calculation:

   

In the properties window, enter Injury_Calculations in the Display Folder. Then, change the Format to Whole Number and change the Show Thousand Separator to True. Finally, add to Description Total Number of Fatalities Recorded:

How it works…

In this recipe, we added a new measure to the existing model that calculates the total number of fatalities on the Crash_Data table. Then we added a new folder for the users to see the calculation. We also modified the default behavior of the calculation to display as a whole number and show commas to make the numbers easier to interpret. Finally, we added a description to the calculation that users will be able to see in the reporting tools. If we did not make these changes in the model, each user will be required to make the changes each time they accessed the model. By placing the changes in the model, everyone will see the data in the same format.

Modifying model columns

We will modify the properties of the columns on the WEATHER table. Modifications to the columns in a table make the information easier for your users to understand in the reporting tools. Some properties determine how the SSAS engine uses the fields when creating the model on the server.

How to do it…

1. In Model.bim, make sure you are in the grid view and change to the WEATHER_T tab.
2. Select WEATHER column to view the available Properties and make the following changes:
   • Hiddenproperty to True
   •  Uniqueproperty to True
   • Sort By ColumnselectWEATHER_CONDITION
   • Summarize By to Count

   

3. Next, select the WEATHER_CONDITION column and modify the following properties.
   • Description add Weather at time of crash
   • Default Labelproperty to True

   

How it works…

This recipe modified the properties of the measure to make it better for your report users to access the data. The WEATHER code column was hidden so it will not be visible in the reporting tools and the WEATHER_CONDITION was sorted in alphabetical order. You set the default aggregation to Count and then added a description for the column. Now, when this dimension is added to a report only the WEATHER_CONDITION column will be seen and pre-sorted based on the WEATHER_CONDITION field. It will also use count as the aggregation type to provide the number of each type of weather conditions. If you were to add another new description to the table, it would automatically be sorted correctly.

Modifying model hierarchies

Once you have created a hierarchy, you may want to remove or modify the hierarchy from your model. We will make modifications to the Calendar_YQMD hierarchy.

How to do it…

1. Open Model.bim to the diagram view and find the Master_Calendar_T table.
2. Review the Calendar_YQMD hierarchy and included columns.
3. Select the Quarter_Name column and right-click on it to bring up the menu.

4. Select Remove from Hierarchy to delete Quarter_Name from the hierarchy and confirm on the next screen by selecting Remove from Hierarchy.

5. Select the Calendar_YQMD hierarchy and right-click on it and select Rename.

6. Change the name to Calendar_YMD and hit on Enter.

How it works…

In this recipe, we opened the diagram view and selected the Master_Calendar_T table to find the existing hierarchy. After selecting the Quarter_Name column in the hierarchy, we used the menus to view the available options for modifications. Then we selected the option to remove the column from the hierarchy. Finally, we updated the name of the hierarchy to let users know that the quarter column is not included.

There’s more…

Another option to remove fields from the hierarchy is to select the column and then press the delete key. Likewise, you can double-click on the Calendar_YQMD hierarchy to bring up the edit window for the name. Then edit the name and hit Enter to save the change in the designer.

Creating a calculated table

Calculated tables are created dynamically using functions or DAX queries. They are very useful if you need to create a new table based on information in another table. For example, you could have a date table with 30 years of data. However, most of your users only look at the last five years of information when running most of their analysis. Instead of creating a new table you can dynamically make a new table that only stores the last five years of dates. You will use a single DAX query to filter the Master_Calendar_T table to the last 5 years of data.

How to do it…

1. OpenModel.bim to the grid view and then select the Table menu and New Calculated Table.

   

2. A new data tab is created. In the function box, enter this DAX formula to create a date calendar for the last 5 years: FILTER(MasterCalendar_T, MasterCalendar_T[Date]>=DATEADD(MasterCalendar_T[Date],6,YEAR))

   

3. Double-click on the CalculatedTable 1 tab and rename to Last_5_Years_T.

   

How it works…

It works by creating a new table in the model that is built from a DAX formula. In order to limit the number of years shown, the DAX formula reduces the total number of dates available for the last 5 years of dates.

There’s more…

After you create a calculated table, you will need to create the necessary relationships and hierarchies just like a regular table:

1. Switch to the diagram view in the model.bim and you will be able to see the new table.
2. Create a new hierarchy and name it Last_5_Years_YQM and include Year, Quarter_Name, Month_Name, and Date
3. Replace the Master_Calendar_T relationship with the Date column from the Last_5_Years_T date column to the Crash_Date.Crash_Date column.

   

Now, the model will only display the last 5 years of crash data when using the Last_5_Years_T table in the reporting tools. The Crash_Data table still contains all of the records if you need to view more than 5 years of data.

Creating key performance indicators (KPIs)

Key performance indicators are business metrics that show the effectiveness of a business objective. They are used to track actual performance against budgeted or planned value such as Service Level Agreements or On-Time performance. The advantage of creating a KPI is the ability to quickly see the actual value compared to the target value. To add a KPI, you will need to have a measure to use as the actual and another measure that returns the target value. In this recipe, we will create a KPI that tracks the number of fatalities and compares them to the prior year with the goal of having fewer fatalities each year.

How to do it…

1. Open the Model.bim to the grid view and select an empty cell and create a new measure named Last_Year_Fatalities:Last_Year_Fatalities:=CALCULATE(SUM(Crash_Data[FATALITIES]),DATEADD(MasterCalendar_T[Date],-1, YEAR))
2. Select the already existing Sum_of_measure then right-click and select Create KPI….

   

3. On the Key Performance Indicator (KPI) window, select Last_Year_Fatalities as the Target Measure. Then, select the second set of icons that have red, yellow, and green with symbols. Finally, change the KPI color scheme to green, yellow, and red and make the scores 90 and 97, and then click on OK.

   

4. The Sum_of_Fatalites measure will now have a small graph next to it in the measure grid to show that there is a KPI on that measure.

   

How it works…

You created a new calculation that compared the actual count of fatalities compared to the same number for the prior year. Then you created a new KPI that used the actual and Last_Year_Fatalities measure. In the KPI window, you setup thresholds to determine when a KPI is red, yellow, or green. For this example, you want to show that having less fatalities year over year is better. Therefore, when the KPI is 97% or higher the KPI will show red. For values that are in the range of 90% to 97% the KPI is yellow and anything below 90% is green. By selecting the icons with both color and symbols, users that are color-blind can still determine the appropriate symbol of the KPI.

Modifying key performance indicators (KPIs)

Once you have created a KPI, you may want to remove or modify the KPI from your model. You will make modifications to the Last_Year_Fatalities hierarchy.

How to do it…

1. Open Model.bim to the Grid view and select the Sum_of_Fatalities measure then right-click to bring up Edit KPI settings….

   

2. Edit the appropriate settings to modify an existing KPI.

How it works…

Just like models, KPIs will need to be modified after being initially designed. The icon next to a measure denotes that a KPI is defined on the measure. Right-clicking on the measure brings up the menu that allows you to enter the Edit KPI setting.

Deploying a modified model

Once you have completed the changes to your model, you have two options for deployment. First, you can deploy the model and replace the existing model. Alternatively, you can change the name of your model and deploy it as a new model. This is often useful when you need to test changes and maintain the existing model as is.

How to do it…

1. Open the Chapter3_model project in Visual Studio.
2. Select the Project menu and select Chapter3_Model Properties… to bring up the Properties menu and review the Server and Database properties. To overwrite an existing model make no changes and click on OK.

   

3. Select the Build menu from the Chapter3_Model project and select the Deploy Chapter3_Model option.

   

4. On the following screens, enter the impersonation credentials for your data and hit OK to deploy the changes.

   

How it works…

the model that is on your local machine and submits the changes to the server. By not making any changes to the existing model properties, a new deployment will overwrite the old model. All of your changes are now published on the server and users can begin to leverage the changes.

There’s more…

Sometimes you might want to deploy your model to a different database without overwriting the existing environment. This could be to try out a new model or test different functionality with users that you might want to implement. You can modify the properties of the project to deploy to a different server such as development, UAT, or production. Likewise, you can also change the database name to deploy the model to the same server or different servers for testing.

1. Open the Project menu and then select Chapter3_Model Properties.
2. Change the name of the Database to Chapter4_Model and click on OK.

   

3. Next, on the Build menu, select Deploy Chapter3_Model to deploy the model to the same server under the new name of Chapter4_Model.

When you review the Analysis Services databases in SQL Server Management Studio, you will now see a database for Chapter3_Model and Chapter4_Model.

Summary

After building a model, we will need to maintain and enhance the model as the business users update or change their requirements. We will begin by adding additional tables to the model that contain the descriptive data columns for several code columns. Then we will create relationships between these new tables and the existing data tables.

Resources for Article:

Further resources on this subject:

• Say Hi to Tableau [article]
• Data Tables and DataTables Plugin in jQuery 1.3 with PHP [article]
• Data Science with R [article]

---