Blog Author: Leon Kong, Senior Consultant
Be sure to look at your existing infrastructure architecture too.
For many businesses PowerBI seems like the cost-effective, silver bullet solution for their business analytics requirements. But for too many, without the right guidance and expertise it can cause more problems than it solves. Because it’s not just a question of PowerBI analytics. It’s a question of architecture too.
PowerBI is not a single, simple choice. There are actually three different ways PowerBI can influence the type of capability you have, based on your current infrastructure architecture. When connecting to your data sources, you might find your architecture won’t support or ultimately won’t meet your requirements, and you could find yourself saddled with unplanned development cost, slower performance, fewer capabilities, paying for capabilities you don’t need, excessive ongoing costs – or all five.
All PowerBI users want basically the same thing; to work with their data for analysis, insight and visualisation. But not all users have to take the same route. Everything depends on the scale of their data, its sources, and their existing estate.
If the quantity of data you usually work with is relatively low, then “Import” (also known as “Get Data”) may be the most appropriate PowerBI connection for you. It has a 1GB dataset limit on disk (or a Pro PowerBI limit of 10GB) but – because it imports a copy of the data into the PowerBI desktop to work on locally – it’s extremely fast.
It’s also flexible. You can import data from any and all sources (Excel, SharePoint, SQL Server etc.), combine it however you need to, and use the full capabilities of the PowerBI Desktop to manipulate it in any way you want.
But if you need to be able to handle larger datasets, or your data is updated more frequently than a standard refresh will accommodate, then Import won’t meet your needs. But “Direct Query” might.
Instead of importing the data to your PowerBI Desktop, Direct Query leaves the data at the source and simply sends queries to it. This means there’s no limit on the amount of data you can work with, and because your data remains at the source it can be continually updated. Even so, it’s not perfect.
Performance can be slow, especially if your data source isn’t set up to handle Direct Query. That means you may need to completely change the structure of your data. You may also need to optimise your data – by pre-filtering or placing a limit on visuals – to increase performance speed. And whatever you do to try to boost performance, you’ll only ever be able to use a limited range of data sources, as not all support Direct Query.
“Live Connection” is your third option for connecting PowerBI to your data sources.
If your organisation already has a multi-dimensional or tabular model, then this is the quickest and easiest answer (it should be noted that the tabular cubes model does support import and live connection). Simply connect PowerBI and you’re ready to go. It’s ideal for really, really large models as there’s absolutely no data limit. But of course, there are downsides.
Firstly, there’s the limited number of data sources which support Live Connection – and they are all a type of SQL Server Analysis Services. Secondly, there’s the fact that all PowerBI data preparation features are turned off, under the assumption that this is all done on the server supporting the model. Which means you’re left with PowerBI as nothing more than an analytical visualisation tool.
So Live Connection is really only a sensible choice for IT and enterprise applications looking for assured qualified data.
As you can see, choosing your PowerBI connection isn’t straightforward. Your decision can have an impact not only on your business intelligence and analytics performance, but also on your licensing arrangements, your costs, and even on data governance. There’s also the consideration of how you set-up your PowerBI Gateway. And if you opt for a cloud solution, you will need to manage cloud tenancy too.
Even if you simply subscribe to PowerBI as part of Office 365, it can have unforeseen and costly consequences. We have seen many clients start out with a blank canvas and without fully understanding the implications, only to be laden with capability issues forcing them to upgrade some of their users from PowerBI licences to Pro Licences and increasing their costs. This has then led to collaboration issues, forcing them to subscribe to PowerBI Premium cloud services, with even greater cost implications.
With the right advice from the start, they could almost certainly have found a better planned, more cost-effective solution, delivered far faster.
So, if you think PowerBI is the solution you’re looking for but there are many more questions you need to ask, get in touch and talk to us.
