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

Grain - Fact or Dimension

2 posters

Go down

Grain - Fact or Dimension Empty Grain - Fact or Dimension

Post  KKumar Mon Feb 24, 2014 9:14 pm

I am designing a star schema for a registration form process for property with the following business objective
To analyze the number of forms generated, number of forms processed, number of days to process a form , registration status (valid,invalid,not responded) of the form
The grain of the system is  a registration form for every property to register.
Following is the design of the star schema I proposed

dim_reg
_______
reg_key
reg_id
reg_generate_date
reg_submit_date
reg_form_intake_start_date
reg_form_intake_end_date
reg_processed_by
...... 20 other attributes

dim_property
____________
property_key
property_id
property_address
property_city
property_state
property_zip
....other 10 attributes

dim_reg_form_type
_________________
reg_form_type_key
reg_form_type_id
reg_form_type_name

dim_generate_date
_________________
date_key
date_value
day_number
month_name
year
....other attributes

fact_reg
_________
reg_key
property_key
reg_form_type_key
date_key
number_of_form_received (default to 1)
number_of_days_to_process
number_of_form_processed (1 or 0)
...other facts

For every record in dim_reg a corresponding record exist in fact_reg i.e., its a one to one relationship

My question is Is this the right approach to model or can I move all the attributes from dim_reg to fact_reg

KKumar

Posts : 22
Join date : 2011-07-29

Back to top Go down

Grain - Fact or Dimension Empty Re: Grain - Fact or Dimension

Post  BoxesAndLines Tue Feb 25, 2014 9:00 am

Do you only load processed forms? That is, do you ever need to update the number of days to process metric? If not, this design is fine. If you do, then you should adjust your design to an accumulating snapshot fact table.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Grain - Fact or Dimension Empty Re: Grain - Fact or Dimension

Post  KKumar Tue Feb 25, 2014 9:32 am

We load all the forms (processed and not processed).

Thanks for the reply

KKumar

Posts : 22
Join date : 2011-07-29

Back to top Go down

Grain - Fact or Dimension Empty Re: Grain - Fact or Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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