Template for Physical Dimensional Model Document

Dimensional Model as designed with the facts & dimensions using normally Ralph Kimball methods or sometimes using Bill-Inmon books , normally pertains to the so called “Logical Data Model” or “Logical Dimensional Model” . There are many steps which need to be done to execute this logical model into the physical model & in most projects this step is completed by some other team. Here is one such template which covers all the aspects which is to be included in the Physical Dimensional Model document. The source for all this remains a “Logical Dimensional data model” and a “Data Profiling”.


-Template for Physical Dimensional Model Document

 

This strategy template covers the details about the physical implementation of the above dimensional model.

 

  1. q  DBMS system contents
  2. q  Partition settings
  3. q  Disk striping specifications
  4. q  Indexing Strategy
  5. q  Indexes Plan
  6. q  Processing hints
  7. q  Database Resizing
  8. q  Log Processing
  9. q  Data Archiving Business Rules
  10. q  Data Purging Business Rules
  11. q  DBMS-level security privileges and grants
  12. q  View definitions
  13. q  Stored procedures and SQL administrative scripts
  14. q  DBMS backup status, backup procedures, and backup security

 

1. DBMS Server Code Tree

 

 

Drive Name

Directories

Subdirectories Description

 

Physical Database Design

 

Physical Data Model Diagram

[Insert Erwin Diagram here]

Physical Data Model Objects

 

 

Table / Column name

 

Data Type

Permit nulls?  

Comment

       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       

 

2. Partition settings

 

The fact table is partition at DBMS based on below setting. The indexing strategy related to Partitions is included in Index Plan

 

 

Partition  Name Partition  Type Partition Logic Data Estimates Location ETL Configuration
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           

3. Disk striping specifications

 

 

The GPFS used in current project & the disk striping specifications are as below

 

 

 

 

 

 

4. Indexing Strategy

 

The detailed list of indexes is included below in index plan. The strategy is as below.

 

A. Entity Based Indexing

 

1. Dimensions Table Index Rules

 

Dimension  Type Data Size Index Rules Location Justification
         
         
         
         

 

 

 

2. Fact Table Indexes Rules

 

Fact Type Data Size Index Rules? Location Justification
         
         
         
         

 

 

B. Partitioned Based Indexing

 

1. Global Indexes/Local Indexes

 

Partition  Type Data Size Index Rules? Location Justification
         
         
         

 

 

 

 

 

C. Access Based Indexing

 

Below are the rules for creating the report access based indexing.

 

Report Type Join Path Multi-Col  Index? Index Order Index Rules Location Justification
             
             
             
             

 

 

5. Index Plan

 

Refer to below list for index plan

 

Table / Index Name Index Type Unique? Columns Location Justification
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           
           

 

6. Processing hints

The processing hints are used to influence the OPT as described below.

 

Join Path Hint Rules Hint  Type Hash?

Nested Loop?

Table-Columns Location Justification
             
             
             

7. Database Resizing

The resizing of DBMS is done both at the level of table structure & Buffer-pools

Table Resizing

 

Table  /Col Data Type Re-sizing Rules Haircut? Page Sizes Data Profiling Stats Justification
             
             

Buffer Pools Resizing

 

8. Log Processing

The logging strategy are described below for the DBMS system

A. Active Logs

B. Archive Logs

C. Transaction Log Buffer

 

 

9. Data Purging Strategy

Please refer to Data Archiving strategy as it is coupled with the same.

10. Data Archiving Strategy

The business rules are included below in Appendix for the data archiving strategy.

 

11. DBMS-level security privileges and grants

 

The security deployed Row Level security is loaded through ETL Load [refer ETL Strategy Document] & grant plans are included below

 

User Group Grant Type Business Justification Role Type Notes
         
         
         

 

 

 

12. View definitions

 

The views created in system are as below

 

View Name Source Table Justification Notes
       

 

 

13. Stored procedures and SQL administrative scripts

 

 

14. DBMS backup status, backup procedures, and backup security

The production systems will always a mirror system. The procedure to switch from mirror system is included in below script for normally completed ETL loads. For the failover loads the same script will be triggered by the failover.sh job. [Refer ETL Strategy Document for more details]

 

 

 


Appendix A

 

General Ledger Data Archiving & Purging Rules

 

This document presents a brief sample description of the business of Company and specific business requirements applicable to the Data Archive project.  Any subsequent changes, additions, or deletions are not part of this document and will be submitted to Company separately for acceptance and inclusion as an additional requirement for the project.

 

Data Archive handles two types of General Ledger transactions: Balances and Journals. Data Archive also processes Actual, Budget, and Encumbrance type transactions for Balances and Journals. Data Archive handles balance transactions in three Entities: Balance Actuals, Balance Budgets, and Balance Encumbrances. Data Archive archives and purges all Balance information for a given type as long as the General Ledger period for the balance information is closed or permanently closed. This rule is enforced during population of the interim (or candidate) table. No other rules are enforced for Balance transactions. Data Archive handles journal transactions in three Entities: Journal Actuals, Journal Budgets and Journal Encumbrances. For each Entity, the same business rules are enforced. The journal batch that the journal transaction is associated with must be posted to the General Ledger, and the period in which the journal was posted must be closed or permanently closed. When choosing the parameters for these Entities, Data Archive limits the date selection to closed and permanently closed periods only.

 

Business requirements

General Ledger: journals

These entities process general ledger journal transactions, including actual journals, budget journals, and encumbrance journals.

Transaction Type Candidate Selection Criteria:
Actual Journals GL – Journals – Actuals driving interim

select A.je_header_id, A.je_batch_id, P.start_date

from GL.gl_je_headers A, GL.gl_period_statuses P

where P.application_id = 101  AND P.set_of_books_id = NVL(:p_sob_id, P.set_of_books_id)  AND TRUNC(p.start_date) >= :p_start_date  AND TRUNC(P.end_date) <= :p_end_date  AND P.closing_status||” IN (‘C’,’P’) AND A.period_name = P.period_name  AND A.set_of_books_id = P.set_of_books_id AND A.actual_flag = ‘A’;

Budget Journals GL – Journals – Balances driving interim

select A.je_header_id, A.je_batch_id, P.start_date

from GL.gl_je_headers A, GL.gl_period_statuses P

where P.application_id = 101  AND P.set_of_books_id = NVL(:p_sob_id, P.set_of_books_id)  AND TRUNC(p.start_date) >= :p_start_date  AND TRUNC(P.end_date) <= :p_end_date  AND P.closing_status||” IN (‘C’,’P’) AND A.period_name = P.period_name  AND A.set_of_books_id = P.set_of_books_id AND A.actual_flag = ‘B’;

Encumbrance Journals GL – Journals – Encumbrances driving interim

select A.je_header_id, A.je_batch_id, P.start_date

from GL.gl_je_headers A, GL.gl_period_statuses P

where P.application_id = 101  AND P.set_of_books_id = NVL(:p_sob_id, P.set_of_books_id)  AND TRUNC(p.start_date) >= :p_start_date  AND TRUNC(P.end_date) <= :p_end_date  AND P.closing_status||” IN (‘C’,’P’) AND A.period_name = P.period_name  AND A.set_of_books_id = P.set_of_books_id AND A.actual_flag = ‘E’;

 

Note: journal batch is not purgeable

Exception Description This rule validates the journal batch has been posted to the General Ledger.
Exception Workaround Data Archive checks the value of three fields: the Status, the Status Verified Flag and the Posting Run ID. The journal batch needs to have a status of “Posted,” the status verified flag (updated by the posting program) should be set to “Yes” and the posting run id should have any value, which signifies that the journal batch was

part of a posting run.

Informatica may modify this rule to ignore the status verified flag. Oracle’s posting program does not always update this flag properly during the posting process. If the other two conditions are met during the enforcement of this rule, the journal batch can be considered posted.

Condition Statement EXISTS(SELECT B.je_batch_id FROM GL.gl_je_batches B WHERE A.je_batch_id=B.je_batch_id AND (B.status <> ‘P’ OR B.status_verified <> ‘Y’ OR NVL(B.posting_run_id,0) = 0))
Modified Condition Statement* EXISTS(SELECT B.je_batch_id FROM GL.gl_je_batches B WHERE A.je_batch_id=B.je_batch_id AND (B.status <> ‘P’ OR NVL(B.posting_run_id,0) = 0))

*the  condition was modified to exclude the check for status_verified

General ledger: balances

These entities process general ledger balance transactions, including actual balances, budget balances, and encumbrance balances.

No rules are enforced by these entities. The candidate selection process only identities balance records whose general ledger period has been closed or permanently closed.

Transaction Type Candidate Selection Criteria:
Actual Balances GL – Balances – Actuals driving interim

select A.set_of_books_id, A.period_name, B.start_date, B.end_date, ‘Y’, B.start_date

from GL.GL_BALANCES A, GL.gl_period_statuses B

where B.application_id = 101  AND B.set_of_books_id = NVL(:p_sob_id, B.set_of_books_id)  AND TRUNC(B.start_date) >= :p_start_date  AND TRUNC(B.end_date) <= :p_end_date  AND B.closing_status||” IN (‘C’,’P’)  AND A.set_of_books_id = B.set_of_books_id AND A.period_name = B.period_name  AND A.actual_flag = ‘A’;

Budget Balances GL – Balances – Budgets driving interim

select A.set_of_books_id, A.period_name, B.start_date, B.end_date, ‘Y’, B.start_date

from GL.GL_BALANCES A, GL.gl_period_statuses B

where B.application_id = 101  AND B.set_of_books_id = NVL(:p_sob_id, B.set_of_books_id)  AND TRUNC(B.start_date) >= :p_start_date  AND TRUNC(B.end_date) <= :p_end_date  AND B.closing_status||” IN (‘C’,’P’)  AND A.set_of_books_id = B.set_of_books_id AND A.period_name = B.period_name  AND A.actual_flag = ‘B’;

Encumbrance Balances GL – Balances – Encumbrances driving interim

select A.set_of_books_id, A.period_name, B.start_date, B.end_date, ‘Y’, B.start_date

from GL.GL_BALANCES A, GL.gl_period_statuses B

where B.application_id = 101  AND B.set_of_books_id = NVL(:p_sob_id, B.set_of_books_id)  AND TRUNC(B.start_date) >= :p_start_date  AND TRUNC(B.end_date) <= :p_end_date  AND B.closing_status||” IN (‘C’,’P’)  AND A.set_of_books_id = B.set_of_books_id AND A.period_name = B.period_name  AND A.actual_flag = ‘E’;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s