Overview
This guide provides comprehensive instructions for implementing a dynamic, API-driven Power BI reporting solution for tenant consumption tracking. The new template replaces static reporting methods with a flexible, dynamic, and real-time data retrieval system.
Key Benefits
Automatic Data Refresh: Automatic API-based data refresh
Simplified Reporting: Eliminates manual data updates
Accurate Billing: Consistent and up-to-date consumption information
Scalable Integration: Configurable reporting template
System Requirements
Microsoft 365 Power BI Desktop application
Microsoft 365 Power BI Service with either Power BI Pro or Power BI Premium license
Active Druva MSC API credentials
Note: A Pro or Premium Power BI Service license is essential for sharing and collaborating on published reports.
Prerequisites
1. Obtain API Credentials
Work with your Managed Service Provider (MSP) administrator to get the Authentication Credentials. Moreover, you can use the following details while invoking the tenant consumption API.
Tenant Consumption API Endpoint URL: msp/reporting/v1/reports/consumptionItemized
Access Token Endpoint:
msp/auth/v1/token
2. Download Report Template
Download the latest MSP PowerBI Tenant Consumption eExchange document.
Configuration Procedure
The Power BI Desktop extracts the data from various data sources and creates the reports. The downloaded Power BI document (MSP PowerBI Tenant Consumption eExchange) already contains the power query functions; using these you can extract the data to your dashboard.
This process involves two major steps:
Set the automatic refresh setting in the Power BI service. The report data is updated automatically based on the refresh frequency you set.
Step 1: Configure the report template on the Power BI Desktop
Retrieve Initial Data
Open the 1.0.0.pbix document (MSP PowerBI Tenant Consumption eExchange) in the Power BI Desktop.
Select Transform data on the Home ribbon to open Power Query Editor.
Configure Data Range
You can specify the date and time range for initial data retrieval.
RangeStart: Specify the start date and time
RangeEnd: Specify the end date and time
Recommended Setting: Initial retrieval of up to 1 year of historical data.
Now that you have defined the RangeStart and RangeEnd parameters, it will filter the data to be loaded into the model based on those parameters.
Authentication and Data Fetch
In the Enter Contract Information query function:
Provide required contract details
Click Invoke to generate the associated information table
In the Get Tenant Consumption Data query function:
Enter authentication parameters:
Client ID
Client Secret
Access Token Endpoint: msp/auth/v1/token
Tenant Consumption API Endpoint: msp/reporting/v1/reports/consumptionItemized
Click Invoke to retrieve tenant consumption data
When you invoke the Get Tenant Consumption Data query function, Invoked Function (2) is created. This will create a table with data from the tenant consumption API within the RangeStart and RangeEnd parameters you provided. You can apply a date range filter to this Invoked Function (2).
Note:
Once the Power Query function is evoked, the client credentials and API endpoint URLs get removed.
The client credentials are only visible in the first step to the MSP administrator who invokes the function (2) in the Power BI desktop. They are not accessible and visible to anyone on the Power BI Service.
Customize Date Range Filter
Apply a custom date filter to the RangeStart and RangeEnd parameters in the Invoked Function (2) table. This filter is required before setting up an incremental refresh policy on the table.
Procedure
In Power Query Editor, select the date column you want to filter on, and then choose the dropdown arrow > Date/ Time Filters > Custom Filter.
In Filter Rows, specify the parameter conditions.
Set the first condition:
RangeStart - is after or equal to the desired start date.
Add a second condition:
RangeEnd - is before the desired end date.
Click OK to close the window.
On the Home ribbon in Power Query Editor, select Close & Apply. Power Query loads data based on the filters defined by the RangeStart and RangeEnd parameters.
Key Objectives of Date Range Filtering:
Mandatory step before configuring incremental refresh policy.
Define precise data retrieval boundaries.
Ensure only relevant time-period data is included.
Inform the Power BI service when the data should be incrementally refreshed.
Configure Incremental Refresh Policy
Once you set the date range filters, you can configure the incremental refresh policy. This policy is activated only after the package is published to the Power BI service, and a manual or automatic refresh setting is enabled.
Procedure
In the Data view, right-click the Invoked Function (2) table in the Data pane and select Incremental refresh.
In Incremental refresh and real-time data, verify that the Invoked Function (2) table is selected.
In Set import and refresh ranges, turn on the Incrementally refresh this table slider.
Set parameters following parameters:
Archive data: 1 Year.
Incremental refresh: 1 Day.
Select Apply to complete the refresh policy.
Recommended Settings
When you set the incremental refresh policy for your Power BI dashboard, the system fetches and updates the data based on the period an administrator set for archival data and incremental refresh. During the first dashboard refresh, it will load all historical data between the RangeStart and RangeEnd defined for the data retrieval.
Post this initial load, subsequent refreshes will exclusively update the data based on the incremental refresh period set by the administrator, while preserving the earlier retrieved data. This approach ensures that older data remains archived and accessible up to the archive data parameter set in the incremental refresh policy.
Let's understand how the incremental refresh policy works. This setting determines the time frame for which new data is fetched during each refresh cycle. For instance, if you set the period to 1 day, the system will only retrieve data for the past 1 day during each refresh. This significantly reduces the amount of data processed, improving performance, reducing data transfer and storage costs, and ensuring your reports always display the most up-to-date information.
Assume that you have specified a RangeStart and RangeEnd date to define the initial data load, and set an incremental refresh policy to one day with an archival period of one year. For example, this would be 01-01-2023 to 01-01-2024.
On each subsequent refresh, Power BI automatically adjusts the RangeStart and RangeEnd dates. For instance, the next day's refresh would have RangeStart as 02-01-2024 and RangeEnd as 02-01-2024 and retrieve the data for that day only. This ensures that only the new data is loaded, improving performance and data freshness.
Archive Data Period: 1 year
Refresh Frequency: Daily incremental updates
Data Retention: Automatic management of data older than 12 months
Step 2: Publishing to Power BI Service
Power BI Desktop packages your report and data, including visualizations, queries, and custom measures, which you upload to the Power BI service.
Click the Publish button on the Home ribbon.
Select the target workspace and save the changes.
Open the Power BI service, when your upload is completed.
Configure automatic refresh frequency in Power BI Service settings.
Detail Overview of the MSP BI tool dashboard
The Power BI dashboard is designed to provide comprehensive insights into tenant consumption and contract performance. It offers a multi-dimensional view of your tenant landscape, empowering you to make data-driven decisions and optimize your resource allocation.
Contract Overview Tab
Provides a comprehensive overview of contract details, including start and end dates, remaining contract duration, and monthly consumption trends. In this tab, you can:
Visualize contract timelines, remaining terms, and consumption trends.
Monitor consumed and remaining CUs.
Track tier discounts and their impact on overall costs.
Key Terms and Definitions
Term | Description |
Start Date | The beginning date of the contract. |
End Date | The final date of the contract. |
Starting Balance | The initial number of Consumption Units (CUs) allocated to the contract. |
Balance Remaining | The current number of CUs left to be consumed. |
Projected term remaining | An estimate of the remaining months and days until the current CU balance is depleted, based on the current consumption rate. |
CU Rate | A multiplier applied to the consumption cost, providing a discount based on the MSP's tier level. |
Note: The months until the consumed field will be blanked if the remaining CU balance is negative.
Tenant Overview Tab
This tab provides granular tenant-level reporting with date range and customer-specific filtering. It provides a holistic view of customer-specific CU consumed and CU cost at tier ($).
In this tab, you can get:
User and Resource Summary
Microsoft 365 statistic
Google user statistic
Endpoint user statistics
Total storage consumption (TB)
Tier-based cost analysis
Hybrid Workload Detailed Reporting
Customer identification
Service edition details
Long-term retention (LTR) metrics
Consumption Unit cost
Consumption Unit utilization
SaaS Application Insights
Customer-specific breakdown
License editions
Active user counts
Preserved user statistics
Comprehensive product-level analytics
Key Terms and Definitions
Term | Description |
CU Consumed | The number of Consumption Units (CUs) that have been used or consumed so far within the contract period. |
Cost at Tier ($) | The total cost of the consumed CUs, taking into account the specific tier level and any associated discounts. |
Total No of TB | The size of total storage consumed in terabytes(TB). |
Workload Chart Tab
Provides a visual representation of user distribution across various workloads, enabling easy comparison and analysis.
In this tab, you can:
Visualize the distribution of protected users across different workloads.
Identify trends and patterns in workload usage.
Optimize resource allocation based on workload requirements.
MSP BI Chat Tab
This is an inbuilt chat option available on the MSP BI where MSPs can ask or select a question in this tab to get details from the Power BI tool. The result will appear in the MSP BI Chat tab based on the question.
You select the chat icon on the top-left corner of the dashboard to choose the system-generated questions as well. In this tab, you can:
Interact with the dashboard.
Ask questions about consumption, costs, and performance metrics.
Receive immediate answers and visualizations.
This multi-tab approach ensures administrators can quickly access detailed insights, track consumption, and make informed decisions about their tenant resources and contract performance.