New SQL Server Tabular Projects for Analysis Services

By:   |   Comments (1)   |   Related: > Analysis Services Development


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.


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.


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.

Analysis Services Configuration

Getting Started With Tabular Mode

  1. Let's start Microsoft Visual Studio 2010
    Start the Visual Studio

  2. In the Visual Studio Menu, go to File, New Project and select Analysis Services Tabular Project.
    Tabular Model Error

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

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

    Solution Explorer

  5. Click the Import Data Icon to import data into the Tabular Project.
    Import Data from Data Source

  6. 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.
    Connect to a Data Source

  7. In the Table Import Wizard specify the SQL Server name, the authentication and the database to be used.
    Table Import Wizard

  8. 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".
    Choose How to Import the Data

  9. Select the tables that you want to include in your project and click Finish.
    Select Tables and Views

  10. Once the Importing process is complete, click Close.
    Importing data

  11. Now your Tabular Project is ready for reports, queries, exporting data to Excel, etc...
    The tabular project

In this tip, you have learned how to create a Tabular Analysis Services project using data imported from a SQL Server database.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 30, 2014 - 12:42:39 PM - SANDEEP Back To Top (34777)

Even after I change the analysis service server mode to tabular I'm still getting the error that u mentioned in step 3.

Also the BIM file's workspace server is the one in tabular mode only.

get free sql tips
agree to terms