April 27, 2012 at 9:03 am
HI Lynn,
Thanks again... I know you the tips and suggestions you gave me... I understand that you are trying to help me! 🙂
My issue is about the summary table... that I'll call it an aggregate table to be aligned with kimball Methodology. The question is that this agregate table has the transaction as the grain and can make some confusion for all those persons that follows the Kimball approaches.
Ok! CASE 01 is an aggregate table with transaction grain that aggregates data from line items.
Thank you guys.
Pedro
April 27, 2012 at 9:10 am
ppcentral (4/27/2012)
HI Lynn,Thanks again... I know you the tips and suggestions you gave me... I understand that you are trying to help me! 🙂
My issue is about the summary table... that I'll call it an aggregate table to be aligned with kimball Methodology. The question is that this agregate table has the transaction as the grain and can make some confusion for all those persons that follows the Kimball approaches.
Ok! CASE 01 is an aggregate table with transaction grain that aggregates data from line items.
Thank you guys.
Pedro
It doesn't have the transaction as the grain. It is a summary.
And I guess I'm done.
Good luck, hope you don't need data down the road that you decided not to capture.
April 27, 2012 at 9:23 am
The grain is the transaction...
But in a Transaction Fact Table Type the grain is the transaction per line item as Kimball refer in his methodology.
Regards, 🙂
Pedro
April 27, 2012 at 9:50 am
ppcentral (4/27/2012)
The grain is the transaction...But in a Transaction Fact Table Type the grain is the transaction per line item as Kimball refer in his methodology.
Regards, 🙂
Pedro
CASE 01 doesn't go to the line item level, it only goes to the order level.
April 27, 2012 at 9:54 am
Exactly... at the order level which has the TransactionID.
April 27, 2012 at 9:56 am
ppcentral (4/27/2012)
Exactly... at the order level which has the TransactionID.
Which is not what Kimball indicated based on this from your own post (emphasis is mine):
But in a Transaction Fact Table Type the grain is the transaction per line item as Kimball refer in his methodology
April 27, 2012 at 10:04 am
Of course...
One thing is the grain by the transaction (CASE 01)
Another thing is the grain by the transaction per item line (CASE 02)
Where in CASE 01 you have a row per transaction ID
And in CASE 02 you have a row per item line of each transaction ID
Do you agree?
April 27, 2012 at 10:08 am
ppcentral (4/27/2012)
Of course...One thing is the grain by the transaction (CASE 01)
Another thing is the grain by the transaction per item line (CASE 02)
Where in CASE 01 you have a row per transaction ID
And in CASE 02 you have a row per item line of each transaction ID
Do you agree?
Not completely, your CASE 02 lacks all the details of the transaction at the line item level. From the data you provided you are missing key elements, such as discounts allowed.
April 27, 2012 at 10:12 am
Thank you for the reply... I understand you if you assume that the same product in the same transaction could have different discounts, correct?
Take a look here:
http://en.wikipedia.org/wiki/Fact_table
Transactional
A transactional table is the most basic and fundamental. The grain associated with a transactional fact table is usually specified as "one row per line in a transaction", e.g., every line on a receipt. Typically a transactional fact table holds data of the most detailed level, causing it to have a great number of dimensions associated with it.
Sub-transactional
The sub-transactional table is used to store facts that represent events at a more detailed level than the transactional table. These events, known as Griffith facts after their inventor, are typically those events that occur during the processing of a transaction, for example "change returned" amount from a vending machine
What you think?
April 27, 2012 at 11:04 am
CASE 02
Date Customer TransactionID ProductID Quantity Sales Ammount
20120101 CLI01 FAC0005 PROD01 3 300 €
20120101 CLI01 FAC0005 PROD02 2 500 €
20120101 CLI01 FAC0006 PROD01 1 100 €
20120101 CLI02 FAC0007 PROD01 5 500 €
20120102 CLI01 FAC0008 PROD01 4 340 €
20120102 CLI02 FAC0009 PROD01 6 560 €
Looking at the above, it is obvious (to the most casual observer) that the unit price charged for PROD01 on TransactionID FAC0005, FAC0006, and FAC0007 is not the same price charged on either TransactionID FAC0008 or FAC0009. Also, the unit price charged on the last two transaction are different. This implies (or is it inferes) that there are discounts applied at the line item level. This information needs to be captured.
If not, here is another question your data warehouse won't answer: How much in discounts did we give per product, or How much in discounts did we give per product per sales region.
April 29, 2012 at 1:56 pm
ppcentral (4/27/2012)What you think?
Nice to see that for once Wikipedia got it right 😀
Seriously, I think you are focusing too much in meaningless formalities and too little in the task at hand which is to design a proper datamart.
Let me explain the point with a parallel in a pretty different activity. Soccer (football association) coaches rely on different drills to coach specific plays, an error that many fresh coaches do is to end up coaching the drill instead of coaching the play - focusing too much on the formalities of the specific drill and forgetting that the purpose of the drill was to coach a specific play. Do you see the difference?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 2, 2012 at 3:21 am
I'm also a teacher, for that reason I'm being soo specific and trying to get detail information about Kimball approaches.
Regards,
You're always answering with a question... and my question is simple and objective.
Thank you both for yoru kindly replies.
Pedro
May 2, 2012 at 6:49 am
ppcentral (5/2/2012)
You're always answering with a question... and my question is simple and objective.
Thank you both for yoru kindly replies.
Pedro
http://www.pedrocgd.blogspot.com[/quote%5D
No, I'm not always answering with a question. But you are missing the point of two of the questions I did leave you with. Those questions were not for you, but were examples of questions that your business users may ask you that would need to be answered using data from your data warehouse. They were an attempt to show you why the data you were showing us wasn't enough to be considered transactional.
Here are the questions I left you with in two of my previous posts. You need to determine what data you would need to capture for the data warehouse to be able to answer these questions.
How many items of each product are sold on a daily basis? Or how about, how many of each product are sold on a daily basis in each sales region?
How much in discounts did we give per product, or How much in discounts did we give per product per sales region.
May 2, 2012 at 6:56 am
Dear Lynn,
Thank you for your reply.
I didn't wrote that your questions were not valid.. Are very important if I need more information like you referred:
"How many items of each product are sold on a daily basis? Or how about, how many of each product are sold on a daily basis in each sales region?"
"How much in discounts did we give per product, or How much in discounts did we give per product per sales region."
For the previous answers I need to have information from the lowest level (line item) and also have the discount, units sold, taxes...
But my concern was about the fact table type of a table that has the TransactionID... I assume that all agree now that is an aggregate Fact table type... because aggregate line items data from a transaction... and natural that in this case (and typecal from an agregation) I'll lose some information like product.
Regards
Pedro
May 2, 2012 at 7:03 am
Must be a language thing because you are missing the my point. Your data warehouse needs to capture that information that you are missing from your aggregate fact tables. If you don't have that data your data warehouse is incomplete and who knows what else it may be missing.
Bottom line, the data warehouse needs to be able to rebuild completely the transactions from the OLTP system or it is incomplete.
I understand that we may have answered your question, that isn't in dispute now.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply