Data Analysis is evolved into a science in the last decade, and the tools used for analysis were developed to deal with massive amounts of data. MS Excel caught up with this evolution with Power Pivot.
Power Pivot implements the features of database software such as database tables and relationships, adds its calculation engine with a language designed explicitly for analysis over Power Pivot, the Data Analysis Expressions (DAX), and reports analysis results with pivot tables and visualisations such as dashboards on Excel.
Power BI took potent features of Power Pivot and supported them with many improved features for analysis and presentation of data. It is also built on a similar database table structure and relationships, DAX engine and dashboard design features.
For ETL (extract, transform, load) operations Power BI has a separate tool, the Power Query, which is a much-improved version of “Get & Transform Data” of Power Pivot. It has a service component that allows online analysis and publishes the analysis reports on the web or mobile platforms. It has embedded AI capability and is a member of Microsoft’s Power Platform suite of applications.
Microsoft defines Power BI as a self-service business intelligence tool, “a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights”.
While Power BI is accepted as a leader in the business intelligence realm, it is also a work in progress, evolving into a much stronger tool with hundreds of features added every year by Microsoft.
We will start our analysis by loading Lead Merchants Sales Database tables to Power BI and setting relationships between these tables. Then we will answer our questions with the analysis of the sales database with Reports, and we will prepare the same dashboard we developed in Excel.
While Power BI is one of the most powerful tools for business intelligence, it is not designed to prepare bespoke reports. The major tool for reporting and building business models and calculations is still classic Excel. So as a final step, we will prepare the flat file we previously have arranged with SQL and Power Pivot to use in classic Excel.
You can download the Power BI file used for our analysis from the link below. If you want to follow the workshop performing the step-by-step instructions yourself, you can find the Lead Merchants Database tables here.
You can download and install the Power BI desktop here, and there is also a desktop application in Microsoft Store. Power BI is a Windows-only program and can not be installed on Mac unless it is using the Windows operating system. Installation is straightforward, and you need to register to use the software with service functionality.
In the first part, we will start with Power BI Desktop and load our tables. We will prepare the Report to answer the questions and the Dashboard, the same way we have done in SQL and Excel. Finally, we will again prepare the Flat File to summarise the data to use in Excel.
Next, we will explore Power BI Service, distributing our reports online over the internet and mobile devices, have a look at the AI and connection to systems for automation.