BI-ETL runtime monitoring

For everything, supervision is an effective way to improve itself, as is BI. In my personal experience, BI supervision can be divided into two types (Welcome to the discussion ):Runtime supervision(Runtime Monitoring)AndMonitoring of data warehouse health status(DW Healthy Monitoring):

1. Runtime supervision

The so-called runtime supervision refers to the process of monitoring data from the data source to the data warehouse. In general, it is to supervise the ETL execution process. I believe that the vast majority of BI systems have this function. The difference lies only in the monitoring method and the details of monitoring information.

2. Data Warehouse health monitoring

The so-called data warehouse health monitoring is actually monitoring the various indicators of the data warehouse system: the number of table records, storage size, Shard size, index usage, query execution efficiency for data warehouses, and other information. For this monitoring item, for BI projects that people are exposed, this function is rarely implemented in a system.

This article only discusses runtime monitoring, and will discuss an article about data warehouse health monitoring later. As my ETL tool is based on Microsoft SSIS, the monitoring implementation method discussed is limited to Microsoft's SSIS.

Broadly speaking, there are three methods to monitor ETL runtime:

① Implementation method based on SSIS event processor

② Implementation method based on SSIS log provider

③ SSISDB-based implementation (available only after SQL Server 2012)

Event-based processor

As shown in, an SSIS package has four tabs: control flow, data flow, event processor, and package browser:

650) this. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/F1/wKioL1RcjHXwBsEgAAC8N4k3HZY856.jpg "title =" 1.png" alt = "wKioL1RcjHXwBsEgAAC8N4k3HZY856.jpg"/>

We know that all tasks and packages placed on the control flow are called executable, and the event processor is specific to an executable. In addition, executable can form hierarchical relationships. For example, if an ExecuteSQL Task is included in the ForLoop container, it will form the following hierarchical relationships:

650) this. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/F2/wKiom1RcjCOhPcVTAACGu-mokMg003.jpg "title =" · .png "alt =" wKiom1RcjCOhPcVTAACGu-mokMg003.jpg "/>

For hierarchical executable, the event processor has a very important feature: the processor that throws and triggers a response from its own layer. Based on this feature, we can only add an event processor to the Package, so that all tasks in the Package can be captured.

Below are several frequently used events: 650) this. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/F2/wKiom1Rci-vDkp09AAGY_OJIUXo116.jpg "title =" 1.png" alt = "wKiom1Rci-vDkp09AAGY_OJIUXo116.jpg"/>

When designing custom logs, we can simply use OnError to record information in task errors, and use OnPreExecute to obtain information (such as the start time) at the beginning of the task ), use OnPostExecute to obtain the information at the end of the task (such as the end time), and then use the RowCount task to obtain the information about the number of rows processed to construct a simple log table, for example, the following:

650) this. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/F1/wKioL1RcjKKDSNFuAAC2Mcp0F44376.jpg "title =" · .png "alt =" wKioL1RcjKKDSNFuAAC2Mcp0F44376.jpg "/>

Log-based providers

Comparatively speaking, using the log provider provided by SSIS is a simple way to implement runtime monitoring. Right-click the SSIS package and select Logging from the pop-up menu. The following interface is displayed:

1. The Containers interface allows us to select the executabe for monitoring its operation

2. In the Providers and Logs interface, we can select the built-in log Provider. Generally, we choose "SSISlog Provider for SQL Server"

If this provider is selected, SSIS automatically writes logs to a table named sysssislog, and the table must already exist. SSIS does not automatically create the table. Fortunately, this table exists in the system table of the msdb system database. We can create this table in another database based on the structure of this table, or directly use this database.

1. On the Details page, select the event to be monitored.

650) this. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/F2/wKiom1RcjFigmW6cAAJJlBNa6KU581.jpg "title =" · .png "alt =" wKiom1RcjFigmW6cAAJJlBNa6KU581.jpg "/>

The following is an actual example:

650) this. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/F2/wKiom1RcjHrDvvlgAANbN3eQpes696.jpg "title =" 1.png" alt = "wKiom1RcjHrDvvlgAANbN3eQpes696.jpg"/>

BI-ETL runtime monitoring