Arriving at Facts and Dimensions

View previous topic View next topic Go down

Arriving at Facts and Dimensions

Post  rkraj on Fri Jun 29, 2012 1:33 pm

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?

rkraj

Posts : 12
Join date : 2012-06-29

View user profile

Back to top Go down

Re: Arriving at Facts and Dimensions

Post  LAndrews on Fri Jun 29, 2012 2:54 pm

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.


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum