Grain - Fact or Dimension
2 posters
Page 1 of 1
Grain - Fact or Dimension
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
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
Re: Grain - Fact or Dimension
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Grain - Fact or Dimension
We load all the forms (processed and not processed).
Thanks for the reply
Thanks for the reply
KKumar- Posts : 22
Join date : 2011-07-29
Similar topics
» conformed dimension for two fact tables which are at different grain
» Transaction fact with different grain dimension hierarchy
» Accumulating Snapshot Fact with Dimension at Same Grain
» calendar grain on both dimension and fact tables
» Differing grain dimension tables with the same transactional fact
» Transaction fact with different grain dimension hierarchy
» Accumulating Snapshot Fact with Dimension at Same Grain
» calendar grain on both dimension and fact tables
» Differing grain dimension tables with the same transactional fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|