Accessing Data In OLAP Cube is slow

Question

Accessing Data In OLAP Cube is slow

Ansari Mohamed Asif

SSC Veteran

Points: 276
More actions
November 21, 2005 at 11:25 pm

#111517

Dear Colleagues,
I have a OLAP cube in Analysis Server with 4 measures and 4 dimensions.
The cube holds the data right from Jan'05 till today.The cube size is 1.6 GB.
When I open the cube in analysis server to browse the data it take 15-20 minues to show the data.
Is there any way to speed up this and reduce the time?
Thanks in advance.
Asif

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Must be a fair amount of data being loaded in if it's just from Jan and is 1.6Gb.

Are you using partitions? If not, you may want to investigate this as it can make query response time fasster, plus when you load you only load data to one, or at worst a few partitions.

Are you using aggregations? If not, you'll want to add these, but I'd look at having partitions first, otherwise your aggregations could/will take a long time to build as you'll need to aggregate the entire dataset rather than a subset (ie a partitions worth). Maybe partition on month? It will depend on how many rows you're loading per month, or week etc. In AS2000 it's bit of a pain to manage the partitions, if you can move to 2005 this becomes somewhat easier.

As to the 15-20 minutes, I'm guessing you have no partitions and no aggregations (and prob no default members) so when the cube opens, it's opening at the highest level (ie top of all dimensions) and so has to aggregate on the fly all of the data beneath these levels to present you with a starting number/s.

Steve.

Ansari Mohamed Asif SSC Veteran Points: 276 More actions · Answer 2

Dear Steve,

Thanks for the reply.

Yes, we have not created additional partitions and also no aggregations. The cube has the default one partition only.

Can I create aggregations on this single partition?

And with this aggregation on single partition, Will it help in boosting the spped? Or I will have to create more partitions?

Thanks.

Asif.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hi Asif,

You can definitely design aggregations for that single partition, but as mentioned previously these may take quite a while to build (dependent on incoming row count, and especially your hardware config --> this means RAM hungry. See the Performance Guide on MSDN for how to set your processing cache).

To create the aggregations, in Analysis Manager, right click on the cube and select 'Design Aggregations'. While eveyone's situation is different, a normal approach is to use MOLAP and then go for either performance gain or size on disk (of aggregations) to tell the optimiser how is should plan the aggregations. But, if this suited everyone, there wouldn't be the other options, so read up on them to determine if they are better suited to your situation.

Cheers,

Steve.

Ansari Mohamed Asif SSC Veteran Points: 276 More actions · Answer 4

Hi Steve,

If I add the Aggregations to the cubes, will the existing data in the cube be retained or lost,as the fact table for the cube does not have the data.

We truncate the fact table once cube is processed incrementally every month.

Thanks,

Asif

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

I'm pretty sure that you need to process the aggregations at the same time as the cube.

Do you archive your fact data anywhere? If not, you're running with a big risk factor in your production strategy.

Other than disk space, is there a reason that you truncate the fact? If you're using Enterprise Edition, with a little workyou could use horizontally partitioned tables, so keep only a months worth of data in each subtable, but the sum total of all of these tables is accessible through a single view. Just a thought.

Steve.