Business Intelligence and Big Data: How Microsoft Power BI Brings It All Together

In recent years, there has been an explosion in the amount of information and data that businesses collect and have access to. So, how do we extrapolate and use this to inform decision making and forecast events?

In this article, we are going to look at how to import data into Power BI for this type of analysis.  To start, let’s look at some of the overarching concepts.

What is Big Data?

Big data is mainly characterised as data that exhibits:

• Volume (the quantity of data)
• Variety (the type of data)
• Velocity (the speed at which the data is generated and needs to be processed)
• Variability (varying structure of the data)
• Veracity (quality of captured data).

The term also may refer to the technology that is needed to handle the large amounts of data. The exponential growth of data has made it increasingly difficult for organisations to identify and take advantage of the information derived. This is where business intelligence comes into play. With this new era of big data and data-driven decisions, the potential benefits with the implementation of Business Intelligence is unparalleled.

What is Business Intelligence?

Business Intelligence in a set of methodologies and processes that leverages software and services to transform data into actionable insights that informs an organisation’s strategic decisions.

Business Intelligence can be explained as an ongoing cycle whereby companies set their targets, analyse their progress, gain insights, take actions, measure their success and use it as a metric to set new targets.

The term Business Intelligence covers at least these five components:

• multidimensional analysis,
• reporting,
• data mining,
• financial consolidation,
• budgeting, and
• key performance indicators.

What is Power BI?

Power BI is a cloud-based user-friendly business intelligence platform that helps organisations and users to collate, manage and analyse data from a variety of sources. Power BI is used strategically to convert raw data into intelligible insights using visual charts and dashboards. This allows users to generate convincing reports and share the present state of the business.

Power BI consists of the Windows desktop application called Power BI Desktop, an online Software as a Service (SaaS) called Power BI Service, or mobile Power BI apps available on Windows, iOS and Android phones and tablets.

What are the benefits of using Power BI?

With Power BI, businesses can scrutinise and visualise their data from across the organisation. This gives them greater insights into their operations and performance which allows for informed decisions based on real data.

• Large volumes of data – Power BI can take advantage of huge volumes of data that most other platforms would struggle with. Power BI has impressive compression abilities which allow to analyse and visualise data that cannot be opened in Excel. Large datasets need not be sampled and aggregated to show a brief analysis. Power BI allows for all the granular details to be accessible through drill-downs.
• Modify and prepare data for analysis – Data cleaning and transformations can be performed using Power BI which includes changing data formats, adding and deleting rows and columns, transposing, pivoting and unpivoting tables, creating calculated measures, columns and tables. Creating relationships between multiple tables especially when the data warehouse uses star or snowflake schema. New datasets can be added into the data model without the need for restructuring the entire data model.
• Rich personalised dashboards – Information dashboards can be customised to meet the exact requirements of an individual or a team in an organisation.
• Publish reports to multiple consumers securely – Power BI helps to set up periodic data refreshes and publish reports allowing all the users to avail the latest information. It also ensures data security, offering controls on accessibility of the reports, dashboards and data both internally and externally.
• Supports Advanced Data Analysis – Power BI has built-in machine learning features that can analyse data and helps users spot valuable trends and make educated predictions. Power BI has a forecasting feature based on historic data. Automated machine learning (AutoML) for dataflows enables business analysts to train, validate, and invoke Machine Learning models directly in Power BI. Power Bi can also be used for text analytics for example visualising customer comments.
• Power BI Integrations – The dashboards and reports can be embedded and integrated into different applications using the JavaScript SDK with the dashboard embedding API. The platform also with business management tools like Office 365, SharePoint, Dynamics 365, Spark, Hadoop, Google Analytics, SAP, Salesforce and Mail Chimp.
• Specialised Technical Support – Power BI provides an agile approach and analysis so that there is no requirement for specialised technical support. It supports a powerful Natural language interface with the use of graphical designer tools.

Importing Data into Power BI

There are multiple Data Sources that are supported on Power BI. We can import data into Power BI using the “Get Data” dialogue box. 

Data Sources that Power BI supports or integrates with

The different data sources are listed and organised in the following categories. For this example, we will consider an Excel workbook with multiple tables in different sheets.

Select the Excel file.

The Navigator window opens and automatically lists out all the tables in the workbook. It also lists all the Excel sheets individually as well. For this example, I will be using the detected tables and have check-marked the tables I require. After this click on ‘transform data’ to open Power Query window.

The data types for each of the columns in automatically assigned and a step created. These steps or query settings can be seen at the right-hand side of the window. Using the name option, the name of the table can be changed. Each of the applied steps on the data to convert it to the ideal format is listed in the Applied steps.

Consider the Sales Table which contains only Sale Price, Cost price and Quantity sold. For reporting purposes, we are also interested in Total Sales, Total Cost and Gross profit per item. This can be calculated using measures as well without altering the table but in this case, we have opted for having 3 separate columns. We can add a new column using the Add Column tab in the Ribbon and selecting Custom Column.

Power Query provides a lot more ways of creating columns, but in this scenario a custom one makes sense.

The Custom Column Dialog box opens where we can manually write the Formula to create a Total Sales column or we can choose the columns and insert a mathematical function.

Similar process for Total Costs and Gross Profit.

After the tables are set up for reporting, we can click on Close and Apply found in the Home ribbon. Now the data has been imported into power BI.

The last step to get the data ready is to form connections between the tables so the data can be referenced while reporting. This is done in the model tab as shown below. The first tab is the Report tab, this is where we build reports. The second is the data tab, this is where we can do further changes to the data like add measures and change data types. This tab also allows for viewing the data in a table format.

We connect the fact and dimensions tables using the relationships between two different columns of two different tables. One-to-Many connection is always preferred for this. Drag a column name from one table into the column name of the other table to create a relationship. Here a relationship was created between Buyers table and Sales Table with a One-to-Many relationship between the Category column.

Similarly, we can create relationships between all the tables.

The connections between tables can also be created using the Manage Relationships button on the Home ribbon. This also has an autodetect feature which detects relationships between the tables and applies them.

How Power BI brings it all together

Power BI is the perfect combination of visual design, Microsoft Excel and Power Query. Power BI also has data modelling capabilities that allows you to connect data from multiple sources. This makes it the perfect platform for Business Intelligence. Power BI is not limited to the size of the company or team, which makes it very scalable.