Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    12

    Unanswered: SSAS: Processing Cube hangs

    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:
    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
    Last edited by Edwardvb; 08-04-06 at 12:29.

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Without knowing the limitations of the system and the cube I can offer a suggestion

    You could make your join syntax in a view and then connect your cube to that. That would allow you to use the join syntax rather than the where syntax.

    However I think that the root problem may be the number of dimensions in you cube. I believe that I read that optimal cube moideling should limit dimensions to about 16. I cannot remember the source of this.

    I think if I read you specs correctly you have a total of 28 dimensions

    Dimension User: about 200 records 1
    Dimension Line: about 200 records 1
    Dimension Direction: 4 records 1
    Dimension Date: 365 records for each year 1
    Dimension TimeInterval: with 24 intervals 24


    Just some thoughts not sure if This helps

  3. #3
    Join Date
    May 2006
    Posts
    12
    my mistake about the intervals.... the timeinterval dimension contains 24 records, which consist of 24 intervals...
    so there are only 5 dimensions...
    (I changed it in the openingpost also now)

    about your suggestion of a view... i tried that, but couldn't work it out properly... i will try it again however... have to do something huh?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •