Hi,

I have a problem with processing my cube. My fact table (with telephone data) contains about 400,000 records... which is increasing rapidly (400,000 records is about 8 months of data)...
I have a few dimensions:
Dimension User: about 200 records
Dimension Line: about 200 records
Dimension Direction: 4 records
Dimension Date: 365 records for each year
Dimension TimeInterval: with 24 records

So far so good... when I process this dimension I have no problem....
However, when I add a dimension (CalledNumber, with exactly 101 records) the processing hangs as soon as it starts...

The SQL performed when processing the cube looks like this:


When I execute above SQL in the Query Analyser from SQL Server Enterprise Manager, it ALSO hangs...

I am not really suprised by that, because this SQL first create a huge table of 400,000 x 200 x 200 x 4 x 365 x 24 x 101 records and after that works through the WHERE statements to filter out the appropriate records.

for me it would be more logical to use the following code to process the cube, but that cannot be changed in Analysis Manager:

When I execute above SQL in the Query Analyser from SQL Servel Enterprise Manager, it does NOT hang, but performs the query in about 35 seconds....
But Analysis Manager does not allow me to change the SQL used for processing the cube...

What can I do to add more dimensions to my cube... (It will be more anyway after adding the CalledNumber dimension)??
any suggestions?

PS. Forgot to mention: I am using Sql Server 2000

Your LEFT JOIN query should not be that much faster than the query that
AS2k executes. The syntax for the SQL query that AS2k uses is an older
syntax that is effectively be equivalent to using inner joins.

eg.

SELECT field1, field2,... fieldn
FROM table1
INNER JOIN table2 ON (table1.id=table2.table1id)
....
INNER JOIN tablem ON (tablem.id = table1.tablemid)

The query optimizer in SQL Server knows how to work with both syntaxes
and should produce the same query plan. It would be interesting to see
what the query plans are for the different syntaxes. It could be
something to do with statistics possibly being out of date. But there
might be a better way of getting a much more significant speed boost.

Look into optimizing your cube schema:

http://msdn.microsoft.com/library/de...l=/library/en-
us/olapdmad/agoptimizing_1qr7.asp

If your tables and cubes are appropriately structured you can eliminate
the all the joins when you process the cube, turning the select
statement into something like:

SELECT field1, field2,... fieldn
FROM table1

Even if not all your dimensions meet the criteria, it is worth trying to
run the optimization process as you can have a hybrid solution with some
joins still being used and others optimized out.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <Edwardvb.2c0nu0 (AT) no-mx (DOT) forums.yourdomain.com.au>,
Edwardvb.2c0nu0 (AT) no-mx (DOT) for...rdomain.com.au says...

Thanks, that link is very very useful... I'm looking into it now and it looks very promising! thanks a million!