Contributed February 20, 2001 by Ian Pratley (ian.pratley@cis.co.uk ) Audit Title: Data Warehouse (consider in conjunction with MI BRAM) Audit Ref:
Gross risk Impact (V,H,M,L)
Prepared by: Ian Pratley Reviewed by:
Date: Date: Test ref.
Controls Effective? (Y/N) Net Risk Impact (V,H,M,L) Net Risk Likelihood (V,H,M,L)
WP Ref:
Business Objectives Sufficient information to meet organisations MI requirements. Information utilised.
Key Risks xref MI BRAM
Causes of Risk
Possible Controls to Mitigate Risk
Audit Tests
Recommendation
Commentary
Data and information within warehouse and/or marts cannot be understood.
Poor design methodology of data warehouse and/or marts
Lack of understanding in design methodology Poor documentation.
Out of date documentation
Response times too great Excessive use of data warehouse rather than data marts for running queries Poor design methodology of data marts Performance not monitored Lack of understanding in running queries and creating reports xref MI BRAM xref MI BRAM Inaccuracies, omissions and inconsistencies in the source data not identified during the extract/transform process Errors identified during the extract/transform process not corrected within the source systems
Standard documented design methodology adopted, for example approach towards normalisation Training given to designers of the data warehouse and/or data mart. Accurate and up to date documentation of data models - should use automated documentation tools Meaningful documentation of data definitions and data descriptions Procedures for maintenance of documentation in line with changes to the source systems Consideration given to replication of data marts.
xref above, but consideration given to indexes, aggregation, de-normalisaton Training issue - xref MI BRAM
Accurate, consistent and up to date data.
Enquiry equipment in use not effective Insufficient enquiry equipment in use Data within the source systems is in error
Extractions/transforms designed to report errors within source data where this is practicable. Aged reports of outstanding errors
Responsibilities allocated for correcting errors within source systems. Agreed approach for treatment of errors reported, with regard to the action to be taken for the specific extract/transform run Risks relating to transfer of information from source systems: time dependencies of transactions, incomplete or corrupted transfers
10/16/2009
Page 1
Audit Title: Data Warehouse (consider in conjunction with MI BRAM) Audit Ref:
Gross risk Impact (V,H,M,L)
Prepared by: Ian Pratley Reviewed by:
Date: Date: Test ref.
Controls Effective? (Y/N) Net Risk Impact (V,H,M,L) Net Risk Likelihood (V,H,M,L)
WP Ref:
Business Objectives
Key Risks Data structure changes within source systems not reflected within data warehouse/data marts
Causes of Risk
Possible Controls to Mitigate Risk Specific consideration of impact on data warehouse/marts within source system change control procedures Source system changes reflected in data warehouse and data mart documentation xref above Source system changes tested through to data warehouse Database integrity checks such as referential integrity, and data model integrity checks such as reconciliation of aggregates. Controls over data edits Discuss approach adopted for the addition of new areas
Audit Tests
Recommendation
Commentary
Errors arise in the data within the data warehouse and/or data marts
Unauthorised update of data warehouse and/or data mart Information can be extended, for example to include additional products Design methodology does not facilitate future additions to the data warehouse and/or data mart Poor design methodology of data warehouse and/or marts xref above Poor design methodology of data warehouse Capacity of data warehouse not monitored xref above
xref above
Poor or out of date documentation Capacity of data warehouse exceeded
Use of data normalisaton to minimise data redundancy.
Ongoing availability of the information.
Capacity of data warehouse exceeded Data warehouse and/or marts unreliable
Could indicate need for replication Reliability of data warehouse and marts not monitored
10/16/2009
Page 2