Cube Design Question ( Star Vs Constellation Schema)

Here are the two designs I have : (I'm using SQL Server 2005)

Constellation Schema :

http://img84.imageshack.us/img84/7206/constellation.jpg

Star Schema :

http://img692.imageshack.us/img692/971/starme.jpg

I need to build a report which shows ProjectID,ProjectName,Hours,isImpacted,isRequesting for a given line of business(LOB) and year filter.

E.g. :

Filters:  Line of Business : TestLOB   ; Year : 2009

Report:   TestLOB - 2009

ProjectID   ProjectName           Hours            isRequesting   IsImpacted            

T312       |     TestPrjName     |        523     |    Yes       |   No                 

T121       |     TestPrjName     |        213     |    No        |   Yes

Approach 1: (Constellation)
I'm getting the hours aggregated correctly but the RequestingLOB ImpactedLOB are messed up. Could be an issue with my MDX.

Approach 2:(Star)
Measures are calcualted incorrectly. One of my friends suggested to use a semi-additive measure to fix the issue.

Either way, firstly, I would like to get the designs validated and then work on how to fix the MDX queries / calculating measures.

Any inputs will be greatly appreciated.

Thanks for your time.

Kiran

ansaurus

tags:

views:

answers:

Cube Design Question ( Star Vs Constellation Schema)

related questions