none
Designing a Fact Table RRS feed

  • Question

  • Hi All,

    I need help on designing a Fact table

    I have a Contract Table that has Start Date, End Date, and Stop Date. Each contract has different chargetype.

    ChargeType ID | Contract No. | Start Date | End Date | Stop Date

    1001 | Con10001 | 2011-01-01 | 2026-12-31 | NULL

    1002 | Con10001 | 2011-01-01 | 2026-12-31 | NULL

    1003 | Con10001 | 2011-01-01 | 2026-12-31 | NULL

    1001 | Con10002 | 2012-01-01 | 2011-12-31 | NULL

    1002 | Con10002 | 2012-01-01 | 2011-12-31 | NULL

    1003 | Con10002 | 2012-01-01 | 2011-12-31 | NULL

    1001 | Con10003 | 2011-01-01 | 2025-12-31 | 2012-12-31

    1002 | Con10003 | 2011-01-01 | 2025-12-31 | 2012-12-31

    1003 | Con10003 | 2011-01-01 | 2025-12-31 | 2012-12-31

    Start Date is the start of Contract and End Date is the of the Contract, while Stop Date is the where the termination date, always between the Start Date and End Date.

    I would like to know how can i design a fact table based on these table. Obviously i will be having 3 dimension, Date, ChargeType and Contract Dimension. My problem is I dont know to design a fact table with this scenario.

    What they want to know is how many valid, expired, and terminated contract per day, month, quarter and year.

    Thanks in advance.

    Saturday, February 11, 2012 12:54 PM

Answers

  • Hi,

    I understand you are basically there. I would add a surrogate key, call it say ContractID of type int with an identity seed within your contract dimension. Then replace the Contract No with this surrogate key in your fact table. This would complete the design of your fact table. Surrogate keys are not only best practice they enable you to future proof your data warehouse in the event you have a different data source for contracts in the future.

    I'd make the columns foreign keys within the fact table which you have already correctly identified as columns to map to respective dimensions.

    This would natually lead you to be able to write very simple queries to identify how many valid, expired, and terminated contracts per day, quarter and year.

    I guess you are already aware that analysis services has a good wizard for creating date / time dimensions.

    I hope this helps.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood




    Saturday, February 11, 2012 2:10 PM
  • In addition to what Kieran allready stated, you may want to define expiry. If contract is terminated, can he expire as such? Answer to that question can bring a different fact design.

    Also, if you need to allow for queries like "How many active contracts on specific date", you should read on M2M implementations. Not directly relevant for your fact design, but coiuld be important.

    Marko Culo

    Tuesday, February 14, 2012 6:38 AM

All replies

  • Hi,

    I understand you are basically there. I would add a surrogate key, call it say ContractID of type int with an identity seed within your contract dimension. Then replace the Contract No with this surrogate key in your fact table. This would complete the design of your fact table. Surrogate keys are not only best practice they enable you to future proof your data warehouse in the event you have a different data source for contracts in the future.

    I'd make the columns foreign keys within the fact table which you have already correctly identified as columns to map to respective dimensions.

    This would natually lead you to be able to write very simple queries to identify how many valid, expired, and terminated contracts per day, quarter and year.

    I guess you are already aware that analysis services has a good wizard for creating date / time dimensions.

    I hope this helps.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood




    Saturday, February 11, 2012 2:10 PM
  • In addition to what Kieran allready stated, you may want to define expiry. If contract is terminated, can he expire as such? Answer to that question can bring a different fact design.

    Also, if you need to allow for queries like "How many active contracts on specific date", you should read on M2M implementations. Not directly relevant for your fact design, but coiuld be important.

    Marko Culo

    Tuesday, February 14, 2012 6:38 AM
  • Considering there is only a single date dimension , i am thinking the fact table would look like

    facttable ( ContractId , datekey, chargetype , is_started , is_Ended , Is_Stopped)


    Tuesday, February 21, 2012 7:21 AM