Question:
Hi
Has anyone experienced any problems like the following :
Query created on a compressed cube (compressed from 1.4 billion records down to 34 mill) Aggregates created on cube for queries. If you run a query for a single plant the query runs within seconds (about 6 seconds) if you run for multiple plants the query takes around 40mins minimum. It only does it when we try on multiple plants and its really only been a problem since we put in SP13 on BW3.1 content 3.3 any ideas?
Cheers
Daniel
_________________
life only sucks when you let it get that way!
Answer:
-Please run the query and have a look in SM50. what tables it is accesiing E table first or F table.
- From my understanding u r not qurying a Multi provider. If yes then you have to use the sub query technique.
- Check wether u have exculdes in ur query. Better dont use them.
Please check all above.
If you can gimme a littile more picture i can be helpful
Answer:
A compressed cube (compressed from 1.4 billion records down to 34 mill)
Wow, i gotta stop complaining about my 3 million-record-Cube
And maybe a bit obvious (i know you're no beginner), but did you check via RSRT if your selection wiht multiple plants really uses the aggregates?
Answer:
Hi
Yep check RSRT and it uses the aggregates something strange though!! if you run the query and instead of putting a FROM : TO : range and instead use the drop down and enter the values in individually then the query runs in under 30 seconds. Take the individual ones out and enter the range back in and your looking at a 45 min report!!
Any Ideas?
Wow, i gotta stop complaining about my 3 million-record-Cube
Ahh those were the days little cubes!!!
(i know you're no beginner),
Sometimes a feel like a beginner everyday BW amazes me!!!
_________________
life only sucks when you let it get that way!
Answer:
When you select a list of specific values the index can be accessed directly. When searching for a range (or if values are excluded) some kind of scan of all values is necessary. My guess is that a large table is being scanned repeatedly.
Assuming the DB statistics are up to date and the indexes are free of errors this may be a case where manually creating an additional index will help.
Answer:
Where to create index to speed up the query?
Thanks.
Answer:
Where to create index to speed up the query?
It would need to be done manually outside BW, you would probably need to work with your DBA