Great news! We have enhanced the SAC BI Calculations by supporting Page/story filters in Measure based Calculated dimension.
With the latest releases of 2021.19 for the SAC fast track customers (BI weekly release model) and for Quarterly release cycle with 2021 Q4 QRC, SAP Analytics cloud is improving the BI calculations performance by supporting page/story filters in Measure Based Calculated Dimension.
Measure Based Calculated Dimensions (MBCD) are part of SAC BI Story Calculations. When you are working with huge number of Dimension members and it may be helpful to group them based on a measure. For Example, when you are analyzing the Sales data with sales mangers on how much revenue they are bringing in. In the Below screenshot shows Sales Revenue for different Sales Managers.
You can use Measure based Calculated dimension to group them as High, Medium and Low on Sales revenue. In the below screenshot, “Sales Manager Group” is a MBCD which groups all sales managers in High( >2 Million) , Medium ( >1 Million and < 2 Million ) and Low ( <1 Million) order.
Now, Sales Revenue is grouped in High, Medium and Low order for different Sales Managers.
Let’s consider a scenario to filter the above data for different years. You can add a Date Dimension as a page/story filter to include a condition, such as a 2014 filter.
Currently when the Measure-based Calculated Dimension is calculated, your existing story, page or widget filters are ignored.
In the below screenshot observe that Sales Revenue per Sales Manager values are filtered by 2014. But Measure Based Calculated Dimension values are not grouped correctly with High and Low. Chart shows some values less than 2 Million values are grouped to High instead of Medium. Kiran Raja and David Sales Revenue is less than 2 Million and they should be grouped to medium.
For MBCD to include the filters, you have to add a specific calculation input control for the viewer to select between different dates. This is cumbersome for the viewers as they would have to make that same selection twice. Ideally a designer can choose whether a story or page filter is used as filter context for a MBCD.
To address the above problem, A new option is introduced in the MBCD calculation editor to select Page/story filters as part of Filter context.
You can select the Date dimension in the Story/Page filter Context.
Now Sales Revenue per Sales Manager are Grouped properly for the year 2014. Ie., all values less than 1 Million are grouped to Low and values less than 2 million are grouped to medium.
Kiran and David are in Medium Group.
MBCD with Story Filter:
Measure based calculated dimension also supports a story filter. In the same example, you can add “Cost Pool” story filter to the Sales Manager Group MBCD calculation as part of filter context.
Now, all the story widgets which contains Sales Manager Group MBCD calculation, will filter with Cost Pool and Date values.
MBCD with Same Dimension used as Page and Story Filter:
If you are using a same dimension in Page filters and Story filters, both the values are displayed in the MBCD calculation editor and you can select both the values. For example: Time dimension is used as both Story and Page Filter.
If you select the story filter value as “2014” automatically the page filter also changes to 2014 due to the default cascading effect settings.
MBCD with Filter across Models:
Filter across models can be achieved when two dimensions are linked together using one dimension between two models. If you create an MBCD with Primary model, it will list only the Primary model dimensions as Page filters/Story Filters. Example: In the below chart, Sales Manager and Date dimensions are linked between two different models.
Note: For the secondary models, MBCD calculation will not list the existing Page/story filters. You can add Page/story filters in MBCD calculation to only Primary models.
Universal Model support:
If you are using universal model or a new model, MBCD is renamed as Value Based Calculated dimension. You can now create Value based Calculated dimensions and include Page/story filters for the account dimensions and Measures.
Example: Below table shows the Total assets, Amount Per Entity.
You can create a Value Based Dimension for Total Assets, Amount per Entities and Group them to High, Medium and Low category.
When you filter the table with Date 2017, Total Assets are filtered but not in Proper order. Medium Group shows the values less than 100 thousand.
Now, you can add date page filter in the Value Based Calculated dimension to consider the filter values.
Total Assets is grouped to Medium and Low category by including the filter value date=2018.
Optimized View mode:
Optimized View Mode addresses several usability challenges and performance improvements in the product.
MBCD with Page/filters are not currently supported in Optimized view mode. This will be addressed in future releases.
If you have created an MBCD with the Page/story filters and try to save in Optimized view mode, a warning message is displayed under the unsupported feature list. However, it will allow to save the story in Optimized view mode.
Once the story is saved in Optimized view mode, any widgets contain MBCD with Page/story filters are displayed as Ghost widgets with appropriate warning message.
Key points to remember:
- You can now include single or multiple Page/story filters in the MBCD calculation.
- Only selected page filters are considered in MBCD and in other pages Values are set to default behavior.
- MBCD with Page filters defined will respect the Linked Analysis. Ie., if you remove the page filters to link the widgets then MBCD in that widget will not consider the page filters.