KBA45011:
| Knowledge Base Article: | 45011 |
| Applicable Versions: | N-compass 3.0 and later, Report Manager | N-compass 3.2 |
| Date Created/Updated: | October 12, 2011 |
Introduction
The N-central to Report Manager | N-compass communication is accomplished by a two-step process that requires both steps to complete in order to have accurate data in your reports. If you are noticing inaccurate data in your Report Manager | N-compass reports, one of the two below processes are likely not completing successfully.
During the first step, N-central will export data to the Report Manager | N-compass server via port 1433 in the form of a raw SQL dump of data. This data goes into the ODS database on your Report Manager | N-compass server. This is where raw data is held.
By default, the N-central server will retain up to 100 days of Aggregated data as well as up to 3 days of detailed data (Backup Exec, Windows Event Log and EDF services). When Report Manager | N-compass is enabled, N-central will retain all data until it can be exported. If there is an issue with the Data export, all data will be preserved until it can be exported. This is to ensure that there is never a gap of data in the Report Manager | N-compass database.
In order to prepare the raw data to be reported on, aggregations and calculations must be made in order to optimize the reporting process. This is done during the second step of the process called the ETL. Once the export completes, an ETL is queued to run. An ETL is an industry-standard term short for Extract, Transform, and Load. More information can be found here: http://en.wikipedia.org/wiki/Extract,_transform,_load
The ETL is a series of DTS packages that contain collections of pre-defined SQL statements are applied to the ODS, migrating the information into the Warehouse database. There are roughly 50 DTS packages that run during an ETL.
Reports run in Report Manager | N-compass are pulling data from the Warehouse database; if the ETL does not complete or fails somewhere along the way, you may have missing or corrupt data in your reports.
During the ETL, the Transaction Logs of the Staging and Warehouse databases can grow considerably; this is normal. This is why we typically recommend 1-1.5x the size of the ODS database in free space. This allows the Staging database to have room to grow during the hourly ETL process. During the last step of the ETL, the Staging Database is cleared and SQL will perform maintenance (a Shrink) on the Log files that have grown to return them to their default size of 1024kb.
The Data Exports are run on an hourly basis and will not run during periods of N-central maintenance (ie. backups or daily maintenance periods). Once the Export completes, it will call on SQL to begin an ETL immediately thereafter. This would explain why you will see regular activity on your Report Manager | N-compass server. To disable the Exports and ETLs from running, you simply have to stop the Maintenance Service on the Report Manager | N-compass machine.