A Personal Challenge
When SAP announced visual composer last year as the tool to build dashboards with a *** appeal for specific user’s needs they showed it first to us in the industries and asked us to build industry specific dashboards as first showcases. My boss heard ‘analytics’, took a look at his staff and picked me out to contribute because I have a background from financials, which he assumed to have something to do with analytics. So I received a 2 days crash course to learn how to build dashboards with visual composer and then could decide what to build for the industry.
Here I will report on the result of the considerations. I will leave out the dead ends and unnecessary detours I went will give you only the straightforward way. (I should add that I was by no means a BW expert when I began this task. But during the work I got acquainted with a lot of BW experts in SAP and learnt a lot…)
An Industry Challenge
In Metal Industries a product is often described using configuration characteristics (e.g. a metal sheet, with characteristics grade, length, width, thickness, type of coating). So the description of a specific product is only complete with the material master and the configuration characteristics. These characteristics are always specific to the individual implementation and therefore they can not be contained in delivered BI content. (BI content is example content that SAP delivers to enable a jump- start into BW reporting for customers) However configuration would be interesting for various analyses (e.g.: Show me for last month my sales figures in the US for metal sheets with zinc coating vs. uncoated sheets). This frequent request causes some challenges – and I will describe a possible solution.
Outline of The Procedure
In a first step, you need to determine in ERP backend (OLTP) which configuration characteristics should be extracted.
Also, the extraction of sales order data needs to be set up – and the configuration characteristics have to be extracted into a separate BW info cube, which have to be created up for this purpose. During extraction we will group the characteristics length into 5 separate groups instead of keeping each certain value. In a next step, you will have to join characteristics and sales figures into an ODS (operational data store). Since the configuration cube has the sales order number and sales order item as key, the configuration data will complete their respective sales order item data in the ODS.
As the last step we direct the ODS data further into an info cube. That gives the BW system the chance to aggregate the data along the dimensions of reporting since the key of the single order item is not longer contained in the cube.
Scheme of the data path:
As screenshot from the BW administrator’s workbench:
We will have a single, joint and aggregated BW info cube ready for use in reporting:
Result
Having done these steps, you will be able to answer questions in reporting via BW queries and visual composer like:
- What percentage of my sales was zinc coated vs. uncoated
- In which markets do I sell the highest share of high-grade products
- Show me the top 10 material and grade combinations or grade and length-group combination
- How did the sales of grade A materials evolve over the last 5 years, compared to total sales
This screenshot show how a sales order item configuration enters in a BW query. First the display in the ERP sales order:
And here you see the line in the query, in the line with the Order quantity of 255 LB
Following this approach, you will not have to do any modifications or programming in OLTP. The described steps allow to be applied flexibly to any classification data. Also you may combine configuration to any other providers containing sales order references. In a make- to-order scenario that would enable to join configuration data with production orders related data or inventories. This approach is also helpful in grouping characteristics, such as dimensions (e.g.: length 213 cm would show up in group length from 200 cm – 250 cm).
Another advantage of this solution: Changes in modeling can be performed without reloading data because of the pre-storages in ODS. Data storage in provider is performing well since single item data is aggregated according reporting needs.
Outlook
The implementation can be reviewed in the Mill Products demo scenario built into the IDES demo landscape. One interesting feature there: additional to the BW data we use also an ERP-Bapi to enrich the data wit sales order details directly from the OLTP.
Visual Composer Dashboards are ready for download from SAP service marketplace and built into IDES portal.
In a continuation I will give you more technical details how to set up the scenario in the different systems so that you can achieve the same in your own system landscape with low effort.
2 Comments
