Announcements
Management Reporter needs rebuild data mart often. For the third time in four months I had to rebuild the data mart for a client. This week they were running reports fine until today and then all data for 2014 just stopped appearing on the reports. You rebuild the data mart and all returns to normal. Is this a sign of a more serious problem? They are on CU 9.
*This post is locked for comments
I seem to remember last year, on a much earlier version of Management Reporter, running into a similar problem. I made sure the services created by the install for Management Reporter were set to auto-recover when they failed in services on the server having the problem.
This resolved the problem, but I wasn't fond of not knowing why the services were failing on the machine, so I moved Management Reporter to a different virtual machine. I did not wrangle the SQL databases. I just installed Management Reporter on another machine and Exported and Imported the Building Blocks. It took about an hour.
Potentially there was an issue with the one of the 9 functions updating the data mart integration. To verify the last time all of the process completed open the Configuration Console and select the integration and verify the 'last time everything was fully integrated' data. If the date has not updated and is over three days old there is potential for data not to update correctly in the data mart database.
In the Configuration Console > Logs data mart integration review the logs for any error messages that could be contributing to the issue. You could also run the following script against the Management Reporter database.
select CIG.[Description]
, ST.[Name]
, SM.[Text]
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[StartTime]) as LocalStartTime
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[EndTime]) as LocalEndTime
, SL.[TotalRetryNumber]
, SL.[IsFailed]
, STT.[Name] as TaskType
from [Scheduling].[Log] SL with (nolock)
inner join [Scheduling].[Task] ST with (nolock) on SL.TaskId = ST.Id
inner join [Scheduling].[Message] SM with (nolock) on SL.Id = SM.LogId
inner join [Scheduling].[TaskType] STT with (nolock) on ST.TypeId = STT.Id
inner join [Connector].[IntegrationGroup] CIG with (nolock) on CIG.[IntegrationId] = ST.[CategoryId]
order by SL.[StartTime] desc
Below is a SQL query that will help verify the current data mart process when ran against the Management Reporter database. The state Type of 3 mean the function is currently in progress and 5 mean it is complete.
select [Connector].[IntegrationGroup].[Description], [Scheduling].[Task].[Name],
[Scheduling].[TaskState].[Progress], [Scheduling].[TaskState].[StateType],
[Scheduling].[TaskState].[LastRunTime], [Scheduling].[TaskState].[NextRunTime],
[Connector].[Map].[ContinueOnRecordError], [Scheduling].[Trigger].[UnitOfMeasure],
[Scheduling].[Trigger].[Interval], [Scheduling].[Trigger].[IsEnabled]
from [Connector].[Map] with (nolock)
inner join [Scheduling].[Task] on [Scheduling].[Task].[Id] = [Connector].[Map].[MapId]
inner join [Scheduling].[TaskState] on [Scheduling].[Task].[Id] = [Scheduling].[TaskState].[TaskId]
inner join [Connector].[IntegrationGroup] on [Connector].[IntegrationGroup].[IntegrationId] = [Scheduling].[Task].[CategoryId]
inner join [Scheduling].[Trigger] on [Scheduling].[Task].[TriggerId] = [Scheduling].[Trigger].[Id]
order by [Connector].[IntegrationGroup].[Description], [Scheduling].[Task].[Name]
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
