Welcome to my SQL Server Analysis Services site. This page contains links to anything I'm interested in related to Analysis Services.The main focus is on internet connectivity and analytics. It's frequently updated, so check back later for more content.

AS2008 Perf Guide published!

Get it here.

Handling Begin and End Times

Based on a conversation with a US broadcasting company and Thomas Kejser (a colleage on the SQL CAT team), we came up with a pretty novel approach to handle events with a begin and end time. For example, when people watch TV there is a time they turn it on and off. What broadcasters want to know is how many viewers are watching at any given time – often down to the minute level. There are many other examples: issue tracking (opened date, closed date, assigned to, etc), flight arrival and departure. More info here.

AS2008 Perf Guide Draft Available!

Still has to go through another round but you can take a look at a draft here (update: link removed after official version available).

Gemini - my first reaction

Saw the demo at the BI Conference. Pretty cool.

Calculated Members and Aggregations in SQL Server Analysis Services

I’ve been stumped once or twice by the interaction of calculated members at non-leaf levels. For example, consider what happens if in the MDX Script, the user defines this:

Create member measures.[Profit Revenue Ratio] as Profit/Revenue;

<pass incremented by intervening calculations>

Create member Time.[Both Years] as aggregate(Time.2007, Time.2008];

What do you expect at the intersection of the two calculations? 

The last assignment wins because it has the highest calculation pass. But Analysis Services doesn’t know how to aggregate calculated measures ( and this is logical because calculated measures have no aggregation function associated with them). The situation looks a bit like this – what is the value one should expect for the yellow cell (given that the Aggregate calculation has precedence)? Is it:

a)      The aggregation of the 2 years; eg, 0.5 + 0.75 = 1.25

b)      The ration of the profit to revenue for both years: 4/6

c)       An error (because calculated measures should be aggregated)

In SQL Server 2000 Analysis Services,  we gave an error. Arguably we should still do that. But we made a decision that most people expect the calculated member’s _original_ expression to take precedence. So we go back to the create member statement and take that value. In this case it is (b) 4/6. In 95% of the cases this is what people expect.

But things can go awry. Consider this instead:

Create member measures.[Profit Revenue Ratio] as null;

measures.[Profit Revenue Ratio]  = Profit/Revenue;

<pass incremented by intervening calculations>

Create member Time.[Both Years] as aggregate(Time.2007, Time.2008];

And now answer the same question = what is the value at the intersection of the two cells?

The AS engine does the same thing – it flips precedence back to the _original_ expression which is not  profit/revenue but null.

To resolve this issue, it’s best to position the calculated members in the script so the calculated measure takes precedence.