Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
Identifying Unused Tables in AAS Cube Over the Last 30 Days
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 76%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EM%3C/text%3E%3C/svg%3E)
madmax
0
Reputation points
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
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(99.2, 65%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECB%3C/text%3E%3C/svg%3E)
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator2024-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 15,455 Reputation points Microsoft External Staff Moderator
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 15,455 Reputation points Microsoft External Staff Moderator
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 15,455 Reputation points Microsoft External Staff Moderator
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
AzureDiagnosticsand 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
AzureDiagnosticstable, filtering for the QueryEnd operation, and then extracting the table names from thePropertiesfield. - Get the List of Tables Accessed in the Last 30 Days - The second part of the query filters the
AzureDiagnosticstable again for theQueryEndoperation, 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 namesQuery 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 timeNote: 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 15,455 Reputation points Microsoft External Staff Moderator
2024-11-15T08:57:51.5666667+00:00 The errors you are encountering indicate that the fields Properties and
ClientApplicationNamemay not exist or are named differently in yourAzureDiagnosticstable. 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
AzureDiagnosticsTable:- Run a query to inspect the schema and understand the available fields.
AzureDiagnostics | take 10This will give you a glimpse of the columns in the
AzureDiagnosticstable, 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
AzureDiagnosticstable, 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
Propertieswith the actual field name that contains the table name. For example, if the field is namedTableNameinstead 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 namesVerify and Adjust the
ClientApplicationNameField:- Check if the
ClientApplicationNamefield 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 timeOnce you adjust your
AzureDiagnosticstable 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 15,455 Reputation points Microsoft External Staff Moderator
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 15,455 Reputation points Microsoft External Staff Moderator
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
Sign in to answer