Automating the ETL Process via Sparx Enterprise Architect

[Tarzan]

Hello everyone,

I’m looking for advice and best practices for automating the ETL process within our Enterprise Data Warehouse (EDWH), which is build in SAS. Our team currently uses Sparx Enterprise Architect for modeling and design purposes, but we want to explore whether we can leverage this platform to automate the ETL process, or at least support the design and orchestration of it.

The challenge:
We’re looking for ways to automate the extraction, transformation, and loading process using EA, or use EA as a supporting tool to streamline the process.
We also want to know if there are any integrations between SAS and Sparx Enterprise Architect that can help us with this automation.

Specific questions:

• Can Sparx Enterprise Architect itself execute the ETL process? Or is it mainly focused on modeling and documenting the process, requiring other tools for the actual execution?
• How can we integrate Sparx Enterprise Architect with SAS to support data extraction, transformation, and loading processes? Are there any specific add-ins or APIs that make this integration possible?
• Can we design workflows and process models for the ETL process in EA and then link them to the ETL tools within SAS?
• Is there a possibility to automate the ETL process via scripting in EA, such as extracting data from a model or preparing transformations?
• What tools or techniques do you recommend to optimize the collaboration between EA and SAS for ETL automation?

Thanks in advance for your help!

[Geert Bellekens]

EA is a modelling tool client. Not suited for ETL execution.

I don't know any existing integrations with SAS.

We use the EA Mapping tool I built to document the mappings between logical and physical models.

EA is highly customizable, so you can definitely use scripts or queries to get the mapping data out in a format you can use for your development.

Geert

[Modesto Vega]

You can use Sparx EA to model your ETL process or any other data integration process but, AFIK, you cannot use it to forward engineer the process.