Power BI

 

SwiftPOS > Power BI

 

Description

 

This document is aimed at providing help information when using the free version Microsoft Power BI Desktop as a BI tool for interrogating SwiftPOS data.

 

Power BI is a business intelligence (BI) and visual analytics platform that supports a range of use cases, including centrally deployed guided analytics apps and dashboards, custom and embedded analytics, and self-service visualization, all within a scalable, governed framework.

 

To Be Considered

 

  1. The SwiftPOS BI Power BI solution provided makes use of Power BI's Data Import option, as opposed to DirectQuery option. It is recommended by Microsoft that wherever possible the Import option is used.
    1. Import - Using this option means that data is only imported whenever Queries are refreshed. The benefit of this option is that as you create and/or interact with visualisations, Power BI uses the imported data, rather than the underlying data source (SwiftPOS DB). The downside is that the data being viewed, is always as recent as the last time the data was imported (refreshed). In order to view current data you will need to refresh the data (Queries). Also, when it comes to importing very large amounts of data, the import option may NOT be the appropriate option, as it involves data transfers into Power BI's cache. In light of this, we recommend to set the Periods and No Of Periods parameters to 3 Months to begin with. This will ensure ONLY the last 3 months of data will be imported. This can then be changed as required. See Changing Parameters below.
    2. DirectQuery - Using this option means that data is returned by directly querying the underlying data source (SwiftPOS DB) every time you create and/or interact with visualisations. This will mean you are always be viewing current data. However, this may potentially be a cause for performance degradation and place and adverse load on the underlying data source (SwiftPOS DB). It is only recommended to use this option when the near 'real-time' reporting is needed.

 

  1. PROS
    1. Free download of the Desktop version of Power BI. For alternative options and pricing information click here.
    2. It's great for Excel users. If you're a Microsoft Excel power user, you will love Power BI because the tool navigation is very similar.
    3. It has good report visualization capabilities. It has around 16 different chart types, which is a reasonable amount if you want to represent your data in a variety of formats and visualizations.
    4. It has extensive database connectivity capabilities. Power BI connects to most types of on-premise databases and they have a large and growing list of cloud-based connection options as well.

 

  1. CONS

 

    1. It's challenging to use. If the end user of your BI solution is a business user, Microsoft Power BI is going to be very challenging to use. Thankfully Microsoft Power BI doesn't require users to write SQL code like other BI tools, but the tool is challenging to use if you are not a Microsoft Excel power user.
    2. It doesn't handle large data sources well. If you have a huge set of data to analyse, Microsoft Power BI probably isn't the best solution. When trying to connect and import large datasets, Microsoft Power BI users will experience a lot of time-outs and slow performance. Their solution will be to migrate that data into SQL Server to start.
    3. It's pretty complex: If Microsoft has one flaw, it's that they make their product options too complex. Power BI is no exception. It's very difficult to understand exactly what components of Power BI you may need, and the list is long. There's Power BI desktop, the Power BI Gateway, Power BI Services, and the list goes on and on. Because there are so many moving parts to this software, you will spend a large chunk of time figuring out what each part does. This also means that there are more parts to the software that have the potential to break, making the product more challenging to troubleshoot. This all adds up to increasing the total cost of ownership which we outline here.

 

Pre-Requisites

 

  1. Follow the steps outlined in BI Overview. Specifically the Pre-Requisites and the Getting Started sections.
  1. Download and Install Power BI Desktop from here. Create a login and register.
  2. Create a folder PowerBI in C:\...\Documents, if it does not already exist.
  3. Download the https://swiftpos-helpfiles.s3.ap-southeast-2.amazonaws.com/SwiftPOSBIPowerBI.zip  file. This file contains the following file:
    1. SAL001 SwiftPOS SALES.pbit - This file contains the Power BI template for the reporting of Sales data.
    2. SAL002 SwiftPOS SALES BY MEDIA.pbit - This file contains the Power BI template for the reporting of Sales by Media data.

 

  1. Extract the above files to the folder  C:\...\Documents\PowerBI folder.

 

Getting Started

 

  1. Ensure the steps outlined in the Pre-Requisites above, have been completed.
  2. Launch Power BI Desktop, and do as follows:
  1. Login.
  2. In Power BI Desktop, select Open other reports option on the left

 

  1. In the File Explorer prompt, locate and open the PowerBI folder created in step 2 of the Pre-Requisites section above.
  2. Select the Power BI template files (*.pbit) option in the File type drop down list.
  3. Select and open the SAL001 SwiftPOS SALES.pbit file extracted earlier.
  4. The following parameter screen will be displayed:

 
  • Server - Enter the name of the Server hosting the SwiftPOS database.
  • Database - Enter the name of the SwiftPOS database.
  • Period - Select from the drop down list the type of Period to be used when importing Sales data. For example, select MONTH to import Sales data based on Months.
  • NoOfPeriods - Enter the number of Periods to be selected when importing Sales data. For example, to import the last 3 months of Sales data, enter 3.

 

Select Load

 

 

  1. Power BI will immediately attempt to run the Queries contained in the template.

 

Select Run

 

  1. You will be prompted with a prompt similar to the following:

 

Select Run

 

  1. Power BI will immediately attempt to refresh the Queries again. On completion, it should return a Filters page based on the last 3 months of your data, that should look something similar to this:

 

Note : If however, Power BI does NOT return data, but rather remains endlessly trying to refresh the queries or is unable to refresh the queries, it is recommended that you close and restart Power BI and try again by specifying a smaller volume of data to import by changing the Period and NoOfPeriods parameters in step 6 above.

 

  1. Currently the Sales Query imports Sales data for all Locations. If you would like to restrict the import of data to selected Locations, the do as follows:
  1. Select the Edit Queries icon at the top of the screen.
  2. In Power Query Editor screen:
  1. Select the Sales Query.
  2. Select the Advanced Editor icon at the top of the screen.
  3. In Advanced Editor screen, locate the LocationNo BETWEEN statement in the WHERE clause of the SQL Query.

 
  1. Change LocationNo BETWEEN 0 AND 99999 to:
    1. LocationNo BETWEEN 1 AND 5 to import data for all Locations between 1 and 5, OR
    2. LocationNo IN (1,3,5) to import data for Locations 1,3 and 5.

 

  1. Select the Close and Apply button in the top left hand corner of the screen.
  2. Power BI will immediately attempt to refresh the Queries contained in the template. Note : If however, Power BI does NOT return data, but rather remains endlessly trying to refresh the queries or is unable to refresh the queries, it is recommended that you close and restart Power BI and try again by specifying a smaller volume of data to import by changing the Period and NoOfPeriods paramaters. For more information ...

 

  1. In the FILTERS page.

This page provides multiple Filters that can be used to further filter the data reported. For example:

  1. Months Ago - Displays a list of the Months applicable to the data returned by the Load script. 0 - being the current month, 1 - being the previous month, 2 - being the month before that and so on.
  2. If I wanted to see all Beverage Sales of 2 months ago, then I would select 2 - 2019 Apr in the Months Ago filter and 2 - Beverage in the Master Groups filter.
  3. The SALES Inc value displayed (top right), will change depending on the filters selected.

 

  1. Select any of the remaining 5 pages available to further analyse the Sales data. For example:

 

Changing Parameters - Server / Database / Period / No of Periods

 

If your SwiftPOS database is:

  1. Not located on the localhost, OR
  2. Named anything other than SWIFTPOS, OR
  3. The Period/No of Periods to be reported on needs to be changed
  4. Then the Parameters will need to be changed. This can be done by selecting the Edit Queries drop down and selecting Edit Parameters.

 

  1. Change the parameters as required.
  2. Select OK.
  3. Then select the Apply Changes button highlighted at the top of the screen.
  4. You may be prompted with a prompt similar to the following:

 

Select Run to refresh the queries using the new Parameters.

 

Power BI Development/Support

 

For further information see the Development/Support section in the BI Overview.

 

Related Topics

 

  1. BI Overview

 

Published 28 Mar 2024