MDX Query never finishes

I am populating flatfiles from MDX queries run i SSIS against AS databases.

I have ran in to problems with MDX queries returning large resultsets that never finishes.

The query performed is purchase calculations over different time periods for each member in the customer dimension. There are 6-8 calculated members created and the customer dimension contains about 3,5 million members.

When query is run, after 1-2 hours the server stop allocating memory and starts to get data from cache in intervals. In the end of each interval, memory is dropped. In perfmon it seems to be no activity during long periods

I have given it as long as over 3 days without any result.

Server is 8 x 64bit cpu with 16 gig memory.

Some help in this matter would be much appreciated!

/David

Can you please provide the expressions for the calculated members and what they are intended to do. Perhaps this forum will be able to suggest optimizations. 3.5 million members in the dimension is not that big. While calculations could take some time - it should definitely be in seconds, not in hours.|||

If I am not reading this in the wrong way this is about transforming cube records dimension members to a text file with SSIS.

I am not sure about the API:s that SSIS uses for this task.

Perhaps it is better to write this query in SSRS2005 and use export to csv or xls-files?

Regards

Thomas Ivarsson

|||

Here is one of the MDX queries which never finishes:

WITH
MEMBER [hush?ll?vrigaK?pSen12M?nBeloppV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200612])}),[Po?ngbelopp]),NON_EMPTY_BEHAVIOR=[Measures].[Po?ngbelopp]
MEMBER [hush?ll?vrigaK?pSen12M?nBes?kV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200612])}),[AntalBesok]),NON_EMPTY_BEHAVIOR=[Measures].[AntalBesok]

MEMBER [hush?ll?vrigaK?pFgSen12M?nBeloppV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200512])}),[Po?ngbelopp]),NON_EMPTY_BEHAVIOR=[Measures].[Po?ngbelopp]
MEMBER [hush?ll?vrigaK?pFgSen12M?nBes?kV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200512])}),[AntalBesok]),NON_EMPTY_BEHAVIOR=[Measures].[AntalBesok]

MEMBER [hush?ll?vrigaK?pFfgSen12M?nBeloppV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200412])}),[Po?ngbelopp]),NON_EMPTY_BEHAVIOR=[Measures].[Po?ngbelopp]
MEMBER [hush?ll?vrigaK?pFfgSen12M?nBes?kV?rde] AS SUM(({LASTPERIODS(12,[Date].[Year - Month].&[200412])}),[AntalBesok]),NON_EMPTY_BEHAVIOR=[Measures].[AntalBesok]

SELECT {

[hush?ll?vrigaK?pSen12M?nBeloppV?rde],
[hush?ll?vrigaK?pSen12M?nBes?kV?rde] ,

[hush?ll?vrigaK?pFgSen12M?nBeloppV?rde],
[hush?ll?vrigaK?pFgSen12M?nBes?kV?rde],

[hush?ll?vrigaK?pFfgSen12M?nBeloppV?rde],
[hush?ll?vrigaK?pFfgSen12M?nBes?kV?rde]

}
ON 0,
[Kund].[Premiegrupp].children DIMENSION PROPERTIES MEMBER_KEY, MEMBER_CAPTION ON 1

FROM [xxxxxx]
WHERE {[Butik].[Kedja].&[7],
[Butik].[Kedja].&,
[Butik].[Kedja].&[2],
[Butik].[Kedja].&[10],
[Butik].[Kedja].&[13],
[Butik].[Kedja].&[16]
}

The following MDX finishes after 30-40 min:

SELECT { [Po?ngbelopp],[AntalBesok] } ON 0,

{nonemptyCrossjoin([Kund].[Premiegrupp].children)} ,

{[Stambutik].[Kedja - Butik].[Kedja].&[14].children})} DIMENSION PROPERTIES MEMBER_KEY, MEMBER_CAPTION,[Stambutik].[Kedja - Butik].[butiksnummer] ON 1 FROM [xxxxx]

The language is swedish and the meanings are [Kund]=[Customer], [Butik]=[Store], [Po?ngbelopp]=[Amount], [Antal bes?k]=[number of visits]

To use SSIS for this type task usually works fine, some small problems with datatypes though. I will look in to how SSRS works thanks for the tip.

|||

OK, I got it (Butik is a slang for Store in Russian too, I never knew it had Swedish origin!). First query has many problems with it, so I think we should start with the second query. The syntax for set on ROWS seems to be wrong, but I assume that you really want to do NonEmptyCrossJoin(Customers, Some stores). Given that there is usually strong affinity between customers and stores, the relationship is almost many to one, sometimes called "many to few", I expect much less than 3.5 million rows in the resultset. Can you tell how many rows are in the result for that query ?

When you look into Profiler, what do you see ? How long did it take to scan partition ? How long does serialization of the result takes ? I hope you are using OLEDB and not ADOMD.NET here too, because consuming big results in ADOMD.NET is not as optimal as in OLEDB...

|||

Thank you for your quick reply.

I the second query is actually not a problem, I just submitted it as a reference that works fine. But it returns about the same number of rows as the first one. This join includes a factless fact which describes the most frequently visited store, and therefore the result is bigger than an ordinary [Customer]-[Store] relation. I forgot to mention [Stambutik]=[favourite Store]

The problem i have is with the first query, and I am very interested in the problems you found there.

|||For me 30-40 minutes to execute a query is the problem. The second query doesn't have any calculations, of course once you start doing SUM(PeriodsToDate()) the running time increases greatly (although I wonder whether you use SP2). Can you please answer the questions from the previous post.|||

I use a DataReader source SSIS using an OLEDB connection. SP2 is not installed.

I have no data for the time to scan partitions, serialization takes 5 min of min total execution-time. the [favourite store] is related to sales via a many-to-many relation and . I also noticed I forgot to include the "WHERE {lastperiods(12,[Date].[Year - Month].[Month].&[200612])}"

|||So what exactly do you see in the profiler when you run the query. How many rows do you get back (both questions apply to the second query).|||

Now I have set up a new partition scheme for the cube so the second query executes considerably faster.

The second query executes in 15 min,

What I see in SQLprofiler is:

Calculate Non Empty: 5 min duration

and a few Query Subcube events.

994 916 rows returned.

The first query still never finishes.

It has a lot of Query Subcube events.

I hope this helps you.