Slowly Chaging Fact and Dimensions

View previous topic View next topic Go down

Slowly Chaging Fact and Dimensions

Post  run400 on Sun Jul 15, 2012 10:58 am

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.

run400

Posts : 2
Join date : 2012-07-15

View user profile

Back to top Go down

Re: Slowly Chaging Fact and Dimensions

Post  hesh on Mon Jul 16, 2012 9:27 am

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.

hesh

Posts : 12
Join date : 2011-08-16

View user profile

Back to top Go down

Re: Slowly Chaging Fact and Dimensions

Post  run400 on Mon Jul 16, 2012 4:00 pm

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.

run400

Posts : 2
Join date : 2012-07-15

View user profile

Back to top Go down

Re: Slowly Chaging Fact and Dimensions

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