Querying Premium Capacity Health Metrics Using XMLA End Point

[monaraya]

To query Capacity Metrics, we can connect to the XMLA end point. If you had installed the ‘CapacityMetrics’ App in a specific workspace, please follow the documentation here to retrieve the workspace connection. It looks similar to the screen below:

Copy the connection and connect from the client tool. For demo purposes, I am using SQL Server Management Studio here. Below are the steps to connect:

1. Open SSMS
2. Connect Analysis services
3. Specify the server name that you had copied earlier
4. Specify the authentication and click on connect

In case the connection is not successful, please proceed with the troubleshooting as mentioned here. Once the connection is successful, we will see the data model as shown below:

We can directly query the data set / browse the data/ automate using PowerShell. To query the dataset directly, you can run a DAX query like below:

EVALUATE SUMMARIZE (EvictionMetrics, EvictionMetrics[capacityObjectId], EvictionMetrics[timestamp], EvictionMetrics[activeModelCount], EvictionMetrics[inactiveModelCount], EvictionMetrics[averageIdleTimeBeforeEviction])

You can browse the data by right clicking on the data model and click on browse.

To automate this using PowerShell, we can consider using the connection string as mentioned below:

Provider=MSOLAP; Data Source= powerbi://api.powerbi.com/v1.0/myorg/<WorkspaceName>; Initial Catalog=<Provide Dataset ID>; Update Isolation Level=2

Author: Chandrika Ravuri

Reviewer: Mounika Narayana Reddy