Guest Author: Dan Clark, Senior BI Consultant, Pragmatic Works

Introduction

The goal of this blog series is to expose you to the process needed to create a BISM Tabular Model in SQL Server 2012 and to deploy it to an Analysis Server where it can be exposed to client applications. The first part of this series covered setting up a tabular model project and importing data into the tabular model. This segment looks at table relations, implementing calculations and creating measures with DAX, and Part 3 will cover implementing semi-additive measures and securing the model. Finally, Part 4 will conclude the series by looking at how you deploy and connect to the tabular model from a client application.

Creating Relationships

Once you have imported data into your tabular model, it’s time to create relationships between the tables. When you import data from a relational database system, the Data Import Wizard will detect relationships defined in the database and automatically import them for you. There are two views you can use to examine the model, the Data View and the Diagram View. Viewing the model in the Diagram View mode reveals the relationships between the tables (Figure 1).

Diagram_View_Mode

Figure 1 – Diagram View Mode

To edit a relationship, you just double-click on the relationship arrow, which launches the Edit Relationship dialog (Figure 2).

Editing_a_Relationship

Figure 2 – Editing a Relationship

If you bring data in from multiple sources, you will have to establish the relationship between the tables. To create a new relationship, you can drag and drop the related field from one table to the other. For example, drag and drop the ProductCategoryKey from the ProductCategory table to the ProductCategoryKey in the ProductSubcategory table.

The tabular model can only support a single active relationship between tables. This relationship is used by the model to establish how data is connected between the tables. For example, by establishing a relationship between the Date table and the Sales table using the Sales OrderDate column, you can filter the Sales by OrderDate. Although you can only have one active relationship between two tables, you can have many inactive relationships between tables. Figure 3 shows one active (solid line) and one inactive relationship (dashed line) between the Sales and the Date table. Inactive relationships can be used when querying the data using Data Analysis Expression language (DAX) by indicating the relationship to use in the query.

Active_and_Inactive_Relationships

Figure 3 – Active and Inactive Relationships

In order to participate in a relationship, a table must contain a primary key that uniquely identifies rows in the table. Composite keys are not allowed, and if you import a table with a composite key, you must combine the keys into a single calculated column that will act as the primary key.

Although tabular models do not support the creation of many-to-many relationships and self-joins, there are DAX functions available to help simulate these types of relationships.

Hierarchies

Hierarchies define relations between two or more columns in a table. They provide users with an intuitive path for rolling up and drilling down into the data. For example, a calendar hierarchy created in a date table can consist of year à quarter à month. You can easily use the hierarchy to view aggregate values such as sum of sales at each level. To define a hierarchy in a table, click the icon in the upper right corner of the table (Figure 4). Name the hierarchy, and drag and drop the fields for the hierarchy on top of the name.

Creating_a_Hierarchy

Figure 4 – Creating a Hierarchy

Creating Calculated Columns

In order to create calculated columns and measures in a BISM Tabular model you use the Data Analysis Expression language (DAX). If you have ever worked with formulas in Excel, you will find the DAX language syntax very familiar. DAX works with the Vertipaq engine to quickly perform calculations on large volumes of in-memory data.

Calculated columns are columns you add to an existing table in the tabular model. The value of the column is calculated for each row at the time you create the column. It is recalculated if the underlying data is refreshed. These values are static values that do not change as the client slices the data in a PivotTable.

To create a calculated column in the tabular model, change the model designer so that it is in the Data View Mode. The Data View mode shows the data in an Excel-like sheet, with each table as a separate tab (Figure 5).

Data_View_Mode

Figure 5 – Data View Mode

To create a calculated column, right-click on any column and select insert column. At the top of the sheet is the formula bar where you enter the formula for the calculated column. For example, Figure 6 shows a margin calculated column in the sales table.

Creating_a_Calculated_Column_in_the_Formula_Bar

Figure 6 – Creating a Calculated Column in the Formula Bar

DAX introduces functions for following relationships and retrieving related data from another table. For example, you can use the Related function to retrieve a value from the one side of a many-to-one relationship. Figure 7 shows retrieving the ModelName of a Product in an order and using it as a calculated column in the Sales Table.

Retrieving_Related_Information

Figure 7 – Retrieving Related Information

Measures and KPIs

Unlike a calculated column, a measure is a calculation based on the set of data being evaluated. They are often based on aggregate functions such as count and sum. For example a client application may request the total sales amount for each country by year. Since the value in each cell of the PivotTable is dependent on the combination of row and column headers, the formula needs to be evaluated for each cell. As the user applies different filters, the values are dynamically recalculated for the cells. The xVelocity engine is designed to provide optimum performance, through its use of column storage and in-memory data storage, when calculating measures on the fly.

To create a measure, you use the measure grid at the bottom of the table. You simply click on an empty cell in the measure grid and type the formula in the formula bar. Figure 8 shows a Total Units measure that sums up the total units sold.

Creating_a_Measure_Using_the_Formula_Bar

Figure 8 – Creating a Measure Using the Formula Bar

DAX is a very powerful easy to use language and can be leveraged to create complex measures. It also contains a set of Date functions that make it easier to compare data over time. For example, the following DAX expression creates a measure that determines the previous month sales for comparison to the current month.

PrevMonthSales:=CALCULATE(SUM(ResellerSales[SalesAmount]),PREVIOUSMONTH('Date'[FullDate]))

Key Performance Indicators (KPIs) are often used to gauge performance and identify trends. For example, you may want to measure sales against a target sales value. A KPI calculation includes a base value, target value and a status threshold. The base value is the measure you are interested in analyzing, for example, the sales amount. The target value is the goal; you are comparing the base value to this goal, for example, sales quota. The status threshold defines how the comparison is interpreted, and it is often used with a graphic (red, yellow, green) to help users quickly determine performance.

To create a KPI, right-click on the measure you want to use for the base value and select Create KPI. This launches the Key Performance Indicator dialog. Figure 9 shows a KPI created using the Sales as the base value and the Sales Quota as the target value.

Creating_a_KPI

Figure 9 – Creating a KPI

The next step in creating a KPI is to set the threshold values and choose an image to display for the values. Figure 10 shows the threshold values for the Sales KPI.

Setting_Up_Threshold_Values

Figure 10 – Setting up Threshold Values

For more information on DAX refer to the whitepaper “Data Analysis Expressions (DAX) In the Tabular BI Semantic Model” available for download here.

Summary

The goal of this blog series is to expose you to the process needed to create a BISM Tabular Model in SQL Server 2012 and to deploy it to an Analysis Server where it can be exposed to client applications. This segment looked at table relations, implementing calculations and creating measures with DAX. In order to get the most out of your data analysis, It is important that you create solid tabular models, and that you are comfortable using DAX to create calculations and measures. Join me for Part 3 of this series, where we will cover implementing semi-additive measures and securing the model, coming soon.