BubbleUp Exceptions

by Forrest Dermid , Sr. Solutions Engineer, Pyramid Analytics

Objective of Document: Explore one approach using a calculation to identify at the lowest level in the Microsoft SQL Server Analysis Services data model where a threshold has not been met also referred to as a “Bubble Up Exception”.  A common example is in grocery or retail industries where you are drilling into a product hierarchy with aggregate levels for a product profitability metric, say Profit Per Item.  In practice, this is not so easy a task to calculate and can require at the micro level cost allocations with expensive calculations (time to calculate) and is commonly known as ABC, or Activity-Based Costing.  ABC is beyond the scope of this document to explore (albeit very exciting). We will work with Margin for our examples.  At the aggregate level, the Margin calculation does not tell the whole story such as which individual product(s) are performing poorly or exceeding expectations.  How many product(s) are performing poorly and how do I bring attention to the problem aggregate levels and or products?  Is there a specific geographic region or sales team that trends worse than another? This document will explore both the multi-dimensional and in-memory model calculations to aid in identifying product hierarchy levels to explore and/or take corrective action using bubble up exceptions with Pyramid Analytics.  

Multi-Dimensional Models

  1.  Using New Data Discovery, connect to a multi-dimensional model.  I am working with a generic data model we use at Pyramid Analytics that has various dimensions and metrics.  A good model to work with is the demo data sets provided by Microsoft, AdventureWorks and can be found here. (http://msftdbprodsamples.codeplex.com/).
  2. Note the hierarchy in the product dimension Products and as a best practice I used the folder property during the design of the model to place the attributes in a folder called Attributes.  TIP: Using folders can significantly improve readability and keep your models in order, similar to how might maintain your computer C Drive.

  3. Drag the Product dimension to rows and the measures dimension to columns. 

  4. Double click the measures dimension and select Margin.

  5. At the aggregate level, note the Margin for 2008 is 24.14%.  Certainly, for your model you will have a different Margin.  As I drill down note how the ranges of the Margin vary depending on the Product level.

  6.  How would I know I had a poor Margin for Front Derailleur without drilling into this Product Category>Product Subcategory>Product?  How many other products are below a specific Margin level?  One approach is to use a calculation to bubble up the exceptions to the aggregate level counting where the condition exists.

  7. Select Analytics tab> Calc Designer from the ribbon> Custom Member.

  8. Using the Advanced Calculation Designer, we will write the following MDX: sum(descendants(products.currentmember, products.product), iif(measures.margin < .2, 1, null)).  

  9.  Select Test to be sure all is good and then OK.

  10.  If you have never used MDX, now is the time!  This may not sound cool like a British accent, but this is essential to growing your skill set as an analyst with this technology.

  11. Let’s explain what this calculation is doing.  Starting with the very last part of the calculation, if the Margin is less than 20% (.2), then the value will be 1, else null.  Nothing that complex, simple Excel formula!

  12. MDX uses the conditional IF and that is why we have IIF.

  13. We now need to apply the calculation at the aggregate level(s) in the Product dimension for the Products hierarchy.  So, DESCENDANTS is an MDX function and we are saying for the aggregate level(s) that exist in the Products hierarchy, Products.Currentmember and down to the lowest level in the Products hierarchy Products.Product, I want to SUM all the values for my logic. TIP: You can perform the calculation at any level you want to, just be sure to reference it correctly.  For labels that have spaces, MDX syntax will require you to put brackets around the label. E.g., if I wanted the calculation to be at the Product Subcategory level I would reference the level like: Products.[Product Subcategory].  You may want to always use brackets, but faster to type without them if not required.

  14. What does this look like?  Using the same grid, we add the newly created measure BUMarginDemo and we get the following.  

  15. We now know without any drilling, there are 97 products that have less than 20% Margin at the All Products level, but we also break that down to each Product level so we know exactly where to drill to identify each of the products by level by name.

  16. What if we wanted to make the threshold more dynamic?  There are several ways to accomplish this with the more relevant per level per dimension approach of creating the measure threshold in the source and then reference the measure in the calculation as an Average.  In our example, we are going to use a Pyramid Analytics Cell Overlay.

  17. Let’s right-click on the newly created measure BUMarginDemo and select Edit in Calculation Designer.

  18. Highlight the .2 value and select the Variables. 

  19. Select New Variable and enter a meaningful Caption, change the Variable Type to Text Box, and enter a default Value, select Save.

  20. Select the new Variable in the Variables list. 

  21.  Select Test to be sure everything is working correctly, then select OK.

  22. You can now enter a threshold to recalculate which products at the lowest level meet the condition.  In this example, I have change the varBUMarginDemo from .2 to .3.

  23. Yikes!! There are many products that are less than 30% margin, 275.  Each of the levels in the Products hierarchy would not necessarily have the same threshold to determine if there was a problem.  You could modify the calculation to test for various levels, attributes, etc., but this is beyond the scope of this document. 

  24.  If I want to filter by Country or any other dimension, drag the dimension to the Slices.

In-Memory Models

  1.  Using New Data Discovery, connect to an in-memory model. I am working with a generic in-memory data model we use at Pyramid Analytics that has various dimensions and metrics, but a different model than the multi-dimensional model we were using previously.
  2.  In-memory models use a different syntax referred to as DAX and cannot be used in the Advanced Calculation Designer.  However, for most of your calculations you can use the Pyramid Analytics Advanced Calculation Designer and let Pyramid do the syntax conversions for you.  Quite a benefit! 
  3. So, to do the same bubble up calculation in in-memory models use the same calculation as you used in the multi-dimensional models adjusted for the dimension hierarchy level names.

  4. Select Analytics tab> Calc Designer from the ribbon> Custom Member.

  5. Using the Advanced Calculation Designer, we will write the following MDX: sum(descendants(product.currentmember, product.itemname), iif(measures.[GM%] < .2, 1, null)).  

  6.  Notice in this example I used the Pyramid Analytics “Data Points” for the measure.  Instead of typing the measure as measures.[GM%], I used the Data Points and referenced the measure.  Might be handy especially when you want to “Scope” the calculation or restrict the calculation to a specific hierarchy level, attribute or member and not concern yourself with the syntax.  TIP: I would encourage you to never use special characters in label names (e.g., GM%) as they can cause you many problems.

  7. Select Test to ensure all is good then select OK.

  8. Add the newly created measure to the grid.

  9.  To aid in learning syntax, right-click on the newly created calculation and select Edit Custom Measure to see the syntax.  If the Data Point referenced a Pyramid Analytics calculated measure it will be displayed with a GUID.

  10. You may be more comfortable working with DAX vs. working with MDX.

  11. We are now going to do the same bubble up calculation, but with DAX.

  12. If the in-memory model was created with Pyramid Analytics, you can edit the model provided you have the correct permissions.

  13. From the tabs, select Modeling

  14. Select Edit Data Model. 

  15. The New Data Model dialogue will be displayed, select Next.

  16. No changes to the tables for the model, select Next.

  17. For the calculation, we will create two Custom Measures.  One will be GM DAX and the other will be GM DAX BULVL.

  18. Select the icon Custom Measure. 

  19. If you are new to DAX, Microsoft has an excellent resource located here for reference (https://msdn.microsoft.com/en-us/library/ee634396.aspx).  As you work with DAX I suggest using Microsoft Excel PowerPivot to test your expressions and then paste the working expression into the Pyramid Analytics Calculated Column or Custom Measure.

  20. In the Edit Custom Measure dialogue, enter the Name and Expression, select OK.             

  21.  Now select the icon Custom Measure again to create the GM DAX BULVL calculation.

  22. In the Edit Custom Measure dialogue, enter the Name and Expression, select OK.  The screen dialogue box does not expand so here is the expression:

           SumX ( Values ( ProductDim[ITEMNAME]), If ( [GM DAX], If ( [GM DAX] < .2, 1, 0 ) ) )

  23. Continue to select next to finish the model changes and process the model.  If there were DAX syntax errors the model will not process and you will need to go back and correct the expression(s).  TIP: You may want to do one expression at a time so if there is an error you know which one it is to work on.

  24.  Now select the newly created measures to add them to your grid. 

  25. The following measures were created through Pyramid Analytics Advanced Calculation Designer: GM% and BUMarginDemo.  The other two were the ones we just created using DAX and named accordingly. 

  26. Notice the two bubble up exception measures do not equal each other.  That is because the DAX expression is more correct and identifies the null whereas the MDX measure would need a conditional IF to handle the null value for Omega J-500Mx.

This concludes our Bubble-Up discussion.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
login to reply
Like2 Follow
  • 2 Liked by
  • 5 mths agoLast active
  • 335Views
  • 2 Following