cancel
Showing results for 
Search instead for 
Did you mean: 

Why BEx query not hitting aggregates?

former_member198905
Participant
0 Kudos

Hi

I already read lots of documents regarding creating aggregate.

I try following procedures to create aggregates on cube:

1) Create separate aggregate for each dimension.

2) Create separate aggregate for each dimension with time dimension.

3) Single aggregate contains all dimension.

4) System proposed all aggregates.

5) Selected optimize aggregates from system proposed all aggregates.

Non of the aggregate size is greater then InfoCube size.

Following are the best valuation when using option (2):

But

Why BEx query not hitting aggregates?

Thanks

Accepted Solutions (0)

Answers (5)

Answers (5)

FCI
Active Contributor
0 Kudos

Please check the read mode of your query in the RSRT / query properties (it should be H or X):

former_member198905
Participant
0 Kudos

Read mode of my query in the RSRT is H in query properties.

FCI
Active Contributor
0 Kudos

Use the RSRT to execute and debug your query.

Choose the display aggregate option:

The transaction will then display the infoProvider read with all the characteristics the query needs:

In your case, if your query doesn't hit an aggregate, it will display the cube with all the characteristics the query needs.

To be used by the query, an aggregate must have at least the same set of characteristics and a larger selection if your aggregate contains filters.

Regards,

Frederic

former_member198905
Participant
0 Kudos

Hi Frederic

Yes, you are right in my case, my query doesn't hit an aggregate and it will display the cube with all the characteristics the query needs.


To be used by the query, an aggregate must have at least the same set of characteristics and a larger selection if your aggregate contains filters.

As per your recommendation I create an aggregate containing all characteristics which show in RSRT and those characteristics which contains filters in query. Now query is hitting this aggregate.

But this aggregate has larger number of minus signs, if I create it with filters or without filters, it mean valuation is worse of this aggregate.

So how can its valuation convert to larger number of plus signs? or in other words how can this worse aggregate turn to best aggregate??

Regards

FCI
Active Contributor
0 Kudos

Hi Imran,

It just means that your query probably needs too much details for an an aggregate to be useful.

Your query needs the same level of detail than the cube has, an aggregate won't bring you any benefit.

Regards,

Frederic

former_member198905
Participant
0 Kudos

Hi Frederic

My query's default layout contains:

1 Characteristic (Non Line Item)

2 Filters

10 Key Figures

Any how If there is no solution then what kind of aggregates I need for my cube because there are 15 characteristics more are available in the free characteristic area?

Regards

Former Member
0 Kudos

I think there is a solution, just we haven't found it.

Can you please the detail fo you query and your aggregate? I mean, displayed and free chars for query and the chars used in aggregate.

Regards.

former_member198905
Participant
0 Kudos

In query only one display characteristics which is Paylot

Free characteristics are: Inbscnt, PstDat, Oip, BnkNo, BnkClrAc, Calmonth, ContAc, ContRef, ItmNo, ValDat, BnkRef, DocTyp.

Aggregate characteristics are:

100077: BnkNo, BnkClrAc, Calmonth.

100083: Inbscnt, Oip, Calmonth.

100079: PstDat, ValDat, Calmonth.

100078: Paylot, Calmonth.

100081: DocTyp, BnkRef, ItmNo, Calmonth.

Line Item characteristics: ContAc, ContRef.

Hitting characteristics shows in RSRT: Calmonth, DocTyp, PayId, ItmNo, Paylot. (Bad aggregate if created)

I think every thing is mentioned above, Now what we are missing??

Regards.

FCI
Active Contributor
0 Kudos

So none of your aggregates contains the characteristics your query needed, and you wonder why they are not hit ?

former_member198905
Participant
0 Kudos

Hitting characteristics shows in RSRT: Calmonth, DocTyp, PayId, ItmNo, Paylot. (Bad aggregate if created)

I think you missed the above lines as per your recommendation when I created  this aggregate, larger number of minus signs shows, it mean valuation is worse of this aggregate.


As per your recommendation I create an aggregate containing all characteristics which show in RSRT and those characteristics which contains filters in query. Now query is hitting this aggregate.

But this aggregate has larger number of minus signs, if I create it with filters or without filters, it mean valuation is worse of this aggregate.

which I already mentioned above.

I am still waiting for your solution???

FCI
Active Contributor
0 Kudos

It just means that your query probably needs too much details for an an aggregate to be useful.

If your query needs the same level of detail than the cube has, an aggregate won't bring you any benefit.

former_member198905
Participant
0 Kudos

In this scenario, Is there any aggregate you recommend or no aggregate needed??

FCI
Active Contributor
0 Kudos

Test and see...

Generally, a low rated aggregate has a loading and storage cost too heavy compared to its query benefit. In some exceptional cases, this may not be the case.

Regards,

Frerderic

former_member198905
Participant
0 Kudos

One more thing I need to clarify from you.

What is basic aggregate for speed up change run??

Following SAP link shows

"Do not delete basic aggregates that you created to speed up the change run."     

Design and Components of Aggregates - Business Intelligence - SAP Library

Regards

Imran

FCI
Active Contributor
0 Kudos

The master data change run updates the navigational attributes you may have stored in your aggregate.

If you only have in your aggregate (A) the navigational attribute and not the characteristic itself (ex: material type and not the material), the rebuilding of the aggregate can take a really long time (it has to be completly rebuilt).

It may then be interesting to create an intermediary aggregate (B) with the characteristic (ex: material) so that the rebuilt of the agregate A can be done by reading the aggregate B and not the basic cube.

Aggregate B may not be used by queries and so, its last used date is not updated. The help just indicates that an aggregate with no last used date may actually be used (to build other aggregates).

Regards,

Frederic

former_member198905
Participant
0 Kudos

Thanks Frederic for detail description.

Another cube which aggregates are hitting through out the month perfectly and within a 2 minutes query output is displayed but after every month end closing when all transaction and master data process chains are unscheduled same aggregates are not hitting as a result same query which display its result within a 2 minutes displayed its output after approx. 45 minutes. After closing when daily data-load process chains running again the aggregates are hitting perfectly why it happens??? and how to rectify it???

Thanks

Imran

former_member198905
Participant
0 Kudos

Dear Frederic

Waiting for your last expert opinion.

Thanks

Imran

former_member202718
Active Contributor
0 Kudos

Hi Imran,

Use the Characteristics of Aggregates in the Query input and then check.

Create Agg as per need of the Query and not necessarily as suggested by system.

Regards

SVU

former_member198905
Participant
0 Kudos

Hi SVU

Sorry I don't get your point "Use the Characteristics of Aggregates in the Query input".

Please explain it in detail.

Regards

Former Member
0 Kudos

Hello Imran,

Have in mind these statements:

  1. The larger the number of minus signs, the worse is the evaluation of the aggregate, "-----" means: The aggregate can possibly be deleted.
  2. The larger the number of plus signs, the better is the evaluation of the aggregate, "+++++" means: The aggregate could make a lot of sense.

About

  Why BEx query not hitting aggregates?

It's all about the query definition. You need to have the same characteristics in the display of a Query and in the aggregate definition. If query and aggregate characteristics are the same, then the system reads the aggregate.

Before you create your Agg, first check you query definition and create a single aggregate with the same char and run some tests.

If there is a doubt you can RUN RSRT and click the Performance tab. You can check Detalied info or click on "Display SQL",so you can analyze the read and why the system is not using aggregates.

Hope this helps.

Regards from México.

former_member198905
Participant
0 Kudos

Before you create your Agg, first check you query definition and create a single aggregate with the same char and run some tests.

Aggregate which is equal to query definition have large no. of minus sign in its evaluation column. I create 2 aggregates of 2 queries but both have worse evaluations. what next??


In RSRT "Display SQL"  option how to find why system is not using aggregates??



Former Member
0 Kudos

Hello Imran,

when you click in Perfomance Tab, the list showed if an aggregate is used in your query. It should has a green light.

If not, in SQL tab, the system list the search with all chars used in the query execution. Include all chars you find in this section in your aggregate, active it, load it and execute again your query.

Regards.

former_member198905
Participant
0 Kudos

Hi Gonzalo


In RSRT's Perfomance Tab, the list showed aggregate has a green light.

But in RSRT's Aggregation Layer Tab showed only cube name in aggregate column instead of aggregate numbers.

Regards 

john_hawk
Active Contributor
0 Kudos

Hi Imran,

Check the query properties in RSRT. Is it possible someone turned off the use of aggregates to analyze the query, and forgot to turn it back on?

Please don't forget to record your final solution and to mark the question as answered.

Good luck,

John Hawk

former_member198905
Participant
0 Kudos

Hi John

Which query properties in RSRT is use to turned on the use of aggregates to analyze the query??

Thanks

john_hawk
Active Contributor
0 Kudos

Hi Imran,

I don't have a BW system to log into, but there should be drop down menu for "method of execution". Select "Without Chache and without aggregates" or words to that effect.

John Hawk