Analyse query performance

[DennesTorres]

Hi,

I know the environment is a PaaS and most underlying details should not be a concern for us. However, there are high-level decisions which leads to consequences we may need to analyse. For example, how delta tables deal with updates and the decision to build dimensions with upsert or even delete.

I was analysing exactly this and it was difficult to identify the performance differences. It's difficult to identify when the query is reading the storage, reading a cache and the impact in the capacity.

I tried to identify this analysing the impact on the capacity using the app "Premium Capacity Utilization and Metrics". But this leads to a 2nd question: This App is mainly focused on dataset refreshes. On fabric, we have the SQL Endpoint and data warehouse which may involve queries directly to them, instead of dataset refreshes. I couldn't identify this information in the App.

I opened the App dataset in Excel and the Items table only contains dataset refreshes (or am I mistaken?). Is there another way to analyse the impact of a query in a SQL Endpoint to the capacity, for example? Identify if the query was using cache or not?

I know there are the API's, but I was trying to avoid build something "from the ground" querying the API's.

Kind Regards,

Dennes

[puneetvijwani]

@DennesTorres here are the relevant links
https://appsource.microsoft.com/en-us/product/power-bi/pbi_pcmm.microsoftpremiumfabricpreviewreport?...
Its seems driect query to underlying hosted dataset


Blog : https://blog.fabric.microsoft.com/en-us/blog/capacity-metrics-in-microsoft-fabric?ft=Core:category#:....

[puneetvijwani]

@DennesTorres  If we Navigate to apps on left hand bar of Fabric portal 

and get apps you can search for this one below and get it 

[DennesTorres]

Hi,

I tested the new App. 

The link on the Admin portal still points to the old one, shouldn't it be fixed? 

The report itself fails, I couldn't see it :

But when checking the dataset in the workspace I couldn't find any error message, so I tried to open it in Excel.

The Items table contains the missing Items, not only the Datasets anymore - that's exactly what I was missing.

However, I couldn't find yet the correct relations to list the operations in order of date. Is there a documentation of these tables, to identify these relations ?

Is there are documentation about the connection? Could we import this data into a lake/dw to work with this data in SQL? The dataset doesn't work as a source for the lake, it would need to be at least a dataflow. But if we could make the same connection to extract the usage information from the capacity, this would help. Is there a documentation about this?

Kind Regards,

Dennes

[puneetvijwani]

@DennesTorres you using this fabric capacity overview app ?? or premium capacity utilization ???

[DennesTorres]

Hi,

Premium capacity Utilization. The portal took me to this one.

I will try to find this new one, if you have a link, it will help.

Kind Regards,

Dennes