woensdag 16 juli 2014

SSAS : Introduction to Tabular Model in SQL Server 2014 (RTM) (Part I)


In this introduction blogpost I'll explain the Tabular Model with a walk through the tutorial that is available on MSDN. In this tutorial a Tabular Model is created on the AdventureWorks Database. This tutorial is based on the Adventure Works Cycles, a fictitious company. They produce and distributes bicycles in North America, europe and Asia.

1. Setting up the project

The first thing to do is creating the project with Visual Studio 2012. 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. In Name, type AW Internet Sales Tabular Model, then specify a location for the project files. Click OK.

2. Select an Analysis Services Instance

The next dialog is about selecting the right Workspace Server and the right compatibility level. First set the Workspace server to the Tabular Model Instance in SQL Server. The next thing to do is setting the compatibility level of Tabular model. In SQL Server 2014 Analysis Services Instance supports the following compatibility levels (database version) (MSDN):
  • SQL Server 2012 (1100)
  • SQL Server 2012 SP1 (1103)
  • SQL Server 2014 (1103)
Strangly enough, I see two compatability levels: SQL Server 2012 (1100) and SQL Server 2012 SP1 (1103).

I assume that SQL Server SQL Server 2012 SP1 (1103) and SQL Server 2014 (1103) are the same. Checking the compatibilty level in the Analysis Service Properties in SQL Server Managament Studio shows me that the compatibility level is also 1103.

Kasper de Jonge blogged about the differences between compatability levels 1100 and 1103. Please take a look at the differences between the two.

3. The Tabular project

Once you have created the Tabular project the following window is presented on the screen. There are a couple of interesting menus, options and properties available. In the Model menu, you can launch the Table Import Wizard, view and edit existing connections, refresh workspace data, browse your model in Microsoft Excel with the Analyze in Excel feature, create perspectives and roles, select the model view, and set calculation options. In the Table menu, you can create and manage relationships between tables, create and manage, specify date table settings, create partitions, and edit table properties. And in the Column menu you can add and delete columns in a table, freeze columns, and specify sort order. 

In the properties window of the model.bim, you can also see the compatbility level again(1103), the DirectQuery property. This property handles the In Memory (or not) possibillity of deploying a Tabular model.


In this blogpost I described an introduction to the Tabular model (Part I). I wrote about the compatability level of Tabular Model in SQL Server 2014 and Visual Studio 2012.


1 opmerking:

  1. Hi ,
    Why do we need to create partitions only on fact table. why not on the dimension tables. Could you please explain.