Export (0) Print
Expand All

About Managing the Data Warehouse

Updated: March 25, 2014

Applies To: System Center 2012 - Service Manager, System Center 2012 R2 Service Manager, System Center 2012 SP1 - Service Manager

In System Center 2012 – Service Manager, there are seven data warehouse jobs that run at various times to maintain the data warehouse, as listed in the following table.

 

Data warehouse job Description

MPSyncJob

This job synchronizes all the management packs from the Service Manager source. These management packs define the content of the data warehouse. This job starts to run as soon as you register the Service Manager management group, and it takes several hours to complete on its initial run. For more information, see ”Register with Service Manager Data Warehouse” in the Deployment Guide for System Center 2012 – Service Manager.

DWMaintenance

This job performs data warehouse maintenance, such as indexing and updating statistics. This job will run automatically after the MPSyncJob has finished.

Entity (or Grooming)

Grooming functions typically involve activities on the data warehouse that remove data based on a configurable time period.

noteNote
For this release of Service Manager, grooming functions are handled as a workflow. Settings for this job are not configurable.

Extract

This job retrieves data from the Service Manager database. This job queries the Service Manager database for the delta data from its last run and writes this new data into the DWStagingAndConfig database in the data warehouse. There are two extract jobs in Service Manager: one for the Service Manager management group and the other for the data warehouse management group.

Transform

This job takes the raw data from the staging area and does any cleansing, reformatting, and aggregation that is required to get it into the final format for reporting. This transformed data is written into the DWRepository database.

Load

This job queries the data from the DWRepository database and inserts it into the DWDatamart database. The DWDatamart is the database that is used for all end user reporting needs.

For more information about the extract, transform, and load (ETL) jobs, see the blog post Data Warehouse – Anatomy of Extract, Transform, Load (ETL).

In order to manage the data warehouse, which is primarily used by reporting, you must perform maintenance tasks on these jobs. For example, you can view their status, pause and resume, set a schedule, enable and disable schedules, and troubleshoot data warehouse jobs. You can perform all of these maintenance tasks by using Windows PowerShell cmdlets. In addition, you can perform some of these tasks through the Service Manager console.

During deployment, you registered the Service Manager management group as discussed in “Register Service Manager Management Group” in the Deployment Guide for System Center 2012 – Service Manager. As a result of that action, management pack deployment started and MPSyncJob started. You should not start or resume any data warehouse jobs until MPSyncJob has finished, as shown in the Data Warehouse Jobs pane in the Service Manager console.

In the procedures in the links in the following table, you can manage the data warehouse by using a combination of Windows PowerShell cmdlets and the Service Manager console, as appropriate, to perform tasks with data warehouse jobs.

 

Task Reference

Enable and disable data warehouse jobs

How to Enable Data Warehouse Job Schedules

View data warehouse jobs status

How to View the Status of a Data Warehouse Job

Start and stop data warehouse jobs when you have to

How to Stop and Start a Data Warehouse Job

Schedule data warehouse jobs on a recurring basis

How to Schedule a Data Warehouse Job

Determine whether data warehouse jobs are running as expected

How to Troubleshoot a Data Warehouse Job

Job Schedule and Frequency

The schedule for a job defines when a job starts. Frequency refers to how often the job runs after it has started. Regardless of schedule and frequency, a job does not run unless the schedule for that job has been enabled. Except for the Entity (Grooming) job, each job has a default scheduled start time, which is midnight. The following table lists the scheduled start time, frequency, and default schedule setting.

 

Data warehouse job Scheduled start time Frequency Enabled by default?

MPSyncJob

Midnight

Every hour

Yes

DWMaintenance

Midnight

Every hour

Yes

Extract

Midnight

Every 5 minutes

Yes

Transform

Midnight

Every 30 minutes

Yes

Load

Midnight

Every hour

Yes

In this release of Service Manager, grooming functions are handled as a workflow. Settings for this job are not configurable.

Windows PowerShell Cmdlets

The Service Manager Windows PowerShell module contains cmdlets that are used in this scenario to manage data warehouse functions on the server that hosts the data warehouse. You must run all Windows PowerShell cmdlets as an administrator. To view the Windows PowerShell Help, type the get-help command, followed by the name of the cmdlet for which you want help. For example, type get-help Set-SCDWJobSchedule. The following cmdlets are used in this scenario:

  • Get-SCDWJobSchedule—Displays the schedule for a data warehouse job.

  • Get-SCDWJob—Displays status for all recurring Service Manager data warehouse jobs.

  • Get-SCDWMgmtGroup—Shows details for a management group that is registered with the data warehouse.

  • Remove-SCDWMgmtGroup—Removes a management group from the data warehouse.

  • Set-SCDWJobSchedule—Sets the schedule for data warehouse jobs.

  • Enable-SCDWJobSchedule—Enables a data warehouse job schedule.

  • Disable-SCDWJobSchedule—Disables a data warehouse job schedule. Job schedules are disabled by default.

For information about how to run Service Manager Windows PowerShell cmdlets, see Getting Started with Service Manager Cmdlets for Windows PowerShell.

Getting Started with Data Warehouse Jobs

When you register with the Service Manager data warehouse (see "Register with Service Manager Data Warehouse" in the Deployment Guide for System Center 2012 – Service Manager, the MPSyncJob starts running. This job can take several hours to complete its initial run. When this job is complete, you can see two extract jobs listed in the Data Warehouse Jobs pane. One extract job is listed as Extract_<data warehouse management group name>, and the other extract job is listed as Extract_<Service Manager management group name>. When both of these extract jobs appear, you know that the initial run of the MPSyncJob is complete and that you can now proceed with the subsequent maintenance tasks.

See Also

-----
For additional resources, see Information and Support for System Center 2012.

Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
-----
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft