We’ve blogged earlier on the 60,000 -feet level overview of Power BI and what there is to like about it. This blog is a follow-on with a particular angle. I’m going to make a case that Power BI is a great set of tools for speeding up database modeling and rapid prototyping. That’s true whether or not you have an existing data repository (i.e., warehouse or marts), which means it can be true for just about anybody.
First, a refresher. We’re going to build this model in Excel, right? We’re going use Power Query to extract the data, Power Pivot to model it (add hierarchies, calculations — and a whole lot more, by the way) and Power View to build snappy visualizations. So the flow looks like this:
Power Query -> Pull data from private and public sources, clean it up, save it to an internal data model
Power Pivot -> Create relationships, custom measures, hierarchies, and KPI’s
Power View/ Power Map -> Build interactive views, charts and dynamic maps
Of course, you don’t necessarily need to use Power Query for your extraction. You can use the Power Pivot interface to pull from lots of data sources, just not all the data sources available in Power Query. And you also don’t need to use Power View or Power Map to create visualizations. If you’ve published your Power Pivot model to a SharePoint site enabled with PowerPivot server software, you can use Reporting Services to build your visualizations based on your Power Pivot model. You will, of course, need Power Pivot, since that’s the heart of the data model. But I think you’ll want to use Power Query and Power View/Map if rapid prototyping is what you’re all about.
(We recently learned from Microsoft, by the way, that it’s not just us that find it confusing that there are three very similar looking ribbons – the Power Pivot, Power Query and standard Excel ribbon – you can use to pull data. Apparently the current thinking is that Power Query will be the ribbon that will trump, but we’ll need to wait and see.)
So how does rapid prototyping work?
Let’s say you don’t have an existing data repository. You have some transactional systems that you’d like to pull data from and do some integration work on. Let’s say further that you are headed towards a data repository because you are a believer in best practices and you’re getting pressure from everyone for reports based on validated, single-source-of-truth data.
It’s pretty simple. Let’s assume your ERP or transactional system tables don’t have impossible-to-understand column names (and that’s a big assumption) or if they do, they come with a wrapper. That wrapper should give access to tables and views for use in reporting. You can point Power Query to those sources and pull a reasonably sized amount of data. (Remember that Excel data models are tabular, in-memory models that compress data on the order of magnitude of 10 to 1, and are wicked fast to boot). In Power Pivot you relate those tables to each other, create some hierarchies for drill and calculated fields and columns for analysis. You will need to know a little DAX if you want to get fancy with those calculations, but if you’re familiar with SQL and Excel functions, there’s not a steep learning curve.
But I’d actually suggest that you don’t go deep into the calculations. I suggest you pretty quickly put some visualizations on it and pass it around to end users. Ask them what’s missing and what works. It’s a lot easier for end users to know what they want with a straw man in the room.
Everything I’ve said above you can do if you have Excel 2013 or Excel 2010. You don’t need to deploy it to a Power BI SharePoint site if it’s prototyping you’re after. You can simply give these workbooks to users and let them play with them. If they like them, you can take it the next step, which is to deploy it to a SharePoint site and track what kind of usage you get. If it’s a PowerBI site you deploy it too, you can have users play with the queries behind them and rate those queries.
But that’s part of the growing-up track, which is the subject for a different blog. For now, we’ll leave it at this: think about whether rapid prototyping could be useful. If so, this is probably one of the least expensive, least time-consuming, and least hassle avenues to getting there. And it’s fun because the interfaces are friendly. It’s easy, but don’t be fooled by that. Power Query and Power Pivot have great depth behind them. And what it can’t do now, Microsoft is probably tracking to build. So stay tuned.
