Re: Fairly Large Cube-Performance Improvement recommendations

Hi Dave,

I beleiev the storage design wizard starts maxing out ( I mean does not
move) if the cube has large number of dimensions.

We have 30 dims in the cube and the storage design wizard taked about 45
minutes to move from 5 to 6%.

Not sure what to do with this expect waiting for it to complete our deired
rabge of 20%. As an fyi, it toof 4 hours to design aggs for 10%.

Any better ideas?

Thanks,

"Dave Wickert [MSFT]" wrote:

> The major problem I see the high dimensionality.
> Can you create a subset of the dimesions to create aggs on?
> For example of the 30, are there like 10 which most users use and the other
> 20 are infrequently used?
> If so, you can set the aggregation usage at TOP LEVEL ONLY for the 20
> dimensions -- this reduces the cube complexity down to 10 dimensions and you
> should be able to easily design aggs for that.
> As to what else might be slowing the system, perform this simple test:
> 1) execute a fairly long query, let's say one that runs for 20 seconds.
> 2) wait 1 minute
> 3) execute it again within your tool
> Is response time fast or slow? This will tell you if caching is coming into
> play. What you want to see happen is that the query runs very quickly, let's
> say in a second or 2. If it isn't then there is something else to focus in
> on -- i.e. what calculated measures are being done, why aggs are not coming
> into play (even if cached), etc.
>
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@xxxxxxxxxxxxxxxxxxxx
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Karthik" <karthiksmiles@xxxxxxxxx> wrote in message
> news:1114639365.825193.205410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > I'm having problems with very poor response time when users query our
> > cubes and I thought I'd make a posting here and seek recommendations of
> > others who have had similar issues. We have a fairly large cube with 55
> > partitions- processed size of 3GB and about 30 dimensions, 30 measures
> > and 160M rows. Most dimensions are single level dimensions with less
> > than 50 members. Our hardware setup is a Quad processor box with 4GB
> > RAM and RAID 5 storage. As we have SQL Server and As sharing the same
> > box, we have not enabled the 3GB switch.
> >
> > We have tried warming up the cubes by executing MDX of the common
> > queries so that there is a better response time when users first query
> > the cubes. We have also used the partition aggregation utility to put
> > in aggregations across the big and commonly used dimensions. But, none
> > of these have contributed in significant improvements to query response
> > time. The storage design wizard maxes out at about 10% performance
> > gain! :(
> >
> > Any ideas...?
> >
>
>
>
.

Relevant Pages

  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: Timeout problems due to huge Fact Table
    ... I've also experienced very poor performing queries that the cube itself has ... of dimensions & with 'materialize' set on the relationships in the dimension ... to fill your cube you are using a very complex query instead of a table. ... level and adding required table in the database. ...
    (microsoft.public.sqlserver.olap)
  • MDX : Taking a union of slices
    ... In a MDX query, I would like to slice on 2 dimensions in the following ... The sales cube in the sample OLAP database I need to retrieve the Unit ...
    (microsoft.public.sqlserver.olap)
  • Analysis Services Query Log Inconsistency
    ... Analysis Services 2000 allows up to 128 dimensions per cube; ... level in each dimension a particular query uses. ...
    (microsoft.public.sqlserver.olap)
  • Re: Analysis Manager Operations extremely slow
    ... Each of the cubes have 12 partitions with monthly slices. ... Counting multiple hierarchies as dimensions, we 10 regular dimensions and 6 ... The first time we bring up the cube edit it take 1 min 20 secs, ...
    (microsoft.public.sqlserver.olap)
Loading