Microsoft Power BI

Owned by Scott Pearce
Last updated: Nov 09, 2022 by Monica Holliday
6 min read

 

 

 

About Microsoft Power BI

Microsoft Power BI is Microsoft's offering in the business intelligence space. Data may be input by reading directly from a database, webpage, or structured files such as spreadsheets, CSV, XML, and JSON. The advantages of leveraging Microsoft Power BI are many, including, but not limited to:

  • Access to a variety of data-sources including SQL Server and Azure services.

  • Integration with other Microsoft applications including Edge, Excel, and Teams.

  • A mature and continually maintained product.

  • Desktop and web (SAAS) based designer software.

  • A wealth of support resources including video tutorials, forums, and skilled professionals for hire.


Getting Started

There are two aspects to learning about Power BI in terms of Jiwa. One, how to develop "dashboards" in Power BI, and two, how to "integrate" these dashboards into Jiwa (i.e. add them as a menu item to be launched from within Jiwa itself). This document focuses primarily on aspect two - how to integrate Power BI dashboards into Jiwa, as there are already plenty of other resources for creating Power BI dashboards available elsewhere, including:

 

My First Power BI Report

Below is a walk-through of how to create a simple Power BI dashboard that is driven by data coming from a Jiwa database. After creating the report, you will be shown how to integrate it into Jiwa by using a plugin. The plugin allows you to add your report as a form which can be launched from the Jiwa menu by any Jiwa user that you give permissions to. Here is what the end product will look like within Jiwa:

Creating a Report

  1. Begin by downloading and installing the Power BI Desktop application.



  2. Run Power BI Desktop. You should sign in using your Microsoft account - signing in is necessary because you will be "publishing" the dashboard you create via your Microsoft account for others to see. If you do not have a Microsoft Account, you can create one for free here. Sign in by clicking on the "Get Started" button on the Power BI splash screen as shown below:






  3. Once you have signed in, running Power BI will show you this splash screen:




    Click on the "Get Data" link at the top left of the splash screen.



  4. Choose "Database", then "SQL Server Database" and then click the "Connect" button:






  5. Enter the SQL Server Name and database, and press OK:





  6. Enter the credentials of a SQL user (NOTE: In my tests JiwaReports or JiwaUser did not work - I had to use sa... If you are connecting to an Azure SQL database, then you should provide the username defined when you created the Azure SQL server), and press "Connect":






  7. Select tables and views. For this quick example, just select the view SO_Sales - this is the sales history. Press "Load" when done:





  8. Add a Bar Chart visualisation by clicking the chart tool on the top right, under the "visualisations" pane. Drag and drop DebtorName from the fields into the Axis field, and then drag and drop the LineTotal field onto the Value field of the visualisation:






  9. Add another bar chart visualisation, this time drag and drop the Cat1Description onto the Axis, and again LineTotal field onto the Value field of the visualisation. Add a third Bar Chart visualisation and drop the Cat2Description onto the Axis, and again LineTotal field onto the Value field of the visualisation:



 

Publishing a Report to Power BI Service

  • Publish the visualisation using the Publish tool on the ribbon (you may be prompted to login using your Microsoft account).

  • Open the Power BI site in a browser (you may be prompted to login using your Microsoft / Power BI account), and in the Workspace pane, select the Report published previously. 

  • Click on File → Embed Report → Website or portal

  • Copy the first link, this will be used in the plugin in Jiwa.

 

Install Jiwa Power BI Plugin

 

  1. Still in the plugin maintenance form, select the documents tab and Right-click on the document "SQL Script to add form to forms table", and save to disk. 

  2. Open and run the SQL Script - make sure you select the correct database. 

  3. Quit Jiwa, and log back in.

  4. Open the System configuration form and enter the URL copied previously into the contents of the "Power BI report URL" under the "Power BI" Tab. Save:





  5. Open Menu Maintenance in Jiwa and select the default menu. Click on the Type column of the Forms grid until it is sorted so the Plugin forms are at the top. Drag and drop the "Power BI Sample" form onto the menu. Save. Note for a production scenario you shouldn't modify the default menu, as any future upgrade will overwrite it. You should create a new menu, add the Power BI menu option and then attach that menu to a user group. This way the menu option can be explicitly exposed only to certain users. There is also a permission you can set to allow only certain user groups access to the form:





  6. Locate the newly added form on the menu and open it. You may be prompted to enter your Microsoft account credentials, enter them if needed (and perhaps select the "remember me" checkbox):





  7. The dashboard built should then appear. The bars of the charts can be selected individually, or multiple-selected to filter the data visually.





Further Information

After following the above guide, you will probably want to edit the dashboards / reports with some more interesting data and visualisations. This is very easy to do, and also note that there is a gallery of additional visualisations you can download from the Power BI Visuals Gallery. You can also author and publish your own visualisations.

The model built can be refreshed manually from the Power BI Desktop application, and then re-published - or you can refresh the data from the Power BI web portal. You can also schedule refreshes to happen automatically (eg: daily).

We'll be building some models in the future and publishing them for standard Jiwa databases, but the above guide should be enough for now to get anyone started who wants to get some Power BI integration with Jiwa immediately.

Another example visualisation we've done internally to give an idea of the diverse usefulness of PowerBI visualises the time spent by staff, by customer and activity from Service Manager as a TreeMap:

 

 

(Some customer and staff names redacted)

And then clicking on the Yellow/Orange tile, which represents a particular debtor, I can see the breakdown of time by staff and date (month) - in this case, I can see only one staff member spent time on that debtor during the month of October 2015. I also see the total hours elapsed now showing me a figure just for that debtor:

 

 

Another way to slice the data is to select a staff member - now we see the time spent just for that staff member:

 

 

It should now be apparent that Power BI is relatively easy to use, extremely powerful, and offers endless possibilities for visualising your company's' data.