Views:

If you have Power BI Desktop from the Microsoft Store, you can install Luminesce Power BI Connector on a Windows computer to query Luminesce providers directly.

To do this, you require:

Note: If you have a different BI app on a Windows computer, including non-Desktop or non-Microsoft Store versions of Power BI, start with this article.

Installing the Luminesce Power BI Connector

  1. The first step is to to install the Luminesce ODBC driver, on which the Luminesce Power BI Connector has a dependency.

    As a LUSID user with the lusid-administrator role, navigate to https://<your-domain>.lusid.com/honeycomb/swagger/index.html, where <your-domain> is the root of your LUSID domain, for example acmecorp. If you do not have the lusid-adminstrator role, see the appendix. 

  2. Use Swagger's Try it out button for the DownloadBinary API to request the Odbc_Win64_Driver binary:

  3. Click the Download file link in the response to download the binary to your browser's download location:

  4. Run the following command: 

    msiexec /i Finbourne.Luminesce.ODBCWin64.<version>.msi
  5. When the Luminesce ODBC Drivers Setup Wizard appears, select Next on each prompt and then select Finish.

  6. Repeat step 1 to navigate to Swagger. Use the Try it out button for the DownloadBinary API to request the PowerBi_Connector binary:

  7. Click the Download file link in the response to download the binary to your browser's download location:

  8. Run the following command:

    msiexec /i Finbourne.Luminesce.PowerBIConnector.<version>.msi
  9. When the Luminesce Power BI Connector Setup Wizard appears, select Next on each prompt and select Typical when prompted to Choose Setup Type.
    Note: If you are installing the connector for another user, contact us for advice.
  10. Select Install when prompted. Once the installation is complete, select Finish.
  11. Open Power BI and confirm Luminesce (Beta) and Luminesce SQL Query (Beta) now appear as data sources. This can be found in Power BI through Get Data > More... > Online Services.
 

Updating to the latest version of the Luminesce Power BI Connector

You can update to the latest version of the Luminesce Power BI Connector and driver by obtaining the latest version of the corresponding installer and following the above installation instructions.

Version 1.x onwards introduces support for query folding, enabling the use of native query with DirectQuery data connectivity mode. If updating the connector from version 0.2.x to version 1.x and above, you must perform the following actions:

  1. Update the Luminesce ODBC driver to version 1.2.652 or above.
  2. Update the Luminesce Power BI Connector to version 1.x or above. This can be done in two ways:
    • Install the latest version of the connector and follow the instructions in the installation wizard.
    • Locate the Power BI Desktop\Custom Connectors folder on your device and replace the existing .pqx file with the Luminesce-<version>.pqx file from the latest version of the connector.
  3. In Power BI, two new data sources - Luminesce (Beta) and Luminesce SQL Query (Beta) - should appear. To migrate your existing reports to the new data sources, go to Power Query Editor > Advanced Editor for your query and update the function names as follows:
    • LuminesceODBC.Query must be replaced with Luminesce.Sql.
    • LuminesceODBC.Connection must be replaced with Luminesce.Platform. Additionally, your query must be appended with {[Name="Luminesce",Kind="Database"]}[Data], for example: 
      Source = Luminesce.Platform("demosetup", [CommandTimeout=null, UseMetadataCache=true, UseQueryCache=null, QueryCacheDuration=null]){[Name="Luminesce",Kind="Database"]}[Data],

See the following table for more information on the four data sources you can now see in Power BI Desktop:

Data source nameLuminesce Power BI Connector versionDescriptionFunction name
Luminesce (Beta)1.x or aboveConnect live or import data from Luminesce. Full connector generation of SQL, or the use of a user-specified single select statement to act as a base query, are supported.Luminesce.Platform
Luminesce SQL Query (Beta)Import data from Luminesce using a Luminesce SQL syntax native query.Luminesce.Sql
Luminesce (Beta)0.2.xConnect to LuminesceLuminesceODBC.Connection
Luminesce Query (Beta)Run a Luminesce native queryLuminesceODBC.Query

You can remove the version 0.2.x data sources from Power BI Desktop after migrating any existing reports. To do so, locate the Power BI Desktop\Custom Connectors folder on your device and delete the LuminesceODBC-0.2.<x>.pqx file.

Using Power BI Desktop to query a Luminesce provider

You can interact with a provider in one of two ways:

Connecting live or import data to Luminesce 

The Luminesce data source enables you to connect to your data in either Import or DirectQuery mode. You can specify a Luminesce SQL native query to be run on connection, or use the navigator to explore the data source and select, load and transform data. This method supports query folding, which can greatly improve the efficiency of your requests. See more on query folding.

To use this data source:

  1. In Power BI, navigate to File > Options and settings > Options > Security and ensure the Require user approval for new native database queries checkbox is NOT ticked.
  2. Select Luminesce (Beta) and click Connect in Power BI.
  3. Enter your LUSID domain. For example, if your domain is set up for https://acme.lusid.com/app/home, enter acme here.
  4. Optionally, enter a Luminesce native query to be run on connection. We recommend selecting the smallest amount of data you need at this point to make your query and any subsequent data refreshes more efficient. Read more on query folding.
  5. Select your preferred Data Connectivity mode: 
    • Import imports selected tables and columns into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data. To see underlying data changes since the initial import or the most recent refresh, you must refresh the data, which imports the full dataset again.
    • DirectQuery queries the underlying data source as you create or interact with a visualization, so you’re always viewing current data. With this mode, no data is imported or stored in Power BI Desktop.
  6. Choose an authentication method:
    • OAuth2 PKCE (recommended): Power BI requests a username/password and issues any MFA challenge when you click the Sign in button. This is a secure approach for individual users using Power BI on their own desktop.
    • Personal Access Token (PAT): Supply a token generated in the LUSID website for the domain which is held in the encrypted Power BI credentials store. Credentials set up in this way expire on expiry of the PAT (if set).
    • DSNs (not recommended for Power BI Desktop use): DSNs can be used for authentication, however this method is more complex and requires configuring your ODBC for service authentication. You also need to supply your Domain name as dsn=<your_dsn> for this authentication method. 
  7. Once signed in, click Connect. You can then choose to Load or Transform your data and begin building visualizations.
 

Importing data from Luminesce using a Luminesce SQL native query

The Luminesce SQL Query data source enables you to specify a Luminesce SQL native query to be run on connection which immediately generates a table. All data sources created against Luminesce SQL Query are Import and considered native database queries.

To use this data source:

  1. In Power BI, navigate to File > Options and settings > Options > Security and ensure the Require user approval for new native database queries checkbox is NOT ticked.
  2. Select Luminesce SQL Query (Beta) and click Connect in Power BI.
  3. Enter your LUSID domain. For example, if your domain is set up for https://acme.lusid.com/app/home, enter acme here.
  4. Enter a Luminesce SQL query to be run on connection under Native query and click OK.
  5. Choose an authentication method:
    • OAuth2 PKCE (recommended): Power BI requests a username/password and issues any MFA challenge when you click the Sign in button. This is a secure approach for individual users using Power BI on their own desktop.
    • Personal Access Token (PAT): Supply a token generated in the LUSID website for the domain which is held in the encrypted Power BI credentials store. Credentials set up in this way expire on expiry of the PAT (if set).
    • DSNs (not recommended for Power BI Desktop use): DSNs can be used for authentication, however this method is more complex and requires configuring your ODBC for service authentication. You also need to supply your Domain name as dsn=<your_dsn> for this authentication method.
  6. Once signed in, click Connect. A table for your query is immediately generated and you can choose to load or transform your imported data.
 

Accessing logs

By default, Luminesce ODBC driver logs are written to C:\Windows\Temp\FinbourneLuminesce\ODBC64Logs. You can adjust the base directory (C:\Windows\Temp\FinbourneLuminesce) using the LOGPATH argument when running the MSI.

Luminesce Power BI Connector logs can be found in the normal Power BI logs location: File > Options and Settings > Options > Diagnostics > Enable tracing > Open crash dump/traces folder

Uninstalling

You can uninstall the driver and connector using Add or remove programs in the usual way, for example:

Appendix A: Allowing non-administrative users to download binaries

Once a license is granted, users with the lusid-administrator role have permission to download the PowerBi_Connector and Odbc_Win64_Driver binaries out-of-the-box.

To enable less privileged users to download, create a feature policy consisting of the following JSON, assign it to a role, and the role to their LUSID user:

{
    "description": "User can download Power BI Connector and Windows ODBC Driver binaries",
    "applications": [
        "Honeycomb"
    ],
    "grant": "Allow",
    "selectors": [
        {
            "idSelectorDefinition": {
                "identifier": {
                    "code": "PowerBi_Connector",
                    "scope": "Honeycomb"
                },
                "actions": [
                    {
                        "scope": "Honeycomb",
                        "activity": "DownloadBinary",
                        "entity": "Feature"
                    }
                ],
                "name": "Power BI connector binary",
                "description": "User can download Power BI Connector binary"
            }
        },
        {
             "idSelectorDefinition": {
               "identifier": {
                    "code": "Odbc_Win64_Driver",
                    "scope": "Honeycomb"
                },
                "actions": [
                    {
                        "scope": "Honeycomb",
                        "activity": "DownloadBinary",
                        "entity": "Feature"
                    }
                ],
                "name": "Windows ODBC driver binary",
                "description": "User can download Windows ODBC driver binary"
            }
         }
    ],
    "when": {
        "activate": "2023-01-01T00:00:00.0000000+00:00",
        "deactivate": "9999-12-31T23:59:59.9999999+00:00"
    }
}
 
Keywords: powerbi, power bi, odbc, luminesce