Identifying Unused Tables in AAS Cube Over the Last 30 Days

madmax 0

I have a question regarding identifying unused tables in Azure Analysis Services (AAS).

In our organization, we’re using AAS, and due to limited storage, our cube refresh is failing. I’ve been tasked with finding a list of tables that haven’t been used in the last 30 days. Our AAS model is utilized in some SSRS reports, in Power BI reports (which are scheduled for daily refresh), and is also directly queried via SSMS.

Could someone guide me on how to identify tables within the AAS cube that haven't been used in the past 30 days?

Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-08T07:34:07.0066667+00:00
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.
Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-11T03:34:56.84+00:00

@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.
Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-12T01:18:39.0433333+00:00

@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.
madmax 0 Reputation points

2024-11-12T13:29:43.5766667+00:00

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!
Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-13T07:01:57.3533333+00:00
@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.
madmax 0 Reputation points

2024-11-13T16:48:22.2433333+00:00

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

" 'summarize' operator: Failed to resolve scalar expression named 'Properties' Request id: 7d359dbe-042a-46ea-a215-83bb9e58bead "

and for Query to Identify Tables Accessed by Power BI Reports:

" 'where' operator: Failed to resolve scalar expression named 'ClientApplicationName' Request id: 2a4297d2-5ce4-4f63-9f5c-bc1ac310609c "
Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-15T08:57:51.5666667+00:00
@madmax

The errors you are encountering indicate that the fields Properties and ClientApplicationName may not exist or are named differently in your AzureDiagnostics table. To resolve these issues, you will need to inspect the schema of your AzureDiagnostics table and adjust the queries accordingly. Here are some steps that might help you:

Inspect the Schema of AzureDiagnostics Table:

Run a query to inspect the schema and understand the available fields.

AzureDiagnostics | take 10

This will give you a glimpse of the columns in the AzureDiagnostics table, allowing you to identify the correct field names.

Adjust the Queries Based on the Actual Field Names:

Once you have verified the structure of the AzureDiagnostics table, you can adjust your queries. Here’s how you might modify the queries based on the findings:

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

Make sure to replace Properties with the actual field name that contains the table name. For example, if the field is named TableName instead of being nested in Properties, you would adjust the query as follows:

// 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(TableName); // Adjust this line based on actual field name // 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(TableName); // Adjust this line based on actual field name // 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

Verify and Adjust the ClientApplicationName Field:

Check if the ClientApplicationName field exists. If it's named differently, use the correct field name in your query.

Query to Identify Tables Accessed by Power BI Reports:

Similarly, adjust the field name for ClientApplicationName:

AzureDiagnostics | where ResourceType == "MICROSOFT.ANALYSISSERVICES/SERVERS" // Filter for Azure Analysis Services logs | where OperationName == "QueryEnd" // Focus on completed queries | where ClientAppName contains "Power BI" // Adjust this line based on actual field name | project TimeGenerated, tableName = tostring(TableName), ClientAppName // Adjust this line based on actual field name | 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

Once you adjust your AzureDiagnostics table according to the actual schema, the queries should run smoothly.

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

Thank you.
Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-18T01:11:38.5233333+00:00

@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.
Chandra Boorla 3,460 Reputation points Microsoft Vendor

2024-11-19T00:56:57.1966667+00:00

@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.

Share via

Identifying Unused Tables in AAS Cube Over the Last 30 Days

Your answer