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.
