Identifying Unused Tables in AAS Cube Over the Last 30 Days

Hi @madmax

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

Identifying unused tables in an Azure Analysis Services (AAS) cube can be a bit challenging, as AAS does not provide built-in functionality to track table usage directly. However, you can use a combination of monitoring tools and techniques to achieve this.

Here are some steps you can take to identify tables that haven't been used in the last 30 days:

To track usage, you can enable query logging in Azure Analysis Services. This will allow you to capture queries that are executed against your AAS model.

Enable Query Logging:

• In the Azure portal, navigate to your Azure Analysis Services instance.
• Under the Monitoring section, look for Diagnostic settings.
• Enable logging to capture query data. You can send logs to Azure Monitor, Log Analytics, or a storage account.

I hope this information helps. Please do let us know if you have any further queries.

Thank you.

@madmax

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.

@madmax

Following up to see on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.

We've set up Log Analytics for AAS and confirmed that logs are being generated.

I need assistance with writing a KQL query to identify tables that haven’t been used in the last 30 days.

And also tables accessed by Power BI reports hosted in Power BI Service.

Your help would be greatly appreciated.

Thank you in advance!

@madmax

Below is the Kusto Query Language (KQL) query to identify tables that haven’t been used in the last 30 days in Azure Analysis Services.

Query to Identify Tables that Haven’t Been Used in the Last 30 Days:

Assuming that your logs are stored in a table called AzureDiagnostics and that the table accesses are logged under an event called QueryEnd.

• Get the List of All Tables - The first part of the query retrieves a list of all the tables in your Azure Analysis Services model by querying the AzureDiagnostics table, filtering for the QueryEnd operation, and then extracting the table names from the Properties field.
• Get the List of Tables Accessed in the Last 30 Days - The second part of the query filters the AzureDiagnostics table again for the QueryEnd operation, but this time limits the results to logs from the last 30 days using TimeGenerated >= ago(30d).
• Find Tables Not Accessed in the Last 30 Days - Finally, the query compares the two lists and returns tables that are present in the first list (all tables) but not in the second list (tables accessed in the last 30 days).

// Step 1: Get the list of all tables in your Analysis Services model
let allTables = AzureDiagnostics
    | where ResourceType == "MICROSOFT.ANALYSISSERVICES/SERVERS" // Filter for AAS resource type
    | where OperationName == "QueryEnd" // Filter for completed queries
    | summarize by tableName = tostring(parse_json(tostring(Properties)).TableName); // Extract table names

// Step 2: Get the list of tables accessed in the last 30 days
let accessedTables = AzureDiagnostics
    | where ResourceType == "MICROSOFT.ANALYSISSERVICES/SERVERS" // Filter for AAS resource type
    | where OperationName == "QueryEnd" // Filter for completed queries
    | where TimeGenerated >= ago(30d) // Limit to logs from the last 30 days
    | summarize by tableName = tostring(parse_json(tostring(Properties)).TableName); // Extract table names

// Step 3: Find tables that haven’t been accessed in the last 30 days
allTables
| where tableName !in (accessedTables) // Find tables not in the accessed list
| project tableName // Display the table names

Query to Identify Tables Accessed by Power BI Reports:

To find tables accessed by Power BI reports, you need to look for queries initiated by Power BI Service. This can usually be identified via the ClientApplicationName or similar properties.

The below KQL snippet will help you track which tables are being queried by Power BI users in your Azure Analysis Services model.

AzureDiagnostics
| where ResourceType == "MICROSOFT.ANALYSISSERVICES/SERVERS" // Filter for Azure Analysis Services logs
| where OperationName == "QueryEnd" // Focus on completed queries
| where ClientApplicationName contains "Power BI" // Filter queries initiated by Power BI
| project TimeGenerated, tableName = tostring(parse_json(tostring(Properties)).TableName), ClientApplicationName // Extract relevant fields
| summarize AccessCount = count() by tableName, bin(TimeGenerated, 1d) // Count accesses per table, grouped by day
| order by TimeGenerated desc // Sort by most recent access time

Note: Ensure that the actual field names in your logs match the ones used in the queries; you may need to adjust the queries based on your specific log schema.

I hope this information helps. Please do let us know if you have any further queries.

Thank you.