We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →Author and Share Power BI Reports on Real-Time PingOne Data
Use the CData ODBC Driver for PingOne to visualize PingOne data in Power BI Desktop and then upload to the Power BI service.
With built-in support for ODBC on Microsoft Windows, CData ODBC Drivers provide self-service integration with self-service analytics tools such as Microsoft Power BI. The CData ODBC Driver for PingOne links your Power BI reports to operational PingOne data. You can monitor PingOne data through dashboards and ensure that your analysis reflects PingOne data in real time by scheduling refreshes or refreshing on demand. This article details how to use the ODBC driver to create real-time visualizations of PingOne data in Microsoft Power BI Desktop and then upload to Power BI.
The CData ODBC Drivers offer unmatched performance for interacting with live PingOne data in Power BI due to optimized data processing built into the driver. When you issue complex SQL queries from Power BI to PingOne, the driver pushes supported SQL operations, like filters and aggregations, directly to PingOne and utilizes the embedded SQL Engine to process unsupported operations (often SQL functions and JOIN operations) client-side. With built-in dynamic metadata querying, you can visualize and analyze PingOne data using native Power BI data types.
Connect to PingOne as an ODBC Data Source
If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.
To connect to PingOne, configure these properties:
- Region: The region where the data for your PingOne organization is being hosted.
- AuthScheme: The type of authentication to use when connecting to PingOne.
- Either WorkerAppEnvironmentId (required when using the default PingOne domain) or AuthorizationServerURL, configured as described below.
Configuring WorkerAppEnvironmentId
WorkerAppEnvironmentId is the ID of the PingOne environment in which your Worker application resides. This parameter is used only when the environment is using the default PingOne domain (auth.pingone). It is configured after you have created the custom OAuth application you will use to authenticate to PingOne, as described in Creating a Custom OAuth Application in the Help documentation.
First, find the value for this property:
- From the home page of your PingOne organization, move to the navigation sidebar and click Environments.
- Find the environment in which you have created your custom OAuth/Worker application (usually Administrators), and click Manage Environment. The environment's home page displays.
- In the environment's home page navigation sidebar, click Applications.
- Find your OAuth or Worker application details in the list.
-
Copy the value in the Environment ID field.
It should look similar to:
WorkerAppEnvironmentId='11e96fc7-aa4d-4a60-8196-9acf91424eca'
Now set WorkerAppEnvironmentId to the value of the Environment ID field.
Configuring AuthorizationServerURL
AuthorizationServerURL is the base URL of the PingOne authorization server for the environment where your application is located. This property is only used when you have set up a custom domain for the environment, as described in the PingOne platform API documentation. See Custom Domains.
Authenticating to PingOne with OAuth
PingOne supports both OAuth and OAuthClient authentication. In addition to performing the configuration steps described above, there are two more steps to complete to support OAuth or OAuthCliet authentication:
- Create and configure a custom OAuth application, as described in Creating a Custom OAuth Application in the Help documentation.
- To ensure that the driver can access the entities in Data Model, confirm that you have configured the correct roles for the admin user/worker application you will be using, as described in Administrator Roles in the Help documentation.
- Set the appropriate properties for the authscheme and authflow of your choice, as described in the following subsections.
OAuth (Authorization Code grant)
Set AuthScheme to OAuth.
Desktop Applications
Get and Refresh the OAuth Access Token
After setting the following, you are ready to connect:
- InitiateOAuth: GETANDREFRESH. To avoid the need to repeat the OAuth exchange and manually setting the OAuthAccessToken each time you connect, use InitiateOAuth.
- OAuthClientId: The Client ID you obtained when you created your custom OAuth application.
- OAuthClientSecret: The Client Secret you obtained when you created your custom OAuth application.
- CallbackURL: The redirect URI you defined when you registered your custom OAuth application. For example: https://localhost:3333
When you connect, the driver opens PingOne's OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver then completes the OAuth process:
- The driver obtains an access token from PingOne and uses it to request data.
- The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.
The driver refreshes the access token automatically when it expires.
For other OAuth methods, including Web Applications, Headless Machines, or Client Credentials Grant, refer to the Help documentation.
After creating a DSN, follow the steps below to connect to the PingOne DSN from Power BI Desktop:
- Open Power BI Desktop and click Get Data -> ODBC. To start Power BI Desktop from PowerBI.com, click the download button and then click Power BI Desktop.
- Select the DSN in the menu. If you know the SQL query you want to use to import, expand the Advanced Options node and enter the query in the SQL Statement box.
- Select tables in the Navigator dialog.
Click Edit to edit the query. The table you imported is displayed in the Query Editor. In the Query Editor, you can enrich your local copy of PingOne data with other data sources, pivot PingOne columns, and more. Power BI detects each column's data type from the PingOne metadata retrieved by the driver.
Power BI records your modifications to the query in the Applied Steps section, adjusting the underlying data retrieval query that is executed to the remote PingOne data. When you click Close and Apply, Power BI executes the data retrieval query.
Otherwise, click Load to pull the data into Power BI.
Create Data Visualizations
After pulling the data into Power BI, you can create data visualizations in the Report view by dragging fields from the Fields pane onto the canvas. Follow the steps below to create a pie chart:
- Select the pie chart icon in the Visualizations pane.
- Select a dimension in the Fields pane: for example, Id.
- Select a measure in the Username in the Fields pane: for example, Username.

You can change sort options by clicking the ellipsis (...) button for the chart. Options to select the sort column and change the sort order are displayed.
You can use both highlighting and filtering to focus on data. Filtering removes unfocused data from visualizations; highlighting dims unfocused data. You can highlight fields by clicking them:

You can apply filters at the page level, at the report level, or to a single visualization by dragging fields onto the Filters pane. To filter on the field's value, select one of the values that are displayed in the Filters pane.

Click Refresh to synchronize your report with any changes to the data.
Upload PingOne Data Reports to Power BI
You can share reports based on ODBC data sources with other Power BI users in your organization. To upload a dashboard or report, log into PowerBI.com, click Get Data -> Files, and navigate to a Power BI Desktop file or Excel workbook. You can then select the report in the Reports section.
Refresh on Schedule and on Demand
You can use the Power BI Personal Gateway to automatically refresh the dataset associated with your report. You can also refresh the dataset on demand in Power BI. After installing the Personal Gateway, follow the steps below to schedule refreshes for an ODBC DSN:
- Log into Power BI.
- In the Dataset section, right-click the PingOne Dataset.
- Click Schedule Refresh.
- In the settings for your dataset, expand the Data Source Credentials node and click Edit Credentials in the ODBC section.
- Expand the Schedule Refresh section, select Yes in the Keep Your Data Up to Date menu, and specify the refresh interval.
You can now share real-time PingOne reports through Power BI.
