Automate Testing SSAS Tabular Models

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”

Exporting Power BI Data to SQL Server

 

Exporting Power BI Data to SQL Server

Update 2019 April: If you’re interested in exporting the data model from Power BI Desktop or Power BI Service to SQL Server check this out.

Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.

In the previous blog posts I explained how to export Power BI data to Excel and CSV here and here. As promised in this post I explain how to export data from Power BI Desktop to SQL Server.

Hans Peter Pfister has already explained how to export data from Power BI Desktop to SQL Server using R scripts. Although Hans has done a brilliant job, it might be a bit hard to make it work if you don’t have any R experience and you don’t even know how to install and call R libraries. That’s so true about myself, I’m NOT an R guy, but, who knows, maybe I will be. Smile 

But, what if you don’t want to go with R? If you are more involved with BI than analytics, then using R might not really be your cup of tea. Luckily, there is another way to export your Power BI data to SQL Server which is more BI friendly. You can export Power BI data to SQL Server using SSIS (SQL Server Integration Services). So if you are familiar with SSIS, then it might be your your preferred choice.

With respect to Hans, in this post, I explain his method of exporting data from Power BI Desktop to SQL Server more in details so that anyone who is not that familiar with R can make it work. I also explain how to export data from Power BI Desktop to SQL Server using SSIS. If there is any other methods you’re aware of please let me know in the comment section below.

Exporting Data from Power BI Desktop to SQL Server with R

As stated before, Hans has already explained this method here. So I don’t explain exactly what he did, but, I use his method to export data from existing Power BI Desktop model to SQL Server and I explain it step-by-step.

Requirements

To make this method work you need to:

  • Latest version of Power BI Desktop, you can download it from here
  • Have access to an instance of SQL Server, either on your own machine or on a server in your local network to export the data to
  • Either install R for Windows, you can download it from here OR using an existing R-Server OR install SQL Server 2016 R Services
  • Install RODBC library for R, you can download the library from here

Note: I haven’t installed R Studio and nothing went wrong.

Installing RODBC Library for R and SQL Server R Services

As mentioned earlier, you can install R OR SQL Server R Services OR R-Server, but, as I haven’t tried R-Server myself I just explain how to install RODBC in R and SQL Server R Services.

You have to download the library from the link provided above, then extract the contents of the zip file which contains a “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder exists in either R or SQL Server 2016 folders in your “Program Files” folder.

Library folder in R

Library folder in SQL Server 2016

How Does It Work?

Open an existing Power BI Desktop model that you’re willing to export its data to a SQL Server table and follow the steps below: (I use “Internet Sales” model created on top of AdventureWorksDW2016CTP3. You can download my Power BI Desktop model at the end of this post.)

Continue reading “Exporting Power BI Data to SQL Server”

Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

Dynamic_Measures_in_Card_Visual_-_Power_BI_Desktop

A while ago I was working on a Power BI project which the customer wanted to define a new table directly in the model. The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. The good news is that Power Query will be available in the next version of SSAS Tabular in SQL Server vNext. But, until then a workaround would be entering data to a CSV file then load it to the model. Another way is to implement custom tables in DAX in SQL Server 2016 Tabular models using Table and Row Constructors. In this post I show you a way of creating custom table in SSAS Tabular using table constructors in DAX. You can do the same in Power BI as the same principle applies. Therefore, in case you’d prefer not to use “Enter Data” feature which effectively uses Power Query to create a new table in Power BI Desktop, then you can use DAX to do the same.

Requirements

If don’t already have SQL Server 2016 it’s probably time to download and install it. I use AdventureWorksDW as sample database in this article.

Scenario

You are involved with an SSAS Tabular project and the customer asked for a report in Power BI with dynamic Card so that the values shown in the Card visual should dynamically change based on selected measure from a slicer. You have several different measures in the model and the customer wants to show some of them dynamically in only one Card visual. Consider you have the following measures to be shown in the Card:

  • Total Internet Sales
  • Internet Sales in 2014
  • Total Number of Internet Sales Transactions

You have to create a logic so that the users can selected any of the above measures to show in a single Card visual.

How it works

After you meet the requirements, you’re good to start implementing the above scenario in SQL Server Data Tool (SSDT). Creating a calculated table in SSAS Tabular 2016 is fairly easy. All we need to do is to create a custom table with two columns. One column stores friendly names for measures and the other one holds DAX expressions for the measures. As you might have noticed, I’m talking about creating a custom table in DAX and populating it with values. Continue reading to see how. What we are going to do is to create a calculated table using table constructors in DAX. Table and Row Constructors weren’t available in previous versions of DAX in SSAS Tabular. They are very similar to Lists or a list of Tuples just like what we have in MDX.

I’ll explain this later when we created our sample model in SSDT. Continue reading “Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX”

Query Azure SQL Data Warehouse in SSMS and SSDT

Azure SQL Data Warehouse in SSMS and SSTD

A while ago I wrote a blog post about Azure SQL Data Warehouse and Power BI which I explained how to install a new instance of Azure SQL Data Warehouse and how to visualise your Azure SQL DW data in Power BI. In this post I explain how to query an Azure SQL DW in SSMS and Visual Studio.

Requirements

Querying Azure SQL Data Warehouse from Visual Studio

Prior the latest release of SQL Server Management Studio (SSMS) 2016, the only available tool for querying an Azure SQL Data Warehouse was SQL Server Data Tools (SSDT) for Visual Studio 2013 or 2015. Here is how you can use SSDT 2015 to query an Azure SQL Data Warehouse:

  • Open SQL Server Data Tools 2015
  • Click “SQL Object Explorer” from View menu

SQL Server Object Explorer Visual Studio

  • Click “Add SQL Server”

Add Server to SQL Server Object Explorer Visual Studio

Connect to Azure SQL Data Warehouse in SQL Server Object Explorer Visual Studio

  • Enter “Server Name”
  • If you don’t recall server name then open a web browser and log into Azure portal
  • Click “SQL databases”
  • Click any desired Azure SQL Data Warehouse you created before. Make sure the database is “Online”

Azure SQL Data Warehouse in Azure Portal

Continue reading “Query Azure SQL Data Warehouse in SSMS and SSDT”

How to Download SQL Server 2016 Developer Edition for Free

Update1: As per April 2018, the process has slightly changes since I wrote this blog post. Now you can find it under “Downloads” tab, under “Servers” section. (Thanks to “David Shannon” for pointing this out.)

Update2: Click here to utilise the SQL Server installer. By clicking the link you’ll download an executable file “SQLServer2016-SSEI-Dev.EXE” produced by Microsoft that gives you three options:

  • “Basic”: To quickly install basic features like SQL Server Database Engine
  • “Custom”: You go through SQL Server installation wizard to choose what you want to install
  • “Download Media”: To download SQL Server setup files and install them later

SQL Server Developer Edition with SP2

A while ago Microsoft decided to provide Developer edition of SQL Server 2014 and 2016 for free. So we should be able to download SQL Server 2016 Developer Edition for free legitimately. If you search for it on the Internet you’ll quickly find out that it is NOT that straightforward. In this post you see how to download SQL Server 2016 Developer Edition for free legally.

But, what is the difference between SQL Server Developer Edition and any other editions? Well, generally speaking, the Developer edition has all features of an Enterprise edition, but, it’s not for commercial use. To learn more about different editions of SQL Server 2016 have a look at here. I encourage you to see this datasheet as well.

To be able to download SQL Server 2016 Developer Edition for free you need to have MSDN Subscription or you can join Visual Studio Dev Essentials.

Download SQL Server 2016 Developer Edition using MSDN Subscription:

  • If you do have an MSDN subscription you can easily download SQL Server 2016 Developer Edition for free. Just click here and download.

SQL Server 2016 Developer Edition from MSDN

Join Visual Studio Dev Essentials and Download SQL Server 2016 Developer Edition:

  • Joining Visual Studio Dev Essentials is free and simple. While you join Visual Studio Dev Essentials lots of benefits will get unlocked including a free SQL Server Developer Edition.
  • Click here to join Visual Studio Dev Essentials
  • Click “Join Now”

Joining Visual Studio Dev Essentials

Continue reading “How to Download SQL Server 2016 Developer Edition for Free”

Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters

Power BI Desktop and SQL Server Dynamic Data Masking

As I promised in my earlier post, in this article I show you how to leverage your Power BI Desktop model using Query Parameters on top of SQL Server 2016 Dynamic Data Masking (DDM). I also explain very briefly how to enable DDM on DimCustomer table from AdventureWorksDW2016CTP3 database. We will then create a Power BI Desktop model with Query Parameters on top of DimCustomer table. You will also learn how to create a Power BI Template so that you can use it in the future for deployment.

Note: If you want to learn about using a List output in Power BI Desktop Query Parameters have a look at the next post of these series “Power BI Desktop Query Parameters, Part 3, List Output“.

Use Cases

In the previous post I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. In this post you learn :

  1. Using Query Parameters on top of SQL Server Dynamic Data Masking (DDM)
  2. Query Parameters in Power BI Template

Requirements

Just like the Part1 of Power BI Query Parameters, you require to meet the following requirements to be able to follow this post:

  1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)
  2. SQL Server 2016 (You can download SQL Server 2016 Developer Edition for free)
  3. Adventure Works 2016 CTP3

Definitions

I’m not going to provide much details about DDM as you can find lots of information here. But, to make you a bit familiar with Dynamic Data Masking I explain it very briefly.

Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) is a new feature available in SQL Server 2016 and also Azure SQL Database. DDM is basically a way to prevent sensitive data to be exposed to non-privileged users. It is a data protection feature which hides sensitive data in the result set of a query. You can easily enable DDM on an existing table or enable it on a new table you’re creating. Suppose you have two groups of users in your retail database. Sales Persons and Sales Managers. You have a table of customers which in this post it is DimCustomer from AdventureWorksDW2016CTP3. This table contains sensitive data like customers’ email addresses, phone numbers and their residential adders. Based on your company policy, the members of Sales Persons group should NOT be able to see sensitive data, but, they should be able to all other data. On the other hand the members of Sales Managers group can see all customers’ data. To prevent Sales Persons to see sensitive data you can enable Dynamic Data Masking on the sensitive columns on DimCustomer table. In that case when a sales person queries the table he/she will see masked data. For instance he see uXXX@XXX.com rather than user@domain.com.

Create a table with DDM on some columns

It’s easy, just put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it should look like this:

CREATE TABLE Table_Name   (ID int IDENTITY PRIMARY KEY,    Masked_Column1 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),    Masked_Column2 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),

 

)

GO

Alter an existing table and enable DDM on desired columns

As you guessed you have to use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL should look like:

ALTER TABLE Table_Name ALTER COLUMN Column_Name1 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);

GO

ALTER TABLE Table_Name

ALTER COLUMN Column_Name2 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);

GO

For more information please refer to MSDN.

Power BI Template

A template is basically a Power BI file that represents an instance of a predefined Power BI Desktop which includes all definitions of the Data Model, Reports, Queries and parameters, but, not includes any data. Creating Power BI Templates is a great way to ease the deployment of existing models. Creating templates is very easy, you just click File –> Export –> Power BI Template. We will look at this more in details through this article.

Scenario

You are asked to implement a new level of security on customers’ data (DimCustomer on AdventureWorksDW2016CTP3 database) so that just privileged users can see the customers’ email, phone numbers and residential address. Privileged users are all members of “SalesManager” database role. You are also asked to prevent “SalesPerson” database role to see sensitive data. But, all members of both “SalesManager” and “SalesPerson” database roles can query DimCustomer table. The users should NOT have SQL Server logins.

Continue reading “Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters”