Ali Kayan
asked on
MS SQL tables, .net application and ETL connundrum
Hi all
We have a database which has a number of tables that all utilise different identification methods for currencies. Some use ISO numbers other tables use ISO Codes. All the data and (and effectively) structure of these tables is from outside third party sources, so there is no influence on how and the format of the data produced.
We have a table that is a Currency table that contains both ISO number and ISO alpha codes, and a CurrencyId (just a simple integer: 1, 2, 3 etc).
We have an ETL that imports the data, and in some tables, the data is converted at import time from one currency identifier (eg ISO number) to the other currency identifier (eg ISO code). This I think is a result of different, incongruent, teams working on the software but that is a topic in its own right lol.
We have a .net software application that references these tables directly, and obviously there's no consistency in which ISO code (number or alpha) that the software is referencing.
It is also proving difficult to maintain code by referencing sometimes the ISO code and other times the ISO Number for the currency.
So we are considering one of the following resolutions:
1) Import all data and map at that point a new derived field (we are using SSIS) that is the CurrencyId for that currency from the "Currency" table based on the ISO code number or alpha (and still import the currency identifier that the original data does). Then change the references in the software to utilise the CurrencyId instead of the currency ISO code or number.
OR
2) build views for all the relevant tables and join them to the currency table on either ISO number or ISO code (whichever relevant). Then change all the references in the software to reference the view instead and utilise the CurrencyId instead of the currency ISO code or number.
We would like some advice and input as to which would be better approach for a performance perspective. Data storage perspective I imagine will be a small impact since I think the CurrencyId can be contained within a tinyint datatype field, but still very interested to know what you think in this area too.
We have a database which has a number of tables that all utilise different identification methods for currencies. Some use ISO numbers other tables use ISO Codes. All the data and (and effectively) structure of these tables is from outside third party sources, so there is no influence on how and the format of the data produced.
We have a table that is a Currency table that contains both ISO number and ISO alpha codes, and a CurrencyId (just a simple integer: 1, 2, 3 etc).
We have an ETL that imports the data, and in some tables, the data is converted at import time from one currency identifier (eg ISO number) to the other currency identifier (eg ISO code). This I think is a result of different, incongruent, teams working on the software but that is a topic in its own right lol.
We have a .net software application that references these tables directly, and obviously there's no consistency in which ISO code (number or alpha) that the software is referencing.
It is also proving difficult to maintain code by referencing sometimes the ISO code and other times the ISO Number for the currency.
So we are considering one of the following resolutions:
1) Import all data and map at that point a new derived field (we are using SSIS) that is the CurrencyId for that currency from the "Currency" table based on the ISO code number or alpha (and still import the currency identifier that the original data does). Then change the references in the software to utilise the CurrencyId instead of the currency ISO code or number.
OR
2) build views for all the relevant tables and join them to the currency table on either ISO number or ISO code (whichever relevant). Then change all the references in the software to reference the view instead and utilise the CurrencyId instead of the currency ISO code or number.
We would like some advice and input as to which would be better approach for a performance perspective. Data storage perspective I imagine will be a small impact since I think the CurrencyId can be contained within a tinyint datatype field, but still very interested to know what you think in this area too.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would second Ste5an's solution.
Only thing is that rather than doing this matching and determination of the ISO Number from the ISO Code in the SSIS, my vote would be to extract in a common standard by using a query in the OLE DB source (you can probably join the source table to the currency identifier tables to return the common field - ISO Number or ISO Code as necessary).