Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Mini-Dimensions and Type 1 Outrigger

Go down

Mini-Dimensions and Type 1 Outrigger Empty Mini-Dimensions and Type 1 Outrigger

Post  aecobalt 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

Back to top Go down

Back to top

- Similar topics

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