SSAS Tabular Part 10 : Create Partitions in Table

We apply partition to table for easy management and faster access data and performance. Partitions, in tabular models, divide a table into various logical partition of objects. Each partition can then be processed independent of other partitions and because of that the performance gets increase. 

As per my view, it is very different from how partitions are implemented and utilized for deployed models.

To know more on this click here.

Scenario:

For example, a table certain 100000 rows that contain 16 years of record(2000 to 2016). In this table some of the record changes rarely (for years 2000 to 2015), but other row sets have data that changes often(for years 2015 to 2016). In these cases, there is no need to process all of the data when you really just want to process a portion of the data. Partitions enable us to divide portions of data we need to process frequently from the data that can be processed less frequently.

In this scenario we can divide it into 2 partition. First Partition contain 2000 to 2015 years data (rarely changes data) and other contain only 2016 year data (Frequently changes data).

Steps to create table Partition

For example, I am taking Fact Internet Sales table and going to create a partition on it.

Total number of Rows present in Internet Sales table : 60398

Years present in Internet Sales table: 2005, 2006, 2007 and 2008

Now we are going to create 2 partition on Internet Sales table.

•       Internet Sales 2005to2007 --> 2005, 2006 and 2007 years data (28133 Rows)
•       Internet Sales 2008             --> 2008 data (32265 Rows)

1. In the model designer, click on the Fact Internet Sales table, then click on the Table menu, and then click Partitions.
   
2. In the Partition Manager dialog box, in the partitions list, click the Fact Internet Sales partition.
3. In Partition Name, change the name to Internet Sales 2005to2007.
4. Select the Query Editor button just above the right side of the preview window.
5. In the SQL Statement field, we got full select statement. Here we have to add WHERE statement or we can remove entire query and paste the query given below. After that Click Validate.

               Select * from [dbo].[FactInternetSales] where (([OrderDate] >= N'2005-01-01 00:00:00')

               AND ([OrderDate] < N'2008-01-01 00:00:00'))

      6.  Internet Sales 2005to2007 partition has created. For 2^nd partition click on New                    button.

7. In Partition Name, change the name to Internet Sales 2008.
8. Select the Query Editor button just above the right side of the preview window.
9. In the SQL Statement field, we got full select statement. Here we have to add WHERE statement or we can remove entire query and paste the query given below. After that Click Validate.

                Select * from [dbo].[FactInternetSales] where (([OrderDate] >= N'2008-01-01 00:00:00'))

      10. In the Partition Manager dialog box, notice the asterisk (*) next to the partition            names for each of the new partitions we just created. This indicates that the partition has not been processed (refreshed). So, we have to process the created partition now. Click the Model menu, then point to Process (Refresh), and then click Process Partitions. Select 2 partitions and click on Process.

Output:

In the model designer, click on the Fact Internet Sales table, then click on the Table menu, and then click Partitions. Now you can see 2 partitions without  asterisk (*) for Fact Internet Sales.

SSAS Tabular Part 9 : Create Hierarchies

Hierarchies are groups of columns in a table and arranged based on level and its sub levels. For example we can say Year-->Samester-->Quarter etc.

To create hierarchies, we will use the model designer in Diagram View.

Steps to create Hierarchies

For example, I am taking DATE  table and going to create a Hierarchies on it.

1. In the model designer, click on the Model menu, then point to Model View, and then click Diagram View.

2. Right-click the Date table, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.
3. In the hierarchy name, rename the hierarchy by typing Datetree, and then press ENTER.
4. In the Date table, click the CalendarYear column, then drag it to the Datetree hierarchy, releasing it on top of it.
5. In the Date table, click and drag the CalendarSemester column to the Datetree hierarchy.
6. In the Date table, click and drag the CalendarQuarter column to the Datetree hierarchy.
7. Datetree hierarchy has created.

Output:

Now we have to see the outcome of Datetree hierarchy.

1        1. To see it’s activity click the Model menu, and then click Analyze in Excel.

2. In Excel, in the PivotTable Field List, notice the Datetree field in  Date Table as well as Geography, Product, Product Category, Product Subcategory, and Internet Sales tables with all of their respective columns appear.

3. Go to Date table and select Datetree field, go to Product Table and select EnglishProductName and finally go to Measure and select TotalOrderQuantity.

4. Now we can see first column appear as Drilldown. By Clicking “+” symbol we can dill the data up to granular level. (Year --> Semester --> Quarter)

SSAS Tabular Part 8 : Create KPI (Key Performance Indicators)

Through KPI we can quickly understand a business. It indicates states of business modules success by some graphical indicator . KPIs are used to gauge performance of a value, defined by a Base measure, against a Target value.

We create KPI in top of the measure. To create KPI, we will use the Measure Grid. By default, each table has an empty measure grid.

Steps to create KPI

For example, I am taking Fact Internet Sales  table and going to create a KPI on it.

1. In the model designer, click the Internet Sales table (tab).

2. Click on the Measure Grid and select a cell.

3. In the formula bar, type the following formula and Press ENTER:     TotalOrderQuantity:=SUM([OrderQuantity]) (This measure will serve as the Base  measure for the KPI.)

4. Right-click the TotalOrderQuantity measure, and then click Create KPI.

5. In the Key Performance Indicator (KPI) dialog box, in Target, select the Absolute Value         option.

6. In the Absolute Value field, type 4300, and then press ENTER. (We set this value is the         target value)

7. In the left (low) slider field, type 1720, and then in the right (high) slider field, type 3440.

8. In Select Icon Style, select the first icon type (Circel-Red, Circel-Yellow, Circel-      Green).

9. Click OK.

Output:

1. To see it’s activity click the Model menu, and then click Analyze in Excel. 

2. In Excel, in the PivotTable Field List, notice the KPI’s and  Internet Sales measure    groups appear, as well as the Customer, Date, Geography, Product, Product Category,  Product Subcategory, and Internet Sales tables with all of their respective columns appear.

3. Now go to Product table and click on respective column EnglishColumnName. After that go to KPI’s and expand TotalOrderQuantity and click on Value and Status.

4. Now we can see the try color circle indicator (Red, Yellow, Green) for all Product Name and TotalOrderQuantity based on their value.

Apart from that we can define Target value as some measure name also. In this case STATUS THRESHOLD should be Percentage wise calculation. 
For example Assume TARGET Measure value is 100. Then we have to declare In the left (low) slider field, type 30% (Means 30% of 100), and then in the right (high) slider field, type 70% (Means 70% of 100).

SSAS Tabular Part 7 : Create Measures

A measure is a calculation of column values. We can create it using a DAX formula. One thing we have to remember is ‘It is not like column in a table’. Most of the cases it return only single line. For example SUM of a column, Count of rows for a particular column applying filter (Monthly Total, Quarterly Total, Average, Maximum, Minimum etc).

To create measures, we will use the Measure Grid. By default, each table has an empty measure grid. The Measure Grid appears below a table in the model designer when in Data View.

Apart from this, we can click the Table menu, and then click Show Measure Grid.

Steps to create measures

For Example, I am taking Fact Internet Sales  table and going to create a measure on it.

1. In the model designer, click the Internet Sales table (tab).
2. Click on the Sales Order Number column heading.
3. On the toolbar, click the down-arrow next to the AutoSum (∑) button, and then select DistinctCount.
4. 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.

Here we see the simple logic for this Measure. We can write some DAX expression also.

Like this we are going to create one more measure for 'Order Quantity' column using another method.

1. In the model designer, click the Internet Sales table (tab).
2. Click on the Measure Grid and select a cell.
3. In the formula bar, type the following formula: TotalOrderQuantity:=SUM([OrderQuantity]) (Here 'TotalOrderQuantity' is the Measure name followed by ':' and then we written DAX formula for aggregation)

SSAS Tabular Part 6 : Create Calculated Columns

A calculated column is a column that we add to an existing tabular model table. Instead of pasting or importing values in the source database column, we create a DAX formula that defines the column values.

For Example, I am taking Date table and going to add a calculated column named QuarterDescription after CalenderQuarter.

AIM: In this calculated column, we have to fill Quarter name as per "CalenderQuarter" column value. Calculation scenario given below.

Steps to create QuarterDescription column:

• Click on 'Grid View' Panel (Bottom Right Corner).
• Click on Date Table. Scroll right in date table until CalenderQuarter comes.
• Right click on next column header of CalenderQuarter and click on ‘Insert Column’.(Now we can see one column with default name is created just right of CalenderQuarter column )

• Now change the header of the column by double click on it and give name as QuarterDescription.
• In the formula bar above the table, type the following DAX formula given below

           =SWITCH([CalendarQuarter];1;"First Quarter";2;"Second Quarter";3;"Third                      Quarter";"Fourth Quarter") 

• After paste the formula, press ENTER.

SSAS Tabular Part 5 : Review Existing Relationships and Create New Relationships

If your SQL Server database already contain primary Key and foreign key relationship, then  relationships that were created automatically when you imported data table. 

Steps to view existing relationships between tables :

• Click on 'Grid View' Panel (Bottom Right Corner)  OR click on the Model menu, then point to Model View, and then click Diagram View. (It displaying all of the tables you imported with lines between them)
• Go to any particular Line and double click on this. (Inside 4 drop-down box, It display the source table with column name and lookup table and it's column name)

In above picture it is a relationship between 'Customer' and 'FactInternetSales' Tables. 

CustomerKey is the primary key of 'Customer' and foreign key of 'FactInternetSales'. This is one to Many (1:M) relationship. 

Now we have look on how to establish a relation between tables.

Steps to view existing relationships between tables :

• In the model designer, Select a lookup table, click and hold on the particular column, then drag the cursor to the primarykey column in the source table, and then release. (A solid line appears showing you have created an active relationship between both tables)

For example we are going to create a one to many (1:M) relationship between 'ProductSubCategory' and 'Product' table.

Now we have click and hold 'ProductSubCategoryKey' in 'Product' table then drag the cursor to the 'ProductSubCategoryKey' of ProductSubCategory table and release it. We can see a new line came in between this tow tables. By Double click on this line we can see the relation description.

There is one more way to create relationship. 

Go to 'Table' menu and then click on 'Create relationship' option. then select both tables with column.

SSAS Tabular Part 4 : Rename and Hide column from table in Tabular Model

Now we have to rename the column of a particular table. To do that First we have to import the table (After filter and rename) into model designer. Then we have to go each table and rename the column as we want to appear.

   Steps to do rename column:

•  we have Click on 'Grid View' Panel (Bottom Right Corner).
•  Click on particular table.
•  Double click on header of the column which we want to change.
•  Type the Proper name of the column.

 Steps to hide column:

•  we have Click on 'Grid View' Panel (Bottom Right Corner).
•  Click on particular table.
•  Right click on Particular column and select 'Hide from Client tool' option.