on 10-06-2015 9:08 PM
Hello,
I have a BEx query that is inappropriately combining records when I review the query output in Analyzer. Those records are not combined when I view them at the cube level. Is there a setting in Query Designer that I might be missing, that is causing records to combine?
More specifically, my query is built on an FS-CD (Financial Services - Collections & Disbursements) cube. The data in this cube is a snapshot of open items in FS-CD at each month-end. The business users can input a company code, GL account and month-end date to pull the data. From what I can tell, my query is inappropriately combining records when both the Reference Key and Document Number are the same, but I see these as different records when I pull the data off the cube using RSA1, or when running an FPo4 right in FS-CD.
Thank you for your help.
Angela
Hi Angela,
While viewing data from the cube, can you check the setting "Use DB aggregation"?
Now check if your cube output matches with the query output.
Br, Harish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Harish,
I tried it checking the setting "Use DB aggregation", and now the cube output does match the query output. But, I would like the query output to be what the cube output was without DB aggregation. Is there a setting in Query Designer that I need to fix so it doesn't aggregate?
Thank you for your help.
Angela
Hi Angela,
The default query property is that the key figures will be aggregated based on the characteristic combination. This cannot be changed. Period.
If you want the data not to be aggregated, I'd suggest you to add a new characteristic that shows the record count to your infoprovider.
This should be included in the query row, to have the non-aggregated output.
Let me know if you have any clarification.
Br, Harish
Thank you everyone - we actually do have "Item Number" and "Subitem Number" that we are bringing in - I added those to my query, and if Item Numbers are not different between records, then Subitem Numbers are always different. And it works, it brings each item in without aggregating!
Thank you!!
Angela
Hi Angela,
I don't see any issue here, looks like both cube and Query data are matching. You can try aggregation tab in Bex for specific Key figure you are trying to maintain aggregation for.
I faced the same issue what you are facing check the example below:
Example:
Cube Without DB aggregation
Material | Plant | Cost |
100 | 1 | 20 |
100 | 1 | 20 |
200 | 2 | 10 |
300 | 1 | 10 |
Cube With DB Aggregation (cost is aggregated to 40 because Material and plants are identical)
Material | Plant | Cost |
100 | 1 | 40 |
200 | 2 | 10 |
300 | 1 | 10 |
Bex Query
Material | Plant | Cost |
100 | 1 | 40 |
200 | 2 | 10 |
300 | 1 | 10 |
So in above example I wanted to see cost =20 for material 100 and plant 1.
But still if you can post examples with field names and business logic that would help us in resolving your issue.
Thank You,
Vinay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vinay,
I can't figure out how to attach a document to show you - but it's really just like your example above - all fields are the same (except in my case, dollar amounts are different), and the query is combining those records. That Aggregation tab in BEx looks like the place to fix this, except mine is greyed out, like yours is in your picture above. Do you know how to make that active so that I can change it?
Angela
Hi Angela,
What i think is that the Data is getting aggregated in Query ...where as in the cube the data is at detailed level.
The reason for this is that this is the Basic property of the Query Designer where all data gets aggregated when it has similar Characteristics records.
Aggregation will not happen if the records are different.
rgds
SVU
Well you can use aggregation once you use the Key figure in formula, but Is your business case to look at only unit price? If No then the result in your query is correct with aggregation.
Also, any changes to aggregation tab of Key figure will do no good.
If you want to see unit price based on material/plant then you can enhance it as attribute.
I think, what you are seeing in query is correct, unless you want to see unit price based on Material/plant.
As SVU 123 said Basic property of the Query Designer is to aggregate data when it has similar Characteristics records.
So I would say that you should check with your business, there might be no issue.
please provide example data with desired output and the screenshot of what is taking place at Bex level
thanks, Aparajit
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Angela,
Can you post an example from cube and query.
Reason could be missing fields in query.
Thank You,
Vinay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.