SSAS: Processing Cube hangs

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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 intervals

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:

Code:
--------------------

SELECT field1, field2,... fieldn
FROM table1, table2,.... tablem
WHERE
(table1.id=table2.table1id)
AND
(table2.id=table3.table2id)
...

--------------------


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:

Code:
--------------------

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

--------------------


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


--
Edwardvb
------------------------------------------------------------------------
Edwardvb's Profile: http://www.dbtalk.net/m323
View this thread: http://www.dbtalk.net/t323737

.

Relevant Pages

  • Re: Strange processing error.
    ... I've a sneaking suspicion that this may be down to memory, ... Analysis Services previously (especially when trying to share a box w/ a SQL ... There are around 7-10 dimension connected / measure group. ... cube Analysis Services Execute DDL Task ...
    (microsoft.public.sqlserver.olap)
  • Re: Why "VIEW" a date dimension?
    ... If you have only one date table and one date dimension (after all all the ... The reason being that the SQL behind the cube mistakenly ... it appears that Oracle is expecting the syntax in some specific manner. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Strange processing error.
    ... The datasource for the AS database is on the same SQL. ... Analysis Services Execute DDL Task Description: Internal error: The operation terminated unsuccessfully. ... The cube has 6 big measure groups each with 2-4 partitions each with 40 - 70 million records. ... The dimension design I think is pretty good as the query and processing performance is quite good. ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube processes successfully, but some records dont display
    ... extracting the cube process to a SQL statement ... > one of your dimension tables. ... > to get the SQL statement that AS generates when processing your cube ... > and paste it onto a query window and then remove one dimension table at ...
    (microsoft.public.sqlserver.olap)
  • Re: Analysis Manager Operations extremely slow
    ... both RDBMS SMO and Analysis Services ... testing domain authentication. ... cube editor slowness if connecting to Oracle. ... Gender dimension to "M" and still browse the Sales cube. ...
    (microsoft.public.sqlserver.olap)