Blog Series: Creating BI Solutions with BISM Tabular (Part 1 of 4)

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.

Introduction

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.

Part I

What Makes Tabular Mode Different?

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.



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.



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.

Creating a Tabular Model Project

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).

Importing Data

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).



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).



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).



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.



Using the Table Import Wizard, select the tables and fields shown in Figure 9.





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.

Conclusion

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.