Help with design of Factless Fact Table SCD
2 posters
Page 1 of 1
Help with design of Factless Fact Table SCD
I have a log/summary type Fact table which currently has a row for each day and type with columns for different counts. I believe it is a Factless Fact table. Below is the create statement. The DateKey column is when the event occur. Everything works well except for a new column we need to add which will track another piece of information on a daily basis. This new piece of information can change on a daily basis. So on 7/10/13, on 7/5/13 there was a count of 500 and then on 7/11/13 on 7/5/13 there was a count of 800. As I looked into it more, I believe this is happening for all of our counts and it was designed to write once and ignore any future changes!
I can think of 3 possible ways of handling this and I'd like to see if there are any other design possibilities that I may have not thought of:
1) Overwrite the value every day.
(CONS... don't like this since it is not consistent with the other counts and we lose history.) Kind of like SCD1
2) Take the first value and ignore the future differences.
(PROS and CONS... this is consistent with the other counts but not reflecting an accurate value of what is current).
3) Redesign the table to include another date columnn such as daterecorded. Then there will be multiple rows for each message, dateevent, and release.
(PROS and CONS... probably the correct design but a big change and rewrite of existing reports, cube, etc.)
We are going to add another count column called DownloadsSuccessfulCount.
CREATE TABLE [dbo].[FactMessageLog](
[DateKey] [datetime] NOT NULL, --this should be date since the time is always all 0's!
[MessageKey] [dbo].[ID] NOT NULL,
[ReleaseKey] [dbo].[ID] NOT NULL,
[ApplicationKey] [dbo].[ID] NULL,
[DownloadsCount] [int] NULL,
[DisplayedCount] [int] NULL,
[ViewedCount] [int] NULL,
[ClosedCount] [int] NULL,
[ErrorsCount] [int] NULL,
[LaterCount] [int] NULL,
[TimeKey] [datetime] NULL,
[LastUpdate] [datetime] NULL
) ON [PRIMARY]
GO
The Unique key to the table is DateKey, MessageKey and ReleaseKey.
Any help will be kindly appreciated.
I can think of 3 possible ways of handling this and I'd like to see if there are any other design possibilities that I may have not thought of:
1) Overwrite the value every day.
(CONS... don't like this since it is not consistent with the other counts and we lose history.) Kind of like SCD1
2) Take the first value and ignore the future differences.
(PROS and CONS... this is consistent with the other counts but not reflecting an accurate value of what is current).
3) Redesign the table to include another date columnn such as daterecorded. Then there will be multiple rows for each message, dateevent, and release.
(PROS and CONS... probably the correct design but a big change and rewrite of existing reports, cube, etc.)
We are going to add another count column called DownloadsSuccessfulCount.
CREATE TABLE [dbo].[FactMessageLog](
[DateKey] [datetime] NOT NULL, --this should be date since the time is always all 0's!
[MessageKey] [dbo].[ID] NOT NULL,
[ReleaseKey] [dbo].[ID] NOT NULL,
[ApplicationKey] [dbo].[ID] NULL,
[DownloadsCount] [int] NULL,
[DisplayedCount] [int] NULL,
[ViewedCount] [int] NULL,
[ClosedCount] [int] NULL,
[ErrorsCount] [int] NULL,
[LaterCount] [int] NULL,
[TimeKey] [datetime] NULL,
[LastUpdate] [datetime] NULL
) ON [PRIMARY]
GO
The Unique key to the table is DateKey, MessageKey and ReleaseKey.
Any help will be kindly appreciated.
abbi- Posts : 8
Join date : 2010-10-11
Re: Help with design of Factless Fact Table SCD
A factless fact table is one with no measures, all it contains are dimensions. Since yours has measures (the counts), its a fact table.
What you describe in options 1 & 2 is a snapshot fact. It contains totals by some time period, in this case, daily counts. And they can be updated in place or loaded once and remain as is. You have pretty much covered the pros and cons of this type of table. Simple, but no history.
The 3rd option is referred to as an accumulating snapshot. You have an additional dimension as to when the change occurred. There are two ways of implementing this. One is to add a new row with a complete restatement of all counts (like a type 2 dimension as you stated). To be useful it helps if you have a current row flag in the table as well. The other method is to append deltas. The new row would only contain the difference from current totals. This is a bit more complex to update if you are only receiving new, complete rows and it requires you sum all rows for a given date to get the values for that date. But it does have the advantage of being able to easily query movement of the counts over time.
What you describe in options 1 & 2 is a snapshot fact. It contains totals by some time period, in this case, daily counts. And they can be updated in place or loaded once and remain as is. You have pretty much covered the pros and cons of this type of table. Simple, but no history.
The 3rd option is referred to as an accumulating snapshot. You have an additional dimension as to when the change occurred. There are two ways of implementing this. One is to add a new row with a complete restatement of all counts (like a type 2 dimension as you stated). To be useful it helps if you have a current row flag in the table as well. The other method is to append deltas. The new row would only contain the difference from current totals. This is a bit more complex to update if you are only receiving new, complete rows and it requires you sum all rows for a given date to get the values for that date. But it does have the advantage of being able to easily query movement of the counts over time.
Re: Help with design of Factless Fact Table SCD
Thanks, ngalemmo. I appreciate the explanation and the extra option!
abbi- Posts : 8
Join date : 2010-10-11
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Fact Table or Factless Table: Please Suggest
» Same attribute in multiple dimensions or Create new dimension?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Customer and Factless Fact Table(s)
» Fact Table or Factless Table: Please Suggest
» Same attribute in multiple dimensions or Create new dimension?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Customer and Factless Fact Table(s)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum