Import Zuora Data into the Power BI Service for Visualizations

Ready to get started?

Learn more or sign up for a free trial:

CData Connect Server


Use CData Connect Server to create an OData feed for Zuora and create custom reports in the Power BI Service.

Did You Know?

Zuora is supported in CData Connect Cloud! Our Data Connectivity as a Service provides a richer, cloud-to-cloud integration with your Zuora data.

Check out the updated integration guide here: Import Zuora Data into the Power BI Service for Visualizations


Power BI transforms your company's data into rich visuals for you to collect and organize so you can focus on what matters to you. When paired with CData Connect Server, you get access to Zuora data for visualizations, dashboards, and more. This article shows how to use the CData Connect Server to generate an OData feed for Zuora, import Zuora data into Power BI and then create reports on Zuora data in the Power BI service.

NOTE: You can also use the on-premise data gateway and the SQL interface in Connect Server to connect to Zuora data in real-time (instead of importing the data). Read how in the related Knowledge Base article.

Create a Virtual SQL Database for Zuora Data

CData Connect Server uses a straightforward, point-and-click interface to connect to data sources and generate APIs.

  1. Login to Connect Server and click Databases.
  2. Select "Zuora" from Available Data Sources.
  3. Enter the necessary authentication properties to connect to Zuora.

    Zuora uses the OAuth standard to authenticate users. See the online Help documentation for a full OAuth authentication guide.

    Configuring Tenant property

    In order to create a valid connection with the provider you need to choose one of the Tenant values (USProduction by default) which matches your account configuration. The following is a list with the available options:

    • USProduction: Requests sent to https://rest.zuora.com.
    • USAPISandbox: Requests sent to https://rest.apisandbox.zuora.com"
    • USPerformanceTest: Requests sent to https://rest.pt1.zuora.com"
    • EUProduction: Requests sent to https://rest.eu.zuora.com"
    • EUSandbox: Requests sent to https://rest.sandbox.eu.zuora.com"

    Selecting a Zuora Service

    Two Zuora services are available: Data Query and AQuA API. By default ZuoraService is set to AQuADataExport.

    DataQuery

    The Data Query feature enables you to export data from your Zuora tenant by performing asynchronous, read-only SQL queries. We recommend to use this service for quick lightweight SQL queries.

    Limitations
    • The maximum number of input records per table after filters have been applied: 1,000,000
    • The maximum number of output records: 100,000
    • The maximum number of simultaneous queries submitted for execution per tenant: 5
    • The maximum number of queued queries submitted for execution after reaching the limitation of simultaneous queries per tenant: 10
    • The maximum processing time for each query in hours: 1
    • The maximum size of memory allocated to each query in GB: 2
    • The maximum number of indices when using Index Join, in other words, the maximum number of records being returned by the left table based on the unique value used in the WHERE clause when using Index Join: 20,000

    AQuADataExport

    AQuA API export is designed to export all the records for all the objects ( tables ). AQuA query jobs have the following limitations:

    Limitations
    • If a query in an AQuA job is executed longer than 8 hours, this job will be killed automatically.
    • The killed AQuA job can be retried three times before returned as failed.
  4. Click Test Database.
  5. Click Privileges -> Add and add the new user (or an existing user) with the appropriate permissions.

Connecting to Connect Server from Power BI

To import and visualize your Zuora data in the Power BI service, add a Connect Server API user, add Zuora OData endpoints in Connect Server, and create & publish a dataset from Power BI Desktop to the service.

Add a Connect Server User

Create a User to connect to Zuora from Power BI through Connect Server.

  1. Click Users -> Add
  2. Configure a User.
  3. Click Save Changes and make note of the Authtoken for the new user.
  4. Click Database and select the Zuora virtual database.
  5. On the Privileges tab, add the newly created user (with at least SELECT permissions) and click Save Changes.

Add Zuora OData Endpoints in Connect Server

After connecting to Zuora, create OData Endpoints for the desired table(s).

  1. Click OData -> Tables -> Add Tables.
  2. Select the Zuora database.
  3. Select the table(s) you wish to work with and click Next.
  4. (Optional) Edit the table definition to select specific fields and more.
  5. Save the settings.

Publish a Dataset from Power BI Desktop

With OData endpoints added to Connect Server, you can create a dataset in Power BI descktop using OData connectivity and publish the dataset to the Power BI service.

  1. Open Power BI, click Get Data -> More, then select OData feed, and click Connect.
  2. Set URL to the Base URL of your Connect Server instance (e.g.: CONNECT_SERVER_URL/api.rsc) and click OK.
  3. Set User name and Password to user name and Authtoken from above.
  4. Select the full Base URL (CONNECT_SERVER_URL/api.rsc) as the level to apply the settings to/
  5. Select the table(s) to visualize in the Navigator dialog.
  6. Click Load to pull the data into Power BI.
  7. Define any relationships between the selected entities on the Relationships tab.
  8. Click Publish (from the Home menu) and select a Workspace.

Build Reports and Dashboards on Zuora Data in the Power BI Service

Now that you have published a dataset to the Power BI service, you can create new reports and dashboards based on the published data:

  1. Log in to PowerBI.com.
  2. Click Workspaces and select a workspace.
  3. Click Create and select Report.
  4. Select the published dataset for the report.
  5. Choose fields and visualizations to add to your report.

SQL Access to Zuora Data from Applications

Now you have a direct connection to live Zuora data from the Power BI service. You can create more data sources and new visualizations, build reports, and more — all without replicating Zuora data.

To get SQL data access to 200+ SaaS, Big Data, and NoSQL sources directly from your applications, see the CData Connect Server.