New SQL Server Tabular Projects for Analysis Services

written by Daniel Calbimonte June 20, 2012 0 comments

Problem

Many people have stayed away from using SQL Server Analysis Services, because it takes a lot of time to understand the basics to get started. You need to understand the concepts of Dimensions, Measures, Hierarchies, Fact Tables and a lot of other new items that don’t translate easily if you have been primarily working with just the SQL Server database engine.

In SQL Server 2012, Microsoft introduced a new type of Analysis Services mode called Tabular Mode which is much easier to setup and get started with.

Solution

Tabular Mode is a different engine and architecture compared to the traditional Multidimensional model. This engine compresses and stores the data in memory at runtime. Tabular Mode is also faster and easier to create than the traditional Analysis Services Multidimensional Mode.

The structure used in Tabular Mode lets us create hierarchies, KPIs and reports and the structure is created to generate reports easily.

Installation

Unfortunately when you install Analysis Services you need to choose either the Multidimensional and Data Mining Mode or the Tabular Mode, you cannot have both on the same instance. Also, earlier versions of SQL Server do not support Tabular Mode this is new for SQL Server 2012.

When you install a new Analysis Services instance, the Analysis Services Configuration lets you install the Tabular Mode as shown below.

Getting Started With Tabular Mode

Let’s start Microsoft Visual Studio 2010
In the Visual Studio Menu, go to File, New Project and select Analysis Services Tabular Project.
If you receive the following message: “You cannot open the BIM file. Reason: the workspace database server is not running in tabular mode”, go to step 4, otherwise go to the step 5.
In Solution Explorer, go to the Model.bim, double click on it and in the Workspace Server property specify the instance that is setup for Tabular Mode.
Click the Import Data Icon to import data into the Tabular Project.
In the Table Import Wizard, select the source of data that you want to use in your tabular project. In this example, a Microsoft SQL Server database will be used.
In the Table Import Wizard specify the SQL Server name, the authentication and the database to be used.
In the Choose How to Import the Data, select the option: “Select from a list of tables and views to choose the data to import”.
Select the tables that you want to include in your project and click Finish.
Once the Importing process is complete, click Close.
Now your Tabular Project is ready for reports, queries, exporting data to Excel, etc…