Periodic snapshot table & unchanging values

Hello,

I'm working on the design of a data warehouse in which we transfer, every month, financial information about current deals, a kind of snapshot.

My problem is : some facts should remain unchanged after first input, for example : project kickoff costs, initial sales, initial dates, etc., and I don't know how to properly design it, how to respect dimension modeling approach and star schema.

Here's the three solutions I imagine :

1/ Only one fact table in which we control that concerned data remains unchanged from a snapshot to the next one. The problem is that there will be a lot of duplicated informations (around 30 to 40% of fields)



2/ A second fact table called "Deal header" which will store unchanging information. The point is that there will be a relation between the two fact tables.



3/ A second fact table (deal header) like the previous solution, with a "deal" dimension.



Thank you for any advice !

What is the grain of your FACT table?

manickam wrote:What is the grain of your FACT table?

It's a monthly based snapshot (one record per month for each deal).

You can have your fact tabe like this.

DEAL_NO YEAR MONTH INITIAL_COST COST1 COST2 KICKOFF_DATE DATE1 DATE2


COST1, COST2, DATE1, DATE2 fields can be refreshed every time.


This is may assumption, but let us wait for the comments from experts.

Yeah, so it's solution 1 : use the ETL to insure that some field keep unchanged record after record.

Hi,
can you clarify whether or not this is a snapshot? If it is a snapshot then you would just load your table with all your deals once a month, including an FK to your Month Dimension, and you obviously make sure that people don't run queries that aggregate across months (unless they are very clear about what the results they get will mean).

If it's not a snapshot then what is it? Remember, a fact table holds the record of an event so what event is it that your fact table is recording? If it is just the state of your deals at the end of each month then it is a snapshot.
What is the business question that this fact table is meant to be answering?

Regards,

It is a snapshot (i.e. : state of our deals at the end of the month).

My question is : some data have to remain unchanged throught time ; should I create another fact table to store these unchanging measures, or should I keep them in a unique fact table, which will lead to redundancy ?

I'm a bit confused - a snapshot is what it says it is: a snapshot of a moment in time. Once you have created the records for a month they will never change - why do you think data in your DW will be changing through time?
For every month you will create one new record per deal/month - with all the measures and dimension values applicable for that month. You would not touch the records created in previous months.
If you are talking about Dimension attribute values changing over time then you would just implement a slowly-changing dimension design for any dimension where you need to fix the dimension attribute values to a point in time

Sorry if I'm not clear (I'm French), lets get an example :

on my record I got two "types" of measure : initial/kickoff costs & revised costs.

Once a initial / kickoff cost has been set for a deal, it must stay unchanged. We will keep the same values for theses fields for each new records of the same deal.
Revised costs could change every month.

For example :

1/ January record : DEAL#1 ; JANUARY ; INITIAL-COST : 100 ; KO-COST : 0 ; REVISED-COST : 0 (initial cost has to remain 100 for each future row)
2/ February record : DEAL#1 ; FEBRUARY ; INITIAL-COST : 100 ; KO-COST : 120 ; REVISED-COST : 120 (KO cost has to remain 120 for each future row)
3/ March record : DEAL#1 ; MARCH ; INITIAL-COST : 100 ; KO-COST : 120 ; REVISED-COST : 130
4/ April record : DEAL#1 ; MARCH ; INITIAL-COST : 100 ; KO-COST : 120 ; REVISED-COST : 150
Etc.

My question is : should I keep all fields in a single fact table or should I split it in two fact tables, one with actual INITIAL/KO costs (a single row for each deal), and the other one with revised costs (a record for each deal & month) ?

By design, fact tables are independent entities. A design that has direct relationships between fact tables is not a dimensional design, it is an ER (entity relational) design.

Create your snapshot as an independent table. Include whatever dimensions and measures you want. How you load it is an ETL issue, not an design issue.

Sure, that's why I imagined the solution 3 : two fact tables with a common "deal" dimension (containing deal key).

But it's okey for me to have only one fact table : I will just have more redondant information and a bit more treatment in the ETL part.

Yes, basically you would place applicable header dimensions into the snapshot fact table.  This is standard procedure.