Slowly Chaging Fact and Dimensions

[run400]

Hi,

I am relatively new to the world of Dimensional Modelling and would appreciate any insight/guidance.

We are currently in the process of modelling a Complaints Model for an Insurance Company. Essentially, a Customer will make a complaint and this is logged onto the system and progressed. Throughout this process numerous changes can be made, very much due to stages in the process (i.e. complaint, made, complaint officially opened on system and complaint closed). At the same time, due to limitations on the front-end system it is possible that a number of mistakes are made when entering a complaint onto the system and these will have to be corrected. It is this part of the model I am currently having issues with.

There are a range of Dimensions where this history must be kept so it makes sense to have a Type 2 Slowly Changing Dimension for these attributes with Start and End Dates included. At the same time, there is a Complaint Fact to hold various measures such as compensation paid. Although it won't often change, it is possible this compensation value entered may be incorrect and will have to be updated. This historical change must be held in the system so they can track by why a complaint reported last week at £10 pound compensation has now changed this £20.

For this fact I am thinking of including a Start and End Date and joining to a Calendar Dimension. I realise I don't really need the Calendar Dimension but it will be useful for the front-end reporting tool that is being used. Does this seem like a valid approach to use? We considered creating Daily Snapshots, but this feels like overkill as it is possible that the values for a Complaint may never change and storing the same values on a daily basis could greatly increase the size of the table.

Any other approaches/thoughts would be greatly appreciated. If I am not clear on anything please let me know and I'll attempt to clarify.

[hesh]

Hi,

May be you are overwhelmed with the inputs from the system :).... trust me For interim forget about corrections to the complaints , just concentrate on complaints and their stages. Later probably you need an other fact table for complaint corrections.


Thanks,
Hesh.

[run400]

Thanks for the reply.

In terms of modelling the stages, that's something that I believe we have captured pretty well (accounting for the various stages that is).

I suspect my explanation has made the initial view seem more complicated than it is.

The capturing Complaint corrections that is really critical to a number of key requirements and we are now looking at this on it's own to begin with. I suspect a seperate Fact will be required to deal with this and it was the approach suggested in my original post that was being considered.

[Sponsored content]