By: Daniel Calbimonte | 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.
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...
In this tip, you have learned how to create a Tabular Analysis Services project using data imported from a SQL Server database.
Next Steps
- Once your Tabular project is ready, you can now filter, aggregate the information and create KPIs
- Here is a sstep by step tutorial for tabular projects
- Here is more information about tabular projects
- Comparing Tabular and Multidimensional Solutions (SSAS)
- Stay tuned for future tips related to the new Analysis Services Tabular Mode
About the author
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
