Skip to Content
Author's profile photo Martin Grob

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

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.

Assigned tags

      93 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anand Kumar
      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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

      Author's profile photo syed zabiullah
      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

      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix

      Hi Syed,

      Add to the knowledege.....try it yourself and let us know.

       

      Goodluck,

      Benedict

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      yes that will work this way

      Author's profile photo Former Member
      Former Member

      Hi Martin,

       

      "How to design Process Chain for such flow"

       

      Regards,

      Sushant

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      pretty simple once you have create the extraction you just need to add the dtp into your process chain it's that simple.

      Martin

      Author's profile photo Former Member
      Former Member

      Hi,

       

      Is it replacement/ Less dependent of APD.

       

      Regards,

      Sushant

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thats exactly what it is

      Martin

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      that's certainly possible

      Author's profile photo CH Raman
      CH Raman

      Your too fast buddy, Just thinking to write with proper test case  on it. Mean while you done it.Keep it up. Thanks for sharing.

       

      Thanks

      Raman

      Author's profile photo Former Member
      Former Member

      Great Martin. Good document with clear screens. Can we expect many of your BW7.3 blogs .

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Glad you liked it I'm working on some more 7.3 stuff..

      Author's profile photo CH Raman
      CH Raman

      Nothing to worry, its not an issue.Keep continue to explore NEW BW7.3 options.

      Author's profile photo Chandra Janardhansudhir Kunar
      Chandra Janardhansudhir Kunar

      Hi Martin,

      Thanks for the document, unfortunately we are on BI7.0 .

      You made it look so simple.

      Appreciate any work around for us who are on BI7.0..

      Thanks & Regards,

      Sudhir

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Unfortuntately I don't know any workaround you need 7.30

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Hi we are on SAPKW73105 and it was available I wouldn't recommend 05 though I would go for SAPKW73108

      Martin

      Author's profile photo babu JPM
      babu JPM

      Yeah, its good.

      But this option is disabled in RSRT. How we can enable ?

       

      Regards,

      Babu.

      Author's profile photo CH Raman
      CH Raman

      Need to be on BW7.3 version and SAPKW73008 higher.

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      It's disabled if you have a variable in your source query. Make sure this is not the case.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo syed zabiullah
      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

      Author's profile photo CH Raman
      CH Raman

      Yes, if you see as separate info object(displ attr) at source side at transformations level then you can.

      Author's profile photo syed zabiullah
      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

      Author's profile photo CH Raman
      CH Raman

      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Hi syed

       

      You'll have those attributes also available in your transformation.

      does it clarify?

      Martin

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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

      Author's profile photo Former Member
      Former Member

      Yes Martin its clear now thanks alot for the explanation .

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      good happy it worked for you!

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      HI Benedict

       

      I felt the same thing when I discovered it Glad you like it

      Martin

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Good one Martin,

       

      Thanks for sharing....

       

       

      BR

      Prabhith

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks

      Author's profile photo Former Member
      Former Member

      Hi,

       

      Good Document, But I have 1 question "How to design Process Chain for such flow"

       

      Regards,

      Sushant

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Hi

      To design the process chain you simply need to add the DTP that will load your DSO/cube as you would do normally.

      Martin

      Author's profile photo Kamal Mehta
      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Thanks Martin.

       

      Here comes various limitations for this approach then but indeed useful in various scenario's.

       

      Thanks

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      found another limitation you also can't use inventory keyfigures..

      Author's profile photo Former Member
      Former Member

      Hi Martin,

       

      Thanks for sharing the useful information. I have a question here, Can we use set filters in DTP?

       

       

      Many Thanks

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hi Martin,

       

      Nice article.

      Hope to see it working myself soon...no BW 7.3 as of now

       

      Br,

      Arpit

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Hi

      Thanks

      It is a quite nice feature saving you the way through APD

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thank you hope you can use it too..

      Martin

      Author's profile photo Suresh Kumar Desai
      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.

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      What version are you on? Its only possible from SAP  BW 7.30 SP8.

      Martin

      Author's profile photo Suresh Kumar Desai
      Suresh Kumar Desai

      Oh is it..we are on SAP BW 7.3 SP7..

       

      Thanks for explanation.

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      i updated the document with that info thanks

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks hope it works for you..

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      You're welcome

      Author's profile photo Shanthi Bhaskar
      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.

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      I know we have the same thing also implemented a couple APD which are obsolete now

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Glad you like it it is indeed pretty handy..

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      you're welcome thanks for your comments

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks for commenting hope you like it

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks and you're welcome Naveen

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hi Martin ,

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

       

       

      Regards,

      Surajit

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      keep finding new things too

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks!! glad you like it

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      So did we shortens the export a lot without APD

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      I agree this is definitely a nice leap on 7.3 being able to skip an APD..

      Martin

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      you're welcome

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      good hope you can use it too..

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      you're welcome

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      it is quite handy when you can cut out the APD for the same result..

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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..

      Author's profile photo Josef Kuenzli
      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

      Author's profile photo Harald Wetzel
      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

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Make sure you don't have inputready variables in your query and it's not based on non cumulative keyfigures

      Author's profile photo Harald Wetzel
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo amine lamkaissi
      amine lamkaissi

      Hi Martin,

       

      Very good document. Thanks for your efforts.

       

      Amine

      Author's profile photo Nitesh Kumar
      Nitesh Kumar

      Hi Martin,

       

      It's simple and very easy to use solution.

      Thanks for sharing. Very nice document.

       

      Regards,

      Nitesh

      Author's profile photo Former Member
      Former Member

      Hello Martin,

       

      Very helpful and clear document.

       

      Thanks

      NS

      Author's profile photo babu JPM
      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.

      Author's profile photo CH Raman
      CH Raman

      Please raise as new discussion at same forum rather than asking here.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Sanjeev Kumar
      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.

      Author's profile photo Rajesh Ramakrishnan
      Rajesh Ramakrishnan

      Nice One. Thanks.

      Author's profile photo Former Member
      Former Member

      Nice and useful  information. Thanks

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Martin Grob
      Martin Grob
      Blog 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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Henry Jones
      Henry Jones

      Thanks for the article!!! Do you have any ideas on how to handle navigational attributes?

      Author's profile photo Vinod Patel
      Vinod Patel

      HI martin,

       

      I have one query related this information.

      can we use SUMCT Function and then we use the Query as infoprovider

       

      Author's profile photo Vinod Patel
      Vinod Patel

       

      HI martin,

      I have one query related this information.

      i have created Query as Data-sources but i have used sumct function in Bex Query .

      when i am loding the data in DSO it's showing the error.

      Error:-"Value cannot be calculated"

      Kindly suggest me this function will support for query as info-provider

      Regards,

      vinod patel

      Author's profile photo shlomi weiss
      shlomi weiss

      Hi,

      Very nice and useful blog

      We are in BW 7.5 SP 15 without HANA

      I've flagged the "Query is used as InfoProvider" but I still don't see the option to choose

      Query Element as Object Type in “Source of DTP”

       

      Any idea what can be the reason?

       

      Thanks

       

      Shlomi

      Author's profile photo Tilmann Knoedler
      Tilmann Knoedler

      Hi,

      try to create the transformation first with a "QueryProvider".

      The DTP can be created afterwards.

       

      Kind regards

      Author's profile photo Sagarika Nayak
      Sagarika Nayak

      Hi Martin,

      Thanks for the informative blog. However, my question is can we use Bex Query as a source to Advanced DSO as well in BW on HANA?

      Thanks in Adv,

      Sagarika