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.

SSAS Tabular Part 3 : Rename and Filter the table data Before importing into Tabular Model

For Table Security and easy user access Microsoft added options for Rename table. You can give any Name for each table before import the table into model.

For example i have given some Friendly name for few tables.

Now come to Filer option of the table data.

To do that you have to select particular table and click on "Preview & Filter" button.

Here i have selected 'DimProduct' Table and given friendly name as 'Product'. Now i click on 'Preview & Filter' button.

Now we can see Product table with all the columns. But in our model we have no need for all the columns in 'Product' Table. To unselect the column, we have to untick the check box near specific column.

Here i have removed 2 column from 'Product' table by untick the SpanishProductName and FrenchProductName.

Apart form that we can apply filter on specific selected column also. 

After filter click on 'Finish' Button.

SSAS Tabular Part 2: Create a New Data source Connection in Tabular solution

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.
• 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 Domain\User Name and Password, enter your Windows logon credentials, and then click Next.

• Now Choose Select from a list of tables and views to choose the data to import Option.

• You will get all Tables with Views exists in AdventureWorksDW2012 database.

By selecting checkbox of particular table, you can Import some table data in to model.

NOTE: In some situation you may need to import other tables which are not in this current model. So, you have to connect database again through datasouce and get the tables.

This time many people do mistake by clicking the 'Import from datasource' option.

If you do the same, then SSDT asked to renter your user, Password and Database .you will get expected table with data. SSDT will create one more datasorce connection for that, which is wrong method to implementation.

The correct process is:

Go to Model and click on 'Existing Connections'. You will get the connection you have created previously. Now press on 'Open' button and go ahead by click 'Next'  Button.

SSAS Tabular Part 1: Create a New Tabular Model Project

To create a new tabular model project in SSDT 2012

1.       Open SQL Server Data Tools 2012.

2.       Click On NEW Project.

3.    In the New Project dialog box, under Installed Templates, click Business Intelligence, then click Analysis Services, and then click Analysis Services Tabular Project.

4.       In Name, type your project name, then specify a location for the project files.

By default, Solution Name will be the same as the project name; however, you can type a different solution name.

5.     Click OK.

6.    In the Tabular model designer dialog box, in Workspace server, type the name of a SQL Server 2012 Analysis Services instance where you have Server Administrator permissions, and then click Test Connection.

7.       Your Tabular Project is Created with Model.bim

What is SSAS Tabular Model?

On the Microsoft data platform, SQL Server Analysis Services (SSAS) provides a mechanism for creating and publishing corporate data models that can be used as a source for PivotTables in Excel, reports in SQL Server Reporting Services, dashboards in SharePoint Server PerformancePoint Services, and other BI reporting and analysis tools.

Tabular model is a new enhancement in Microsoft SQL Server 2012 analysis service. Now a days, we are using power Pivot and Power BI for data analysis and reporting. It is very faster compare to other reporting tool (SSRS) and light weight too.

It run in-memory or in DirectQuery mode, accessing data directly from backend relational data sources.

This model has compression algorithms and multi-threaded query processor, so that, the in-memory analytics engine delivers (vertipaq) fast access to tabular model objects (table, Views) and data by reporting client applications such as Microsoft Excel and Microsoft Power View.

About SSAS Tabular Model

We can implement Tabular models in SQL Server Data Tools (SSDT) using the Tabular model project template that provides a design surface for creating a model, tables, relationships, and DAX expressions. You can import data from multiple sources, and then enrich the model by adding relationships, calculated tables and columns, measures, KPIs, hierarchies, and translations.

Tabular Model Life Cycle

The steps that happen during data processing in Vertipaq:

1. Reading of the source dataset, transformation into a columnar data structure of VertiPaq, encoding and compressing each column.
2. Creation of dictionaries and indexes for each column.
3. Creation of the data structures for relationships.
4. Computation and compression of all the calculated columns.

Look and feel of Tabular Solutions:

Table Partition in SQL Server

We apply partition to table or database for easy management and faster access data and performance.

We can make partition of a table in Inside single Database. In case of multiple Database, then we called it as distributed partition view.

To avoid table partition, we can create multiple local views (LPV- Local partition views) of a table based on various range.

To do the table partition, we have decide the Partition Key.

•      The Partition Key is responsible for data distribution across your nodes.
•      The Clustering Key or Primary Key is responsible for data sorting within the partition.

For example If a table contain data’s form 2009 to till (2016). We want to make table partition by each year data.

• Number of Partition: 8
• Number of Boundary: 7

How to Create Table Partition

Step1: Create the file groups in the database.

Step2: Assign the files to file groups.

Step3: Create a Partition function for the table based on number of boundary.

Step4: Create a Partition schemes for the table based on Partition function  .

Step5: Create table based on Partition schema.

Practical Implementation:

1. Create a database named “Test_DB” on the SQL Server.
2. Go to Properties and click on file groups. Now add as many file group you want. Here I          have created 4 new file groups along with Primary File group.

3. Create new files and assign those file to File groups.

4. Now create a partition function with 3 boundary points.

     CREATE PARTITION FUNCTION Sales_Partition_func (int) as

    range right for values (2014,2015,2016) 

5. Create Partition Schemes based on partition Function.

     CREATE PARTITION SCHEME Sales_Partition_Schem AS

PARTITION Sales_Partition_func TO

(

[Data_2014],

[Data_2015],

[Data_2016],

[Data_Others])

6. Create Partition table. (Here you have to remember the partition column should be under     primary or unique key). 

     Create table Sales (

   Sales_Id Int Identity (1,1),

   Sales_Man Varchar(100),

   Sales_Date date,

   Sales_Amount Int,

   Sales_Year Int

   Primary key (Sales_Id,Sales_Year))

   ON [Sales_Partition_Schem] (Sales_Year) 

 Insert some values into sales table.

 Insert into Sales values ('Pritam Das','2013-06-13',100,2013)

 Insert into Sales values ('Pritam Das','2014-06-14',300,2014)

 Insert into Sales values ('Pritam Das','2014-08-17',600,2014)

 Insert into Sales values ('Pritam Das','2015-06-15',200,2015)

 Insert into Sales values ('Pritam Das','2015-08-18',500,2015)

 Insert into Sales values ('Pritam Das','2016-06-16',100,2016)

 Insert into Sales values ('Pritam Das','2016-08-14',300,2016)

 Insert into Sales values ('Pritam Das','2017-06-17',100,2017)

After Inserting values to sales table, Now is the time to Check for partition.

Select $PARTITION.[Sales_Partition_func](Sales_Year) as P_no,* from Sales

Here we have used Partition function and pass Partition Key (Sales_Year).

Output:

There are many more sql Sys table is there to check partition of table.

To get the Row count of particular partition you can run below query by change your partition table name.

Select * from sys.partitions where Object_id in (Select object_id from sys.objects where name='Sales')

Other Partition related SQL Queries.

1. To get the data of particular partition.
       
    Select * from Sales where $PARTITION.[Sales_Partition_func](Sales_Year)=4 

1   2. To get the Partition function existing in particular Database and each function details.               Select * from sys.partition_functions

     3. see partition range values and Boundary in a particular function.

Select * from sys.Partition_range_values where function_id in (select    function_id from sys.partition_functions where name='Sales_Partition_func' )

SELECT * FROM SYS.PARTITION_SCHEMES

SELECT * FROM SYS.DATA_SPACES

SELECT * FROM SYS.DESTINATION_DATA_SPACES

SELECT * FROM SYS.FILEGROUPS