Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

New SQL Server Tabular Projects for Analysis Services


By:   |   Updated: 2012-06-20   |   Comments (1)   |   Related: > Analysis Services Development

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.

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


Last Updated: 2012-06-20


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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.


Learn more about SQL Server tools