I installed a local instance on Sitecore 10.2Sitecore 10.2 using SIASIA, and I performed the upgrade scripts on the Core, master, and web databases, since it is a local instance I didn't really care about the analytics data so I kept using the default empty xdbXDB databases that came with the installation, when trying to view the experience analytics I get the errors in the attached image
I went through the logs for all the xconnectxconnect jobs as well as the main xconnectxconnect site and found no errors, I was also able to visit the local xconnectxconnect site without issues (it showed the timer on visit)
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] != @OtherKeyId)) ) ,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], DATEADD(week, DATEDIFF(week, 0, [Date]),0) AS [Date], 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]
GROUP BY DATEADD(week, DATEDIFF(week, 0, [Date]),0) ) SELECT , 1.0TimeOnSite/Visits as [avgVisitDuration], 1.0Bounces/Visits as [bounceRate], 1.0Converted/Visits as [conversionRate], 1.0PageViews/Visits as [avgVisitPageViews], 1.0Value/Visits as [valuePerVisit], 1.0MonetaryValue/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.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)>
>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] != @OtherKeyId))
)
,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], DATEADD(week, DATEDIFF(week, 0, [Date]),0) AS [Date], 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]
GROUP BY DATEADD(week, DATEDIFF(week, 0, [Date]),0)
)
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 made sure the client certificate is correct in all instances (xm, xconxm, xcon) and that the local service as well as the AppPools have access to it.
