Hi Martin,

Cheers for posting your take on it. After discussing at length internally and taking your points (& Jeffs) into consideration I'm of the opinion that making the underlying dimensions...

• This reply was modified 2 years ago by  Niggles.

Hi,

Just to be inclusive I am including a few points that I came across in my research that I'm considering areas of concern in substituting a Dimension Scd2 load approach...

Hi Jeff,

This gives me more confidence that the approach is at least viable and I'm not losing my mind :). Thanks a mil for offering that.

Apologies for the confusion in...

Just thought for the sake of helping anyone reading through these posts that I'd include the following link which is relevant to the discussion.

Quote "you can forego the “Is this...

• This reply was modified 2 years ago by  Niggles.
• This reply was modified 2 years ago by  Niggles.

Hi Martin,

If the dimension is temporal (by temporal I mean system-versioned temporal table) then history would be tracked automatically. Having a type 1 change would mean an overwrite but the...

Hi Martin,

Thanks for chiming in. Ok let me present this scenario to you just so I'm clear;

Scenario 1)

On 01/01/2022 I create and load a dimension and assign all attributes as...

• This reply was modified 2 years ago by  Niggles.
• This reply was modified 2 years ago by  Niggles.

Hi Jeff,

Points 1,2 & 3 are helpful to me actually as I was trying to achieve the same in a different way but your approach is clear thanks.

As a general...

• This reply was modified 2 years ago by  Niggles.

Hi Jeff,

This is very helpful thanks a million. First of all, just to be clear, I have the IsActive flag on the underlying dimensions and not on the Fact table...

Hi Martin,

Thanks for responding much appreciated. Let me just provide a little more info;

In relation to tracking changes to the Appointment fact table, there is a requirement to make multiple...

Thank you very much and apologies I didn't see this before

Hi Jeff & Admin, apologies I didn't mean to be rude, I am just re-stating the question as I wasn't really clear so temporarily took it down. As always I...

Something else to think about... I needed to be able to use the BULK LOGGED recovery model and I'm pretty sure that both of these methods (CDC and CT)...

My personal feeling is that CDC is incredible overkill for simple DML logging of historical data both in complexity of use and design as well as causing excessive duplication...

Hi Jeff,

First of all thanks for your input much appreciated. The trigger sounds like an interesting approach although I have heard that they are not good performance wise...

Thanks to everyone who chimed in...I’m trying to figure out how to reply directly to a comment but haven’t got there yet 🙂