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.


Query1.png

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.


Query16.jpg

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.


Query3.png

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. 


Query4.jpg

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.

Query5.jpg

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.  

Query6.jpg

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.

Query17.jpg

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.

Query9.jpg

I can choose the connection I already established previously using the recently used window. 

Query10.jpg

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.


Query11.jpg

Query12.jpg

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.


Query13.jpg


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.


Query14.jpg


After clicking Merge, you can see the material group text in each record.


Query15.jpg


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.

To report this post you need to login first.

22 Comments

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

  1. Gaurav Kimothi

    Once I connect to the ERP system I am able to see the infosets in the left panel. However, when I click on any one of these I do not see any query in the ‘Query’ space. What could be missing?

    (0) 
    1. Linda Peruzzi Post author

      Hi Gaurav,

      In my system, I need to navigate to the subdirectory Infosets under Global or Local Catalog to get a list of my infosets.  I’m wondering if that is true in your system as well.

      Thanks,

      …LInda

      (0) 
      1. Antoine CHABERT

        Hi Linda, hi Gaurav,

        The catalog display is independent of the system being used. Also the catalog display is the same whether using Lumira or Information Design Tool as this is provided by the same technical component (aka connection server).

        Best regards

        Antoine

        (0) 
  2. Henry Banks

    To determine what authorizations are missing from the user account, you should check the authorization trace in transaction ST01 or SU53. Any failed authorizations as indicated by this trace need to be assigned to the user account.

    (0) 
    1. Gaurav Kimothi

      Thanks for the help. I was able to resolve this issue. It’s an interesting functionality but the user-interface needs improvement in my opinion. It is very ‘IT Centric’ right now. For example if I wish to limit the amount of data that it brings over I need to know the SAP field names to modify the query, however, I cannot know the available fields until I preview the data. There are many other such areas of improvements that need to be done before this really becomes user-friendly.

      (0) 
      1. Linda Peruzzi Post author

        Hi Gaurav,

        Thanks for the feedback. 

        If you could, we would love to hear more of your ideas.  Can you vote on existing entries in the SAP Lumira idea place that Antoine listed above or also create any new ideas if you don’t see it listed so others can vote as well. 

        http://ideas.sap.com/SAPLumira

        Much appreciated.

        …Linda

        (0) 
      2. Dany Alchammas

        Hi Gaurav,

        I have the same problem you have had before.. a list of infosets that do not show any SQL. How did you manage to resolve it?

        Thanks

        Dany

        (0) 
  3. Vivian Shen

    When use this connection method and publish the Lumira report to Lumira Server, the data can’t be refreshed every time user opens the report, right? is there any plan to enhance the refresh function to this type of data source?

    (0) 
  4. Alejandro Haller

    Hi, somebody knows where can I find the functional description of each infoset and its fields? To make it easer to consume information from this infosets thru Lumira, is there any way to view the functional name of infosets or the fields at least when you access them with Lumira?

    Regards

    (0) 
  5. Sven Schneider

    The JCo connection user see all Global/Local Infosets, regardless of Query User Group authority and authority object S_QUERY.

    If you set “Display System Objects” under menu Settings in transaction SQ02 for this user, you see also all Quickview-Infosets SYSTQV… (generated with transaction SQVI) under Local tree?!

    This is because JCo uses internally RFM RSAQ_REMOTE_FUNCAREA_CATALOG and this FRM considered no SAP Query authority.

    If you use Local-Infosets with Lumira-Desktop and you have experience with randomly deleted ABAP query objects (also for local area) and related runtime errors with transaction SQ01/SQVI:

    See note 2327531and Run Time Error – SQ01 query

    (0) 
  6. Bassam Jamil

    Hi Experts,

    I have a question :.

    Can anyone tell me once we import the data(from ECC) into SAP Lumira through the method as in the post will any change in data in ECC (e.g in the table new data is inserted in ECC system) directly effect the tables/visualization in Lumira or we have to import that infoset again?

    Regards,

    Bassam Jamil

    (0) 
  7. Arun S

    Hi  Linda –

    I am trying to use this blog to dynamically populate the current date. Using the example you have used in your blog…

    ====================

    WHERE “EKKO-BSTYP” = ‘F’ AND “EKKO-BEDAT” > DATE’2011-12-31′

    ====================

    I want to be replace DATE’2011-12-31′ by system date (current date) but no luck so far.

    Have you tried this? Please let me know.

    Thanks,

    Arun

     

     

     

    (0) 

Leave a Reply