Correlated - Separate Dimensions OR Single Dimensions ?

Hi, Please help me with your suggestions to resolve the problem.

In ODS (normalized) we have the following setup.


Security - Master table for all Securities (ESMP). Not all securities have settlements.

Settlement Security - Subset of securities. Only contains securitiesies with settlements. Resolution process will link Settlement securities to securities.
     Also, multiple settlement securities from different systems may map to same security in the security table once they are resolved (please see below).


Note: There is a business reason to maintain the securities at settlement system level. At the same time we maintain relation ship between settlement security and security.
-----

Security   Settlement Security
========   ====================  

SEC_ID         STTL_SEC_ID   STTL_SYS     SEC_ID
------          -----------   --------     -------
ESMP1          SSEC_ID1       SYS_A        ESMP1  
                   SSEC_ID2       SYS_B        ESMP1
                    SSEC_ID3       SYS_C        ESMP1


Now, we are building brand new DW (dimensional model) and have the following options:


option 1: Create  DIM_SECURITY (TYPE2)  and DIM_STTL_SECURITY (TYPE2)

Map both dim_security_key and dim_sttl_Security_key into the fact and have SEC_ID as the foreign key in DIM_STTL_SECURITY to DIM_SECURITY

this way the history of security and settle security can be maintained at the same time they can be linked if required.

Note: We are interested in maintaining history for only those securities that are in sttl_security table



option 2: Create one flat DIM_STTL_SECURITY by bringing all attributes of Security into single settle security table.

all attributes at one place, only one dimension key in fact.


The problems are:
1. if ESMP1 changes then we have to update the all settlement securities that is mapped to ESMP1
2. Also if someones wants to track ESMP1 changes over the time it is tricky as its interlaced with settlement security changes.


What is your suggestion?

Does the system where the settlement takes place have any material effect on the security? Does the fact a trade is settled have any material effect on the security?

A security dimension should have information about the security, not the trade. A settlement system dimension should have information about the settlement system. The settlement itself is a transaction, represented by a fact table which would have security and settlement system as dimensions.

No, settlement events do not have any material effect on the security.

Security table (ODS) maintains only the security attributes

Settlement security table (ODS) maintains the security attributes of the settlement system. There is a resolution process that confirms later
that settlement security (ssec_id) is nothing but this security (esmp1)

So, as described, a security dimension, a settlement system dimension, and a fact table for the settlement itself with parties and other dimensions.

Ok Thansk! so if I understand correctly DIM_SECURITY and DIM_STTL_SECURITY.

Questions
=========
1. Then how about having SEC_ID (natural/business id) as the foreign key in DIM_STTL_SECURITY to maintain the same relationship that is currently in ODS ???

2. Is there any harm in mapping both DIM_SECURITY_KEY and DIM_STTL_SECURITY_KEY into the FACT_SETTLEMENT ???

so that one can query FACT_SETTLEMENT with Settlement Security as well as Security??

No. The settlement dimension I was referring to relates to the settlement system, not the security. When you record the settlement, the settlement transaction would include the security being settled and the system that processed the settlement as dimensions. There is no apparent reason to create unique dimension rows that are combinations of security and settlement system.