Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CoderMan1
Starting Member
6 Posts |
 Posted - 2008-03-06 : 23:41:35
|
| Need some opinions on how to query a set of tables designed like this:Main table: SubscriberTable has 80 columns, each column has a table which is written to via an update trigger everytime the column changes. Subscriber table is Type 1 dimension but the "Change" tables are Type 2 dimension. The "change" tables have 3 columns, SubscriberID, ChangeDate, NewValue. The requirement is to push a Type 2'd version of the Subscriber table into a data warehouse for historical reporting. Meaning the users need to be able to see what a Subscriber looked like on an exact date.Has anyone had any experience with this type of structure? If so, how did you join together the Subscriber table to all the change tables to extrapolate a "view" of the subscriber on each given day? You can assume that a record can only change once in a day, but not every column will change on the record.I was thinking of something like this:SELECT Subscriber.ID, (SELECT TOP 1 NewValue FROM Subscriber_FirstNameChanges b WHERE b.SubscriberID = Subscriber.ID AND b.ChangeDate <= @GivenDate ORDER BY b.ChangeDate DESC)FROM SubscriberThis would pull the most recent version of the subscribers FirstName based on a given date (I'd have to add in the 79 other change columns with almost identical sub queries). This would also take into consideration that there may not be change records for particular columns on particular dates because not all columns change at the same time/day and pull the most recent "version" of the subs FirstName since @GivenDate. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-06 : 23:47:23
|
[code]SELECT TOP 1 Subscriber.ID, b.NewValueFROM Subscriber s INNER JOIN Subscriber_FirstNameChanges sfnc on s.ID = b.SubscriberID AND b.ChangeDate <= @givendateOrder by b.ChangeDate desc[/code]
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
CoderMan1
Starting Member
6 Posts |
Posted - 2008-03-07 : 00:00:17
|
| The idea is to pull all subscribers for a given day, not 1 subscriber |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-07 : 00:18:51
|
LOL...Oops.I should have refilled my coffee and re-read the premise...let me take another look and see what i come up with. 79 other columns you would have to do the SAME thing to?....So as I scan this again, the 80 row table is currently unique, and has the most recent info. The update trigger sends the subscriberID, change date, and the new value ..to 79 different record tables...1 for each of the 80 columns.Ouch. I have some ideas, and have a similar issue I am dealing with, although I got into it early enough, and my change table stores the entire row of data, even if only 1 thing changed..along with the date. Will post back when I have something that isn't useless...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
CoderMan1
Starting Member
6 Posts |
Posted - 2008-03-07 : 00:21:54
|
| You understand it correctly. Just kind of a pain on a medium powered sql server with 60million rows in the subscriber t able and millions of rows per change table.I will probly end up having to load data 1 day at a time. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-07 : 00:23:54
|
ahhh...we'll see. You can email me or find me on IM if you like to discuss it. Each of the 79 tables is named [ColumnName] and the word [Changes] tacked on?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-07 : 00:32:06
|
Obviously there is no way this can be pushed into a table...it would be around a gazillion rows. If you need the entire 80 columns, for 60 million customers...presuming even only 1/2 of them had at least 2 changes is at minimum another 60 million row/80 column table. Talk about your data explosion...phew.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
"Interesting" table design...need opinions