ETL Feature


It is well-known that m-Power can run over any database that supports a JDBC connection. In fact, many customers choose to license m-Power to multiple databases in order to create web applications over each database that their business uses. For setups like these, a common question is how to create applications that pull data from two different databases.
While in the past, there have been various approaches such as using imports to combine two or more screens, a new m-Power feature allows you to pull data first into one database where you can then build applications. This feature, commonly called ETL for "extract, transform, load", will allow you to build a summary that grabs data from one database, and inserts it into another. This can then be run manually or scheduled to automatically run at the desired interval.
To begin using this feature, first build a summary over the necessary data. Note that while you still must set a subtotal level to yes, you can do this over a unique field or fields, to ensure that you end with a duplicate of data instead of a summary.
Once your summary is configured, the first thing we must do is configure which database and library to output the data to. To do this, navigate to the Application Settings screen. On this screen, we can scroll to the bottom in order to see the two properties we need, Summary Target Datasource and Summary Remote Target Schema. The Datasource is the datasource id from the spring context file. If you are unsure what this is, you can navigate to the Admin Menu, Edit Dictionary Files, Datasource Configuration. Find the necessary datasource id on the left, in this case for our oracle database, copy it, and then paste it back on the Application Settings screen.
Once done, fill in the name of the schema you would like to use as well. This schema is the schema you want to output the file to on the new datasource. In this case, I have set up a schema on oracle called ORACLETEST. You can also choose the table name and any other settings above. Once finished click the Save and Proceed to Data Selection button.
Now that we have configured which datasource to export our data to, we need to compile this application by clicking the Build and Customize button. If this application has not been previously built, the application will automatically compile, otherwise click the Rebuild Application button and choose to overwrite both the HTML and XML. Note that compiling this summary will build the associated summary table on both the current datasource as well as the secondary datasource specified earlier.
Once it has finished building, open the properties by clicking on the Edit Properties button. In this window, the populate_remote_datasource option allows you to control whether or not this application will run like a normal summary, or will output the data to the database specified earlier. Let's set this to Enabled and run the application. Running this summary will now show us that the summary processes records as normal, however instead of inserting them into the current database will output them to our secondary datasource specified on the Application Settings screen.
You can now schedule this summary to run using the scheduled task utility to automate this process, if desired. Additionally, the data table exists on the secondary database and can be used in your applications just like any other table.

Created: July 9, 2014 | Modified: July 15, 2014