SQL Server 2012 offers two deployment modes for Analysis Services: multi-dimensional mode and tabular mode. While multi-dimensional mode has been widely used, tabular mode introduces a new enhancement to the analysis service database structure. In this article, we will discuss the benefits of using the tabular model and when it is appropriate to use it.

Why Use the Tabular Model?

The tabular model is designed to be user-friendly and empowers information workers. Here are some reasons why you might consider using the tabular model:

1. Easy to understand and implement, especially for non-technical users.
2. Uses DAX (Data Analysis Expressions) for scripting, which is similar to using Excel formulas and is faster to learn.
3. Utilizes the Vertipaq (x-velocity) engine for in-memory column storage, resulting in excellent performance and the ability to retrieve data and aggregates in real-time.
4. Allows for easy upgrade from Powerpivot models, providing a familiar tool for business users and IT professionals.
5. Compatible with client applications that support multi-dimensional mode, as it uses the same data provider that understands both MDX and DAX queries.

When Not to Use the Tabular Model?

While the tabular model offers many advantages, there are certain scenarios where it may not be the best choice:

1. When the data source is based on dimensional modeling and has complex relationships with a large volume of data.
2. When you require support for writing back or parent-child hierarchy.
3. When you need to perform complex calculations, scoping, and named sets.

Getting Started with the Tabular Model Project

To create a tabular model project, follow these steps:

1. Open SQL Server Data Tools and click on “New Project” under Business Intelligence > Analysis Services.
2. Select “Analysis Services Tabular Project” as the template.
3. Specify an instance of Analysis Services installed in tabular mode.
4. Import data from a source by going to “Model” in the toolbar menu and clicking “Import data from source”.
5. Choose the desired data source and enter the necessary credentials.
6. Select the tables and views you want to import or choose to include all related tables based on relationships.
7. Click “Finish” to start the import process.
8. Once the import process is complete, you can view the project in either grid or diagram view.
9. Right-click on a table to access various options for creating relationships, hierarchies, or hiding tables from client tools.

By following these steps, you can create a tabular model project and import data from a data source. The tabular model offers a user-friendly and efficient way to analyze data, making it a valuable tool for information workers.

Thank you for reading!