Which problem do you prefer, when thirsty –
Too Much Bottleneck (left) or Too Little Structure (right)?
Answer: Neither is preferred of course!
I’m part of an email distribution list that involves a bunch of BI pros, as well as a number of the PowerPivot engineers back in Redmond. Last week on that list, I asked a question that I knew would draw some fire:
“How many measures can I safely put on one table in PowerPivot?”
There were 2 flavors of response: technical on one hand, and “um, is that really a good idea?” on the other.
Technical answer: no practical limit to # of measures
The official technical answer was good news: the number of measures defined on a table in PowerPivot should not have any real impact on performance, unless you are using all of them in a single query of course. Measures that aren’t part of the current report won’t drag you down, in other words.
(I am told this is in contrast to the traditional Analysis Services product, which scanned all measures at once. That prior approach is a good thing when you are using a large percentage of your measures in a single query, but a bad thing when you are using only a small percentage.)
So I immediately dug in and built a model that has nearly 1,300 measures on a single table 🙂
Yes, they are real measures.
Yes, the addin seems stable with that many measures in the field list, and performance does not seem to have suffered, at least not in my initial testing.
And yes, it was tedious. More on this in a later post.
Modeling Hygiene Answer: Why the hell would you do that Rob?
The natural reaction from the BI/DB pros was to suspect I was doing something wrong.
Now, I know myself pretty well, and the prospect of me Doing Something Wrong can best be described as Entirely Plausible.
So I asked a few followup questions. And it turns out, no, there isn’t any pure philosophical flaw in what I am doing, but they were still skeptical.
Diet, Caffeine Free, Cherry, Coke Classic, Zero… Please
Here’s a joke that I play with my colleagues when they are leaving the room and it NEVER gets old:
“Hey, would you mind getting me a can of Diet Caffeine Free Cherry Coke Classic Zero?”
This joke is a roundabout explanation for how we end up with 1,300 measures.
Thing is, we only have 5-6 core numerical data columns in this model. But all the permutations are what get us. For example, we have Sales of course. But we also have Sales in Prior Period, and Sales from Same Period Year Ago. Don’t forget Sales Growth. Ooh, and Sales Growth Percentage. And, you guessed it, Sales Growth Percentage Year Ago. You get the idea 🙂
Traditional Analysis Services has a feature named Calculated Members that helps address this problem. But until that feature comes to PowerPivot, well, Coke is just gonna have to make all 2^11 flavors for me.
Bottleneck vs. Discipline
One member of the list in particular took an interest in what I was doing. His name is Thomas, and he knows 100 times more about traditional Analysis Services than I do.
His chief concern was that I was using the wrong table structure in my model, like perhaps one big single table. As it happens, I am not. I am using something known as a Star Schema, which is something I have shown a lot of on the blog but never really described with any clarity.
I think Thomas has an excellent point. It is easy to start out with the wrong table structures, especially if you are coming from an Excel background. So I think it’s time someone who understands the Excel mindset AND the need for proper table structure, um, explain that.
Dick has volunteered to do a post (or series of posts) on that topic, so I will leave that to him.
My point back to Thomas was essentially this: the need for smart measures today is almost certainly 10x what is provided, or more. When my consulting budget ran out on the original Football Project, and my Analysis Services pro was no longer sitting in my office all day… well, I was not getting any more new measures.
Doesn’t mean I didn’t want new measures, or need them. I just wasn’t going to get them. The person with the questions (me) lacked the tools to build them himself. That is changing now.
I think that hundreds of measures in a model, or at least the potential to have hundreds, is a VERY good thing. It is a sign that the bottleneck is going away.
Now, there are consequences of course. A 1,300 measure field list isn’t a lot of fun to navigate (although the Search box in the PowerPivot addin is a savior!), and there will of course be the danger of “I just recreated measure X without realizing I already had a measure that did the same thing.”
But those are TOOLS issues. Microsoft can build better field lists for us, with better ways to organize and view things, etc. They will have to.
Don’t limit me arbitrarily. Sooner or later, all 1,300 measures in this new model WILL be used. Not all in the same report of course. But they will.
We could have built these measures on an as-needed basis rather than all at once, but guess what? They would be less consistent, more prone to error, etc. – building them all at once was actually MORE disciplined 🙂
So, BI pros, my advice to you is to embrace the fact that the number of measures is about to grow, a LOT, in the average model. And that is a Good Thing.
But as Thomas points out, correctly, in his latest blog post, the Excel folks can learn a TON from the BI community in terms of discipline and technique. So let’s keep the conversation going 🙂