I spent most of the day today in Pete Meyers’ full-day precon session, “Zero to BI Semantic Model with SQL Server 2012″. Pete gave a great presentation, and like yesterday, I came away with some key insights, and points of clarification. (He also referred to MDX as “Mind Destroying Expression”, which I got a laugh out of.) The session focused on comparing the Tabular and Multidimensional models. The choice between the two models is multifaceted, and though Multidimensional has been along for much longer, it would seem that Tabular has all of the momentum. I’ve already made my bed for the week with the Multidimensional model, but weighing the pros and cons for a long-term solution is going to take some careful thought and planning.

Power View

One obvious advantage of the Tabular model is Power View, a very slick BI delivery tool. The demos of Power View that I’ve seen are impressive – data coming to life in reports with animated measures growing and shrinking as they move across dimension axes – very interactive stuff. Take it for a spin if you haven’t seen it yet. If you watched John White on CNN tonight, working that electoral map like a sushi chef, you’ve got an idea of what Power View can do.

Before I learned that it was only available in Tabular mode, I downloaded a trial license of Excel 2013, and tried to connect Power View to my Multidimensional model, but was rejected unceremoniously with the following error message:

No Power View for Multidimensional

So, in the spirit of democracy and today’s US Presidential elections, I joined 34 other unhappy Multidimensional BI developers on the Microsoft Connect thread and registered my vote for enabling Power View to access Multidimensional models. And while I doubt that 35 votes will get any late-night product road-map meetings convened over in Redmond, the voting was cathartic. (NB: this is the kind of thing my colleague John would call farting in the wind.)

SSRS Matrix Report Comparison (OLTP vs OLAP)

On the topic of BI delivery, Pete Myers had an interesting statistic regarding response times. He referenced a study that said if end users had to wait longer than 5 – 8 seconds between submitting a request for data and receiving the response, they not only lost faith in the BI system, but also forgot what it was they were querying.

If Business Analysts are dozing off at 5-8 seconds, have I got a report for you.

Back at the office, we have an SSRS report that gets run a few times a month, on an ad-hoc basis. It’s a gnarly thing with lots of aggregates, and even a few sub-reports (to accomplish distinct counts where the report matrix couldn’t), and it’s sitting on an OLTP relational database.

I remoted in to my workstation, ran the report, and the ran a quick query on the ExecutionLog3 view on the ReportServer. It returned a total execution time of about 75 seconds, which was right in the wheelhouse for that report.

75 seconds on OLTP

I wondered what the performance would be on the same report sitting on top of an OLAP cube. So, I put a similar report together quickly in Report Builder and uploaded it to the instance of Reporting Services running on my laptop. The results weren’t surprising, but still very impressive: 0.185 seconds.

0.185 seconds on OLAP

Every BI developer out there is saying “Duh” right now, but I wanted to see this for myself: OLAP delivering on its promise of high-speed querying against previously aggregated results.

Half Time

Today represents roughly the mid-way point of my OLAP sprint. I’m pleased with my progress thus far – I’ve got a prototype cube built with some basic proof-of-concept reports, and my knowledge is growing in big leaps daily. Wednesday through Friday, I’m hoping to interact with some of the great people here in Seattle at the PASS Summit, and take advantage of all the expertise.

More specifically, I’d like to get some questions answered around hardware platforms, licensing, and security, so hopefully those answers are out there.