SSAS, hiding Measures behind Calculations

Hi,

I have a cube containing 10 fact tables sharing a set of conformed dimensions. I'm thinking about hiding all measures behind Calculations to have the ability to set a custom Display Folder and not having the "measure" under the original measure group. My datawarehouse is very small, about 10 GB.

I want my user to drill across separate business processes and I don't want them to have to click 10 differents measures group to do that. The end result I expect is to have all measures in the same root and then, to group them with display folders in a way that make sense for users.

Is there a better way of doing this?

Thanks,

Alex

You should only use calculations when you need to. It's always preferable to use SQL for calculations than MDX (when possible).

Why not create views over your fact tables so you can group your measures in the folders you want?

Have you looked into perspective feature in SSAS and see if it will achieve what you want.

hang wrote:Have you looked into perspective feature in SSAS and see if it will achieve what you want.

Thanks for your reply, no perspective won't allow me to do that, I would still have to build Calculations over Measures. I'm also limited to MSSQL Standard Edition features for this project.

John Simon wrote:You should only use calculations when you need to. It's always preferable to use SQL for calculations than MDX (when possible).

Why not create views over your fact tables so you can group your measures in the folders you want?

I may misunderstood your point, but I already have one view for each table in the dimensional model. SSAS access to the relational database come through these views. 10 of those tables are fact tables or bridge tables and measures always appears under their respective measure group. The only way I found to change that, is to hide the measure and create a Calculation exposing the measure.

Are you suggesting to build one global view over my 10 fact tables to have all measures in the same Measure Group?

Thanks you for your time.

Now I see your issues. You cannot hide the underlying measures and just show the calculated measures as that will invalidate the calculated measures as well. I think that's something not quite right in SSAS. I guess John's suggestion makes sense if you can achieve the calculations in SQL, as you have already presented the measures through views anyway. You may need to trade off between MDX power and user interface.

My point is that I don't know if it's possible to achieve putting all of your measures in a single folder without creating a view over all of your fact tables which is obviously not ideal.
Also, MDX should not be used for leaf level calculations as it does not perform well, and can produce funny results with aggregates.

I think it's worth asking Darren Gosbell or Boyan Penev on the MSDN forums.

Hi Alexandre,

I think you might be on the right track to "build one global view over my 10 fact tables to have all measures in the same Measure Group".

This would probably be a 10-way UNION query so obviously a it's bit tricky to write the View. But once built it would not only solve your Measure presentation issues, but also dramatically simplify your cube definition - 10 x fewer objects to maintain e.g. Dimension Relationships, Measure definitions, Aggregation Designs. And hopefully less MDX code needed as well which is always a good thing IMO.

If you are using SQL Standard, you might even see an improvement in cube processing times - SSAS would currently be calling each of your 10 queries in series, whereas I think a SQL Union view would trigger more parallelism?

Your dimensionality will need to be very similar across the 10 fact tables (which you indicated). If not you can sometimes use a cludge for the odd missing relationship, pointing that fact data at a dimension's "Unknown" member via an expression in that section of your UNION query.

Good luck!
Mike

Thank you for quick anwsers everyone.

Building one view over my fact table is not something I would have think about, but I'm now considering it and I'm taking information about this solution. I will also post a question on msdn forum as suggested by John Simon.

Right now, My concern with this approach is for fact table that use a regular relationship to a dimension and other fact table that use many to many relation ship to this same dimension table. It may end up strange for the user, maybe not depending of how I design it. I will take a look in my Kimball books and do some research to do it correctly in the context of my project.

Just to make it clear:
Considering that under my cube, if I hide the measure X and I create a new member on the cube under the Calculation tab that just expose the measyre X , the result is going to be exactly what I want if I do not associated a measure group to this calculation. Doing this for every measures of all measure group will result with all Calculations appearing in the same folder. I tested it and it works.

This solution is really simple. Is the one view over all my fact tables worth it vs using the calculated members solution? Also, Is this practice considered as a good practice in BI development?

Also, I'm wondering about the comment "MDX should not be used for leaf level calculations" from John Simon, because I already have build leaf level MDX Calculations in my project. I will search the web about it, but if you are willing, feel free to share your concern.

Thank you.

Alexandre wrote:I'm wondering about the comment "MDX should not be used for leaf level calculations" from John Simon,

I am very sceptical about the comment too. I guess the performance is on rendering too many rows to the grid at detailed level, not on the aggregate calculations. If that's the case, the problem focus should be on the reporting methodology not the cube or MDX usage. I still believe MDX would by far outperform SQL for most OLAP specific functions. I would only use SQL to prepare the fact and dimension views in a way that MDX can be effectively leveraged and hence leave most of the aggregate and analytic calculations to MDX. However the idea of "one view for all facts" does not sound right to me.

Hi Alexandre,

I based my suggestion for a single view on your initial statement that you have "10 fact tables sharing a set of conformed dimensions". You have now clarified that your design is more complex with varying relationships between facts and dimensions. In that case a "single view" design will probably not work.

On the flip side, your determination to present all the measures in one folder might create some user confusion. The varying dimension relationships could result in differing results for measures from various groups against the various dimensions. In this situation the standard Measure Group structure can help to expose the natural data structure to the cube users.

Regarding leaf-level MDX, I'm with John. In addition to his concerns I'd add complexity and fragility of code - it is often difficult to correctly scope such calcs to cover all dimension combinations, and maintain that code to cover future dimension changes. Complex code then leads to complex test requirements.

Good luck!
Mike

Since your fact tables do not have the same dimensionality then I would advise against using a single view. I also echo the concern of having all measures in a single measure group as I think this will be more confusing to users.
Regarding leaf level calculations I’ll make two points.
Firstly, you get incorrect aggregations. For example, let’s say we have three measure attributes: Qty, Cost and Amount. If we use MDX to calculate Amount as Cost *Qty then we get the following results:

Product	Qty	Cost	Amount
A	1000	1	1000
B	1	1000	1000
Total	1001	1001	1002001

Notice that the total for Amount is 1002001 instead of 2000, because it has multiplied Qty * Cost? At the product level it works fine, but at the aggregate level the values are incorrect.
However if we use a Named Calculation to calculate Amount, then we don’t run into this issue.

Secondly, performance.

I’ll point you to my friend Boyan Penev’s blog. Boyan is a SQL Server MVP and an expert on SSAS. In his post http://www.bp-msbi.com/2010/07/using-the-dsv-to-its-full-potential/ he writes about this issue and says:

Leaf-level calculations
If we have the common requirement to perform leaf-level calculations and then aggregate this up the hierarchy, as opposed to aggregating and then calculating, the best way to do this is in a SQL statement on the fact table. Alternatively, we can do this in and MDX statement:

SUM(DESCENDANTS(Dim.CurrentMember,,LEAVES), MeasureCalc)

However, it comes at a price. Since SSAS would have to do the calculation for each leaf and then sum this up the hierarchy, this could take a long time to perform. Also, SSAS would not be able to use pre-processed aggregations and the calculations will be done at execution time. To avoid this we could add a new column to the fact table and do the calculation there (in SQL), using the column as a new measure in the cube, which can then be aggregated by SSAS as any other measure. The performance gain is usually substantial and using a Named Query or a Named Calculation should always be the preferred option.

Thanks for your time John,

I got your point for the concern of having a single measure group and I agree with you that most of the time it would not be a good idea. I will have a session with beta users very soon to see how they feel about it, but in this particular case, I’m confident that having separate measure group would be more confusing for them.

Thank you for the leaf level calculation problem demonstration, I was not aware of it. I'm using the ETL when possible, so currently I should have no MDX calculations that would cause this kind of issue. I will take an extra care to evaluate if this issue may apply in one of my MDX Calculations, but I’m using MDX when I have no other choice, mostly for averages, ratios and to create calculations using measures from different facts, always aggregating then calculating.

Thanks for your time all, that was instructive.