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:
(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.
We will open the model. To make modifications to your deployed models, we will need to open the model in the Visual Studio designer.
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.
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.
In the database on your SQL Server, run the following scripts to create the four tables and populate them with the reference data:
CREATE TABLE [dbo].[MAJCSE_T](
[MAJCSE] [int] NULL,
[MAJOR_CAUSE] [varchar](50) NULL
) ON [PRIMARY]
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')
CREATE TABLE [dbo].[LIGHT_T](
[LIGHT] [int] NULL,
[LIGHT_CONDITION] [varchar](30) NULL
) ON [PRIMARY]
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')
CREATE TABLE [dbo].[CSRFCND_T](
[CSRFCND] [int] NULL,
[SURFACE_CONDITION] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO CSRFCND_T
VALUES
(1, 'Dry'),
(2, 'Wet'),
(3, 'Ice'),
(4, 'Snow'),
(5, 'Slush'),
(6, 'Sand, Mud'),
(7, 'Water'),
(99, 'Unknown')
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
After you create a calculated table, you will need to create the necessary relationships and hierarchies just like a regular table:
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.
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.
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.
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.
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.
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.
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.
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.
When you review the Analysis Services databases in SQL Server Management Studio, you will now see a database for Chapter3_Model and Chapter4_Model.
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.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…