|| DATA CONSOLIDATION
Spreadsheets exist at the heart of every company and
every business department. Spreadsheets are used to
help the company make fundamental decisions about it’s
future. They are used to monitor current performance
and predict future trends. At a departmental level,
spreadsheets are used to reconcile figures from business
applications, produce periodic budget figures and generally
track and maintain the current status of business processes.
The information in the spreadsheet needs to be accurate
and up to date. However the maintenance of spreadsheets
is largely manual, but “routine” in that
the information used to update the spreadsheets is typically
the latest version of the same figure from the same
input source every time the spreadsheet is updated.
The person whose job it is to maintain the spreadsheet
will usually take an amount of time each day/month manually
repeating the same process. Problems will occur when
this person is not at work and someone else steps in
(or the process is forgotten).
PTC’s Data Consolidation module is an optional
component of the PTC Scheduler which allows spreadsheets
to be maintained dynamically and automatically. PTC
enables you to identify each of the input sources required
to maintain the spreadsheet and describe the method
to extract the required data from each individual input
PTC can populate Excel spreadsheets from virtually
any input source, including:
Reports (txt, pdf, crystal)
Files (xml, csv, tsv)
Any database, via a SQL command
Web query (internet, intranet, web process)
Generated spreadsheets can include all features
of Excel, including “objects” such
as charts, pictures, shapes etc.
|PTC uses an intuitive, interactive graphical user
interface to help you to configure the data consolidation
task. Once the process is complete, the resulting
“consolidation document” can be included
in the overnight batch schedules, or run periodically
and automatically from PTC’s inbuilt diary,
with the resulting spreadsheet optionally emailed
to the appropriate user on completion
PTC allows you to describe the format for the output spreadsheet from
within the module. Alternatively, you can copy
and paste the format from an existing spreadsheet.
By building the template within PTC, it is not
required to install a copy of Microsoft Office
on the server that runs the PTC component.
Cell formats, excel formulae and spreadsheet
formatting are all maintained within the PTC template.
|For each individual input source, you need to
tell PTC how to identify the data that you wish
to place in the output spreadsheet. For reports,
such as pdf, crystal and text, PTC provide a parser
to identify the correct data using intuitive string
recognition techniques. For database extracts, PTC
enables multiple data items to be linked directly
for rapid extraction and updating. For web extracts,
PTC uses an inbuilt macro recorder to enable you
to build a script to navigate to the correct web
page and extract the desired data. The recorded
script can be replayed through PTC whenever required.
Once the input sources have been described,
the required data elements can be placed into
the output spreadsheet using simple “drag
and drop” techniques. Advanced options allow
multiple values to be placed individually in a
column or row, or summed, counted, averaged or
even linked to other information within the spreadsheet.
The completed Data Consolidation can then be
executed as frequently as is required by the business,
or included in the overnight schedules.
here for more details