How to use classic SAP Query Infosets as a Data so...

linda_peruzzi · ‎01-16-2015

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.

former_member182336 · ‎01-16-2015

Very useful blog. This should enable even Business Users to create simple infosets and consume within Lumira.

Former Member · ‎01-17-2015

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?

Henry_Banks · ‎01-19-2015

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.

linda_peruzzi · ‎01-19-2015

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

achab · ‎01-20-2015

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

linda_peruzzi · ‎01-20-2015

Thanks for clarifying Antoine.

achab · ‎01-21-2015

There are some ideas related with ERP free-hand SQL support.

They might be of interest for readers of this post.

https://ideas.sap.com/SAPLumira/D9918

https://ideas.sap.com/SAPLumira/D20383

Best regards

Antoine

former_member23991 · ‎01-21-2015

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?

achab · ‎01-21-2015

Hi Vivian,

I would suggest you raise this as a separate blog post. Sharon Om would certainly be helpful for such question.

Best regards

Antoine

former_member23991 · ‎01-21-2015

sure. thanks!

Former Member · ‎01-21-2015

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.

linda_peruzzi · ‎01-21-2015

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

Former Member · ‎05-01-2015

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

Former Member · ‎11-11-2015

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

sven_schneider2 · ‎06-08-2016

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

former_member212573 · ‎07-15-2016

Hi Linda,

Very user friendly Doc, now I am able to connect from Lumira to ERP system.

Best Regards,

Pankaj

former_member212218 · ‎08-14-2016

Simple to follow and try it out, good one.

Thanks Linda!

Former Member · ‎08-18-2016

Hi Linda,

Thank you very much for the tutorial. Now i have a question.

Is there some detailed tutorial to create the infosets?

Regards.

TammyPowlas · ‎08-18-2016

Juan - if you search SCN, there are plenty of materials to create InfoSets inside SAP. Good luck!

Former Member · ‎08-22-2016

Thanks Tammy. I found something that works. SAP Adhoc Query SQ01 SQ02 SQ03

The configuration of the blog doesn't have any failure.

Regards

bassam · ‎03-15-2017

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

arunsriv · ‎04-30-2017

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