What is BI?
BI, or ‘Business Intelligence’, is the generic term for the process of transforming data into meaningful information to increase understanding and awareness around a business and help management to make informed decisions.
In recent years corporate organisations have really started to see the value in developing their Business Intelligence. Whether the aim is to cut costs, increase productivity, improve branding, ensure compliance with the law or pretty much anything else a big business needs to deal with, good BI can help. As a result of this shift in focus, powerful BI software like Qlikview and Tableau has flourished. These programs feature all sorts of visually stunning ways of presenting information but they can be very expensive!
The Power BI suite is Microsoft’s answer to these competitors. They are fighting back and as a result Excel is evolving into something more powerful than ever before, with the new self-service Power BI add-ins installed.
What is self-service Power BI?
Self-service Power BI tools are there so you don’t need to depend on your IT department anymore to build all your reports and carry out your analysis. They empower the end user to do that themselves. With minimal programming skills, anyone with a good logical mindset can quickly become proficient with them and get cracking running queries and delivering business insights.
We’re going to breakdown the key Power BI tools with brief explanations here, and we’ll go into much more detail in future articles.
Just a quick word on compatibility with the various versions of Excel. If you’re using 2007 or prior, you can’t use Power BI within Excel but you can download Power BI Desktop for free. Although limited, using that will help get you into the swing of things. Excel 2010 and 2013 users can enable the necessary add-ins to their installations. If you’re using 2016 the tools are in-built and ready to go, just be aware that Microsoft seem to have dropped the ‘Power’ from the naming – so ‘Power Query’ is now ‘Get & Transform’ for example.
Power Query has the potential to replace what you might previously have done using MS Access in terms of querying data, and in VBA in terms of automation. When previously you would have been recording macros and writing an awful lot of VBA to carry out a relatively simple Excel process, Power Query allows you to do lots of these things in seconds. Unpivot data is a prime example of this. Your query is then saved and is reusable for the future, whether you simply want to refresh it every time you open the file or want to copy it and make slight adjustments to make it work for you on a different project. The programming language used for writing your queries is known as ‘M’ but you can do an awful lot with the in-built functionality without any knowledge of the M language. We’ll do lots more with Power Query on this site in the coming weeks and months so watch this space!
Power Pivot allows you to create relationships between tables and other data sources and join them in a data model with an easy interface in much the same way as you might in a Microsoft Access query. Using Power Pivot can replace some of the mundane VLOOKUPs, pivot tables, INDEX / MATCH formulas you may have become accustomed to in pre-Power BI Excel. It has the capability to work with huge datasets, millions of rows rather than having the same limitations as an Excel worksheet. DAX (Data Analysis Expressions) formulas can be used to add calculated fields, amongst other things.
Power Maps is an add-in which allows you to display geographical data visually in the form of a 3D map. This allows you to identify hotspots when reporting on anything with data that includes a location, it works with countries, cities, even individual postcodes depending on what data you have available.
Power View enables users to present their data in a dashboard format without having to learn any specialist Excel skills. All sorts of fancy interactive charts, graphs and other visuals are available in Power View which you won’t find available in native Excel.
Power BI Desktop
Available as “freemium” software from the Power BI website, this cloud-based tool has a free version which can be downloaded or you can opt for the full paid premium product. This effectively provides you with Power Query and Power Pivot rolled into one, along with the data model behind it and using DAX formulas just like the rest of the Excel Power BI suite. The data visualisation capabilities are beyond those of Excel’s and this is Microsoft’s closest equivalent to the likes of Qlikview and Tableau. If you’re still using an older version of Excel (2003 or 2007) it’s also an opportunity for you to get to grips with Power BI outside of Excel. I won’t go into masses of detail here, but while this serves as a good alternative it doesn’t have all of the full functionality of the Excel add-in versions.
Excel Data Model
The data model behind the Power BI stack is the key reason we can talk about Power Pivot crunching millions of rows of data rather than thousands. When data is loaded from Power Query, you have the choice to either load to the Excel worksheet or instead to the Excel Data Model. If you choose the latter, the data is effectively still stored outside of the Excel file itself and just connected to as and when its needed. As long as the final output is still within Excel’s limitations, the queries that make it up no longer need to be.