Re: SSAS: Processing Cube hangs

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/default.asp?url=/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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
Edwardvb.2c0nu0@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx says...

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)
.

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: access cmd to sql server "grant insert on.."
    ... You need to create a SPT query. ... This uses the syntax of the SERVER. ... Ditto for SQL Server. ...
    (microsoft.public.access.externaldata)
  • RE: Use of IF then Else syntax in Access
    ... I'll post a sample of a pass-thru query that I use in a separate message. ... It should give you an idea of the syntax differences. ... that the dbo_ before the table name gets changed to dbo. ... go to query on the menu and choose sql specific, ...
    (microsoft.public.access.queries)
  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... > from a set of segments bearing the same name. ...
    (microsoft.public.access.queries)
  • RE: Newbie Need help on MDX
    ... Bear in mind that this in not the exact equivalent of the SQL query, but just an approximation, because most SQL queries cannot be translated into MDX. ... > dimension lets said dimenson a and dimension b.Both dimension is not a ... > select total from sales where name='a' or product='b' ...
    (microsoft.public.sqlserver.olap)
Loading