Design a Tabular BI Semantic Model 2

This is the second of four posts that explores Tabular data analysis model in Microsoft SQL Server 2016. We examined how to create a Tabular data analysis project and load data to SQL Server Data Tools (SSDT) in previous post. Complete solution is available in GitHub.

In this post we will explore few basic operations of Tabular data modeling.

Prerequisites,

• SQL Server Analysis Services (SSAS) 2016 configured to use tabular model.
• SQL Server Data Tools (SSDT).
• Adventure Works DW 2014 sample database.
• Microsoft Excel.
• Content covered in previous post.

Assuming all prerequisites are satisfied, let’s start work by opening the “AW Internet Sales Tabular Model Solution” in SSDT.

1

          1.   Renaming columns

In the model designer, select the table (tab) that needs to modify. Simply double click on the column to be renamed. Rename the column and press enter. This step needs to carried out for following columns.

Customer
Source Name	Friendly Name
CustomerKey	Customer Id
GeographyKey	Geography Id
CustomerAlternateKey	Customer Alternate Id
FirstName	First Name
LastName	Last Name
MiddleName	Middle Name

Continue renaming columns as specified in this spreadsheet.

2.   Mark as Date Table

A date table with a column specified to be used is necessary when performing time intelligence functions with Data Analysis Expressions (DAX). Fist select the Date table (tab) in model designer. Select the Date column and make sure the data type in properties window is Date. Click in main menu Table -> Date -> Mark as Date Table. In Mark as Date Table column, select Date as the column.

3.   Create Relationships

Relationship between two tables is a logical connection that describes how data in both tables correlates to each other, Relationships are to be automatically generated during data import, but relationships between existing tables and data/table that imports later stages will not be created automatically.

Existing relationships can be review by in main menu Model -> Model View -> Diagram View. Although this diagram view and Manage Relationships windows can be used to verify relationships.

The solid line appears between two tables represents an active relationship, which is used as the default relationship between those two tables when calculating DAX formulas.

To create relationships, click on parent tables column, drag it to child table and release it there. Create the relationships as mention is below table.

Source [Table].[Column]	Destination [Table].[Column]
[Internet Sales].[Due Date]	[Date].[Date]
[Internet Sales].[Ship Date]	[Date].[Date]

4.   Create Calculated Columns

Calculated columns are based on available data in other columns. Calculated columns can only be created in Data View of the mode designer. To create calculated columns, switch to Data View and select the tab that needs a calculated column. Columns can be inserted either by right clicking on a column and selecting insert new column option or double clicking on empty column with ‘Add Column’ at the right most end of the table. Crate the following calculated columns.

Table Name	Column Name	Function
Date	Month Calendar	=RIGHT(" " & FORMAT([Month],"#0"), 2) & " - " & [Month Name]
Date	Day of Week	=RIGHT(" " & FORMAT([Day Number Of Week],"#0"), 2) & " - " & [Day Name]
Product	Product Sub Category Name	=RELATED('Product Subcategory'[Product Subcategory Name])
Product	Product Category Name	=RELATED('Product Category'[Product Category Name])
Internet Sales	Margin	=[Sales Amount]-[Total Product Cost]

In this post we discussed few table and column related operations and we will continue with few more operations in next post.