Smetric Insights NZ & Australia

Smetric Insights | Business Intelligence, Benchmarking, Forecasting

by [post_author_posts_link_outside_loop]

How to use Power BI

Why Power BI is Not a Standalone Solution

So, you found you’ve got a subscription to Microsoft Power BI Desktop with your Microsoft 365 licence and thought this was the opportunity to do your own Business Intelligence over your various systems?

But… It hasn’t been as easy as you thought? Here’s the reason why…

Power BI is not a standalone solution.

With Microsoft Power BI implemented properly, you can better reporting without the challenges of Excel. You can get:

  1. Automated data extraction from multiple systems
  2. Automated reporting, in one place
  3.  Less errors with one version of the truth
  4. Visualisation of data for actionable insights
  5.  Sharing of dash boards with ease
  6. Save time, reduce stress

But we see many people in business having a go at DIY Business Intelligence, with the result falling short of these aims. There are a number of reasons for this:

1.      Different Versions of Power BI have different capabilities

There are three options for Power BI

  • Desktop – free
  • Pro (online) – $10US per user, per month
  • Embedded –Depends on usage

Power BI Desktop is not suitable where you have multiple users, require different levels of access, automated data uploads and need one version of the truth.

2.      Data Storage Limits

Power BI Pro has a limit of 10GB of storage. Depending on your usage, external data storage may be required.

3.      Connecting Power BI to Data

Power BI has some ‘out of the box connectors’ e.g. to Xero, MYOB AccountRight, MYOB Advanced and QuickBooks. But these standard connectors only have access to a limited range of the data endpoints. For ‘light’ users of reporting, this may be fine. But for deeper analysis and insights for a business, and ETL tool would give a better result. Some software doesn’t have any connector at all, e.g. WorkflowMax, Cin7, Hubspot, Unleashed, Simpro.

4.      Data Refreshing

Power BI online is limited to 8 refreshes per day. There are a raft of fish hooks in ways around that in import mode.

5.      Access to Rest APIs

Power BI does not support using Rest APIs through using Microsoft Power Query M and will not automatically refresh and this be difficult to share online. But an automated ETL solution will resolve this issue.

6.      Data Transformation

Some data requires transformation before it can be used e.g. breaking up a string in an account code into its various meanings. E.g. |200 Sales|  goes to  |200 | and | Sales|

7.      Data Uploading Times to Power BI

Reporting can be optimised where data storage is managed efficiently rather that uploading data directly to Power BI from multiple sources. For one client, the data upload time was reduced from 20 hour to 30 minutes.

Data uploading times can be reduced where incremental data extraction is used with an ETL solution. For one client, daily data extraction has been reduced from 30 minutes to 4 minutes.

8.      Processing Times

There are processing time gains for regular reports where dashboard measures are created in our ETL tool and sometimes with a native query. Processing time can be saved by having measures in the database rather than managing them in Power BI. We do database transformation in T-SQL, R and Python, and very simple measures in Power BI. 

9.      Machine Learning Capability

Machine learning (e.g. customer churn and forecast models) is possible using Power BI Desktop and it works with scheduled refreshes. But we have found that it works best using an ETL tool and a separate database to keep a record of how models have performed in the past. These can be used for model optimisation and other accuracy measures and alerts where accuracy levels fall below an acceptable threshold.

If you want a great Business Intelligence solution, Microsoft Power BI is certainly game-changing and is Number One position in the Gartner February 2019 Survey. It has the look and feel of Microsoft software for ease of use. It is much more affordable that other options like Tableau and Qlik. There is a large, growing user community.

But it is not a stand-alone solution. To create an end-to-end solution, you have two options:

1.      Start from Scratch

Build your own BI team, spend a fortune on a steep learning curve and wait a long time to see results.

2.      Quick Start BI

Outsource your BI project by using a team of data science and business translator experts like we have at Smetric Insights. Get results in weeks, not months and have ongoing expert support.

Want to get more traction with your Business Intelligence project but don’t want to keep going down the DIY track? Send me a message and tell me a bit about your firm and what you’re looking to achieve. Ideally you’re between $2 and $50M in revenue. Contact us when you’re ready.

Recent Posts