I am currently working on a financial Risk data warehouse. For my collateral dimension, I am souring the data from one source system. However, after further research by the business analyst, we found a legacy application that also holds collateral information which the bank also needs in the data warehouse. Bar a few common attributes that both source systems share, the legacy application contains a lot more attributes than what is defined already in my current collateral dimension. What is therefore the best way to onboard this new information in the Warehouse? I was thinking of extending the current collateral dimension but then would I need to do this every time I find a new source, which is very likely given the size of the bank. Alternatively is it better to create a new dimension called dimCollateralAdditionalInfo and add the extra attributes there?
As we always say that a DWH model is evolutive in the time since new business requirements can appear over time. The most important thing is to check if the new attributes are worth to be added and if they present an analytic axis.
You can store all the information in the dimCollateral and you need to think to manage this dimension properly in terms of optimization (indexes, data types...)
Or you can create an extended dimension dimCollateralExtension containing the additional info and it will have a one to one relationship with the master dimension dimCollateral