Hi,
Some projects I work on, make use of Industry Models, like the IIW Insurance Information Warehouse by IBM. These are standard warehouses, with accompanying solutions for Solvency reporting etc.
Loading these Industry Models sometimes takes hundreds or thousands of mappings, transporting and transforming the data through the different layers (stages) and ultimately into the Warehouse.
Some of my colleagues (working on different ETL tools like Informatica Datastage) have come with a solution facilitating this process by Template Based ETL Design.
The idea is that a program can be developed that reads a template (XML, XLS or other format), and processes the data into mapping files that can be read by the ETL tool. A first successful approach was by using Python (OO programming language), reading an XLS file containing source columns, target columns and simple transformational info. The Python code uses a custom built library where the ETL functions of Informatica are replicated in Python code. The library functions can then be used in the Python program.
This works for Informatica Datastage, because Datastage can read/import the XML mapping files created by Python. Downside is that the XML is version dependent and needs to be changed whenever there is a change in the Datastage version.
But it works.
I would like to be able to do a similar exercise for SAS, but am not sure if is it feasible, because SAS is so very metadata driven and has limited import functionality.
Maybe SAS Macros are a possibility, but I have 0% experience in SAS Macros.
My question to you on this forum is, if Template Based ETL Design is at all possible in SAS and if so, what is the best approach?
Thanks in advance,
Frank