Help with design of Factless Fact Table SCD

View previous topic View next topic Go down

Help with design of Factless Fact Table SCD

Post  abbi on Thu Jul 11, 2013 11:04 pm

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.

abbi

Posts : 8
Join date : 2010-10-11

View user profile

Back to top Go down

Re: Help with design of Factless Fact Table SCD

Post  ngalemmo on Fri Jul 12, 2013 12:07 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Help with design of Factless Fact Table SCD

Post  abbi on Sun Jul 14, 2013 3:03 pm

Thanks, ngalemmo.  I appreciate the explanation and the extra option!

abbi

Posts : 8
Join date : 2010-10-11

View user profile

Back to top Go down

Re: Help with design of Factless Fact Table SCD

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum