MOM 2005 Summary Reporting Pack Guide

The MOM 2005 Summary Reporting Pack (SRP) performs aggregation, or summarization, of the performance counter and alert data in your organization's MOM 2005 Reporting database. The SRP then performs analysis of this summarized data and provides reports that aid in evaluating operational trends in your organization's IT infrastructure.

The SRP provides generic performance counter and alert data reports and the following management pack specific reports:

• Active Directory

• Exchange

• MOM

• Windows base operating system

The time needed to run these reports increases minimally even as the MOM Reporting database increases significantly in size.

The installation of the MOM 2005 SRP creates new tables in the SystemCenterReporting database, the same database used by MOM 2005 Reporting. These tables are documented in the Technical Reference section of this document.

Pre-Installation Requirements for the Summary Reporting Pack

Verify you meet the following requirements:

• The SRP requires a system hosting a MOM 2005 SP1 or later Reporting database. MOM 2005 SP1 can be downloaded from the MOM home site, https://go.microsoft.com/fwlink/?linkid=6727.

• The MOM 2005 Data Transformation Services (DTS) job, SystemCenterDTSPackageTask, which transfers data from the MOM database to the MOM Reporting database, needs to have run at least once before installing the SRP. Running it is an option of the MOM 2005 Reporting setup and is scheduled by default to run daily at one A.M.

• Verify that your SystemCenterReporting database is at least 40% free before installing the Summary Reporting Pack. Additional space is required for the additional schema objects as well as the aggregated data.

Considerations When Setting the Start Date

All data in the MOM Reporting database, from the start date set during setup until the aggregation job runs, is summarized on the initial run of the job. This initial aggregation of data can take several hours, depending on the size of the MOM Reporting database.

The aggregation job, BuildAggregations, is scheduled by SQL Server to run one hour after the MOM 2005 DTS job completes. The aggregation job can be re-scheduled from the server's SQL Server Agent node in SQL Enterprise Manager. The MOM 2005 DTS job, SystemCenterDTSPackageTask, is run as a Windows Scheduled Task, not a SQL job.

The time required for the aggregation job to run varies greatly, depending on factors such as the size of the MOM Reporting database, capabilities and other loads on the system hosting the reporting database. For general guidance, expect that it will take one to two hours for the aggregation job to run for every 100 GB of data aggregated. During this time, the scheduled MOM 2005 DTS job cannot run, potentially delaying the transfer of data from the MOM 2005 to the MOM 2005 Reporting database. If this occurs, the MOM 2005 DTS job will run when it is next scheduled. Subsequent runs of the aggregation job summarize only new data, requiring significantly less time.

If the time to run the initial aggregation job and the potential delay of the MOM 2005 DTS job conflicts with your business requirements, you can use one of the following options for installing the SRP:

• Initiate the SRP installation when it won't be an inconvenience for your organization, potentially a weekend.

• During the installation of the SRP, set the start date to the current date; data will not be aggregated by the initial run of the aggregation job. Then run the aggregation job manually with smaller blocks of time. For instructions about how to do this, see How to Manually Control the Aggregation of Data in the Reporting Database.

Installation of the Summary Reporting Pack

While it is unlikely that installing the SRP will cause issues in the MOM Reporting database, it is recommended that you back up the MOM Reporting database before you install the SRP. For instructions about how to do this, see How to Back Up and Restore the Summary Reporting Pack.

To run the SRP installation you need to:

1. Have the following rights.

   • Database owner (DBO) of the MOM 2005 Reporting database, SystemCenterReporting.

   • Write access to the directory where MOM 2005 is installed.

2. Add the directory path where dtsrun.exe is installed, such as "C:\Program Files\Microsoft SQL Server\80\Tools\Binn", to the system path variable.

There are two methods to install the SRP, user interface and the command line. Following are the procedures for these.

How to Install the Summary Reporting Pack Using the User Interface

Use the following procedure to install the SRP using its setup user interface.

To install the SRP using the setup user interface

1. Double-click the MOM 2005 Summary Reporting Pack.msi file to expand the MOM 2005 Summary Reporting Pack installation file and documentation.

2. On the computer that hosts your MOM 2005 SP1 or later Reporting database, double-click the SummaryReportingPack.msi file to start the MOM 2005 Summary Reporting Pack installation.

3. On the Welcome page, click the Next button.

4. On the End-User License Agreement (EULA) page, read the EULA, select I Agree, and click Next.

5. On the Summary Reporting Settings page, set the First Day of the Week value, typically Sunday or Monday. The SRP uses this date for the start of the week for weekly reports.

   Note

   You can re-configure the SRP settings after installation; see How to Change the Summary Reporting Pack Settings Using SQL Statements.

6. Set the Start Date. (Performance and alert data collected by the MOM clients from this date to the present will be aggregated by the SRP.) Click Next.

   Caution

   On the initial run of the aggregation job, all data in the reporting database from the start date to when the aggregation job runs is summarized. This could take several hours depending on the size of the reporting database. For more information, see Considerations When Setting the Start Date.

7. On the Groomings Settings page, set the number of days to retain aggregated numeric sample (performance counter) data and alert summary data. The default is 395 days.

8. On the Ready to Install page, click Next. The Installing MOM 2005 Summary Reporting Pack page should display and provide installation status.

9. When the Finish page appears, click Finish.

10. Launch the MOM 2005 Administrator Console, run the Import/Export Management Pack Wizard, and import the SRP reports from where you expanded the SPR download.

How to Install the Summary Reporting Pack Using the Command Line

You can install the SRP from the command line and customize it to your organization's needs by setting the properties described in the following table.

Important

To run the MOM 2005 Summary Reporting Pack installation from the command line, you must first double-click the MOM 2005 Summary Reporting Pack.msi file to expand the MOM 2005 Summary Reporting Pack installation file (SummaryReportingPack.msi) and documentation.

Property	Description	Default Value
Week_Start_Date	The day of the week from which weekly reports start, such as if your organization wants weekly reports to start on Monday. Valid values 1: Sunday 2: Monday 3: Tuesday 4: Wednesday 5: Thursday 6: Friday 7: Saturday	1
Summary_Start_Date	The SRP aggregates data reported by clients from this date to the present. Format: YYYY-MM-DD	2000-01-01
Samples_Groom_Days	The number of days of summarized numeric (performance counter) data to retain. Valid values: Any integer greater than 7.	395
Alerts_Groom_Days	The number of days of summarized alert data to retain. Valid values: Any integer greater than 7.	395

The following example:

%WinDir%\System32\msiexec.exe /i "C:\Program Files\MOM 2005\MOM 2005 Summary Reporting Pack\SummaryReportingPack.msi" Summary_Start_Date=2005-02-01 Week_Start_Date=2

would perform the following actions:

1. Run the Windows Installer (msiexec.exe)

2. Use the installation program of C:\Program Files\MOM 2005\MOM 2005 Summary Reporting Pack\SummaryReportingPack.msi to install the SRP.

3. Set the Summary_Start_Date ** property to February 1, 2005.

4. Set the ** Week Start ** date to Monday.

5. The installation retains the defaults for properties not otherwise set.

Note

If you prefer not to launch the SRP setup user interface, you can add the Windows Installer ** /qn ** command-line option to the preceding example. See the Windows Installer reference on https://go.microsoft.com/fwlink/?linkid=7771 for a complete list of Windows Installer command-line options.

The final step is to launch the MOM 2005 Administrator Console, run the Import/Export Management Pack Wizard, and import the SRP reports from the <MOM 2005 installation directory>\Summary Reporting Pack\Reports directory.

Re-installation of the Summary Reporting Pack

When you remove the Summary Reporting Pack from your computer, the tables that contain your aggregated data and their associated views are not removed. After you remove the Summary Reporting Pack and before you re-install it, you must manually remove the following views:

• SC_Daily_AlertFact_View

• SC_Daily_AlertFact_Detail_View

• SC_Weekly_AlertFact_View

• SC_Weekly_AlertFact_Detail_View

• SC_Weekly_CounterDataFact_View

And you must manually remove the following tables:

• SC_Daily_AlertFact_Table

• SC_Daily_AlertFact_Detail_Table

• SC_Weekly_AlertFact_Table

• SC_Weekly_AlertFact_Table

• SC_Daily_CounterDataFact_Table

• SC_Weekly_CounterDataFact_Table

After you have manually removed these tables and views, you can successfully re-install the Summary Reporting Pack.

Explanation of the Reports Provided by the Summary Reporting Pack

SRP reports are run from the MOM 2005 Reporting Console. The SRP management pack summary reports, such as Exchange, have a similar MOM 2005 report. The difference between them is the SRP reports are based on aggregated data, and therefore the time needed to run them increases minimally even as the reporting database increases significantly in size.

The SRP reports provide analysis on both a daily and a weekly basis, aiding in performing operational trend analysis of your organization's IT environment. Their names end in (Daily) or (Weekly), helping to differentiate them from the MOM 2005 provided reports.

Following is an example of an SRP Performance Counter Analysis (Daily) report.

The preceding report is sorted by date and includes the following:

• Date range (May 1, 2005, through May 20, 2005, although only the first two days are shown in the preceding example)

• The computer group (Windows 2003 DNS servers)

• All computers within the computer group

• The monitored object of the computers (Memory)

• All performance counters for the object

• All instances of the object

The report contains the following columns of performance counter data to aid you in performing trend analysis of your organization's IT environment.

Total Samples for the day

The number of samples provided for a performance counter. The preceding example report indicates that the SMX\SMXDC01 system returned 31 values for Available MBytes of hard disk space on May 11, 2005.

Min. Value during day

The lowest value of the samples for the counter, 53 (MB) in the preceding example.

10th Percentile

The value at which 10 percent of the samples are less than or equal to the value. In the above example 10 percent of the samples are less than or equal to 56.

The percentile is a configurable integer with a default of 10, minimum of 1, and maximum of 49. For information about configuring the Lowest Percentile, see Low Percentile Value in How to Change the Summary Reporting Pack Settings Using SQL Statements.

Avg. Value during day

The sum of the samples for the performance counter divided by the Total Samples—114.26 (MB) in the preceding example.

90th Percentile

The value at which 90 percent of the samples are less than or equal to the value. In the preceding example, 90 percent of the samples are less than or equal to 333.

The percentile is a configurable integer with a default of 90, minimum of 51, and maximum of 99. For information about configuring the Highest Percentile, see High Percentile Value in How to Change the Summary Reporting Pack Settings Using SQL Statements.

Max. Value during day

The highest value of the samples for the counter, 342 (MB) in the preceding example.

Highest Avg. Value in 1/2 period

To compute the Highest Average Value (HAV), each day is divided into 48 half-hour intervals, on the whole and half hour. The average value for the counter for each interval is calculated. The HAV is the largest of these average values, 338.50 (MB) in the preceding example.

# of 1/2 hr. periods with Highest Avg. Value

The number of half-hour periods matching the Highest Average Value—1 for the preceding example.

Time period of Highest Avg. Value

The half-hour period when the HAV first occurred. In the preceding example, the HAV occurred in the 16:30 to 16:59 time period.

Following is an example of an SRP Number of Alerts Analysis (Daily) report.

The preceding report includes the following:

• Date range (May 1, 2005, through June 23, 2005.)

• The computer group (Windows 2003 DNS servers)

• All computers within the computer group

To aid you in performing trend analysis of your organization's IT environment, the report provides the columns of summarized alert data defined in the following table.

Total # of Resolved Alerts during day

The number of alerts resolved on a given day. The preceding example report indicates that two alerts were resolved on May 11, 2005, for the SMX\SMXDC02 system.

Total # of Un-resolved Alerts during day

The number of alerts with a status of other than resolved for the day—0 in the preceding example.

Highest # of Resolved Alerts in 1/2 hr. period

The largest number of alerts resolved during a half-hour period for the day—1 in the preceding example.

Highest # of Un-resolved Alerts in 1/2 hr. period

The largest number of alerts remaining unresolved during a half-hour period for the day—0 in the preceding example.

# of 1/2 hr. periods with Highest Resolved Alerts

The number of half-hour periods in the day having the same value for the Highest # of Resolved Alerts—2 in the preceding example.

# of 1/2 hr. periods with Highest Un-resolved Alerts

The number of half-hour hour periods in the day having the same value for the Highest # of Un-resolved Alerts—0 in the preceding example.

Time Period of Highest # Resolved Alerts

The time of the first half-hour period, on the whole and half hour, for the day with the largest number of resolved alerts. In the preceding example, this occurred in the 14:00 through 14:29 time period.

Time period of Highest # Un-resolved Alerts

The time of the first half-hour period, on the whole and half hour, for the day with the largest number of unresolved alerts. In the preceding example, this is blank because no unresolved alerts occurred.

Reports Provided by the Summary Reporting Pack

Following are the reports provided by the SRP.

Alert reports

Report Name
Alert Resolution Time Analysis
Alert Resolution Time (Daily)
Alert Resolution Time (Weekly)
Number of Alerts Analysis (Daily)
Number of Alerts Analysis (Weekly)
Number of Alerts Detailed Analysis (Daily)
Number of Alerts Detailed Analysis (Weekly)

Performance counter reports, not management-pack specific

Report Name
Performance Counter Analysis (Daily)
Performance Counter Analysis (Weekly)

Management pack performance counter reports

Management Pack	Report Name
Exchange	Disk Usage (Daily)
Exchange	Disk Usage (Weekly)
Exchange	IMAP4 Usage (Daily)
Exchange	IMAP4 Usage (Weekly)
Exchange	Information Store Usage (Daily)
Exchange	Information Store Usage (Weekly)
Exchange	Mailbox Store Usage (Daily)
Exchange	Mailbox Store Usage (Weekly)
Exchange	MTA Usage (Daily)
Exchange	MTA Usage (Weekly)
Exchange	POP3 Usage (Daily)
Exchange	POP3 Usage (Weekly)
Exchange	SMTP Usage (Daily)
Exchange	SMTP Usage (Weekly)
Exchange	Web Mail Usage (Daily)
Exchange	Web Mail Usage (Weekly)
AD	ADMP DC Disk Space / Servers (Daily)
AD	ADMP DC Disk Space / Servers (Weekly)
AD	AD Replication Latency (Daily)
AD	AD Replication Latency (Weekly)
Base Operating System	Operating System Performance (Daily)
Base Operating System	Operating System Performance (Weekly)
MOM	MOM Performance Analysis (Daily)
MOM	MOM Performance Analysis (Weekly)

Detailed descriptions of the management pack reports can be found in the guides for the MOM 2005 management packs at https://go.microsoft.com/fwlink/?linkid=45768.

To create custom reports with the data aggregated by the SRP, see the Technical Reference section of this document for details about the tables added to the reporting database by the SRP.

Maintenance Tasks for the Summary Reporting Pack

The topics in this section explain how you can perform the following tasks:

• Change Summary Reporting Pack settings using SQL statements

• Manually control data aggregation

• Groom non-aggregated data

• Back Up and Restore the SRP

• Troubleshoot the SRP

How to Change the Summary Reporting Pack Settings Using SQL Statements

The Summary Reporting Pack settings can be changed after installation by using the following procedure.

To change the settings of the Summary Reporting Pack after installation

1. Run SQL's Query Analyzer for the system running the MOM 2005 reporting database, SystemCenterReporting.

2. Select SystemCenterReporting from the list of available databases.

3. Enter the SQL statement for the setting, provided below, in the Query Analyzer Query window.

4. Run the statement.

Following are the Summary Reporting Pack settings that can be changed after installation by using SQL queries.

Start Date of Weekly Cycle

Setting	Comments
Description	The day of the week from which weekly reports start, such as if your organization wants weekly reports to start on Monday. Replace <value> with a non-quoted valid value, such as 2.
Value type	Integer
Valid Values	1: Sunday 2: Monday 3: Tuesday 4: Wednesday 5: Thursday 6: Friday 7: Saturday
Default value	1
SQL query	EXEC p_SetWeekStartDay <value>

Start Date

Setting	Comments
Description	The SRP will aggregate data reported by clients from this date to the present. Replace YYYY-MM-DD with a valid value retaining the single quote, such as '2000-01-01'.
Value type	Date
Valid Values	Any past date in the YYYY-MM-DD format. Caution Must be the first day of the week as set by the start date of Weekly Cycle, such as a Monday in the preceding example. Note It's a good policy to set a start date no farther in the past than the grooming interval; otherwise, when data is aggregated, those records that are older than the grooming interval are groomed out of the MOM Reporting database.
Default value	2000-01-01
SQL query	EXEC p_SetCutoffDate YYYY-MM-DD

Number of Days to keep samples

Setting	Comments
Description	The number of days of summarized numeric (performance counter) data to retain. Replace <value> with a non-quoted valid value, such as 180.
Value type	Integer
Valid Values	Any integer greater than 7.
Default value	395
SQL query	EXECp_SetSampleGroomDays <value>

Number of Days to keep alerts

Setting	Comments
Description	The number of days of summarized alert data to retain. Replace <value> with a non-quoted valid value, such as 180.
Value type	Integer
Valid Values	Any value greater than 7
Default value	395
SQL query	EXEC p_SetAlertGroomDays <value>

The SRP installation uses the default values for the following settings. They are configurable after installation by using SQL queries.

Low Percentile Value

Setting	Comments
Description	The value at which x percent of the samples are less than or equal to the value, such as 10 percent of the samples being less than or equal to 56. Replace <value> with a non-quoted valid value, such as 20 to signify 20 percent.
Value type	Integer
Valid Values	1 through 49
Default value	10
SQL query	EXEC p_SetLowPercentileValue <value>

High Percentile Value

Setting	Comments
Description	The value at which x percent of the samples are less than or equal to the value, such as 90 percent of the samples being less than or equal to 333. Replace <value> with a non-quoted valid value, such as 80 to signify 80 percent.
Value type	Integer
Valid Values	51 through 99
Default value	90
SQL query	EXEC p_SetHighPercentileValue <value>

Batch Size (during aggregation)

Setting	Comments
Description	The number of records processed in one batch of the aggregation process. Reducing this value helps if aggregation causes the SQL transaction log to reach its maximum size. Replace <value> with a non-quoted valid value, such as 50000.
Value type	Integer
Valid Values	Any value greater than 10
Default value	100000
SQL query	EXEC p_SetGroomBatchSize <value>

How to Manually Control the Aggregation of Data in the Reporting Database

The following procedure enables you to manually control the aggregation of data in the reporting database as of the day the SRP is installed. For information about why you might want to do this, see Considerations when Setting the Start Date.

Use the following bulleted list of instructions to run the three subsequent aggregation commands from the CMD shell:

• Provide the path to dtsrun.exe, installed with SQL Server.

• Provide the path to the *.dts files, installed with the SRP

• Replace YourServer, keeping the double quotes, with the name of the server hosting your SystemCenterReporting database, and where appropriate instance of SQL, such as SQL1\Instance1.

• Replace YYYY-MM-DD, keeping the double quotes, with the Start and End Date for time span you want the aggregation job to encompass, and retaining to the YYYY-MM-DD date format.

Aggregation Command 1: Build Daily Sample (Performance Counter) Aggregations command

<path>\dtsrun.exe /F "<MOM 2005 Reporting installation directory>\Reporting\BuildDailySampleAggregations_AnyDays.dts" /W True /A "ServerName":"8"="YourServer" /A "DatabaseName":"8"="SystemCenterReporting" /A "StartDate":"8"="YYYY-MM-DD" /A "EndDate":"8"="YYYY-MM-DD"

Aggregation Command 2: Build Weekly Sample (Performance Counter) Aggregations command

<path>\dtsrun.exe /F "<MOM 2005 Reporting installation directory>\Reporting\BuildWeeklySampleAggregations_AnyDays.dts" /W True /A "ServerName":"8"="YourServer" /A "DatabaseName":"8"="SystemCenterReporting" /A "StartDate":"8"="YYYY-MM-DD" /A "EndDate":"8"="YYYY-MM-DD"

Aggregation Command 3: Build Alert Aggregations command

<path>\dtsrun.exe /F "<MOM 2005 Reporting installation directory>\Reporting\BuildAlertAggregations_AnyDays.dts" /W True /A "ServerName":"8"="YourServer" /A "DatabaseName":"8"="SystemCenterReporting" /A "StartDate":"8"="YYYY-MM-DD" /A "EndDate":"8"="YYYY-MM-DD"

How to Groom Non-Aggregated Data in the Reporting Database

After you've aggregated the data in your MOM 2005 Reporting database, you might want to groom the older non-aggregated data to reduce the size of the database.

How to Back Up and Restore the Summary Reporting Pack

The MOM 2005 SRP will be backed up and restored by following the existing procedures for backing up and restoring MOM 2005 Reporting. For complete details, see Chapter 5 of the MOM 2005 Operations Guide at https://go.microsoft.com/fwlink/?linkid=47164.

How to Troubleshoot the Summary Reporting Pack

If the SRP installation fails, see the Windows Installer log file, in the Temp directory by default, for details. Information about Windows Installer logging can be found at https://go.microsoft.com/fwlink/?linkid=21877.

If the DTS BuildAggregations job fails, you can restart it from the server's SQL Server Agent node in SQL Enterprise Manager or wait for it to run at its next scheduled time. The failure of the job should not affect the validity of data in the MOM Reporting database.

For general troubleshooting of the SRP, see the server's Management node in SQL Enterprise Manager to obtain information such as the following:

• Status of the SQL Server Agent jobs

• Detail in the SQL Server Logs

Security Considerations for the Summary Reporting Pack

For details on the MOM 2005 Reporting database related to security, see the MOM 2005 Security Guide at https://go.microsoft.com/fwlink/?linkid=33035.

Technical Reference for the Summary Reporting Pack

Following are the tables the SRP adds to the existing MOM 2005 Reporting database, SystemCenterReporting. The tables can be used to create custom reports based on the SRP's aggregated data.

SC_Daily_AlertFact_Table

Description: This table stores daily alert aggregations.

View Name: SC_Daily_AlertFact_View

Level of Detail: One row per day per computer per management group. Each row represents daily alert aggregations for a specific computer.

Column Definitions

Column Name	Data Type	Allow Null Value	Column Description	Groom Column for Table
DateGenerated_PK	Smalldatetime	No	Calendar date. Part of Primary Key.	Yes
Computer_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the Computer Dimension. Represents the computer on which the alerts were raised.	No
ConfigurationGroup_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the ConfigurationGroup Dimension. Represents the management group from which the alerts were raised.	No
TotalResolvedAlerts	Int	Yes	Total Daily Number of resolved alerts.	No
TotalUnresolvedAlerts	Int	Yes	Total Daily Number of unresolved alerts.	No
MinResolutionTime	Numeric(9,1)	Yes	Minimum value of Resolution Time, in minutes, for alerts raised on a given date.	No
MaxResolutionTime	Numeric(9,1)	Yes	Maximum value of Resolution Time, in minutes, for alerts raised on a given date.	No
AvgResolutionTime	Numeric(9,1)	Yes	Average value of Resolution Time, in minutes, for alerts raised on a given date.	No
LowPercentileResolutionTime	Numeric(9,1)	Yes	Low Percentile value of Resolution Time, in minutes, for alerts raised on a given date.	No
HighPercentileResolutionTime	Numeric(9,1)	Yes	High Percentile value of Resolution Time, in minutes, for alerts raised on a given date.	No
HighestNumberResolvedAlerts	Int	Yes	Maximum of Average Number of Resolved Alerts for alerts raised on a given date.	No
HighestNumberUnresolvedAlerts	Int	Yes	Maximum of Average Number of Unresolved Alerts for alerts raised on a given date.	No
sTimeIntervalIDResolvedAlerts	Varchar(8)	Yes	First 30-minute interval where HighestNumberResolvedAlerts was reached.	No
sTimeIntervalIDResolvedAlerts	Varchar(8)	Yes	First 30-minute interval where HighestNumberUnresolvedAlerts was reached.	No
NumberIntervalsOfHighestNumberResolvedAlerts	Int	Yes	Number of 30-minute intervals with the same value as the HighestNumberResolvedAlerts.	No
NumberIntervalsOfHighestNumberUnresolvedAlerts	Int	Yes	Number of 30-minute intervals with the same value of HighestNumberUnresolvedAlerts.	No

Index Definitions

Index Name	Clustered	Unique	Index Column List
SC_Daily_AlertFact_Table_Computer_FK_PK_Index	No	No	Computer_FK_PK (ASC)
SC_Daily_AlertFact_Table_PK	Yes	Yes	DateGenerated (ASC), Computer_FK_PK (ASC), ConfigurationGroup_FK_PK (ASC)

SC_Daily_AlertFact_Detail_Table

Description: This table stores daily alert aggregations per alert level per alert resolution type.

View Name: SC_Daily_AlertFact_Detail_View

Level of Detail: One row per day per computer per alert level per alert resolution type. Each row represents daily alert aggregations for a specific computer.

Column Definitions

Column Name	Data Type	Allow Null Value	Column Description	Groom Column for Table
DateGenerated_PK	Smalldatetime	No	Calendar date. Part of Primary Key.	Yes
Computer_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the Computer Dimension. Represents the computer on which the alerts were raised.	No
ConfigurationGroup_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the ConfigurationGroup Dimension. Represents the management group from which the alerts were raised.	No
AlertLevel_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the AlertLevelDimension. Represents the alert level.	No
AlertResolutionState_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the AlertResolutionStateDimension. Represents the resolution state of the alerts.	No
TotalAlerts	Int	Yes	Total Daily Number of alerts.	No
MinResolutionTime	Numeric(9,1)	Yes	Minimum value of Resolution Time, in minutes, for alerts raised on a given date.	No
MaxResolutionTime	Numeric(9,1)	Yes	Maximum value of Resolution Time, in minutes, for alerts raised on a given date.	No
AvgResolutionTime	Numeric(9,1)	Yes	Average values of Resolution Time, in minutes, for alerts raised on a given date.	No
LowPercentileResolutionTime	Numeric(9,1)	Yes	Low Percentile daily values of Resolution Time, in minutes, for alerts raised on a given date.	No
HighPercentileResolutionTime	Numeric(9,1)	Yes	High Percentile daily values of Resolution Time, in minutes, for alerts raised on a given date.	No
HighestNumberAlerts	Int	Yes	Maximum of Average Number of alerts raised on a given date.	No
sTimeIntervalID	Varchar(8)	Yes	First 30-minute interval where HighestNumberAlerts was reached.	No
NumberIntervalsOfHighestNumberAlerts	Int	Yes	Number of 30-minute intervals with the same value as the HighestNumberAlerts.	No

Index Definitions

Index Name	Clustered	Unique	Index Column List
SC_Daily_AlertFact_Detail_Table_Computer_FK_PK_Index	No	No	Computer_FK_PK (ASC)
SC_Daily_AlertFact_Detail_Table_PK	Yes	Yes	DateGenerated (ASC), Computer_FK_PK (ASC), ConfigurationGroup_FK_PK (ASC), AlertLevel_FK_PK (ASC), AlertResolutionState_FK_PK (ASC)

SC_Weekly_AlertFact_Table

Description: This table stores weekly alert aggregations.

View Name: SC_Weekly_AlertFact_View

Level of Detail: One row per week per computer per MG. Each row represents weekly alert aggregations on a specific computer.

Column Definitions

Column Name	Data Type	Allow Null Value	Column Description	Groom Column for Table
WeekGenerated_PK	Smalldatetime	No	Calendar date. Part of Primary Key.	Yes
Computer_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the Computer Dimension. Represents the computer on which the alerts were raised.	No
ConfigurationGroup_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the ConfigurationGroup Dimension. Represents the management group from which the alerts were raised.	No
TotalResolvedAlerts	Int	Yes	Total number of alerts resolved in a week.	No
TotalUnresolvedAlerts	Int	Yes	Total number of alerts unresolved in a week.	No
MinResolutionTime	Numeric(9,1)	Yes	Minimum weekly value for Resolution Time, in minutes, for alerts raised on a given date.	No
MaxResolutionTime	Numeric(9,1)	Yes	Maximum weekly value for Resolution Time, in minutes, for alerts raised on a given date.	No
AvgResolutionTime	Numeric(9,1)	Yes	Average weekly value for Resolution Time, in minutes, for alerts raised on a given date.	No
LowPercentileResolutionTime	Numeric(9,1)	Yes	Low Percentile weekly value for Resolution Time, in minutes, for alerts raised on a given date.	No
HighPercentileResolutionTime	Numeric(9,1)	Yes	High Percentile weekly value for Resolution Time, in minutes, for alerts raised on a given date.	No
HighestNumberResolvedAlerts	Int	Yes	Maximum of Average Number of Resolved Alerts for a given date.	No
HighestNumberUnresolvedAlerts	Int	Yes	Maximum of Average Number of unresolved Alerts for a given date.	No
NumberIntervalsOfHighestNumberResolvedAlerts	Int	Yes	Number of 30-minute intervals with the same value as the HighestNumberResolvedAlerts.	No
NumberIntervalsOfHighestNumberUnresolvedAlerts	Int	Yes	Number of 30-minute intervals with the same value as the HighestNumberUnresolvedAlerts.	No

Index Definitions

Index Name	Clustered	Unique	Index Column List
Weekly_AlertFact_Computer_FK_PK_Index	No	No	Computer_FK_PK (ASC)
SC_ Weekly _AlertFact_Table_PK	Yes	Yes	WeekGenerated_PK (ASC), Computer_FK_PK (ASC), ConfigurationGroup_FK_PK (ASC)

SC_Weekly_AlertFact_Detail_Table

Description: This table stores weekly alert aggregations per alert level per alert resolution type.

View Name: SC_Weekly_AlertFact_Detail_View

Level of Detail: One row per week per computer per MG per alert level per alert resolution type. Each row represents weekly alert aggregations on a specific computer.

Column Definitions

Column Name	Data Type	Allow Null Value	Column Description	Groom Column for Table
WeekGenerated_PK	Smalldatetime	No	Calendar date. Part of Primary Key.	Yes
Computer_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the Computer Dimension. Represents the computer on which the alerts were raised.	No
ConfigurationGroup_FK_PK	Bigint	No	Foreign key to the ConfigurationGroup Dimension. Represents the management group from which the alerts were raised.	No
AlertLevel_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the AlertLevelDimension. Represents the alert level.	No
AlertResolutionState_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the AlertResolutionStateDimension. Represents the resolution state of the alerts.	No
TotalAlerts	Int	Yes	Total number of alerts for a week	No
MinResolutionTime	Numeric(9,1)	Yes	Minimum weekly values of Resolution Time, in minutes, for alerts raised on a given date.	No
MaxResolutionTime	Numeric(9,1)	Yes	Maximum weekly value of Resolution Time, in minutes, for alerts raised on a given date.	No
AvgResolutionTime	Numeric(9,1)	Yes	Average weekly values of Resolution Time, in minutes, for alerts raised on a given date.	No
LowPercentileResolutionTime	Numeric(9,1)	Yes	Low Percentile weekly values of Resolution Time, in minutes, for alerts raised on a given date.	No
HighPercentileResolutionTime	Numeric(9,1)	Yes	High Percentile weekly values of Resolution Time, in minutes, for alerts raised on a given date.	No
HighestNumberAlerts	Int	Yes	Maximum of Average Number of alerts for alerts raised on a given date.	No
NumberIntervalsOfHighestNumberAlerts	Int	Yes	Number of 30-minute intervals with the same value as the HighestNumberAlerts.	No

Index Definitions

Index Name	Clustered	Unique	Index Column List
Daily_AlertFact_Detail_Computer_FK_PK _Index	No	No	Computer_FK_PK (ASC)
SC_Daily_AlertFact_Detail_Table_PK	Yes	Yes	DateGenerated_PK (ASC), Computer_FK_PK (ASC), ConfigurationGroup_FK_PK (ASC), AlertLevel_FK_PK (ASC), AlertResolutionState_FK_PK (ASC)

SC_Daily_CounterDataFact_Table

Description: This table stores daily performance counter aggregations.

View Name: SC_Daily_CounterDataFact_View

Level of Detail: One row per day per computer per management group. Each row represents daily counter aggregations on a specific computer.

Column Definitions

Column Name	Data Type	Allow Null Value	Column Description	Groom Column for Table
DateGenerated_PK	Smalldatetime	No	Calendar date. Part of Primary Key.	Yes
Computer_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the Computer Dimension. Represents the computer on which the alerts were raised.	No
CounterDetail_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the CounterDetail Dimension. Represents the counter detail for all daily samples.	No
ConfigurationGroup_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the ConfigurationGroup Dimension. Represents the management group from which the alerts were raised.	No
TotalSamples	Int	Yes	Total number of samples for a day.	No
MinValue	Float	Yes	Minimum performance counter value among the samples for a given date.	No
MaxValue	Float	Yes	Maximum performance counter value among the samples for a given date.	No
AvgValue	Float	Yes	Average performance counter value among the samples for a given date.	No
LowPercentileValue	Float	Yes	Low Percentile value among the samples for a given date.	No
HighPercentileValue	Float	Yes	High Percentile value among the samples for a given date.	No
HighestAvgValue	Float	Yes	Maximum of average values for each 30-minute interval for a given date.	No
sTimeIntervalID	Varchar(8)	Yes	First 30-minute interval where HighestAvgValue was reached.	No
NumberOfHighestAvgValues	Int	Yes	Number of 30-minute intervals where HighestAvgValue was reached.	No

Index Definitions

Index Name	Clustered	Unique	Index Column List
Daily_CounterDataFact_Computer_FK _PK_Index	No	No	Computer_FK_PK (ASC)
SC_Daily_CounterDataFact_PK	Yes	Yes	DateGenerated_PK (ASC), Computer_FK_PK (ASC), CounterDetail_FK_PK (ASC), ConfigurationGroup_FK_PK (ASC)

SC_Weekly_CounterDataFact_Table

Description: This table stores weekly sample aggregations.

View Name: SC_Weekly_CounterDataFact_View

Level of Detail: One row per week per counter. Each row represents weekly counter aggregations on a specific computer.

Column Definitions

Column Name	Data Type	Allow Null Value	Column Description	Groom Column for Table
WeekGenerated_PK	Smalldatetime	No	Calendar week. Part of Primary Key.	Yes
Computer_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the Computer Dimension. Represents the computer on which the alerts were raised.	No
CounterDetail_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the CounterDetail Dimension. Represents the counter detail for all daily samples.	No
ConfigurationGroup_FK_PK	Bigint	No	Part of Primary Key. Foreign key to the ConfigurationGroup Dimension. Represents the management group from which the alerts were raised.	No
TotalSamples	Int	Yes	Total number of samples for a week.	No
MinValue	Float	Yes	Minimum performance counter value among the samples for a given week.	No
MaxValue	Float	Yes	Maximum performance counter value among the samples for a given week.	No
AvgValue	Float	Yes	Average performance counter value among the samples for a given week.	No
LowPercentileValue	Float	Yes	Low Percentile value among the samples for a given week.	No
HighPercentileValue	Float	Yes	High Percentile value among the samples for a given week.	No
AvgPeakValue	Float	Yes	Average Peak value among the samples for a given week.	No
HighestAvgValue	Float	Yes	Maximum average value among the samples for a given week.	No
sTimeIntervalID	Varchar(8)	Yes	List of up to three 30-minute intervals where HighestAvgValue was reached.	No
NumberOfHighestAvgValues	Int	Yes	Number of 30-minute intervals where HighestAvgValue was reached.	No

Index Definitions

Index Name	Clustered	Unique	Index Column List
Weekly_CounterDataFact_ Computer_FK _PK_Index	No	No	Computer_FK (ASC)
SC_Weekly_CounterDataFact_Table_PK	Yes	Yes	WeekGenerated (ASC), Computer_FK (ASC) ), CounterDetail_FK _PK(ASC), ConfigurationGroup_FK_PK (ASC)