092 - Generating an AI Builder Consumption Report Using Power Automate Desktop

Hello and welcome to the channel! Today, we're going to focus on creating AI Builder consumption reports using Power Automate Desktop. Let's dive in!

Why This is Important

The Power Platform Admin Center provides a way to download AI Builder consumption reports. However, there is no public API available for this. As a result, we must manually click a download link to retrieve this data. To avoid this manual task and automate the generation of these reports, we can use Power Automate Desktop to click the download link. After downloading the data in CSV format, we will use Power Automate Desktop to convert the CSV file into XLSX (Excel's native format).

Once converted, we can create a data table on top of the file, which can then be ingested inside of Power Automate Cloud flows. From there, we can loop through the table, extract the data, and insert the records into Microsoft Dataverse. This data can later be used for building reports in Power BI.

Architecture

The architecture involves the following steps:

Power Platform Admin Center:
- Download AI Builder report manually.
- Click on the "Environments" page, navigate to "Resources" > "Capacity".
- Scroll down to "Download Reports" and click "New".
- Select the report type and generate a new report.
- Refresh the table to retrieve the latest report and download it.
Power Automate Desktop Script:
- Launch Chrome and navigate to the Admin Center.
- Record the manual steps for navigation, generating, and downloading the report.
- Retrieve the list of files from the "Downloads" folder, sort by the last modified file.
- Launch Excel, convert the downloaded CSV into an XLSX file, and save it to OneDrive.
- Use Power Automate Cloud flow to list rows present in the table and insert them into Microsoft Dataverse.
Cloud Flow:
- Set up a recurring job to run the automation every month or week.
- Orchestrate the flow by calling out to Power Automate Desktop and introducing a delay to allow OneDrive to sync the file to the cloud.
- Retrieve file metadata and list rows present in the table, then loop through the data inserting each row into Dataverse.

Demo

Manual Download Process in the Admin Center

Walkthrough:

Click on Environments and navigate to Resources > Capacity.
Scroll to and click on "Download reports".
Generate a new report and click the download link for the generated CSV file.

Power Automate Desktop Script

Launch Chrome and navigate to the Admin Center.
Record navigation steps to generate and download the report.
Retrieve the latest file from the "Downloads" folder.
Launch Excel, create a table within Excel, and save it as an XLSX file to OneDrive.

Cloud Flow

Setup a recurring job to call Power Automate Desktop.
Introduce a delay for OneDrive synchronization.
Retrieve the file metadata and unique identifiers from the file in OneDrive.
List rows from the table and insert records into Dataverse.

Conclusion

Following this approach, you can successfully automate the generation, downloading, and processing of AI Builder consumption reports using Power Automate Desktop and Cloud Flows, allowing seamless data integration into Dataverse for further analysis.

Keywords

Power Automate Desktop
AI Builder Consumption
Dataverse
Cloud Flow
CSV to XLSX Conversion
Power Platform Admin Center
Excel Automation
OneDrive Sync

FAQ

Q1: Why is automating the AI Builder consumption report generation important?
A1: Automating the report generation eliminates manual tasks and ensures timely and accurate data retrieval for further processing and analysis.

Q2: What tools are required to automate the AI Builder consumption report generation?
A2: The tools required include Power Automate Desktop, Power Automate Cloud Flows, Microsoft Dataverse, and OneDrive.

Q3: How does Power Automate Desktop handle CSV to XLSX conversion?
A3: Power Automate Desktop launches Excel, creates a data table from the CSV, and saves the file as XLSX in OneDrive.

Q4: What is the role of delays in the automation process?
A4: Delays ensure that OneDrive has sufficient time to sync files between the local machine and the cloud, ensuring the accuracy of subsequent operations.

Q5: How is the latest downloaded report identified in the script?
A5: The script sorts files in the "Downloads" folder by the last modified date and retrieves the most recent file matching the specified pattern.

Q6: Can the same automation be applied to other report types from the Admin Center?
A6: Yes, the process can be adapted for other report types like portal logons or page views, assuming their download and format mechanism are similar.