How to create calculated tables in a tabular SQL Server Analysis Services SSAS model

By: Siddharth Mehta   |   Updated: 2016-07-06   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Analysis Services Development

Problem

In a Tabular SQL Server Analysis Services (SSAS) model, data is modeled to support the analytics requirement in alignment with the data sourced from different source systems. The tabular model may inherit the model of the source system up to an extent. To meet the analytic requirements, derived or calculated objects may be required. If the model does not support the creation of calculated database objects and only allows importing an exact clone of a database object from a source system, the only solution left would be to create calculated objects required for the tabular model into the source system. In this tip we will look at how to address this issue using a SSAS 2016 Tabular model.

Solution

We will assume you already have SSAS 2016 Tabular as well as SQL Server Data Tools (SSDT) installed on a development machine where you will be performing this exercise. In this tip we will look at how to create calculated tables in a SSAS 2016 tabular model using SSDT. Let's walk through the steps below:

1: Create a new SSAS Tabular project using SSDT.

2: Select the model file from the Solution Explorer and ensure that the Compatibility Level is 1200 (i.e. SQL Server 2016).

3: Import a table from an external data source, so that we can use it later as the base table to create a calculated table. Assuming that at least one table has already been created in the model, switch to the data view of the model.

4: Click on the Table menu in SSDT, and you should be able to find a menu item "New Calculated Table". Click on this and this should bring up a new screen.

5: The calculated table editor screen should look similar to the one below. You can enter a DAX expression in the formula pane to create a calculated table. A simple way to test the calculated table feature is to create a copy of an existing table. I have a table named tblUser already added to the model. Entering the DAX expression =tblUser will create a copy of this table and a new calculated table will be created. You can also edit the properties of the calculated table from the properties window.

After deployment there is no difference between a table imported from a data source and a calculated table. In this way SSAS 2016 and SSDT can be used to create calculated tables which can serve as role-playing dimensions, views, or any derived database object required for analytics.

Next Steps

• Try to create more complex DAX expressions and create more calculated tables to test the potential use in a tabular data model.
• Read more SSAS tips

About the author

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

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

View all my tips