hello all!

we have:
MS analyses services witha special Cube (MOLAP) on
W2Kserver (professions), dual Xeon 1,7Ghz (1MB cache),
4Gigs ram, well optimized system with Raid5 disk array
and SQL server 2000

FactTable: 3million rows
Dimensions: 22
Measures: 23
Calulated Members:11

When i do a full process with 5% aggregations, it needs ~2
hours, which i think is ok.
When i do a full process with 15% aggragations, it needs
about 3 days (!!)
The server has a cpu load of 2-3%, the analysess service
is well conifgured (sql-performance.com), the harddisk has
a disk wait time of 6, which is ok for raid5 i think,
so we cannot find any problem, but 3 days, ..thats too
long.
What happens if i do the full process:
1) SQL Server loads the fact table joined with the
dimensions...needs ~10 minutesm, with high cpu load..thats
ok. (so i don't think that the SQL Server is the problem)
2)the Server is busy for the rest of the time, but without
high cpu load, or not enough memory, everything seems fine,
but the machine is busy (i click on start, and 1minute
after the menu popups)

Q1: are 22 dimensions too much? can anybody help me to
create junk dimension (what is a junk dimension...any
examples?)
Q2: how can i find a bottleneck, eg. if the harddisk is
too slow, what performance-measures should show which
value?
Q3: how much threads, and how much virtual memory should
the process use?

i know that a partition is a solution, but not now, if we
have only 3 million records..in the future, we think of
100 and more million records, then i know that i will have
ot use partitions.

i help that somebody can help me...thanks for any help in
advance,

Thomas Gugler

It sounds as if you have a distinct count in that
cube...possibly? With that many dimensions AND a distinct
count, 3 days for 15% aggregations doesn't sound that
farfetched to me. You would be better served
incorporating 'Usage Based Optimization' into your cube
rather than generic aggregations.

KRS

3 Days is definitely way too long. Your cube is big - but not that big.

I have cubes with around 5m rows, 29 dims, 20 measures that refresh in about
5-7 hours with 40% aggregation. They work on slower chips with half your
RAM.


here are some things to review:

1. if you are using W2K professional (not server), SQL server will run much
slower (fewer available threads).
2. Ensure your machine is fully using all the RAM. Turn the /3GB switch on
in your boot.ini
3. As mentioned previously, remove all distinct count measures to their own
cube (one for each). Then recombine using a virtual cube
4. Ensure that all your dims have SINGLE joins to the fact table. Also,
improve the quality of your RDB to ensure that the members at the bottom of
each of your dimensions have UNIQUE KEYS.
5. If you can do 4 above, choose "cube optimization" in Analysis manager
cube editor to improve querying time.
6. Processing time is driven more by RAM and CPU than by Disk Speed
(especially for the aggregation process). So make sure that your server is
not running anything else while you are processing a cube. Disk speed
becomes important if you have many queries against cells that have NO
aggregations.
7. Enlarge the process buffer.
8. If you have developed your cube in stages, it pays to delete the
prototype and restart with a clean build once you know how it should look
and work. AS2K has been known to create inefficiencies with cube structures
that get changed a lot. Further, consider migrating your repository to SQL
SERVER (from MS Access).





"Thomas Gugler" <tg (AT) immobilien (DOT) net> wrote

You might want to spend some time with this white paper.
http://www.microsoft.com/technet/pro...e/AnSvcsPG.asp
It addresses many of these (and other) issues.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Avi Perez" <avi.perez (AT) irisbi (DOT) com> wrote