SQL 2000 Cube Processing Takes Forever

Last Post 21 Jun 2007 03:59 PM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

21 Jun 2007 06:41 AM
Fact table has 20 million rows. Dim tables (5) are reasonable like states
(or state info N/A), magazin subscription (100 mags). IncomeLevelGroups (5), etc. Nothing fancy.

The measure is Member (set to 1).

So the default aggregation sum(Member) works real fast.

When I add UserID (not unique) to the measures and set the aggregation to
select distinct(userid) at the properties panel, it processes forever.

The unique userids are around 5M. Meaning that a user subs to 4 mags on the average.

How can i speed up the Unique measure processing? Otherwise I have to take it out.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
The Best SQL Server 2005 Training in the World - SQLUSA:
New Member
New Member

21 Jun 2007 03:59 PM
Standby SQL is running with AS, not production.

All other cube processing is reasonable around 3-10 min.

I could see >distinct userid< is much more difficult for AS than running a sum or count.

New Member
New Member

23 Jun 2007 04:30 AM

Drop the select count(distinct userid) measure - was experimental anyhow.

Instead, dynamic stored procedure with parameters to create a slice & dice on the fact table.

So the user can get the non-unique counts from the cube. When actually unique necessary, the stored procedure is fired up.

Kalman Toth
SQL Server 2005 Training - http://www.sqlusa.com

Acceptable Use Policy