Supercharging Self-Service BI: Unleashing the Power of SQL Server Analysis Services (SSAS) Tabular Models

SQL Server Analysis Services (SSAS) Tabular models are in-memory databases that model the data with relational constructs such as tables and relationships, in order to provide a rapid and powerful way of providing self-service Business Intelligence (BI) to client applications such as Microsoft Excel and Microsoft Power BI. When SQL Server 2012 was launched by Microsoft, they introduced the tabular model for SSAS. This model utilizes an in-memory database powered by the velocity analytics engine and advanced compression algorithms. The tabular model can be seen as a fusion between a relational database in SQL Server and a multidimensional cube in SSAS. Like a database, it supports tables and their relationships, offering measures and key performance indicators (KPIs) like a cube. These components work together to create a highly efficient and speedy database capable of providing self-service BI to various applications such as Excel, PowerPivot, Power BI, and SQL Server Reporting Services (SSRS).

Tabular models in Analysis Services are in-memory or Direct Query mode databases that connect to relational data sources. The Vertipaq analytics engine in Analysis Services utilizes advanced compression algorithms and a multi-threaded query processor, enabling fast access to tabular model objects and data for reporting client applications like Power BI and Excel. Direct Query mode serves as an alternative query mode when the model is too large for in-memory storage or when data volatility requires a different processing strategy. Direct Query mode achieves feature parity with in-memory models, supporting various data sources, calculated tables and columns, row-level security via DAX expressions, and query optimizations for improved throughput.

Tabular models are designed using the Analysis Services projects extension in Microsoft Visual Studio. This extension provides a design surface where semantic model objects like tables, partitions, relationships, hierarchies, measures, and KPIs can be created. Deploying tabular models is possible to Power BI Premium, Azure Analysis Services, or an instance of SQL Server Analysis Services configured for Tabular server mode. Once deployed, the tabular models can be managed using SQL Server Management Studio (SSMS) or various other tools.

You can open Visual Studio and can select the option of creating a new project in Visual Studio as shown below.

In the second step we can add the tag of “analysis” in order to choose the Analysis Services Tabular Project. After selecting the option, you can click on next to proceed.

In the 3rd step, we define the project name and click Create to create the project in local repository.

In next step, you can see that the project has been created. On the right side of window, you have the option of data sources from where you can import the data through various sources

Next, you can right click on Data Sources and select New Data Source. In my case, we are importing from SQL server. As shown in figure below, you can select Microsoft SQL Server and click Next.

In the next step, as shown in figure below, you can provide the connection name, server name, and the database name. After doing so, you can test the connection and click Next.

In the 6th step, you can choose service account as you are working on local machine.

After selecting the service account for the tabular model to be processed, there will be the checklist for selecting the number of tables present in the specified database. We can select the tables required for our tabular cube.

By clicking Finish button from the previous step, the selected tables will be imported and processed with the data as shown below.

The figure below shows the number of imported tables in the tabular model. On the left window, we can see the processed data in the imported tables. Here we have multiple options to perform different functionality in the model.

We can create different measures to perform analysis on the specified tables. Perspectives are also there which are also known as data marts. We can manage partitions, relations, roles, and much more in our tabular model.

For deploying the model to analysis server in SSMS, we must choose the option Solution Explorer as mentioned in the figure below.

By selecting the Deploy option, we can deploy the model as object on SSAS Server.

We can see that the model has been deployed to the SSAS Server. We can view this inside SQL Server Management Studio. Here we can browse the database as tabular model and from where we can also connect the data to BI tools like Microsoft Power BI. Moreover, we can also process latest data directly from source to model to keep the cube up-to-date.

1. Future proof of your data size:

The number of records loaded into Power BI files can grow quickly as your business goes on. However, someday, you will encounter a limit for the amount of data that can be compressed in the memory of your PBI files. Currently, this limit is 1GB for a Standard Power BI version. The latest Power BI Premium version has no limit anymore, but that means you’re moving to another cost level. In SSAS Tabular, you are only limited by the size of the on-premises server’s RAM or by the amount of available Azure resources in case of cloud deployment.

2. Fine-grained security control:

SSAS Tabular gives you the possibility to define very precise and granular security rules on consumable data. With row-level security rules, data rows can be shielded from target audiences like external users or employees from other departments depending on your business requirements.

3. Boost consistency:

Developing Power BI files is an easy and fun thing to do. If you work in a large organization, it will not take long before your infrastructure will host a ton of Power BI files. Within these files are models that differ from each other ever so slightly. Table and column names may be different across PBI files, for instance. In this plausible scenario, there is a bigger chance that calculations and KPIs will be named differently and even that their definitions are scripted in another way. We’re only humans after all. These slightly differing models confuse your users when they look for the right file and data to use.

With SSAS Tabular, however, you can deploy a single model which can easily be queried from many Power BI files for analysis and reporting. The underlying base model will always be consistent. In SSAS Tabular model, definitions of calculations, KPIs, and calculated columns as well, will be defined consistently for all end applications using them.

4. Easier management of security access:

It may be time-consuming to change user access in case you have tens or hundreds of Power BI files located in on-premises file shares and in Power BI Service work folders. In the case of an SSAS Tabular model, you can regulate this by adding or removing a user from a role that can access the Tabular model.

5. Use other reporting tools:

Power BI and Excel are just great tools to use with SSAS Tabular model. They match perfectly. But if you want to use other reporting tools, such as SSRS, Tableau, Pyramid, Spotfire, and the like, then you’re set with SSAS Tabular Model.

SSAS is a powerful platform for creating and managing analytical models. It offers two primary modes: in-memory and Direct Query, providing flexibility based on data size and volatility. The in-memory mode utilizes state-of-the-art compression algorithms and a multithreaded query processor, enabling fast access to tabular model objects and data. Direct Query mode allows querying large datasets directly from the back-end relational data sources. SSAS tabular models are designed using tools like Microsoft Visual Studio and the Analysis Services projects extension, offering a comprehensive design surface for creating semantic model objects. These models can include tables, partitions, relationships, hierarchies, measures, and key performance indicators (KPIs).

Once created, tabular models can be deployed to various platforms such as Power BI, Azure Analysis Services, or a tabular server mode instance of SQL Server Analysis Services. Management and administration of deployed tabular models can be performed using SQL Server Management Studio or other compatible tools. Overall, SSAS empowers organizations to build efficient and scalable analytical solutions, delivering fast access to data for reporting and analysis through integration with popular client applications.