<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SSAS Tabular model implementaion with example

Published on
6,372 Points
3,172 Views
2 Endorsements
Last Modified:
Most SQL Server administrators know about the Analysis Service as part of SQL Server. There are two ways to create cubes. One is the Dimension model we use most of the time to create cubes. In SQL Server 2012, Microsoft introduced  the Tabular model -- a lightweight version of Dimension model. It is like Power Pivot but with some extra features.

These are the benefits of the Tabular model, and an idea of when you can use it.

Benefits:

The architecture of the Tabular model is more lightweight than Multidimensional model.  Using the Tabular model you can create a cube directly from an OLTP database; there is no need to create the DW compatible database. One can also create a cube from an OLAP database as well. Moreover, they are easy to create and less expensive in terms of time and money.

The Tabular database design is normalized so data retrieval is faster than a de-normalized database. It uses the  same xVelocity engine as the Dimension model so it is faster than Power Pivot.

In Power Pivot you cannot save it and use it later. However, using the Tabular model you can save it on the server and review it like a normal Dimension model cube.

When to use the Tabular model:

There is no requirement of big DW and data size is lesser then one can use Tabular Model.
One more reason is need DW up and running in shorter period.
Budget constrains in organization.

How to create a Cube using the Tabular model:

To work with the Tabular model you have to create new Instance of SSAS using Tabular model. see steps here

Getting Started
 

1.

Open VS 2012 and New project in that you can find new template for Analysis Service Tabular project. It will create project for you with single .bim file.
Tabular Creation

2.

Now create a new connection by hitting the DB icon in the toolbar and follow the friendly steps and import tables those you want in the Cube. After finishing the import step you will get each table in a tabular format on separate sheets like Excel.
Create Connection and Import dataCreate Connection and Import dataCreate Connection and Import data

3.

Now you can see the all tables in Table format.  Without measure cube is nothing. To create a measure click on field from table and hit the Icon for Sum. You can measure the value for different aggregate functions like Sum , Count, Max, Min and Avg etc. When you create a measure you can see it in below table.
Create Measures

4.

Now create KPI. Select the measures you want to use for KPI and right click. The wizard will show a new context menu you use to Create KPI. Give absolute values or select a measure to create KPI.
Create KPIView is in two formats: Tabular and Diagram form. At the bottom right you can find the button to switch the view.

5.

After moving to Diagram mode you will see the tables with their relationships. If there is no relationship between tables you can create it by right clicking on a table and select the menu item as what you want to create.
Hierarchy and Relationship

6.

Now it's time to deploy the Cube. Go to Solution explorer, select the project, right click and select the deploy menu item Deploy Cube. It will be deployed to the SSAS Tabular service.
Hope you enjoyed reading.
2
Comment
1 Comment
 

Administrative Comment

by:Eric AKA Netminder
Alpesh,

Congratulations! Your article has been published.

ericpete
Page Editor
0

Featured Post

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Join & Write a Comment

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month