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
Thank you
Jiri
Žádné komentáře:
Okomentovat