locked
Merge Invoice Header and invoice Line - Suggestions RRS feed

  • Question

  • We have an  Invoice Header and an Invoice Line table that we are looking forward to using cubes. I just want to get your thoughts on what's the pros and cons of merging the two tables verses having them as is.

    Thanks in advance.

    Monday, January 10, 2011 7:40 PM

Answers

  • Pro is the performance gain during data delivery and cube processing due to the denormalized structure
    Con is the cost involved with the update process. If the Invoice Header data has to be updated for any reason, the update might span many rows. Also, you have to analyze the data to figure out the storage overhead

    HTH


    -Remember to mark as helpful/the answer if you agree with the post.
    • Proposed as answer by Jerry Nee Wednesday, January 12, 2011 7:03 AM
    • Marked as answer by Jerry Nee Tuesday, January 18, 2011 7:13 AM
    Monday, January 10, 2011 8:49 PM
  • Hi,

    I have done this several times,

    1. If you merge the tables be sure to create a surrogate key that is primary for the invoice header and use a column that combine that skey and item number of the invoice as identifier on every row.

    2. Then make sure that all values(measures) that are summarized in the invoice header and are not present in the details be put on the first item row.

    3. keep track of the granularity of your dimensions

    4. Create a attribute dimension for invoice item numbers

    5. make sure that all other used dimensions connect at lowest possible granularity.

    Its a bit tricky, but keep track of the granularity of your dimensions! 


    ELVIS_OF_BI
    • Proposed as answer by Jerry Nee Wednesday, January 12, 2011 7:03 AM
    • Marked as answer by Jerry Nee Tuesday, January 18, 2011 7:13 AM
    Tuesday, January 11, 2011 5:05 PM

All replies

  • Pro is the performance gain during data delivery and cube processing due to the denormalized structure
    Con is the cost involved with the update process. If the Invoice Header data has to be updated for any reason, the update might span many rows. Also, you have to analyze the data to figure out the storage overhead

    HTH


    -Remember to mark as helpful/the answer if you agree with the post.
    • Proposed as answer by Jerry Nee Wednesday, January 12, 2011 7:03 AM
    • Marked as answer by Jerry Nee Tuesday, January 18, 2011 7:13 AM
    Monday, January 10, 2011 8:49 PM
  • Hi,

    I have done this several times,

    1. If you merge the tables be sure to create a surrogate key that is primary for the invoice header and use a column that combine that skey and item number of the invoice as identifier on every row.

    2. Then make sure that all values(measures) that are summarized in the invoice header and are not present in the details be put on the first item row.

    3. keep track of the granularity of your dimensions

    4. Create a attribute dimension for invoice item numbers

    5. make sure that all other used dimensions connect at lowest possible granularity.

    Its a bit tricky, but keep track of the granularity of your dimensions! 


    ELVIS_OF_BI
    • Proposed as answer by Jerry Nee Wednesday, January 12, 2011 7:03 AM
    • Marked as answer by Jerry Nee Tuesday, January 18, 2011 7:13 AM
    Tuesday, January 11, 2011 5:05 PM