#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:
- 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.
Hello Martin,
Thanks for a wonderfull blog.
I have a question "Can I use Input Ready query as data source ".
With regards,
Anand Kumar
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
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
Hi Syed,
Add to the knowledege
.....try it yourself and let us know.
Goodluck,
Benedict
yes that will work this way
Hi Martin,
"How to design Process Chain for such flow"
Regards,
Sushant
pretty simple once you have create the extraction you just need to add the dtp into your process chain it's that simple.
Martin
Hi,
Is it replacement/ Less dependent of APD.
Regards,
Sushant
thats exactly what it is
Martin
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
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
that's certainly possible
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
Great Martin. Good document with clear screens. Can we expect many of your BW7.3 blogs
.
Glad you liked it I'm working on some more 7.3 stuff..
Nothing to worry, its not an issue.Keep continue to explore NEW BW7.3 options.
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
Unfortuntately I don't know any workaround you need 7.30
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
Hi we are on SAPKW73105 and it was available I wouldn't recommend 05 though I would go for SAPKW73108
Martin
Yeah, its good.
But this option is disabled in RSRT. How we can enable ?
Regards,
Babu.
Need to be on BW7.3 version and SAPKW73008 higher.
It's disabled if you have a variable in your source query. Make sure this is not the case.
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.
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
Yes, if you see as separate info object(displ attr) at source side at transformations level then you can.
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
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
Hi syed
You'll have those attributes also available in your transformation.
does it clarify?
Martin
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
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
Yes Martin its clear now thanks alot for the explanation
.
good happy it worked for you!
HI Benedict
I felt the same thing when I discovered it
Glad you like it
Martin
Good one Martin,
Thanks for sharing....
BR
Prabhith
thanks
Hi,
Good Document, But I have 1 question "How to design Process Chain for such flow"
Regards,
Sushant
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
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
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
Thanks Martin.
Here comes various limitations for this approach then but indeed useful in various scenario's.
Thanks
found another limitation you also can't use inventory keyfigures..
Hi Martin,
Thanks for sharing the useful information. I have a question here, Can we use set filters in DTP?
Many Thanks
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
Hi Martin,
Nice article.
Hope to see it working myself soon...no BW 7.3 as of now
Br,
Arpit
Hi
Thanks
It is a quite nice feature saving you the way through APD
Martin
thank you hope you can use it too..
Martin
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.
What version are you on? Its only possible from SAP BW 7.30 SP8.
Martin
Oh is it..we are on SAP BW 7.3 SP7..
Thanks for explanation.
i updated the document with that info thanks
thanks hope it works for you..
Martin
You're welcome
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.
I know we have the same thing also implemented a couple APD which are obsolete now
Martin
Glad you like it it is indeed pretty handy..
Martin
you're welcome thanks for your comments
Martin
thanks for commenting hope you like it
Martin
thanks and you're welcome Naveen
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
Hi Martin ,
Its really nice doc. keep it up ....posting new bw 7.3 features..:)
Regards,
Surajit
keep finding new things too
thanks!! glad you like it
Martin
So did we shortens the export a lot without APD
Martin
I agree this is definitely a nice leap on 7.3 being able to skip an APD..
Martin
you're welcome
good hope you can use it too..
you're welcome
it is quite handy when you can cut out the APD for the same result..
I hope it gets even better when you also can use this function for non cumulative keyfigures and be able to create inventory snapshots..
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
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
Make sure you don't have inputready variables in your query and it's not based on non cumulative keyfigures
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
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
Hi Martin,
Very good document. Thanks for your efforts.
Amine
Hi Martin,
It's simple and very easy to use solution.
Thanks for sharing. Very nice document.
Regards,
Nitesh
Hello Martin,
Very helpful and clear document.
Thanks
NS
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.
Please raise as new discussion at same forum rather than asking here.
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.
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.
Nice One. Thanks.
Nice and useful information. Thanks
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?
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.
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
Thanks for the article!!! Do you have any ideas on how to handle navigational attributes?
HI martin,
I have one query related this information.
can we use SUMCT Function and then we use the Query as infoprovider
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
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
Hi,
try to create the transformation first with a "QueryProvider".
The DTP can be created afterwards.
Kind regards
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