Hi,
I always use Analysis Services with Fact Tables that contains only 50.000
rows. The problem is, that now I need process a cube with a big fact table
with 30 million rows, I have 10 dimensions (all dimension has up to 20
members) and 8 measures, one of them is a distinct count measure.
The process is running for 1 week, and has 15 of 77 agregations complete.
This time is consider as normal?, how i can improve the performance of this
process?

Thanks in advance,

Danton

If the database server is half decent you should be getting throughput
of about a million rows a minute. It sounds like the cube is completely
non-optimized (if that's a word).

Can you trace the SQL Analysis Services is sending to the database
server. This should demonstrate what joins you've got. Plus whats the
network connection like between the SQL and AS boxesin addition to
other server configuration (disk, processor, etc.)

Two points:
1) SSAS Performance Guide has a lot of material on optimizing processing:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

2) You will have a differences in performance with distinct count measures.
That doesn't explain a week's worth of processing :-) but it might explain
why you probably won't be able to do the 1M rows per minute which Steve
mentioned below (which I agree with BTW -- that is a good general
rule-of-thumb). Unfortunately have a DC measure is one of those areas where
the rule-of-thumb doesn't hold.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Steve McHugh" <steve (AT) oneleg (DOT) net> wrote