Power BI Dashboards in Jiwa 7

Samples and Examples of using the Jiwa 7 framework - including plugins, stand-alone applications and scripts.

Power BI Dashboards in Jiwa 7

Postby Mike.Sheen » Fri Nov 06, 2015 4:11 pm

PowerBI is an awesome platform for business intelligence.

I'm going to show you how you can embed a Power BI dashboard in Jiwa, so you can deliver stunning dashboards and interactive BI visualisations, such as this:

Sample Dashboard.PNG
Sample Dashboard
Sample Dashboard.PNG (134.06 KiB) Viewed 7764 times


Step 1.
Download and install the Power BI Desktop application.


Step 2.
Run Power BI Desktop, And select "Get Data" from the opening splash screen.
Open Power BI.PNG
Power BI Desktop - Get Data



Step 3.
Choose "Database", then "SQL Server Database" and then click the "Connect" button.
Select Database.PNG
Get Data - SQL Server



Step 4.
Enter the SQL Server Name and database, and press OK
SQL Credentials.PNG
Enter SQL Server Name and Database Name



Step 5.
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"
SQL Credentials 2.PNG
SQL Username and password



Step 6.
Select tables and views. For this quick example, just select the view SO_Sales - this is the sales history. Press "Load" when done.
Select Tables.PNG
Select Tables and Views



Step 7.
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.
Visualisation - Debtors and Value.PNG
Adding a chart visualisation


Step 8.
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.
Visualisation - Cetegories.PNG
Add Category Visualisations


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


Step 10.
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. On a tile, press the Pin icon on the top-right of the tile and "choose Pin to dashboard". Choose "New Dashboard" and give it a name (JiwaDemo in the screenshot), then click the "Pin" button. Then Pin the other tiles to the same dashboard
Pin to new dashboard.PNG
Pin report tile to new dashboard



Step 11.
Select the elipses (...) of the dashboard and choose the "Share" button.
Share Dashboard.PNG
Share dashboard
Share Dashboard.PNG (32.5 KiB) Viewed 7764 times



Step 12.
Select the "Shared With" tab and copy the Share link into your clipboard - we'll need that in one of the next steps. Note you may want to also add other people to share with, if they're also going to want to see this dashboard - just add their addresses to the "Invite" tab.
Share Link.PNG
Share Link



Step 13.
Open Jiwa, and Open the Plugin Maintenance Form. Import the attached plugin and save.
Plugin Power BI Sample.xml
Power BI Sample Plugin
(213.82 KiB) Downloaded 319 times



Step 14.
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. Open and run the SQL Script - make sure you select the correct database. Quit Jiwa, and log back in.


Step 15.
Open the System configuration form and enter the URL from Step 12 into the contents of the "Power BI Share Link" under the "Power BI" Tab. Save.
System Setting.PNG
System Configuration


Step 16.
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.
Menu Maintenance.PNG
Menu Maintenance



Step 17.
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).
Sign in.PNG
Sign in to Power BI



Step 18.
The dashboard built should then appear. The bars of the charts can be selected individually, or multiple-selected to filter the data visually. Below we show a selecting a single Category 1:
Result.PNG
Dashboard Result



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:
JiwaTreeMap.PNG
Jiwa Service Manager Tree Map

(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.
JiwaTreeMapSelected.PNG
TreeMap - Selected Debtor


Another way to slice the data is to select a staff member - now we see the time spent just for that staff member:
JiwaTreeMapSelectedStaff.PNG
Tree Map - Selected Staff


Endless possibilities, and extremely powerful!
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Return to Samples and Examples

Who is online

Users browsing this forum: No registered users and 1 guest