Triggers and their Use for Incremental Updating


This segment also concerns databases that support Incremental Updating, for which the Incremental Updating segment of the Create Cube From Relational Source dialog box will be active (see figure below)—specifically the creation of additional tables and Triggers to support Incremental Updates. Triggers, as you will recall, are created precisely for this reason.

 

Warning


You must be aware of the implications: your underlying database will be affected, and your Administrator should make the determination of how Triggers will affect your application.

 

 

 

OLAP Exchange creates 3 temporary additional tables in the underlying relational database when you check 'Support Incremental Updating' checkbox in the Create Cube From Relational Source dialog box (preceding figure). The 3 tables are:

[1]   OLAP_EXCHANGE_DIMMAP;

[2]   OLAP_EXCHANGE_FACT; and

[3]   OLAP_EXCHANGE_META)

 
 

Below is a description of the tables above:

 

PowerOLAP OLAP Exchange creates Triggers in the source tables that populate these latter two tables, OLAP_EXCHANGE_FACT and OLAP_EXCHANGE_META, which are then used to populate PowerOLAP cubes. The Synchronization method determines how often the data will be pulled out of these tables into PowerOLAP. At the point the data is pulled out of the holding tables, the data is purged.

OLAP DIMMAP is not affected by the creation of Triggers.

The term Trigger is defined as a special form of a stored procedure that is carried out automatically when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.

In PowerOLAP's case, the Triggers are used to record a history of changes to source tables in the database. When Synchronization is requested, it becomes a simple procedure of importing this recorded history. The Triggers generate the history by adding entries into the two tables, (OLAP_EXCHANGE_FACT and OLAP_EXCHANGE_META) that reflect what was changed in the database.

Each Trigger can be compound, meaning that it can perform multiple actions based on any number of conditions (a single Trigger can insert any number of entries into the history). The more complex the Dimension, the more complex the Trigger becomes. This segment is not concerned with the Trigger SQL syntax or internal structure of what the Trigger must do in order to translate changes in the physical database world to changes in the logical PowerOLAP® world. This is simply an overview of what the Trigger naming conventions are and where the Triggers are created.

 
 

The Triggers generated by PowerOLAP® come in sets of 3's (one for Insert, Update, and Delete), and are generated on the source tables that map to the following logical objects:

Notes


This is an exception to the above rule. Only the Insert trigger is generated for Hierarchies; there is NO Update or Delete trigger.

 
 
 
 

Naming Convention (Triggers)

The following naming convention is used for all the triggers:

tr

for trigger

OE

for OLAP Exchange

Nnn

where nnn represents the internal PoweOLAP ID of the objects the trigger is acting upon (helps uniquely identify the trigger)

F,M,A,H  

for Fact Data, Member, Alias, or Hierarchy

i,u,d

for insert, update, delete
 

 

 

 

Triggers

The following 3 triggers are generated on the Measures Dimension's source table. These triggers look for any new records added, records deleted, or changes to any of the table fields that were chosen as Members for this Dimension (i.e., representing the Fact Data for the Dimension):

tr_OE_nnn_F_i

tr_OE_nnn_F_u

tr_OE_nnn_F_d

 

These 3 triggers are generated on any Auxiliary Dimension's linked source table (but only on the last source table in the link). These triggers look for new records added, records deleted, or changes to any of the table fields that were chosen as Members for this Auxiliary Dimension.

tr_OE_nnn_M_i

tr_OE_nnn_M_u

tr_OE_nnn_M_d

 

These 3 triggers are generated on any Auxiliary Dimension's linked source table (but only on the last source table in the link). These triggers look for new records added, records deleted, or changes to any of the table fields that were chosen to belong to an Alias Group for this Dimension:

tr_OE_nnn_A_i

tr_OE_nnn_A_u

tr_OE_nnn_A_d

 

This trigger is generated on any Auxiliary Dimension's linked source table (but only on the last source table in the link). This trigger looks for new records added that affect the table fields that were chosen to belong to the Hierarchy for this Dimension:

tr_OE_nnn_H_i

 
 
 
 

Rules

[1]  The Alias and Hierarchy triggers are optional and will not be generated unless these objects were actually created during the creation of the Cube.

[2]  All the triggers may end up on the same source table if the Cube has an Auxiliary Dimension that points back to the Measures Dimensions source table.

[3]  All the triggers will be deleted if for any reason creation of the Cube fails.

[4]  The triggers will be deleted if the Cube in PowerOLAP is deleted.

 
 
 
 

Viewing the Triggers Created

Below, we will examine the tables and triggers created using the Northwind example in SQL Server:

1.   Launch SQL Server Managerment Studio.

2.   Expand Northwind database.

3.   Click on the Tables—a list of the available tables in the database selected is displayed on the Summary window.

4.   Expand Tables folder.

5.   Expand Order Details table and then expand Triggers. A listing of triggers is shown as follows:

6.   Click on the trigger to view its content in the Summary window.


Important


If you are repeatedly recreating the database, then prior to recreating OLAP Exchange®, make sure that you delete the tables and triggers.