SAP ABAP Adhoc Query

Document Sample
SAP ABAP Adhoc Query Powered By Docstoc
					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




       ?

				
DOCUMENT INFO