Familiar. Collaborative. Managed.
Guest blogger Dan Clark is a senior BI consultant for Pragmatic Works. As a member of the Pragmatic Works SEAL Team (Special Engagements and Learning) he is focused on learning new BI technologies and training others how to best implement the technology. Dan has published several books and numerous articles on .NET programming and BI development. He is a regular speaker at various developer/database conferences and user group meetings, and enjoys interacting with the Microsoft developer and database communities.
Business Intelligence, the process of analyzing business data to support better decision-making, has become a necessity for most businesses in today’s competitive environment. In order to bring BI to small and mid-sized companies, there needs to be a set of affordable and easy-to-use tools at each company’s disposal. Microsoft has long had a vision and commitment to bringing the power of BI to the masses, and creating a set of tools and technologies to allow for self-service BI.
In order to realize this vision, Microsoft introduced the Business Intelligence Semantic Model (BISM), which supports two models, the traditional multidimensional model and a new tabular model. The tabular model is based on a relational table model that is familiar to DBAs, 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.
The goal of this blog series is to expose you to the process needed to create a BISM Tabular Model in SQL Server 2012 and deploy it to an Analysis Server where it can be exposed to client applications. The first part of this series covers setting up a tabular model project and importing data into the tabular model. Part II will look at table relations, implementing calculations, and creating measures with DAX. Part III will cover implementing semi-additive measures and securing the model, and Part IV will conclude the series by looking at how you deploy and connect to the tabular model from a client application.
SSAS supports both traditional multidimensional models using the MOLAP storage engine and tabular models using the new xVelocity engine. However, the same instance of SSAS cannot support both types of projects. If you need to support both multidimensional and tabular projects you must install a separate instance for each.
While the traditional MOLAP engine is optimized for multi-dimensional modeling and uses pre-built aggregation stored on disk, the xVelocity engine takes a different approach. XVelocity is an in-memory column store engine that combines data compression and scanning algorithms to deliver fast performance with no need for pre-built aggregation. In addition, since all aggregation occurs on the fly in memory, it avoids costly I/O reads to disk storage.
When to Use SSAS Tabular Model vs.Just Publishing of a PowerPivot Workbook?
Microsoft now offers several options within the BI platform (Figure 1), each targeted for the different types of BI analysis, from personal BI to corporate BI. As you move from personal BI implementation to team and corporate BI implementation, Tabular Models in SSAS provide a more robust solution in terms of scale, management, security, and development tools. For example, tabular models in SSAS do not have a hard upper data size limit. Tabular models also have partitioning, which is used to manage the processing of large data volumes. In terms of security PowerPivot is limited to the workbook level while tabular models hosted in SSAS support row level and dynamic security.
Figure 1 – Microsoft BI Semantic Model
Setting up the Development Environment
In order to create tabular data models, you need to install SQL Server Data Tools (formerly known as Business Intelligence Development Studio). You will also need an instance of SQL Server Analysis Services 2012 in tabular mode available to host the tabular model while it is being developed.
Installing SSAS 2012 is essentially the same whether you want to use the Multidimensional Mode (the default) or Tabular Mode. During the install, the wizard will ask which mode you want to install (Figure 2). Choose the Tabular Mode to install the xVelocity engine on the server.
Figure 2 –Choosing SSAS Tabular Mode
Later in the installation process you are asked which features you want to install. Make sure you select the SQL Server Data Tools (Figure 3). Be aware, it still has the old name, Business Intelligence Development Studio, in this version of SQL Server 2012.
Figure 3 – Installing the SQL Server Data Tools
In order to create a tabular model project, you need to launch an instance of SQL Server Data Tools. In the New Project dialog box, under Installed Templates, you select the Business Intelligence templates. Under the Analysis Services templates, you should see the Analysis Services Tabular Project template (Figure 4).
Figure 4 – Creating a Tabular Project
You can import data into a tabular model project from a variety of sources including relational databases, multidimensional cubes, text files, and data feeds. Under the Model menu and click “Import From Data Source”. This launches the Table Import Wizard (Figure 5).
Figure 5 – Choosing a Data Source
The Table Import Wizard guides you through the steps necessary to import the data. First, choose a data source and create a connection. The connection information required is determined by the type of connection used. Figure 6 shows the dialog for connecting to a SQL Server database. Use the Table Import Wizard to connect to the AdventureWorksDW2008R2 database (available at http://msftdbprodsamples.codeplex.com).
Figure 6 – Connecting to a SQL Server Database
After entering security credentials, you get the option of selecting data from a list of tables and views or entering a query to select the data. By selecting the tables and views option, you can select tables to import, automatically select related tables, and provide filters for the data import (Figure 7).
Figure 7 – Choosing Tables to Import
Selecting the query option allows you to create your own queries to retrieve data from tables, views, or stored procedures (Figure 8). You can also launch a pretty handy query designer to help construct your queries.
Figure 8 – Creating Your Own Data Import Queries
Using the Table Import Wizard, select the tables and fields shown in Figure 9.
Figure 9 – Table and Field Selection
After the data loads under the Model menu, select ModelView –> DiagramView. You should see a tabular model similar to the one shown in Figure 10. Save the project for use in Part II of this series.
Figure 10 – Tabular Model in the Diagram View window.
In this first portion of the four part series, you have seen how to create tabular model projects in SQL Server Data Tools. You also saw how to use the Data Import Wizard to import data into the tabular model. You should save this project for use in part two of this series where you will implement calculations and create measures with DAX.
It would be nice if you would cover at some point how to setup the SSIS project, which refreshes the data once everything is deployed in SSAS Tab.
Can you also please cover when Tabular would *not* be suitable? Our fact table is about 2TB and one of the dimension tables is very large. As I understand the entire contents are loaded into memory, which, even after xVelocity compression would require a very large amount of memory. Or will it?
Also, I echo Patrick's request - would be great if you commented on how to incrementally (or fully) refresh the Tabular model via ETL tools (we happen to use Informatica, but are not opposed to using SSIS for this task).
Thank you for sharing this business intelligence solution blog. This is very informative blog.
Reply to TheRomit:
As was mentioned above -
“tabular models in SSAS do not have a hard upper data size limit”.
When hosted in SSAS the data will be loaded in and out of memory depending on how it is being queried. However, the more memory you have on the SASS server the better.
Cathy Dumas shows how to do this in her blog -