BEx query inappropriately combining records

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

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

[former_member202718]

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

[Former Member]

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.

[former_member182997]

please provide example data with desired output and the screenshot of what is taking place at Bex level
thanks, Aparajit

[Former Member]

Hi Angela,

Can you post an example from cube and query.

Reason could be missing fields in query. 

Thank You,

Vinay

[Former Member]

Thank you for your response Vinay, I will do this if I can't figure out the DB aggregation with Harish.