March 22, 2016 at 11:19 am
I've been requested to find a tool to allow end users to write their own ad hoc reports from an MS SQL database. This is for a new application that will user SQL Server 2014.
The reports would mostly be financial in nature and although the users would have financial experience, experience with Excel, I don't believe they have much database type experience.
It's not something I'm keen on, but the request is on the table.
What recommendations would you have?
Thanks in advance.
March 23, 2016 at 9:23 am
There are several options...
If they're already good at Excel, you might try either PowerPivot or PowerBI Desktop. I would lean toward PowerBI Desktop. If you have something like a mini data warehouse set up, then reporting should be easy, and they can explore their data all they want. PowerBI and PowerPivot both require some skill with DAX, but if you get Rob Collie's book, you'll be off to the races (like $25?). PowerBI is completely free, and can connect lots of disparate sources together, which is really handy (HTML tables, databases, spreadsheets, whatever).
You can download PowerBI desktop from the Microsoft site and play around with it... I would definitely recommend it. It's free - so what do you have to lose? What kinds of reports are you intending to write, or what kind of analysis are you trying to do?
The crucial part of PowerBI/PowerPivot is getting the data model right. The rest is pretty easy after that. That's where Rob's book[/url] comes in handy. Really easy to read, and the Mashup chapter (17) is superb. That's when things get fun. The book is maybe $25, and worth every penny. If you go to his website, you can download the PowerPivot files, and you can do the exercises in both Excel and PowerBI. the one thing that PowerBI seems to lack (maybe I just don't know where to look) is the conditional formatting that he uses everywhere in the book. (to make important numbers stick out). You don't need SQL Server to use the examples from the book, either. He uses an Access database in his examples. So you could probably do most of it at home.
That's my opinion anyway... and if you decide that PowerPivot is really great and works well for you but you're outgrowing it, you can upsize to Analysis Services tabular...and then you can get row level security built in.
If you're in the Reporting Services world and want to stay there, you can use Report Builder. I don't really use it, since I can just build reports in SSRS, but it's an option too (and free). What you use will most likely depend a lot on where your data is stored and what kinds of analysis you're trying to do.
March 23, 2016 at 1:52 pm
I'll second the recommendation of PowerBI. Ad hoc reporting and data analysis are really what it is designed for. You definitely don't want to just turn your users loose on your OLTP sytems with it though, well unless you don't mind someone bringing the app down.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 23, 2016 at 3:40 pm
You could always restore a read-only copy of your database and let your users connect to that... then at least the two databases would be separate... if you ran a backup/restore script each night or an incremental backup/restore, then you should be close to real time. Depends on how close to real-time data you need.
March 28, 2016 at 4:03 pm
pietlinden (3/23/2016)
You could always restore a read-only copy of your database and let your users connect to that... then at least the two databases would be separate... if you ran a backup/restore script each night or an incremental backup/restore, then you should be close to real time. Depends on how close to real-time data you need.
or an availability group with a readable secondary! I just built one last week for a similar purpose!
March 28, 2016 at 4:51 pm
Apologies if I'm repeating myself. Here's one way of exploring what your best options are...
Since your target users are already Excel folks, either PowerPivot or PowerBI sounds like the easy option. If someone develops a model in either PPVT or PBI, you can upsize that model to Analysis Services Tabular Model, and then apply row-level security etc. (Not an expert on that part, sorry.) But the cool thing is that you can basically build everything and test it for free inside PowerPivot and/or PowerBI. Then when you have it right, you can upsize to SSAS, because PowerPivot and SSAS use the same query language (DAX). If you're not sure, invest in a copy of the 2nd edition of Rob Collie's book Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016.
Check out his website: http://www.powerpivotpro.com
I was reading the SSAS introduction stuff on the MS site today, and what they build in the tutorial there is pretty much identical to what Collie walks you through in his book. Absolutely worth the $30 or whatever - without question. And you can download all the files from the book and play with them. In the book, Rob sticks to what he's best at - PowerPivot. He doesn't go terribly far into SSAS, but there are books for that - I think Stacia [Misner] Varga wrote one if you want gory details.
If you've never really gotten into PowerPivot, you owe it to yourself (in my opinion) to check out Chandoo.org and watch some of his videos. (Check youtube.) His stuff is great. And if you have some money to spend on your education, he offers a course on PowerPivot.
Okay, in a nutshell... You could start in PowerPivot in Excel or PowerBI Desktop for free. Spend about $30 on a book and read and do all the exercises (I've done most of them several times) until you get your head around DAX etc. If you get to the end of Collie's book and you want more on star schemas, get a copy of Chris Adamson's "Star Schema: The Complete Reference". Then maybe get into Analysis Services. (You can upgrade a PowerPivot model to an SSAS Tabular model with a wizard... how hard could it be?)
Hope that helps (and I didn't just repeat all the stuff I'd already said!)
March 30, 2016 at 5:39 am
Thanks for your suggestions.
After a demo of the proposed application UI to the user, it looks like a large part of their ad hoc reporting requirements could be met with a couple of views where they can arrange columns and apply filters.
I will give Power BI a look and likely suggest that for more complicated reports.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply