Hi,
I ve got a fact table called project_fact and a dimension table called technology_dim.
project_fact:
proj_id time_id tech_id revenue cost
technology_dim:
tech_id tech_name
Now there is a many to many relation between fact and dimension table. one project can be done on many technologies. How to resolve this, do i need to create a third table?
Please tell me in details...
Thanks in advance,
Siddharth Rai.
Create a table that represents the combination of technologies exploited by your project. Let's call this the TechGroup table:
create table TechGroup (
TechGroupID int not null identity(1,1)
)
alter table TechGroup add
constraint PK_TechGroup primary key (techgroupid)
Create another tables that relates the TechGroup to the Technologies that comprise it:
create table TechGroupTechnologyBridge (
TechGroupID int not null,
TechnologyID int not null
)
alter table TechGroupTechnologyBridge add
constraint PK_TechGroupTechnologyBridge primary key (techgroupid, technologyid),
constraint FK_TechGroupTechnologyBridge_TechGroupID foreign key (techgroupid) references TechGroup (techgroupid),
constraint FK_TechGroupTechnologyBridge_TechnologyID foreign key (technologyid) references Technology (technologyid)
In your fact table, reference the TechGroup table.
When you set up the cube, create a TechGroup dimension. Add the TechGroupTechnologyBridge as a measure group with a single measure (using COUNT). Make the measure not visible (which will hide the bridge measure group).
In your dimension usage tab, set the relationships between TechGroupTechnologyBridge measure group and TechGroup and Technology dimensions. Relate your fact table measure group to the TechGroup dimension as well.
Now, set a many-to-many relationship between your fact table and the Technology dimension using the TechGroupTechnologyBridge measure group as the intermediate measure group.
You will probably want to make the TechGroup dimension not visible within your cube.
Good luck,
Bryan
Hey thanks Bryan...sql
