Dimension Tables Got Corrupted

Question

Post reply

Dimension Tables Got Corrupted

RModz

SSC Veteran

Points: 280
More actions
July 4, 2010 at 4:13 pm

#140898

This is just an interview question i got from one of interviewer. for which I did not knew answer.

Any insight on this welcome..

If we have dimension tables loaded and used surrogate keys for processing. Now what happens is the we loaded fact table too. What happens if the dimension tables gets corrupted and we do not have any backup of the dimension tables. So will the fact table still funstional and will give us correct results. Is there any we can get out dimension tables back from the fact table (keep in mind there is surrogate key used).

Do we need to rebuild the fact table by rebuilding the dimension table. Will it cost much time reuilding fact table. Is there any approach besides this.

Please let me know....

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Depends what he means by 'corrupted'

If he's talking about database corruption then you'll likely need to restore the database from backup as SQL throws high-severity errors when it encounters corruption.

If he meant something else, answer could be anything

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RModz SSC Veteran Points: 280 More actions · Answer 2

Thanks for showing up your interest.

Corrupted ...here in the sense could be - say..dimension tables got deleted or something else...just corrupted And we do not have backup for them.....basicallly i think what his expectation was just to ask i guess - whether we can recover dimension tables from just standlone fact table, or some thing like that provided that there are surrogate key used to load data. So can we recover this by any other means as we do not have backup? ...because i guess its just impossible to load manually the data just by looking 10s of millions of records and removing the surrogate keys and putting the data back to dimension tables....

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Well, it you think about the structure of star (and snowflake) schema, the fact table just contains the keys for the dimensions, not the entire dimension table (names and all other attributes), so it would not be possible to recreate the dimension table. (You do know how star and snowflakes schemas look, I assume, as you're interviewing for a BI position)

btw, if there are no backups, the DBA is not doing there job. 'No backups' is not a position that any sensible company should ever be in, so, as an interview question it's a bit stupid.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2872 More actions · Answer 4

As Gail intimated the question is a bit unrealistic and the answer could be anything.

What the interviewer in this instance was probably looking for was a bit of creativity.

So one thing you could have mentioned is possible recreation based on the data in the staging area (if it hadn't been updated).

Another area you could have mentioned is the control tables for the dimension (depending on the DW set up) which also may also be used for recreation

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 5

GilaMonster (7/4/2010)

'No backups' is not a position that any sensible company should ever be in, so, as an interview question it's a bit stupid.

Sometimes they ask stupid questions, just to see your reaction 😉

(but most of the times to watch you being creative of course)

On-topic:

if the dimension tables are lost and there are no back-ups, then your only option (as far as I can think of) is to rebuild the dimension using your source data (if your lucky, all of your dimension data still resides in staging, as someone else mentioned in this topic.)

After that, you need to remap your surrogate keys in the fact table using the business keys. If you can't get all the dimensional data back in the dimension (if the source has been long gone or something like that), then you need to map all the SK's to a "dummy SK", signifying the lost link.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Dane-472702 Newbie Points: 1 More actions · Answer 6

I think the other posters are right, the point here is to show some creativity. I also think the focus of this question was about restoring the surrogate keys themselves. If he specified the date table my first response would be, this situation would be one of the advantages of using a logical surrogate key for the date table. If you use a key like YYYYMMDD then it would be very easy to rebuild the detail table using the same surrogate keys for the same rows.

However if the situation is any other Dimension table things get a lot more complicated. The possibility of restoring the the Dimension table with accurate historical keys will depends on weather or not the ETL system is staging and archiving the necessary data to recreate all the records.