SAP NetWeaver MDM customers enjoy many features and abilities of the product including content consolidation, master data harmonization, and many others.

However a customer may need to analyze the master data for any of the following reasons:

The ability to generate MDM DB views was officially added to the MDM product in the MDM 7.1 SP10 release. DB views provide the customer with a real time data representation of the MDM content via SQL views. An SQL view is generated for each MDM table, by using a simple CLIX command.

The views are read-only.

SAP Lumira is a data manipulation and visualization tool. Users can connect to various data sources, select and clean data, and manipulate and visualize data using a diverse set of graphical charts and tables.

In this article I will describe how to visualize the MDM data using SAP Lumira.

To demonstrate the capabilities of visualizing MDM data on SAP Lumira I’ll use a simple product repository.

I’ve already got data in the repository but I’ve been requested to provide analysis reports on the status of the repository’s products to upper management.

The request is:

In my repository I have the fields ‘Part Number’, ‘Manufacturer’ and ‘Customer_Country’ in the main table ‘Products’ of the repository.

‘Part Number’ uniquely identifies each product in the repository. ‘Manufacturer’ and ‘Customer_Country’ are the fields that I use to generate the reports.

Without the integration between SAP Lumira and NW MDM I’d have to query the data myself using MDM Data Manager or the MDM APIs, and then summarize and formalize the results, processes that could take hours and could lead to mistakes of data changed or incorrectly copied during one of the steps.

The integration of SAP Lumira with SAP MDM will generate the two reports in a matter of minutes.

SAP Lumira will query the data directly from the database using the MDM DB views in 3 easy steps.

MDM DB views are generated using the CLIX command, RepViewsGenerate.

This command generates an SQL view for the MDM data tables and each MDM table will be represented by a single view with all the display fields as the SQL view fields.

The syntax of the command is:

In my example my repository name is MyRepository, it is mounted on an MS SQL server called MYMSSQL and my MDM server name is MYSERVER so I’ve used the following syntax:

CLIX RepViewsGenerate MYSERVER MyRepository;MYMSSQL;S;sa;pass Admin:adminpass "*"

Once I’ve executed the command the MDM DB views will be generated in my database, and each table is now represented by a single view.

My assignment is to generate analytic reports on the main table Products, so I’ll use the corresponding view M_Products_1_0.

Once I’ve generated the views my next step is to connect to the DBMS using SAP Lumira using the following 2 components:

I’m using MS SQL server 2008 so I’ve used Microsoft SQL Server JDBC Driver 3.0.

Once I’ve configured the JDBC driver in SAP Lumira (File -> Preferences -> FreeHand SQL) and restarted the application, I’m ready to connect to the MDM main table view.

Connecting to the MDM main table view is done in the following way:

In the SAP Lumira’s preparation screen I select the ‘Visualize’ view.

In the view I can see my 3 fields in the ‘Attribute’ column.

I select the field ‘Part Number’ so that I can uniquely identify each product in my repository.  The next step is to define this field as measure, which means that this field will provide the data units for the charts and graphs that I’ll create.

I right-click the attribute and choose ‘Create a measure’.

I can now complete the first task I was requested to do: Provide a visual report on the number of products per manufacturer.

This is a rather simple visual report and SAP Lumira can present it in several different charts and graphs.

I start with a simple pie chart:

That’s it! A pie chart with the number of products per manufacturer is generated!

I can see from the chart that most of my products are manufactured by SAP AG, Apple Inc and Microsoft Corporation.

But that’s not all. I can now switch between different visual representation types by clicking the relevant icon and decide which way I’d like to present my data,

For example, the same data is shown below in 3D Column bars, (available under the ‘Bars’ icon) and in cloud tag (available under the ‘Others’ icon).

My next task is to provide a visual report on the number of products per customer country.

Now, I can use the exact same method I used in the previous example and simply replace the ‘Manufacturer’ attribute with the ‘Customer_Country’ attribute and it will work, but I’d like to use an additional way to present the data for this report.

Although I acquired 3 fields from my main table view as attributes, there is a different icon next to the ‘Customer_Country’ attribute, which indicates that SAP Lumira detected that this attribute contains geographical data and can be displayed in geographic format.

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.

Geographic

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.

Tree Map

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.

Summary

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:

http://help.sap.com/lumira

and at:

http://www.saphana.com/community/learn/solutions/sap-lumira

SAP BusinessObjects Lumira

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”.

http://help.sap.com/nwmdm

SAP NetWeaver Master Data Management

Enjoy,

Tal Shnaiderman

MDM Development