Arriving at Facts and Dimensions

All

I have come out with a sample Model for a small report. I don;t have business requirement doc but i have info on tables and elements of the report.

Using the existing tables how to arrive at this model is what i have done.

Can you guys give me your comments.

Source:- Existing DataMart

Target:- A New Star Schema

Purpose for developing reports on that new Star

A Financial institution maintains the banking account for each
Housing Unit. Inside each Housing UNIT we can have more than one account created in this institution.

Housing Units are grouped based on the Branch/region of where this financial Instituition is located.


Report has the following details

Monthly Metrics

a] Total number of Housing Units in a particular region for
April 2011, May 2011 etc
b] Total no of ASSET Values that each Housing UNIT has for month of April 2011, May 2011 etc
c] Total Revenue coming out of each Housing Unit for themonth of apr 2011, May 2011 etc

Daily Metrics
For each day of a month
a] Count of new housing Units formed as of yesterday for a particular region/Branch
b] Account balances for the entire day for the whole set of Housing UNIT
c] Account transfer Flows from this institution to outside place... Get the value of the ASSETS for each account that is flowing out and similarly for ASSETS values flowing in (for the whole set of House Holds)


Let me know if you need more data..



Dimension Tables


Housing_UNit Dimension
_____________________
H_Unit_ID (PK)
H_Unit_Creation_date


Housing_Branch_Dimension
________________________
H_Unit_Number
Division_CD
Region_CD
Status_CD


Account_DIM
___________
ACCT_NO(PK)
Account_Balance
Account_Creation_Date


DATE_DIM
________

DAY(Pk)
WeekDay
Year
Calendar Quarter
Calender Months
Calender Week


FACTS

ACCT_FACT
_________
ACCT_NO (FK)
DAY(FK)
ASSET_VALUE
Account_Balance



REVENUE_Of_Instituion_FACT
____________
ACCT_NO (FK)
Housing_Unit_Id(FK)
BR_NO
DAY(FK)
Commision_Amount
Brokerage_Fee



Account_Flows_FACT
___________________

ACCT_NO (FK)
BR_NO(FK)
Housing_Unit_ID(FK)
DAY(FK)
Transaction_Date
Account_Transfer_Deliver_Value ( Value of assets outgoing)
Account_Transfer_REcv_Value (Value of Assets incoming)

My question:- For a single report shuld we have 3 facts.. Will this not be a performance issue when the reporting tool BO/Cognos is trying to pull by using joins? I have arrived at facts based on the logical measures. Should i group it together?

Having 3 facts for a single report shouldn't be a problem.

You are correct - you don't want to be joining facts.

The report should be built using 3 queries, one for each fact.

The results can then be combined based on the conformed dimensions.