Daily dataset size email

[dd8zc]

It's possbile to use this SQL to get the dataset size https://docs.microsoft.com/en-us/power-bi/admin/service-premium-large-models#checking-dataset-size

I want this information every morning via email. What's the best approach? Is it better to use powershell, python or even microsoft flow for this?

[lbendlin]

I would register an app and then go with PowerShell cmdlets.  By the way you need DMV code, not SQL.

[dd8zc]

Thanks for the answer, @lbendlin, I will check this possibility. 

Why should I use DMV, whats the advantage? The linked article mentioned this SQL code:

SELECT * FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMNS,
 [DATABASE_NAME] = '<Dataset Name>') //Sum DICTIONARY_SIZE (bytes)

SELECT * FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS,
 [DATABASE_NAME] = '<Dataset Name>') //Sum USED_SIZE (bytes)

[lbendlin]

There is no choice, you have to use DMV. It is a very limited subset of SQL.

Dynamic Management Views (DMVs) in Analysis Services | Microsoft Docs

The first query has no size information. The second one can be used as

SELECT DIMENSION_NAME, USED_SIZE FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS,
[DATABASE_NAME] = '<dataset name>')

but then you have to do the aggregations yourself. DMV doesn't support any aggregations.