Facts at different levels.

Hi Everyone,
The data I have has no perticular grain statement on which I can base my model..

Can someone suggest a better design for this scenario,

Simplified version is,...

I have 2 dimensions
1) Property.. Heirarchy is Site -> Building -> Floor.
2) Account (No heirarchies)

The account values are available at different levels...(for some of the accounts it is at building level...and for some it is Floor level)
There are some buildings with no floors.

I can think of 3 solutions.

1)..Having different dimensions for all three......In this case,.I loose drill down

2) Aggregating the floor level fact data to Building level data and attaching the aggregate data with Building dimension.
but, I can not c the entire list of Buildings at the floor level data at first fact(bcoz, there are building with no floors)..
and the reporting is bit difficult inthis case...

3)..Having a filll-in value such as 'No floors' in the data where there r no floors ...
Example, there is a building B1 with Floors B1F1, B1F2 another BUilding B2 with no floors...

Account A1 contains floor level data where as A2 contains Building level data..

so,.The fact looks like...
A1, B1, B1F1, 10
A1, B1, B1F2, 20
A1, B2, 'No floors', 0

A2, B1, B1F1, 20(Building level value)
A2, B1, B1F2, 20
A2, B2, 'No floors', 30

But, when we aggregate the first set to building level...it gives 30 which is correct amount.
BUt, second set of data aggreagting to Building.. gives twice the original value..

I also thoguht of having 2 facts....Floor level and Building level..and storing the data accordng to the account type..
and making the floor level data as aditive fact..and building level data as non addtive fact.....but, then we can not aggregate this through different dimensions....

Can someone suggest a better solution for this.....

Regards,
Kalpana.

Consider the deisgn of the Building dimension table as

key
tier0(site info)
tier1(bldg info)
tier2(flr info)

I am assuming that you have only two level of aggregate information bldg and flr but it can be generalize for more site as well

1. Have a dummy record for each of the Bldg as
key = numerci value (1)
tier0(site info)=S1
tier1(bldg info)= B1
tier2(flr info) = B1

And if the Bldg has floors as well then also have those details as
key = numerci value (2)
tier0(site info)=S1
tier1(bldg info)= B1
tier2(flr info) = B1F1

key = numerci value (3)
tier0(site info)=S1
tier1(bldg info)= B1
tier2(flr info) = B1F2 and so on

2. Now while storing the fact data into the fact table import the foreign keys accordingly

Fact ->
key =1
Amount =20

key =2
Amount =10

Key = 3
Amount =10

This example tells stores the aggregates both at the Blg level and the Flr level using the FK's

Now suppose we want to know the Bldg level aggregate from the fact what we do is
a) Join the Fact table with the dimension using the FK's and then put the filter dimension.tier1 =dimension.tier2

If we need only the flr level data then do this
b) Join the Fact table with the dimension using the FK's and then put the filter dimension.tier1 <> dimension.tier2