Acual Vs Target on dimension attibute

I came across a scenario where actual vs target was to be compared. The intial model  looks like below. This shows the transaction fact table with couple of dimension.



After a few months the business users wanted to compare the actual units versus  the target units for every fiscal year . So we added a dimension and fact to the  model as below



Again after a few weeks they wanted to compare actual vs target but this time it was  for every fiscal year and project_type_desc which is an attribute in the project  dimension.
My question is how do I connect the fact_unit and fact_target_unit_fy_pt using the  project dimension?

Project needs to be a dimension of the facts.

Thanks ngalemmo for your response.

But if I add project dimension (dim_project) to fact_target_unit_fy_pt , I will be having detail facts (day level) and aggregate (Fiscal Year) in one table.
The analysis is done to compare target project unit and actual project units, so the end report should look somewhat like this

Fiscal Year ProjectType Description Target Units Actual Units
2014 New Construction 100 80
2014 Rehab 50 60
2015 New Construction 150 10
2015 Rehab 40 50
and so on....

Actual Units can be achieved by calculating the sum(fact_unit.total_start_unit)
ProjectType Description is an attribute in the dim_project table.

Please advise.

Thanks

Don't mix detail and aggregates in the same table.

You need product as a dimension is both tables otherwise you can't do project level analysis. Its as simple as that. Store detail, don't worry about aggregates, you can always add them later (in separate aggregate tables) if query performance is an issue.

A good data warehouse design ALWAYS starts with detailed facts with all applicable dimensions. Never only build aggregates. It limits the ability to perform analytics on the data.

Thanks ngalemmo. Appreciate your response.
I don't know if I understood this correctly because my total target units is at the fiscal year and the actual units is at the day level.
But let me know go through your response again.

If the data you receive about targets is by year without project then you cannot compare actuals to target at the project level. If the business does not plan targets at the project level how would they expect to compare against actuals? If they do create targets at the project level, you should be loading that data.

ngalemmo - your response was helpful

To answer your question-
The business plans the units to be built at the start of the fiscal year and not by project. In fact they plan for different level of information.
For example,
Units Plan for Fiscal Year 2015 = 1000 to be built
and
Units Plan for Fiscal Year 2015 and unit size
2015 and Studio = 500 to be built
2015 and 1 bedroom = 300 to be built
2015 and 2 bedroom = 200 to be built


Thanks

If all you need is summaries by type description, you don't need the _fy summary, just the _fy_pt. The _fy table doesn't help much since there are only two types. There really isn't much performance gain any you can get yearly totals easily enough from the _fy_pt table.

As far as comparing actuals to target by type, just summarize actuals by type and combine it with the _fy_pt table. The only condition is the type description in the fact conforms with the type description in the dimension.

Thanks ngalemmo