cancel
Showing results for 
Search instead for 
Did you mean: 

CCA Query Performance

Former Member
0 Kudos
168

Can someone please tell me why everytime we run a query using the 0Occa_c11 cube and I watch the query run using SM50 it always does a long sequential read of the cube fact table. Its seems like it retrieves all the data at the database level ignoring the fiscal year constraint and then filters the data on the client instead of on the database server for fiscal year. Can someone please help out. I need to make this query run faster.

Thanks

Richard

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

hi Pizzaman

If u dont mind, pls provide the info to login to BW expert site to my id ksribat@yahoo.co.in

Regards

Sriram

Former Member
0 Kudos

BW Expert is a subscription based newsletter / web site. If you want access you'll have to subscribe.

Former Member
0 Kudos

Create partitions or aggregates

Former Member
0 Kudos

Delete and build indexes on this cube.

Former Member
0 Kudos

We did this and it tramatically reduced the time. However the query runs about two minutes ther first time and I know its doing a table scan on the large fact table. I need to learn how to control this.

Aggregates should only be considered as a last resort. I've already cut the query run time from 20 minutes to 2 minutes by uping the dbms buffer size , recreating the indexs and then running update statictics. I forgot this query has an extremly large hierarchy. No way I'm going to just give up now and build an aggregate.

Former Member
0 Kudos

If you filter on the fiscal year you should definitely partition the cube on FISCPER. You should also switch on query caching. You should also check RSRT -> Query -> Properties. The read mode should be set to H (Read at navigation/hierarchy expansion).

Best regards

Dirk

Former Member
0 Kudos

If you partition the cube on 0FISCPER, your query will have to filter on 0FISCPER in order to trim unnecessary partitions. So you would need to modify your query to reference 0FISCPER instead of 0FISCYEAR, or use some mechanism to populate 0FISCYEAR from 0FISCPER.

You fail to mention what DB and version. If you are using Oracle and are at the latest version 9.2.0.6 or .5 with some patches you should set the STAR TRANSFORMATION ENABLED parm to TRUE. (Review OSS Notes) This will use other execution plan than Full Scan of the Fact table which can help a lot.

A full scan is not necessarily a bad thing. If your query is not very selective, a full scan is being chosen because it is the fastest.

Don't understand the hesitancy to create an aggregate. In this case, if it is really performing a full scan of the fact table, and you filter on 0FISCYEAR, create an aggregate(s) that contain just that year - seems like the only solution if you can't get a Star Transformation.

Former Member
0 Kudos

Thank You this is very helpful. We are on Oracle 9.2.0.5

I'm going to re-check everything you mentioned.

Former Member
0 Kudos

Where do i verify query caching is on?

Former Member
0 Kudos

Quick way is tran RSRCACHE - should show you Cache parms and Cache Active should show green light. It will also show you size of OLAP cache. You can click on Main Memory and should be able to see query results that have been cached.

Using the OLAP cache to it's fullest capability can require you to change variable settings and an educational effort to users as the interaction in Bex when you refresh a query is different if you are using Variables set so that they can change be changed durin navigation. That's really a whole other topic. I'll see if I can find a ppt or something that talks about it in detail. BW Expert recently had a good article on OLAP cache. I'll go post the issue/date and maybe you have access or can get a sample copy.

former_member188975
Active Contributor
0 Kudos

Hi Richard,

For this query in particular, where is Fiscal year included, in the Filter area or Free Chars ?

Former Member
0 Kudos

I'm at home now and trying to remember but the fiscal year is in the filter. Can you explain the how it would react differently if it was in the free?

Thanks