April 26, 2012 at 9:27 am
Dear friends,
Today I post another issue related with Kimball Fact Table Types...
The following cases are two examples of Fact Tables in a dimensional model. Which Fact Table Type you consider in both cases? Transactional in both cases?
CASE 01
Date Customer TransactionIDSales Ammount
20120101 CLI01 FAC0005800 €
20120101 CLI01 FAC0006100 €
20120101 CLI02 FAC0007500 €
20120102 CLI01 FAC0008340 €
20120102 CLI02 FAC0009560 €
CASE 02
Date Customer TransactionIDProductID Sales Ammount
20120101 CLI01 FAC0005PROD01 300 €
20120101 CLI01 FAC0005PROD02 500 €
20120101 CLI01 FAC0006PROD01 100 €
20120101 CLI02 FAC0007PROD01 500 €
20120102 CLI01 FAC0008PROD01 340 €
20120102 CLI02 FAC0009PROD01 560 €
April 26, 2012 at 11:40 am
ppcentral (4/26/2012)
Dear friends,Today I post another issue related with Kimball Fact Table Types...
The following cases are two examples of Fact Tables in a dimensional model. Which Fact Table Type you consider in both cases? Transactional in both cases?
CASE 01
Date Customer TransactionIDSales Ammount
20120101 CLI01 FAC0005800 €
20120101 CLI01 FAC0006100 €
20120101 CLI02 FAC0007500 €
20120102 CLI01 FAC0008340 €
20120102 CLI02 FAC0009560 €
CASE 02
Date Customer TransactionIDProductID Sales Ammount
20120101 CLI01 FAC0005PROD01 300 €
20120101 CLI01 FAC0005PROD02 500 €
20120101 CLI01 FAC0006PROD01 100 €
20120101 CLI02 FAC0007PROD01 500 €
20120102 CLI01 FAC0008PROD01 340 €
20120102 CLI02 FAC0009PROD01 560 €
I do not think all "lines" of the originating transactions are represented in either one of these tables therefore these are not Transactional Fact tables.
_____________________________________
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.April 26, 2012 at 11:48 am
PaulB-TheOneAndOnly (4/26/2012)
ppcentral (4/26/2012)
Dear friends,Today I post another issue related with Kimball Fact Table Types...
The following cases are two examples of Fact Tables in a dimensional model. Which Fact Table Type you consider in both cases? Transactional in both cases?
CASE 01
Date Customer TransactionIDSales Ammount
20120101 CLI01 FAC0005800 €
20120101 CLI01 FAC0006100 €
20120101 CLI02 FAC0007500 €
20120102 CLI01 FAC0008340 €
20120102 CLI02 FAC0009560 €
CASE 02
Date Customer TransactionIDProductID Sales Ammount
20120101 CLI01 FAC0005PROD01 300 €
20120101 CLI01 FAC0005PROD02 500 €
20120101 CLI01 FAC0006PROD01 100 €
20120101 CLI02 FAC0007PROD01 500 €
20120102 CLI01 FAC0008PROD01 340 €
20120102 CLI02 FAC0009PROD01 560 €
I do not think all "lines" of the originating transactions are represented in either one of these tables therefore these are not Transactional Fact tables.
Have to agree. Nothing there tells how many of each product was sold on each order.
April 26, 2012 at 11:49 am
HI Paul,
Thanks for you reply.
Imagine the case that the previous step demonstrate replicate the orginal sales invoice... That you in a E-R model has the something like this to represent an invoice:
HEADER
TransactionID (PK)
Data
Customer
DETAIL_TABLE (
TransactionID (PK)
TransactionLineID (PK)
ProductID (FK)
Amount
OR
DETAIL_TABLE2 (
TransactionID (PK/FK)
ProductID (PK/FK)
Amount
Better now?
How is the fact table (type Transactional) resulting from this?
Regards and thank you!
Pedro
April 26, 2012 at 11:55 am
The CASE 02 is the fact Table that has the more detailed data... And from them you can see that the count of products from each order(transaction) is
FAC00052
FAC00061
FAC00071
FAC00081
FAC00091
Correct?
Now, you can have the CASE 01 that gives you just the Date and Customer information and also the transactionID... This is a real Transaction type? if not, which type is for you?
Thank you!!
April 26, 2012 at 12:11 pm
ppcentral (4/26/2012)
The CASE 02 is the fact Table that has the more detailed data... And from them you can see that the count of products from each order(transaction) isFAC00052
FAC00061
FAC00071
FAC00081
FAC00091
Correct?
Now, you can have the CASE 01 that gives you just the Date and Customer information and also the transactionID... This is a real Transaction type? if not, which type is for you?
Thank you!!
Neither. How many of each product was sold on each order? Nothing in the details provides that information.
April 26, 2012 at 12:17 pm
Thank you for the reply!
Ok... I missed the quantity sold, but the doubt still present.
CASE 02
Date Customer TransactionIDProductIDQuantitySales Ammount
20120101CLI01FAC0005PROD013300 €
20120101CLI01FAC0005PROD022500 €
20120101CLI01FAC0006PROD011100 €
20120101CLI02FAC0007PROD015500 €
20120102CLI01FAC0008PROD014340 €
20120102CLI02FAC0009PROD016560 €
The question is objective... Which type of fact table you have if I decide to have the fact table CASE 01 just with dimensions Date, Customer and the Degenerate Dimension TransactionID?
The CASE 02 we all think that it's a true Fact Table type transactional, correct? And the CASE 01?
Thank you!
April 26, 2012 at 12:26 pm
ppcentral (4/26/2012)
Thank you for the reply!Ok... I missed the quantity sold, but the doubt still present.
CASE 02
Date Customer TransactionIDProductIDQuantitySales Ammount
20120101CLI01FAC0005PROD013300 €
20120101CLI01FAC0005PROD022500 €
20120101CLI01FAC0006PROD011100 €
20120101CLI02FAC0007PROD015500 €
20120102CLI01FAC0008PROD014340 €
20120102CLI02FAC0009PROD016560 €
The question is objective... Which type of fact table you have if I decide to have the fact table CASE 01 just with dimensions Date, Customer and the Degenerate Dimension TransactionID?
The CASE 02 we all think that it's a true Fact Table type transactional, correct? And the CASE 01?
Thank you!
Again, not quite. At the transactional level what you have posted still doesn't tell the whole story of each sale. What is the unit price of Prod01? Two orders obviously sold this product at two different prices from the other orders.
April 26, 2012 at 3:30 pm
Actually is pretty straight forward, rows in a Transactional Fact table have to show the level of detail of each and every "line" of the originating transactions.
If the originating transaction has three "lines" telling you...
On 4/26/12 Customer XX bought 10 units of Product AA @ $3 each, Invoice 123
On 4/26/12 Customer XX bought 20 units of Product BB @ $1 each, Invoice 123
On 4/26/12 Customer XX bought 30 units of Product CC @ $5 each, Invoice 123
... the Transactional Fact table has to allow you to see that information.
_____________________________________
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.April 27, 2012 at 2:56 am
Paul and Lynn,
Thank you for your replies! Although I think you are not focusing in my issue... I know I can include unitPrice (but I may decide to not include it) or even other usefull information related to each line of the invoice like discount, tax.... So, with more or less atributes we all know that the CASE 02 is a transactional fact table.
Now, the question is: Which type of fact table you have if I decide to have the fact table CASE 01 just with dimensions Date, Customer and the Degenerate Dimension TransactionID? (Because I could not want to include the line items data.... I know I dont have the product dimension and other usefull information, but I'll have less amount of records...)
For the CASE 01 which type of fact table you think it is? (The grain is also the transaction.... but not the line item of the transaction)
Regards and thanks,
Pedro
April 27, 2012 at 5:57 am
ppcentral (4/27/2012)
I can include unitPrice (but I may decide to not include it) or even other usefull information related to each line of the invoice like discount, tax.... So, with more or less atributes we all know that the CASE 02 is a transactional fact table.
More or less?
Do you realize that any data not captured at this time will be lost forever?
If you cannot recreate the original transaction then you do not have a transactional fact table.
What kind of table is it? may be a not so well designed one 😀
ppcentral (4/27/2012)
For the CASE 01 which type of fact table you think it is? (The grain is also the transaction.... but not the line item of the transaction)
CASE 01 is a summary of each transaction, isn't it?
When the granularity of a FACT table goes up to the summarization of the originating document I prefer to call them Summary Tables.
_____________________________________
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.April 27, 2012 at 6:33 am
Paul,
Thanks again for your reply...
I made the CASE 02 just for explain my issue.
And finally you answered my question! 🙂
Ok... you think the CASE 01 is a Summary Table... of the transaction.
This fact table from CASE 01 is often used in real world... and even using the both CASE 01 and CASE 02... the big disadvantage of the CASE 02 is that the number of records is too big comparing with CASE 01.
Thank you!
Pedro
April 27, 2012 at 7:37 am
ppcentral (4/27/2012)
Paul,Thanks again for your reply...
I made the CASE 02 just for explain my issue.
And finally you answered my question! 🙂
Ok... you think the CASE 01 is a Summary Table... of the transaction.
This fact table from CASE 01 is often used in real world... and even using the both CASE 01 and CASE 02... the big disadvantage of the CASE 02 is that the number of records is too big comparing with CASE 01.
Thank you!
Pedro
What do you mean by disadvantage and that the number of records is too big? Your summary tables are built off of the transactional tables, not in place of them. The data warehouse needs to capture all the information from the OLTP system, it needs to be able to rebuild the complete transaction if needed.
April 27, 2012 at 7:59 am
Thank you Lynn for you reply.
Again, I need to have a Fact Table (CASE 01) with just the information stored in the ORDER_Header (DATE, CUSTOMER and TRANSACTIONID) and the aggregate value of the SALES from ORDER_Detail source table. I have huge number of line items in ORDER_Detail that I want to avoid... This is my requirement.
Stop try to convince me to get all possible information! 🙂 COnsidering that I'll never need those detail information and other several vantages (and disadvantages)
So... which name you give to the CASE 01 fact table?
Regards and thanks!
Pedro
April 27, 2012 at 8:54 am
ppcentral (4/27/2012)
Thank you Lynn for you reply.Again, I need to have a Fact Table (CASE 01) with just the information stored in the ORDER_Header (DATE, CUSTOMER and TRANSACTIONID) and the aggregate value of the SALES from ORDER_Detail source table. I have huge number of line items in ORDER_Detail that I want to avoid... This is my requirement.
Stop try to convince me to get all possible information! 🙂 COnsidering that I'll never need those detail information and other several vantages (and disadvantages)
So... which name you give to the CASE 01 fact table?
Regards and thanks!
Pedro
It is a summary table. If that is all you are going to capture, and you are 100% sure you will NEVER need more detailed information ever from your warehouse, go for it. We are just trying to make sure that you are aware of the risks if the 100% will never change requirement changes and you don't have the information available anymore.
The data warehouse should be a repository of all the data and you should be able to recreate any transaction to any level of detail. Your summary table belongs in a data mart, a subset of the data warehouse that is used to answer specific questions.
Here is a question your summary table can't answer, 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?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply