Advice on Dimensional Modeling where dimensions share attributes

[sukhid.singh]

Hi All,

I need some help with the following. I've over a dozen dimensions, but I'll just take two for example:

1. Activity:

Agency Activity Activity Desc

01 100 Activity 100 Desc

02 100 Activity 100 Desc

2. Org Unit:

Fiscal Year Org Unit Org Unit Desc

2011 1000 Org Unit 1

2012 1000 Org Unit 1 Desc

So my scenario is that I've some dimensions have a common attribute called Agency and some have Fiscal Year and yet others have both (Agency and Fiscal Year)

How should I be designing this?

Option 1: Provide two dimensions, Agency and Org Unit and within them, provide hierarchies such as:

Agency >> Activity

Agency >> Activity Desc

or

Fiscal Year >> Org Unit

Fiscal Year >> Org Unit Desc

Option 2: Provide 4 different dimenions viz: Agency, Fiscal Year (or the entire Date), Activity and Org Unit. If I do that, then how do I restrict the appropriate data in the Activity or Org Unit's attributes once the user narrorws down a particular Agency and Fiscal Year?

Thanks in advance for your suggestions.

[ngalemmo]

Why do you have fiscal year combined with org units?