Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
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.

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

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.

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

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

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.

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 :smile:

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

7 Comments
Labels in this area