Fact Table Design Question. Please help.

Question

Fact Table Design Question. Please help.

u2kota

SSChasing Mays

Points: 601
More actions
May 23, 2012 at 9:56 am

#261896

Hi All,
I am trying to create a new OLAP database using the following tables in my OLTP database.
tblIndustry
IndustryIDIntPrimary Key
NameVarchar(100)(Ex: Accountancy or Sales etc)
tblRole
RoleIDIntPrimary Key
NameVarchar(100)(Ex: Developer or DBA etc)
tblVacancy
VacancyIDInt Primary Key
TitleVarchar(100)
--------
--------
tblVacancyIndustry
VacancyIDInt
IndustryIDInt
tblVacancyRole
VacancyIDInt
RoleIDInt
As you can see from above, each vacancy has multiple industries and multiple roles and this data is stored in tblVacancyIndustry & tblVacancyRole.
My goal is to quickly calculate the vacancy counts by industry & by role. In my OLAP database I have created the dimension tables DimIndustry & DimRole. While creating the fact table FactVacancy I got stuck. My question is..........
Should I create one fact table which is a CROSS PRODUCT of tblVacancy, tblVacancyIndustry & tblVacancyRole? If this is not correct, what is the correct way to design the fact table.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Whenever there would be any vacancy, it will belong to some industry & some role. It’s safe to have one fact table FactVacancy with RoleID, IndustryID as grain.

And you don’t need to have a CROSS JOIN to populate the fact table. INNER JOIN on VacancyID should be sufficient for requirements.

u2kota SSChasing Mays Points: 601 More actions · Answer 2

Hi Dev,

Thanks for your response. Each vacancy in my database is linked to more than one industry and role. In such case how do I design my fact table?

Cheers

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

In this case you should add VacancyID itself as a grain (sounds crazy? I know).

tblIndustry

IndustryID Int Primary Key

Name Varchar(100) (Ex: Accountancy or Sales etc)

(1) IT

(2) Finance

tblRole

RoleID Int Primary Key

Name Varchar(100) (Ex: Developer or DBA etc)

(1) Architect

(2) Manager

tblVacancy

VacancyID Int Primary Key

Title Varchar(100)

(1) Techno-Functional Consultant

Proposed Fact Table (Fact Less Fact)

VacancyIDRoleIDIndustryID

111

122

11?

1?2

1??

Demerits:

•Double Counting, if you ignore distinct values

•NULL (Missing ‘?’) Values, which can result in weird scenarios.

Merits:

•Reporting on Vacancy / Role / Industry

•Scope for missing Roles in Industry or Industry for Roles.

u2kota SSChasing Mays Points: 601 More actions · Answer 4

Hi Dev,

Thanks for your response. Am I right in assuming that this "Fact Less Fact" table is a bridge table? If not can you please tell me if bridge table provides an answer to my problem. If so how do I go about it? Sorry to be a pain and thanks for your time.

u2kota

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

I am sorry for the late reply. It came to my notice just now. I am not much regular on SSC these days but I am surprised with a fact that nobody in SSC covered it in these many days.

To answer your question, yes a ‘Fact Less Fact’ table it is a bridge or association table for two or more dimensions. IIRC, it’s a Kimball's term.