Home Service Manager

need help with DWDataMart issue

Answers

  • alex_kokinalex_kokin Member IT Monkey ✭
    edited May 17

    Can anyone provide some guidance here please. I have rebuilt the data warehouse twice now but still unable to see any data at all in the DWDataMart. There are these jobs that appear to be failing and the Last Run and Next Run looks like they are stuck back on the 14th for each of the ones that are Failed.

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @alex_kokin

    Are you able to retrieve the error it is failing on please either through the GUI or via powershell?

    Thanks.

    Shane

  • alex_kokinalex_kokin Member IT Monkey ✭

    ETL Module Execution failed:

     ETL process type: Transform

     Batch ID: 507

     Module name: TransformIncidentDim

     Message: Object reference not set to an instance of an object.


     Stack:  at Microsoft.SystemCenter.Warehouse.Etl.Watermark.SerializeWaterMark(Watermark wm)

      at Microsoft.SystemCenter.Warehouse.Etl.StoredProcedure.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

      at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 batchSize)

      at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

      at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)



    An error countered while attempting to execute ETL Module:

     ETL process type: Transform

     Batch ID: 507

     Module name: TransformIncidentDim

     Message: Object reference not set to an instance of an object.


     Stack:  at Microsoft.SystemCenter.Warehouse.Etl.Watermark.SerializeWaterMark(Watermark wm)

      at Microsoft.SystemCenter.Warehouse.Etl.StoredProcedure.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

      at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 batchSize)

      at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

      at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)


    ETL Module Execution failed:

     ETL process type: Load

     Batch ID: 504

     Module name: LoadCMDWDataMartProblemDim

     Message: Object reference not set to an instance of an object.


     Stack:  at Microsoft.SystemCenter.Warehouse.Etl.Watermark.SerializeWaterMark(Watermark wm)

      at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.ShredWaterMark(Watermark wm)

      at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 loadBatchSize)

      at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

      at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

  • alex_kokinalex_kokin Member IT Monkey ✭

    TransformIncidentDim                     Failed    <Errors><Error EventTime="2021-05-19T03:23:18.4860331Z">Object reference not set to an instance of an object.</Error><Error EventTime="2021-05-19T03:23:18.5641481Z">Object reference not set to an instance of an object.</Error><Error EventTime="2021-05-19T03:23...

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    @alex_kokin

    Oh dear, object references errors are possibly the least helpful here haha!

    Try this:

    Run the following powershell command and send me the full results

    Get-SCDWJobModule


    This will help us determine which jobs are failing

    Disable all DW jobs and make sure no jobs are currently running

    Then we need to do some SQL to clear the errors


    Run this SQL command against the DWStaging and Config DB

    select processId from infra.process where processname like 'DWMaintenance'


    select batchid from infra.batch where processid = <processIdfromscript>


    select * from infra.workitem(nolock) where batchid = <batchIdfromscript>

    Use this if there is multiple

    select * from infra.workitem(nolock) where batchid IN ('479119','481576','481626')


    update infra.workitem set statusid = 3 where workitemId = <WorkitemID from results>


    update infra.workitem set ErrorSummary = NULL where workitemId = <sameworkitemid>


    This sets the status back to 'Not Started' and clears the error messages


    Then restart all the jobs

Sign In or Register to comment.