SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Effective date updation in Fact records


Effective date updation in Fact records

Author
Message
koustav_1982
koustav_1982
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1311 Visits: 215
HI Team

This would be a common problem and I tried to search the net but could not find a good answer. The problem is
Say the model of the fact table is an insert only model , where the records are always getting inserted by the ETL process. A separate process would search if the inserted record already exist and if it does, will mark the end_date of that record . The new record will have an end_date as null.

I have achieved it through a self join on the table. We use Informatica , and a target lookup would have also helped, but caching the full table is expensive. Is there any other way to do it ?

Thanks
Jez-448386
Jez-448386
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3766 Visits: 1230
Output the new facts to a working table. Then use T-SQL to insert the new records and update the old ones.

Jez
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)

Group: General Forum Members
Points: 848095 Visits: 46691
While you're at it, I recommend making the new end-date = '9999' (equals 9999-01-01 when inserted into a DATETIME column) so that you're queries aren't plagued by the use of ISNULL or IS NULL and makes the possibility of future dating not only possible but identical in nature to normal date searches.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
koustav_1982
koustav_1982
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1311 Visits: 215
Yes Jez we have that implementation too . Thanks for suggesting that .

I like Jeff's idea.Will do it that ways .
koustav_1982
koustav_1982
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1311 Visits: 215
Bringing an old topic alive , this is what I am thinking .

We have a fact table that is , say 1000 columns wide and then lets say we have an additional column , an isactive column which , if 1 means that the fact record is active and if 0 means the record is inactive.

NOw if the same record comes in with the latest ETL , the older version of the record needs to be set to isactive = 0 and the latest one retains as isactive = 1 .

Lets say , we take the PK , the Natural Key and the isactive column to a separate table and instead of updating the 1000 column wide table on one flag column , we update this new table to switch the flags.

Downside is , the select queries have to join the 2 tables to get the active records.

Is there any obvious performance difference between updating 1 column in a 4 column wide table vs 1 columns in a 1000 column wide table ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search