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]

Drivers

Someone in the class was told that drivers for other db's than SQL Server (i.e. Oracle, DB2) would not be supported at RTM.  Someone in the class form MSFT said this is not true.  Do you know the answer off-hand?

[Stacia Misner] It depends on the context, I think. For example, in Reporting Services, there are certain data sources supported out of the box – SQL Server, Oracle, ODBC, and Analysis Services. Report Builder though will only support SQL Server and Analysis Services. That leaves Integration Services – I remember thinking each build since Beta 2 had a different collection of adapters so I didn’t bother memorizing what – wanted to see it stabilize first. Traditionally, DTS supported Oracle natively – don’t recall if it tries to in SSIS. At the very least – you can use OLE DB or ODBC or write your own.

 

[Reed Jacobson] Oracle can be used as a data source from virtually anywhere. (I don't know anything about Report Builder, so if it will only access SQL Server, that's news to me--it would still be easy to create an AS UDM that models the ORACLE source--even as pure ROLAP--and then Report Builder should be able to get to it. The only place I know you can't use ORACLE is as the metadata databases for Reporting Services--those have to be stored in a SQL Server database.

AS Settings

Are there any AS settings that affect whether cubes process query requests  or cube processing using multiple processors.  If not, how is AS in using multiple processors? 

 [Stacia Misner] The first place I’d look would be the server properties in SSMS. Right-click on the server name in the Object Explorer and take a look at the properties there.

[Reed Jacobson] For processing, it's an option in the Process dialog box (that you get when you use the explicit Process command, either from BIDS or from SSMS). You can choose whether to go parallel or sequential. I believe that if you choose parallel, it makes intelligent use of the number of available processors. Regarding queries, my guess would be the same as Stacias.