In wandering around the blog-sphere and help pages, it’s come to my attention that I’m one of a rare few who staunchly believes that SQL queries are a better reporting tool than OLAP cubes. I’m not saying that there’s no place for the OLAP cubes – I think it depends a lot on your reporting requirements, security constraints, and your level of confidence with Excel. In my case, my reporting requirements are high, my security constraints are complicated, and my confidence in Excel is darned good. I wanted to make a list of pros and cons but I realized that my opinion is biased and I couldn’t think of many pros for OLAP cubes, other than the obvious. So instead, I’m not going to badmouth OLAP cubes, I’m just going to tell you what I have accomplished with SQL queries and Excel and let you draw your own conclusions. Here’s a few important things I love about this method:
WHERE statements that control data:
Most people happily filter their data from within Excel once they’ve pulled it into a table. Those people have clearly never accidentally tried to pull in an entire database and crashed Excel…about 500 times. I swore that mistake off some months ago and found some really clever ways to make the query do the filtering work. In fact, you’ll find that the ‘date range’ ability of using your WHERE statement in SQL is much more ‘self updating’ than what Excel can accomplish. For example, when you’re trying to set a date range in Excel, you have some dynamic options like “Last Week”, “Last Month” and “Next Month” etc. But what you don’t have is “Before Last Week” or “After Next Week”. Have no fear, SQL is here! You can use a dynamic date filter like
>= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ), which does a delightful job of making sure that your dates start at the most recent Monday and go forward. Super helpful for looking at allocations!
Pivot table knows from weeks:
When you pull data in from OLAP, you get the dimensions of Year, Quarter, Month, and Week. That sounds all well and good, except that “week” is divided first by “month”. This results in a week which falls across more than one month being broken into two pieces – then your weekly totals look completely whacked out and make people cry. Well, at least it makes me cry. However, if you pull timebyday into a pivot table (just put the time by day column into the select statement, such as MSP_EpmAssignmentByDay_UserView.TimeByDay ) and then when you add it to your pivot table, you can group it by 7 days. As long as your start date (you’re given an option to chose a start date when you click on group) is a Sunday (or Monday) then you get weeks with dates ( like 1/1/2012 – 1/8/2012 ) instead of some kind of annoying Week34 (what the heck week IS that? Who has that memorized?) as your weekly breakdown, and it doesn’t break weeks in half, either.
This is kind of an obvious ones for those who have been around for a while, but it doesn’t exactly go without saying – the ability to hit ‘refresh’ when project data has been changed and published, instead of having to wait for the cube to build, or to manually build the cube, is just nice.
SQL Queries – Not just for Project!
You’ve probably got someone floating around your company who can write a SQL query. Heck, I have people floating around my back alley that could write a SQL query. (lies, I don’t even have a back alley) The syntax is widely known and easy to learn. Others probably like MDX just fine, but it makes my pretty little head spin!
And last – for my own purposes, I’ve spent so much time learning how to do this that I have some really fancy and cool tricks I’d like to share…so that’s next – Special Little Project Server Query Tricks to Make You Look Like a Goddess! (or God)