mrc logo mrc logo
  • m-Power m-Power
    What is m-Power?
    Overview Demos Build Process Case Studies Specs Pricing Trial
    m-Power Resources
    Overview How-To Videos Webinars & Podcasts White Papers Fact Sheets
  • Solutions Solutions
    What does m-Power build?
    Overview Database Front-Ends Reporting CRM Systems Business Intelligence Dashboards Inventory Management Mobile Apps ERP Enhancements Modernization Spreadsheets to the web MS Access to the web B2B/Web Portals Scheduling Embedded Analytics Web Forms Workflow Data Exploration Budgeting & Forecasting APIs and Web Services Db2 Web Query Alternative
    Solutions by Industry
    Overview Manufacturing Government Foodservice Software Vendors Logistics & Supply Chain Software Consultants Healthcare
  • Services Services
    Development Services Training Mentoring
  • About About
    Overview Partners Press Releases Careers Events Contact Blog
  • Support Support
    Support Home FAQ Documentation Customer Portal Enhancements Updates Roadmap Techblog
Try m-Power

m-Power Manual

Browse:

  • Home
  • Summaries
  • ETL Feature
Back to Manual

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

Search


Browse By Category

Build Process (13)
Starting with m-Power (8)
Retrievals (10)
Reports (15)
Summaries (4)
Maintainers (17)
Graphs (8)
m-Power Data Explorer (4)
General (24)
Calculations (5)
Utilities (9)
m-Power Administration (23)
Security (11)
Freemarker (6)
m-Painter (29)
Form Validation (5)
External Objects & UDFs (12)
Deprecated Documentation (23)
Bootstrap Templates (7)

Popular Tags

Form Validation Email Retrievals Security Performance Graph Properties RPG Administration Calculations Record Selections Freemarker Database Getting Started Maintainer External Objects Build Process m-Painter Prompt Screens Excel Video SQL Report Bar Graphs Tomcat Graphing Graphs Reports Popular Dropdowns Parameters DB2 Summaries Admin Compiling mrc-Productivity Series Bootstrap Templates Maintainers Java App Properties Retrieval Application Properties Data Dictionary Advanced Production Dates

See all tags »

michaels, ross & cole, ltd. (mrc)

Privacy Policy Cookie Policy Cookie Settings Notice at Collection Do Not Sell or Share My Personal Information

mrc (US)

2001 Midwest Road
Suite 310
Oak Brook, IL 60523
630-916-0662

mrc (UK)

Mortlake Business Centre
20 Mortlake High Street
London, SW14 8JN
+44-20-335-59566


© 2024 mrc. All rights reserved.