Hi,

I noticed that one partition is created automatically and by default
when I create and process a cube...

Here's the scenario that I am faced with:

My cube has 4 Dimensions and I am facing a performance 'concern' despite
having set the aggregation storage option as 100%.

Do you think that I should use Partitions? I'll have to read and learn
about Partitions but as long as this is the solution?

--
Thanks.

Hi,

Regarding your performance issues, here are some advices

Take into account
- the size of your dimensions (check the number of leaf members)
- and most of all, the size of your fact table
- your measures (and particularly if you use DISTINCT COUNT or crossjoins)
- your agregation strategy for each cube
- other points like database modeling, hardware, service packs, etc..

Usualy, you may take advantage by using partition if you fact table is very
big.
If this is the case, the idea is to split it into several smaller physical
partitions (but this is transparent to the final user)

1 : create partitions on your datawarehouse using a split criteria (usualy
date)
2 : you'll be able to define index rebuild strategies. This will improve you
load process (only one partition is impacted, and each partition's size is
under control) as much as your requests on these tables.
3 : define olap partitions based on these database partitions. you also have
to define the date criteria used for these olap partitions.
4 : the same way you were able to improve tables' size and indexes, you'll
be able to control your cube partitions' size and define agregation
strategies for each partition.

Regarding agregations, do not use 100%, this is not useful and takes a lot
of disk space.
Just use the agregation wizard and set the level of performance you want.
Usualy, a level of 20% is enough to maintain a level of performance around
70%.
Define levels according to each partition (old ones and the current one).
The daily process should only impact the current partition (no need to
rebuild old partitions > thus you gain time).
Later, to fine tune your agregations, have a look at "usage based
aggregations".

If you use DTS to rebuild cubes, you can define the partitions you want to
rebuild (instead of full processing) and also if the process is full or
refresh.
For partitions, if there are few (for example, one new partition each year
or every 6 months), you can manually create them, else you need to
programatically create them (database and olap).

Last remarks
- there is a microsoft software called 'Partition Explorer' to easily manage
your partitions
- check if you have applied latest service packs (SP3a for
SQLSrv2000/AS2000).
- if you consult your cubes through network, check your network bandwith and
performance (100MB/s, full duplex); You can make a test by conulting
directly on the server
- antivirus
- check you client PC (cpu, ram) and the software you use (Excel XP SP3?)
- Analysis Services likes several Cpus and RAM
- the speed of your hard drives and controller type (raid 10)

I hope this will help you
Regards

Jean-Christophe CHENIS






"Learner" <wantnospam (AT) email (DOT) com> a écrit dans le message news:
MPG.1b3ba3e0942e03b4989736 (AT) msne... microsoft.com...

I'd also recommend that you read the following. Many of the topics
discussed below are covered in the whitepaper.

http://www.microsoft.com/technet/pro.../ansvcspg.mspx

Sean

Sean Boon
Microsoft Office BI


"Jean-Christophe CHENIS" <hidden (AT) hidden (DOT) com> wrote

Hello CHENIS and Sean,

Many thanks for your replies. I visited the site but there are links to
many whitepapers. Sean are you referring to the web page itself or any
particular whitepaper...

Till then I have a few questions (silly one's) and will appreciate your
reply.


1) Is there a easy/simple way to determine/count the number of leaf
members?

2) My fact table has 173633 rows. This would probably fit into the small
category. Right?

3) The service PACK MIGHT be the problem. I have SP1 and gave up trying
to apply SP3a after many attempts, posts, and reading. Every time i got
the 'Strong Password.... dialog box"

I'll appreciate your reply.

Many thanks.

1) Leaf member count:
easiest way is : when desiging your dimension.
click on the last level, there is a properties tab (below left) with an
"Item count"
refresh it and check this value.
else you can estimate a global count with a select count(*) from
your_dimension_table

2) fact table has 173633 rows
I would say it's rather small
but it also depends on number of columns and joins with dimension tables
(check your indexes on join criterias)
note : you have the same item count for the fact table. This help Analysis
for its agregates wizard.
Check if this value is correct compared to a : select count(*) from
your_fact_table

3) Indeed, SP3a corrects a lot of bugs
Note that some (rare) people here have had problem installing it or
incompatibilities with their applications
As for me, i didn't have any problems at all so i won't be of great help for
you
note: there is SP3a for sql server AND SP3a for analysis

Jean-Christophe

"Learner" <wantnospam (AT) email (DOT) com> a écrit dans le message news:
MPG.1b3cc24b9621b7f2989738 (AT) msne... microsoft.com...

Hi,


Well my dimensions are small. 100 records, 200 records, and 150 records.
and the larger ones are 840 records and 370 records. I'll also try to do
an accurate count using the tip that you gave me (below):



Yup that's what I thought too

Actually I started out with using views to be used as the source for my
dimensions but as I read more and more posts I am getting confused. Some
seem to advocate that using views (to "flatten" the data) for dimensions
is a good idea while others say that the Snowflake approach is better
(which is back to really the "relational manner' of linking things.
Isn't it??). This is where I can really use some guidance.


Regarding the success in terms of applying SP3 (or is it SP3a) is a
painful chapter so far... I plan to try it again but have given up for a
while after spending almost a full day without success


Many thanks.