Re: 0 Aggregations designed

certainly, SSAS 2005 is completely different from 2000
so the design of the aggregation is different.

having a lot of aggregations don't result in a better performance if these aggregations are around already optimized objects.

to track what's appends in the cube, use the profiler and choose the right options to display the aggregation usage to see which one is used by your queries.

have you correctly setup the attribute relationship option of each attribute in your dimensions? (setup it to rigid if possible)
have you optimized your user hierarchies with the unique key and unique name option if applicable?

what's appends if you don't force the aggregation usage for your attributes?


"Fazal" <Fazal@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:44C05705-E4A3-436D-97DE-01428ACF5187@xxxxxxxxxxxxxxxx
Hi Ramunas and Chris,

Thank you very much for your response to my query.

I tried changing AggUsage to unrestricted and still I do not get high number
of aggregation.

I have the same cube in AS 2000 and I have about 800 aggregations with only
2% of performance . While in AS 2005 (Aggregation wizard) I get 140
aggregations with 25% of performance. This does not make sense to me. Does
2005 create aggregations differently than 2000 and if yes then these 140
aggregations are somewhere equivalent to 800 aggregations of 2000? Wizard
says that performance is 25% but still the cube is too slow.

Out of 32 dimensions about 20 are having only one level. i.e. Y/N. So my
dimension tables has IntFlag and CharFlag as Column, I use IntFlag as Key and
use CharFlag as NameColumn.
Other dimensions are having more than 1 level and I have all as natural
hierarchies.
Also, I have partitions based on one of the Time Dimensions's Month
attribute. Each partition has around 9milion rows of FACT table. I have total
of 13 partitions. I have 25 Calculated members and I use Excel 2003 to query
the cube. I am really concerned about the aggregation thing....It would be
great if you help me out to determine whats wrong am I doing...

Any help is greatly appreciated.
Thank you,
Fazal

"Fazal" wrote:

Hi Ramunas, I AggUsage to Unrestiricted. Still my number of aggregations are
only 150 and performance is worst. My same cube in 2000 has 800 aggregations
and it works fine. How can I achieve same in 2005?

"Ramunas Balukonis" wrote:

> Fazal,
> try replacing AggUsage to Unrestiricted.
>
> Ramunas
>
>
> "Fazal" <Fazal@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:D06A3B80-C338-4BCB-957D-7145A702C10D@xxxxxxxxxxxxxxxx
> > Hello,
> > I have designed cube in AS 2005. I have set AggregationUsuage > > Property to
> > FULL for most of the attributes that the users will query most. When > > I try
> to
> > design the aggregations, it does not create any (It gives me message > > that
> 0
> > aggregation desgined). If I change the AggregationUsage proeprty back > > to
> > "Default" then it only calculates 140 aggregations with 24% > > performance
> even
> > though it is set for 100% performance gain. Can anyone please help me > > what
> > else do I need to change inorder to improve the cube perfromance. I > > have
> > close to 9million records in my Fact table and about 30 dimensions.
>
>
>
.

Relevant Pages

  • Re: Can someone suggest me how do you define a big cube..
    ... if you have a DCount aggregated measure, you must store it in a dedicated cube to make sure the performance was good enough. ... to improve the performance insure that your hierarchies in the dimensions are setup with the uniquekeys option. ... Does your queries hit your aggregated content? ... and how big are your aggregations and MOLAP storage on these disks? ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... My guess here is that it is not aggregations but rather indexes being ... The actual processing of partitions doesn't appear to be significant here ... Processing Cube 'Sales Current' completed successfully. ... aggregations when the dimension is updated. ...
    (microsoft.public.sqlserver.olap)
  • Re: Aggregations/performance
    ... If you get good performance out of your cube, ... The first point is that you can certainly have aggregations with a changing ... As a worst-case, even if you have several changing dimensions, this ...
    (microsoft.public.sqlserver.olap)
  • Re: dimensions and cube aggregs
    ... You might consider looking at the Analysis Services Performance Guide. ... > I have 8 dimensions that are needed for various queries. ... > I am trying to use them all in 1 cube, ... > The problem is the aggregations get a bit out of hand even at 20%. ...
    (microsoft.public.sqlserver.olap)
  • Re: #ERR when trying to view detail of time dimension
    ... I chose HOLAP becuase there are 3.8 million records and I thought it would ... MOLAP though. ... just designing aggregations with the design ... to query the cube, then I would optimize the storage settings based on usage. ...
    (microsoft.public.sqlserver.olap)