Forum Stats

  • 3,839,458 Users
  • 2,262,493 Discussions


How to map one dimension table field against multiple fields in one fact table

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



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 .