Foglight for SQL Server 5.7.5.41 - PI Schema Reference Guide

Chat now with support

pass_batch_dim pass_change_tracking_item pass_ct_constraints_state pass_ct_database_properties_state pass_ct_disks_state pass_ct_file_group_state pass_ct_files_state pass_ct_network_interface_state pass_ct_partitions_state pass_ct_swap_space_allocation_state pass_ct_user_account_state pass_ct_version_info_state pass_general_config pass_file_dim pass_instance_dim pass_instance_lock_fact pass_instance_stat_fact pass_instance_wevent_fact pass_object_dim pass_plan_dim pass_plan_syntax_history pass_request_lock_fact pass_request_stat_fact_1m pass_syntax_dim pass_unique_file_io_stat_fact pass_unique_lock_fact pass_unique_object_io_stat_fact pass_unique_stat_fact pass_unique_wevent_fact pass_wait_event_dim

SQL PI Repository Cold Backup Procedure

SSAS SQL PI Repository Schema

About the SSAS SQL PI repository Connecting to the SSAS SQL PI repository General tables Performance Metric (Fact) tables Dimension tables Change Tracking Data Table Data dictionary

pa_repository ssas_instance_dim ssas_general_config ssas_instance_stat_fact ssas_instance_object_fact ssas_instance_perfmon_fact ssas_unique_stat_fact ssas_unique_object_fact ssas_sessions_stat_fact ssas_sessions_object_fact ssas_sessions_lock_fact ssas_command_dim ssas_change_tracking_item ssas_ct_devices_state ssas_ct_disks_state ssas_ct_network_interface_state ssas_ct_schema_state ssas_ct_server_properties_state ssas_ct_resources_state

General tables

General information related to the repository is stored in:

•

pa_repository. Repository Schema information.

•

ssas_instance_dim. All monitored instances in the repository.

•

ssas_general_config. Configuration used in the PI engine per monitored instance.

Performance Metric (Fact) tables

The Fact tables hold the OLAP cube of the monitored instance performance data. There are several categories that define the data that a Fact table holds, and (as result) the table name:

•

Aggregation Level. The resolution to which the collected data is grouped.

Table 33. Aggregation Level
Level	Description	Name
Instance	The entire monitored instance level	ssas_instance_..._fact_...
Unique combination	Top n unique combinations of dimensions that assemble the cube	ssas_unique_..._fact_...
Session	A more detailed grouping of the unique combinations with additional identifiers of the running session	ssas_sessions_..._fact_...

•

Data Type. The type of performance metrics saved in the table.

Table 34. Data Type
Type	Name
Performance metrics	ssas_{level}_stat_fact_...
Object activity	ssas_{level}_object_fact_...
Lock events	ssas_{level}_lock_fact_...
Windows Perfmon	ssas_{level}_perfmon_fact_...

•

Retention. The granularity of the data and the period for which the data is saved (age), as derived from the retention policy.

Table 35. Retention
Granularity	Age	Name
1 minute	6 hours	ssas_{level}_{type}_fact_1m_<YYYYMMDDHH>00 NOTE: The tables in the 1m granularity level are partitioned, meaning that a whole old table is dropped instead of deleting data in the table.
15 minutes	3 days	ssas _{level}_{type}_fact_15m
1 hour	2 weeks	ssas _{level}_{type}_fact_1h
6 hours	30 days	ssas _{level}_{type}_fact_6h
1 day	90 days	ssas _{level}_{type}_fact_1d
1 week	2 years	ssas _{level}_{type}_fact_1w

The suffix of the table indicates the starting time of the data in the table (GMT times), aligned to 6 hours (the age):

•

<YYYYMMDD>0000 (between 00-06)

•

<YYYYMMDD>0600 (between 06-12)

•

<YYYYMMDD>1200 (between 12-18)

•

<YYYYMMDD>1800 (between 18-24)

Dimension tables

The Dimension tables hold additional information to explain some of the dimensions in the Fact tables. Therefore, their values are in the form of IDs.

Table 36. Dimension tables
Dimension	Description	Name
Command	Holds the text of the command which matches the command hash column in the Fact tables.	ssas_command_dim_{suffix}

The suffix of the tables defines the granularity of the data stored in it:

•

_1m_<YYYYMMDDHH>00. Holds data for six hours.

•

Empty. Holds data permanently.

Change Tracking Data

For detailed information, see the following topics:

•

Change Tracking Items

•

Change Tracking Snapshots tables

Change Tracking Items

This table contains all the identified changes per type over time: ssas_change_tracking_item.

Change Tracking Snapshots tables

These tables hold the latest state of a specific type of data, to be compared in each cycle to the state in the monitored instance and identify changes.

Table 37. Change Tracking Snapshots tables
Type	Description	Name
Devices	The system devices in the monitored host	ssas_ct_devices_state
Disks	The system disks in the monitored host	ssas_ct_disks_state
Network interface	The system network interfaces of the monitored host	ssas_ct_network_interface_state
Schema	The schema objects of the monitored instance	ssas_ct_schema_state
Server properties	The properties of the monitored instance	ssas_ct_server_properties_state
Swap space allocation	The system space allocation in the monitored host	ssas_ct_resources_state

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Foglight for SQL Server 5.7.5.41 - PI Schema Reference Guide

General tables

Performance Metric (Fact) tables

Dimension tables

Change Tracking Data