Target Reader SAP Analytics Cloud: Business Users, Consultants
Required SAP Analytics Cloud Proficiency: BasicSAP Analytics Cloud allows business users to perform analysis leveraging transactional and dimension information coming from all sorts of data sources and source systems. The simplest way is to upload a worksheet or CSV file. With every advantage, there is a price to pay. A common challenge of using external dimension data is to find the right members in a fast manner, especially when the end user would use a planning table for input.
In this blog, I would like to show you in a real-life scenario some simple tips and tricks about how to find the dimension members needed in a faster manner with minimal data massaging effort.
The Analytic Scenario Overview
Previously I have created an SAP Analytics Cloud story to help analyze the energy intake by meals.
Now I am going to add some depth in this analysis and would like to understand the nutrition on meal and person granularity, so that I can see some reports like shown below.
For this, I would need a “Menu” or Dish entity if I don’t want to work on the ingredient level. In this culinary example, I will only deal with actual version of the menu.
I prepared a simple model where a dish menu can be constructed with a “Dish” dimension, an “Ingredient” dimension, besides the time and version dimensions. Each Ingredient, for example a certain kind of beef, would have various attributes which specify the nutrition composition, such as proteins, minerals, vitamins, etc. In each “Dish”, I would specify the standard portion weight of each ingredient. As a result, I could get like to have the nutrition table of each Dish.
The Challenge with Poor Master Data Quality
I searched for the nutrition information of food materials and was lucky to find a very rich data source which provides me over 200 thousand nutrition entries, after some reorganization of rows, I was able to get more than 6 thousand ingredients.
I uploaded the master data into my Ingredient dimension and began building the dishes. I was in an agile development mode and in my first iteration I simply took the text string descriptions as both ID and description in my dimension.
As can be seen below, while I was trying to add the “peanut oil” on a dish, I searched for the string “oil, peanut” and got more than 100 hits. All of them are related to the string and are returned by fuzzy algorithm. The search function is smart enough to do a lazy loading of 100 members to achieve optimal performance. However, none of these results was what I searched for. In this data set, there are too many ingredients which have “peanut” or “oil” in the ID. The same apply to generic ingredient such as “water”, “salt”, etc.
The lazy loading and fuzzy search are two methods SAP Analytics Cloud provides for best end user experience in most business cases. However, because of the homogeneity of my master data entries, it won’t help me get to the speed of prototyping I expected.
Since I booked facts on the model, my goal is to have a quick solution without disrupting all the existing models and data actions related to the dimension.
One Simple Solution
I tackled the situation with these simple steps:
- In order to enable guided search, I built a parent child hierarchy on the dimension according to the ingredient type on height 3 and alphabetic nodes on height 2.
- Out of many ingredients that highly reused, I created a table which allows me to reuse ingredients with booked facts, which emulates the behavior of a “frequently used” function.
Now I will show more details.
Create Parent-Child Hierarchy
When the end user views a high cardinality dimension, SAP Analytics Cloud will not load all the members in one go. With this behavior some functions will be deactivated since the JavaScript logic would need all data in those scenarios. To create this hierarchy, I would need to first “load all of the members”. Otherwise the option to create the hierarchy is grayed out on the UI.
To create the parent nodes, I employed some simple Excel formulas to work on the description of the ingredients. As a result, all leaves have now a valid semantics category, all categories will have a letter as parent.
After that, I reloaded the master data via an import job into my Ingredient dimension.
Some of the outliers of my ingredients cannot be uniformly categorized by the Excel formula, this is where the graphical hierarchy editing panel in SAP Analytics Cloud would come really handy. I adjusted these nodes within in minutes.
Already with simple hierarchy, I am able to drill into my master data while working on the story and find my ingredient within several clicks.
Below you can see how peanut oil finally shows up with 3 clicks and a bit scrolling.
Input Exact Member by “Pseudo-ID”
SAP Analytics Cloud allows us to search either by ID, by Description or by both. That means I can use the description attribute as a “Pseudo-ID” to input exact member without having to start from the root in a hierarchy each time.
For all ingredients, I give them Pseudo-ID starting with the letter “I” and for generated categories the letter “C”.
The question is then, how I can get the Pseudo-ID easily and persist it for future use. For this purpose, I created a table with a simple calculated constant as measure and displayed the Ingredient in a dimension. By searching a specific member, I will select “unbooked data” so that I can drill down to all the ingredients. By reusing a frequently used member, such as “salt” or a specific brand of ingredient, I will deselect the “unbooked data”, so that only booked ingredients are shown. I can now get the Pseudo-ID and by creating new dish I can skip the searching from root.
I pinned this table next to the input table for Dish as quick reference.
Now, I can either search in dimension input pop-up with the help of my hierarchy or input exact member with my pseudo-ID. Problem solved.
Summary
- SAP Analytics Cloud provides fuzzy search and returns up to 100 members as return.
- SAP Analytics Cloud uses lazy loading in various place to improve performance, end users should be aware of these situations.
- Having hierarchies can help one organize master data and navigate quickly to the needed members.
- The search of members with either ID or Description offers a great possibility to cure
The purpose of the simple solution is to allow me to fast be prototyping an analytic scenario. In a productive context, master data is important for analytic quality and efficiency. Thanks for your time reading it and let me know please if something mentioned here was helpful.
- SAP Managed Tags:
