Bi accelerator and calculated key figures

[Former Member]

Hi,

We use BI Accelerator and we have a performance problem because of calculated key figures.

The query runtime is divided by 10 when we remove these KF from the query. Knowing that we couldn't calculate these KF in the infocube (during infocube loading).

I wd like to know if the BI Accelerator does not take into account the calculated key figures.

If yes, what is the solution to by-pass this problem?

In other words, do you have please any suggestion to optimise a query run Time with calculated KF and BI Accelerator?

Thx.

Radj.

Edited by: Radjech Radjech on Jun 9, 2011 9:28 AM

[Former Member]

Calculated KFs are processed in the OLAP engine therefore can't benefit from BWA, unless you have BW 7.3 + BWA 7.2.

To prove that create a simple query on top of this cube, run it and check the statistics to see if BWA indexes were used or not ( the table names that end with $X). And add a CKF and run the same query and check the stats again

Cheers

Tansu

[Former Member]

Thx Tansu.

I already tried this test and shown that the query doesn't use BWA indexes when It contain CKF.

We are unfortunetly in BI 7.0.

Is there another solution to by-pass this problem ? knowing that we can't calculate this KF in the Infocube (loading process).

Radj.

[Former Member]

Pre-calculating the queries/web templates via Broadcasting / Reporting Agent every day, will be an option to calculate the query result, and the run-time performance will be faster. http://www.tnsr.eu/fileadmin/user_upload/Documenten/PDF/How_To/How_To...Perform_Tuning_with_the_OLAP...

[Former Member]

Hi

To expedite the database read time, you can still use aggregates if possible.

thanks

[Former Member]

Hi Tansu,

But we have a BWA, Can we use the aggregates even if we use BI Accelerator?

Hi Bindu,

I m afraid that pre-calculated solution doesn't work, because it concern an advanced view with a lot free characteristics (the user could choose among these free characteristic by drill down process ...).

Radj.

Edited by: Radjech Radjech on Jun 13, 2011 4:37 PM

[Former Member]

Yes, exclude this query from BWA by updating NOHPA field with "X" in RSRREPDIR table. Then it should use aggregates

thanks

[Former Member]

In this case, is there aggregate more speed than BWA ?

[Former Member]

I don't think so. The aggregates in your case might be the only solution, that's all I am saying

Thanks

[former_member93896]

Hell Radj,

In general the data selection does not depend on what CKF are contained in the query. What is the definition of the CKF? Also is this on a single InfoCube or on a MultiProvider?

Regards,

Marc

SAP CSA - Technology

[Former Member]

Hi Marc,

We use a multiprovider based on a single infocube.

This infocube contain arround 1 million records, and 3 dimension that 2 are in line item.

we are in BI 7.0 and we use the BI Acceleratore.

The CKF has been created following the SAP doc (How to Count the occurrences of a characteristic relative to one or more other characteristics) :

Please see the below link, [http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/7e58e690-0201-0010-fd85-a2f29a41c7af]

Or have a look on the detailed steps below

1. Create a Key Figure with name u201CCounteru201D.

2. Insert this InfoObject into your InfoCube.

3. Add the InfoObject to the Communication Structure and recreate the Update Rules of the InfoCube.

4. In the transfer rules set the key figure counter to a constant value of 1.

5. In the BEx Query Builder create a new calculated key figure for the InfoCube. The formula should be u201C=Counteru201D i.e. simply drag the key figure counter to the formula definition area.

6. When you save the calculated key figure you have to provide a technical name and a description u201CCounter (Average per Material)u201D. Additionally, you have to define the exceptional aggregation behavior of the calculated key figure. On the popup window press the button u201CEnhance >>>u201D. For Exception Aggregation choose u201CAverage of all valuesu201D. The reference characteristic should be set to the characteristic that you want to count. This is material in our case.

7. Create a second calculated key figure. Here you define the formula to be u201CCounter/Counter (Average per Material)u201C. This calculated key figure now shows you the number of different materials relative to any other characteristic. In the properties set the number of decimal places to 0.

8. Define a new Query. In the columns use the key figure "Counter (Material per any other Char.)u201C. In the rows insert a drill

down with characteristic customer. Add the remaining characteristics to the free characteristics.

9. In the properties for characteristic customer you should set Suppress result rows to u201CAlwaysu201D.

10. Run the query. The result will be the number of different materials per customer.

11. By setting a filter on order you now get the number of different materials per customer and order.

thx for the answer.

[Former Member]

Raj,

Sorry, but your processes is little bit confusing. Can you be clear with the example of what you want to achive using CKF ?

Regards,

Sonti.

[Former Member]

Hi Sonti,

We have created a CKF (in the query designer) based on a KF Counter (stored in the infocube).

We have followed the "SAP how to" to create this CKF, as explained in the link below:

[http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/7e58e690-0201-0010-fd85-a2f29a41c7af]

Now, we have a performance problem because of this CKF, even if we use BI Acceleartor.

For information, the query is created on multiprovider that contain only one infocube.

I hope that is more clear now.

Radj.

[former_member205352]

> 6. When you save the calculated key figure you have to provide a technical name and a description u201CCounter (Average per Material)u201D. Additionally, you have to define the exceptional aggregation behavior of the calculated key figure. On the popup window press the button u201CEnhance >>>u201D. For Exception Aggregation choose u201CAverage of all valuesu201D. The reference characteristic should be set to the characteristic that you want to count. This is material in our case.

As far as I know BWA 7.2 and below never supported exception aggregation.

I think this feature was added in BWA 7.3 (never tried it though).

[Former Member]

I believe the progression in BW was from 7.0 to 7.01 to 7.3, and for BWA 7.0 to 7.2.  I do believe there is some support for exception aggregation in the BWA 7.2 engine.

See also http://www.insiderlearningnetwork.com/dr_berg/blog/2011/01/05/bw_7.3_feature_map