Re: Timeout problems due to huge Fact Table

I've also experienced very poor performing queries that the cube itself has
generated based on a table - however, typically when having a large number
of dimensions & with 'materialize' set on the relationships in the dimension
usage tab.

I've one measure group that takes around an hour to process, and it has only
10 million rows. :(

"Jeje" <willgart@xxxxxxxxxxx> wrote in message
news:623301D2-D55C-430F-A2D4-D109F4931F80@xxxxxxxxxxxxxxxx
from what I understand,
to fill your cube you are using a very complex query instead of a table.
(right?)

have you try to do it in your ETL process instead-of a view?
or part of the query can be evaluated in the ETL this can reduce the query
complexity used for the cube.
indexed views is not a solution you'll be a winner by playing at the ETL
level and adding required table in the database.

how many rows do you have in the table?
and have you optimized the tempdb database?
and on how many disks your database is and your tempdb is?


"Harry Leboeuf" <HarryLeboeuf@xxxxxxxxxxxxxxxxx> wrote in message
news:eyDZjKYYIHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
I would like to have some thoughts on how other people deal with this
problem.
It's mostly in our testing environment that this problem occurs.
The production server is q very performant machine and can still deal
with the huge fact table, but one day we will get performance problems
there too.
Ok, at that moment servers might be again double so fast ....

But how do you people deal with a fact table that (even when just doing
the select in Sql) that needs over 6 hours to complete.
Workign with a staging table would nog completely solver the problem as
building the staging table would take the same time.
The performance problem is not in SSAS, is just the huge query.
I could create an indexed view of the query but still .....
And working with daily uploads would create a quite difficult upload
system as we have (even historical) quite volatille (chaging) data.

If anybody has some thoughts, suggestions ... feel free.

Kind Regards, Harry



.

Relevant Pages

  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: Fairly Large Cube-Performance Improvement recommendations
    ... move) if the cube has large number of dimensions. ... We have 30 dims in the cube and the storage design wizard taked about 45 ... > 1) execute a fairly long query, let's say one that runs for 20 seconds. ...
    (microsoft.public.sqlserver.olap)
  • Re: Cubes
    ... So your virtual cube will have 2 customers dimensions. ... Can you create multiple cubes from multiple sources and link ... >>> fact table must come from the same database. ...
    (microsoft.public.sqlserver.olap)
  • MDX : Taking a union of slices
    ... In a MDX query, I would like to slice on 2 dimensions in the following ... The sales cube in the sample OLAP database I need to retrieve the Unit ...
    (microsoft.public.sqlserver.olap)
  • Analysis Services Query Log Inconsistency
    ... Analysis Services 2000 allows up to 128 dimensions per cube; ... level in each dimension a particular query uses. ...
    (microsoft.public.sqlserver.olap)
Loading