Automate Testing SSAS Tabular

In real world SSAS Tabular projects, you need to run many different testing scenarios to prove your customer that the data in Tabular model is correct. If you are running a Tabular Model on top of a proper data warehouse then your life would be a bit easier than when you build your semantic model on top of an operational database. However it would be still a fairly time-consuming process to run many test cases on Tabular Model, then run similar tests on the data warehouse and compare the results. So your test cases always have two sides, one side is your source database that can be a data warehouse and the other side is the Tabular Model. There are many ways to test the system, you can browse your Tabular Model in Excel, connecting to your Data Warehouse in Excel and create pivot tables then compare the data coming from Tabular Model and the data coming from the Data Warehouse. But, for how many measures and dimensions you can do the above test in Excel?

The other way is to run DAX queries on Tabular Model side. If your source database is a SQL Server database, then you need to run T-SQL queries on the database side then match the results of both sides to prove the data in Tabular Model is correct.

In this post I’d like to share with you a way to automate the DAX queries to be run on a Tabular model.

Straight away, this is going to be a long post, so you can make or take a cup of coffee while enjoying your reading.

While I will not cover the other side, the source or the data warehouse side, it is worth to automate that part too as you can save heaps of times. I’m sure a similar process can be developed in SQL Server side, but, I leave that part for now. What I’m going to explain in this post is just one of many possible ways to generate and run DAX queries and store the results in SQL Server. Perhaps it is not perfect, but, it is a good starting point. If you have a better idea it would be great to share it with us in the comments section below this post.

Requirements

SQL Server Analysis Services Tabular 2016 and later (Compatibility Level 1200 and higher)
An instance of SQL Server
SQL Server Management Studio (SSMS)

How does it work

What I’m going to explain is very simple. I want to generate and run DAX queries and capture the results. The first step is to get all measures and their relevant dimensions, then I slice all the measures by all relevant dimensions and get the results. At the end I capture and store the results in a SQL Server temp table. Let’s think about a simple scenario:

you have just one measure, [Internet Sales], from ‘Internet Sales’ table
The measure is related to just one dimension, “Date” dimension
The “Date” dimension has only four columns, Year, Month, Year-Month and Date
you want to slice [Internet Sales] by Year, Month, Year-Month and Date

So you need to write four DAX queries as below:

EVALUATE
SUMMARIZE(
    'Internet Sales'
    , Date'[Calendar Year]
    , "Internet Sales", [Internet Total Sales]
)

EVALUATE
SUMMARIZE(
   'Internet Sales'
   , 'Date'[Month Name]
   , "Internet Sales", [Internet Total Sales]
)

EVALUATE
SUMMARIZE(
    'Internet Sales'
   , 'Date'[Year-Month]
   , "Internet Sales", [Internet Total Sales]
)

EVALUATE
SUMMARIZE(
     'Internet Sales'
    , 'Date'[Date]
    , "Internet Sales", [Internet Total Sales]
)

It is easy isn’t it? But, wait. What if you have 10 measures related to 4 dimension and each dimension has 10 columns? That sounds laborious doesn’t it? Well, in real world scenarios you won’t slice all measures by all relevant dimensions, but, you still need to do a lot. What we are going to do is to generate and run the DAX queries and store the results in a table in SQL Server. How cool is that?

OK, this is how it works…

Creating a Linked Server for SSAS Tabular instance from SQL Server
Generating DAX queries using Tabular DMVs
Running the queries through Tabular model and getting/storing the results in a SQL Server temp table

Continue reading “Automate Testing SSAS Tabular Models” →

DAX measures are the heart of every SSAS Tabular model, Power BI and Power Pivot solution. You write lots of DAX measures and you potentially reference some of them in other measures. So the number of DAX measures you write and reference them via other measures grow very quickly. Especially in complex solutions you may have hundreds of DAX measures. While your solution works perfectly, to make a minor change or adding a new measure to the solution or fixing a problem in your existing measures can be such a pain in the neck. In this post I’m going to take a step further and show you a simple way to get the whole data model dependencies then visualise the dependencies in Power BI. You can find the download link at the end of this post.

A simple search in Google brings you a bunch of useful articles talking about the subject. Some of the bests, in my mind, are as below:

In this post I use a DMV that gives us everything we want. ( Chris Webb already discussed the DMV here: Document Dependencies Between DAX Calculations). Running the DMV we can see what measures are references by other measures, what columns are referenced in the calculated columns and much more.

This is a very useful DMV that helps us getting a better understanding of the model we’re working on. We can also use this method for documentation.

How It Works

This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.

An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:

Open SSMS
Select “Analysis Services” as “Server Type”
Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”

Continue reading “DAX Measure Dependencies in SSAS Tabular and Power BI” →

What are DMVs?

Dynamic Management Views, DMVs in short, are queries that retrieve metadata information about an instance of SQL Server Analysis Services. DMVs work on both SSAS Multidimensional and SSAS Tabular server modes. DMVs can be used to monitor server operations and health. The DMV query structure is very similar to T-SQL, therefore you use “SELECT” statement followed by “$System” which is an XMLA schema rowset. The DMV queries look like below:

SELECT * FROM $System.<schemaRowset>

So you can open SQL Server Management Studio, connect to an instance of SSAS (Tabular model for the sake of this post) and run the following query to get lots of information about tables in your Tabular model:

select * from $SYSTEM.TMSCHEMA_TABLES

Note:This method only works with SSAS Tabular 2016 and above.

Document SSAS Tabular without DMVs

In SSAS Tabular 2016 and above there is a tiny metadata database that can be loaded in Power BI Desktop or Excel to document the corresponding SSAS Tabular model. The database is a SQLite database. In the previous post I explained how to visualise SQLite data in Power BI. The requirements for this post are the same as the previous post, so I encourage you to check it out. Therefore, I just explain how to find the metadata file and how to build a model in Power BI Desktop. I also explain how to do the same in Excel for those of you who would like to add some annotations or comments to the outcomes.

Tag: DMV

Automate Testing SSAS Tabular Models

Requirements

How does it work

Quick Tips: Boolean Conditions when Querying SSAS DMVs

DAX Measure Dependencies in SSAS Tabular and Power BI

How It Works

SSAS Tabular Model Documenter with Power BI without DMVs

What are DMVs?

Document SSAS Tabular without DMVs

Requirements

How does it work

Share this:

Share this:

How It Works

Share this:

What are DMVs?

Document SSAS Tabular without DMVs

Share this: