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

Follow RSS feed Like

61 Likes 37,569 Views 92 Comments

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:

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.
Go to transaction RSRT and choose your query. In “Properties” tick your query as “is
used as InfoProvider”
Now instead of creating a transformation first you create a DTP. Right click on the
DSO, OpenHub etc. and select “Create data transfer process”
Select Query Element as Object Type in “Source of DTP”. After you did step 2) you will
find you query in the F4 help.
BW created a transformation and a DTP. Now you can map and design additional logic
in the transformation as you are used to.

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.

Alert Moderator

92 Comments

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

Anand Kumar

September 19, 2013 at 12:19 pm

Hello Martin,

Thanks for a wonderfull blog.

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

With regards,

Anand Kumar
- Like(0)
- Martin Grob Post author
  
  September 19, 2013 at 4:38 pm
  
  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
  - Like(0)
  - syed zabiullah
    
    September 20, 2013 at 9:51 am
    
    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
    Like(0)
    - Benedict Venmani Felix
      
      September 21, 2013 at 3:36 pm
      
      Hi Syed,
      
      Add to the knowledege…..try it yourself and let us know.
      
      Goodluck,
      
      Benedict
      
      Like(0)
      /
    - Martin Grob Post author
      
      September 22, 2013 at 3:31 pm
      
      yes that will work this way
      
      Like(0)
      - Former Member
        
        September 22, 2013 at 3:39 pm
        
        Hi Martin,
        
        “How to design Process Chain for such flow”
        
        Regards,
        
        Sushant
        
        Like(0)
      - Martin Grob Post author
        
        September 22, 2013 at 4:00 pm
        
        pretty simple once you have create the extraction you just need to add the dtp into your process chain it’s that simple.
        
        Martin
        
        Like(0)
      - Former Member
        
        September 22, 2013 at 4:24 pm
        
        Hi,
        
        Is it replacement/ Less dependent of APD.
        
        Regards,
        
        Sushant
        
        Like(0)
      - Martin Grob Post author
        
        September 22, 2013 at 4:29 pm
        
        thats exactly what it is
        
        Martin
        
        Like(0)
        
        /
      - Former Member
        
        September 22, 2013 at 4:32 pm
        
        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
        
        Like(0)
      - Martin Grob Post author
        
        September 22, 2013 at 5:09 pm
        
        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
        
        Like(0)
    - Martin Grob Post author
      
      October 13, 2013 at 2:31 pm
      
      that’s certainly possible
      
      Like(0)
CH Raman

September 19, 2013 at 12:25 pm

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
- Like(0)
Former Member

September 20, 2013 at 3:27 am

Great Martin. Good document with clear screens. Can we expect many of your BW7.3 blogs .
- Like(0)
/
- Martin Grob Post author
  
  September 20, 2013 at 5:19 am
  
  Glad you liked it I’m working on some more 7.3 stuff..
  - Like(0)
CH Raman

September 20, 2013 at 5:22 am

Nothing to worry, its not an issue.Keep continue to explore NEW BW7.3 options.
- Like(0)
Chandra Janardhansudhir Kunar

September 20, 2013 at 5:56 am

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
- Like(0)
- Martin Grob Post author
  
  September 20, 2013 at 10:29 am
  
  Unfortuntately I don’t know any workaround you need 7.30
  - Like(0)
Former Member

September 20, 2013 at 7:45 am

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
- Like(0)
- Martin Grob Post author
  
  September 20, 2013 at 10:29 am
  
  Hi we are on SAPKW73105 and it was available I wouldn’t recommend 05 though I would go for SAPKW73108
  
  Martin
  - Like(0)
babu JPM

September 20, 2013 at 9:47 am

Yeah, its good.

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

Regards,

Babu.
- Like(0)
- CH Raman
  
  September 20, 2013 at 9:53 am
  
  Need to be on BW7.3 version and SAPKW73008 higher.
  - Like(0)
- Martin Grob Post author
  
  October 8, 2013 at 5:28 pm
  
  It’s disabled if you have a variable in your source query. Make sure this is not the case.
  - Like(0)
  - Former Member
    
    May 27, 2014 at 3:34 pm
    
    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.
    Like(0)
syed zabiullah

September 20, 2013 at 9:57 am

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
- Like(0)
- CH Raman
  
  September 20, 2013 at 10:00 am
  
  Yes, if you see as separate info object(displ attr) at source side at transformations level then you can.
  - Like(0)
  - syed zabiullah
    
    September 20, 2013 at 10:09 am
    
    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
    Like(0)
    - CH Raman
      
      September 20, 2013 at 10:14 am
      
      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
      
      Like(0)
    - Martin Grob Post author
      
      September 21, 2013 at 1:16 pm
      
      Hi syed
      
      You’ll have those attributes also available in your transformation.
      
      does it clarify?
      
      Martin
      
      Like(0)
Former Member

September 20, 2013 at 10:09 am

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
- Like(0)
/
- Martin Grob Post author
  
  September 20, 2013 at 12:18 pm
  
  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
  - Like(0)
  - Former Member
    
    September 20, 2013 at 12:21 pm
    
    Yes Martin its clear now thanks alot for the explanation .
    Like(0)
    /
Martin Grob Post author

September 20, 2013 at 10:28 am

good happy it worked for you!
- Like(0)
Martin Grob Post author

September 20, 2013 at 5:00 pm

HI Benedict

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

Martin
- Like(0)
/
- prabhith prabhakaran
  
  September 21, 2013 at 4:40 pm
  
  Good one Martin,
  
  Thanks for sharing….
  
  BR
  
  Prabhith
  - Like(0)
  - Martin Grob Post author
    
    September 24, 2013 at 11:07 am
    
    thanks
    Like(0)
Former Member

September 21, 2013 at 4:49 pm

Hi,

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

Regards,

Sushant
- Like(0)
- Martin Grob Post author
  
  September 30, 2013 at 3:20 pm
  
  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
  - Like(0)
Kamal Mehta

September 24, 2013 at 6:26 pm

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
- Like(0)
- Martin Grob Post author
  
  September 24, 2013 at 7:00 pm
  
  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
  - Like(0)
  - Kamal Mehta
    
    September 25, 2013 at 2:25 am
    
    Thanks Martin.
    
    Here comes various limitations for this approach then but indeed useful in various scenario’s.
    
    Thanks
    Like(0)
    - Martin Grob Post author
      
      October 24, 2013 at 7:21 pm
      
      found another limitation you also can’t use inventory keyfigures..
      
      Like(0)
  - Former Member
    
    October 1, 2013 at 12:51 pm
    
    Hi Martin,
    
    Thanks for sharing the useful information. I have a question here, Can we use set filters in DTP?
    
    Many Thanks
    Like(0)
    - Martin Grob Post author
      
      October 1, 2013 at 3:05 pm
      
      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
      
      Like(0)
Former Member

September 25, 2013 at 7:41 am

Hi Martin,

Nice article.

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

Br,

Arpit
- Like(0)
/
- Martin Grob Post author
  
  September 25, 2013 at 4:31 pm
  
  Hi
  
  Thanks
  
  It is a quite nice feature saving you the way through APD
  
  Martin
  - Like(0)
Martin Grob Post author

September 27, 2013 at 7:10 am

thank you hope you can use it too..

Martin
- Like(0)
Suresh Kumar Desai

September 27, 2013 at 10:10 am

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.
- Like(0)
- Martin Grob Post author
  
  September 27, 2013 at 11:14 am
  
  What version are you on? Its only possible from SAP BW 7.30 SP8.
  
  Martin
  - Like(0)
  - Suresh Kumar Desai
    
    September 27, 2013 at 2:09 pm
    
    Oh is it..we are on SAP BW 7.3 SP7..
    
    Thanks for explanation.
    Like(0)
    - Martin Grob Post author
      
      October 24, 2013 at 7:20 pm
      
      i updated the document with that info thanks
      
      Like(0)
Martin Grob Post author

September 28, 2013 at 9:26 pm

thanks hope it works for you..

Martin
- Like(0)
Martin Grob Post author

September 29, 2013 at 8:49 pm

You’re welcome
- Like(0)
Shanthi Bhaskar

October 1, 2013 at 3:17 pm

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.
- Like(0)
- Martin Grob Post author
  
  October 1, 2013 at 3:48 pm
  
  I know we have the same thing also implemented a couple APD which are obsolete now
  
  Martin
  - Like(0)
  /
Martin Grob Post author

October 2, 2013 at 4:46 pm

Glad you like it it is indeed pretty handy..

Martin
- Like(0)
Martin Grob Post author

October 6, 2013 at 10:55 am

you’re welcome thanks for your comments

Martin
- Like(0)
Martin Grob Post author

October 10, 2013 at 8:04 pm

thanks for commenting hope you like it

Martin
- Like(0)
Martin Grob Post author

October 10, 2013 at 8:05 pm

thanks and you’re welcome Naveen
- Like(0)
Martin Grob Post author

October 17, 2013 at 5:43 am

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
- Like(0)
/
- Former Member
  
  October 17, 2013 at 8:49 am
  
  Hi Martin ,
  
  Its really nice doc. keep it up ….posting new bw 7.3 features..:)
  
  Regards,
  
  Surajit
  - Like(0)
  - Martin Grob Post author
    
    October 24, 2013 at 7:20 pm
    
    keep finding new things too
    Like(0)
    /
Martin Grob Post author

October 17, 2013 at 9:18 am

thanks!! glad you like it

Martin
- Like(0)
Martin Grob Post author

October 17, 2013 at 2:44 pm

So did we shortens the export a lot without APD

Martin
- Like(0)
/
Martin Grob Post author

October 22, 2013 at 8:17 pm

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

Martin
- Like(0)
Martin Grob Post author

October 24, 2013 at 11:35 am

you’re welcome
- Like(0)
Martin Grob Post author

October 24, 2013 at 5:42 pm

good hope you can use it too..
- Like(0)
Martin Grob Post author

October 28, 2013 at 3:28 pm

you’re welcome
- Like(0)
Martin Grob Post author

November 4, 2013 at 6:23 am

it is quite handy when you can cut out the APD for the same result..
- Like(0)
Martin Grob Post author

November 10, 2013 at 1:59 pm

I hope it gets even better when you also can use this function for non cumulative keyfigures and be able to create inventory snapshots..
- Like(0)
Josef Kuenzli

March 6, 2014 at 4:39 pm

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
- Like(0)
Harald Wetzel

March 26, 2014 at 1:47 pm

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
- Like(0)
- Martin Grob Post author
  
  April 1, 2014 at 12:39 pm
  
  Make sure you don’t have inputready variables in your query and it’s not based on non cumulative keyfigures
  - Like(0)
  - Harald Wetzel
    
    April 2, 2014 at 2:22 pm
    
    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
    
    Harald
    Like(0)
    /
  - Former Member
    
    May 15, 2014 at 3:35 pm
    
    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
    Like(0)
amine lamkaissi

April 1, 2014 at 1:23 pm

Hi Martin,

Very good document. Thanks for your efforts.

Amine
- Like(0)
Nitesh Kumar

April 2, 2014 at 7:35 am

Hi Martin,

It’s simple and very easy to use solution.

Thanks for sharing. Very nice document.

Regards,

Nitesh
- Like(0)
Former Member

May 27, 2014 at 4:41 pm

Hello Martin,

Very helpful and clear document.

Thanks

NS
- Like(0)
babu JPM

July 14, 2014 at 11:36 am

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.
- Like(0)
- CH Raman
  
  July 14, 2014 at 11:39 am
  
  Please raise as new discussion at same forum rather than asking here.
  - Like(0)
Former Member

August 8, 2014 at 8:11 pm

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.
- Like(0)
Sanjeev Kumar

March 23, 2015 at 5:02 pm

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.
- Like(0)
Rajesh Ramakrishnan

June 4, 2015 at 6:16 am

Nice One. Thanks.
- Like(0)
Former Member

June 5, 2015 at 3:31 pm

Nice and useful information. Thanks
- Like(0)
Former Member

June 22, 2015 at 7:06 pm

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?
- Like(0)
- Martin Grob Post author
  
  June 29, 2015 at 2:16 pm
  
  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.
  - Like(0)
Former Member

July 10, 2015 at 3:15 pm

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
- Like(0)
Henry Jones

September 11, 2015 at 2:24 pm

Thanks for the article!!! Do you have any ideas on how to handle navigational attributes?
- Like(0)
Vinod Patel

December 11, 2017 at 5:32 am

HI martin,

I have one query related this information.

can we use SUMCT Function and then we use the Query as infoprovider
- Like(0)
Vinod Patel

December 11, 2017 at 5:42 am

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
- Like(0)
shlomi weiss

December 31, 2019 at 6:41 am

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
- Like(0)
- Tilmann Knoedler
  
  June 2, 2020 at 1:55 pm
  
  Hi,
  
  try to create the transformation first with a “QueryProvider”.
  
  The DTP can be created afterwards.
  
  Kind regards
  - Like(0)