Familiar. Collaborative. Managed.
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).
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).
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.
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 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.
Figure 4 – Creating a Hierarchy
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).
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.
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.
Figure 7 – Retrieving Related Information
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.
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.
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.
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.
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.