Identifying Unused Tables in AAS Cube Over the Last 30 Days
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?
Azure Analysis Services
Azure Synapse Analytics
SQL Server Reporting Services
-
Chandra Boorla 3,950 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,950 Reputation points • Microsoft Vendor
2024-11-11T03:34:56.84+00:00 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,950 Reputation points • Microsoft Vendor
2024-11-12T01:18:39.0433333+00:00 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,950 Reputation points • Microsoft Vendor
2024-11-13T07:01:57.3533333+00:00 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 calledQueryEnd
.- 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 theProperties
field. - Get the List of Tables Accessed in the Last 30 Days - The second part of the query filters the
AzureDiagnostics
table again for theQueryEnd
operation, but this time limits the results to logs from the last 30 days usingTimeGenerated >= 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.
- 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
-
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,950 Reputation points • Microsoft Vendor
2024-11-15T08:57:51.5666667+00:00 The errors you are encountering indicate that the fields Properties and
ClientApplicationName
may not exist or are named differently in yourAzureDiagnostics
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 namedTableName
instead of being nested inProperties
, 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,950 Reputation points • Microsoft Vendor
2024-11-18T01:11:38.5233333+00:00 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,950 Reputation points • Microsoft Vendor
2024-11-19T00:56:57.1966667+00:00 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.
Sign in to comment