Analysis Services Telemetry: What information does SSAS submit? and how has it changed in SSAS 2019?

Starting with SSAS 2016, SSAS sends some usage information back to Microsoft by default. This behavior is similar to how Telemetry works in SQL Server and SSMS and can be turned off.

The first time I noticed SSAS Telemetry, it was while I was checking the SSAS Administrators group membership on a server and saw a member called “NT Service\SSASTELEMETRY”. I was curious to find out exactly what information was potentially being submitted so I started a trace and let it run for 24 hours. I was surprised about the amount of meta data queries that ran.

Of course, the trace just showed the queries ran but that does not show if the results or a portion of them were actually submitted or if the results were anonymized before they were submitted. Based on Christian Wade’s response to my post on Twitter, any personally identifiable data about the customer such as names of databases/measures/etc. would be hashed/anonymized in SSAS 2016/2017 before they get submitted. Also the good news is that in SSAS 2019 (CTP2.2 at the time of writing this post), Telemetry has been significantly dialed back.

What meta data is being collected?

The trace I ran recorded the following queries. As previously said, collected does not mean the result was necessarily sent over to Microsoft but one has to assume that’s the purpose of Telemetry in the first place to submit some amount of meta data.

Every Hour:

In all versions of SSAS (starting 2016), Telemetry runs the following DMX query every hour:

select 1 as [ConnectionCount] from [$SYSTEM].[DISCOVER_CONNECTIONS]

This query uses one of SSAS’s Dynamic Management Views (DMVs) to return the number of user connections to the server. This does not seem too bad but think about this: If this were the only piece of information being sent over by Telemetry, hypothetically, at any given point in time, Microsoft can tell how heavily an instance is being used at any customer location that has Telemetry set to on. Aggregate this data (because that’s what B I people do!) and Microsoft would know how many users are connected to each instance over a region and across the world every hour and over time. Wouldn’t it be interesting if there were already a cube/model built off of this usage data?

Once a day: All versions

Then comes the more interesting queries. Once a day, the following query runs in all versions:

SELECT [Compatibility_Level], [Type], 1 as [CountDBCompatibilityType]
FROM [$system].[DBSCHEMA_CATALOGS]
Order by [Compatibility_Level]

The above query returns one row for each SSAS database on the server with information about its compatibility level. The following screenshot shows the output of this query on a server with two 1400 level databases (SSAS 2019) and one 1200 (SSAS 2016) database.

Once a day: SSAS 2016 and SSAS 2017 only

SSAS 2016 and SSAS 2017 submit more queries once a day. First they get a list of all databases on the server.

--Returns a list of databases on the server.
SELECT [CATALOG_NAME] as [ForEachDBID], 1 as [CountDB]
FROM $System.DBSchema_Catalogs

Then for each database, the following queries run:

--Returns a list of cubes/model names in the database. 
SELECT [CATALOG_NAME], 1 AS [DBCubeCount]
FROM [$System].[MDSCHEMA_CUBES] WHERE [CUBE_SOURCE] = 1
--Returns a list of dimensions/tables in a database.
SELECT [CATALOG_NAME], [CUBE_NAME], 1 as [DimensionCount]
from [$SYSTEM].[MDSCHEMA_DIMENSIONS] ORDER BY [CUBE_NAME]
--Returs a list of measure groups/tables in a database.
SELECT [CATALOG_NAME], [CUBE_NAME], 1 as [MeasureGroupCount]
FROM [$SYSTEM].[MDSCHEMA_MEASUREGROUPS] ORDER BY [CUBE_NAME]
--Returns a list of measures (without name) along with their measure group (table) they belong to.
SELECT [CATALOG_NAME], [CUBE_NAME], [MEASUREGROUP_NAME], [MEASURE_DISPLAY_FOLDER],
1 as [MDDisplayFoldersMeasureCount]
FROM [$SYSTEM].[MDSCHEMA_MEASURES]

This is a lot of meta data. The last couple of queries return more useful information for multidimensional models than they do for tabular models.

How to turn off SSAS Telemetry

In order to turn off Telemetry for SSAS you have to make a change to a registry key. This will stop the Telemetry. I also like to remove the account that Telemetry creates from the SSAS administrators group.

1. Set CPE.CustomerFeedback key to zero

Depending on the SSAS version, the key path is in the following format:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\YOUR_SSAS_SERVER_INSTANCE\CPE

For example, for SSAS 2017, the path is:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS14.MSSQLSERVER14\CPE

For SSAS 2019, the path is:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS14.MSSQLSERVER15\CPE

Once you find the path, set the CustomerFeedback key’s value to 0 instead of 1.

2. Remove the SSAS Telemetry account from SSAS administrators group

Making the registry key change, stops the Telemetry. This step is only for clean up purposes.

Summary

In SSAS 2016/2017, Telemetry may submit a large amount of meta data which should be anonymized. In SSAS 2019, the amount of meta data collected has been significantly reduced and seems to only contain the number of connections and compatibility level of databases. Either way, Telemetry can be easily turned off by editing a registry setting.