In case you have multiple source and/or target systems with the same table structures, you do not have to implement your dataflow multiple times, there is a feature called datastore profiles. Another usecase is porting flows between different databases, like each of our RapidMart dataflows should work with Oracle, SQL Server and DB2 as target database. And we definitely not want to build all dataflows n-times.
The way this thing works is that upon job execution, you decide what the actual settings for the datastore should be including the table owners. But actually it is a two step approach.
First, you edit the datastore in question, click on advanced to get to the configurations button at the bottom.
Then you create a new configuration by clicking on the left most button in the dialog's toolbar and provide the connection information for the new database.
The values you entered initially for the datastore itself are actually the first configuration and it was marked as default. By adding more configurations you can specify the other databases connection information. So you might have one configuration called "US" where you connect to your sales server for US, another configuration called "Europe" where you connect to a different server located in Europe. All databases do have the same tables and columns, just different data, otherwise the dataflows will not work there obviously.
The default configuration is used by the Designer application, e.g. when previewing data it connects to this database only.
The biggest danger with datastore configurations would be to mix those, e.g. execute a job with reading the US data but loading the Europe Data Warehouse or vice versa. In order to minimize the risk, we have another layer on top, the system profiles. In the menu Tool -> System Profile you can specify what configurations belong to each other. For each system profile, you specify one and only one datastore profile to use. In our example, we would have two system profiles, one for US reading using the US configuration of the source datastore and loading the US target datastore configuration. And a second for Europe. This way it is impossible to accidentally load US data into the Europe Data Warehouse as long as no such combination is defined as system profile.
Impossible, because when executing a job all you can choose from are the system profiles.
Obviously, there is no requirement to have multiple profiles for all datastores, e.g. you might have two configurations for the source but all data will be going into the same target, our enterprise data warehouse, the example we have created here. For the other datastores leave the field empty, choose from the drop down list the "default configuration" or the one configuration that is there only, it will be all the same as there is just one.
Lifeng LeeFung Wu
This works well if the datastore is an SAP R3 datastore. But what if the datastore is MS SQL Server database? For each configuration the table owner/schema is different, for example
In this case, the tables under the datastore don't seem to transfer over when you change the "System configuration" at runtime?
You shoud use Aliases in the Configurations for DataStore.