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

 

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


By:   |   Last Updated: 2016-07-06   |   Comments   |   Related Tips: 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.

New SQL Server Analysis Services Tabular Project

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

Compatibility Level Configuration in Solution Explorer

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.

Data View for the SQL Server Analysis Services Tabular 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.

Table menu for a New Calculated Table

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.

Specify the Table Name as Calculated Table

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


Last Updated: 2016-07-06


get scripts

next tip button



About the author




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.



    



Learn more about SQL Server tools