Converting Snowflake to Star

Not sure how to approach this. I have inherited an existing design with a fact table representing Tenants at an apartment property, and a dimension representing the property.

Tenant Fact:
PropertyKey (FK)
DateKey (FK)
MonthlyRent
YearlyIncome
...

Property Dimension:
Property Key (PK, autonumbered)
Natural Key (identifies property in business processes)
Property Name
...

Each property can participate in several funding programs, and the flaking continues with many things being associated with each program etc. So the relationship is such that one Tenant has one Property has Many Funding Programs

Now if we don't worry about the things beyond funding for now, to make things simple, what if I want to incorporate Funding Programs into my Property Dimension. Often a user will want to see something like, what is the average income of tenants at each property, but only showing those properties funded by Program B or Program C. Such that the report has one row per property and an average income.

Now if I simply join in the attributes associated with each program, then the Property dimension now has multiple rows per property. This seems fine because my query works fine by only grouping on property related attributes. The problem is now there is not a single row in the property dimension which the tenant is related to. The tenant is related to a single property, but now a property is duplicated in the property dimension for each program. So there is not a single key in the product dimension that the Tenant is associated with.

I look at examples such as Products, but these are cases where each Product is related to one brand, or one Flavor, or one Package. Denormalizing this into a dimension makes sense because you will still have one row per Product.

How do I handle the opposite relationship though, such as the above, where one Property has many Programs?

Funding program is a dimension. What are the relationships?

Is a funding program something a tenant uses to help with the rent? If so it should appear as as FK reference in the tenant fact.

There should also be another fact, 'program availibility', which relates the funding program with the property (and probably effective/expiration dates).

If you have very large volumes, you may consider an aggregate the gathers statistics as the property/program level. But, my sense is, just combining the two facts dynamically should be fine.

The funding is not for the tenant. The Property is related to the Programs in that the cost to build or rehabilitate the property was partially supported by funding from one or more programs. And each Program is not really shared across Properties as one might suspect. Even though multiple properties might receive funding from Program B, in each case the agreement for funding is different such that there are different attributes at the program level. To clarify this, if I joined Program and Property it might look like this:

Property 1, Program B, $200,000 funded, For New Construction
Property 2, Program C, $100,000 funded, For New Construction
Property 2, Program B, $50,000 funded, For Rehabilitation

Edit to further clarify, in the source normalized model, that I also inherited:

Property:
Property Key (PK)
Property Name
...

Tenant:
Tenant Key (PK)
Property Key (FK)
Yearly Income
Monthly Rent
...

Program:
Program Key (PK)
Property Key (FK)
Program Type (Ex: "B")
Funding Amount
New Or Rehabilitation
...

From programs it continues to decend through one-to-many relationships a few levels. Hoping that once I understand how to handle Programs, then incorporating each level will be the same process.

Program Restrictions(when receiving funding from a program, the property must cater a part of it's units to a particular group):
Restriction Key (PK)
Program Key (FK)
Number Units
Type (Elderly, Disabled, etc.)

Here's a question for you: How many tenants live in apartments that were funded by A or B? The only way you can answer such a question in a simply query is if you had a dimension table that had a column for every type of funding. The problem is whenever you add a new funding type you will have to make a model change to your funding type dimension.

I think answering such a question has to be a 2 step process - ID the appropriate apartments and then count the number of tenants in each apartment.

If this is the case, the I think you have 2 fact tables - Tenant and Apartment-Funding. Both Fact Tables share the Apartment. The Apartment-Funding fact has a row for every Apartment and it's funding mechanism.

Its definitly two facts, although the latter would be building funding (or even property funding if it applies to multiple buildings). Dimensions would, at minimum, be the propery, funding program and date, with the amount of funding as a measure.

As for the tenants, who is in which properties with which funding programs would be achived by consolidating the two facts.

Thanks Jeff.

Intersting you mention repeating columns, as our current Property dimension does have the repeating columns for the program type, but of course we have the problem you describe as well as the additional problem of more repeating columns for additional program related attributes.

What you suggest would make sense, but I was afraid it would be confusing to users, because they always think in terms of the property being funded, not the individual apartments, because that is how funding is structured.

Additionally, a tenant really represents an apartment. You have made me reallize how poorly the entity is named, as the row contains both apartment and tenant data. The grain of that table is actually one row per apartment, and it contains all the dollar values that might be measured, as well as things like # of people in each age group. It sounds like a pre aggrgated row, which I know we should have a more detailed grain with one row per tenant, but this is the way the data is collected. I am now considering a future improvement to break these up as you describe with Tenant facts, Apartment dim, and Apartment Facts(for things that would be measures like rent).

But your suggestion has gotten me thinking about some alternatives I didn't consider. I have alot to think about now and was able to find a few relavant articles in the Kimball Group Reader, and want to draw up some drafts of possible revisions to my model.

ngalemmo wrote:Its definitly two facts, although the latter would be building funding (or even property funding if it applies to multiple buildings). Dimensions would, at minimum, be the propery, funding program and date, with the amount of funding as a measure.

As for the tenants, who is in which properties with which funding programs would be achived by consolidating the two facts.

Makes sense, thanks!