A friend of mine recently asked for assistance on creating a custom DLL. I’m a total n00b at this. But, i asked him, why he was pulling his hair over this.
He wants the DLL to implement data integrity in the cube. For example, in a customer dimension with SSN as an attribute, he was hoping, a DLL can be created to ensure that SSN is always 9 digit number.
I had no clue. However, I’ve suggested him to use constraints in the sql table, source data warehouse itself. Check constraint on SSN column can get the job done.
There are many benefits of using constraints in the data warehouse for this particular case:
- Check constraints are simple. Even people like me, with no knowledge on DLL’s can maintain them.
- They ensure that data in the warehouse is clean. You don’t want to allow 15 digit number for SSN, for example.
- Only the data warehouse contains source data for cubes. And if data here is clean, there is no way bad data is entered into the cube. At least in the perfect world.
- With clean data in the warehouse, reports can consume data from either warehouse or cubes.
What’s your approach?
What would be your approach to this problem? Did you create a DLL? What other solutions do you have?
You are welcome to post a comment below.
~Sam.
Sam Vanga 