If your organization has provisioned Power BI Premium capacity then you’re probably already familiar with the Power BI Premium Capacity Metrics app for monitoring performance and resource utilization. In most scenarios, the interactive reports included in this app are sufficient to support the most common questions such as which datasets receive the most queries, which datasets consume the most resources (memory), and whether the provisioned resources are sufficient for the current workload.
However, you might also want to craft your own report against this same dataset with a tool such as Power BI Desktop, Power BI Report Builder, or Excel. A paginated report might be particularly useful given the export and distribution features associated with these reports such as parameterized subscriptions.
The Scenario
Let’s say you’re a Power BI service administrator with visibility to all premium capacities in your tenant. Let’s also say that you’ve allocated premium capacity across separate departments such that certain stakeholders are only interested in the activity for the premium capacity node(s) their department uses. Therefore, your goal is to create a single paginated report (.rdl) that can be filtered by premium capacity node and then setup subscriptions to the different teams/users with a parameter value reflecting the premium capacity they utilize.
Paginated Report Development
You’ll first need to download the Power BI Report Builder if it’s not already installed. If you have an older version, you should probably update to the latest version (File – Options – Updates – Check for Updates now). You should also make sure you’ve already installed the Power BI Metrics app as described in the documentation link provided earlier and can view the required premium capacities.
From a blank paginated report, right-click the Data Sources folder and select ‘Add Power BI Dataset Connection’. You’ll then select the Power BI Premium Capacity Metrics dataset from My Workspace per the image below:
With a data source established (My Workspace\Power BI Premium Capacity Metrics) you can now begin to build your datasets (DAX queries) and format and structure the paginated report.
Right-click the Datasets folder and click ‘Add Dataset’. Choose the Capacity Metrics data source as the data source for this new dataset, and click ‘Query Designer’ to expose the field list of the metrics app dataset and some query building tools:
From the Query Designer interface you ‘could’ drag-and-drop’ fields to help define your query as the default experience. However, with paginated reports, whether it’s a relational database with SQL queries or a Tabular dataset/model with DAX queries, I prefer to write out the dataset queries rather than rely on the graphical interface tool. Additionally, I often save the query files in a project for version control, further analysis/tuning, and reuse in other future scenarios.
Given the strategic importance of paginated reports against Tabular models/PBI Premium datasets, I would expect the DAX query authoring experience to improve over time. However, for now it’s my experience and view that even for simple reports you will need to develop your own DAX queries in other tools like SSMS or DAX Studio.
To provide your own DAX query, you click the Design Mode icon (triangle and ruler at the top) and enter your query into the empty text box per the following image:
In this case we’re using the SUMMARIZECOLUMNS() function to group two attributes (dataset and workspace) with one aggregation expression – the sum of the “totalCount” column from the Query Metrics table. (This expression wouldn’t be necessary if a DAX measure that summed this column was already in the dataset).
The CALCULATETABLE() function and the @PremiumCapacity parameter is used to filter the query down to one specific premium capacity node. You’ll need to declare this parameter via the Query Parameters dialog (the @ icon on the left) and you’ll surely want separate, simple datasets to provide the availalable values and default value for the parameter.
It’s outside the scope of this post to walk through parameter setup in great detail but you can download the four DAX queries used in this example from my GitHub repository.
Paginated Report Published
Like many paginated reports, the intent in this scenario is to generate simple but detailed information (per dataset) that can be easily exported, printed, or emailed as an attachment. Therefore, in this example a single tablix data region (a table) and two supporting text boxes with expressions are all that’s required:
The report defaults to the premium capacity with the most queries via the DAX query used to define the default value. The DAX query providing the list of available premium capacity values populates the parameter dropdown for interactive filtering.
The text boxes above the table are bound to the currently selected premium capacity node (the parameter value) and the min and max timestamp column values from the TimeStamps table in the Premium Metrics app dataset. (At some point in the relatively near future more history may be available from the premium metrics app beyond the 7 days)
Note that alternating the background color of the detail group rows is a common practice to improve readability and, though outside the scope of this post, the headers and text boxes are set to repeat on any additional pages of the report.
All that’s left at this point is to create the email subscriptions for the different stakeholders via the Subscribe dialog in the Power BI service.
Each subscription will have a different premium capacity parameter value such that the recipients only see the datasets and query volume relevant to them. As an email subscription with the filtered data attached (in Excel in this case) the recipient doesn’t necessarily have to login to Power BI to analyze the report further.
Wrapping Up
So there you have it – a simple paginated report which supports multiple email subscriptions per premium capacity node. You could of course build on to this report with additional metrics and analysis available from the Premium Metrics app dataset or use a different tool like Power BI Desktop for a more interactive visualization experience.
I should have another new blog post out soon related to XMLA connectivity to Premium datasets. Thanks for visiting Insight Quest and please feel welcome to share any thoughts or questions you have.
Insight Quest 
2 comments