Cost plans for projects - One fact table or several fact tables?

View previous topic View next topic Go down

Cost plans for projects - One fact table or several fact tables?

Post  bustaliz on Tue Nov 17, 2009 2:37 pm

Hi,
I'm currently struggling with my Dimensional Model. It is about projects with their related cost plans. Every project starts with an initial cost plan (called WIPL), the project is saved as version 0 and the WIPL attribute and the ACTUAL attribute in the database is set to Y. If the cost plan is updated in the course of the year the project is saved as a new version, here it would be version 1 and the ACTUAL attribute in the database is set to Y. The ACTUAL attribute of version 0 is set to N. If the ACTUAL version's cost plan is committed by a leader, the VIST attribute is set to Y. If another changes are made, version 2 is created and gets the ACTUAL attribute set to Y and version 2 to N.

So, the table would look like this when version 2 is the actual version:

Code:

Version  | 0 | 1 | 2 |
---------------------
WIPL    | Y | N | N |
VIST    | N | Y | N |
ACTUAL  | N | N | Y |

The information about the cost plans is stored in a separate table.

Now I want to create dimension and fact tables for the projects. I'm not sure, whether my current ideas are the right ones. So, my current idea is to make a fact table for every cost plan (therefore FACT_CPL_WIPL, FACT_CPL_VIST, FACT_CPL_ACTUAL) and one dimension "project" with dimension attributes WIPL, VIST, ACTUAL.

Or is it better to create one fact table (FACT_CPL) with three dimensions (Project_WIPL, Project_VIST, Project_ACTUAL).

The data is to be loaded every day and is to be stored in the fact table day-based.

So, what are your thoughts about this.
I'm thankful for every help

bustaliz

Posts : 4
Join date : 2009-11-17

View user profile

Back to top Go down

Re: Cost plans for projects - One fact table or several fact tables?

Post  ngalemmo on Tue Nov 17, 2009 5:21 pm

Why not have one fact table with project, version and status as dimensions? Status would be a junk dimension with the various settings of the WIPL, VIST and Actual attributes.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Cost plans for projects - One fact table or several fact tables?

Post  bustaliz on Tue Nov 17, 2009 7:13 pm

Thx for your answer. I've never heard about/used junk dimensions, but it seems to be a good idea for this problem.
But I don't understand why to create a version dimension, isn't it better to have it as an attribute in the project dimension?
Additionally the hierarchical project dim has several attributes like year, employee name, strategic issue etc.

/Edit
Is a junk dimension also feasible, when two attributes are Y at the same time? Because a project can be ACTUAL and WIPL at the same time, also VIST and ACTUAL.
The different cost plans are planned to be compared and isn't it better respective easier to have for each cost plan type (WIPL etc) a single fact table? Or doesn't make it a difference?

bustaliz

Posts : 4
Join date : 2009-11-17

View user profile

Back to top Go down

Re: Cost plans for projects - One fact table or several fact tables?

Post  ngalemmo on Wed Nov 18, 2009 12:01 pm

Version could simply be a degenerate dimension... i.e. storing the version number in the fact table. It may or may not make sense to make it a project attribute depending on what it means to the project (and the attributes you have for a project). To me, a different version of a project is still the same project... but then, its just an opinion.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Cost plans for projects - One fact table or several fact tables?

Post  Sponsored content


Sponsored content


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