Help in declaring grain and identifying dimensions

Requirements: 3 Reports
-----------------------

Source Systems: 3 Project DBs

1st Report : Portfolio Summary Details
The project monthly details report will display project information i.e. summary actual costs from the Vendor History file database expenses & capital and dollarized labor. There will be a single line item entry on the report per project.
Grouping of report
• By Director
• By Project Manager

Selection parameters to run the report:
• Director
• Project Manager

Sorting of report
• Descending Budget Amount
• By Tier

2nd Report: Project Portfolio Summary
The Project monthly summary report will display project information from Primavera and monthly actual costs by category groupings from the SQL Vendor History file database, Canadian Carpe Diem system for Canadian FTE and hardware/software expense & capital. Also US FTE Labor costs from Primavera.

The report will be one project per page.
Timing of the report
• Monthly PDF will be run to match financial month end reporting
• On demand report
Grouping of report
• By Director
• By Project Manager
• Project ID
Selection parameters to run the report:
• Director
• Project Manager

3rd Report: Project Actual Invoice Transaction Detail

The project actual invoice transaction detail report will display specified project information from actual invoice costs paid in the Vendor History File.

The report will display line item detail by project id based on date range entered.

Timing of the report- On demand
Grouping of report
By Project ID

Sorting of report
By Invoice date

----------------------------------------------------------------------------------------------------------------------------------------------------------
I have created an excel spreadsheet. I have categorized the reporting fields in following reporting categories:

1. Project Identifiers - Project Ids, Name, etc.
2. Stakeholders - PM, Director, Infrastructure Manager, etc.
3. Project Governance - IT Key Project,Strategic Project,Approving Body,Funding Source,Project Governance, Governance Approval Status,Strategic Theme,Strategic Driver,Legal / Reg / Mand, Discretionary / Non-Discretionary,shareholder Value, Internal Rate of Return (IRR),Payback in Years,Finacial Projection Analysis Required,Business Opportunity,Compliance Date,Estimated Duration, Funding Source
4. Project Status - Health, % Complete, Phase, Project Status, SDLC Phase
5. Project Dates - Start Dates, Actual Dates, Implementation Dates, Revised Dates
6. Project Budget & Forecasting
Estimated Cost to Charter
Estimated Project Cost
IT Annual Operational Cost
Incremental Annual Revenue
Incremental Annual Cost Reduction
Estimated IT Capital Budget
Original Approved Budget (Total)
Current Approved Budget (Total)
Estimated Technology Budget (Total)
7. Project Actual Data
Capital - Hardware
Capital - Software
Capital - Other
Capital - Contractor
Expense - Hardware
Expense - Software
Expense - Other
Expense - Contractor
Project ID Number
Vendor Number
Vendor Name
Invoice Number
Purchase Order Number
Transaction Date
Bank
Invoice Date
Invoice Amount
Account Id
Account Description
GL Date
Type
Project ID Number
Vendor Number
Vendor Name
Invoice Number
Purchase Order Number
Transaction Date

A normalized data model is a valuable input to the dimensional modeling process. It will show you the data dependencies not evident in the dimensional model.

Please validate if I have correctly identified all the entities based on the requirements:

1. PROJECT - Project for one snapshot period
2. PROJECT_STAKEHOLDER
3. PROJECT_CODE
4. PROJECT_BUDGET
5. PROJECT_GOVERNANCE
6. PROJECT_STATUS

DRAFT ER MODEL
-----------------

PROJECT (M:M) PROJECT_CODE
PROJECT (M:M) PROJECT_STAKEHOLDER
PROJECT (1:M) PROJECT_GOVERNANCE
PROJECT (1:1) PROJECT_BUDGET
PROJECT (1:M) PROJECT_STATUS

DRAFT DIMENSIONAL MODEL
----------------------------

1. F_PROJECT (Fact) - Project for one snapshot period (Actual Hours by Project by Resource Weekly) (Price=Rate * Hours)
2. D_PROJECT_STAKEHOLDER (Dimension)
3. D_PROJECT_GOVERNANCE (Dimension)
4. D_PROJECT_BUDGET (Dimension)
5. D_PROJECT_STATUS (Dimension)

6. F_PROJECT_SUMMARY (Total Cost to Date)

You may treat the project fact as the accumulating snapshot to track the project's lifecycle. You could have a number of role playing date keys in the fact to indicate the project process milestones. You may also need a bridge table to link projects to stakeholders, in addition to the project, stakeholder and other dimensions.

Obviously you have quite a few interesting metrics to analyse, so they should also be in your fact table. Part from those obvious numerics like cost, capital, expense, actual and budget, you may also need to store some durations (eg. in days) between those milestones. Like a typical accumulating snapshot fact, your fact table will contain many date keys and numeric values and you will periodically revisit the same project in the fact to update those date keys and durations accordingly until the project is completed or closed.