DAX for Analysis Services Multidimensional workaround

I have already write a blog post about existing limitation when using Power BI live connected to Analysis Services Multidimensional here. But recently I found out workaround which used not to work but does work now (and hopefully it will stay this way). But first brief introduction of the business problem

(if you can’t wait just scroll down to workaround part).

Introduction

Analysis Services can be installed in 2 main flavours. Tabular which is used in Power BI backend (first version introduced in Power Pivot for Excel 2010) and Multidimensional much older then that.

Native language of Multidimensional is MDX, native for Tabular is DAX. 

In theory you can query both installation types using both languages and I do use MDX to query tabular a lot when developing Reporting Services reports.

And I also tried the other way around to query Multidimensional with DAX. And it works in management studio.

For approximately 3years I’ve been waiting for option to write custom DAX measures when live connected to SSAS MD.

I thought that something small has to be developed in order to make it work and it is not a priority. After digging deeper and talking to product team I found out it is exactly the opposite case. 

DAX works on tabular structures and Multidimensional structures are just way too different. So some principles like iteration over table in case of SUMX wouldn’t work and doesn’t make sense.

In better cases DAX just wouldn’t work in worse cases it would lead into unpredictable results.

So the team did extra work in order to block it in UI of Power BI Desktop.

But this blocking all DAX leads into cases where you need something simple like Sales in Thousands in Power BI table and you are forced to change calculate script of your enterprise SSAS

model for sake of Power BI report...

And this is unfortunate at least.

Word of caution

But before you’ll apply the workaround just keep in mind that in can lead to incorrect results when using more complex DAX or it simply won’t work.

If it doesn’t work for you don’t blame Microsoft and don’t blame me :)

Workaround

It is rather simple first create blank Power BI workbook connected to SSAS Multidimensional using live connection

If you try to create new measure right click measure group and you can just expand all/collapse all.

(And last few years I was just few minutes before collapse because of this limitation)

Then save the file and publish to Power BI Service. I named it SD_PRD_Main (Shared Dataset PRoDuction Main) PBIX

Then create second blank Power BI file and choose Power BI Dataset as a source

Search the list

Here you go :)

Conclusion

Use it with caution, more complex DAX doesn’t have to work as stated above. But simple stuff like Measure in thousands. Actual vs Target etc... These can be done directly on report level.

This approach has other benefits like having one centralized connection string which can be used in 20-50 and more reports.

So you just change one connection if you need to perform failover to different environment/server.

Only outstanding problem I now have is how to change existing reports in batch to map them from SSAS MD live connection to Power BI Dataset as a source.

I heard it could be possible using Power Shell but I wasn’t able to make it work.

Enjoy your report level calculations. If it doesn’t work for your don’t complain to Microsoft. Because if you do, they could block the feature for good ;)

DAX for SSAS Multidimensional

One of my major developer pains using Power BI is inability to create report level calculations in Power BI reports live connected to SQL Server Analysis Services Multidimensional. This affects my development for 2+ years and after trying to persuade product team as MVP with no success I decided to go public to support my cause and hopefully fix the problem for me and others by setting up Power BI Idea and let it vote up by public.

First, I would like to bring little bit of context. Currently I work on a project that started prior I joined the company approximately 5 years ago. Solution was built in SSAS multidimensional and is still extended even now. From SSAS we’re reporting using different technologies on Microsoft platform SSRS, ad hoc analysis in Excel and Live Connected Power BI reports.

Why live connection to cube?

Because of data size and shared business logic, that can be used in different reporting tools. Specifically measure definitions for Measures, Time related calculations, Time and Target Variances etc. Security model with Row Level Security. I don’t want to duplicate this logic in multiple data models using data import, because it would be a huge pain to maintain if logic changes for any of these. I need central definition, this is top to bottom BI Delivery.

Why not (yet) tabular?

As stated above, project started 5 years ago and Tabular by the time wasn’t there where it is now (not as Multidimensional). I agree, there were great improvements in Tabular in last 5 years and if I would stand in front of new project, I would probably take it as my first choice. But... The solution is already built. One of main motivators were time calculations stored as utility dimension (one definition working for all measures) and I know this is coming to SQL Server 2019, but I must use what I have in production now.

Truth is there is no straight forward way to migrate Multidimensional solution to Tabular without months of developer resources which we don’t have available, because new business requirements are coming. Even if we had time and resources, current solution contains a lot of logic in calculate script and I’m not 100% sure how I would model it in Tabular without refactoring DWH layer as well.

I’m sure there are other companies/consultants doing Top to Bottom BI Delivery and are stuck now with multidimensional. Please prove me right :-)

Why DAX doesn’t work against Multidimensional cubes?

And that is the point. DAX works against Multidimensional. Thanks to BISM (Business Intelligence Semantic Model) you can query Tabular and Multidimensional using MDX and you can query them by DAX as well. I’m personally using MDX against Tabular data models a lot when developing SSRS reports. And I know I can do it the other way around. It works.

So why it doesn’t work in Power BI?

Tabular works on totally different principals then Multidimensional. Multidimensional doesn’t understand concept of table and relationships as tabular interprets them. So totally different semantics of languages. Some DAX functions won’t work for Multidimensional as tabular. So Power BI team is blocking the functionality of DAX measures against SSAS Multidimensional on purpose from the beginning and had to implement UI changes to achieve that.

Problems with Power BI live connected to SSAS MD

I won’t cover here all the problems I have, just few examples. Even though formats work alright in Excel when connected to cube (Excel is client issuing MDX queries), measure is formatted in Excel correctly, but not in some Power BI visuals. Have a look at this percentage example (which is still valid by the way) https://community.powerbi.com/t5/Desktop/SSAS-Live-Connection-Formatting-Issue/td-p/413419

Chart displays numbers as decimal, data preview table correctly as percentage. This doesn’t happen every time and that is why it is painful. If formatting works or not can be impacted by logic inside of calculate script.

Second for multidimensional live connected models you can’t +/- expand matrix visual.

And the main one for me is, you can’t create measures and this is what this whole blog post is about.

If I need something simple like “Sales Ex Vat in Thousands” and I have in my cube Measure Sales Ex Vat only. I must create calculated member in cube (enterprise data model), divide the number there by 1000 for sake of one Power BI report.

This is wrong!

What would help?

Ability to create report level DAX measures.

I could create [Sales Amount Ths] = [Sales Amount] / 1000

If I have problem with [Margin %], I could create measure [Margin %_] = [Margin %] and format it on report level.

If I need to create variance [Actual]-[Target], I could do it without side effects.

Why not?

If I would need to write complex DAX, with function that could potentially not work or provide incorrect results (which is the assumed concern here). It is my responsibility as developer to dev and test the functionality.

If intellisense won’t work here, I don’t mind. I can write the formula outside of Power BI Desktop, copy and paste.

Even if it is feature which needs to be explicitly enabled to avoid misuse by random user, I’m happy with that.

Some functionality is better than no functionality.

Changing enterprise data models for sake of one report if the calculation could be derived from data in the report is wrong.

If you agree with me. If you miss the functionality. If you would like to help me from my pain. Please support this idea and share

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37477873-dax-for-ssas-multidimensional

Thank you

Jiri

Monitoring BI Solution using Power BI (presented on SQL Saturday Prague)

Intro:
Tento článek píši v angličtině. Téma jsem prezentoval na SQL Saturday Praha též v anglickém jazyce, kde byla značná část publika mluvící jiným jazykem (a nemám na mysli jen bratry a sestry ze Slovenska :) ) Dále již tedy v angličtině.

I will write this article in English and this is because I had a session on this topic on SQL Saturday Prague also covered in English. Significant part of audience was not speaking Czech (and I'm not talking about brothers and sisters from Slovakia only). Therefore rest of blog post in English

Blog post:
When doing anything it is good to have feedback if you are doing right. How to know if you are doing BI right? If your end users are using your outputs they are either happy with them (good for you) or they are using them because they have to (and if not happy with content and/or performance they will usually tell you).
If they have access to report and not using it? There is something wrong about it. Especially if you spent several weeks building output for particular user and now see zero usage. And that is it. Monitoring will tell you if your effort was successful or if you did all for nothing (paycheck will maybe ease your pain, but not completely).
So how do you get feedback about usage of your BI stuff?
By monitoring usage.
If your end user complains about slow SSRS report, what can you do about it? Improve it if you know the reason. How do you get to know reason?
By monitoring report executions. And analyzing performance related data.

In this blog post I will focus just on analyzing SSRS reports usage and OLAP usage (as on SQL Saturday)

SSRS
To analyze performance and usage you will need 3 tables in ReportServer database.
First of all
dbo.ConfigurationInfo - property ExecutionLogDyasKept needs to be changed from default 60 if you want to analyze data over longer period then 60 days
then you will need dbo.Catalog - list of reports, folders
ExecutionLogStorage - main table containing interesting stuff, can be joined to Catalog by connection Catalog.ItemId = ExecutionLogStorage.ReportId
At the end of this blog post will follow link to sample file created during presentation.
Tables described in data model:
Executions = ExecutionLogStorage
Reports = Catalog
OlapQueryLog = OlapQueryLog

DAX generated tables
Date = CALENDARAUTO()
Users = DISTINCT(UNION(DISTINCT('Executions'[UserName]),DISTINCT('OlapQueryLog'[MSOLAP_User])))
Measures of interest including DAX formulas to calculate it in blue
I'm interested in number of executions. Could be calculated in DAX as
Total Executions = COUNTROWS('Executions')

Also interested in Distinct Users of reports

Distinct Users = DISTINCTCOUNT('Executions'[UserName])

For performance troubleshooting we can break execution of SSRS report into

Time to return dataset (ExecutionLogStorage[TimeDataRetrieval])

Intermediate (format independent) report format creation contains data and layout, report level formulas ExecutionLogStorage[TimeProcessing]

Rendering (to specific format) - mhtml, Excel, Pdf, etc. ExecutionLogStorage[TimeRendering]

I would be also interested in BytesCount. If this number is high, execution on report server can be already finished on server, but it will take some time to render it on client (BytesCount then sent over network).

DAX Calculated column

Execution Time = (Executions[TimeRendering]+ 'Executions'[TimeDataRetrieval] + 'Executions'[TimeProcessing])/1000
Measures
Average Execution Time = AVERAGE('Executions'[Execution Time])
Average Data Retrieval = AVERAGE(Executions[TimeDataRetrieval])/1000
Average Data Rendering = AVERAGE(Executions[TimeRendering])/1000
Average Data Processing = AVERAGE(Executions[TimeProcessing])/1000

RequestTypeID 0 is adhoc execution, 1 is subscription.

OLAP usage analysis
For analysing OLAP usage you can enable loging on SSAS instance for both multidimensional and tabular. To avoid too many queries stored there is default sampling 10. Every tenth query will be stored. But to get general idea it is sufficient.

OLAP Executions = COUNTROWS('OlapQueryLog')

Then I can just create 2 common dimensions for calendar and distinct users (see DAX above). Create relationships between tables.

And create a report

Sample report is available here:

It can be downloaded here

Conclusion

This blog post was about providing resources to attendees of my session on SQL Saturday Prague. To other readers it should give idea how to monitor your BI landscape and get some information about usage and performance related metrics. File shared here is far from enterprise ready, but can be used straight ahead. Just change connections from localhost to your servers and remove last steps in Power Query transformations (I have to fake data, so I used first, last 4 for user names and report names).

If you want something finer tuned you can check out tool by my friends from Joyful Craftsmen (who participated heavily on SQL Saturday Prague). You can check out their tool here

Enjoy and if you have any feedback, let me know. 

Power BI – Live connection vs. Import, comparison and limitations

Some of the data sources have option both to import data as well as use live connection. But there is no easy method to change it once you already built your report. So you should rather carefully decide which method you’re going to use upfront. To make this decision right, it is good to know limitations of each method. Purpose of this blog post is to cover these limitations.

First what kind of license are you going to need? If you’ll use live connection, you’ll need Pro license for all end users consuming report/dashboard. If you’ll import data from on premises data source through gateway, you’ll need Pro License again. If your data are accessible without gateway, but you want to refresh them more often, then once a day, Pro License is required too. If you want to stay with free Power BI license, you’ll need to use data Import method only and avoid on premises data sources, refresh data maximally once a day.

Data import

Data import can be used against any data source type. Current Power BI service limitation published file size is 1 GB. Data are loaded to file using column store compression. If you’ll read for report purposes just data required for usage in visuals this is not limiting too much. Even with Power BI Pro license you are limited to 8 refreshes per day. Times can be scheduled either at full hour or half past full. Scheduled time is approximate. When will refresh actually happen depends on service utilization. If you need to refresh data, more often consider using live connection. When using import, data are stored in Power BI file/service. Therefore, there is no need to setup permissions on data source side (service account for load is enough) and you can share data publically or with people outside organization. On the other hand, all data are stored on Power BI. So if you have requirement for Row Level Security, you need to implement it on Power BI side. If you need same logic applied for multiple reports, you have to duplicate it in all the reports. Same applies to calculations, if you need same measure in multiple reports, you need to recreate it every time.

Live connection

There are more limitations for live connection in place. It doesn’t work against all data sources. Current list can be seen here https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#live-connections-and-directquery-to-on-premises-data-sources

You are also limited to just one data source/database you selected. You can’t combine data from multiple data sources anymore. If you are connected to SQL Database, you can still create logical relationships between objects from that database as well as measures and calculated columns. When you are connected to SQL Server Analysis Services, you are limited just to report layout and even can’t make calculated columns or measures. When connected to SSAS, you could also reach some licensing limitations. Power BI issues DAX queries against SSAS and this isn’t supported in Standard edition of SQL Server before SQL Server 2016. When using live connection, users have to have access to underlying data source. This means you can’t share outside of your organization or publically. Other way around, when you set up security once, all reports using live connection to the same source have the same security model applied automatically. Similarly, you can prepare your data model carefully with all measures and calculated columns on data source side. All reports from the same data source can benefit of it. Refresh frequency is unlimited. Power BI simply shows data as they are in database. However, be careful using live connection against highly transactional OLTP systems. If you suffer from performance problems now, analytical workloads from Power BI won’t help it. Consider enabling features on data source side like Operational Analytics in SQL Server 2016.

Summary

Both methods have limitations, developer should be aware upfront. For import it is mainly refresh frequency, data size and necessity to duplicate same logic (calculations, security) in multiple reports. That could be helped by templates, but if you need to change logic of calculation, you need to change it in multiple reports again. For live connection, not all data sources are supported. If you pick one, then you’re limited just to that one data source for report. If logic is setup on source side, all reports can benefit. My personal preference is use live connection against SSAS models, have the model centralized and maintain logic in one place. For SQL Databases where I don’t have model, I prefer import and use Power BI as a model and vizualization.

Different approach to Dynamic Row Level Security

There were already written few blog posts both about Row Level Security. I would like to add one more about this topic. I use this pattern for several years in SQL Server Analysis Services. Biggest advantage of this approach is, that you don’t have to fight with table relationships, which can be sometimes tricky to make work correctly.

To follow with steps, you can download sample file, which uses Adventure Works Sample Data.

File can be downloaded here:

https://drive.google.com/file/d/0B9ZohZ1CALKZOFZBRG9YZERWakk/view?usp=sharing

Introduction

Let’s have a look at the report and scenario. Adventure Works sales products, that are grouped to categories. Common requirement is to limit data in report, so category manager would see just his/her category. So if I want to grant access just to “Bikes” I could write DAX row filter

DAX formula:

[Category] = “Bikes”

This could work for few categories. But if you have 40. Role membership would be hard to maintain. Same thing can be handled by one role using configuration table.

Solution

For purpose of blog post, I will enter data manually using “Enter Data” option. You can replace data in table with real user names and email addresses. In real life I use for this configuration SQL Server Master Data Services, but any table would do.

Table security can stay unrelated

What I’ll need is to create calculated column to DimProduct table. Let’s call it Security_Category using function lookupvalue searching in security configuration table.

DAX formula

Security_Category = LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],"domain\username")

first column is the value I want to return, second is searched column, third value I want to find, fourth second column to be searched, fifth value.

Arguments 4,5 are here to restrict access to particular user if we had multiple users in same configuration table.

DAX filter needs to be evaluated as true value. I can check if anything was found by function ISBLANK function

DAX formula

Security_Category = ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],"domain\username"))

Formula returns now true, where records weren’t found. I need it oppositely and can switch logical value using function NOT

DAX formula

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],"domain\username")))

As next step I can create new role Dynamic Security in Power BI desktop and apply Row Filter on DimProduct table referencing calculated column with formula from previous step.

I can check what if visible to role members using View as role Dynamic Security.

To make it really dynamic, I will replace static value “domain\username” with function USERNAME() in formula.

DAX formula:

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],USERNAME())))

Function USERNAME behaves differently in desktop where it returns username in format “domain\username” and when published to powerbi.com where it returns email address username@domain.com

Therefore before publihing replace column USER with column EMAIL in DAX formula

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[EMAIL],USERNAME())))

Conclusion

In this blog post I covered how to implement dynamic security in Power BI without relationships in data model using DAX formulas. Let me know if you have any questions or comments

Jiri