Answered by:
Merge Invoice Header and invoice Line - Suggestions

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 overheadHTH
-Remember to mark as helpful/the answer if you agree with the post.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_BITuesday, 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 overheadHTH
-Remember to mark as helpful/the answer if you agree with the post.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_BITuesday, January 11, 2011 5:05 PM