All the consultants out there are talking about deriving more insights from your “data” and your own top management are also singing in unison to move towards a “data-driven” future. No doubt, “Qlik” and “Tableau” had definitely been brought up in these conversations. You must be thinking, “Seriously? These software are gonna bring change to my company? Oh please!”.
Well I had that exact same thought. So I decided to explore these Business Intelligence (BI) tools further, starting with Microsoft Power BI. Why? Because it is free!
What is Power BI?
Check it out from the official site: https://powerbi.microsoft.com/en-us/
I would say it is Microsoft’s attempt to compete with the two leading BI tools by leveraging on its strengths in Office Suite and Cloud Services. My first impression of Power BI was pretty good. The start-up is fast, the UI is similar to a typical Office software, and making changes to the dataset is also smooth. If you are a seasoned Excel user, then learning to use Power BI should be a breeze for you. As an absolute noob, you might have to go through the tutorials offered by Microsoft and hangout in the Power BI forum.
However, after using the tool for a while, I come to realise that all the hype over “Self-Service” BI does not equate to ease of use or intuitiveness. The tools merely combined some basic data extraction and manipulation with the ability to create graphs and charts. It is definitely not designed for the ordinary users who do not have an inkling of how data set can form relations, perform table join operations, and how the data can be presented objectively. Even though it is not necessary, having some foundation in data science will certainly help.
Cloud, Desktop, and Mobile
The Power BI desktop client is the application designed primarily to interact with the data and build reports. Power BI also provides a cloud service that can perform the required BI analysis, although the main objective of this service is to publish and share any reports that you have built. The mobile application is designed for users to read the published reports from the cloud using their mobile devices.
So for someone who would like to explore the analysis capabilities of this tool, installing the desktop client is a must.
Report Building Process
This is generally the sequence of actions for building a new report from scratch. I have yet to figure out how to create a report development workflow that will apply existing report templates to new or updated data sources. The following are performed with Power BI Desktop.
- Connect to Data Source
In my case, I worked with a couple of Excel spreadsheet. You may consider connecting directly to databases. After connecting, the tool will extract/query all the available data tables.
- Modify the Data
The data table may not be in a desirable format to produce any useful visualisation. Use the Query Editor to manipulate the data without modifying the source. It provides a number of useful functions, it processes the familiar Excel formula language (DAX or Data Analysis Expression), and also can execute your custom scripts on the data.I noticed that every change performed on the data is recorded as a layer (similar to Photoshop). You may go up and down the stack and modify or remove any layers you wish. This comes in handy when you are trying to explore/clean up your data but do not want to commit the changes until you are confident.
- Relationship View
One of the three views that Power BI desktop provides. This allows you to create relationship between different data tables (outputs from step 2). Note that relationships only makes it easier for you to find information across tables pertaining to specific records using related fields between tables. It is not a join operation and it will not provide any benefit that a table join otherwise will provide. (Table joins should be performed at step 2).
- Data View
I honestly feel that this view provides the same functionality as Query Editor, which makes me wonder what I should do with this view. My guess is for any further modification of the data that you failed/forgotten to perform in step 2, you may perform it in this view.Some the things you can do to your data includes filtering, data transform, data transpose, changing data format, data processing using DAX, aggregation, table joins, adding and removing fields and records, etc.
- Report View
When you have finally gotten all your data prepared, the report view will assist you to create visualisations from the data and assemble it into a report, that can be published on the cloud service.In the report view, all that you should be doing is to match data and present them using the variety of charts. You should not attempt to manipulate the data within the report view. Of course, even if you are not prepared, you can revisit any of the previous steps and make the necessary adjustments.
- Publish to Cloud
And after all your hard work, you may publish the report to the cloud service and share your creation with others. You may also choose to modify the Phone view so that the report may be accessed from a mobile device using the Power BI mobile app.
- Interactive Visuals
What I like about Power BI is the interactive visualisations that automatically filter values and changes the charts dynamically as you click on it. You are also able to drill down into deeper details of the data.
Additional Helpful Tips
The following information may get into the details of using Power BI. These tips were very helpful when I was learning to use Power BI, and I will just park them here for future reference.
- Difference between Query Editor and Data View
- Change text field to date format (dd/mm/yyyy)
- Replacing blank cells with value from the row above
- The different types of table joins
- Filtering for rows with duplicate values existing in a certain column
- Relationship – understanding cardinality and cross filter direction
- How to stop some of the fields from showing up as “sum” in the report view
- Count the number entries in a column but exclude certain values