Performance counters collected by System Center Operations Manager 2007 are exported from the Operations Manager OnePoint database to Performance Warehouse database. Moving this data to PWDB40:
- Allows customer to archive performance data for long-term
analysis.
- Keeps the Operations Manager OnePoint database to a manageable
size.
Data collection applications installed on back-end Microsoft Exchange Server 2007 and Windows SharePoint Services servers work in conjunction with update data applications and Microsoft SQL Server Integration Services (SSIS) to update PWDB40.
Get Customer Data into PWDB40
In order to generate reports based on service plan types and customers, the provisioning information must be collected from the provisioning database and inserted into the PWDB40.
When using SSIS to import customer data into PWDB40, the tasks listed here are run in the following order:
- Determine the most recent date and time a row in the
provisioning database table was last updated. This time is what
will be used as the basis for the export.
- Export new rows created since the last time this package was
run into the associated PWDB40 temporary table.
- Evaluate temporary table data against associated reporting
tables in PWDB40.
- Delete duplicate rows in the temporary tables, if
present.
- Insert data by joining the temporary tables to its associated
reporting table and perform a retrieval operation.
- Create a new index for the reporting tables by running the
Database Consistency Checker (DBCC) Dbreindex command for
each.
The Import Customer Data script contains logic for managing the process of importing category plan data from the provisioning database into PWDB40. This logic differs from that used to collect and insert data into the other tables.
CategoryPlans does the following:
- Get data from Provisioning table (CategoryPlans) and put in
PWDB40 staging table (CategoryPlansUpdates).
- Insert new rows from PWDB40 staging table
(CategoryPlansUpdates) into PWDB40 reporting
(CategoryPlans).
- Delete rows not present in staging table (CategoryPlansUpdates)
from PWDB40 reporting table (CategoryPlans).
Table: Table Sets for Import Customer Data Tasks and Transforms
| Provisioning table | Temporary PWDB40 table | Reporting PWDB40 table |
|---|---|---|
|
CustomerTypes |
CustomerTypesUpdates |
CustomerTypes |
|
EventTypes |
EventTypesUpdates |
EventTypes |
|
StatusTypes |
StatusTypesUpdates |
StatusTypes |
|
AssetTypes |
AssetTypesUpdates |
AssetTypes |
|
Features |
FeaturesUpdates |
Features |
|
ServicePlanCategories |
ServicePlanCategoriesUpdates |
ServicePlanCategories |
|
ServicePlans |
ServicePlansUpdates |
ServicePlans |
|
ServicePlanFeatures |
ServicePlanFeaturesUpdates |
ServicePlanFeatures |
|
ServidePlanTypes |
ServicePlanTypesUpdates |
ServidePlanTypes |
|
Customers |
CustomersUpdates |
Customers |
|
AvailablePlans |
AvailablePlansUpdates |
AvailablePlans |
|
CutsomerAssets |
CustomerAssetsUpdates |
CutsomerAssets |
|
CustomerAssetProperties |
CustomerAssetPropertiesUpdates |
CustomerAssetProperties |
|
CategoryPlans |
CategoryPlansUpdates |
CategoryPlans |
|
CustomerPlans |
CustomerPlansUpdates |
CustomerPlans |