SQL Server Analysis Services Tabular Model

Tabular Models use relational modeling constructs such as tables and relationships for modeling data, and the in-memory analytics engine for storing and calculating data.

Advantages

• The tabular model uses DAX for scripting which is similar to using excel formulas and is faster to learn.
• It uses Vertipaq (x-velocity) engine for in memory column storage, which gives great performance  and direct query mode for retrieving data and aggregates directly from the database which is beneficial for querying data in real time.
• Powerpivot models can be easily upgraded to tabular models, thus providing a path for business users and IT professionals to author models in familiar tools like MS Excel.
• All the client applications that support  multi-dimensional mode will also support  tabular and work natively with it. This is because tabular uses the same data provider that understands both MDX and DAX queries.

Tabular Models are not recommended

• When the source is based on dimensional modeling and has complex relationships with very large volume of data.
• When you want to do complex calculations, scoping and named sets
• No support for writing back or parent, child hierarchy

Multidimensional Vs Tabular Models

Multidimensional

• Based on Kimball Methodology
• Fact and Dimensions

Tabular

• Based on relational Modeling
• Tables, Relationships
• Calculations (Calc Columns, Measures)
• Relationships are based on one single column, i.e we can create only simple relationships.
• DAX is faster them MDX

Examples

Create a New Tabular Model Project

• In SQL Server Data Tools, on the File menu, click New, and then click Project.
• In the New Project dialog box, under Installed Templates, click Business Intelligence, then click Analysis Services, and 
• then click Analysis Services Tabular Project.

Adding Data to Model

By using the Table Import Wizard, you can import data from a variety of relational sources: Access, SQL, Oracle, Sybase, Informix, DB2, Teradata, and more. The steps for importing data from each of these relational sources are very similar to what is described below. Additionally, data can be selected using a stored procedure.

Create a Connection

To create a connection to a the AdventureWorksDW2012 database

• In SQL Server Data Tools, click on the Model menu, and then click Import from Data Source.This launches the Table Import Wizard which guides you through setting up a connection to a data source. If Import from Data Source is greyed out, double click Model.bim in Solution Explorer to open the model in the designer.

• In the Table Import Wizard, under Relational Databases, click Microsoft SQL Server, and then click Next.

• In the Connect to a Microsoft SQL Server Database page, in Friendly Connection Name, type Adventure Works DB from SQL.
• In Server name, type the name of the server you installed the AdventureWorksDW database.
• In the Database name field, click the down arrow and select AdventureWorksDW, and then click Next.
• In the Impersonation Information page, you need to specify the credentials Analysis Services will use to connect to the data source when importing and processing data. Verify Specific Windows user name and password is selected, and then in User Name and Password, enter your Windows logon credentials, and then click Next.

• In the Choose How to Import the Data page, verify Select from a list of tables and views to choose the data to import is selected. You want to select from a list of tables and views, so click Next to display a list of all the source tables in the source database.

• In the Select Tables and Views page, select the check box for the following tables: DimCustomer, DimDate, DimGeography, DimProduct, DimProductCategory, DimProductSubcategory, and FactInternetSales.
• We want to give the tables in the model more easily understood names. Click on the cell in the Friendly Name column for DimCustomer. Rename the table by removing “Dim” from DimCustomer.

• To filter the table data prior to importing 
• Select the row for the Customer table, and then click Preview & Filter. The Preview Selected Table window opens with all the columns in the DimCustomer source table displayed.Review your selections. If everything looks OK, click Finish.

Rename Columns

• To rename columns in the model designer, click the Customer table (tab).
• When you click a tab, that table becomes active in the model designer window.Double click the CustomerKey column name, then type Customer Id, and then press ENTER.

Similary, We can rename other columns as well

Mark as Date Table

Whenever you use Time Intelligence functions in calculations, as you will do when you create measures a little later, you must specify date table properties, which include a Date table and a unique identifier Date column in that table. You can then create valid relationships between other tables and the Date table; necessary for calculations using DAX time intelligence functions.

To set Mark as Date Table

In the model designer, click the Date table (tab).

Click the Table menu, then click Date, and then click Mark as Date Table.

In the Mark as Date Table dialog box, in the Date listbox, select the Date column as the unique identifier.

Review Existing Relationships and Add New Relationships

When you imported data by using the Table Import Wizard, you imported seven tables from the AdventureWorksDW database. Generally, if you import data from a relational source, existing relationships are automatically imported together with the data. However, before you proceed with authoring your model you should verify those relationships between tables were created properly.

To review existing relationships

• In SQL Server Data Tools, click on the Model menu, then point to Model View, and then click Diagram View.
• The model designer now appears in Diagram View, a graphical format displaying all of the tables you imported with lines between them. The lines between tables indicate the relationships that were automatically created when you imported the data.
• Use the mini map controls in the upper-right corner of the model designer to adjust the view to include as many of the tables as possible. You can also click and drag tables to different locations, bringing tables closer together, or putting them in a particular order. Moving tables does not affect the relationships already between the tables. To view all of the columns in a particular table, click and drag on a table edge to expand or make it smaller.
• Click on the solid line between the Customer table and the Geography table. The solid line between these two tables show this relationship is active, that is, it is used by default when calculating DAX formulas.
• Notice the Geography Id column in the Customer table and the Geography Id column in the Geography table now both each appear within a box. This shows these are the columns used in the relationship. The relationship’s properties now also appear in the Properties window.

To add new relationships between tables

• In the model designer, in the Internet Sales table, click and hold on the Order Date column, then drag the cursor to the Date column in the Date table, and then release.
• A solid line appears showing you have created an active relationship between the Order Date column in the Internet Sales table and the Date column in the Date table.
• In the Internet Sales table, click and hold on the Due Date column, then drag the cursor to the Date column in the Date table, and then release.
• A dotted line appears showing you have created an inactive relationship between the Due Date column in the Internet Sales table and the Date column in the Date table. You can have multiple relationships between tables, but only one relationship can be active at a time.
• Finally, create one more relationship; in the Internet Sales table, click and hold on the Ship Date column, then drag the cursor to the Date column in the Date table, and then release.
• A dotted line appears showing you have created an inactive relationship between the Ship Date column in the Internet Sales table and the Date column in the Date table.

Create Calculated Columns

There are several ways to create new columns, rename them, and place them in various locations in a table.

Create a Month Calendar calculated column in the Date table

• In SQL Server Data Tools, click the Model menu, then point to Model View, and then click Data View.
• Calculated columns can only be created by using the model designer in Data View.
• In the model designer, click the Date table (tab).
• Right-click the Calendar Quarter column, and then click Insert Column.
• A new column named CalculatedColumn1 is inserted to the left of the Calendar Quarter column.
• In the formula bar above the table, type the following formula. AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available.

=RIGHT(" " & FORMAT([MonthNumberOfYear],"00"), 2) & " - " & [MonthName]

• When you have finished building the formula, press ENTER.
• Values are then populated for all the rows in the calculated column. If you scroll down through the table, you will see that rows can have different values for this column, based on the data that is in each row.
• The Month Calendar calculated column provides a sortable name for Month.

• Rename this column to Month Calendar

Create a Day of Week calculated column in the Date table

• With the Date table still active, click on the Column menu, and then click Add Column.
• A new column is added to the far right of the table
• In the formula bar, type the following formula:

=RIGHT(" " & FORMAT([DayNumberOfWeek],"#0"), 2) & " - " & [EnglishDayNameOfWeek]

• When you have finished building the formula, press ENTER.
• Rename the column to Day of Week.
• Click on the column heading, and then drag the column between the Day Name column and the Day of Month column.
• The Day of Week calculated column provides a sortable name for the day of week.

Create a Product Subcategory Name calculated column in the Product table

• In the model designer, select the Product table.
• Scroll to the far right of the table. Notice the right-most column is named Add Column, click the column heading.
• In the formula bar, type the following formula.

=RELATED(ProductSubcategory[EnglishProductSubcategoryName])

• When you have finished building the formula, press ENTER.
• Rename the column to Product Subcategory Name.
• The Product Subcategory Name calculated column is used to create a hierarchy in the Product table which includes data from the Product Subcategory Name column in the Product Subcategory table. Hierarchies cannot span more than one table. 

Create a Product Category Name calculated column in the Product table

• With the Product table still active, click the Column menu, and then click Add Column.
• In the formula bar, type the following formula:

=RELATED(ProductCategory[EnglishProductCategoryName])

• When you have finished building the formula, press ENTER.
• Rename the column to Product Category Name.
• The Product Category Name calculated column is used to create a hierarchy in the Product table which includes data from the Product Category Name column in the Product Category table. Hierarchies cannot span more than one table.

Create a Margin calculated column in the Internet Sales table

• In the model designer, select the Internet Sales table.
• Add a new column.
• In the formula bar, type the following formula:

=[SalesAmount]-[TotalProductCost]

• When you have finished building the formula, press ENTER.
• Rename the column to Margin.
• Drag the column between the Sales Amount column and the Tax Amt column.
• The Margin calculated column is used to analyze profit margins for each (product) row.

Create Hierarchies

To create a Category hierarchy in the Product table

• In the model designer, click on the Model menu, then point to Model View, and then click Diagram View.
• In the model designer, right-click the Product table, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.

• In the hierarchy name, rename the hierarchy by typing Category, and then press ENTER.
• In the Product table, click the Product Category Name column, then drag it to the Category hierarchy, and then release on top of the Category name.

• In the Category hierarchy, right-click the Product Category Name column, then click Rename, and then type Category.
• In the Product table, right-click the Product Subcategory Name column, then in the context menu, point to Add to Hierarchy, and then click Category.
• Rename Product Subcategory Name to Subcategory.
• By using click and drag, or by using the Add to Hierarchy command in the context menu, add the Model Name and Product Name columns (in order) and place them beneath the Product Subcategory Name column. 
• Rename these columns Model and Product, respectively.

To create hierarchies in the Date table

Calender Hierarchy

• In the model designer, right-click the Date table, and then click Create Hierarchy.
• Rename the hierarchy to Calendar.
• Add the following columns, in-order, and then rename them:
• Calendar Year --> Year
• Calendar Semester --> Semester
• Calendar Quarter --> Quarter
• Month Calendar --> Month
• Day Number Of Month --> Day 

Fiscal Hierarchy

• In the Date table, repeat the above steps, creating a Fiscal hierarchy, including the following columns:
• Fiscal Year --> Year
• Fiscal Semester --> Semester 
• Fiscal Quarter --> Quarter 
• Month Calendar --> Month 
• Day Number Of Month --> Day

Production Calender Hierarchy

• Finally, in the Date table, repeat the above steps, creating a Production Calendar hierarchy, including the following columns:
• Calendar Year --> Year
• Week Number Of Year --> Week
• Day Of Week --> Day

Create Measure

You can create a measure by clicking on an empty cell in the measure grid, and then typing a DAX formula in the formula bar. When you click ENTER to complete the formula, the measure will then appear in the cell. You can also create measures using a standard aggregation function by clicking on a column, and then clicking on the AutoSum button (∑) on the toolbar. Measures created using the AutoSum feature will appear in the measure grid cell directly beneath the column, but can be moved if necessary.

To create a Days Current Quarter to Date measure in the Date table

• In the model designer, click the Date table.
• If an empty measure grid does not already appear beneath the table, click on the Table menu, and then click Show Measure Grid.
• In the measure grid, click the top-left empty cell.
• In the formula bar, above the table, type the following formula:

=COUNTROWS( DATESQTD( 'Date'[FullDateAlternateKey]))

• When you have finished building the formula, press ENTER.
• Notice the top-left cell now contains a measure name, Measure 1, followed by the result, 30. The measure name also precedes the formula in the formula bar.
• To rename the measure, in the formula bar, highlight the name, Measure 1, then type Days Current Quarter to Date, and then press ENTER.

To create a Days in Current Quarter measure in the Date table

• With the Date table still active in the model designer, in the measure grid, click the empty cell below the measure you just created.
• In the formula bar, type the following formula:
• Days in Current Quarter :=COUNTROWS( DATESBETWEEN( 'Date'[Date], STARTOFQUARTER( LASTDATE('Date'[Date])), ENDOFQUARTER('Date'[Date])))
• Notice in this formula you first included the measure name followed by a colon (:).
• When you have finished building the formula, press ENTER.

To create an Internet Distinct Count Sales Order measure in the Internet Sales table

• In the model designer, click the Internet Sales table (tab).
• If the measure grid does not already appear, right-click the Internet Sales table (tab), and then click Show Measure Grid.
• Click on the Sales Order Number column heading.
• On the toolbar, click the down-arrow next to the AutoSum (∑) button, and then select DistinctCount.
• The AutoSum feature automatically creates a measure for the selected column using the DistinctCount standard aggregation formula.
• Notice the top cell below the column in the measure grid now contains a measure name, Distinct Count Sales Order Number. Measures created using the AutoSum feature are automatically placed in the top-most cell in the measure grid below the associated column.
• In the measure grid, click the new measure, and then in the Properties window, in Measure Name, rename the measure to Internet Distinct Count Sales Order.

Internet Previous Quarter Margin
=CALCULATE([Internet Total Margin],PREVIOUSQUARTER('Date'[Date]))

Internet Current Quarter Margin
=TOTALQTD([Internet Total Margin],'Date'[Date])

Internet Previous Quarter Margin Proportion to QTD
=[Internet Previous Quarter Margin]*([Days Current Quarter to Date]/[Days In Current Quarter])

Internet Previous Quarter Sales
=CALCULATE([Internet Total Sales],PREVIOUSQUARTER('Date'[Date]))

Internet Current Quarter Sales
=TOTALQTD([Internet Total Sales],'Date'[Date])

Internet Previous Quarter Sales Proportion to QTD
=[Internet Previous Quarter Sales]*([Days Current Quarter to Date]/[Days In Current Quarter])

Create Key Performance Indicators

Internet Current Quarter Sales Performance KPI

To create an Internet Current Quarter Sales Performance KPI

• In the model designer, click the Internet Sales table (tab).
• In the measure grid, click an empty cell.
• In the formula bar, above the table, type the following formula:

             Internet Current Quarter Sales Performance :=
               IFERROR([Internet Current Quarter Sales]/[Internet Previous Quarter Sales Proportion to QTD],BLANK())

• When you have finished building the formula, press ENTER.
• This measure will serve as the Base measure for the KPI.
• In the measure grid, right-click the Internet Current Quarter Sales Performance measure, and then click Create KPI.
• The Key Performance Indicator dialog box opens.
• In the Key Performance Indicator dialog box, in Define Target Value, select the Absolute Value option.
• In the Absolute Value field, type 1.1, and then press ENTER.
• In Define Status Thresholds, in the left (low) slider field, type 1, and then in the right (high) slider field, type 1.07.
• In Select Icon Style, select the diamond (red), triangle (yellow), circle (green) icon type.

Internet Current Quarter Margin Performance KPI

To create an Internet Current Quarter Margin Performance KPI

• In the measure grid for the Internet Sales table, click an empty cell.
• In the formula bar, above the table, type the following formula:
• Internet Current Quarter Margin Performance :

    =IF(

           [Internet Previous Quarter Margin Proportion to QTD]<>0

           ,([Internet Current Quarter Margin]-[Internet Previous Quarter Margin Proportion to QTD])

          /[Internet Previous Quarter Margin Proportion to QTD]

          ,BLANK()

         )

• When you have finished building the formula, press ENTER.
• In the measure grid, right-click the Internet Current Quarter Margin Performance measure, and then click Create KPI.
• In the Key Performance Indicator dialog box, in Define Target Value, select the Absolute Value option.
• In the Absolute Value field, type 1.25.
• In Define Status Thresholds, slide the left (low) slider field until the field displays 0.8, and then slide the right (high) slider field, until the field displays 1.03.
• In Select Icon Style, select the diamond (red), triangle (yellow), circle (green) icon type, and then click OK.