Datawarehouse Question

Question

I have a structure like this in a DB

| RoleID | RoleTypeID | PersonID | OrganizationID | FromDate | ToDate |

Each role is only valid for dates between FromDate and ToDate.

Each person has a name, gender and birth date.

I want to put this date into a data warehouse to use with SSAS.

There I want to be able to build cubes with the number of roles per organization, per role type, per gender, per age per time.

How would I design a DW to store this, I could potentially get ~200 rows just because of the age and gender data combinations, but then I have role types and organizations on top of that.

E.g. rows like

|2009-01-01|RoleTypeID=Employee|Organization=HQ|Gender=Male|Age=32|Count=17| |2009-01-01|RoleTypeID=Employee|Organization=HQ|Gender=Female|Age=32|Count=12| |2009-01-01|RoleTypeID=Employee|Organization=HQ|Gender=Male|Age=33|Count=18|

Gut feeling tells me I am on the wrong track here…

Answer 1

Hi Fredrik,

A Datawarehouse project is not really something you can jump into without having a good solid grasp and understanding of the fundamentals involved. The design process is not the same as the 'relational model' and requires that you think differently about data.

My advice to you is to get hold of a copy of the following text. It is an excellent resource that will walk you through the steps required for your entire project.

http://www.amazon.com/Microsoft-Data-Warehouse-Toolkit-Microsoft-Business-Intelligence/dp/0471267155