Correlated - Separate Dimensions OR Single Dimensions ?
2 posters
Page 1 of 1
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?
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?
Last edited by dwdesigner on Mon Aug 05, 2013 1:31 pm; edited 1 time in total (Reason for editing : formatting)
dwdesigner- Posts : 3
Join date : 2013-08-05
Re: Correlated - Separate Dimensions OR Single Dimensions ?
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.
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.
Re: Correlated - Separate Dimensions OR Single 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)
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)
dwdesigner- Posts : 3
Join date : 2013-08-05
Re: Correlated - Separate Dimensions OR Single Dimensions ?
So, as described, a security dimension, a settlement system dimension, and a fact table for the settlement itself with parties and other dimensions.
Re: Correlated - Separate Dimensions OR Single 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??
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??
dwdesigner- Posts : 3
Join date : 2013-08-05
Re: Correlated - Separate Dimensions OR Single Dimensions ?
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.
Similar topics
» A single Dimension table Or separate the Dimensions?
» single denormalized dimension or 2 separate dimensions?
» Account attributes in separate dimensions
» Model with Attributes Based on Two Separate Dimensions
» Hierarchy within single dimension or two dimensions
» single denormalized dimension or 2 separate dimensions?
» Account attributes in separate dimensions
» Model with Attributes Based on Two Separate Dimensions
» Hierarchy within single dimension or two dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|