3

I installed a local instance on Sitecore 10.2 using SIA, 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 XDB databases that came with the installation, when trying to view the experience analytics I get the errors in the attached image

dashboard errors

I went through the logs for all the xconnect jobs as well as the main xconnect site and found no errors, I was also able to visit the local xconnect site without issues (it showed the timer on visit)

The only error I found was on the main site:

>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, xcon) and that the local service as well as the AppPools have access to it.

I also tried rebuilding the reporting DB which succeeded but I still get the error when switching to the rebuilt DB.

5
  • You mention installing SC10.2 using SIA. If so why would you need to upgrade? As the DBs that are installed as part of this process are correct. If you have in fact upgraded then what version did you upgrade from? Aug 21, 2022 at 7:27
  • 1
    Please see sitecore.stackexchange.com/a/33407/1278 for the answer Nov 11, 2022 at 6:02
  • Hey @MarkGibbons the solution did not work for me, I found the converted field was included for that particular field
    – Muradious
    Nov 16, 2022 at 13:59
  • @DeanOBrien from 9.1, the issue is that the Analytics DB's are all clean instance, we only included Core, master and Web from the old 9.1 which I applied the upgrade scripts to.
    – Muradious
    Nov 16, 2022 at 14:00
  • The error suggests to me that the database doesnt have the column 'converted', which is strange if clean DBs... please double check conn strings in the xconnect instance, follow dbs and confirm column exists. Also, what happens if you connect the original OTB master DB. Does error go away? If so you might need to package some of the relevant Markting Control panel items from vanilla to new. Nov 16, 2022 at 15:10

1 Answer 1

1

I had a similar problem in the past. Make sure that you have your valid license.xml file in ..\App_Data\ folder of your xConnect site.

1
  • Can you expand on this answer at all? Nov 11, 2022 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.