Mini-Dimensions and Type 1 Outrigger
Page 1 of 1
Mini-Dimensions and Type 1 Outrigger
In the process of Designing a Rental Property dimensional model and still confused as to when to apply a specific design technique.
Techniques explored :
1. Add Mini-Dimensions and Type 1 Outrigger.
2. Embedding all information into one dimension.
Case:
We have a [Property] table on the OLTP system (this holds data for houses, flats, garages,etc...that are let or rented).
As well as core property data i.e: No. of Bedrooms, No.Stories, Property Type (house,flat,etc..) This table also holds codes
such as Arrears Officer Code, Functional Area etc... and as usual these codes reference lookup tables.
Modeling Options queries:
---------------------------------
Option 1. Add Mini-Dimension and Type 1 Outrigger - Using SCD-2 On the mini-dimensions and SCD-1 on the main 'DimProperty' to keep it up to date + All Dims keys on Fact.
DimArrearsOfficer : sk_arrears_officer, ArrearsOfficerCode, ArrearsOfficerName, ArrearsTeam, etc... (SCD-2)
DimFunctionalArea: sk_functional_area, FunctionalAreaCode, FunctionalAreaName, AreaCategory, etc... (SCD-2)
DimProperty : sk_property_key, NoBedrooms, sk_arrears_officer (fk), sk_functional_area (fk) (SCD-1)
FactRentBalance : sk_date_key,sk_property_key, sk_arrears_officer, sk_functional_area, gross_balance, net_balance
or
Option 2. Embedding all information into one dimension 'DimProperty' and use SCD-2 on the attributes in question.
DimProperty : sk_property_key, NoBedrooms, ArrearsOfficerCode, ArrearsOfficerName, FunctionalAreaCode, FunctionalAreaName.
FactRentBalance : sk_date_key, sk_property_key, ... gross_balance, net_balance,etc...
Questions:
--------------
Q1) What is the best option to model the Property dimension (DimProperty) with SCD support considering that Arrears Officer and Functional Area will be used by other Business Processes / Facts. (should this be sufficient case for conformed / separate dimensions)
Q2) With Option 1, how to deal with the filters/parameters when Reporting (creating drop downs filters / report parameters)
(i.e. Do you perform a SELECT DISTINCT [ArrearsOfficer] from the main Big dimension every time the report is presented to the user?
(What is the best practice? Do you extract OLTP lookup codes to be used as Reporting 'helper smaller/tables'? )
Any Feedback is very much appreciated, thanks.
Techniques explored :
1. Add Mini-Dimensions and Type 1 Outrigger.
2. Embedding all information into one dimension.
Case:
We have a [Property] table on the OLTP system (this holds data for houses, flats, garages,etc...that are let or rented).
As well as core property data i.e: No. of Bedrooms, No.Stories, Property Type (house,flat,etc..) This table also holds codes
such as Arrears Officer Code, Functional Area etc... and as usual these codes reference lookup tables.
Modeling Options queries:
---------------------------------
Option 1. Add Mini-Dimension and Type 1 Outrigger - Using SCD-2 On the mini-dimensions and SCD-1 on the main 'DimProperty' to keep it up to date + All Dims keys on Fact.
DimArrearsOfficer : sk_arrears_officer, ArrearsOfficerCode, ArrearsOfficerName, ArrearsTeam, etc... (SCD-2)
DimFunctionalArea: sk_functional_area, FunctionalAreaCode, FunctionalAreaName, AreaCategory, etc... (SCD-2)
DimProperty : sk_property_key, NoBedrooms, sk_arrears_officer (fk), sk_functional_area (fk) (SCD-1)
FactRentBalance : sk_date_key,sk_property_key, sk_arrears_officer, sk_functional_area, gross_balance, net_balance
or
Option 2. Embedding all information into one dimension 'DimProperty' and use SCD-2 on the attributes in question.
DimProperty : sk_property_key, NoBedrooms, ArrearsOfficerCode, ArrearsOfficerName, FunctionalAreaCode, FunctionalAreaName.
FactRentBalance : sk_date_key, sk_property_key, ... gross_balance, net_balance,etc...
Questions:
--------------
Q1) What is the best option to model the Property dimension (DimProperty) with SCD support considering that Arrears Officer and Functional Area will be used by other Business Processes / Facts. (should this be sufficient case for conformed / separate dimensions)
Q2) With Option 1, how to deal with the filters/parameters when Reporting (creating drop downs filters / report parameters)
(i.e. Do you perform a SELECT DISTINCT [ArrearsOfficer] from the main Big dimension every time the report is presented to the user?
(What is the best practice? Do you extract OLTP lookup codes to be used as Reporting 'helper smaller/tables'? )
Any Feedback is very much appreciated, thanks.
aecobalt- Posts : 3
Join date : 2013-08-29
Similar topics
» Handling of SCD type 2 attributes in outrigger dimension
» Outrigger dimensions in Facts
» Values of Mini Dimension in SCD Type 4......!
» Use of Mini-Dimensions
» Mini Dimensions
» Outrigger dimensions in Facts
» Values of Mini Dimension in SCD Type 4......!
» Use of Mini-Dimensions
» Mini Dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum