This article describes how to write job results directly into your Microsoft Power BI datasets.
- Basic knowledge of Arm Treasure Data, including the toolbelt.
- A Microsoft Power BI account as an organizational user for Azure Active Directory tenant who can grant permissions to Treasure Data.
Choose saved connection
Choose Saved Connection will be displayed. You will need to select an existing power_bi connection (see image below). If you do not have a Saved Connection already setup, follow the steps on how to create a new connection.
Create a new connection
Visit Treasure Data Connections search and select Power BI. The dialog opens:
Select an existing OAuth connection for Power BI, or click the link under OAuth connection to create a new one.
Create a new OAuth connection
Sign into your Power BI account from popup window:
You will get a list of permissions that you need to grant access to. Click on the
You will be redirected back to Treasure Data Connections. Repeat the first step (Create a new connection) and choose your new OAuth connection. Finish creating the connection.
From here, you will be able to use the new power_bi connection in Choose Saved Connection.
After selecting a power_bi saved connection or creating the new one, you must complete the fields Dataset and Table to create a new Power BI dataset and table, or to update an existed one.
Service plan rows limit: Max rows added per hour per dataset for service plans. Free service plan: 10,000 rows. Paid service plan: 1,000,000 rows. References: Power BI Rest API Limitations
Write the query
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 (for the ease of testing out this feature), but you still must choose your database, so pick “sample_datasets” or any other arbitrary table. Also, make sure that Presto is chosen as the SQL dialect.
The query should complete in a few seconds. After completion, check the dataset in PowerBI’s dashboard and verify that data was exported:
Execute the query
Finally, either save the query by entering a name for the query and run, or just run the query. Upon successful completion of the query, the query result will be automatically imported to the specified database destination.