MS BI Conference 5: Practical Design Techniques for SQL Server Analysis Services

This was a level 300 session.

It reminds me how much I don't know about SSAS. There's tons of stuff in the AdventureWorks analysis database that are good, meaty examples of design practices. Things like dealing with multiple currencies, charts of account, semi-additive measures, extrapolating measures at a smaller grain than the fact.

 He showed dealing with multiple currencies - the measure in the fact table was always USD, but there was an original Currency ID as a dimension of the fact, so with an exhcange rates measure and a many to many relationship, you could have a conversion done using a measure expression. I've done this before, but he showed a few things about setting the IsAggregatable false, and the default member, and setting the Locale to show the proper currency symbol (which I'd tried but couldn't get to work right) that were useful.

Somehow he was debugging the calculated measures and expressions when he was using the cube browser! I've never thot of trying to set a break point on those expressions. Sheesh. It's like working int he dark for a long time, then someone comes in and turns onthe light you didn't know you had.

He showed extrapolation - he had a Sales Quota cube that was ont he Quarter grain of the time dimension - sales quota amounts were set quarterly. Then he used some expressions to extrapolate, in a weighted fashion, based on the previous year's sales, the monthly values. So if the quarterly sales quota was $10,000, and July had 40%, Aug had 35%, Sept 35%, the monthly quotas were $4000, $3500 and $3500.

Attribute relationships are extremely important in dimensional design. They can make or break your performance, and I need to understand them much much better.

He showed stuff around chart of account stuff, in a dimension, to make sure they aggregate properly - depending ont he account type. Revenue/Expense accounts are additive  while some other types are subtractive or not additive at all. Again, I realized there's a lot I don't know here.

I wasn't lost in this session, but I was recognizing that I have encountered some of those issues, and in some ways managed to overcome them and sometimes I chose non-optimal ways I think. And there are fine distinctions that I don't understand, but I know make big differences.

I like getting humbled by sessions like this. There's always a bigger fish.