- To transform the raw master data into visual information for business purposes
- To gather statistical and quantitative analysis of the master data
- To perform data aggregation according to certain criteria (e.g. Supplier, Region) and present the results in a visual manner
MDM DB Views
A real life scenario
- Provide a visual report on the number of products per manufacturer.
- Provide a visual report on the number of products per customer country.
Step 1: Generating MDM DB Views
RepViewsGenerate <ServerName> <RepositoryName>;<DB_Name>;<DB_Type_Letter>;<DB_UserName>;<DB_Password> <Repository_UserName>:<Repository_Password> “*”
CLIX RepViewsGenerate MYSERVER MyRepository;MYMSSQL;S;sa;pass Admin:adminpass "*"
Step 2: Connecting to the MDM DB Views with SAP Lumira
Configuring a JDBC driver for SAP Lumira
- SAP Lumira Desktop Standard Edition
- A JDBC driver for my DBMS
Connecting to the MDM Main Table View
- I start a new SAP Lumira document.
- In the Data Source selection screen, I select FreeHand SQL.
- I select my DBMS type. The connection icon should be green if the JDBC driver is configured correctly.
- I enter the DBMS login parameters and click ‘Validate connection’. After the validation has passed successfully, I click ‘Acquire’.
- If the validation step failed, the issue might be wrong login details (Server Name, Server Port, Username and Password) or a problem with the JDBC driver or network.
- The next dialog box displays a ‘query’ box, but there is no need to write a SQL query to get the data. I expand the catalog in the upper left corner and look for my repository name with an ‘_MXXX’ suffix (usually _M000).
- Once I find my repository database, I expand it and look for the view I need (in my example the data resides in the view M_Products_1_0) and double-click the name of the view.
- I click ‘Preview Data’ to fetch a preview of the main table data.
- The menu below shows the column selection for columns that will be used by SAP Lumira. The default selection will fetch the data of all columns of the MDM main table into SAP Lumira; however, it is unnecessary for me to fetch all of them since I only require the 3 fields I mentioned earlier, ‘Part Number’, ‘Manufacturer’, and ‘Customer_Country’.
- I select the 3 columns that I need and click ‘Acquire’ and continue to design my visual report.
Step 3: Preparing the Reports in SAP Lumira
A visual report on the number of products per manufacturer
- I click on the ‘Pies’ icon’s drop-down menu and select ‘Pie Chart’.
- I drag the new ‘Part Number’ measure I defined into the ‘Pie Sectors’ box under ‘Measures’ and drag the ‘Manufacturer’ attribute into the ‘Legend Color’ under ‘Dimensions’.
A visual report on the number of products per customer country
In order for me to display the attribute in geographic format I need to create a geographic hierarchy in the following way:
- I right-click the ‘Customer_Country’ attribute and select ‘Create a geographic hierarchy’.
- Two options are presented, ‘By Name’ and ‘By Latitude/Longitude’. Since the data is country names, I select the first option.
- The next window presents the types of geographical locations SAP Lumira can use to present the data. My data is countries, so I’ll select the ‘Country’ option. I can also choose between presenting all the values or only those values to which SAP Lumira could match a country.
- SAP Lumira will now match the data in my repository with the known countries. All the values in my repository were matched to existing countries.
- Once the matching is completed, a new geographic hierarchy called ‘Country’ is created and added in the Hierarchies menu.
- Now I select ‘Geo choropleth chart’ and in it I place the following values:
- I drag the ‘Part Number’ measure into the ‘Value’ box under ‘Measures’ and drag the new geographic hierarchy ‘Country’ into the ‘Geography’ box under ‘Dimensions’.
The result is a map that maps the number of products per customer country. I can clearly see that my products are sold mainly in the US and EMEA. I might need to improve my product availability in the other countries 🙂
Combining the two reports
I completed my two main tasks and sent my reports to my manager. I was then asked if there is a way to combine the two reports and create a visual report on the number of products per customer country and for each country to present the number of products per manufacturer.
Using the measures, attributes, and geographic hierarchies I created, I can accomplish the task easily. I’ll show two ways to present this report.
This scenario is almost identical to the Geo choropleth chart that I created earlier; the difference is that instead of creating a Geo choropleth chart I select ‘Geo Pie chart’. The second change is that I drag the ‘Manufacturer’ attribute into the ‘Overlay Data’ box under ‘Dimensions’.
That’s it! I now have a Geo Pie chart in which each country also contains a separate pie chart for the manufacturer in the products sold in this country.
I can now recognize trends in my repository; for example, most of the products bought in Asia are from the manufacturer SAP AG and most of the products bought in Europe are from the manufacturer Apple.
The second option to present this report is in the ‘Maps’ visualization. I use the ‘Tree map’ option and I drag the ‘Part Number’ into the ‘Area Weight’ box and the ‘Country’ geographic hierarchy and ‘Manufacturer’ attributes into the ‘Area Name’ box under ‘Dimensions’.
This displays the same results as the previous chart. In my opinion this method is less attractive but can provide information easily if drill down into the data is needed.
Row Data Statistics
SAP Lumira can present statistics on the master data as row data in tables. This is useful when the exact data count is needed. In the following example I can extract the exact number of manufacturers and customer countries per product
I switch to the ‘Data’ view and select the ‘Facets’ view.
Next I need to choose the measure according to which the facet tables will be created; I select the ‘Part Number’ measure.
Facet tables are now created for the other two attributes. I can now see the exact number of manufacturers and customer countries per product:
I can now filter the results further by double clicking the values in the facet tables; for example, if I click ‘SAP AG’ I can see the division of customer countries for this specific vendor only:
Reusing the reports
An important subject I want to elaborate on is the reusability of the reports. Once I have saved my reports, I can reuse them as long as the schema of my repository is not changed. All I need to do is to run the ‘Refresh document’ command from the ‘Data’ menu, and SAP Lumira will fetch the new data from my database and update the report automatically.
Publishing the results
Once I complete all the reports and save them, I can share them in several ways. The first step is to switch from the prepared view to the shared view.
It will display all the options to share the data sets and visualizations I created:
Visualizations can be published via emails or on SAP StreamWorks.
Datasets can also be published via SAP HANA, SAP BusinessObjects Explorer, Lumira Cloud or a csv file.
In this article I’ve shown a small portion of what can be done by combining SAP NW MDM and SAP Lumira. My examples were on a relatively simple data set but this doesn’t mean that complex data sets can’t be extracted. For example several joint queries on MDM DB views can provide complex data sets which include MDM hierarchies, tuples, etc.
More information on SAP Lumira can be found in the SAP Lumira user guide at:
More information on DB views can be found in the MDM 7.1 SP10 Console Reference Guide in the section “Generating and Deleting MDM DB Views”.