Actions

#HowTo use a #SAP #BW query directly as datasource

September 19, 2013 | 3,987 Views |
 7.3bexbusiness warehousebwdatadata warehousedatasource
Follow
Applies to: SAP  BW 7.30 SP8

Introduction

How to use a result of a query as datasource for infocubes or DSO


Currently we use the analysis process designer (APD) to write data into a write optimized DSO and then load it from there further.
This is no longer necessary.
As of SAP BW 7.3 a query can be used directly as datasource. You can write it into a DSO, OpenHub etc. This makes it possible to perform high-quality analysis on queries that are simple but contain a very large amount of data.

This has the advantage that you can calculate functions of the query more efficiently in the analytic server before reading the data.
(source BW help)

Here is how:

  1. Create a data target in which you plan to load the query. It should contain the
    dimensions and keyfigures of the query you use as datasource.
  2. Go to transaction RSRT and choose your query. In “Properties” tick your query as “is
    used as InfoProvider”
    A.png
  3. Now instead of creating a transformation first you create a DTP. Right click on the
    DSO, OpenHub etc. and select “Create data transfer process”
  4. Select Query Element as Object Type in “Source of DTP”. After you did step 2) you will
    find you query in the F4 help.
    B.png
  5. BW created a transformation and a DTP. Now you can map and design additional logic
    in the transformation as you are used to.
    C.png

All the OLAP functions can be used in the staging with the query as the data source of a data transfer process.

This does not work with inventory keyfigures (non-cumulative keyfigures) or with any query containing input ready variables.

To report this post you need to login first.

88 Comments

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

  1. Anand Kumar

    Hello Martin,

     

    Thanks for a wonderfull blog.

     

    I have a question  “Can I use Input Ready query as data source “.

     

    With regards,

     

    Anand Kumar

    (0) 
    1. Martin Grob Post author

      H Anand

      Thanks for the comment.

      If you can set the flag for “infoprovider” you might use an input ready query as a source as well..

      Martin

      (0) 
      1. syed zabiullah

        Hi Martin,

        Good post.

        I have a question. If my query has display attributes. Then can i use these display attributes as separate fields in the transformation mapping?

         

        Regards

        Syed

        (0) 
                  1. Sushant Tiwarekar

                    Hi

                     

                    Also what should be data flow?

                    APD Flow is like  APD –> Direct Update DSO –> Standard DSO –> Infocube

                     

                    What will be the flow for new Datamodel   

                     

                    Regards,

                    Sushant

                    (0) 
                    1. Martin Grob Post author

                      it would be dtp from query to dso then dso to cube.. (of course you should update the data which your query is underlying first)

                      Martin

                      (0) 
  6. shidong lin

    Hi Martin,

     

    It seems that now all 7.3 system has this function, we are on SAPKW73103, could you tell in which SP it is added?

     

    Regards,

    Shidong

    (0) 
      1. Peter Chen

        Hi, Martin.  Thanks for the nice article.

         

        When you say “disabled if you have a variable”: do you mean “input-ready” variable only ?

         

        Can we have a customer exit variable in the filter of the query ?

         

        Thanks.

        (0) 
  8. syed zabiullah

    Hi Guys,

    I have a question. If my query has display attributes. Then can i use these display attributes as separate fields in the transformation mapping?

     

    Regards

    Syed

    (0) 
      1. syed zabiullah

        Hi Rama,

        I did not understand.

        I have a master object. example employee id and then employee name as attribute of it.

        now i want to know if i mark query as info provider and try creating transformation to dso then will i see employee name as separate in transformation for mapping?

         

        Regards

        Syed

        (0) 
        1. Raman Korrapati

          Hi syed,

           

          While creating transofmration you can see left side obejcts(query obejcts) and right side objects(targets obejctys). if you can see emp name as info object then you can load to respective info object at target side.

           

          Have you Bw7.3, then try to do on sand box. you will get idea.

           

          Thanks

          (0) 
  9. Aakash Gujja

    Hi Martin,

     

    Nicely designed document. Appreciate your efforts mate.

    Keep posting documents on BW 7.3 so that we can learn a lot from you guys .

     

    Martin i have a question So after creating DTP will system automatically create transformation or after creating DTP do we need to create transformation? Can you please elaborate your 5th point?

    [BW created a transformation and a DTP. Now you can map and design additional logic
    in the transformation as you are used to. ]

     

    BR

    Aakash

    (0) 
    1. Martin Grob Post author

      Hi

       

      It will create the transformation automatically when you create the dtp between query element and your target. Afterwards you can change the generated transformation.

      Does this clarify?
      martin

      (0) 
  13. Kamal Mehta

    Hi Martin,

     

    Nice one.

     

    Thanks for sharing.

     

    Does it extracts all the data from query here. Do we need to create Query Variant in RSRT before doing the same.

     

    Do we have provision for Delta/Full here as well.

     

    We are not on 7.3 so can’t try it out.

     

    Thanks

    (0) 
    1. Martin Grob Post author

      Hi

      HI Kamal

      Thanks! You don’t need a query variant in rsrt. The query just can’t have a variable in it otherwise it can’t be set as “infoprovider”. Also you can only do a full extract

      Martin

      (0) 
        1. Martin Grob Post author

          Hi

           

          You could also just set those filters in the query itself then you don’t need to filter on dtp level any further..

           

          Martin

          (0) 
  16. Suresh Kumar Desai

    Hi Martin,

     

    I am not getting Query Element option as source of DTP when I try to create DTP. Though I have select “Query is used as Infoprovider” option in RSRT for the query.

     

    Please help me out in this regard.

    (0) 
  19. Shanthi Bhaskar

    Good information, I didn’t know this option at all. While I was working on 3.x system I had this kind requirement where I had used APD to fulfill with lots ABAP.

     

    Its really cool option.

    (0) 
  24. Martin Grob Post author

    thanks for your comments I do hope it saves some of the bw people some time not have too look for the files all the time
    Martin

    (0) 
    1. Surajit Pal

      Hi Martin ,

                        Its really nice doc. keep it up ….posting new bw 7.3 features..:)

       

       

      Regards,

      Surajit

      (0) 
  32. Martin Grob Post author

    I hope it gets even better when you also can use this function for non cumulative keyfigures and be able to create inventory snapshots..

    (0) 
  33. Josef Kuenzli

    Martin, this is simply fantastic!

    Now I’m able to load a Bex query result of 200k rows and 8mio cells into a DSO. And then consume it via an unx into a BO Explorer Information Space for instance.

    I tried this before with APD, but there you´re bound to the normal Bex limit of 65k rows and 750k cells.

    Danke vöumou ond wiiter so

    Josef

    (0) 
  34. Harald Wetzel

    Hi Martin,

     

    I have created a query and tried to flag it as described to use it as InfoProvider/ DataSource but the check box is greyed out. We are on SAPKW73106.

     

    The query created is based on an InfoProvider.

     

    Can you advise please?

     

    Regards,

    Harald

    (0) 
      1. Harald Wetzel

        Hi Martin,

         

        thanks for your reply.

         

        I checked the query following your advise, but there is neither an inputready variable nor a non-cumulative keyfigure in my query.

         

        Is there any other pre-requisite to consider?

         

        I also checked this:

        http://help.sap.com/saphelp_nw73/helpdata/en/4b/c347cd494650e9e10000000a15822b/frameset.htm

         

        When I click on Info Button “QueryProvider Information in RSRT > Properties I get an error message as follows:

        S:RSRQPROV:070 Tax Amount

        Query Prperties.jpg

         

        Harald

        (0) 
      2. tilak mishra

        Martin,

         

        I removed the variable we have in the Bex query in order to use it as an Info Provider.

        However, when i go RSRT -> Properties of the Query i still see the option is grayed out.

         

        Is there any other way to apply the check mark?

        Also, i wanted to tell you that we are on BW 7.3 with SP 07. Do you think that could be the reason for this grayed out stuff?

         

        Another advise i need from you is ~ incase we are not planning to upgrade our SP to 08 what would be your recommendation to use APD for this kind of requirement where customer needs feed of the BEx query data into an external SQL server?

         

        Do you recommend me to use APD or does this has many short comings which is best to avoid and still upgrade our SP?

         

        Thanks for all your help..

         

        Regards,

        Tilak

        (0) 
  38. babu JPM

    Hi,

     

    We transported trn’s(Query Ele to DSO) to Prod, Everything is fine.

    The problem is we are unable to create DTP’s directly in prod as we wont transport DTP’s from DEV to Prod.

    While creating the DTP’s in Prod, its showing “

    Selected object is not a valid source of DTP”.

    Please help on this.

     

    Regards,

    Babu.

    (0) 
  39. Dillip kumar

    Hii,

    I am trying to use query , based on MultiProvider(1cube,2DSO) , as datasource.I want to save the query output in a target . But in the DTP only data from one of these infoprovider is fetched to the target.Where as the query output shows data from all three infoprovider correctly.

    Please help on this.

    (0) 
  40. Sanjeev Kumar

    Hi Martin,

     

    Great blog!!

     

    I have a question.  There is a scenario where I want to use Query as Inforprovider to load a DSO.  In productive system, the amount data passing through query to DSO will be billions of records.  Is there any limitation on the data loads from query to a DSO?  Will it work efficiently with this much of data or could fail?

     

    Thanks!

    Sanjeev.

    (0) 
  43. H. van der Haar

    Hi Martin,

     

    Thanx for sharing this blog. A lot of comments / questions and I will add another one.

     

    I want to use the Query as DataProvider to fill OpenHub Destination. In the Query I marked several characteristics as Key / Text. Only the Keys however appear in the mappings of the TRFN. I dindn’t find any possibility to fill Key and Text in the OH. Do you – or anyone – know if this is possible?

    (0) 
    1. Martin Grob Post author

      Hi

      Yeah this is possible but you need to add this manually to your OH and then read TXTSH or whatever with a routine from the corresponding IO.

      (0) 
  44. Sree Chakka

    I was trying this process but am not see all my KPI in the transformation only the first KF is coming  in transformation. any idea?

    thanks

    sree

    (0) 

Leave a Reply