Tag: SSAS Tabular

BISM Tabular, Business Intelligence - BI, Dynamic Management Views (DMV), Power BI, Quick Tips, SQL Server

Quick Tips: Boolean Conditions when Querying SSAS DMVs

Boolean Comparison in SSAS DMVs, Error: A Boolean expression is not allowed in the context

If you are querying SSAS DMVs you may want to add some conditions in the query.

Something like getting all active relationships, perhaps like below:

select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS where IsActive = 'true'

Running the above query on an instance of SSAS Tabular gives you the following error message:

Error: A Boolean expression is not allowed in the context …

Fixing this is quite easy, run the below query to get active relationships:

select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS where IsActive

Boolean Comparison in SSAS DMVs

And to get inactive relationships run this one:

select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS where not IsActive

Boolean Comparison in SSAS DMVs

Azure, Azure Analysis Services, BISM Tabular, Business Intelligence - BI, Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power Query, SQL Server, SQL Server Analysis Services - SSAS, T-SQL

Time Dimension in Power BI and SSAS Tabular Model Supporting Minutes Time Bands

2018-05-23 12_58_48-Symbols (Open in Visio).vsdx - Visio Professional

Date dimension has been discussed quite a lot on the Internet and you can find lots of valuable articles around it here and there. But what if you need to analyse your data in time level? A customer has a requirement to analyse their data in Minutes level. This means that the granularity of the fact table would be at minute level. So, if they store the data in their transactional database in seconds level, then we need to aggregate that data to minutes level. I don’t want to go there, just bear in mind that the granularity of your fact table is something that you must think about at the very first steps. In most cases, if not all cases, you’d be better to have a separate Time dimension. Then you need to have a TimeID or Time column in your fact table to be able to create a relationship between the Time dimension and the fact table. In this post I show you two ways to create Time dimension in Power BI:

  • Creating Time dimension with DAX
  • Creating Time dimension with Power Query (M)

Alternatively, you can take care of the Time dimension in the source system like SQL Server. Continue reading and you’ll find a T-SQL codes as complementary.

The techniques that I explain here can be done in SSAS Tabular model and Azure Analysis Services as well.

Requirements:

To follow the steps of building the test model you need to have:

  • Power BI Desktop: Download the latest version from here
  • A sample fact table containing time or datetime. I modified FactInternetSales from AdventureWorksDW and made it available for you to download in Excel format (find the download link at the bottom of the post)

Continue reading “Time Dimension in Power BI and SSAS Tabular Model Supporting Minutes Time Bands” →

BISM Tabular, Business Intelligence - BI, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS

DAX Measure Dependencies in SSAS Tabular and Power BI

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”

Connect to Power BI Desktop Model from SSMS

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

BISM Tabular, Business Intelligence - BI, Power BI, Power BI Desktop

Demystifying “DirectQuery” and “Connect Live”

The terms “DirectQuery” and “Connect Live” are somehow confusing. I saw lots of people are using both terminologies as alternatives. But, the context of “DirectQuery” and “Connect Live” are very different indeed. Therefore, if use a a terminology when we’re talking about a different context then the whole situation might get quite confusing. in this post I try to explain the differences and make it more clear to prevent using a wrong terminology and make sure everyone is on the same page when we’re referring to “DirectQuery” or “Connect Live”.

When we use the “DirectQuery” terminology we are actually talking about connecting from Power BI Desktop instance to an RDBMS type of data source like SQL Server DB or Oracle DB.

There are two types of data connections when we’re connecting to RDBMS like SQL Server or Oracle DB from Power BI Desktop:

  • Import Data: which literally loads data into the underlying model to make it available in memory
  • DirectQuery: which doesn’t load data into model. Instead, it runs multiple concurrent queries on the RDBMS side (data source side) and gets the results. This is good to support real-time data processing.

Note: The same principal applies to SSAS Tabular.

DirectQuery/Data Import Mode in Power BI Desktop

On the other hand, when talk about “Connect Live”, we are referring to the data connection type from a reporting tool like Power BI Desktop OR Excel to an instance of SSAS, either SSAS Multidimensional or SSAS Tabular.

Continue reading “Demystifying “DirectQuery” and “Connect Live”” →