MDX Query never finishes

Question

MDX Query never finishes

Cesar-134342

Ten Centuries

Points: 1319
More actions
March 31, 2005 at 1:50 pm

#110360

I have a fairly complex MDX query (6 nonemptycrossjoins) vs. a medium-size cube. Up to last week the query ran fine, returning several thousand records in 30 secs. or less.
But this week, after the weeky data upload and cube rebuild, the same query uses 100% of the CPU and had to kill it after two hours with no records returned.
Has anyone seen this before?.
Thanks
April 4, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tobi White Mr or Mrs. 500 Points: 551 More actions · Answer 1

I have experienced weird behavior from sending queries with the nonemptycrossjoin and the non empty functions. I prefer the non empty function over the nonemptycrossjoin, but either way i have found that if I do not have aggregations created for these that it will hit every partition for some reason even though my partitions have a time slice that should prevent this from happening, and would if the non empty function was not used. There is info online comparing the non empty to the crossjoin, but with non empty you can just specify it once usually, for an example,

" SELECT { [Measures].[MeasureName] } ON COLUMNS , NON EMPTY { [Dimension(s)Name] } ON ROWS FROM [cubAcadMerchandise]"

Either way you can set your audit to log every query and then go into your query log and see how many partitions it hit, what aggregations it used, or tried to use, and what the duration was. Unfortunately the querylog is in a jet database by default, but I moved mine to sql server on the same database as the Analysis repository. Hope this helps.

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 2

I migrated the AS repository to SQL too, but the log is still on the jet engine.

1. How do you moved the log table to SQL?

2. How do you interprete the log?.

Thanks

Tobi White Mr or Mrs. 500 Points: 551 More actions · Answer 3

Moving the querylog table to the sql server is a two part process. First you must migrate the actual jet file over to the sql server you want it at. I just used DTS and moved it to the same database as the repository. If you don’t know where the jet file is you can go to the registry key and it will tell you. The registry key in question is HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\OLAP Server\CurrentVersion\QueryLogConnectionString. After migrating the data to SQL Server you must change this registry key to point to your SQL table. Your connection string (the new key value) should look something like Provider=sqloledb;Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;

You must then restart the Analysis services service. As for how to interpret the log – that depends on what type of storage and other factors you are using. The columns I use most are Dataset, Duration, and MOLAPPartitions. The Dataset tells you exactly what aggregation every logged query tried to hit. If you didn’t have that aggregation the chances are that the duration is going to be large for that query. I try to add aggregations for the datasets that were targeted that had durations of over 3 or 4. However, this method requires me to set the exact aggregations that I want. If you use the usage based optimizer it does more or less the same thing using this information.

For registry keys and their usage refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_anservregsettings.asp

For a general discussion of AS refer to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx

I am assuming a certain level of knowledge in my post. You likely have more or less than I have assumed. Feel free to ask questions if there is something you didn’t understand, and please forgive me if there is something you already understood.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

To help you get started, if you do a search back through the articles/scripts of this site you'll find where someone provided a method (and dts packages etc) for getting the log data into a cube, which makes it easier to do your review.

Steve.

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 5

Thank you all. I was able to transfer the log, but now the new week came up, and the cube had to be fully-reprocess (as every week).

But this time, the MDX statement worked just fime (??). so the problem seem gone.

Oddlly, we did a full re-process last week twice, and did not worked. But this time it did.

Great help!, thanks once again.