There are many ways of accessing SAP data with SAP Lumira. Some of the more obvious and powerful are through SAP HANA and BI universes. But not all companies have HANA or BI Universes in their landscape but rather want to utilize their SAP ERP systems. Having created multiple ABAP custom reports in the past, I’m excited to see how quick SAP Lumira can render beautiful visualizations against SAP ERP data using the classic SAP Query Infoset.
If you are not familiar, SAP Query is a tool in SAP ERP that allows individuals to execute their own reports without knowing ABAP programming. SAP Query Infosets are special views of data sources which describe which fields of a data source can be reported on in queries. New infosets can be created in ERP using SAP transaction SQ02.
I would like to show an example of acquiring SAP purchase order quantities for particular material groups for the last 4 years from SAP ERP. Since data can also come from various sources, I will also bring in Material group descriptions from a different SAP Query infoset to demonstrate how data can be merged in the SAP Lumira prepare room.
I’m using SAP Lumira 1.21.
Step 1 Create a New Dataset
Create a new Dataset using the Query with SQL Option and click on Next.
In this case, the SAP ERP 6 – SAP Java Connector (SAP JCo) option will be selected which should already be available out of the box with SAP Lumira. Click Next.
Step 2 Fill in SAP ERP System Details
Fill in details of the ERP system similar to adding information in the SAP GUI properties to access an ERP system. Click on Connect.
A call is now made to the ERP system retrieving the available objects. SAP authorizations will still control what objects the user has access to retrieve into SAP Lumira based on the SAP user name. If the desired infoset is not listed, check your user name authorizations in SAP.
Step 3 Choose the infoset and add any additional filters in SQL statement
Click on the desired infoset and you will see an SQL statement displayed in the Query box. As we are using the SAP Lumira Desktop, I wanted to limit the number of records to retrieve by adding a filter to the query statement. I only want to bring in Purchase Order (F) for dates 2012 and on so I added additional coding.
Select from “Global”.”Infoset”.”/SAPQUERY/ME_BE”
WHERE "EKKO-BSTYP" = 'F' AND "EKKO-BEDAT" > DATE'2011-12-31'
Note: Follow the correct syntax. For example, field names should be in double quotes and values in single quotes.
Step 4 Select Columns to Bring in
Click on Preview to get an example of what data will come in. Again, I just want to choose information I will use in my visualization so will uncheck any columns that I don’t needto help performance of data acquisition. Click Create to acquire the data into SAP Lumira.
In the SAP Lumira Prepare room tab, you will now see data from ERP displayed. This data resides in SAP Lumira. At this point, the data can be enhanced by creating time hierarchies, renaming columns, create groupings etc which won’t be covered here.
Step 5 Add additional datasets to your SAP Lumira Document
Since I will be creating visualizations using the Material group, I also want to bring in the Description text of the material group field for better user experience so I will add a new dataset bringing in the master data information from an infoset I created in SAP ERP. I will follow the same steps using a different infoset.
I can choose the connection I already established previously using the recently used window.
I changed the dataset name to be more comprehensive to Material Group Description. No additional filtering is added. I also only selected 2 columns to come in. When I click on Create, the new dataset will be created in SAP Lumira.
Step 6 Merge Purchase Orders Dataset with Material Group Description Dataset
Changing back to the Purchase Orders dataset as the main object, I will now merge the material group descriptions to the Purchase Orders records so that I can create clear visualizations.
The Lookup Dataset on the Right will be merged into the Current Dataset on the left. The merge mapping key is already proposed from EKPO-MATKL to T023-MATKL. You could have multiple key mappings. I will keep the default of using the Left Outer Join.
After clicking Merge, you can see the material group text in each record.
I now have a dataset brought into SAP Lumira from SAP Query Infosets. I can now create visualizations based on this information.
Hope this example helps you bring in your SAP ERP data to SAP Lumira without requiring a BI universe or SAP HANA.
- SAP Managed Tags:
22 Comments
