Dynamic Data Source & Target

[Northern_Monkey]

I am currently trying to dynamically pass through Data Source / Delivery database connections through DM.

The current set up is that source system developers of the OLTP system create and manipulate data in their own database environments. I need to start pulling and cleansing data from their environments to prototype ETL jobs.

At the moment, I have multiple Connections setup for each environment and I either change the Connection Details to point my central database when the OLTP developers release to a central store.
Anyone know how to pass parameter through the system that would allow to handle this?

[MFGF]

Hi,

Sorry - I'm not quite clear on what you're trying to do. You could perhaps use variables within a single source and a single target to switch the physical, connection for each, but how (for example) would you take account of different source databases having different structures?

Cheers!

MF.

[Northern_Monkey]

My apologies for not being clearer...

Let's say I wanted to dynamically control the schema that data is read from in a data source. I imagine I could try and add a variable and pass it through. Never done it but I understand what I may try in order to get it working....

However take a scenario where we have 3 identical source system databases.

1)   DEV – User changes to tables etc are merged daily
2)   DEV – User A – Working on account
3)   DEV – USER B – Working on product

In an ideal state, I would wait for the developers to make their changes to the OLTP system and just create my ETL jobs from the environment labelled '1' above. However in the DWH world, as we all know, there is no such no thing as an ideal world!

Therefore I need to pull changes from all 3 environments for prototyping of extracting data in to staging in the warehouse.

Is there a way to dynamically change the database connection being used? A variable that allows the entry for 'Connections'.

Again, not the best explanation but hope it may help dig further for an answer...

[eknight]

In our system we dynamically determine the schema via a function and we save this result in a job variable. Which we then reference in the build sqls. So for example whenever we reference a table name table_name in our build SQL we do it like this:

      {$schema_name}.table_name

The lets us remain schema independant and in our case you can optionally pass the target or source schema via the commandline for different test cases. 

[MFGF]

Very neat solution!! Thanks for sharing :)