Using Scripts to Simulate High-level Write-Back

Data write-back in Analysis Services has always been a bit frustrating because it's ultimately limited to leaf-level storage. (Not that leaf-level data write-back is not extremely useful--think of the millions of budgets that are input by using Excel spreadsheets--leaf-level data input without the benefits of an OLAP engine.) At any rate, Yukon write-back is still going to be ultimately leaf-level. One option for efficient high-level write-back is to "allocate" the values for each level to a single "dummy" member. But there's another option that we used occasionally in AS2K that would be even easier to implement in Yukon.

In AS2K, you can create Calculated Cell expressions that change the value of a single cell. So you can simulate data write-back by writing a calculated cell that assigns the target value to the specific single-cell subcube.

In Yukon, you get the same effect as a Calculated Cell by simply executing a script assignment. So, in a hypothetical cube with two non-measure attributes--account, and period, if the user enters the value 1000 for March Net Profit, you could "store" that value by using the simple assignment statement

([March], [Net Profit], [Amount]) = 1000;

Over the course of a session, your code collects these assignment statements, and at the "commit" portion, exports them to a process that parses, allocates and loads into a processable fact table.

-- Reed

Writing back descriptive data

OLAP is fundamentally numeric. Through "official" data write-back, you can't write comment values to a cell. That doesn't mean your app can't do it on the side

Inside an app, you could capture the tuple of the cell that gets the comment, convert the tuple into a unique relational key, and store the comment with the tuple. You then do the reverse on query.

Emir pointed out that you could accomplish the same effect completely within the cube: create a "comments" dimension that begins essentially empty. Then use dimension write-back to create the unique key for a tuple and add it as a new dimension member, with the comment text as the "name" of the member.

You could then create an action on the cell that looks up the unique value--perhaps using a User Defined Function (UDF).

- Reed

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