Experience Analytics Error throwing System.Data.DataException error

Question

I am in the upgrading process of Sitecore instance from 9.1 to 10.2 where the upgrade steps have been followed where I created a fresh 10.2.x instance and deployed the solution in to after the fact it got refactored to the version it is about to be deployed with. Except the data exception on Experience Analytics end.

ERROR [Experience Analytics]: System.Exception: Invalid column name 'Converted'. ---> System.Data.DataException: Error executing SQL command: 
WITH WorkingSubset AS 
(
SELECT Fact_ChannelTypeMetrics.[bounces], Fact_ChannelTypeMetrics.[conversions], Fact_ChannelTypeMetrics.[pageViews], Fact_ChannelTypeMetrics.[timeOnSite], Fact_ChannelTypeMetrics.[value], Fact_ChannelTypeMetrics.[visits], Fact_ChannelTypeMetrics.[outcomeOccurrences], Fact_ChannelTypeMetrics.[monetaryValue], Fact_ChannelTypeMetrics.[DimensionKeyId], Fact_ChannelTypeMetrics.[SegmentId], Fact_ChannelTypeMetrics.[Date],Fact_SystemMetrics.[totalVisits], Fact_SystemMetrics.[totalTimeOnSite], Fact_SystemMetrics.[totalPageviews], Fact_SystemMetrics.[totalEngagementValue]
FROM Fact_ChannelTypeMetrics INNER JOIN [DimensionKeys] on [DimensionKeys].DimensionKeyId = Fact_ChannelTypeMetrics.DimensionKeyId LEFT OUTER JOIN (SELECT * FROM Fact_SystemMetrics WHERE [SegmentId] IN (@systemSegmentId1)) AS Fact_SystemMetrics ON Fact_ChannelTypeMetrics.[SiteNameId] = Fact_SystemMetrics.[SiteNameId] AND Fact_ChannelTypeMetrics.[Date] = Fact_SystemMetrics.[Date] AND Fact_ChannelTypeMetrics.[FilterId] = Fact_SystemMetrics.[FilterId] 
WHERE Fact_ChannelTypeMetrics.[SegmentId] = @SegmentId AND Fact_ChannelTypeMetrics.[Date] BETWEEN @StartDate AND @EndDate AND ((Fact_ChannelTypeMetrics.DimensionKeyId = @DimensionKeyId))
)
,BaseResults AS 
(
SELECT Sum(CAST([bounces] as bigint)) AS [bounces], Sum(CAST([conversions] as bigint)) AS [conversions], Sum(CAST([pageViews] as bigint)) AS [pageViews], Sum(CAST([timeOnSite] as bigint)) AS [timeOnSite], Sum(CAST([value] as bigint)) AS [value], Sum(CAST([visits] as bigint)) AS [visits], Sum(CAST([outcomeOccurrences] as bigint)) AS [outcomeOccurrences], Sum([monetaryValue]) AS [monetaryValue], Sum(CAST([totalVisits] as bigint)) AS [totalVisits], Sum(CAST([totalTimeOnSite] as bigint)) AS [totalTimeOnSite], Sum(CAST([totalPageviews] as bigint)) AS [totalPageviews], Sum(CAST([totalEngagementValue] as bigint)) AS [totalEngagementValue], COUNT(*) OVER () AS [TotalDbRows] 
FROM [WorkingSubset]
)
SELECT *, 1.0*TimeOnSite/Visits as [avgVisitDuration], 1.0*Bounces/Visits as [bounceRate], 1.0*Converted/Visits as [conversionRate], 1.0*PageViews/Visits as [avgVisitPageViews], 1.0*Value/Visits as [valuePerVisit], 1.0*MonetaryValue/Visits as [avgMonetaryValue]  
FROM [BaseResults] ---> System.Data.SqlClient.SqlException: Invalid column name 'Converted'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
   --- End of inner exception stack trace ---
   --- End of inner exception stack trace ---
   at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
   at Sitecore.Data.DataProviders.Sql.DataProviderReader..ctor(DataProviderCommand command)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass29_0.<CreateReader>b__0()
   at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters)
   at Sitecore.Analytics.Reporting.SqlReportDataSource.GetData(ReportDataQuery query)
   at Sitecore.Xdb.Reporting.ReportDataProvider.ExecuteQueryWithCache(ReportDataQuery query, ReportDataSource dataSource, CachingPolicy cachingPolicy)
   at Sitecore.Xdb.Reporting.ReportDataProvider.GetData(String dataSourceName, ReportDataQuery query, CachingPolicy cachingPolicy)
   at Sitecore.ExperienceAnalytics.Api.ReportDataService.ExecuteQuery(IReportQueryData queryData, CachingPolicy cachingPolicy)
   at Sitecore.ExperienceAnalytics.Api.ReportingService.RunQuery(ReportQuery reportQuery)
   at Sitecore.ExperienceAnalytics.Api.Http.AnalyticsDataController.Get(ReportQuery reportQuery)

I have validated the following

1. XConnect app_data had infacts a valid license xml file
2. Users been added to the computer management Performance log users and Performance montior users
3. validated that the cert assigned is infact self-assigned
4. Performed a Sitecore 10.2.1 rev. 008245 PRE (OnPrem) Cumulative hotfix patch
5. Being a fresh xconnect instance rather with fresh dbs no old databases were reference to perform any xp related upgrades where as master db related upgrades like sql scripts, clean ups where done

Please let me know if i have missed any.

Answer 1

Dean OBrien's answer was helpful as a starting point when investigating this one. However I needed to dig deeper, which meant following the stacktrace and digging into the decompiled code which lead me to Sitecore.ExperienceAnalytics.Api.Query.QueryBuilder.FactsQueryBuilder.

This builder is actually building up the SQL query based on item configuration in /sitecore/system/Marketing Control Panel/Experience Analytics/Dimensions.

Here we can see that this item in question does not have the 'Converted' metric specified and hence causing the SQL error.

So we can simply delete everything out of /sitecore/system/Marketing Control Panel/Experience Analytics/Dimensions by adding a new version to that item and then deleting the item. Sitecore will delete all the items out the DB and then restore the stock items from the master resource file. After this, restart the CM server to clear all caches.

Answer 2

If you check a fresh copy of the Reporting Database from Sitecore Experience Platform 10.2 you will see there is a column called Converted in the Fact_ChannelTypeMetrics table.

However, the came table in Sitecore Experience Platform 9.1 Update-1, does not have the column.

Therefore, given that the error is stating that it is an invalid column name, I would suspect that you are running old Sitecore 9.1 DLLs against new Sitecore 10.2 databases.

Please check your websites bin folded and ensure the sitecore DLLs are the correct version (i.e. not 12.0.1.0)

UPDATE

If your instance is distributed, remember that you may still have reporting instance configured from pre upgrade (this is removed in SC 10.1). Config for the reporting url will be as below:

  <sitecore role:require="ContentManagement">
    <reporting role:require="!Reporting">
      <remote enabled="true">
        <httpTransportFactory type="Sitecore.Analytics.Core.ApiKeyHttpTransportFactory, Sitecore.Analytics.Core" singleInstance="true">
          <param desc="serviceUrl">#{reporting-service-url}</param>

UPDATE

Sorry upon looking at error again, I think we got it the wrong way round... the source is generating the SQL with 'Converted' in it, and the database is erroring to say it does not exist. So to confirm this, please install a fresh Reporting database and connect that to your instance. If that corrects your issue, then it looks like you have not fully performed all of the SQL upgrade scripts relating to you Reporting Database.