Power BI is an analytics tool used to analyze data and share insights. You can write job results directly into your Microsoft Power BI datasets. 

Limitations

Limitations are controlled by Microsoft and are subject to change. See Power BI Rest API Limitations.

  • 75 max columns

  • 75 max tables

  • 10,000 max rows per single POST rows request

  • 1,000,000 rows added per hour per dataset

  • 5 max pending POST rows requests per dataset

  • 120 POST rows requests per minute per dataset

  • If a table has 250,000 or more rows, 120 POST rows requests per hour per dataset

  • 200,000 max rows stored per table in FIFO dataset

  • 5,000,000 max rows stored per table in ‘none retention policy’ dataset

  • 4,000 characters per value for string column in POST rows operation

  • Only one table can be exported into one dataset.

Review the following topics.

Prerequisites

Creating the Export Dataset

  1. Open TD Console.

  2. Navigate to the Data Workbench > Queries.

  3. Select New Query.

  4. Select the database for which you would like to export data.

  5. Type your query into the query editor.

  6. Select Output Results.

    A dialog Choose Saved Connection is displayed.

  7. Select an existing power_bi connection.
    If you do not have a Saved Connection already set up, see Creating a New Connection During Data Export.


  8. Specify the export details. Dataset and table are required.

  9. Optionally, type a value for Workspace ID.
    The Workspace ID is a PowerBI workspace or group id that allows users to share their work.

  10. Optionally, choose whether to replace existing data or not.
    If chosen, TD deletes all the data before replacing it with fresh data during the export.



  11. Review the limits for your plan and type a value for your Service plan rows limit.
    Max rows added per hour per dataset for service plans. Free service plan allows 10,000 rows. Paid service plan allows 1,000,000 rows.

  12. Select Done.

Creating a New Connection During Data Export


  1. From the Choose Integration screen, select New integration.


  2. Select Microsoft Power BI.



  3. Select Catalog.

  4. Search and select Power BI. The dialog opens:


  5. Select the link under OAuth connection to create a new one. Or you can select an existing connection and skip the rest of this section.

  6. Sign in to your Power BI account from popup window:

    You will get a list of permissions that you need to grant access to.

  7. Select Accept.

    You are redirected back to Treasure Data Connections.

  8. Select the OAuth connection that you just created.


  9. Name and finish creating the connection.

You can use the power_bi connection in Sources.





Example Query and Export

Here is an example of a Power BI connection with an exporting column that has the timestamp format on Treasure Data. You would run the following query with PowerBI as Result Export:

SELECT Category, Name, parse_datetime(created_date, 'yyyy-MM-DD HH:mm:ss.SSS') as ManufacturedOn, isCompete, ProductID FROM (
  VALUES ('Cars', 'Suzuki', '2015-01-16 07:15:33.123', true, 15),
         ('Bikes', 'Silver', '2016-05-20 13:52:44.673', false, 17),
         ('Cars', 'Toyota', '2017-09-27 22:32:34.456', false, 20)
)  tbl (Category, Name, created_date, isCompete, ProductID)

This example query requires no source table.

You must choose your database, pick “sample_datasets”.

Pick Presto as the query engine.

The query should complete in a few seconds.

After the successful completion of the query, the query result is automatically imported to the specified database destination.

Check the dataset in PowerBI’s dashboard and verify that data was exported:




  • No labels