Hi All,
Hope you are doing well
I need your valuable inputs regarding one of the designing problem in dimension model
I need to map one field in dimension table against multiple fields in fact table as below
dimension table dim_vdn with following attributes
CREATE TABLE dim_vdn
(
vdn_sk int4 NOT NULL,
vdn_id int2 NULL,
vdn_name varchar NULL,
active_date timestamp NULL,
inactive_date timestamp NULL,
active_flag bool NULL,
CONSTRAINT dimvdn_pkey PRIMARY KEY (vdn_sk)
);
2 - staging table to be converted in fact table
CREATE TABLE stage_table
(
vdn2 varchar NULL,
vdn3 varchar NULL,
vdn4 varchar NULL,
vdn5 varchar NULL,
vdn6 varchar NULL,
vdn7 varchar NULL,
vdn8 varchar NULL,
vdn9 varchar NULL,
call_durtion int,
total_time int
);
as you can see there are total 9 vdn fields in staging database and those have to be mapped against dimension table dim_vdn in data mart .
what is the best way to map/link the dimension table against the fact table , and what should be the design the fact table ( should come out of stage table)
Thanks very much in advance .
Regards