SLowly changing dimensions

[MrPeds]

Hi,

Not sure if this is the correct forum, so apologies if i have miss-posted!

Some of my colleagues maintain a number of Excel Spredsheet files that relate to information about our shops. e.g. the manager, the shop name, the business area, area manager and so on.

we want to be able to track changes to this information over time. I am sure we will need to import this data periodically into SQL, but I dont know the best way of going about this. should i create a single table for each ecel file, or simply set up 1 table for the information ?

I was curious if anybody else has had this problem?

Thanks,

MrPeds

 

[MDXer]

I would set up a single table that lists the appropriate columns and possible the record source (in this case possibly the file name). If you want to track changes you may want to utilize CRC Logic to handle the SCD changes.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[johnherman]

Agree with MDXer - single table. You can load it directly each time if you assign a unique key to each upload, for instance, the date and time, or a sequential number. This assumes that the user will tell you that they changed the Excel spreadsheet. If you need to determine yourself if the spreadsheet has changed, the CRC is a good idea.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[MDXer]

Applying CRC to the actual record is a way of determining if any record information has changed.

I currently implement Slowly Changing Dimension logic at the column rather than dimension level. I utilize CRCs to determine if a type 1 or type 2 change has occured resulting in the appropriate insert or update taking place. The crc at the file level would be a good way of determing if the file should even be loaded.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck