Re: MDX very slow

from what I understand, you query your cube to extract all the content of the cube, including leaf level and aggregated content into a CSV file...

Sure its slow, there is a lot of text to extract + a lot a calculations to do.
SSAS is really not designed for this purpose.

I really recommend to do a better analysis of what are your requirements, creating a file with both aggregated and detailed content is hard to play with.
what your CSV file become? I think your users will load this file into another database, so send them only the detailed content and they aggregate as they need, and sending the detail is easy to do with a simple SQL query into your source database.
Discuss with the end users and propose options.



"Truc H." <TrucH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:2BC3EABE-0357-45B1-9D57-85F29A0E9ACD@xxxxxxxxxxxxxxxx
We are using straight MDX query to extract large amount of data to un CSV file.
If I have to use SQL queries against the fact table, how can I all the
possible aggregations ?

"Jeje" wrote:

well...
do you really need a so big resulset???
which tool do you use to display this to the end user?
does your users really need thousands of rows in their reports?

some small queries is better because you have to manipulate less data and to
transfer and render less data too. The memory usage is smaller on both the
server and the client side.

if your usage is to extract a large amount of data to a CSV file, or
something like this, then I recommend to create SQL queries against your
source database instead-of a cube and use a tool like SSIS to process and
export the resultset into the flat file

"Truc H." <TrucH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F0E242E9-16A4-4D6F-93A7-01A9614DEC85@xxxxxxxxxxxxxxxx
>I broke the query down by Landing year and now have 16 queries.
> Each of them only take 10 minutes to run (total of 160 minutes instead > of
> 8
> hours!)
> How would you explain that (sum of small queries is less than big > query) ?
> Still it's not very pratical for operational purposes since we will > end
> up
> with thousands
> of queries for large extraction.
> Would it help if I add more aggregations to the cube ?
>
> "Deepak Puri" wrote:
>
>> So that would still be 250K or so cells - maybe the number of cells
>> returned could be slowing things down. Could you, at least for >> testing,
>> break the query into smaller pieces?
>>
>>
>> - Deepak
>>
>> Deepak Puri
>> Microsoft MVP - SQL Server
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>

.

Relevant Pages

  • Re: Better approach for common business problem ? (long post)
    ... have turned off query logging on the server - with this many subqueries, ... especially if you are partitioning by month. ... When you reprocessed the cube to include the new partitions, ... However, as we already established when looking at your aggregations, it's ...
    (microsoft.public.sqlserver.olap)
  • Re: calculated measure
    ... Optimize Query Performance with a Derived Measure ... are stored in the cube file, as we have mentioned, they typically mean ... more efficient query processing. ... calculated prior to the creation of aggregations. ...
    (microsoft.public.sqlserver.olap)
  • Re: Performance Benchmarks?
    ... 20 seconds its on warm cache or cold cache? ... there is too many things to consider like the CPU, memory, aggregations, ... We have developed a new cube (the first use ... the performance issues have more to do with the way the query is written ...
    (microsoft.public.sqlserver.olap)
  • MDX Parameters
    ... the following query in reporting services to extract some ... information from a cube. ...
    (microsoft.public.sqlserver.olap)
  • MDX
    ... the following query in reporting services to extract some ... information from a cube. ...
    (microsoft.public.sqlserver.olap)
Quantcast