ODBC guide

Reading data in an ODBC application

In this section we will learn how to configure and use Power BI Service Connector in the API Driver to extract data from the Power BI Service.

Create data source using Power BI Service ODBC Driver

Video instructions

Watch this quick walkthrough to see how to configure your Power BI Service ODBC data source, or scroll down for the step-by-step written guide.

While this video uses the OData Connector as an example, the core concepts and setup process are exactly the same for the Power BI Service Connector.

Video doesn't load? Watch this video on YouTube.

Step-by-step instructions

To get data from Power BI Service using , we first need to create an ODBC data source. We will later read this data in . Perform these steps:

  1. Download and install ODBC PowerPack (if you haven't already).

  2. Search for odbc and open the ODBC Data Sources (64-bit):

    Open ODBC Data Source

  3. Create a User data source (User DSN) based on the ZappySys API Driver driver:

    ZappySys API Driver
    Create new User DSN for ZappySys API Driver
    • Create and use a User DSN if the client application runs under a User Account. This is the ideal option at design time (e.g., when developing in Visual Studio). Use it for both types of applications (64-bit and 32-bit).
    • Create and use a System DSN if the client application runs under a System Account (e.g., as a Windows Service). This is usually the required option in a production environment. If your Windows Service is a 32-bit application, you must use the 32-bit ODBC Data Source Administrator to configure this

  4. When the Configuration window appears give your data source a name if you haven't done that already, then select "Power BI Service" from the list of Popular Connectors. If "Power BI Service" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

    PowerBiServiceDSN
    Power BI Service
    ODBC DSN Template Selection

  5. Select your authentication scenario below to expand connection configuration steps to:

    • Configure the authentication in Power BI Service.
    • Enter those details into the ZappySys API Driver data source configuration.
    User Credentials

    Power BI Service authentication

    Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]

    Follow these simple steps below to create Microsoft Entra ID application with delegated access:

    WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g. automation@my-company.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.
    1. Navigate to the Azure Portal and log in using your credentials.
    2. Access Microsoft Entra ID.

    3. Register a new application by going to App registrations and clicking on New registration button:

      Start new app registration in Microsoft Entra ID
      INFO: Find more information on how to register an application in Graph API reference.

    4. When configuration window opens, configure these fields:

      • Supported account type
        • Use Accounts in this organizational directory only, if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Use https://zappysys.com/oauth as the URL.
      Register app in Microsoft Entra ID

    5. After registering the app, copy the Application (client) ID for later:

      Copy client ID of Microsoft Entra ID app

    6. Copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:

      Copy Auth and Token URLs in Microsoft Entra ID app

    7. Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:

      • In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
      • In the Scope field use the default value or select individual scopes, e.g.:
        • offline_access
        • https://analysis.windows.net/powerbi/api/Workspace.Read.All
        • https://analysis.windows.net/powerbi/api/Dataset.Read.All
        • https://analysis.windows.net/powerbi/api/Dataset.ReadWrite.All
        Admin consent note:
        • Token generation can still ask for admin approval, even when delegated scopes are listed in API permissions.
        • A Global Administrator can allow user consent for selected low-impact delegated permissions.
        • If tenant policy blocks user consent, an administrator must approve the requested delegated permissions.
        Settings: User consent settings (Entra ID > Enterprise apps > Consent and permissions > User consent settings). Docs: configure user consent, recommended settings.
    8. Press Generate Token button to generate Access and Refresh Tokens.
    9. Click Test Connection to confirm the connection is working.
    10. Optional step. Choose Default Workspace from the drop down menu.
    11. Choose Default Dataset from the drop down menu.
    12. Done! Now you are ready to use the API Connector!

    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to User Credentials [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Press Generate Token button to generate the tokens.
    5. Finally, hit OK button:
    PowerBiServiceDSN
    Power BI Service
    User Credentials [OAuth]
    https://api.powerbi.com/v1.0/myorg
    Required Parameters
    Authorization URL Fill-in the parameter...
    Token URL Fill-in the parameter...
    Application (client) ID Fill-in the parameter...
    Scope Fill-in the parameter...
    Default Dataset (select after generating tokens) Fill-in the parameter...
    Optional Parameters
    Client Secret
    Redirect URI (must match App Redirect URI)
    Default Workspace (Keep Empty for My Workspace - select after generating tokens)
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503
    RetryCountMax 20
    RetryWaitTimeMs 1000
    RetryMultiplyWaitTime True
    Login options
    ODBC DSN OAuth Connection Configuration
    Find full details in the Power BI Service Connector authentication reference.

  6. Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

    ZappySys API Driver - Power BI Service
    Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.
    PowerBiServiceDSN
    Open Query Builder in API ODBC Driver to read and write data to REST API

  7. Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in to retrieve data from Power BI Service. Hit OK button to use this query in the next step. 

    SELECT * 
FROM Workspaces
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
    Configure table/endpoint parameters in ODBC data source based on API Driver
    Some parameters configured in this window will be passed to the Power BI Service API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.

  8. Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in :

    ZappySys API Driver - Power BI Service
    Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.
    PowerBiServiceDSN
    SELECT * 
FROM Workspaces
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
    API ODBC Driver-based data source data preview
    You can also access data quickly from the tables dropdown by selecting <Select table>.
    A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the Power BI Service API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in Power BI Service servers).

  9. Click OK to finish creating the data source.

Reading Power BI Service data from client application

  1. Firstly, to get data from ODBC data source based on ZappySys ODBC driver, in your client application, you would need to connect to ODBC source and then from the list select the data source.

  2. Finally, to read the data just read tables/views in your app or enter a SQL statement to extract data, e.g.:

    SELECT * FROM Datasets

Power BI Service Connector Examples

The ZappySys API Driver is a user-friendly interface designed to facilitate the seamless integration of various applications with the Power BI Service API. With its intuitive design and robust functionality, the ZappySys API Driver simplifies the process of configuring specific API endpoints to efficiently read or write data from Power BI Service.

Click here to find more Power BI Service Connector examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

Consume Data inside your App / Programming Language

Once you know how to load data from Power BI Service Connector, you can click on one of the below links to learn the steps how to consume data inside your App / Programming Language from Power BI Service Connector.

ODBC inside ETL / Reporting / BI Tools

ODBC inside Programming Languages

Key features of the ZappySys API Driver include:

The API ODBC driver facilitates the reading and writing of data from numerous popular online services (refer to the complete list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.

  1. Intuitive Configuration: The interface is designed to be user-friendly, enabling users to easily set up the specific API endpoints within Power BI Service without requiring extensive technical expertise or programming knowledge.

  2. Customizable Endpoint Setup: Users can conveniently configure the API endpoint settings, including the HTTP request method, endpoint URL, and any necessary parameters, to precisely target the desired data within Power BI Service.

  3. Data Manipulation Capabilities: The ZappySys API Driver allows for seamless data retrieval and writing, enabling users to fetch data from Power BI Service and perform various data manipulation operations as needed, all through an intuitive and straightforward interface.

  4. Secure Authentication Integration: The driver provides secure authentication integration, allowing users to securely connect to the Power BI Service API by inputting the necessary authentication credentials, such as API tokens or other authentication keys.

  5. Error Handling Support: The interface is equipped with comprehensive error handling support, ensuring that any errors or exceptions encountered during the data retrieval or writing process are efficiently managed and appropriately communicated to users for prompt resolution.

  6. Data Visualization and Reporting: The ZappySys API Driver facilitates the seamless processing and presentation of the retrieved data from Power BI Service, enabling users to generate comprehensive reports and visualizations for further analysis and decision-making purposes.

Overall, the ZappySys API Driver serves as a powerful tool for streamlining the integration of applications with Power BI Service, providing users with a convenient and efficient way to access and manage data, all through a user-friendly and intuitive interface.