Friday, July 15, 2005

MDX Questions and Answers

Q. When someone creates a subcube in an MDX statement, where does AS store the subcube internally.  Is there something like the relational tempdb for AS?

A. The subcube subquery is a logical construct, so the data itself is not really "stored" anywhere. Of course, when you execute the query against the subcube, the values are stored in server memory, the same as any query. The definition of the subcube subquery in effect filters the data retrieved by the query. It is not at all like making an extract into a tempdb. One of the big differences between SQL and MDX is that SQL needs a rowset to group. MDX reports create a structure and then retrieve the value for each cell independently (more or less). [Reed Jacobson]

Q. When querying a subcube, is AS able to access and benefit from the underlying cube's aggregations?

A. Absolutely, because it is querying the underlying cube. [Reed Jacobson]

Is there a way to analyze the MDX query execution plan and optimize it like there is for relational queries.  I think the answer is no, it is probably to hard to represent since so much depends on how aggregations happened to be created vs. pre-set indexes on the relational side

It's a totally different process than SQL. You can get clues for performance problems by using the system monitor or profiler, but the whole approach is backwards from SQL. [Reed Jacobson]

Q. One student has encountered difficulty in MDX writing a year-to-date comparison to the same previous year-to-date time period.   For example, they know how to do a current year-to-date query that would take them from the beginning of the year through July 6th.  However, is there an easy way to query for previous year information through previous year July 6th

A. It is a little tricky to write the formula. I typically don't like to use a flag for "the current day" because that doesn't let a user look at the number "as of last monday". But a combination of ParallelPeriod and PeriodsToDate is sufficient. Roughly, the formula is Sum(PeriodsToDate([Year],1,ParallelPeriod([Year],1,[Date].CurrentMember)). [I'm doing this from memory and don't swear by the syntax.] 

My position is that MDX Queries are relatively simple (compared to SQL queries), but that MDX Formulas can be quite difficult. If anything, Yukon is likely to make formulas both more powerful and more difficult :-). [Reed Jacobson]

No comments: