Forum Stats

  • 3,759,951 Users
  • 2,251,621 Discussions
  • 7,870,879 Comments

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

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