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