Table of Contents

Overview

When using Microsoft Power Bi to analyse your system information there are three possible approaches

1. General Information and standard reports - Use our internal PowerBi Reports (coming soon)
2. Detailed Analysis with Lots of Data - Setup your own SQL Azure and PowerBi environments (you need a Microsoft Azure license and PowerBI online licenses)
   1. We will create a SQL database with a number of linked tables in Azure 
   2. We will publish data to those tables regularly
   3. Connect PowerBI to this table set - then publish to PowerBi online
   4. Edit and adjust your reports in PowerBi online
   5. Note PowerBi can also connect to other data sources like excel online or websites to enrich the data
3. Export from lists in the system regularly
   1. Either / or
      1. import these to power bi desktop then publish the results
      2. Load these upto excel online that Power Bi is connected to

Microsoft Power Bi

Power Bi Desktop

Use this to:

1. Define the data sources and add new data sources
2. Define the data conversion steps (from raw data to ready to analyse data)
3. Create reports / dashboards
4. This environment can do everything except allow others to view the results.  To do this you must publish your report to the online environment

Power Bi Online

1. An online report editor to create reports / dashboards
   
   1. Note: Can load up single tables and create reports - but not link tables together nor do data cleansing
2. Online Consumption of reports
   1. Reports once published are accessable by users

Where to get data

Power Bi can accept data from many sources into a dataset as you can see here...

Connecting to SQL Azure Database

If you have setup a SQL Azure database - then you can connect from the desktop or from the online version

Publishing the data to the Azure Database is from the Financial Period

Exporting from Lists in the system (CSV files)

Extracts from any list in the system - choose a date range etc if required.

BI Extracts (CSV Files)

If your system has a BI Download configured - these files save as CSV with the "|" character as the delimiter

Connection to a SQL Database

If you have large volumes of data and want regular automatic updates to it - an SQL Database is the ideal method.

The ideal place for this database is in SQL Azure - it is cheap and in the same place as Power Bi Online so data does not travel across your network for designing and refreshing the report data.

• For those that like excel it can also connect to Azure... https://blogs.technet.microsoft.com/office_online_support_blog/2015/10/29/excel-online-refreshing-data-to-a-sql-azure-database/   and https://powerbi.microsoft.com/en-us/documentation/powerbi-azure-sql-database-with-direct-connect/

If you connect to a database that is not in the same place (ie on your site) then every time you refresh the data in PowerBi online (it only has a complete data refresh - not an add updates) - there will be significant traffic on your network.

Data Cleansing

Once you have the data in Power Bi

You are likely to want to change headings, change data types, add columns eg calculations, etc.  Most of these are similar to Excel

Column Transformations

Adding columns

An example additional column is for a bitwise operator.  A bitwise operator means that a single column (OptionsProfile) contains a number that provides information about a number of different settings.

In the sample below - 32 = multiple vehicles, 64 = Favourite, 128 = Is bulk order, 16 = POD.

Review the changes made

Once you have cleansed the data you can review each step applied (it is applied again in order if you refresh the data)

Related articles

• Page:
  Secure Features
• Page:
  Go Live: Importing Open Purchase Orders
• Page:
  Thumbprint Scanner Setup
• Page:
  Point of Sale
• Page:
  setting up POS EFTPOS