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

Conditional Strings and Numbers

One of those minor but terribly annoying facts of MDX in AS2K was that the IIF function was really two functions, one of which could return a number and another that could return a string. Thus, you could never return "OK" if True and 2.134 if False.

 

It is hard to describe how euphoric I got just from discovering that you can now mix and match strings and numbers in an IIF function. Maybe I just need more sleep.

 

-- Reed

Inverse Sets

In an earlier entry “Getting something out of nothing”, Scott explained his solution for using a separate measure group to copy only the desired values from a cube, leaving the rest null.

I had an idea that might be even simpler in some situations. Rather than create a measure group, create a new dimension--similar to a Scenario dimension--where all the values in the fact table have one value. Then you can simply scope on the second member, assigning the selected values from the first.

- Reed

Willkommen!

Greetings from Munich. I have been teaching the first full delivery of the BI Development course. A few rough spots, but overall I think it's gone fairly well.

 

I managed to spend my whole first day here touring the city desperately searching for a US-German power socket adapter. I managed to make full use of trains, subways, trams, busses, and foot, all over the city, only to find what I needed back at the main train station. But it was a fun way to explore the city. Even funner was going with the elegant receptionist at the hotel down to the workshop in the basement to hack-saw up the adapter to allow my cell phone charger to fit.

 

-- Reed

Getting something out of nothing

I was working on a cube that where I wanted values visible for some cells and all other cells to be null. I wanted to define a subcube that would have visible values and apply any needed MDX. Then I would define the complement of this subcube and set the cell values to null. The problem was that while the subcube where I wanted values was easy to define, the complement of that subcube was very difficult to define

I next tried to create a calculated member that would be null and then use a SCOPE statement and an assignment statement to make the calculated member have values where I wanted to see data. The problem with this is that you can only use assignment statements to assign values on the physical space of the cube. You can not assign a calculated member a new value.

My solution was to create a new measure group and measure. In the measure group SQL statement I had “WHERE 1=2”. This means that the measure group will always be empty. But because the measure is a physical part of the cube, I can use a SCOPE statement and an assignment statement to apply MDX to the cells that I want to have values, setting the measure in the empty measure group equal to the measure in the measure group that contains data. The cells that I want null are already null in this new measure group. The final step is the set Visible=False in my original measure.

Scott

Hurricanes

I just finished up the Reporting Services pre-conference, and the regular session at PASS before flying to Germany to do the first full-week delivery of the Ascend BI course.

 

Unfortunately, because Jeanne swept through Orlando, the airport was shut down from Saturday noon until Monday noon, so none of us were able to get to the pre-pre-conference session on Yukon. I flew to Pensacola on Sunday, thinking it would be easier to get to Orlando from there, only to find that my flight to Orlando was cancelled at the last minute because Jeanne had closed down Atlanta, preventing the crew from arriving.

 

So to avoid cancelling the Tuesday pre-conference session, I caught the 5:20 early flight out, arriving in Orlando with 20 minutes to spare before my pre-conference session began. The PASS organizers were a little nervous. The session was on Reporting Services--mostly 2000--and went quite well.

 

Hopefully, tomorrow I can make it over to see a bit of the closing day of Oktoberfest.

 

-- Reed