Microsoft Power BI

Microsoft Power BI is a powerful but accessible desktop and cloud-based Business Intelligence tool, designed to work well with the Microsoft Office software suite. Power BI makes it relatively easy to create meaningful graphics and metrics for gaining and sharing knowledge about business trends. 

Power BI allows easy connection to a wide range of data sources. While it is designed to work best with Microsoft’s software, such as Excel, Azure SQL DB, and SharePoint, it also easily connects to many additional tools, ranging from almost any relational and non-relational database to many online web applications.

Included within its suite are some elaborate data preparation tools, making the normally time-consuming and difficult process of data modeling much simpler, using drag and drop functionality and Microsoft’s familiar Office ribbon toolbar.  It will enable ingestion, transformation, and data enrichment to import and manage multiple data sources. 

Like Microsoft’s Excel product, Power BI provides some advanced tools for data manipulation and graphing, however it also offers some sophisticated AI tools, including forecasting and clustering of datasets. 

Power BI also makes it easy to create many interactive reports just by dragging and dropping data columns into filters. These reports are responsive to user interaction and can easily be published from Power BI Desktop onto the Power BI cloud platform for easy sharing and collaboration. 

Below is what Power BI looks like upon first opening it. It uses the familiar Microsoft Office-style ribbon menu, with many widgets for making manipulation of data relatively simple.

Importing Data

Bringing in data from a spreadsheet, one will first get a quick preview of data to be imported. It can either be loaded immediately, or the data can be transformed using the Power Query tool.

The Power Query screen helps perform most of the data cleanup and modeling typically associated with most ETL tools.

The Power Query tool is particularly useful if bringing in datasets that have been already manipulated by a human; it can eliminate various columns that are not helpful for analysis, such as totals, subtotals, etc.

The “Applied steps” can help you track your changes and see exactly what work you have done. It has other useful features, such as being able to unpivot data, to get raw results.

 

Once the data has been imported it’s easy to examine your full dataset. 

Data Visualization

Power BI makes creating data visualization by enabling straightforward creation of various attractive charts and graphs quite easy and intuitive. One simply needs to drag fields from the data set and place them into the filters on the screen, and charts begin to appear. 

 

There are many options for visualizing data; below is an example of a pie chart:

Importing from Databases

Beyond working with simple spreadsheets or CSV files, Power BI has a series of tools which allow bringing in data from entire databases for the purpose of manipulation and reporting. 

To be able to import a MySQL database required installing a new package (Power BI makes it easy to find), and then restarting the application. 

The ability to get a visual representation of the Entity Relation Model (ERM) of the database is extremely helpful.

 

It’s worth noting that the AI within the application is able to automatically detect relationships between data in a database and any other data sources that are imported; it recognizes potentially common keys to make querying from one dataset to another easy. 

Once installed, and a database is imported, Power BI automatically recognizes relationships between tables. For instance, it is possible to get information from the client table and get a sum of net pay per client, by simply dragging the data into specified fields in the filters.

Power BI includes a number of useful filters. For instance, dates are immediately recognized, and Power BI allows filtering into several standard delineators, such as quarters. 

Dashboard/Sharing

After creating several charts, these can be organized into dashboards. Power BI gives you the option to “publish” your dataset, which will then immediately link to its online cloud. Dashboards are very clear and can be interacted with by end users.  Sharing is easy; users only need to have a Power BI account (which is free for viewing; creation of dashboards the online tool requires a subscription).

Advanced Analytics

Power BI also has some interesting built-in predictive features, with some natural language processing.  Using the “key influencers” widget, it can predict the effect of one indicator on another. For instance, we can determine the effect that “hours” has on “net_pay” by dragging these indicators onto the screen.

 

There’s a built-in Q/A tool, which suggests several questions about data given the existing structures, which will then return results.  Or you can ask your own questions. 

 

Power BI also has some more advanced tools allowing one to write scripts using Python or R to make custom predictive models. To this end, requesting assistance from professional data experts will help you get the most of Power BI. Xperra has a professional team of Data Engineers ready to provide custom solutions for each particular case.

Microsoft has created an excellent Business Intelligence tool. Power BI is well-suited for high level business users who like working with other products within the MS Office suite.  It’s easy to get working for many small projects. Power BI is good for leadership teams who want high level information, who don’t want to have to dive too deep. 

Power BI handles some of the data extraction (particularly for files) and is fairly good at some of the necessary data transformation (such as grouping and organizing data), and it also has a “loading” capability, so that you can load results of multiple merges into a database. Power BI is best to use as a final endpoint application for those dealing with more complex information. 

However, it’s not designed for some of the more advanced analytics projects. Although, it supports ETL functions, for more complex ETL processes such tools as KNIME or Alteryx perform much better.

Overall, since the Desktop version is free for use, and there is a trial for the online features, Power BI is certainly worth experimentation for many businesses who wish to be able to quickly generate some meaningful graphics out of multiple datasets.

File data sources:

• Excel

• Text/CSV

• XML

• JSON

• Folder

• PDF

• SharePoint folder

 

Database Data Sources

• SQL Server database

• Access database

• SQL Server Analysis Services database

• Oracle database

• IBM DB2 database

• IBM Informix database (Beta)

• IBM Netezza

• MySQL database

• PostgreSQL database

• Sybase database

• Teradata database

• SAP HANA database

• SAP Business Warehouse Application Server

• SAP Business Warehouse Message Server

• Amazon Redshift

• Impala

• Google BigQuery

• Vertica

• Snowflake

• Essbase

• AtScale cubes

• BI Connector Data Virtuality LDW (Beta)

• Denodo

• Dremio

• Exasol

• Indexima (Beta)

• InterSystems IRIS (Beta)

• Jethro (Beta)

• Kyligence

• MarkLogic

 

Azure Integrations

• Azure SQL database

• Azure SQL Data Warehouse

• Azure Analysis Services database

• Azure Blob Storage

• Azure Table Storage

• Azure Cosmos DB

• Azure Data Lake Storage Gen2

• Azure Data Lake Storage Gen1

• Azure HDInsight (HDFS)

• Azure HDInsight Spark

• HDInsight Interactive Query

• Azure Data Explorer (Kusto)

• Azure Cost Management

• Azure Time Series Insights (Beta)

 

Online data sources

• SharePoint Online List

• Microsoft Exchange Online

• Dynamics 365 (online)

• Dynamics NAV

• Dynamics 365 Business Central

• Dynamics 365 Business Central (on-premises)

• Microsoft Azure Consumption Insights (Beta)

• Azure DevOps (Beta)

• Azure DevOps Server (Beta)

• Salesforce Objects

• Salesforce Reports

• Google Analytics

• Adobe Analytics

• appFigures (Beta)

• Data.World - Get Dataset (Beta)

• GitHub (Beta)

• LinkedIn Sales Navigator (Beta)

• MailChimp (Beta)

• Marketo (Beta)

• Mixpanel (Beta)

• Planview Enterprise One - PRM (Beta)

• Planview Projectplace (Beta)

• QuickBooks Online (Beta)

• Smartsheet

• SparkPost (Beta)

• SweetIQ (Beta)

• Planview Enterprise One - CTM (Beta)

• Twilio (Beta)

• tyGraph (Beta)

• Webtrends (Beta)

• Zendesk (Beta)

• Dynamics 365 Customer Insights (Beta)

• Emigo Data Source

• Entersoft Business Suite (Beta)

• FactSet Analytics (Beta)

• Industrial App Store

• Intune Data Warehouse (Beta)

• Microsoft Graph Security (Beta)

• Product Insights (Beta)

• Quick Base

• TeamDesk (Beta)

• Workplace Analytics (Beta)

 

Other Data Sources

• Web

• SharePoint list

• OData Feed

• Active Directory

• Microsoft Exchange

• Hadoop File (HDFS)

• Spark

• R script

• Python script

• ODBC

• OLE DB

• BI360 - Budgeting & Financial Reporting (Beta)

• FHIR

• Information Grid (Beta)

• Jamf Pro (Beta)

• MicroStrategy for Power BI

• Paxata

• QubolePresto (Beta)

• Roamler (Beta)

• Siteimprove (Beta)

• SurveyMonkey (Beta)

• Tenforce (Smart)List (Beta)

• Vena (Beta)

• Workforce Dimensions (Beta)

• Zucchetti HR Infinity (Beta)

• Blank Query

 

Exporting data: 

From Desktop

• CSV formats

• PDF

From Power BI (online)

• Pdf

• Powerpoint file

• Publish to web

 

Data from individual visualizations can be exported only as CSV files, using either underlying data, or summarized data.