Transaction Log Report

The Transaction Log Report feature allows you to generate an audit report of fact data changes made to a database based on transaction logging. Thus, a Transaction Log Report is available only if you have enabled the Maintain transaction log for the server database.

To generate a Transaction Log Report:

  1. From the Server, open the database.

  2. From the Server View, highlight the database, right-click, and select Transaction Log Report to open the Export Transaction Log Dialog.

exporttransactionlogdialog.png

Figure 1.    Export Transaction Log Dialog

  1. Select the cubes and users for which you wish to generate a Transaction Log Report.

  2. Enter From and To date and time. The default values for From and To are the present time. Within the From field add a new date and time. Leave the To field values as is, to create a report from that time to the present. For entries made in either box use these formats:

MM/DD/YY_hh/mm/ss_[AM or PM]

  1. Click Browse to find a location to save the report, and to give it a File name.

  2. Save your file name and then click OK in the Export Transaction Log dialog.

  1. You will be prompted with the following message when the Transaction Log Report is completed. Click OK to dismiss the dialog.

transactionlogreportprompt.png

The generated report is a tab-delimited file that contains, for each fact data change, the following field/values:

  • Cube

  • User

  • Date and Time

  • Members from each Dimension at the fact data intersection point

  • Old Value

  • New Value

Sales         BrianO    Mon Jan 15 21:58:37 2001     Cost of sales      USA       February  Aunt Re's Chicken  800       900      

Sales         BrianO    Tue Jan 16 21:58:53 2001     Quantity  USA       March     Aunt Re's Chicken  0         80       

Sales         BrianO    Tue Jan 16 21:58:56 2001     Revenue   USA       March     Aunt Re's Chicken  0         1750     

Sales         BrianO    Tue Jan 16 21:59:03 2001     Cost of sales      USA       March     Aunt Re's Chicken  0         1050     

Sales         BrianO    Wed Jan 17 21:58:39 2001     Quantity  USA       April     Aunt Re's Chicken  7.5       90       

Sales         BrianO    Wed Jan 17 21:58:49 2001     Revenue   USA       April     Aunt Re's Chicken  159.6375  1600     

Sales         BrianO    Wed Jan 17 21:58:52 2001     Cost of sales      USA       April     Aunt Re's Chicken  91.7235   920      

Figure 2.    Sample Transaction Log Output

Created log files

A set of transaction log files are created for Fact Data changes and a set are created for Metadata changes—explained in the table below. These files will reside in the same directory as the database file.

It is worthwhile creating a directory for each database, so transaction log files as well as backup files for different Databases are kept in their own directories—this will also prevent any single directory from expanding with a list of files associated with many Databases.

The two sets of transaction log files, for fact data and metadata changes, are specifically:

Fact Data Transaction Log Files

Data Transaction Log File

Description

xxx_Users.PLF  

Contains ongoing list of users that have made fact data changes to the database [xxx = database name].

xxx_Cubes.PLF

Contains ongoing list of Cubes that have had changes made to them [xxx = database name].

xxxyyy.LOG

Contains ongoing list of fact data points that have been changed [xxx = database name; yyy is an encoded date field representing when the file was created].

Note that a single database file can have multiple LOG files. Each time the server is restarted, a new LOG file is created . LOG files have a maximum size of 10MB.

Metadata Transaction Log Files

Data Transaction Log File

Description

xxx_Metadata.PLF 

Contains ongoing list of metadata objects to which changes have been made—Dimensions, Cubes, Slices [xxx = database name].

XxxObjectID1.MDT 

Contains a snapshot of an entire Object (here, referenced as ObjectID1) to which a change has been made (for example, if a Member is added to a dimension, the characteristics of the entire dimension are included); a snapshot of the entire Object is appended each time the Object is changed.

XxxObjectID2.MDT

As above, contains a snapshot of an entire Object (here, referenced as ObjectID2) to which a change has been made; a snapshot is appended each time the Object is again changed.

There continues to be a separate xxxObjectID.PLF generated for any metadata object to which a change is made, as described above.

Generally, the only time an administrator needs to be concerned about transaction log files—barring their use for restoring or undoing changes—is when the number of the files is large and the amount of disk space is running low.

You should periodically cull the log files in the directory. Assuming you are using the Backup feature: unless you want to undo changes, your last backup (.BAK) file (before further changes have been made) will provide the data you need to restore a database.

Two more facts about transaction log files:

  • If you view the files by name: because of the date encoding scheme, you are guaranteed to get the older files first.

  • These files are stored as binary files and require significantly less disk space than if stored ASCII text.

The following shows sample transaction log files located in a database directory:

sampletransactionlogfiledirectory.png

Figure 3.    Sample Transaction Log File Directory


Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk