Getting started with SSAS 2012 Tabular Project

In SQL Server Denali CTP3 a new type of Analysis Services project type called Tabular Projects is introduced. The tabular model is based on a relational table model which is familiar to DBA’s, developers and power users. In addition, Microsoft has created a new query language to query the BISM Tabular Model. This language, Data Analysis Expression Language (DAX) is similar to the syntax used in Excel calculations and should be familiar to Excel power users.

You can use Business Intelligence Developer Studio (BIDS) to create BISM Tabular Projects. Before you start developing BISM Tabular Projects, make sure you have

• SQL Server Denali (CTP3) installed in tabular mode.
• BIDS is installed (actually SQL Server Data Tools SSDT, in CTP3 it is still called BIDS)
• You are an administrator on the machine.

Open BIDS => Start->All Programs->Microsoft SQL Server Denali CTP3->Business Intelligence Developer Studio

Create a new project using the tabular project template

File->New->Project->Analysis Serivces Tabular Project

After you have selected the project template (I am using the default project name), a new Tabular Project Project File (.smproj) is created which contains a Model.bim file. The .smproj project file is like any other project file (eg .csproj, .vbproj etc.). The Model.bim (business intelligence model) file contains metadata and data imported from different sources (eg. SQL Server, SQL Azure, Access, Oracle, Analysis Services, Reports, Excel File, Text File etc.). The project structure looks like this

If your Denali Tabular installation is a named instance then you might get an error like this.

Don’t worry, just right click on .bim file and select properties. In the Workspace Server change localhost to the named instance.

In grid view

Let’s add some data. Go to Model -> Import From Data Source, select Microsoft SQL Server, click next and fill in the required connection details

Click on Test Connection and make sure that you can connect to selected server and database successfully.

Click Next. You can now specify the windows account (domain\user format) or use service account.

Click Next and “Select from a list of tables and views to choose the data to import” option.

From the list of database objects select DimProduct, DimCustomer, DimDate and FactInternetSales tables

Click on Finish. Once the data import is successful, you should get a screen like this

Click Close. You can toggle between grid layout or diagram layout. In the diagram layout you will see that the import process has detected the relationships between dimensions and the fact tables. Also notice, that the DimDate dimension is a role playing dimension. The relationship between DimDate.DateKey and FactInternetSales.OrderDateKey, FactInternetSales.DueDateKey & FactInternetSales.ShipDateKey is clearly depicted.

Let’s create a simple measure. Go to the grid view and select the FactInterntSales worksheet. Go to the Table menu option and select “Show Measure Grid”. Select the Cell in the measure grid under the SalesAmount column. In the fx cell, paste this DAX code =SUM(FactInternetSales[SalesAmount]). Tab out of the cell and the model will calcuate the sum of SalesAmount. Select the measure cell and in the properties windows, rename the measure from Measure 1 to SUM SalesAmount

We are now almost ready to deploy our first tabular project. Right click on the .smproj file and select properties

Make sure that the server is the same as the workspace server in the .bim file and Query Mode as “Vertipaq” which is the default value. We will touch the difference between server here and workspace server in the .bim file and different types of query modes in the later posts.

Click OK and save the project. Right click on the .smproj file and select deploy. If your service account is a windows account, you’ll be prompted for the account password. Enter the password and click OK. Once the deployment has succeded you should see a screen like this

Click Close. Go to SQL Server Management Studio (SSMS) and connect to the tabular instance. Expand Databases and you should see your tabular project database installed and ready for use on the server.

Click on New Query in the toolbar and start browsing your first tabular database.

SSAS 2012 Tabular Model

Tabular models are in-memory databases in Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processor, the xVelocity in-memory analytics engine (VertiPaq) delivers fast access to tabular model objects and data by reporting client applications such as Microsoft Excel and Microsoft Power View.

Tabular models support data access through two modes: Cached mode and DirectQuery mode. In cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files. In DirectQuery mode, you can bypass the in-memory model, allowing client applications to query data directly at the (SQL Server relational) source.

Tabular models are authored in SQL Server Data Tools (SSDT) using new tabular model project templates. You can import data from multiple sources, and then enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies. Models can then be deployed to an instance of Analysis Services where client reporting applications can connect to them. Deployed models can be managed in SQL Server Management Studio just like multidimensional models. They can also be partitioned for optimized processing and secured to the row-level by using role based security.

Your Ad Here

A single term like BISM suggests that Analysis Services 2012 and PowerPivot are a single, cohesive product, whereas the Tabular and Multidimensional models are actually very different beasts. If you’re going to be working with Analysis Services 2012 on a project the first decision you’ll have to make is which type of model to use, and if you change your mind later you’ll have to start development again from scratch and learn a lot of new skills. I hope one day that the two models will merge again but it won’t happen soon.

Microsoft has correctly identified that many people want to do BI but were put off by the complexity of building Multidimensional models in previous versions of Analysis Services. The simplicity of the Tabular model goes a long way to solving this problem; Tabular also replaces Report Builder models which were really a just a simple semantic layer for people who didn’t like SSAS or had valid reasons to stay with relational reporting. In order not to scare off this market a new name is necessary to avoid the negative connotations that come with “Analysis Services” and “cubes”.

Calling something a “semantic model” suggests that it’s a nice, light, thin, easy-to implement layer on top of your relational data warehouse, with no data duplication (which is often seen as a Bad Thing) involved. In actual fact anyone who has used the Multidimensional model will know you almost always use MOLAP storage which involves all the data being copied in Analysis Services; and I suspect when people start using the Tabular model they will be using it in Vertipaq mode (where again all the data gets copied into Analysis Services) rather than in DirectQuery mode (where all queries are translated to SQL which is then run against SQL Server).