Sap Ad-hoc Query Ppt

SAP Query and it's uses for BI Renee Benjamin BW Team Graybar

Overview • • • •

Why utilize SAP Query with BI Overview of SAP Query SAP Query usage in BI Questions

Why utilize SAP query with BI • ABAP development cycle time is too long. • In certain circumstances, the generic DataSource is faster. • In certain circumstances, flat file loads are faster than delivered or generic DataSources. • To access customized data structures. • Most users want more than what is delivered.

SAP QUERY 

Also known as…   

ABAP Query InfoSet Query Ad-hoc Query (in HR)

Transaction Codes • SQ01 – Query: Initial Screen • SQ02 – InfoSet: Initial Screen • SQ03 – User Groups: Initial Screen

SQ03 – User Groups After creating a user group, click ‘Assign users and InfoSets’.

SQ03 – User Groups On this screen you can assign users to this user group and /or assign InfoSets to the User Group.

SQ02 - InfoSet 

An InfoSet is a view of the data available to the query.



Before creating queries an InfoSet must be built from existing data structures.



An InfoSet is also known as functional area.

SQ02 - InfoSet

SQ02 - InfoSet Options

SQ02 - InfoSet SQ02 uses a simple graphical join display to create the InfoSet.

SQ02 - InfoSet Tables are easily added and deleted using the SAP buttons .

SQ02 - InfoSet You can populate the field groups with these three options.

SQ02 - InfoSet 





Each table is listed with all of it’s individual fields. Each field can be deleted and added to the InfoSet as needed. When completed with the InfoSet build, you must generate the InfoSet to use it in a query.

SQ02 - InfoSet 

After an InfoSet is generated it must be assigned to user groups.

SQ02 - InfoSets InfoSets can be modified by  adding additional fields  adding ABAP code to access other tables or include logic during data access  adding alias tables so the same table can be referenced multiple times in an InfoSet

Logical Databases • Logical databases are pre-defined paths for accessing database tables. • Logical databases are hierarchical. • Logical databases can improve response time when accessing tables at the top of the hierarchy. • Logical databases can also decrease response time when accessing tables at the bottom of the hierarchy. Because of this logical databases are not recommended unless you can not create a simple join in an InfoSet.

Examples of Logical Databases

SQ01 - Query 



Now we are ready to create a query based on our InfoSet. You must position yourself into the user group where you want your query to be stored.

SQ01 - Query 



Click on InfoSet Query. Next you need to select your InfoSet .

SQ01 - Query 





From here you can start creating your query. Fields selected for output will show on the bottom section of the screen in the order selected. Fields selected for Selection will show on the right hand side of the screen.

SQ01 - Query 



Once the query is complete, you can save the query and it will give you a default name or you can change the name and title as you like. Green arrow back and you can execute your query.

SQ01 - Query Execution To select you query to execute you can • Hi-light the respective line or type the query name in the query field. • Then click the appropriate execute button.

SQ01 - Query 

Note: Variants can be used to pre-populate the query selections at execution time, same as any SAP transaction.

SQ01 – Query Variants

SQ01 - Query And here are your results.  Using the standard SAP icons you can modify this 

Examples of SAP Query Results changes     

 

The display can be changed to various formats sort orders can be changed column orders can be changed, columns can be removed or added, selection criteria can be added for more filtering Create graphs Create Excel spreadsheets

QuickViewer Transaction code: SQVI SQVI is basically a personal version of SAP Query. QuickViewer reports are specific to the individual user and can not be accessed by other users via SQVI. QuickViews can be copied to SAP Queries using SQ01. You can use InfoSets in QuickViewer.

QuickViewer conversions • The conversion creates both the query and the InfoSet in the current user group. • Here you can name the query and InfoSet that are created.

What’s all this about standard and global areas? • InfoSets and Queries can be stored in two areas; Standard and Global. • Queries and InfoSets can be created in either of the two areas or in both. • In the standard area, anyone can change the Queries or InfoSets that you create. • Queries created in or copied to the Global area automatically create a transport and are thus protected.

What’s all this about standard and global areas? • InfoSets and Queries stored in the Standard area are client-specific. • InfoSets and Queries stored in the Global area are cross-client. • When in the standard area you will only see the queries that were created in the standard area. • In the global area you will only see queries that were created or copied there.

Changing from Standard to Global area • This is done in two easy steps, in either SQ01 or SQ02.

Export, import and copying • Export, Import and copying from standard to global and creating transports is done in the standard area via SQ02.

Transporting from Standard

Copying from Standard to Global

SAP Query InfoSets as DataSources 

 

Yes, R/3 SAP Query InfoSets can be used as BI DataSources. And it is relatively easy! One requirement when creating BI DataSources is that the InfoSet must be contained in the global area.

SAP Query InfoSets as DataSources We start in the IMG, transaction SBIW.

SAP Query InfoSets as DataSources 

 

Select the datasource. Enter your name. Click the create button.

SAP Query InfoSets as DataSources 



Assign the DataSource to an Application Component. Enter the descriptions and the InfoSet name.

SAP Query InfoSets as DataSources 



All fields included in your InfoSet view are available in your new DataSource. This is where you can chose your selection fields and which fields to include or hide.

SAP Query InfoSets as DataSources 

It is always a good idea to test your new DataSource with RSA3.

SAP Query InfoSets as DataSources 



Now you can jump over to BI and via RSA1 (Admin Workbench) replicate your source system so you can access your new DataSource on the BI side. Then you can test the extractor here.

Using SAP Query to create Flat Files 



Using the Enhancement SQUE0001 the ABAP team can add the private file option to your output options of a SAP Query. For more information on this search the SDN or SAP Query documentation for SQUE0001. Note 74893 also has some information on this subject.

Using SAP Query to create Flat Files 





This is how it looks on our R/3 system. The private file configuration creates the files in one file system location (file system directory). Using the private file option, queries can be scheduled in the background.

Using flat files in BI You can use the standard FTP utility in binary mode to transfer your file to the BI server and load the data with the flat file option.

SAP Query on the BI system 







SAP Query is available on the BI system as well. The transaction codes are the same; SQ01, SQ02 and SQ03. The look of the execute screen is a little different. SAP Query has different screen layouts but it is basically the same.

SAP Query on the BI System SQ02 Sample 1

SAP Query on the BI System SQ02 Sample 2

SAP Query on the BI System SQ02 Sample 3

SAP Query on the BI System SQ02 Sample 4 (with InfoCube)

SAP Query on the BI System SQ01 Sample

SAP Query on the BI System SQ01 Execute Sample

Conclusion • SAP query can be used to extract files or to create reports on both R/3 and BI. • InfoSets can be used as DataSources into BI fairly easily. • Although the format is different between R/3 and BI they function the same. • There are more options in SAP query that we have not covered here that you can explore later. • With the private file function we are able to use a system scheduler (Tidal) to schedule and automate the queries and FTP’s in the background.

Questions and comments

?