Mini-Dimensions and Type 1 Outrigger

View previous topic View next topic Go down

Mini-Dimensions and Type 1 Outrigger

Post  aecobalt on Thu Aug 29, 2013 2:48 pm

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.

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


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...

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.


Posts : 3
Join date : 2013-08-29

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum