Versions Compared

Old Version 1

changes.mady.by.user Maria Harreguy

Saved on

compared with

New Version Current

changes.mady.by.user Maria Harreguy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Guidelines Document

...

Guidelines Document

Introduction 

This document provides guidelines on how to use Power BI as a presentation layer for Workbench data, leveraging Sharperlight as a secure querying tool. This setup ensures that all data accessed and displayed through Power BI is securely extracted, transformed, and delivered without exposing direct connections to the database.    

image-20240926-001640.pngImage AddedObjective 

Objective 

To enable users to create dynamic reports and dashboards in Power BI while ensuring data security and system performance through the integration of Sharperlight. 

System

...

Architecture 

  • Workbench Database: Contains all operational data. 

  • Sharperlight: Acts as an intermediary that queries, transforms, and securely exposes data to Power BI. 

  • Power BI: Used for visualising to visualise and analysing analyse data provided by Sharperlight. 

Getting

...

Started 

  • Setup Sharperlight: 

    • Ensure Sharperlight is installed and properly configured to interact with the Workbench database. 

    • Define and configure the data models within Sharperlight to align with the reporting requirements. 

  • Data Security and Performance: 

    • Use Sharperlight to manage data access permissions and ensure secure data handling practices. 

    • Utilize Sharperlight’s capability to abort slow-running queries and

    to

    • stage data for long-running operations to maintain system performance. 

...

Methods to get data from Sharperlight

There are two ways to communicate between PowerBI and Sharperlight

  1. Custom Connector (PQX) file

  2. Using plain JSON Sharperlight Output. Recommended

The Custom Connector (PQX) is much more challenging to setup and maintain. We recommend using the JSON approach where possible. It allows end users to refresh to get the latest data from the Web and scheduled refresh can also be setup.
Both methods take snapshots of the query data and must be refreshed to get the latest report structure or data.

image-20240926-004945.pngImage Added

 

Connecting Power BI to Sharperlight: 

  • Open Power BI and select ‘Get Data’. 

...

The Power BI or JSON/Web URL can be accessed by either: Right-clicking in the Publisher query list or from the Links tab when you are inside a query.

...



JSON / Web connection:

  1. In Power BI: Select “Get data from another source”.

    image-20240926-014431.pngImage Added

  2. Find & Select “Web” as datasource.

    image-20240926-014519.pngImage Added

  3. Enter the secure endpoint URL provided by Sharperlight that points to the required data model.

...

  1. image-20240926-001750.pngImage Added

  2. Choosing this option means:

    1. You can only bring in one Sharperlight query per JSON link during initial setup.

    2. End users can refresh themselves on the Web to get latest structure & data.

    3. Can schedule refreshes (Up to 8 times per day).

Sharperlight (Custom) – Power BI link Not recommended

  1. Navigate to “C:\Program Files\Philight\Sharperlight\bin\Resources\powerBI”.

  2. Copy & Paste the PQX file into Documents\Power BI Desktop \ Custom Connectors.

  3. Open Power BI > Go into Options > Security > Data Extensions > Allow any extension to load.

  4. Restart Power BI to apply the changes.

  5. Copy Power BI link of any existing query from Sharperlight.

    image-20240926-020842.pngImage Added

  6. In Power BI: Select “Get data from another source”

  7. Find & Select “Sharperlight (Custom)”

  8. Paste the link you copied in Step 5.

  9. Select "Basic" for the authentication method.

  10. Click on the drop down for "Select which level to apply these settings to" and select the /PowerBI option.

  11. Login with your usual Sharperlight user login & password.

  12. Select any of the published queries that have been created as your Power BI data source

    image-20240926-020945.pngImage Added

  13. Choosing this option means:

    • You can bring in multiple Sharperlight queries during initial setup.

    • End users cannot refresh themselves on the Web to get latest structure & data.

    • Report owners / admins must refresh then publish in Desktop, in order for end users to get the latest structure & data on the Web.

Guidelines for Effective Use

  • If a wrong authentication method is accidentally selected

    • Go into the "Data Source Settings" in Options & Settings.

      image-20240926-015020.pngImage Added

    • Select the Data Source that you entered incorrectly.

      image-20240926-015115.pngImage Added

    • Click “Clear Permissions” to clear that data source.

    • Re-do steps in Page 2 again.

  • Refreshing the Report

    • Once the report has been published, you can trigger a manual refresh or schedule a refresh from the web if you have connected the Data Source via JSON/Web. Attempting to refresh a Data Source via Power BI link on the Web will result in an error.

      image-20240926-020117.pngImage Added

    • Before setting up a Scheduled Refresh, a Cloud Connection needs to be setup using your Sharperlight credentials as shown below:

      image-20240926-020235.pngImage Added

    • Once the Cloud Connection has been created and selected, you can then define your Scheduled Refresh:

      image-20240926-020331.pngImage Added

    • To view the report, click on the Item that has Report as Type

      image-20240926-020422.pngImage Added

  • Creating Reports in Power BI: 

  1. Use Power BI Query Editor to Expand query records: List -> To Table -> Expand:

...


  1. image-20240926-001914.pngImage Added

  2. Use the Power BI interface to drag and drop fields from the Sharperlight data

...

  1. source into the report canvas.

...

  2. Apply filters, slicers, and other data visualisation tools available in Power BI to enhance your reports.

...

  1.  

...

  • Data Transformation: 

    • Perform necessary data transformations in Sharperlight before exposing the data to Power BI. 

    • Use the standard data model provided by Sharperlight to ensure consistency and accuracy of the data transformations. 

  • Publishing and Sharing Reports: 

    • Once reports and dashboards are created, they can be published to the Power BI service for sharing with other users. 

    • Ensure that access to published reports is managed according to the security policies defined in Sharperlight. 

  • Maintaining and Updating Reports: 

    • Regularly update the Sharperlight Workbench data

    models

    • model and Power BI reports to reflect any changes in the source systems or business requirements. 

    • Monitor the performance of the reports and make adjustments to the Sharperlight queries as needed. 

Best

...

Practices 

  • Regularly Review Security Settings: Ensure that all data endpoints and transformations adhere to the latest security protocols. 

  • Optimize Data Models: Regularly review and optimize Sharperlight data models for performance and accuracy. 

  • Training and Support: Provide ongoing training and support to users creating reports in Power BI to ensure they are fully equipped to use the tools effectively. 

Integrating Power BI with Sharperlight at Workbench enables a powerful, secure, and efficient way to handle custom reports. By following these guidelines, users can leverage the full capabilities of both platforms while ensuring data security and system performance. 

Info

Integrating Power BI with Sharperlight at Workbench enables a powerful, secure, and efficient way to handle custom reports. By following these guidelines, users will be able to leverage the full capabilities of both platforms while ensuring data security and system performance. 

Panel
panelIconId1f4a1
panelIcon:bulb:
panelIconText💡
bgColor#DEEBFF

Additional Help: https://www.sharperlight.com/power-bi/2020/09/07/power-bi-custom-connector/