In the following article I would like to present a very simple integration between two SAP products, SAP NetWeaver Master Data Management (MDM) and SAP Lumira. The goal of the integration is to easily generate analytic visual reports of the master data content.

Preface

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:

  • 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
The integration between the two products can be done by connecting SAP Lumira to MDM using the MDM DB views.
/wp-content/uploads/2013/11/image002_320467.png

MDM DB Views

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

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.

A real life scenario

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:

  • Provide a visual report on the number of products per manufacturer.
  • Provide a visual report on the number of products per customer country.
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.

Step 1: Generating MDM DB Views

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:

RepViewsGenerate <ServerName> <RepositoryName>;<DB_Name>;<DB_Type_Letter>;<DB_UserName>;<DB_Password> <Repository_UserName>:<Repository_Password> “*”
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.

Step 2: Connecting to the MDM DB Views with SAP Lumira

Configuring a JDBC driver for SAP Lumira

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

  • SAP Lumira Desktop Standard  Edition
  • A JDBC driver for my DBMS
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

Connecting to the MDM main table view is done in the following way:
  • I start a new SAP Lumira document.
  • In the Data Source selection screen, I select FreeHand SQL.
/wp-content/uploads/2013/11/image003_320525.png
  • 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’.

D.png

  • 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).
/wp-content/uploads/2013/11/image011_320529.png
  • 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.
/wp-content/uploads/2013/11/image013_320530.png
  • 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.
/wp-content/uploads/2013/11/image015_320531.png

Step 3: Preparing the Reports in SAP Lumira

In the SAP Lumira’s preparation screen I select the ‘Visualize’ view.
/wp-content/uploads/2013/11/image017_320532.png
In the view I can see my 3 fields in the ‘Attribute’ column.
/wp-content/uploads/2013/11/image019_320533.png
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’.
/wp-content/uploads/2013/11/image021_320537.png
I can now complete the first task I was requested to do: Provide a visual report on the number of products per manufacturer.

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:
  • I click on the ‘Pies’ icon’s drop-down menu and select ‘Pie Chart’.
/wp-content/uploads/2013/11/image023_320538.png
  • 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.png

That’s it! A pie chart with the number of products per manufacturer is generated!
/wp-content/uploads/2013/11/image029_320541.png
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).
/wp-content/uploads/2013/11/image031_320546.png
/wp-content/uploads/2013/11/image033_320547.png

A visual report on the number of products per customer country

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.
/wp-content/uploads/2013/11/image035_320565.png

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

/wp-content/uploads/2013/11/image037_320566.png

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

/wp-content/uploads/2013/11/image040_320589.png

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

/wp-content/uploads/2013/11/image042_320590.png

  • Once the matching is completed, a new geographic hierarchy called ‘Country’ is created and added in the Hierarchies menu.

/wp-content/uploads/2013/11/image044_320591.png

  • 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’.

B.png

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 🙂

/wp-content/uploads/2013/11/image050_320597.png

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

/wp-content/uploads/2013/11/image052_320742.png

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.

/wp-content/uploads/2013/11/image054_320743.png

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

/wp-content/uploads/2013/11/image056_320750.png

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.

/wp-content/uploads/2013/11/image058_320751.png

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.

C.png

Next I need to choose the measure according to which the facet tables will be created; I select the ‘Part Number’ measure.

/wp-content/uploads/2013/11/image064_320797.png

Facet tables are now created for the other two attributes. I can now see the exact number of manufacturers and customer countries per product:

/wp-content/uploads/2013/11/image066_320852.png

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:

/wp-content/uploads/2013/11/image067_320861.png

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:

/wp-content/uploads/2013/11/image070_320862.png

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

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Ferose Fazallu

    Hi Tal,

    Thanks for sharing the information. Is it possible to help me with below, We too have the same requirement.

    1) Is possible to generate DB view in SP9 ?

    2) Is it possible to create the DB View in different database other than MDM Repository DB.

    3) How is the multi value tuple view look like in db view ?

    4) what is the different between SAP Lumira and SAP Information Steward ?

    Thanks in Advance,

    KF

    (0) 
    1. Tal Shnaiderman Post author

      Hi Ferose,

      1) DB Views in SP09 aren’t officially supported and the CLIX command described above won’t work for you, you can however generate the views using a standalone java utility described in SAP Note 1556213, again please note that since It is not part of the official released MDM product it will not be supported by SAP. No enhancements or bug support will be given so my recommendation would be to upgrade to SP10+.

      2) No, the DB Views are generated on the same database the repository is running upon.

      3) If you have a main table and one of the fields in it is MV tuple you will have a separate DB view for the MV tuple values.

      for example I have the main table “products” and in it the MV tuple field “MY_TUPLE”, after the generation I’ll have a DB view called TU_PRODUCTS_MY_TUPLE_XX_YY and in this view I’ll have all the values of the tuple member fields with an ID reference to the main table record the tuples belong to.

      I can get all the tuples for a specific record via a simple query using the main table view and the MV tuple view.

      4) SAP Information Steward is used to improve the quality of the data assets through various modules, SAP Lumira is a data manipulation and visualization tool, selecting a tool depend on the scenario you want, you can get more information in the SCN forums of the two products.

      HTH,

      Tal

      (0) 

Leave a Reply