Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Day 4 of The OLAP Sprint

By Simon E Doubt,

Day 4: 9:55pm: The Halfway Point

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.

Reprinted with permission from http://OLAPsprint.com/

The Other Days

Follow along on this journey and read the other days of Simon's pointed learning journey.

Total article views: 1089 | Views in the last 30 days: 4
 
Related Articles
BLOG

Power View for Multidimensional Models Released

As a followup to my blog Power View for Multidimensional Models – Preview Available, the final versi...

BLOG

Installing Power View for Multidimensional Models

  Power View was a new reporting option added in the SQL Server 2012 release as part of the SharePo...

ARTICLE

Power View for Cubes

This document evaluates the latest functionality provided by Microsoft SQL Server 2012 With Power Vi...

BLOG

Power View for Multidimensional Models – Preview Available

Microsoft introduced an interactive data exploration, visualization and presentation experience call...

BLOG

How to use a multidimensional cube with Power View

The requirements for Power View state that it can only use tabular models as data sources.  So if yo...

Tags
olap    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones