osmereqork (7/10/2013)
Hi,confusing myself with possible options for this scenario -
Reporting from our DW must be in both English and French, therefore i maintain the standard dimension in english and clone it through a translation process to create the equivalent in French.
I get separate data feeds coming from UK and French system to update this dimension. The UK data contains a text field containing 'Day', the french data says 'Jour'.
The challenge for me is how i control this through SSIS. Current thinking is that for the french source data i could firstly pre-translate the field back into english, push that through the normal dimension update process, then re-translate and clone back into the french dimension.
- should i re-stage the pre-translated data or try manage it all within a data flow?
- whats the best method that will cope with potentially having 20+ translatable fields in a dimension. I don't really want to be performing 20+ individual lookups if i can avoid it.
- I also need to consider the introduction of further languages down the line
any help appreciated
If I understand correctly, you are receiving identical feeds - just one is French and the other is English?
If that is correct, is there anything wrong with adding a new field to your "standard dimension" - something akin to LanguageTypeKey and creating a LanguageType table? This way you can just put any feed you receive (regardless of language) into the same table and use SSIS to populate the LanguageTypeKey field based on which feed it is processing. If a further language is introduced down the line you simply add it as a new record in your LanguageType table and modify your SSIS to map the LanguageTypeKey. I assume that the only time you really need to use "Day" vs. "Jour" etc. is as a label in reporting.
Again use the LanguageTypeKey to programatically adjust the labels accordingly - maybe even have another table that stores the labels for every language absed in the same key?
I tend to oversimplify things, so at the least hopefully this shakes an idea loose 🙂
Good luck!