Improving Cube Access Time

[collierd]

Hello

I have 6 cubes all built overnight from a SQL database.
So, effectively, the cubes are stand alone when in use.
They are used by 70 users at various sites around the UK.
These users use both Powerplay Client and Web to access data.
Problem is, 4 cubes work fine (These are between 15 and 400Mb) and 2 don't (750Mb - 1.5Gb).
People can access the 2 problem cubes but, with increasing regularilty, it is timing out (They are set to 300 secs).
Also, even the simplest of queries can take a number of minutes.
This isn't consistant as, at times it works fine.

Does anybody have any suggestions as to what could cause this - the users are getting restless!

Could it be the cube [Build method/properties etc] (if so what do I check).
Could it be the client PC's (They are all a reasonable spec)
Could it be the network (Seems to ping ok)
Could it be the number of people accessing it (How do I check and can this be limited)
Anything else ??

Thoughts would be appreciated

Thanks

Damian.

 

[drlex]

Damian,
I had a similar problem which was solved by stripping down the cubes to more manageable sizes and considering the linking which can be done between them.
You may have Impromptu client and web licences which could allow cube > report drilldown for detail.

By stripping down, I mean removal of less useful dimensions, levels and lengthy descriptions. You might need to have a user poll to help you on this. Some models can grow like topsy when a user says "oh, can you add in a dimension?", when the better course is to create a new model.

Additionally, calculated measures (or roll-ups other than sum) and alternate drill downs can really slow up performance.

In the end, you may find that a dozen medium-sized cubes are more suitable for your users.
What are the category counts in your two problem cubes? How does this compare to your ok ones.

Of course, cube properties can be tweaked (partitioning,cross-tab caching), but the effects are not as great as considering the whole model and its use.

That's my 2p worth. Hope it's of some value.

soi la, soi carre

 

[DoubleD]

drlex is right. Usually when you've got a cube approaching the 1GB mark, you've either got too much detail in it, or you're trying to answer too many questions with it. Putting too much info in a single cube will ultimately defeat the purpose. Users will have information overload, aka analysis paralysis, and not be able to find the meaningful data.
For instance, it is a good rule of thumb that if you're keeping day level data in your cube, your cube shouldn't contain more than 90 days of data. How can you impact 90 day old info at a day level?
If you must keep the cubes the same, play with partioning. This will extend the cube build time, but will shorten the access time.

Pain is stress leaving the body.

DoubleD

 

[collierd]

Thanks for the advice
I will look into these suggestions

 

[flex13]

The number one reason for poor performance is poor model design. As my wise co-contributors have pointed out, too much data can result in an uneccesarily large, bulky cube. You need to figure out what the reporting requirements are for your end users and create a model that satisfies just those questions.

If for some strange reason, they NEED all of that data built into the cube, then you can look at alternative approaches.

- Drill through to another cube or Impromptu.
- Cube groups
- etc.

You may want to try increasing your Read Cache value which affects PowerPlay.

 

[monsky]

Some users have the impression that cubes can contain large amounts of data without any performance penalties. That is why you still need a data mart/data warehouse for details. Cubes should contain only immediate summaries with atomic data at the back end.