Scenario 3: Produce Daily Excel Reports

In this scenario, we're going to use DMT to produce Excel reports from a database.

Although DMT doesn't yet directly generate data files in Excel format (functionality planned for version 2), you can easily generate CSV files that can be read by Excel.

Let's say you have a trading system from which you want to produce daily Excel reports for your 3 top accounts. The data is stored in several databases located around the world.

Account 1: report trades from Paris (SQL Server)
Account 2: report trades from New York (Oracle) and London (DB2)
Account 3: report trades from London (DB2)

Note: For our example, we chose Oracle, SQL Server and DB2 databases and CSV files, but this would work the exact same way with any other database or any other supported file format.

You can easily implement this scenario with DMT:

There are several ways this scenario could be implemented with DMT. Here we've chosen to use one group for all the exports, and to use one data file for Account 2 (containing both New York and London trades).

Step 1: Create the Export Group and Jobs (in the Export Tab)

Create an Export Group to contain all the Export Jobs
- Name: Top 3 Account Reports
- Group Type: Schedule Group
- Scheduling Frequency: Daily
In the group create a Job to export Paris trades for Account 1
- Name: Trades Account 1 (Paris)
- Database Connection: Connection to the Paris SQL Server database
- Data File: Account 1.csv
- SQL Statement: Enter the SQL statement to export Paris trades for Account 1
- e.g.
  SELECT TRADE_ID, STOCK_ID, QTY, AMOUNT
  FROM TRADES_PARIS
  WHERE ACCOUNT_ID = 1
In the group create a Job to export New York trades for Account 2
- Name: Trades Account 2 (New York)
- Database Connection: Connection to the New York Oracle database
- Data File: Account 2.csv
- SQL Statement: Enter the SQL statement to export New York trades for Account 2
- e.g.
  SELECT TRADE_ID, STOCK_ID, QTY, AMOUNT
  FROM TRADES_NY
  WHERE ACCOUNT_ID = 2
In the group create a Job to export London trades for Account 2
- Name: Trades Account 2 (London)
- Database Connection: Connection to the London DB2 database
- Data File: Account 2.csv (note this is the same data file as the previous job)
- We want to store Account 2 trades for both New York and London in the same data file. To achieve this you need to select the "Append to Existing File" option in the "File Parameters" form.
- SQL Statement: Enter the SQL statement to export LONDON trades for Account 2
- e.g.
  SELECT TRADE_ID, STOCK_ID, QTY, AMOUNT
  FROM TRADES_LONDON
  WHERE ACCOUNT_ID = 2
In the group create a Job to export London trades for Account 3
- Name: Trades Account 3 (London)
- Database Connection: Connection to the London DB2 database
- Data File: Account 3.csv
- SQL Statement: Enter the SQL statement to export LONDON trades for Account 3
- e.g.
  SELECT TRADE_ID, STOCK_ID, QTY, AMOUNT
  FROM TRADES_LONDON
  WHERE ACCOUNT_ID = 3

Note: Scheduling parameters are not required for the jobs because they belong to a Schedule Group (i.e. they will run automatically when the group is run).

Once the export group and jobs are created, you can validate them to make sure they are error free and ready to run (right click on the Group then "Validate Group").

Step 2: Start the Scheduler (in the Scheduling console Tab)

Once the Export and Load Groups/Jobs are created, you can let the scheduler run them automatically. Just go to the scheduler Tab and click the Start button. When the scheduler is started, the export group (i.e. its 4 jobs) will run every day at the specified time.

Each time the export group runs, the following is happening:

The job "Trades Account 1 (Paris)" runs and produces the Account 1.csv report file
The job "Trades Account 2 (New York)" runs and produces the Account 2.csv report file
The job "Trades Account 2 (London)" runs and appends data into the Account 2.csv report file
The job "Trades Account 3 (London)" runs and produces the Account 3.csv report file

Note: It's always a good idea to run groups or jobs manually at least once before scheduling them.

How can DMT fit
your requirements?

Just ask us!

Usage Scenarios