Grain - Fact or Dimension

View previous topic View next topic Go down

Grain - Fact or Dimension

Post  KKumar on 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

View user profile

Back to top Go down

Re: Grain - Fact or Dimension

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Grain - Fact or Dimension

Post  KKumar on 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

View user profile

Back to top Go down

Re: Grain - Fact or Dimension

Post  Sponsored content


Sponsored content


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