CrossJoin vs SubCube

In Yukon MDX, you can crossjoin two sets by simply enclosing them in parentheses. In a way, it's like using the CrossJoin function without using the word. In other words, you just list the sets as if they were parameters, but leave the function name off.

But that's not really what's happening. Parentheses form a subcube. A subcube is very similar to a crossjoin, but with slight differences. With the Crossjoin function, you can't have the same hierarchy appear more than once, but with a subcube (that is, with the parentheses alone), you can use a specific member of one or more hierarchies, and then use the Tree() function to fill in all the remaining attribute hierarchies.

Thus, the expression Crossjoin([Bikes],Tree([Product])) is invalid, because the Category attribute appears both in [Bikes] and in Tree([Product]), but the expression ([Bikes],Tree([Product])) is valid, because the subcube operator just "fills in" the unused hierarchies.

One interesting thing about the subcube notation is that it is identical to tuple notation, except that as a tuple, you use single-members instead of sets. The intriguing part about that is that a tuple really is the crossjoin of individual members, so the notational similarity really does match an underlying semantic similarity. Form follows function. The Bauhaus architects would be happy. (See, I learned something in Munich.)

- Reed

Chertsey is not London

[This post has nothing to do with Yukon, and should in no way be interpreted as my having being the least bit disappointed by my experience in, uh, London]

If you ever go to a training class at Microsoft in London, check to see whether it is really in Chertsey, which is where their new training facility is. (Apparently, the facility came along with the Great Plains acquisition.) And if you go to Chertsey, be aware that you are going to a charming little town out in Surrey, and don't expect to pop down to the west end for a show in the evening. And if you stay at the Hilton Cobham (which really is a nice place), expect to be out in the middle of the forest at a popular "hideaway" where the only way out is by car or taxi--an easy hour to Chertsey during rush hour.

Use Hierarchies are Fake

One of the strongest points I tried to make while teaching about the new world of Yukon Analysis Services is that User Hierarchies are completely devoid of meaning--except insofar as they map to attribute hierarchies. One of the students had an interesting scenario, where she wanted to calculate something only at the product level, never at any higher level of a user hierarchy. Adding IIF structures to test for every possible user hierarchy seemed daunting--and unsupportable. So we tried a simple test.

We put a set of User Hieararchy members on Rows in a report, and then created calculated members to show the name of the current member of the attribute hierarchies. So on rows, we had [User Hierarchy].Members, but on columns displayed [Attribute Hierarchy].CurrentMember.UniqueName, etc. In all cases, the current member of the attribute hierarchy was the member mapped to from the user hierarchy.

This was perfect. In the MDX, we could test for [attribute hierarchy].currentmember.level.ordinal <> 0, and it wouldn't matter where we were in whatever user hierarchy.

- Reed

DTS Programming

Let's be honest. It's, er, challenging;to code against the DTS object model. But a lot of the reason is because DTS--oops, Integration Services--is not really a set of objects, but rather a development platform with two primary engines, the runtime engine (for control flow) and the pipeline engine (for data flow). All the tasks and transforms are "plug-ins" that somebody wrote to fit into one of the two main engines. Once you get that distinction clear, and pay close attention to when you are writing to the DTS engine, and when you are writing to the plug-in componen; much of the confusion goes away.

I started using a pseudo-hungarian naming convention where the last letter of the prefix was 'o' for the 'outer' or engine object, and 'i' for the 'inner' or plug-in object. Once I did that, I realized that there was a remarkable pattern going. All the communication with the 'outer' object is essentially the same, regardless of what you're creating, and the 'inner' object code is where the uniqueness of the plug-in reveals itself.

Perhaps that's why they decided to give DTS a fancy new name.

- Reed

SQL and MDX

I just finished delivering the first two rounds of the BI Development course--in Munich and London.

One of the most popular sections was a comparison between SQL and MDX queries. In it, I try to clarify a) how the query structures are similar, b) how they way they are interpreted is different, and c) how MDX is very powerful and extensible for reporting--given the modular nature of set functions.

This is not particularly new for Yukon, but in fact the UDM is a major feature of Yukon, and access to the UDM requires MDX queries, so it becomes increasingly relevant in Yukon.

- Reed

When is Data Mining not Data Mining

After I taught my nice, dry, boring exposition on Data Mining in Munich, Raman Kohli (the Ascend liason who accompanied me from Redmond to make sure I didn't screw up too badly), came in and suggested the idea that data mining should never be referred to as such. That you should talk only about the solutions--Market Basket Analysis, Trend Analysis, etc--and not even use the words 'data mining'. Now, I managed to ignore most of what Raman said to me, but this one actually clicked home. Each of the Data Mining models is actually built to solve a particular type of problem, or analysis, and 'data mining' just happens to be the engine or technique that populates the model with coefficients from actual data.

So, in London, I changed the title slide of the Data Mining section to 'Market Basket Analysis and other Cool Tools'. Raman thought I was making fun of him, which I wasn't (OK, maybe I was, but just a little).

-- Reed

Limiting Delimiters

I had already noticed that Yukon MDX did not require braces around a single member set. So the fragment SELECT [Sales Amount] ON COLUMNS now works fine. What I didn't know until a student pointed it out today is that the MDX in a WITH MEMBER clause no longer needs apostrophes around the expression. So you can say With Member [Measures].[Test] as Sum(Product) instead of ... as 'Sum(Product)'. One appreciates those little things that make ones life simpler.

-- Reed