April 19, 2012 at 4:35 am
Dear friends,
Could you help me to clarify this? The issue is related with Kimball Fact Tables types. I know the three types (that has metrics associated): Transactional, Periodic Snapshot and Accumulative Snapshot, and also others like FactLess, Factless Coverage and even Bridge Tables.
Following Kimball approaches, If I have a fact table aggregated by date and customer with all possible combinations we have a Periodic Snapshot, but if we don’t have all combinations which type of fact able is this? I think Kimball didn’t add any type of this kind of fact tables… (He refer agreggate tables but I'm talking about FACT tables used in real world) could you give me your opinion? See this example:
I have 3 customers:
CASE 01 (I have no doubt following Kimball approach that this is a Periodic Snapshot) / With all possible combinations
Date Customer Sales
20120101 CLI01 900
20120101 CLI02 500
20120101 CLI03 0
20120102 CLI01 340
20120102 CLI02 560
20120102 CLI03 0
CASE 02 (I have no doubt following Kimball approach that this is a Transactional) / The grain is a transactionDate Customer Sales InvoiceID
20120101 CLI01 800 FAC0005
20120101 CLI01 100 FAC0006
20120101 CLI02 500 FAC0007
20120102 CLI01 340 FAC0008
20120102 CLI02 560 FAC0009
CASE 03 (This is the case I have doubts)
Date Customer Sales
20120101 CLI01 900
20120101 CLI02 500
20120102 CLI01 340
20120102 CLI02 560
In the case 03 I have data aggregated by Date and customer but I don’t have all the possible combinations like a normal Periodic Snapshot. Which type you consider here?
The Fact Table in case 3 could be generated from a Transactional (like the periodic) but could be generated from data coming from sources that could even not have a transaction associated.
Any feedback?
Regards,
Pedro
April 19, 2012 at 5:45 am
ppcentral (4/19/2012)
If I have a fact table aggregated by...
Risking to start some kind of religious war here I would say that "facts" are by definition granular, nor aggregated. A FACT table in its original sense should not include aggregated data.
_____________________________________
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 19, 2012 at 7:14 am
To answer the initial question. I am not sure there is a Kimball name for this type of design. However, I am going to jump in and coin a phrase and call it a Dense Periodic Snapshot. Feel free to use this in all your converstions and credit me every time. 😀
I would disagree with the second post that fact tables should not be aggregated. I do agree that it is important to state the grain of every fact table and make sure it is loaded properly according to the grain. At least one of the Kimball books I've read ( One of the Data Warehouse Toolkit books ) listed fact tables of different grains (imply aggregations) for a single data mart/warehouse solution. I know one of my solutions has two grains of fact table because it was too difficult to design the rollups according to our existing business rules.
April 19, 2012 at 7:39 am
Just my opinion, but the reason that your CASE 3 does not appear in Kimball's approach is because you wouldn't create such a fact table. Why would you exclude client three from your facts because they have not have activity for a specific date? This would make using the fact a more complicated join. Using CASE 3 you could have a client appearing and disappearing within the dataset based on activity within dates.
I may not be following the post, but I'm just not sure why you would do this approach.
As for aggregations in a FACT table, I agree with Daniel that aggregations are fine, but granularity rules have to be followed so all records are at the same level.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
April 19, 2012 at 7:49 am
I understand your perspective... and was an interesting answer. But the reality is that you have these agregate tables in real world scenarios assuming the role of a fact Table with several dimensions linked to it. And I think that some of the Kimball fact tables could have aggregated data... (except transactional)
I like the "Dense Periodic Snapshot"...:-) Because is not a Kimball Periodic Snapshot (because we dont have in this case all the possible combinations) and also is not a tempororal refered by Davide Mauri in SQLPAss and SQLBITS conferences.
More feedback from you or other users?
Thank you!!
April 19, 2012 at 7:56 am
Thanks for yor reply.
I exclude customer 3 from CASE 3 like is excluded by a normal transactional fact table in CASE 2. And this is an example just to explain the CASE 3. There is more cases where you need to have a snapshot of data in a point in time without the need to record all the possible combinations.
What you think?
I like to discuss this with high knowledge person like you guys!
Regards,
Pedro
April 20, 2012 at 4:30 am
Any more feedback replies? 🙂
April 20, 2012 at 12:54 pm
ppcentral (4/19/2012)
@Paul,I understand your perspective... and was an interesting answer. But the reality is that you have these agregate tables in real world scenarios assuming the role of a fact Table with several dimensions linked to it. And I think that some of the Kimball fact tables could have aggregated data... (except transactional)
I know; I been accused of being more Kimballistic than Kimball 😀
_____________________________________
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.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply