Monarch 14.2 Learning Guide
User Manual: Pdf
Open the PDF directly: View PDF .
Page Count: 435 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- [1] Using the Monarch Documents
- [2] Introducing Monarch
- [3] Introducing Data Prep Studio
- [4] Data Prep Studio Lessons
- Before You Begin
- Previewing and Adding Data to a Data Prep Session
- Saving a Workspace
- Using the Datawatch Data Connectors
- Adding Data from DB2, Informix, MySQL, Oracle, PostgreSQL, SQL Server, Hadoop Hive, Cloudera Impala, Amazon Redshift, and SybaseIQ
- Connecting to IBM Cloudant
- Connecting to OData
- Connecting to Monarch Server – Content
- Connecting to Business Objects Universe
- Connecting to Salesforce
- Connecting to MongoDB
- Connecting to Google Analytics
- Navigating Tables
- Loading a Workspace
- Joining Data in a Data Prep Session
- Appending Data in the Preview Data Window
- Appending Data in the Prep Data Window
- Transforming Data
- Working with Load Plan Visualization
- Working with Calculated Fields
- Filtering Data
- Preparing Data
- Tracking Changes
- Exporting Data
- Switching from Data Prep Studio to Monarch
- [5] Monarch Table Extractor Lessons
- [6] Monarch Lessons
- [7] Working in Report View
- [8] Extracting Data from a Report
- [9] Special Data Extraction Techniques
- [10] Working in Table View
- [11] Working with Sorts
- [12] Working with Filters
- [13] Working with Calculated Fields
- [14] Working with Multiple Instances of a Report
- [15] Extracting Multiple-Line Fields
- [16] Summaries
- [17] Advanced Summary Capabilities
- [18] Exporting Operations
- [19] Importing Data from HTML, Web Files, and External Databases
- [20] Creating External Lookups
- [21] Using Digital Signatures
- [22] Using Pivot Tables
- [23] Working with Asian (CJK) Character Sets
- [24] Using Monarch Utility
- Conclusion
[Type text]
Monarch Learning Guide
Version 14.2
14.2.0/2017.05.31
Datawatch Corporation makes no representation or warranties with respect to the contents of this manual
or the associated software and especially disclaims any implied warranties of merchantability or fitness for
any particular purpose. Further, Datawatch Corporation reserves the right to revise this publication and
make changes from time to time to its contents without obligation to notify anyone of such revisions or
changes.
Datawatch Monarch software is offered and is to be used in accordance with a SOFTWARE LICENSE AND
MAINTENANCE AGREEMENT. This agreement stipulates that this software be used only in the computer
system designated in that agreement. The agreement further stipulates that the customer shall not copy
or alter, or permit others to copy or alter, the software or related materials in whole or in part, in any
media for any purpose, except to make an archive (back-up) copy or to make a copy as an essential step
in the use of the software with the customer's computer.
Datawatch Corporation hereby grants the buyer the right to reprint this documentation for internal uses
only. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any
form or by any means, electronic, mechanical, photocopying, recording, or otherwise, for any other
purposes, without the prior written permission of Datawatch Corporation.
Datawatch Monarch Version 14.2 Learning Guide
Copyright © 2017 by Datawatch Corporation
All rights reserved. Printed in the U.S.A.
Unpublished - Rights reserved under the copyright law of the United States.
Datawatch Monarch is a trademark of Datawatch Corporation. Other products mentioned herein may be
trademarks or registered trademarks of their respective owners in the United States or other countries.
For U.S. Government End Users, the software is a “Commercial Item(s),” as that term is defined
at 48 C.F.R. Section 2.101, consisting of “Commercial Computer Software” and “Commercial
Computer Software Documentation,” as such terms are used in 48 C.F.R. Section 12.212 or 48
C.F.R. Section 227.7202, as applicable. Consistent with 48 C.F.R. Section 12.212 or 48 C.F.R.
Sections 227.7202-1 through 227.7202-4, as applicable, the Commercial Computer Software and
Commercial Computer Software Documentation are being licensed to U.S. Government end users
(a) only as Commercial Items and (b) with only those rights as are granted to all other end users
pursuant to the Datawatch Software License and Maintenance Agreement.
DATAWATCH CORPORATION
CORPORATE HEADQUARTERS
4 Crosby Drive
Bedford, MA 01730, USA
Tel.: +1 800.445.3311 / +1 978.441.2200
UNITED KINGDOM
DATAWATCH INTERNATIONAL LTD.
Siena Court, Broadway
Maidenhead, Berkshire SL6 1NJ
Tel.: + 44 845.362.3270
Floor 42 Level 39
One Canada Square
Canary Wharf,
London, E14 5AB
Tel.: + 44 845.362.3270
NEW YORK
415 Madison Avenue, Suite 1421
New York, NY 10017
Tel.: +1 800.445.3311
GERMANY
DATAWATCH GmbH
Promenadenstraße 1
41460 Neuss
Germany
Tel.: + 49 89.208.039.528
SWEDEN
DATAWATCH AB
Eriksbergsgatan 10
Stockholm, Sweden SE-114 30
Tel.: + 46 853.480.483
Table of Contents
[1] Using the Monarch Documents ............................................................................. 1
Monarch Learning Guide ................................................................................................ 1
Monarch Help File ......................................................................................................... 1
Monarch Training .......................................................................................................... 1
Monarch Model Building Service ...................................................................................... 2
[2] Introducing Monarch ........................................................................................... 3
What is the Monarch Application? .................................................................................... 3
New Features in This Version .......................................................................................... 3
The Monarch Suite of Applications ................................................................................... 4
The Monarch Workflow .................................................................................................. 5
The Monarch Interfaces ................................................................................................. 7
Report View ...................................................................................................... 7
Table View ........................................................................................................ 8
Summary View .................................................................................................. 9
Export View .....................................................................................................10
[3] Introducing Data Prep Studio ............................................................................ 11
What is the Data Prep Studio Application? .......................................................................11
New Features in This Version .........................................................................................12
Launching Data Prep Studio ..........................................................................................12
The Data Prep Studio Workflow .....................................................................................13
The Data Prep Studio Interface ......................................................................................13
The Start Page .................................................................................................14
The Settings Dialogs .........................................................................................15
The Application Menu ........................................................................................16
The Preview Data Window ..................................................................................17
The Transform Dialog ........................................................................................18
The Prep Data Window ......................................................................................19
The Combine Data Stage ...................................................................................20
The Report Discovery Window ............................................................................20
The Export Data Dialog ......................................................................................21
Data Prep Studio Hotkeys .............................................................................................21
Data Prep Studio Table Icons ........................................................................................22
[4] Data Prep Studio Lessons .................................................................................. 23
Before You Begin .........................................................................................................24
Previewing and Adding Data to a Data Prep Session .........................................................25
Adding Data from a Database File .......................................................................25
Adding Data from a PDF Report ......................................................................... 32
Adding Data from a Website .............................................................................. 38
Adding Data from XML and JSON Files ................................................................ 40
Adding Data from Monarch Files ........................................................................ 43
Adding Data from Excel Files ............................................................................. 44
Using the Recent Files Panel to Load Data ........................................................... 45
Displaying Recent Data Sources in the Select Data Source to Open Dialog .............. 46
Saving a Workspace .................................................................................................... 48
Using the Datawatch Data Connectors ........................................................................... 49
Adding Data from DB2, Informix, MySQL, Oracle, PostgreSQL, SQL Server, Hadoop
Hive, Cloudera Impala, Amazon Redshift, and SybaseIQ ....................................... 50
Connecting to IBM Cloudant .............................................................................. 53
Connecting to OData ........................................................................................ 54
Connecting to Monarch Server – Content ............................................................ 56
Connecting to Business Objects Universe ............................................................ 58
Connecting to Salesforce .................................................................................. 61
Connecting to MongoDB .................................................................................... 61
Connecting to Google Analytics .......................................................................... 62
Navigating Tables ....................................................................................................... 64
Navigation Controls .......................................................................................... 64
Searching for Data ........................................................................................... 64
Working with Rows .......................................................................................... 65
Showing Nulls and Whitespace Characters .......................................................... 65
Docking/Undocking Tables ................................................................................ 65
Loading a Workspace ................................................................................................... 66
Joining Data in a Data Prep Session .............................................................................. 67
Analyzing Joins ................................................................................................ 70
Creating a New Join ......................................................................................... 71
Using Fuzzy Joins ............................................................................................. 71
Appending Data in the Preview Data Window .................................................................. 72
Appending Data in the Prep Data Window ...................................................................... 74
Appending Data via the Context Menu ................................................................ 74
Appending Data via the Append Helper ............................................................... 75
Transforming Data ...................................................................................................... 78
Extracting Nulls and Blanks ............................................................................... 78
Pivoting Columns ............................................................................................. 78
Unpivoting Columns ......................................................................................... 80
Grouping Data ................................................................................................. 82
Removing/Showing Duplicates ........................................................................... 83
Working with Load Plan Visualization ............................................................................. 85
Working with Calculated Fields...................................................................................... 86
Filtering Data ............................................................................................................. 88
Preparing Data ........................................................................................................... 89
Tracking Changes ....................................................................................................... 96
Applying a Change List to a Different Table ......................................................... 97
Exporting a Change History List ......................................................................... 99
Importing a Change History List .........................................................................99
Modifying Items in a Change List ...................................................................... 101
Creating Custom Change Lists .......................................................................... 102
Exporting Data .......................................................................................................... 103
Exporting to CSV, Microsoft Excel, Microsoft Access, Datawatch Designer, Qlik, and
Tableau Files .................................................................................................. 104
Exporting to IBM Watson Analytics .................................................................... 105
Exporting to IBM Cognos Analytics .................................................................... 108
Exporting Data to Microsoft Power BI ................................................................ 110
Exporting Data to Monarch Swarm .................................................................... 112
Exporting to Tableau Server ............................................................................. 113
Switching from Data Prep Studio to Monarch ................................................................. 114
[5] Monarch Table Extractor Lessons ..................................................................... 116
Launching Monarch Table Extractor .............................................................................. 117
Opening a Document .................................................................................................. 117
Defining Tables .......................................................................................................... 119
Auto-Defining a Page ...................................................................................... 119
Viewing and Modifying a Table .......................................................................... 120
Deleting a Table ............................................................................................. 123
Exporting Tables ........................................................................................................ 123
[6] Monarch Lessons ............................................................................................. 125
Installing Lesson Files ................................................................................................. 126
The Options Interface ................................................................................................. 127
[7] Working in Report View ................................................................................... 129
Opening Report Files .................................................................................................. 129
Opening a Single Report File ............................................................................ 129
Opening Multiple Instances of a Report .............................................................. 131
Getting to Know Report View ....................................................................................... 133
Navigating Through Reports ........................................................................................ 134
The Report Selector ........................................................................................ 135
Adding Greenbar ............................................................................................ 136
Changing Fonts and Font Sizes ......................................................................... 137
Moving to the Next Page of a Report ................................................................. 138
Moving through Several Reports ....................................................................... 140
Finding Information in a Report ........................................................................ 140
Using Bookmarks ....................................................................................................... 141
Copying Data from Reports ......................................................................................... 143
Printing from a Report ................................................................................................ 145
Closing Reports ......................................................................................................... 147
Using PDF and XPS Files ............................................................................................. 148
The Stretch Option .......................................................................................... 150
The Mono-spaced Option ................................................................................. 150
The Freeform Option ....................................................................................... 151
Customizing PDF/XPS Import Options ................................................................ 152
[8] Extracting Data from a Report ......................................................................... 154
Getting to Know Report Design View ............................................................................ 154
Extracting Data Using Templates ................................................................................. 158
Creating the Detail Template ....................................................................................... 161
Trapping the Detail Lines ................................................................................. 162
Highlighting Detail Fields ................................................................................. 164
Verifying Field Boundaries ................................................................................ 165
Creating Append Templates ......................................................................................... 167
Trapping the Ship Date Lines ............................................................................ 168
Trapping the Account Number and Contact Lines ................................................ 171
Creating a Page Header Template ................................................................................ 173
Trapping the Page Header ................................................................................ 174
Creating an Exclusion Template ................................................................................... 175
Setting Template Colors .............................................................................................. 177
Changing Default Colors .................................................................................. 177
Resetting Default Colors .................................................................................. 179
Naming Fields ............................................................................................................ 180
Using the Field Properties Panel ........................................................................ 180
Using the Table Design Interface ...................................................................... 182
Using the Field Definition Window ..................................................................... 184
Using the Report Index ............................................................................................... 185
Setting Up a Report Index ................................................................................ 185
Navigating within Report Index ......................................................................... 188
Working with Model Files ............................................................................................. 190
Saving Model Files to a Local Folder .................................................................. 190
Saving Model Files to a Datawatch Server Library ............................................... 192
Saving Model Files to Amazon S3 ...................................................................... 193
Loading Saved Model Files ............................................................................... 194
Working with Project Files ........................................................................................... 194
Project File Limitations .................................................................................... 195
Project Files and Datawatch Automator .............................................................. 196
[9] Special Data Extraction Techniques ................................................................. 197
Using the Address Block Feature .................................................................................. 198
Special Problems with Addresses ...................................................................... 198
Extracting an Address Block ............................................................................. 198
Defining Address Blocks ................................................................................... 200
Using the Auto-Define Feature in Report View ................................................................ 203
Using the Auto-Define Trap Feature .............................................................................. 204
Using the Floating Trap Type ....................................................................................... 208
Using the Floating Trap to Capture Lines ............................................................ 209
Using the Floating Trap to Define Fields ............................................................. 211
Using the Multi-Column Region Trapping Feature ........................................................... 213
Creating a Template to Trap Multi-Column Data.................................................. 218
Specifying Vertical Boundaries for the MCR ........................................................ 221
Using the Regular Expression Trap Type ....................................................................... 224
Using the Exclusion Trap Feature ................................................................................. 227
Using the Start and End Region Trap Features ............................................................... 229
[10] Working in Table View ................................................................................... 232
Getting to Know Table View......................................................................................... 232
Getting to Know Table Design View .............................................................................. 235
Navigating Through a Table ......................................................................................... 237
Finding Information in a Table .......................................................................... 237
Displaying the Source of a Record ..................................................................... 238
Changing Fonts and Font Sizes ......................................................................... 240
Formatting Fields ....................................................................................................... 241
Filling Empty Cells .......................................................................................... 241
Adjusting Field Widths ..................................................................................... 242
Ordering Fields ............................................................................................... 244
Hiding Fields .................................................................................................. 244
Assigning Field Types ................................................................................................. 245
Creating Headers and Footers...................................................................................... 246
Creating a Page Header ................................................................................... 246
Creating a Page Footer .................................................................................... 249
Printing Table Data .................................................................................................... 249
Adjusting Page Setup Options .......................................................................... 249
Printing Table Data ......................................................................................... 250
Copying Data to Other Applications .............................................................................. 251
Redacting Fields ........................................................................................................ 253
Redacting a Single Field ................................................................................... 253
Redacting an Entire Model ................................................................................ 254
[11] Working with Sorts ........................................................................................ 255
Creating a Sort Order Definition ................................................................................... 255
Sorting on Multiple Fields ............................................................................................ 257
Restoring the Original Table Order ............................................................................... 259
Duplicating Sorts ....................................................................................................... 259
[12] Working with Filters ...................................................................................... 260
Creating Value-Based Filters........................................................................................ 261
Creating Formula-Based Filters .................................................................................... 263
Creating Compound Filters .......................................................................................... 265
Using Functions in Filters ............................................................................................ 266
Restoring the Original Table Order ............................................................................... 267
Duplicating Filters ...................................................................................................... 267
[13] Working with Calculated Fields ...................................................................... 268
Creating a Formula Field ............................................................................................. 269
Creating a User-Edited Field ........................................................................................ 272
Creating User-Edited Fields .............................................................................. 272
Using User-Edited Fields .................................................................................. 273
Making Comparisons .................................................................................................. 274
Redaction Functions ................................................................................................... 277
Regex Functions ........................................................................................................ 277
Hiding and Deleting Calculated Fields ........................................................................... 278
Duplicating Calculated Fields ....................................................................................... 278
[14] Working with Multiple Instances of a Report ................................................. 279
Working in Table View ................................................................................................ 281
Sorting .......................................................................................................... 281
Identifying the Source of Each Record ............................................................... 282
The File() Function .......................................................................................... 283
The ID() Function ........................................................................................... 285
Creating a Summary to Analyze Data from Multiple Reports ............................................ 286
[15] Extracting Multiple-Line Fields ....................................................................... 288
Capturing a Multiple-Line Field ..................................................................................... 289
Displaying a Multiple-Line Field in Table View ................................................................ 298
Memo Fields .............................................................................................................. 298
Using Memo Fields ..................................................................................................... 300
Using Memo Fields in Calculated Field and Filter Expressions ................................ 300
Printing Memo Fields .................................................................................................. 302
[16] Summaries ..................................................................................................... 303
Creating a Summary .................................................................................................. 306
Adding Key Fields and Measures .................................................................................. 308
Selecting Filters to Apply in Summaries ........................................................................ 309
Suppressing Duplicate Values ...................................................................................... 310
Adding Subtotals and Blank Lines ................................................................................. 311
Adding Measure Calculations ....................................................................................... 312
Adding Item Fields ..................................................................................................... 314
Collapsing and Expanding a Summary .......................................................................... 315
Copying and Printing Summaries ................................................................................. 317
Duplicating Summaries ............................................................................................... 317
[17] Advanced Summary Capabilities .................................................................... 318
Summary Displays ..................................................................................................... 318
Displaying Key Field Values Across .................................................................... 318
Displaying Null Values ..................................................................................... 319
Freezing Panes ............................................................................................... 320
Adjusting Column Widths ................................................................................. 321
Viewing Other Measures .................................................................................. 321
Displaying Multiple Measures in Across Key Summaries ....................................... 322
Sorting a Summary .................................................................................................... 324
Sorting by Measure Values .......................................................................................... 324
Restoring the Key Field Direction ................................................................................. 326
Top n Analysis ........................................................................................................... 327
Specifying Key Field Values ......................................................................................... 328
Sorting by Key Field Values ......................................................................................... 330
Upper Limit Values ..................................................................................................... 330
Summary Limit Values ................................................................................................ 331
[18] Exporting Operations ..................................................................................... 333
Exporting Reports ...................................................................................................... 333
Exporting Tables ........................................................................................................ 338
Exporting Summaries ................................................................................................. 340
Exporting a Single Summary ............................................................................ 341
Adding a Summary to an Export File ................................................................. 342
Exporting Data to Datawatch Designer ......................................................................... 345
What is a Designer Workbook? ......................................................................... 345
Using Table Data in Datawatch Designer ............................................................ 346
Exporting Redaction Alias Maps ................................................................................... 346
Exporting to Tableau Server ........................................................................................ 348
Duplicating Exports .................................................................................................... 349
[19] Importing Data from HTML, Web Files, and External Databases .................... 350
Importing Data from an External Database ................................................................... 351
Working with the Datawatch ODBC Drivers ................................................................... 357
Setting Up the Datawatch ODBC Drivers ............................................................ 358
Connecting to a Database Using the ODBC Drivers .............................................. 361
Notes on The Salesforce Database Connection .................................................... 362
Working with Database Data ....................................................................................... 362
Setting Field Properties .................................................................................... 363
Setting Delimited Text Properties ...................................................................... 364
Storing Import Parameters in Project Files ......................................................... 365
Opening the Project File ................................................................................... 365
Importing Data from an HTML File ............................................................................... 366
Importing Data from a Web File ................................................................................... 370
[20] Creating External Lookups ............................................................................. 371
What is an External Lookup? ....................................................................................... 371
Creating an External Lookup from a Report ................................................................... 373
Storing Lookup Parameters in a Model File .................................................................... 378
Creating a Lookup from Two Different Reports ............................................................... 378
Using a Report to Create a Lookup File .............................................................. 379
Linking to the Lookup File ................................................................................ 380
Creating a Lookup from Two External Databases ............................................................ 382
Importing Database Data ................................................................................. 383
Linking Database Data ..................................................................................... 384
[21] Using Digital Signatures ................................................................................. 386
What are Digital Signatures? ....................................................................................... 386
Adding Digital Signatures to an Excel File ...................................................................... 387
Viewing Digital Signatures ........................................................................................... 389
[22] Using Pivot Tables .......................................................................................... 391
Adding Pivot Tables to an Excel File .............................................................................. 392
Viewing a Pivot Table ................................................................................................. 394
Working with Pivot Tables ........................................................................................... 395
[23] Working with Asian (CJK) Character Sets ...................................................... 397
Setting Fullwidth Character Mode ................................................................................. 398
Setting Fallback Asian Fonts ........................................................................................ 399
Setting the Date/Time Format ..................................................................................... 399
Setting the Ignore Character Width Option .................................................................... 400
[24] Using Monarch Utility ..................................................................................... 402
Copying Models or Projects ......................................................................................... 402
Converting Text/XML Models or Projects to the Binary Format ......................................... 405
Managing External References in Binary Models or Projects ............................................. 407
Managing Authors and Descriptions in Binary Models or Projects ...................................... 410
Purging Audit Trails in Binary Models or Projects ............................................................ 415
Preparing Files for Monarch ......................................................................................... 417
Conclusion ............................................................................................................. 422
Monarch 14.2 Learning Guide 1
[1] Using the Monarch
Documents
The Monarch documentation consists of a Learning Guide and a Help file.
Monarch Learning Guide
The Monarch Learning Guide contains a short introduction to Monarch and Data Prep Studio,
collectively called Monarch Complete, along with a series of lessons designed to quickly
acquaint you with the program. The lessons will first introduce you to Data Prep Studio and
continue with each of the Monarch views: Report, Table, Summary, and Export. Monarch Table
Extractor, a new component available in Monarch version 14.2, is also discussed.
Each of the chapters describes a major area of Monarch. First-time users will be well rewarded
for the time they spend working through these sections. Advanced topics should be tackled
after you become familiar with the basic concepts of Monarch, as these topics will leverage
what you learned in previous sections. Each lesson should take from 20 to 30 minutes to
complete.
Monarch Help File
The Monarch Help file provides detailed information and step-by-step instructions for
performing numerous tasks in Monarch and Data Prep Studio. It also includes information on
automating Monarch operations, preparing files for input to Monarch, and technical
specifications. To access the file while within Monarch, select File, click on the drop-down
button of the Help menu, and then select Help Topics from the options that display. You
can also launch relevant Help topics from within the Monarch windows by simply clicking the
button located on the upper right-hand corner of most views.
Monarch Training
Datawatch provides Monarch training courses to help anyone in your organization rapidly
improve his or her Monarch proficiency skills. Available in locations around the country, these
training classes are designed for all levels of expertise. Whether you are just getting started
and need the basics, or you would like to hone your skills and learn new ways to use Monarch,
Datawatch is ready to help you reach new levels of Monarch proficiency. Our objective is to
help Monarch users succeed as quickly as possible.
2 Monarch 14.2 Learning Guide
Taught by Datawatch’s seasoned staff of Monarch experts, the training courses guide you
through all aspects of Monarch’s features and capabilities, including tips and techniques on
effective, timesaving shortcuts. There is also a lab session at the end of class where you can
bring in your own files and receive individual instruction and suggestions for using Monarch to
meet your specific requirements.
Monarch onsite training and personal web-based training courses are also available.
To learn more about training, call the Training Department directly:
Phone: +1-800-445-3311
Email: training@datawatch.com
Monarch Model Building Service
Datawatch provides a model building service for its Monarch customers. Free up your time and
let the Monarch experts create your models for you. Datawatch’s highly trained team of
Monarch experts will work with you one on one to create a model that meets your
specifications and satisfaction.
Are you puzzled by a really complex report? Datawatch’s Monarch professionals will be happy
to create your Monarch models for you. When you view your new Monarch model, you will be
able to learn from the experts by viewing the way they have trapped your report, created
calculated fields and filters, defined summaries, and more. Best of all, this is all done from
your own report.
To learn more about the Monarch Model Building Service, contact the Model Building Services
team directly:
Phone: +1-978-441-2200
Email: model@datawatch.com
Monarch 14.2 Learning Guide 3
[2] Introducing Monarch
What is the Monarch Application?
Datawatch MonarchTM (Monarch) is a multi-component application that allows you to model
and extract analytical assets from both structured and less-than-structured sources, such as
PDFs, reports, and EDI streams, and then use the data obtained in a self-service manner on
your desktop for broader analytical use.
When a computer generates a report, it creates a report file that contains all of the characters
and control codes required by a printer to produce the actual report printout. Monarch reads
that same report file. Instead of producing a hardcopy printout with words and numbers
frozen on the page, however, it creates a softcopy of the report on screen, with live data you
can work with.
Monarch reads report files created within any computing environment. These files are
commonly known as print or spool files, but they are also referred to as text (TXT), formatted
text, PRN, PDF, and SDF files. Monarch can also read a wide variety of input files, such as
delimited (e.g., pipe, comma, semi-colon, etc.) text, HTML, MS-Excel, MS-Access, dBase, PDF,
and XPS, among others, and is able to connect to OLE DB and ODBC sources.
The complete features of your Monarch application depend on the license you purchased. In
this learning guide, these licenses may be referred to as “Monarch versions.” Click here to
learn more about the different Monarch licenses available.
New Features in This Version
In Monarch version 14.2, the Classic application includes the following new features:
Read and write SAS XPORT format files (.xpt, .stx) - This feature provides interoperability
between SAS platforms and is the best overall format for interfacing with other systems as
it is consistent over all host environments. Note that this feature is only available in
Monarch Complete and Monarch Complete with Table Extractor.
Enhanced PDF Options – Addition of dynamic and static guides improves user ability to
assess suitability and set the correct stretch factor; screen real estate is also maximized.
Extended support for EBCDIC - New encoding functionality simplifies seeking for correct
encoding and line/record options for EBCDIC and other input documents. As well, the
maximum line length (i.e., maximum width of EBCDIC files) has been increased to 65K
characters.
Autorecovery for models and projects - Users no long have to worry about losing
information when the Monarch application is accidentally terminated or crashes.
License check in/check out
Various bug fixes
4 Monarch 14.2 Learning Guide
The Monarch Suite of Applications
The table below describes all of the main functionalities available in Monarch Classic, Monarch
Complete, Monarch Complete with Table Extractor, and Monarch for IBM Analytics.
FUNCTIONALITY
MONARCH
COMPLETE
MONARCH
COMPLETE
WITH TABLE
EXTRACTOR
MONARCH
CLASSIC
MONARCH FOR
IBM
ANALYTICS
Open reports, databases, and
webpages in Report view
Open reports, databases, and
webpages via the Select Data
Source to Open dialog
Create tables in Table view
Perform table operations
Create summaries in Summary
view
Perform summary operations
Export to .csv, .pdf, .xls, .xlsx,
etc.
Export to Tableau data extract
and QlikView QVX files
Export to IBM Analytics
(Watson Analytics, Cognos
Analytics)
Export to Tableau Server
Export to Monarch Swarm
Interface with Datawatch
Designer
Interface with Data Prep
Studio
Access to Datawatch Data
Connectors
Auto-Define button in Report
view
Access to Monarch Table
Extractor
If the functionality you desire is not included in your version of Monarch, contact Datawatch
Sales to determine how you can best take advantage of all of Monarch's powerful data
extraction and analysis features.
Monarch 14.2 Learning Guide 5
Note that unless otherwise specifically stated, the topics described in this learning guide are
applicable to all versions of Monarch.
The Monarch Workflow
Figure 2-1 shows how report data flow through Monarch. A Monarch session begins when you
load a report file into Report view. As the session unfolds, you can produce a variety of
outputs from each window. The lessons in this guide correspond to the flow chart. You’ll learn
how to bring a report into Monarch, and you will progress step-by-step through the tasks
performed in each window.
Input
Output
Report File
Report View
View and explore report
Look things up (Find)
Use Report Index
Print pages
Copy to clipboard
Define data extraction template
Hardcopy printout of pages
Clipboard image of data copied from the
report, with automatic formatting of the
data when pasting into a worksheet
Export file in fixed width text or PDF
format
Extracted data
Table view
View extracted data
Apply filters to extracted data
Sort data
Calculate new fields
Copy to clipboard
Export selected records
Print selected records
Hardcopy printout of selected data in
columnar report format
Clipboard image of data copied from the
table, in both text and worksheet formats
Export file containing selected data, in a
choice of formats, including XLS, XLSX,
XLSM, PDF, ACCDB, MDB, DBF, HTML,
TXT, DWX, XML, and delimited text.
Extracted data
Summary Window
Analyze selected data
Copy data to the clipboard
Export analysis
Print analysis
Hardcopy printout of summary report
Clipboard image of summary (in both text
and worksheet formats)
Export file containing selected data, in a
choice of formats, including XLS, XLSX,
XLSM, PDF, ACCDB, MDB, DBF, HTML,
TXT, DWX, delimited text
Figure 2-1. The Monarch flow chart.
6 Monarch 14.2 Learning Guide
Monarch reads both report files and data from database files and OLE DB/ODBC databases,
and information from databases can be extracted, manipulated, analyzed, and transformed. It
also allows users to access data from additional sources, share user-defined functions, and
access data from files that are of the types XLS, XLSX, XLSM, PDF, XPS, DBF, DB, MDB, HTML,
and delimited text, as well as any OLE DB- or ODBC-compliant data source, such as SQL
Server, Oracle, and DB2 database management systems.
Input
Output
Database data
Table view
Import data from any
XLS, XLSX, XLSM, MDB,
DBF, delimited text file,
or from any OLE
DB/ODBC compliant data
source
View imported data
Apply filters to data
Sort data
Calculate new fields
Copy to clipboard
Export selected records
Print selected records
Hardcopy printout of data in
columnar report format
Clipboard image of data copied
from the table, in both text and
worksheet formats
Export file containing selected
data, in a choice of formats
including XLS, ACCDB, MDB, DBF,
HTML, XLSX, XLSM, TXT, DWX,
PDF, and delimited text
Imported data
Summary Window
Analyze selected data
Copy data to clipboard.
Export analysis
Print analysis
Hardcopy printout of summary
report
Clipboard image of summary (in
both text and worksheet formats)
Export file containing selected
data, in a choice of formats,
including XLS, XLSX, XLSM, PDF,
MDB, DBF, TXT, DWX, and
delimited text
Figure 2-2. The Monarch flow chart showing data imported from a database.
Figure 2-2 shows how data imported from a database flows through Monarch. You may begin
a Monarch session by either opening an XLS, XLSX, XLSM, MDB, ACCDB, DBF, or delimited
text file, or by importing data from an OLE DB/ODBC data source. When importing from a
database, you can use all of Monarch’s Table and Summary View capabilities to manipulate,
transform, and analyze data.
You can even combine data extracted from report files with database data through a
mechanism called an external lookup.
Note that as the capabilities of newer versions of Monarch improve, the expected outputs
presented in this section may vary. The basic data flows, however, remain the same.
Monarch 14.2 Learning Guide 7
The Monarch Interfaces
Monarch provides three distinct views of report data, each with its own interface. Each view
provides a specific set of functions and operations that will allow you to extract, view,
summarize, and export data.
If you are using Monarch for IBM Analytics, you will not have to access the Summary and
Export views and you will be unable to access Datawatch Designer through the Table or
Summary views.
REPORT VIEW
When you load a report file, a softcopy of the report is displayed in Report view.
Figure 2-3. The Report view of Datawatch Monarch.
Report view displays opened reports and includes all of the tools you will need to work with
them. It also allows you access to Report Design view, an interface through which you can
create templates to extract data and create tables.
8 Monarch 14.2 Learning Guide
TABLE VIEW
Data extracted from the report display in Table view.
Figure 2-4. The Table view of Datawatch Monarch.
Table view allows you to work with the table produced by your report templates. You can click
Table Design to edit your table and its fields. All of the changes you make to a table (e.g.,
filters, sorts, functions, etc.) display in this view. If your Monarch license allows it, you will
also be able to open tables in Datawatch Designer through this view.
Some of the other activities you can perform in Table view include:
Set the active filter to limit the fields displayed
Set the active sort to sort fields
Auto-size columns
Hide or display fields
Find a specific field value
Monarch 14.2 Learning Guide 9
SUMMARY VIEW
User-defined summaries, which analyze report data, are displayed in Summary view.
Figure 2-5. The Summary view of Datawatch Monarch.
Summary View allows you to work with summaries. From this View, you can also click
Summary Design to create or edit summaries.
From within Summary View, you can also do the following:
Navigate a summary
Zoom in or out
Automatically adjust column widths
Freeze panes
Collapse or expand a summary
Customize summary font and background
10 Monarch 14.2 Learning Guide
EXPORT VIEW
Exports defined may be viewed in Export view.
Figure 2-6. The Export view of Datawatch Monarch.
From this window, you can also do the following:
Run exports
Create a new export
Monarch 14.2 Learning Guide 11
[3] Introducing Data Prep
Studio
What is the Data Prep Studio
Application?
Data Prep Studio is a Monarch component intended to provide users with rapid data
preparation (data prep) capabilities. As a quick data prep solution, Data Prep Studio offers
several powerful features, including:
Drag and drop parsing and extraction
Report discovery and data combination
Real-time data preparation
Export capability to several file types
Data Prep Studio provides easy-to-use interfaces for you to:
Add data sources
Data Prep Studio allows you to easily load information from databases, reports, and even
HTML pages. Special Preview functionality allows you to review the information and
improve data quality before committing these into the Data Prep Studio workspace.
Combine disparate data sources
Data Prep Studio allows you to easily integrate information from various data sources.
Clean and transform your data
Data Prep Studio’s data preparation capabilities include pre-built functions to quickly and
consistently transform your data into clean, analysis-ready information.
12 Monarch 14.2 Learning Guide
New Features in This Version
In Monarch version 14.2, the Data Prep Studio application includes the following new features:
Monarch Table Extractor - Identify tables in text-heavy reports, such as annual reports
and prospectuses, select those you need, and export them to Data Prep Studio, ready for
further data preparation.
Excel Explorer - A new dialog implemented when opening Excel files allows users to
preview and view multi-sheet Excel files, treat named ranges as single or multiple sheets,
and select custom options for each sheet or range as needed.
File reconciliation - Make data sharing and reuse easier by swapping or replacing data
sources as necessary. Issues arising from changing or updating a file system are quickly
flagged and resolved.
Join improvements - Join analysis can now be accessed from within the Join Configuration
dialog. Create new joins from the Combine Data Stage with a single click. Allow joining on
disparate data types.
Change list improvements - Data Prep Studio now allows changes to be moved up and
down the Change History List, deleted, and grouped, thereby allowing users greater
flexibility when preparing data for further analysis.
New dialog for Split Address Into Parts - The updated dialog allows extraction of up to six
address lines as well as countries, and error codes.
Various UI/UX improvements
Various bug fixes
Launching Data Prep Studio
Data Prep Studio may be launched in a number of ways
Launching Monarch for the first time after installing it.
Selecting Start > All Programs > Datawatch Monarch 14 > Datawatch Data Prep Studio >
Datawatch Data Prep Studio.
Selecting the Data Prep Studio and Open Table in Data Prep Studio buttons in
Monarch’s Home and Table views, respectively.
Monarch 14.2 Learning Guide 13
The Data Prep Studio Workflow
Figure 3-1 illustrates a typical data prep workflow.
Figure 3-1. The data prep workflow.
All data prep operations are performed in a workspace. Workspaces (including all tables
opened or created, joined, cleaned, and transformed) may be saved and loaded in future Data
Prep Studio sessions.
The Data Prep Studio Interface
First-time Data Prep Studio users will find the Getting Started video especially helpful in
learning about this application’s capabilities.
This video displays the first time Monarch is launched as well as every time the application is
started thereafter unless the Show at start up box located at the lower left-hand corner of
the screen is unchecked.
14 Monarch 14.2 Learning Guide
Select Exit Tutorial to close this video window.
Figure 3-2. The Getting Started video.
THE START PAGE
The Start Page displays when the application is launched and the Getting Started video is
closed (by clicking Exit Tutorial)/disabled. Besides providing access to video tutorials and
related documentation and support, the Start Page is your primary jumping-off point for
bringing data into the current data prep session.
Figure 3-3. The Data Prep Studio Start Page.
Monarch 14.2 Learning Guide 15
Data Prep Studio features one toolbar and several main screens.
Figure 3-4. The Data Prep Studio toolbar.
Each of these tools opens a unique window, dialog, or stage through which Data Prep Studio’s
functions may be realized.
THE SETTINGS DIALOGS
You can specify application default or current workspace settings by clicking the Settings
button located to the far right of the toolbar and selecting the appropriate option.
Note that some application settings, folder tracking, for example, are applied only when Data
Prep Studio is restarted. Also, settings are serialized from the last closed Data Prep Studio
session. This means that when two concurrently running sessions of the application are closed,
only settings from the session closed the last are saved and reflected when a new session is
started.
Application Default Settings allow you to specify row limits, default folders, conversion
settings, and the like on a global level.
Figure 3-5. The Application Default Settings dialog of Data Prep Studio.
The Current Workspace Settings dialog allows you to specify workspace-level conversion and
time-interval settings.
16 Monarch 14.2 Learning Guide
Figure 3-6. The Current Workspace Settings dialog of Data Prep Studio.
Details on each of these settings may be obtained here.
THE APPLICATION MENU
The Application Menu button displays a list of other Data Prep Studio commands you
may wish to apply to the current Data Prep Studio session.
Figure 3-7. The Applications Menu of Data Prep Studio.
Monarch 14.2 Learning Guide 17
Using this menu, you can:
Open a workspace
Work with change lists
Save a workspace
Switch to or open a table in Classic mode
Close a workspace
Exit Data Prep Studio
THE PREVIEW DATA WINDOW
The Preview Data window allows you to:
Open a file or a data source
Preview and confirm the data in the data source
Load a table into your workspace
Figure 3-8. Data Prep Studio’s Preview Data window.
Several tools are provided to enable you to open/preview more tables, select one or a group of
tables, and close one or a group of tables. You can also append tables from the Preview Data
window.
Figure 3-9. The Preview Data window tools.
18 Monarch 14.2 Learning Guide
Tables are arranged according to their Data Source Group. This grouping system allows you
to quickly select, deselect, and remove (close) tables in a group.
Figure 3-10. The Beantown Data Source Group.
The Search Data Source panel located on the upper right-hand corner of the Preview Data
window allows you to search for specific tables by Data Source Type or Data Source Name.
This functionality is especially useful when you wish to load just one or two tables from a large
number of open data sources.
Figure 3-11. The Search Data Source panel.
THE TRANSFORM DIALOG
You can quickly transform any table in the Prep Data window by clicking on the Transform
button of the Data Prep Studio toolbar. Doing so launches a Select Transform Type dialog that
will allow you to extract empty rows in the table, pivot/unpivot columns, group by specific
columns, and remove duplicates.
Figure 3-12. Data Prep Studio’s Transform dialog.
When grouping columns, the following aggregation functions may be applied:
Number
Sum
Count
CountNotEmpty
Average
Maximum
Median
Monarch 14.2 Learning Guide 19
Minimum
Don’t aggregate
Text
Count
CountNotEmpty
Maximum
Median
Minimum
Don’t aggregate
Date/Time
Count
CountNotEmpty
Maximum
Median
Minimum
Don’t aggregate
THE PREP DATA WINDOW
The Prep Data window allows you to transform columns. Pre-defined functions include convert,
extract, clean, replace, and split capabilities, among others.
Figure 3-13. Data Prep Studio’s Prep Data window.
20 Monarch 14.2 Learning Guide
THE COMBINE DATA STAGE
The Combine Data stage is a switch on/switch off function that allows you to join two or more
tables to create a new one.
Figure 3-14. Data Prep Studio’s Combine Data stage.
THE REPORT DISCOVERY WINDOW
The Report Discovery window allows you to open report files and select specific fields from this
report to create a table, which then becomes available in the Preview Data Window.
Figure 3-15. Data Prep Studio’s Report Discovery window.
Monarch 14.2 Learning Guide 21
THE EXPORT DATA DIALOG
The Export dialog allows you to export table data from Data Prep Studio to a number of
different file types.
Figure 3-16. Data Prep Studio’s Export dialog.
Data Prep Studio Hotkeys
Keyboard shortcuts (hotkeys) for the main toolbar are enabled in Data Prep Studio to help you
quickly access the different windows.
Figure 3-17. Data Prep Studio’s hotkeys.
The following table describes the main hotkeys available in Data Prep Studio.
USE THIS HOTKEY
TO
ALT + S
Access the Start Page
ALT + V
Access the Preview Data window
ALT + P
Access the Prep Data window
ALT + T
Launch the Select Transform Type dialog
ALT + C
Access the Combine Data stage
ALT + E
Launch the Export Data dialog
ALT + G
Launch the Application Default Settings dialog
ALT + F
Launch the Application Menu
22 Monarch 14.2 Learning Guide
A detailed list of the keyboard shortcuts available in Data Prep Studio may be found here.
Data Prep Studio Table Icons
The right-hand side of tables displayed in the table lists of the Prep Data window is marked
with icons to help you identify the table type.
THIS ICON
FUNCTION
Marks a table.
Marks a join.
Marks an append.
Marks a report.
Marks a transformation.
Marks a Monarch report node.
Monarch 14.2 Learning Guide 23
[4] Data Prep Studio Lessons
Because most data prep needs may be addressed by Data Prep Studio, we begin this learning
guide with lessons on how to work with this exciting add-on.
Figure 4-1. The Data Prep Studio Start Page.
Data Prep Studio supports a number of main functions:
Add data from a wide variety of data sources
Add data using the Datawatch Data Connectors
Join and append table data to create new data tables
Clean and transform tables for further operations
Export tables
Save and load workspaces
Each of the lessons described in this section demonstrate how to use Data Prep Studio.
24 Monarch 14.2 Learning Guide
Before You Begin
Throughout the lessons, we make the following assumptions:
Monarch Complete is properly installed and the Monarch program item is available when
you start Windows.
The lesson files are installed and Monarch’s default Report, Model, and Project folders are
configured to point to the lesson files (the Setup program ideally automatically assigns
these defaults the first time you install the product but you may want to assign other
folders instead).
You are using an operating system for which Monarch is certified.
The Date Format setting in Monarch has been set to M/D/Y to handle dates in the sample
reports correctly.
NOTE
If you use a different OS, some slight deviations from the steps listed
in the lessons will occur (e.g., the default paths, the appearance of
screens, and other minor differences), but you can effectively
complete the lessons on all versions of Windows that Monarch
supports.
This learning guide also assumes that you have copies of the following files stored in your
computer:
Beantown.mdb
Payroll.mdb
Classic.pdf
Canaccord 042215.pdf
These files are typically installed along with Monarch and are found in
C:\Users\Public\Documents\Datawatch Monarch\Data and
C:\Users\Public\Documents\Datawatch Monarch\Reports. If you do not have these file, you
can download them from here.
We begin our lessons by adding data to a Data Prep session.
Monarch 14.2 Learning Guide 25
Previewing and Adding Data to a Data
Prep Session
A Data Prep session begins by placing data, either from a preexisting table or from a table
created through the Report Discovery window, to the Preview Data window.
The following file types may be opened in Data Prep Studio:
Delimited text files (.csv)
Print files (.dat, .prn, .rpt, .txt)
Fixed text files (.txt)
PDF files (.pdf)
Microsoft Excel files (.xls, .xlsx)
XPS files (.xps)
Microsoft Access files (.mdb, .accdb)
HTML files (.htm, .html, .asp, .aspx)
JSON files (.json)
Log files (.log)
XML files (.xml)
Data Prep workspace files (.dpwx)
ADDING DATA FROM A DATABASE FILE
Steps:
1. Launch Data Prep Studio.
2. Select Open Data to display the Select Data Source to Open dialog.
Figure 4-2. The Select Data Source to Open dialog.
26 Monarch 14.2 Learning Guide
3. Select the data source option Microsoft Access.
4. In the Open dialog that displays, navigate to C:\Users\Public\Documents\Datawatch
Monarch\Data, select Beantown.mdb, and then click Open.
You are brought to the Preview Data window. Each of the tables that make up the
database displays in this window.
Figure 4-3. Tables brought into the Preview Data window.
NOTE
Letters beyond the 64th character are truncated when opening tables
from Excel files with column names exceeding 64 characters because
the Access driver used to read these files imposes a 64-character
limit.
5. Inspect the table you wish to open by clicking on its Preview icon. In Figure 4-4, for
example, a preview of the table Accounting is displayed.
Monarch 14.2 Learning Guide 27
Figure 4-4. Viewing the contents of the Accounting table.
A set of panels showing information about the columns of the table, as well as a preview
of the rows in the table, are shown.
6. To view information about a column, select this column in the Column Information panel.
Information about the column is also displayed on the Statistics panel.
7. To hide the statistics panel, select the Hide Preview button.
8. Load the table(s) into your Workspace by selecting the desired table(s) individually or
clicking the Select All button to load all of the tables simultaneously and then clicking
Load Selected Tables.
The table(s) you selected become(s) available in the Prep Data window.
In Figure 4-5, all three tables that make up the Beantown database were loaded into the
workspace. Each table is presented in its own worksheet and displays on a single table
panel.
28 Monarch 14.2 Learning Guide
Figure 4-5. Loading all of the tables in the Beantown database into the Prep Data window.
Clicking on the drop-down buttons located to the right of these tables displays a context
menu that will allow you to perform table operations, including change row limits, edit file
paths, pin tables, and the like.
A complete list of the actions that can be performed on tables may be found here.
The bottom of the Prep Data window (see figure above) presents several options that will
allow you to navigate through your tables easily.
Note that while a database may contain any number of tables, only those tables you
selected and loaded from the Preview Data window display in the Prep Data window.
Limiting Rows
You can change the number of rows Data Prep Studio loads on a global basis when it opens
tables by selecting Settings from the Data Prep Studio toolbar and then selecting
Application Default Settings from the options that display. This setting may be found in the
Row Limit tab. Note that while decreasing the row limit when opening tables can improve the
performance of Data Prep Studio and speed up response times, this may also result in missed
exceptions and problematic rows, particularly if these rows are not included in the limited row
set.
If you wish to change the row limit of a specific table that has been opened in the Prep Data
window, click on the drop-down button located to the right of the table from the table selector
and, from the options that display, select Load Options > Change Row Limit.
If you wish to change the row limit of a table from the Preview Data window, select the Edit
Source Row Limit button and use the Edit Source Row Limit dialog that displays to do
so.
Monarch 14.2 Learning Guide 29
Changing File Paths
You may wish to change the file path of an open table in the Prep Data window (e.g., a file
with the same name is located in a different folder in your machine or network). To do so,
click on the drop-down button located to the right of the table from the table selector and,
from the options that display, select Load Options > Edit File Path. In the Open dialog that
displays, navigate to the file you wish to use and then click Open. Note that you can only
replace the contents of a table with those of another table with the same file extension. Data
Prep Studio automatically detects the file extension of the open table to help guide your
replacement choices.
Editing Load Options
When your table is one of several tables in an Access database or one of several worksheets in
an Excel file, you can select a different table to open by clicking on the drop-down button
located to the right of the table from the table selector and, from the options that display,
selecting Load Options > Edit Load Options.
For Access data sources, the following dialog displays:
Figure 4-6. The Access Options dialog.
Select the new table you wish to load from the drop-down list provided and then click OK.
For Excel data sources, the following dialog displays:
Figure 4-7. The Excel Options dialog.
From the settings provided, specify those most applicable to the table you wish to open and
then click OK.
30 Monarch 14.2 Learning Guide
For HTML tables, the following dialog displays:
Figure 4-8. The HTML Options dialog.
After specifying the necessary details, click OK.
Pinning Tables
When a table is pinned in the Prep Data window (i.e., click on the drop-down button located to
the right of the table from the table selector and, from the options that display, select Load
Options > Pin Table from the options that display) and the workspace is saved, a user
without direct access to the table source may open the pinned table by opening this same
workspace. When pinning a table, a user may also choose to pin the current rows opened
(when a row limit has been set) or pin the entire data set.
Figure 4-9. Pinning a table in the Prep Data window.
This functionality allows data-sharing with users who do not necessarily have rights to the
source systems required to create working tables. Note, however, that the pinned table cannot
be refreshed by a user without access to the data source.
Refreshing Table Data
When a table (e.g., an Excel worksheet) is open in the Prep Data window of Data Prep Studio,
you essentially have a snapshot of the current state of this table. When you or another user
makes changes to this table, your table “snapshot” must be rebuilt to reflect these changes.
To do so, click on the drop-down button located to the right of the table from the table
selector and, from the options that display, select Refresh Data Table from the options that
display.
Note that tables obtained from drag-and-drop operations in web pages cannot be refreshed
because such tables are not sourced from a file path or URL. In these tables, the Edit File
Path and Refresh Data Table buttons are not available.
Monarch 14.2 Learning Guide 31
Saving Data Sources to the Data Source Library
Data Prep Studio can save tables you opened in the Prep Data window the Data Source Library
Library. From here, you can further choose to save the table to your local library or to the
Monarch Swarm library.
The Monarch Swarm Library includes all data source definitions and workspaces saved with the
intention of sharing in Monarch Swarm.
When you save a data source or table to this library, Data Prep Studio “remembers” the path
or location of this source and applies it when the same is opened in another data prep session.
Thus, you can use the Data Source library to access commonly-used data sources quickly and
seamlessly without using the Open Data Source dialog, previewing the data source, and
selecting individual tables to open.
To save a data source to the Data Source library, go to the Prep Data window, click on the
drop-down button of the table you want to save, and, from the options that display, select
Load Options > Save in Library > Local or Monarch Swarm.
To load a data source from the library, select the Open Library icon from the Data Prep
Studio toolbar or Open Library from the Select Data Source to Open dialog. This action
launches the Library dialog.
Figure 4-10. The Library dialog.
To perform an action on any data source in this list, select it to activate it and then choose an
operation to execute. You can edit the table path, load the data source, preview the data
source, or delete the selected data source.
More information on the Data Source Library may be found here.
32 Monarch 14.2 Learning Guide
ADDING DATA FROM A PDF REPORT
The steps below describe how to open PDF reports in Data Prep Studio.
Steps:
1. Return to the Preview Data window and then select Add More Stuff.
2. In the Select Data Source to Open dialog that displays, click PDF Report to display the
Open dialog.
3. Navigate to C:\Users\Public\Public Documents\Datawatch Monarch\Reports, select
Classic.pdf, and then click Open.
You are brought to the Report Discovery window.
Figure 4-11. Loading Classic.pdf into the Report Discovery window.
4. Automatically define fields by selecting the Auto Define button on the
toolbar.
Monarch 14.2 Learning Guide 33
Figure 4-12. Automatically defining fields in the Report Discovery window.
You can also:
Define each column individually by double-clicking on a field.
Figure 4-13. Defining individual fields in the Report Discovery window.
34 Monarch 14.2 Learning Guide
Define a line by double-clicking on the margin beside the line you want to define.
Figure 4-14. Defining a line in the Report Discovery window.
Initiate Report Discovery Advanced mode by right-clicking on the report area and
then selecting Advanced Mode. This action changes the color of the report to green.
Figure 4-15. Initiating Advanced mode in the Report Discovery window.
Data Prep Studio will create a new column for each field that you define, and populate that
column with similar field values.
Monarch 14.2 Learning Guide 35
5. Select Open in Data Prep Studio to complete document prep. The table you have just
defined is added to the Preview Data window.
Figure 4-16. The newly defined table in the Preview Data window.
If you wish to make changes to your newly defined table after bringing it into the Preview
Data window, you can either select Start Page > Recent Files and then click on
Classic.pdf or click on the drop-down arrow beside the report name in the table list of the
Prep Data window and then select Edit Report.
Doing this will bring up the Report Discovery window once more and allow you to make
additional changes to your table.
6. Load this table into your workspace by selecting it and then clicking Load Selected
Tables.
NOTE
You can perform some changes to the table extracted from a report,
including change its column names and fill nulls with the value
preceding the empty cell, in the Report Discovery window.
These changes do NOT form part of the session’s Audit Trail
(Change History) because the Report Discovery window is a
settings panel that creates a new table from the settings selected.
The Change History function of Data Prep Studio only tracks
changes to existing tables.
If you wish to apply more changes to a table you extracted using
the Report Discovery window and have these changes tracked,
open the table in the Prep Data window instead.
36 Monarch 14.2 Learning Guide
Figure 4-17. The newly defined table in the Prep Data window.
Using Report Discovery Advanced Mode
Instead of using Report Discovery’s Auto-define Fields function, you may wish to define
fields yourself. This is done using Report Discovery’s Advanced Mode.
Steps:
1. In the Report Discovery window, right-click on the report and then select Advanced
Mode.
Figure 4-18. The Advanced Mode screen of the Report Discovery window
Monarch 14.2 Learning Guide 37
2. Hover over a character on the line you wish to mask. Ideally, this character will form part
of your mask.
3. Using your mouse, click on the character you wish to mask and cycle through the mask
characters until you come to the correct one. Mask characters include:
– Any letter
– Any digit
– Any non-blank character
– Blank
For example, starting from the first line with the Media type CD, go to the QTY column,
hover your mouse over the first 4 that appears under this column and then click once to
expose the mask.
4. If the mask is composed of several characters, move to the next character (the first blank
space) and repeat Step 2. Do this step until the mask is completed. Notice that all lines
containing the mask you have defined are highlighted.
The figure below, for example, shows how you might mask the order lines of Classic.pdf.
Figure 4-19. Viewing the results of the mask we created.
The mask we created indicates that the details of any line with one number, two blanks,
and a letter should be flagged and added to your table.
5. Select Accept Mask Changes, and then click Accept on the Report Discovery window.
38 Monarch 14.2 Learning Guide
The table you have just defined is added to the Preview Data window. If you wish to load
this table into your workspace, select it and then click Load Selected Tables.
ADDING DATA FROM A WEBSITE
Data Prep Studio is unique from other data prep software because it is capable of intuitively
determining tables from webpages and recreating these in the Preview Data workspace.
Steps:
1. On your browser, go to the webpage http://www.nasdaq.com/extended-
trading/premarket-mostactive.aspx.
This webpage displays, among others, a chart of the pre-market most active stocks listed
in the NASDAQ, the second-largest stock exchange in the U.S.
Figure 4-20. NASDAQ’s pre-market most active stocks webpage.
2. Click your mouse anywhere one the webpage, taking care to avoid links, and then press
CTRL + A on your keyboard to select the entire page.
3. Using your mouse, drag and drop the contents of the webpage into the Preview Data
window of Data Prep Studio.
Data Prep Studio intelligently detects tables in webpages and recreates them so that they
can be brought into the Prep Data window.
Monarch 14.2 Learning Guide 39
Figure 4-21. The newly defined table in the Preview Data window.
You can also drag and drop tables from HTML files/pages directly into the Prep Data
window or Combine Data stage.
A preview of the table extracted by Data Prep Studio reveals the same table displayed in
NASDAQ’s Pre-market Most Active Stocks webpage.
Figure 4-22. Previewing the details of a table imported from a webpage.
4. Select Load Selected Tables to open the newly defined table in the Prep Data window.
40 Monarch 14.2 Learning Guide
Figure 4-23. The newly defined table in the Prep Data window.
Data Prep Studio allows you to open files stored on a web repository, such as Amazon S3, FTP,
SFTP, and the like. To do so, click the Open from Web button located at the bottom of
the dialog. This action launches an Open Data Source from Web dialog wherein you can enter
the URL of the webpage containing the table you wish to bring into Data Prep Studio.
What’s the Difference between Dragging and Dropping Webpage Content
and Using the Open from Web Dialog?
The browser is not only a rendering engine, but also is a scripting engine. For HTML with no
Javascript that is delivered in its complete form and rendered in the browser, the
copy/drag/drop content will be the same as the content obtained directly from the URL.
However, if the HTML contains Javascript, which dynamically inserts the tables as HTML when
rendered on the browser, only the copy/drag/drop will contain dynamically created tables; the
HTML obtained directly from the URL will not contain these tables.
ADDING DATA FROM XML AND JSON FILES
Data Prep Studio allows you to explore and open JSON and XML files.
Steps:
1. From the Preview Data window, select the Add More… button to launch the Select Data
Source to Open dialog.
2. Choose JSON (or XML).
Monarch 14.2 Learning Guide 41
3. In the Open dialog that displays, navigate to the location of the file you wish to open,
select the file, and then click Open.
The JSON Explorer displays.
Figure 4-24. The JSON Explorer. A similar explorer is also available when opening XML files.
XML and JSON files present a hierarchical arrangement of nodes or tables. Selecting a
node (table) displays all of its fields on the right-hand side of the explorer and a preview
of the data contained within it. When checked, the Use Simplified Column Names box
located on the upper right-hand corner of the explorer instructs Monarch to eliminate
extraneous characters (e.g., the @ sign) from column names.
42 Monarch 14.2 Learning Guide
Figure 4-25. The XML Explorer with a node selected.
Selecting a node also shows the number of rows it contains if this number is more than
one.
4. Select a node to open by clicking on it.
5. Select Row Expand Arrays or Column Expand Arrays to display JSON arrays into rows
or columns, respectively.
6. Select an Encoding Option if necessary.
7. Click OK.
Your table is loaded in the Preview Data window. You can select and add this table to your
list of open tables as usual. For the rest of the exercises, however, this is not necessary.
Monarch 14.2 Learning Guide 43
ADDING DATA FROM MONARCH FILES
Data Prep Studio allows you to work with existing Monarch models and projects.
This means you can take advantage of the robust templating and extraction capabilities of
Monarch Classic and use the pre-defined models to work with reports.
However, note that tables that come from Monarch files behave differently from tables that
come from other sources:
Tables associated with a Monarch file are not displayed on the Preview Data window.
Data from reports associated with a Monarch file table are displayed as tables on the Prep
Data window but cannot be prepped.
The following Monarch Classic features are translated in Data Prep Studio when you open a
Monarch file:
Data captured via trap of all trap types
Models with multi-column regions
Models with formula-based calculated fields using all built-in Monarch functions
Models with lookup table calculated fields
Monarch projects that use with multiple reports
Monarch projects that use PDF reports
The following Monarch Classic features are currently not supported in Data Prep Studio:
Monarch projects that use databases. If you attempt to open a Monarch project that uses
a database, Data Prep Studio will display an error message
Summaries. Data Prep Studio will only display data as displayed on a Monarch table.
Filters. Data Prep Studio will load all of the data from a report and ignore any filters that
have been defined in the Model.
External Lookups. Data Prep Studio will load all of the data and will ignore external
lookups and the join logic associated with these.
User-defined functions. Fields that use user-defined functions are displayed in Data Prep
Studio with null values.
External Models. External model definitions within a Monarch model are simply ignored
when the model is imported into Data Prep Studio.
User-edited and runtime fields. Fields that use user-edit and runtime fields are displayed
in Data Prep Studio with null values.
Sorts
Password-protected PDF files
44 Monarch 14.2 Learning Guide
Special report-related functions
Project exports
To open Monarch Files in Data Prep Studio, the following steps are applied.
Steps:
1. Select Open Data from the Start Page to launch the Select Data Source to Open dialog.
2. Click Monarch File, and, from the Open dialog that displays, navigate to the folder in
which your Monarch file resides, select it, and then click Open.
If you open a Monarch model only, the model is displayed as a table in the Prep Data
window. To view data, you must add at least one report that is compatible with this
model. To do so, in the Prep Data window, click on drop-down located to the right of this
main table and select Add Report.
If you open a Monarch project, the model is displayed as the main table in the Prep Data
window and all other tables obtained from the report associated with the project are displayed
as sub-tables.
More information on working with Monarch Files in Data Prep Studio can be found here.
ADDING DATA FROM EXCEL FILES
Excel files may be added to your workspace via the Select Data Source to Open dialog or
simply dragging and dropping the file into the Start Page, Preview Data window, or Prep Data
window.
When opening Excel files, the Excel Explorer dialog displays.
Figure 4-26. The Excel Explorer dialog.
This dialog is used to specify which tables to open and how the data should be displayed.
When the necessary specifications have been provided and Load Selected is clicked, the
table(s) open as usual and they can be previewed and loaded to the Prep Data window.
More information on the Excel Explorer dialog may be found here.
Monarch 14.2 Learning Guide 45
USING THE RECENT FILES PANEL TO LOAD DATA
Clicking on the Recent Files button located on the upper left-hand corner of the Start Page or
the Preview Data window displays a panel from which you can view all recently opened
workspaces, data sources, and tables.
Figure 4-27. The Recent Files panel.
NOTE
The Recent Files panel is populated with data sources and tables
opened from previous and the current data prep sessions. If you
are using Data Prep Studio for the first time, no data sources and
tables will display when you expand this panel.
Selecting any item in this panel opens the related data source or table. This functionality
allows you faster access to your data and eliminates the need for repeated launching of the
Select Data Source to Open dialog just to load more data to the current data prep session.
When clicking on an item in the Recent Data Sources panel, say, an MDB file, all of the
tables included in this file are loaded into the Preview Data window and made available for
bringing into the Prep Data window. In Figure 4-28, we selected Beantown.mdb from the
Recent Data Sources panel. All three tables included in this database file are loaded into the
Preview Data window. From here, you can select which table(s) to open in the Prep Data
window as usual.
46 Monarch 14.2 Learning Guide
Figure 4-28. Opening Beantown.mdb from the Recent Data Sources Panel.
Items in the Recent Data Sources panel may be closed (i.e., removed from the data sources
list) by hovering your mouse on the data source you want to close and then clicking on the
Remove button that appears to its right.
When an item is selected from the Recent Tables panel, only this item is opened and loaded
into the Preview Data window. As in the previous case, items in this panel may be closed by
hovering your mouse on the table you want to close and then clicking on the Remove
button that appears to its right.
DISPLAYING RECENT DATA SOURCES IN THE SELECT DATA
SOURCE TO OPEN DIALOG
You can view and open recently opened data sources in the Select Data Source to Open dialog
by hovering on a file type option in the dialog. Doing so causes a drop-down arrow to appear
at the bottom right-hand corner of the option box.
NOTE
If no drop-down arrow displays when you hover over a file type
option in the Select Data Source to Open dialog, no files of this
type were recently opened.
Monarch 14.2 Learning Guide 47
Figure 4-29. Viewing recently opened data sources via the Select Data Source to Open dialog.
Clicking on this arrow produces a list of recently opened files belonging to this file type. Select
any item on this list to load it into the Preview Data window. Close data sources (i.e., remove
them from the list) by hovering your mouse on the data source file you want to close and then
clicking on the Remove button that appears to its right.
Figure 4-30. Closing a recently opened data source via the Select Data Source to Open dialog.
Now that we know how to open data from a table, report, and webpage into the Add Window,
we’ll explore how to open data from other database types using the Datawatch Data
Connectors. Let’s save our current workspace and open a new one.
48 Monarch 14.2 Learning Guide
Saving a Workspace
Your workspace includes:
The data tables you have loaded
The join and append definitions you have created
The transform operations you have carried out
The change histories of the tables you opened
You can save your workspace anytime as a Data Prep Workspace file (*.dpwx), and you can
load this workspace in future Data Prep Studio sessions to continue preparing your data.
Learn more about your workspace here.
Steps:
1. Select the Application Menu button from the Prep Data window and, from the menu
that displays, select Save Workspace.
2. Select one of the following:
Save the workspace on your computer
Save the workspace on your computer with a different name
Save the workspace with a password
Save the workspace with a different name and password
Save the workspace to the Monarch Swarm Library
3. In the Save As dialog that displays, navigate to the folder in which you want to save the
workspace, enter Workspace1 into the File name field, and then select Save.
4. If you opted to save the workspace with a password, enter and confirm this password in
the next dialog that displays. Select OK when you are finished.
5. Close the current workspace by selecting Application Menu > Close Workspace.
Monarch 14.2 Learning Guide 49
Using the Datawatch Data Connectors
Data Prep Studio allows you direct access to a wide variety of database types through a set of
built-in data connectors.
Figure 4-31. The Datawatch data connectors.
Data Prep Studio allows you to access data from the following database sources:
Microsoft Access
Oracle
IBM Cloudant
Salesforce
DB2
SQL Server
Informix
Splunk
Microsoft Excel
Monarch Server – Content
MySQL
OLEDB
Oracle
ODBC
Google Analytics
It also allows you to access data from the following big data sources:
IBM Cloudant
Cloudera Impala
MongoDB
Sybase IQ
Amazon Redshift
Hadoop Hive
50 Monarch 14.2 Learning Guide
Finally, data from other sources, such as Business Objects Universe and OData, are also
supported in Data Prep Studio.
To connect to any of these sources quickly and successfully, Data Prep Studio makes use of
data connectors.
To obtain access to data using this connector, specific information, including host name,
server name, user ID, password, and database name, among others, is necessary. Which
information you need will depend on the database to which you are attempting to connect. If
you wish to use any of the data connectors, contact your database administrator to obtain the
necessary connection details.
ADDING DATA FROM DB2, INFORMIX, MYSQL, ORACLE,
POSTGRESQL, SQL SERVER, HADOOP HIVE, CLOUDERA IMPALA,
AMAZON REDSHIFT, AND SYBASEIQ
The connection dialogs for DB2, Informix, MySQL, Oracle, PostgreSQL, SQL Server, Hadoop
Hive, Cloudera Impala, Amazon Redshift, and SybaseIQ are similar, although some data
connections may require more information than others.
Connecting to an Oracle database, for example, will require you to choose between the
Standard or TNSNames connection types; if you choose the Standard connection type, you will
need to provide an SID name or service name besides a hostname, user ID, password, and
edition name. If you choose the TNSNames connection type, you will need to provide a
TNSNames file name. Connecting to a SQL Server will require you to choose between Windows
authentication and username/password authentication.
The following typical example describes how to access data from an Apache Hadoop Hive
database using the relevant Datawatch Data Connector. If you do not have information to
access a Hadoop Hive database at the moment, simply follow along with this tutorial.
Note that this section simply demonstrates how to use the connector; thus, the tables we
opened previously will not appear in the Preview Data window until we load the workspace we
saved in the previous section.
Monarch 14.2 Learning Guide 51
Steps:
1. From the Start Page, select Open Data.
2. In the Select Data Source to Open dialog that displays, select Hadoop Hive.
The Hadoop Hive Data Connection dialog displays.
Figure 4-32. Initiating a connection with Hadoop Hive database.
3. Enter the hostname, user ID, and password to the database you wish to connect to.
4. From the drop-down box of the Hive Server Type field, select HiveServer2.
5. Click Fetch Databases and then use the selector to choose a database to connect to.
6. Click Load Tables to display a list of tables to open.
52 Monarch 14.2 Learning Guide
Figure 4-33. Displaying the tables of a Hadoop Hive database.
7. Select a table from the table list.
The fields of this table display in the Columns box.
Figure 4-34. Selecting columns to add to a table.
Monarch 14.2 Learning Guide 53
8. Choose the field(s) to load by ticking their checkboxes in the Columns box.
9. If you wish to parameterize (i.e., filter) or aggregate (group) the data in these fields, tick
the relevant boxes and then select a parameter or aggregate type to apply from the drop-
down boxes provided.
10. Select OK when you are finished. The table displays in the Preview Data window.
This table can be selected and loaded into the Prep Data window as usual.
CONNECTING TO IBM CLOUDANT
The following steps detail how to connect to a database via IBM Cloudant
Steps:
1. From the Preview Data window of Data Prep Studio, select Add More Stuff.
2. In the Select Data Source to Open dialog that displays, select IBM Cloudant. The IBM
Cloudant Connection dialog displays
3. Enter the account name or URL of the Cloudant account to which you wish to connect in
the Cloudant Account or URL field.
4. If you wish to connect to a specific database, check the box for Connect to a Specific
Database and then specify the name of the database in the field adjacent to this box. If
you wish to choose from a list of databases, skip this step.
5. Enter the username and password into the appropriate boxes and then click Login.
Figure 4-35. Connecting an IBM Cloudant database.
54 Monarch 14.2 Learning Guide
Upon successful connection:
The Database field displays the name of the database you specified in Step 4 or
The Database field’s drop down control is activated if you skipped Step 4; you can use
this field to select a database to connect to.
6. Select a view from the drop-down box provided.
7. Specify other settings using the fields and boxes provided if you wish.
8. Select Ok.
The table(s) contained in the database you selected display(s) in the Preview Data
window.
These tables can be selected and loaded into the Prep Data window.
CONNECTING TO ODATA
The following steps describe how to connect to a database via OData.
Steps:
1. From the Preview Data window of Data Prep Studio, select Add More Stuff.
2. In the Select Data Source to Open dialog that displays, select OData.
The Odata Connection dialog displays.
Figure 4-36. Initiating an OData connection.
Monarch 14.2 Learning Guide 55
3. Enter the collection list URL into the field provided.
4. Place your cursor in the User ID field and specify a user ID and password if necessary.
In the present example, we are using the Northwind database
(http://services.odata.org/Northwind/Northwind.svc/), which requires no user ID or
password.
5. Select Retrieve. A list of tables displays in the Collections box.
Figure 4-37. Selecting a collection (table) to open.
6. Select a collection (table) from the list provided and, in the adjacent Query Parameters
box, select Add.
Figure 4-38. Selecting fields to open in Data Prep.
56 Monarch 14.2 Learning Guide
7. From the drop-down list that appears in the Query Parameters box, select fields to add
to your table. You may filter results by specifying an operator and required (filter) value if
you wish.
8. Continue clicking Add and repeating Step 7 until you are satisfied with your table.
9. Select your desired decimal separator for numerical values.
10. Click OK.
The table you created displays in the Preview Data window.
This table can be selected and loaded into the Prep Data window as usual.
CONNECTING TO MONARCH SERVER – CONTENT
The following steps describe how to connect to Monarch Server content.
Steps:
1. From the Preview Data window of Data Prep Studio, select Add More Stuff.
2. In the Select Data Source to Open dialog that displays, select Monarch Server -
Content.
The Monarch Server - Content Connection dialog displays.
Figure 4-39. The Monarch Server – Content Connection dialog.
3. Enter the Monarch Server Client URL, Username, and Password in the fields provided
and then click Connect when you are finished.
Monarch 14.2 Learning Guide 57
The contents of the dialog expand to enable you to choose a document type group,
document type, model, report date range, report, view, and timezone.
Figure 4-40. Connecting to Monarch Server.
4. Using the drop-downs and boxes provided, specify the data table you wish to open and
then click OK.
The table/report you selected displays in the Preview Data window.
This table/report can be selected and loaded into the Prep Data window as usual.
NOTE
The Monarch Server - Content connector for Data Prep Studio only
functions if the Monarch Server security settings are set to
Default. Other settings, such as Active Directory, are not yet
supported in DPS.
58 Monarch 14.2 Learning Guide
CONNECTING TO BUSINESS OBJECTS UNIVERSE
The following steps describe how to connect to data stored in Business Objects Universe.
Steps:
1. In the Preview Data window, select Add More… to launch the Select Data Source to Open
dialog.
2. Select Business Objects Universe from the list of other database types to open.
The Business Objects Universe Connection Settings dialog displays.
Figure 4-41. The Business Objects Universe connection dialog.
3. Provide the hostname, port, Server, user ID, and password required to connect to the
Business Objects Universe. If the port you wish to use is different from the default port,
change the default value to the correct one.
4. Select any of the following Authentication Types:
Enterprise
LDAP
WinAD
SAPR3
Monarch 14.2 Learning Guide 59
NOTE
This list depends on what authorized providers are available for
the BOU instance.
5. Set the Request Timeout. 30 is the default.
6. Click Connect to load the selected Universe. The directory path where the selected
7. In the treeview of the loaded items, the following element types are displayed:
Folder or
These are the logical groups of related objects. You can only select one folder (group)
at a time.
Universe
Maps to the data in the database.
Dimension
Key objects to which queries are based on.
Business View
These are semantic layers specific for Crystal reports.
Filter
Used to limit the data that is returned.
Attribute (similar to Calculated columns)
8. Perform quick searches for universes, measures, and dimensions in the Search box.
NOTE
Only the previously loaded subtrees can be looked up via quick
searches when sub-trees are fetched on-demand.
All of the selected items in the treeview are listed in the Selected Items box.
60 Monarch 14.2 Learning Guide
Figure 4-42. Displaying selected items in the Business Objects Universe connection dialog.
9. You can modify the Display Name of an item. Also select a filter operator and parameter or
delete an item in the list.
10. The selected table with filters is displayed in the Filters box. These are stored in the
Business Objects Universe.
NOTE
BOBJ (Business Objects) supports specifying custom filters either
in the Filter box or a parameter can be set for any added Item in
the Selected Items box.
There is also a separate list for BOBJ predefined filters that you
can use.
11. Click the Validate button to ensure that your query is correct.
12. Click OK to confirm the selection and retrieve the record set into Designer.
The flat record set corresponding to the executed SQL is returned from the source
database and displayed in Data Prep with the database name as the title and all fields
listed displayed in Data Preview.
13. If you wish to make changes to your fields, you may do so now and then click OK when
you are finished. If you do not wish to make any changes to your data, simply select the
OK button.
Monarch 14.2 Learning Guide 61
CONNECTING TO SALESFORCE
The following steps describe how to connect to Salesforce data.
Steps:
1. Open the Select Data Source to Open dialog and then select Salesforce.
The Salesforce Connector Settings dialog displays.
Figure 4-43. The Salesforce Connector Settings dialog.
2. Enter a valid username, password, and security token and then click Connect.
3. Select the source type for data retrieval, i.e., Salesforce Objects Table or Report.
4. Select the data source to open according to the source type you selected.
5. Click OK.
CONNECTING TO MONGODB
The following steps describe how to connect to MongoDB data.
Steps:
1. Open the Select Data Source to Open dialog and then select MongoDB.
62 Monarch 14.2 Learning Guide
The MongoDB Connector Settings dialog displays.
Figure 4-44. The MongoDB Connection dialog.
2. Enter a valid username and password and then click Connect.
3. Once a connection to the database has been established, select a database and
collection from the drop-down boxes provided. The columns associated with your
selection display in the JSON list box.
4. Create a new column if you wish by clicking the Add Column button and entering the
necessary information. Alternatively, you may click the Generate Columns button. Doing
so launches a query of the first few database record and builds a table schema from the
available information.
5. Specify query options if you wish.
6. Click OK.
CONNECTING TO GOOGLE ANALYTICS
The following steps describe how to connect to data stored in Google Analytics.
Steps:
1. From the Preview Data window, select Add More Stuff.
2. In the Select Data Source to Open dialog that displays, select Google Analytics.
The Google Analytics Connection dialog displays.
Monarch 14.2 Learning Guide 63
Figure 4-45. The Google Analytics Connection dialog.
3. Provide a service account email, key file path, and profile ID and indicate the start and end
dates of the data you wish to fetch.
4. Click the Fetch Dimensions and Metrics button to populate the Categories, Dimensions,
and Metrics list boxes.
5. Select the fields you want to add to your table from these lists and then click OK.
More information on this connector may be found here.
The Google Analytics example concludes our exercise on using the Datawatch Data
Connectors. A full list of the connector dialogs available in Data Prep Studio is available here.
If you would like to save this workspace to continue working on it at a later time, you may do
so now. Otherwise, select Application Menu > Close Workspace.
64 Monarch 14.2 Learning Guide
Navigating Tables
Tables open in the Prep Data window may be easily navigated in a number of ways.
NAVIGATION CONTROLS
The following controls have been enabled in Data Prep Studio to allow quick movement
through a table.
Ctrl + Home – Go to first cell of the first row of the table
Ctrl + End – Go to the last cell of the last row of the table
Ctrl + Right arrow – Go to right-most cell in the current row
Ctrl + Left arrow – Go to the left-most cell in the current row
SEARCHING FOR DATA
Figure 4-46. Searching for data in a table.
You can search for specific data in tables by using the Search field located at the bottom of
your workspace. Enter the string you would like to search for and then either press Enter on
your keyboard or click the Search for Next button located to the right of this field. The
first cell including the string you are searching for is highlighted. Keep pressing Enter or
Monarch 14.2 Learning Guide 65
clicking the Search for Next button to locate all instances of this string. To reverse the
search direction, click on the Search for Previous button.
WORKING WITH ROWS
The row number of any given table is provided at the bottom of your workspace. To go to a
specific row in your table, simply enter the row number you wish in the row field provided. To
go to the first and last rows of the table, click on the First Row and Last Row
buttons, respectively. The Previous Page and Next Page buttons take you to views
of previous and next pages of data, respectively.
Figure 4-47. Viewing the row count of the Accounting table of Beantown.mdb.
You can increase row heights by selecting the Row Height button to fit multi-line data into
your rows.
SHOWING NULLS AND WHITESPACE CHARACTERS
Two buttons, which appear at the lower right-hand corner of the Prep Data window, have been
enabled in Data Prep Studio to help you visualize your data better. The Show Nulls
button helps you see null values (i.e., empty fields). Clicking on the Show Whitespace
Characters button displays spaces, tab indents, and line breaks between text characters;
these whitespace indicators display as •, , and , respectively.
DOCKING/UNDOCKING TABLES
Docking a table helps you view multiple tables at once or move them to a different location,
for example, a second monitor. To undock a table, right click on it from the Prep Data window
table selector and then select Undock Table from the options that display. You can also just
drag the table anywhere on your screen. This action undocks the table and allows you to move
it anywhere you wish. To dock a table, select the Dock Table icon that appears on the
upper right corner of an undocked table. This table is returned to the main interface.
66 Monarch 14.2 Learning Guide
Loading a Workspace
Let’s load the workspace we saved as Workspace1 to continue with our tutorial.
Steps:
1. Select Application Menu > Open Workspace.
2. In the Open dialog that displays, go to the folder in which you saved Workspace1, select
this file, and then click Open.
Figure 4-48. Loading a saved Workspace into Data Prep Studio.
All of the tables you loaded into Data Prep Studio, joins and appends you created, and
transform operations you performed are displayed.
Figure 4-49. All transformations to tables saved into a Workspace file are automatically
performed when the file is loaded into Data Prep Studio.
Monarch 14.2 Learning Guide 67
NOTE
When a saved workspace is opened, Data Prep Studio attempts to
load all of the tables added to the workspace using the file paths
provided in a previous session. But what if the paths to these
tables are changed or the table are renamed? Learn more about
file reconciliation here.
Joining Data in a Data Prep Session
Data Prep Studio allows the following join types:
Left outer join
A left outer join retains all of the rows of the “left” table, regardless of whether or not
there is a row that matches on the “right” table.
Right outer join
A right outer join retains all of the rows of the “right” table, regardless of whether or not
there is a row that matches on the “left” table.
Full outer join
The full outer join returns all rows from the left and right tables. The full outer join
combines the results of both left and right joins.
Inner join
An inner join yields the intersection of two tables, i.e., only the rows they have in
common.
These joins essentially allow you to create new tables from preexisting ones so that you can
get more information out of your data and more detail into future visualization or analytics
work.
But what if you wanted more information on the data that can’t be joined? In this case,
negative joins are what you will need to generate. You can create a left negative join, which
shows all rows from the right table for which the join keys do not match, or a right negative
join, which shows all rows from the left table without matching join keys. More information on
this topic is available here.
In this exercise, we will join data from two tables. Following the steps outlined in Previewing
and Adding Data to a Data Prep Session – Adding Data from a Database, load the table in
Payroll.mdb into Data Prep Studio.
Steps:
1. Select Combine Data from the Data Prep Studio toolbar to launch the Combine Data
stage.
68 Monarch 14.2 Learning Guide
All of the tables you loaded are displayed on the table list to the left of the screen. Note
that the table Compensation must display at the bottom of this list if you successfully
opened Payroll.mdb in Data Prep Studio.
2. Drag Accounting from the table list and into the first Drag a Table box. This table will be
the “left” table of the join.
3. Drag Compensation from the table list and into the second Drag a Table box. This table
will be the “right” table of the join.
4. Select the Click to Join button that displays on top of these tables.
The Join Configuration dialog displays.
Figure 4-50. The resulting Join Configuration dialog.
In this exercise, we will join the two tables via an inner join (i.e., we only want to see rows
where key fields from both tables match).
5. Select the icon, which signifies an inner join, from the available icons located at the
bottom of the Join Name field in the Join Configuration dialog.
6. Select Emp_ID as the key field for the Accounting table.
7. Select Emp_ID as the key field for the Compensation table.
8. If you wish to take advantage of Data Prep Studio’s fuzzy matching function, tick the Use
Fuzzy Matching box and then set an accuracy threshold using the slider that displays.
9. Select OK.
The resulting table is created and added to the table list. A preview of the table resulting
from this join also displays in the Combine Data workspace.
Monarch 14.2 Learning Guide 69
In the Prep Data window, the joined table is named, by default, Join. Succeeding joins will
be named Join(1), Join(2), and so on. These tables can be renamed to whatever you
wish.
Figure 4-51. The results of our join operation.
10. Selecting the Prep Data button on the toolbar shows the contents of this new table.
Figure 4-52. The newly defined table in the Prep Data window.
70 Monarch 14.2 Learning Guide
ANALYZING JOINS
Data Prep Studio includes Join Analysis functionality to help you decide which joins will likely
produce the best results. This functionality may be accessed by selecting the Show Join
Analysis button on the upper right-hand corner of the Combine Data stage BEFORE Step 4 of
the procedure described above.
Alternatively, if you have completed Step 4 of the join procedure, click Recommend Keys in
the Join Configuration dialog.
The Join Analysis dialog that displays provides suggestions as to which join fields may be
combined to yield the most number of results.
Figure 4-53. The Join Analysis dialog.
Hovering over each of the boxes in the dialog displays more information about the suggested
join.
Figure 4-54. Viewing information of the suggested join.
Monarch 14.2 Learning Guide 71
If you are satisfied with the projected join result, simply click on the box corresponding to the
proposed join. The Join Configuration dialog displays and you can proceed with Step 6 of the
procedure described above.
CREATING A NEW JOIN
If you would like to create a new join after completing a previous one, select the Create New
Join button located on the upper left-hand corner of the Combine Data Stage. Doing so
removes all tables already placed in the joining stage, as well as the previous join defined. You
can now begin creating a new join.
USING FUZZY JOINS
When joining data, Data Prep Studio makes an allowance for potential errors in spelling that
would result in a mismatch even when the keys are highly similar (e.g., "bond" vs. "bund").
Such issues are addressed by using fuzzy joins.
Fuzzy joining is activated by ticking the Use Fuzzy Matching checkbox located near the
bottom of the Join Configuration dialog.
Figure 4-55. Activating Fuzzy Matching in the Join Configuration dialog.
72 Monarch 14.2 Learning Guide
The options that display when fuzzy matching is employed allow you to specify an accuracy
threshold for matching and whether or not to include columns detailing the fuzzy matching
results in the table obtained from the join.
Note that join keys MUST be defined before fuzzy matching can be accomplished.
More information on fuzzy matching may be found here.
Appending Data in the Preview Data
Window
Tables with the same fields may be appended via a single click of a button. To do so, simply
select the tables you wish to append from the Preview Data Window.
Figure 4-56. Specifying tables to append in the Prep Data window.
If the tables you selected can be appended, Data Prep Studio activates the Append Selected
Tables button. Click this button.
Monarch 14.2 Learning Guide 73
Figure 4-57. The appended tables display in the Prep Data window.
In the Prep Data window, the appended table is named, by default, Append. Succeeding
appends will be named Append(1), Append(2), and so on. These tables can be renamed to
whatever you wish.
Tables cannot be appended if their fields are different.
Figure 4-58. Tables with different fields cannot be appended.
74 Monarch 14.2 Learning Guide
Appending Data in the Prep Data
Window
In previous lessons, you learned to append similar tables by selecting these tables in the
Preview Data Window and then clicking the Append Selected Tables button. In many
instances, this simple operation is adequate. But what happens if the tables you wish to
append don’t match exactly (e.g., two tables contain the information you want to append but
one table contains 1 field more than the other)?
Data Prep Studio addresses this problem by implementing more complex append operations
from the Prep Data Window
APPENDING DATA VIA THE CONTEXT MENU
For this exercise, open and load the tables of Beantown.mdb.
Steps:
1. Using CTRL+click, select the tables Accounting, Data Processing, and Marketing in
this order.
2. Right-click on your mouse and then select Append Tables >.
You may opt to:
Strict append
This option appends all tables based on name and type.
Match columns on order, name, and type
This option appends your tables first according to the order in which you selected
them, then according to their field names, then according to their field type.
Match columns on order and name
This option appends your table first according to the order in which you selected them
and then according to their field names.
Match columns on order
This option appends your tables according to the order in which you selected them.
3. Select any of these options listed above.
A new append table is created.
Monarch 14.2 Learning Guide 75
APPENDING DATA VIA THE APPEND HELPER
The Append Helper is a wizard that can help you in customizing the append operation. Unlike
other append methods, you can "force" Data Prep Studio to match columns even if they do not
have the same location, name, and/or data type.
Figure 4-59. The original Accounting table.
For this exercise, double-click on the Last Name field header of the Accounting table and
rename it to “Surname.” Click on the drop-down button beside this header and then select
Edit/Move Column Information from the menu that displays. Using your mouse, click on
the drag-and-drop handle of Surname, drag the field, and drop it after First Name. Now we’ll
append the Department, First Name, and Last Name/Surname fields of the Accounting and
Data Processing Tables.
Steps:
1. Select the tables Accounting and Data Processing. Right-click on your mouse and then
select Open Append Helper.
Figure 4-60. The Append Helper.
76 Monarch 14.2 Learning Guide
2. In the Source Tables panel, ensure that Accounting is selected. In the Columns to Append
panel, click on Department and then select Move. Repeat this step for the First Name
and Surname fields.
These fields should appear in the Appended Table panel.
3. In the Source Tables panel, select Data Processing.
The fields of this table should display in the Columns to Append panel.
4. Select Department in the Columns to Append panel, select Department in the Appended
Table panel, and then click the Match control that displays beside Department in the
Columns to Append panel.
Figure 4-61. Matching the Department fields of two tables.
5. Repeat Step 4 to match the First Name field between the two tables.
6. Select Last Name in the Columns to Append panel, select Surname in the Appended
Table panel, and then click on the Match control that appears beside Last Name in the
Columns to Append panel. Select OK when you are finished.
Monarch 14.2 Learning Guide 77
Figure 4-62. Matching the Last Name and Surname fields.
The appended table displays.
Figure 4-63. The resulting appended table.
Note that the Surname field contains the correct data even though the contents of this field
originated from two source tables with different field names.
78 Monarch 14.2 Learning Guide
You can click on the + button that appears to the right of a matched field to view the tables
and field names that make up the match. While the list is displayed, you can click x beside a
field to remove the match.
You can also edit columns before finalizing the append operation.
For example, you can change column names or data types.
To do so, click Edit on a column on the Appended Table panel. The column's name becomes
editable. A list of available data types are also displayed, with the column's current data type
highlighted. You can either
Enter a new column name
Select a new data type
Transforming Data
A Transform dialog is launched when you click on the Data Prep Studio
toolbar. This dialog enables you to quickly change how your tables look and how your data are
grouped.
EXTRACTING NULLS AND BLANKS
To extract empty rows or rows with empty values in a table, select Transform… to launch the
Transform dialog and then click Extract Nulls and Blanks. You can:
Exclude all empty rows
Exclude rows where there is an empty value in any column
Include rows where there is an empty value in any column
PIVOTING COLUMNS
Pivoting allows you to transform column values into column headers, thereby changing the
look of your data from tall+skinny to short+wide.
For this exercise, open Classic.pdf. In the Report Discovery window, double click on the fields
Betty’s Music Store (Customer), CD (Media Qty), and Amount (Amount) and then click
Open in Data Prep Studio. Load this table so that it displays in the Prep Data window.
Replace the null values in the Media Qty column by clicking on the drop-down arrow beside
the column name and then selecting Replace > Ditto. Your table should look like the
following table.
Monarch 14.2 Learning Guide 79
Figure 4-64. The resulting table extracted from Classic.pdf.
Now we’re ready to pivot your columns.
Steps:
1. From the Prep Data window, select Transform > Pivot Column.
The Pivot Column dialog displays.
Figure 4-65. The Pivot Column dialog.
2. Click the drop-down box of the Column to Pivot field and then select Media Qty. Leave
the Grouping Type as Distinct Values.
3. Click the drop-down box of the Values Column field and then select Amount. Leave the
Operation as Sum.
4. Select OK when you are finished.
Your table should be transformed as follows.
80 Monarch 14.2 Learning Guide
Figure 4-66. The pivoted table.
UNPIVOTING COLUMNS
Unpivoting All of the Pivoted Columns of a Table
When all of the pivoted columns of a table are unpivoted, the data are returned to their
tall+skinny look.
Steps:
1. Click on the table you pivoted in the previous lesson in the Prep Data window and then
select Transform Data > Unpivot Columns.
2. In the Unpivot Columns dialog that displays, select CD, DVD, LP, SACD, and BLU and
then click OK.
Your table returns to its original form.
NOTE
If you are unpivoting all of the columns you pivoted in a table,
effectively completely reversing your change, you can also simply
click on the drop-down icon located to the right of the pivoted
table and, from the options that display, select Discard
Transform
Monarch 14.2 Learning Guide 81
Unpivoting Select Columns of a Pivoted Table
When you opt to unpivot select columns of a pivoted table, you are effectively instructing Data
Prep Studio to display the data (values) of these columns (attributes) in a left-to-right
manner but keep all other fields and their associated data in their top-bottom form.
In this case, an Attribute column, which will contain the name(s) of the column(s) you
unpivoted, and a Values column, which will contain all of the data corresponding to these
columns, are created. Depending on the number of columns you choose to unpivot, the new
table created could be much shorter than the original pivoted table.
Steps:
1. Repeat the Pivoting Columns exercise to obtain a pivoted table. Ensure that this table is
displayed in the Prep Data window.
2. From the Data Prep Studio toolbar, select Transform Data > Unpivot Columns.
The Unpivot Columns dialog displays.
Figure 4-67. The Unpivot Columns dialog.
3. Check the box for BLU and then select OK.
The newly transformed table displays as below.
Figure 4-68. The newly transformed table.
82 Monarch 14.2 Learning Guide
GROUPING DATA
The Group By transformation allows you to classify or consolidate rows that belong together
according to some common value within the row. When you consolidate the records, you can
also select column values to aggregate.
Let’s use the table in the previous exercise to group our data.
Figure 4-69. The Append Helper.
In the table above, we can group all media sales so that only the total amounts for each media
type are displayed.
Steps:
1. From the Prep Data window, select Transform > Group By.
The Group By dialog displays.
Figure 4-70. The Group By dialog.
Monarch 14.2 Learning Guide 83
2. Click the drop-down button of the Group By field and then select Media Qty. Leave the
Grouping type as Distinct Values.
3. In the Column field, select Amount. Leave the Operation as Sum. If you’d like to rename
the new grouped column, you can do so by changing the entry in the New Column Name
field.
4. Select OK when you are finished.
Your new table displays below.
Figure 4-71. The table resulting from a Group By operation.
REMOVING/SHOWING DUPLICATES
You can remove or show duplicate rows from tables by selecting Transform and, in the dialog
that displays, clicking the Remove/Show Duplicates option.
Removing Duplicates
You can remove duplicate data from a table.
Steps:
1. In the Prep Data window, view the table with duplicate rows to remove and then select
Transform > Remove/Show Duplicates. The Remove/Show Duplicates dialog displays.
84 Monarch 14.2 Learning Guide
Figure 4-72. The Remove/Show Duplicates dialog.
2. Select Remove Duplicates.
3. If you wish to remove duplicates from all columns, select Column Name and then click
OK.
4. If you wish to remove duplicates from select columns instead, untick the box for Column
Name and then tick the boxes for these columns from the column selector and then click
OK.
The resulting table displays.
Showing Duplicates
Instead of removing duplicates, you can also show rows with duplicate data as a new table.
Steps:
1. In the Prep Data window, view the table with duplicate rows to show and then select
Transform > Remove/Show Duplicates. The Remove/Show Duplicates dialog displays.
2. Select Show Duplicates.
3. If you wish to show duplicates from all columns, select Column Name and then click OK.
4. If you wish to show duplicates from select columns instead, untick the box for Column
Name and then tick the boxes for these columns from the column selector and then click
OK.
The resulting table displays.
More information on removing/showing duplicates can be found here.
Monarch 14.2 Learning Guide 85
Working with Load Plan Visualization
When you join, append, or transform tables, the resulting table displays in the table selector
of the Prep Data window as follows:
Figure 4-73. The Load Plan of a table resulting from join, append, or transform operations.
Clicking on the icon of the main table minimizes the table display as follows:
Figure 4-74. The minimized table display.
The tables that display under a joined, appended, or transformed table (i.e., the “parent”
tables) enable you to visualize which specific tables were employed to obtain the newer table
(i.e., the “child” table). This functionality is called load plan visualization.
Because joins, appends, and transforms are NOT recorded as changes, these operations will
not display in a table’s change history and you may be unable to recreate child tables easily,
especially when working with the same workspace at a later date. Load plan visualization
addresses this issue by allowing you to recreate new tables from previously existing ones:
you’ll know exactly which tables to use and what operation to apply to obtain a desired table.
Note that when pre-existing tables are combined to create new ones, the former are displayed
below the latter.
Figure 4-75. Movement of tables in a load visualization plan.
As with any other table in the Prep Data window, clicking on the drop-down icon located to the
right of child tables allows you to edit the operation and pin and refresh the table. You can
86 Monarch 14.2 Learning Guide
also rename or close the table, duplicate the table (including all other tables used to achieve
it), and discard the operation. In case of the latter, the child table disappears and only the
parent tables remain.
Working with Calculated Fields
The ability to create calculated fields is among the more important data prep operations you
can perform. Calculated fields can be created from text, date/time, and numeric fields. They
can also be edited via the Change History list.
A complete list of the functions available in the Create Calculated Field dialog, as well as their
definitions and examples, may be found here.
Note, however, that Data Prep Studio does not support redaction functions. To use these
functions on a table, switch to Classic mode, apply the necessary functions, and then open the
table in Data Prep Studio.
For this exercise, let’s compute what the unit prices of various media from the Classic.pdf
report would be if we applied a 10% discount to them.
Figure 4-76. The Create Calculated Field dialog.
Steps:
1. From the Classic.pdf report, extract the fields marked by Betty’s Music Store
(Customer), Bartok, Sonata for Solo Violin (Description), MK-42625 (Label/No.),
and 8.99 (Unt_Prc). Load this table into the Prep Data window.
Monarch 14.2 Learning Guide 87
2. Click on the drop-down button beside the Unt_Prc column and then select Create
Calculated Field.
3. In the Create Calculated Field dialog that displays, enter Discounted Prices in the New
Column Name field.
4. Double-click on Unt_Prc field in the Fields panel so that the field name appears in the
Expression box.
5. In the Operators panel, double-click on the multiplication operator. This operator appears
in the Expression box.
6. Using your mouse, click to the right of the multiplication operator in the Expression box
and then type in 0.90. Select OK when you are finished.
The Create Calculated Field dialog closes and a new column is added to your table.
7. Select the drop-down button of the Formula Field column and then click Format. In the
Format Formula Field dialog that displays, select Financial and then click OK.
Your table is rebuilt as below.
Figure 4-77. Adding a calculated field to a table.
88 Monarch 14.2 Learning Guide
Filtering Data
You can choose how much data to import from a table into your Data Prep Session by filtering
your table and selecting the data you want to view. Let’s find out how using the table we
produced from a PDF report. In this example, instead of viewing data for 20 customers, we’ll
select only 5 customers and load their data into a new table.
Steps:
1. Open Classic.pdf and then use the Auto-Define button in the Report Discovery window
to extract data from the report.
2. View the resulting table in the Prep Data window.
3. Click the drop-down button beside the Customer column and then select Apply
Filter.
The Apply Filter dialog displays.
Figure 4-78. The Apply Filter dialog.
4. Click on the Select filter type icon and, from the options that display, select
Multiple selection.
5. Under Value, select Betty’s Music Store, Big Shanty Music, Bluegrass Records, Canciones,
and Chez Rudy.
6. Select Load Selected Tables when you are finished. The resulting table displays in the
Prep Data window.
Monarch 14.2 Learning Guide 89
Figure 4-79. The table obtained from a filter operation.
Note that the data of only five customers are displayed.
Different filter operations are available for different field types. Numeric fields, for
example, may be filtered by using ranges or specifying less than or greater than values.
To filter date fields, you can specify a date range or before/after date. For text fields, you
can specify strings or select discrete values.
Preparing Data
You can prepare columns in a table to prepare them for future visualization or analytics
operations. Data Prep Studio includes pre-built functions that allow you to quickly and
consistently clean your data, turning them into analysis-ready information. Preparing data in
Data Prep Studio is as easy as clicking on the drop-down button located to the right of
each column header. Doing so displays a list of prep operations you can perform on that
column.
90 Monarch 14.2 Learning Guide
Figure 4-80. Available prep operations for Text fields in the Prep Data window.
For text fields, the following prep operations may be applied:
Sort
Case
Clean
Column
Convert
Extract
Replace
Split
For date/time and numeric fields, the following prep operations may be applied:
Sort
Format (for numeric fields)
Column
Convert
Compute (for numeric fields)
Replace
Split (for date/time fields)
Monarch 14.2 Learning Guide 91
Besides these prep operations, you can also change the field type of each of the columns in
your table (e.g., change a numeric field into a date/time field). To do so, simply click on the
field type icon found to the right of the column name and, from the options that display, select
the new field type you wish to apply.
You can select the drop-down button located to the right of each column and, from the options
that display, click Edit/Move Column Information. Doing so displays the Edit/Move Column
Information dialog, which will allow you to make even more detailed changes to your columns,
including their order, name, and type. You can also opt to hide or unhide columns by ticking or
unticking the Hide box that displays whenever a specific column is selected.
Figure 4-81. The Edit/Move Column Information dialog.
In the Prep Data window, you can select specific columns and then perform convert, merge,
remove, and copy operations or create new functions to obtain only the data you truly need.
You can also select columns from a table and create a new table from this selection. You can
even create new columns, either from scratch or from extract and replace operations, and
change the data types of these columns.
You can add metadata from Excel, delimited text, and PDF/PRN reports as new columns to
tables.
To change the table header and use values from a certain row as column names instead, click
on the number of the row you would like to use as a header, effectively highlighting it, right-
click on your mouse, and then select Set Row as Column Headers from the options that
display.
To gain access to these capabilities and more, simply click on the drop-down button located to
the right of each table column. This action displays a menu from which you can select a prep
operation.
92 Monarch 14.2 Learning Guide
The table below provides more information on the transformations you can perform in Data
Prep Studio.
USE THIS TRANSFORMATION
TO
SORT
Sort selected columns
Sort the column in ascending order
Define Sort
Launch the Sort dialog to create a new sort definition
CASE
Make Proper Case
Convert to uppercase the first letter of each word in a
string
Make Lower Case
Convert the entire string to lowercase letters
Make Upper Case
Convert the entire string to uppercase letters
CLEAN
Remove Leading & Trailing
Spaces
Remove all leading and trailing spaces from a string
Remove Consecutive Spaces
Collapse multiple consecutive spaces into one space
Remove Specified Characters
Remove the sequence of characters from the values of
the column you apply this operation to
Remove non-alphanumeric
characters
Remove all characters that are not numbers or letters
(e.g., periods, commas, other symbols) from the
selected fields
Remove numeric characters
Remove all numerals from the selected fields
COLUMN
Rename
Rename the column you selected
Duplicate
Produce a duplicate of the column you selected
Remove
Remove the column you selected
Hide
Hide the selected column
Show hidden column
Show a list of hidden columns. Selecting a column from
this list displays it in the table once more.
CONVERT
Text to Number
Convert the data type Text into Number
Text to Date/Time
Convert the data type Text into Date/Time
HH:MM:SS to Number of Secs
Convert HH:MM:SS strings into a number of seconds
Date/Time to Text
Convert the data type Date/Time to Text
Date/Time to Number
Convert the data type Date/Time to Number
Number to Text
Convert the data type Number into Text
Number to Date/Time
Convert the data type Number into Date/Time
Advanced Text to Number
Converts text fields with the values “X B,” “X M,” and “X
K,” where “X” is any number, into “X,000,000,000,”
“X,000,000,” and “X,000,” respectively.
Monarch 14.2 Learning Guide 93
USE THIS TRANSFORMATION
TO
EXTRACT
Starting from left
Extract n characters from the beginning of a string
Starting from right
Extract n characters from the end of a string
Using Position & Length
Extract n characters from the xth position from the start
Using Start & End Strings
Extract n characters starting from the start and end
positions indicated by a string of characters
FORMAT (for Date/Time fields)
Short Date
Apply the MM/DD/YYYY form of a date/time field
Long Date
Apply the Day, Date form of a date/time field
Short Date/Time
Apply the MM/DD/YYYY HH:MM AM/PM form of a
date/time field
Long Date/Time
Apply the Day, Date HH:MM AM/PM form of a date/time
field
Time
Apply the HH:MM AM/PM form of a date/time field
Custom
Specify a custom form (e.g., year, month, day, hour,
minute, etc.) for the date/time field
FORMAT (for Number fields)
Number
Format the number with a thousands separator and two
decimal places
Financial
Format the number with thousands separator and two
decimal places. Negatives are enclosed in parenthesis.
Currency
Format the number with thousands separator and two
decimal places. Adds a currency, and negatives are
enclosed in parenthesis.
Percent
Multiply the number by 100 and add a % sign
Scientific
Format the number in exponential form
Custom
Specify a custom form (e.g., 000,000, #0.0E0, #
‘degrees,’ etc.) for the number field
REPLACE
Using Position & Length
Replace strings of a certain length and located at a
certain start position with another string
Using Find & Replace
Replace specific strings with another string
Ditto
Copy non-null values down to fill null values in
subsequent rows in a column
Blank Values
Replace blank values with a specific string
Nulls
Remove null values and replace with a specific string
SPLIT
Into parts from the Left
Start from the left and split a column into two or more
columns, depending on a separator you define. The
separator may be a single character or substring
94 Monarch 14.2 Learning Guide
USE THIS TRANSFORMATION
TO
Into Parts from the Right
Start from the right and split a column into two or more
columns, depending on a separator you define
Names Into Parts
Split the value of a name column into its name parts.
Addresses Into Parts
Split the value of a single address column into its
component address or postal parts
Date/Time into Parts
Split a date/time column into two or more parts
according to the date/time components you select.
Parts include Year, Quarter, Month, Day, Hour, Minute,
Second, Date, Time, DayofYear, HalfYear, and Week.
Date/Time into ISO 8601 Parts
Split a date/time field into two or columns according to
the ISO 8601 date/time components you select
Date/Time into Fiscal Parts
Split a date/time field into two or more columns
according to the fiscal parts selected
COMPUTE
Round with Precision…
Round numbers to a specified number of places to the
right (or left) of the decimal point
Round Up (Ceiling)
Round numbers up to zero decimal places
Round Down (Floor)
Round numbers down to zero decimal places
Remove Negative Signs (Abs)
Return the absolute values of numbers
Remove Decimals (Int)
Return the integer portions of numbers
Calculator
Creates a calculated field using other numeric fields in
the table
Let’s transform the Hire Date column of the table Join so that only the hire date and not the
hire time displays. In this exercise, we will also transform the Salary and Bonus fields so that
the values in these fields resemble monetary values more closely.
Steps:
1. In the Prep Data window, load the Join table.
2. Click on the drop-down button located to the right of the Hire Date column header.
3. From the menu that displays, select Format.
4. From the Format Hire Date dialog that displays, select Short Date.
Monarch 14.2 Learning Guide 95
Figure 4-82. The Format Hire Date dialog.2
5. Select OK when you are finished
The new column displaying only the hire date (without the hire time) displays. Note that
the column header has been changed to reflect at least part of the transformation we
performed.
Figure 4-83. The transformed Hire Date column of the Join (Accounting + Compensation)
table in the Prep Data window.
96 Monarch 14.2 Learning Guide
6. Click on the drop-down button located to the right of the Salary column header and,
from the menu that displays, select Format.
7. In the Format Salary dialog that displays, select Currency and then click OK.
8. Perform Steps 5 and 6 to transform the Bonus field.
Your table should now look as below.
Figure 4-84. The completely transformed Join (Accounting + Compensation) table in the
Prep Data window.
Tracking Changes
A change history is the record of all changes that have been made to a table. This list can be
reapplied to any table that has columns of the same names and data types in the same order.
Change histories may be imported and exported and applied to tables similar to that from
which the imported/exported change history was created.
Data Prep Studio features a nifty tool through which you can track your data transformations
in a stepwise manner. This tool is called Change History and is found as a panel on the right-
hand side of the Prep Data window.
Figure 4-85. Accessing the Change History panel.
Monarch 14.2 Learning Guide 97
This panel shows each of the changes we performed in the previous exercise.
If you want to undo an operation, select the Undo Change button beside that operation.
If you’d like to edit it, select the Edit button.
Note that selecting Undo for one operation will also undo all operations after it and that the
Change History functionality is only available in the Prep Data window.
A complete discussion on Change History is provided here.
APPLYING A CHANGE LIST TO A DIFFERENT TABLE
You can drag and drop single changes (using the header of a change) or whole change lists
(using the drag-and-drop handle of the Change History panel) to other tables. When you do
so, each of the changes specified in the change list are automatically applied in sequence to
the new table if the latter has no change history.
Figure 4-86. The drag-and-drop handle of the Change History panel.
In Figure 4-87, the change list of the table Join (Accounting + Compensation) has been
dragged to the table Join(1) (Data Processing + Compensation). Assuming that both tables
contain the same fields but the latter has a different change list, you are asked if you would
like to replace the Change History of the latter table.
98 Monarch 14.2 Learning Guide
Figure 4-87. The same change list created from the Join (Accounting + Compensation) table
was dragged and dropped into the Data Processing table.
Selecting Yes in the Change List Drop dialog applies the exact same changes in the former
table to the latter one. If a field is hidden and some change must be applied to it, the hidden
field is displayed and the change operations are completed.
If you select No, hidden fields are not displayed but changes to them are still applied.
In another example below, the Change History of the Join table (Accounting + Compensation)
has been dragged and dropped to the table Marketing.
Figure 4-88. The change list created from the Join (Accounting + Compensation) table was
dragged and dropped to the Marketing table.
Monarch 14.2 Learning Guide 99
Because the Marketing table does not contain Salary or Bonus fields, the error shown in Figure
4-88 displays.
EXPORTING A CHANGE HISTORY LIST
Change History lists may be exported for use in a similar table in another Data Prep Studio
session.
Steps:
1. Select Application Menu > Change Lists > Export Change List.
Alternatively, you can select the Export Change List icon on the Change History
panel.
2. In the Save As dialog that displays, use the File Location drop-down to navigate to the
folder in which you want to save the exported change list and then enter ExportChange1
into the File name field.
3. Click Save when you are done.
The export list is saved into the nominated folder with the extension .dpcl.
IMPORTING A CHANGE HISTORY LIST
Importing Change History lists allows you to quickly and easily apply changes made to a
previous table to a similar table.
Steps:
1. Following the steps outlined in Joining Data in a Data Prep Session, join the tables
Marketing and Compensation using an inner join and Emp ID as the key.
100 Monarch 14.2 Learning Guide
The following table displays.
Figure 4-89. The results of joining tables Marketing and Compensation.
2. Select Application Menu > Change Lists > Import Change List.
Alternatively, you can select Import Change List icon on the Change History panel.
3. Using the Open dialog that displays, navigate to the folder in which you stored
ExportChange1, select this file and then click Open.
The Hire Date, Salary, and Bonus fields of the joined table immediately reflect changes.
Figure 4-90. The results of our Import Change List operation.
Monarch 14.2 Learning Guide 101
Viewing the Change List history of the joined table yields the following results:
Figure 4-91. The Change List History of the newly joined Marketing and Compensation
tables.
MODIFYING ITEMS IN A CHANGE LIST
Now that you know that you can reverse changes you have applied to your tables by using the
Change History functionality, what happens if you simply want to modify – not reverse – a
change, such as a formula field? You can modify changes such as filters and calculated field
definitions by clicking on the Edit icon that displays to the right of a change in the Change
History list. Note that if this icon does not appear, the change cannot be modified.
Clicking the Edit icon launches the relevant dialog to enable you to modify your change. If
you were to modify the calculated field you added to your table in the previous exercise, for
example, you would click on the edit button in the change marked
in your Change History list. Doing so will launch the Update Calculated Field dialog.
102 Monarch 14.2 Learning Guide
Figure 4-92. Clicking the Edit button that appears on a change in the Change History list
launches the relevant dialog.
CREATING CUSTOM CHANGE LISTS
Custom change lists apply to a single column of a single data type at a time, similar essentially
to a user-defined function. Users can drag any set of changes that apply to a single data type,
e.g., text, numeric, date, and then save them with a name for reuse within a workspace.
When the changes are applied to one or more columns on a table they will be recorded in the
change history.
Steps:
1. Select the Edit or Create Custom Change List button from the Change History toolbar.
You can also select the drop-down handle of any column in the Prep Data window and then
select Custom Change.
The Custom Change dialog box displays.
Monarch 14.2 Learning Guide 103
Figure 4-93. The Custom Change dialog.
2. Drag change history items from an existing Change History list to the Custom Change
dialog box.
3. Enter a name for the custom change list in the box provided.
The list is automatically saved and may be applied to a compatible column.
More information on creating custom change lists may be found here.
Exporting Data
You can export tables in Data Prep Studio. Note that you can only export data from the Prep
Data Window. The following export types are supported in Data Prep Studio:
Microsoft Excel (.xlsx, .xlsm, .xls)
Delimited text file (.csv)
Monarch Swarm
Datawatch Designer (.xlsx)
IBM Watson Analytics
IBM Cognos Analytics
Microsoft Access (.accdb, .mdb)
Microsoft Power BI
Tableau Server/Tableau data extract file (.tde)
QlikView (.qvx)
104 Monarch 14.2 Learning Guide
EXPORTING TO CSV, MICROSOFT EXCEL, MICROSOFT ACCESS,
DATAWATCH DESIGNER, QLIK, AND TABLEAU FILES
Steps:
1. From the Prep Data window, select the table Join(1) (Data Processing +
Compensation) and then click Export Data on the Prep Data toolbar.
A Select an Export Type dialog displays.
Figure 4-94. The Select an Export Type dialog of Data Prep Studio.
2. Select Microsoft Excel to display the Microsoft Excel Export dialog.
Figure 4-95. The Microsoft Excel Export dialog.
3. Use the Browse button to navigate to the folder into which you want to save your export.
Note that Data Prep Studio will remember the export file path that you use so that you do
not have to enter or select this each time you export to the same file type.
4. Specify a filename to which your export should be saved.
Monarch 14.2 Learning Guide 105
5. Tick the Advanced button to specify how exports to an existing filename or table must be
handled (i.e., append, overwrite, skip).
6. Select OK.
A message box indicating successful export appears.
Figure 4-96. The Export Complete message box.
7. Click OK to close this box.
When you export to a Datawatch Designer data source, the message box returned after
completion of the export operation includes a link that will allow you to open Datawatch
Designer and immediately use the exported table as a data source. Note that you must
have Datawatch Designer installed to take advantage of this functionality.
EXPORTING TO IBM WATSON ANALYTICS
Watson Analytics is IBM’s cloud-based service that streamlines the data-analysis process for
most business users. Using this service, analysts can upload their data and, among other
activities, start exploring the same by typing in questions into or selecting suggested
questions from a simple dialog box.
Figure 4-97. Starting an exploration of the table “Data Processing + Compensation.”
106 Monarch 14.2 Learning Guide
Answers to these questions display in a panel below the question field. When an answer is
selected, the related visualization displays.
Figure 4-98. The results of a Watson Analytics exploration.
Exporting to Watson Analytics is only possible through Monarch Complete and Monarch for IBM
Analytics. Note also that Internet Explorer 11 must be installed in your computer to run this
application correctly. More information on this requirement can be found here.
Some information is necessary to successfully export and work with your data in IBM Watson
Analytics:
A correctly set up Watson Analytics service
IBM ID – Your IBM login ID
Password – Your password
Steps:
1. Continuing from your tables in the previous exercise, export the table Join(1) (Data
Processing + Compensation) to IBM Watson Analytics.
Figure 4-99. Exporting to Watson Analytics.
2. Enter your IBM login information into the dialog that displays.
3. Select Sign In when you are finished.
Monarch 14.2 Learning Guide 107
Figure 4-100. Logging into the IBM Watson Analytics service.
NOTES
You will need to provide your IBM login information only once per
Data Prep Studio session. Subsequent exports to the same service
will use the information you provided upon first export to Watson
Analytics.
Exports to Watson Analytics feature a 100-character limit, which
means text fields with over 100 characters will be truncated. This
limitation is a known issue in Watson Analytics and is implemented
to maintain performance.
Also, different account types present different restrictions when
opening exported files in IBM Watson Analytics:
Free Edition - Can open files with a maximum of 100,000 rows
and 50 columns; file size should be no larger than 500 MB.
Overall capacity for all data sets and analyses is 500 MB.
Personal Edition - Can open files with a maximum of
1,000,000 rows and 256 columns; file size should be no larger
than 2 GB. Overall capacity for all data sets and analyses is 2
GB.
Professional Edition - Can open files with a maximum of
10,000,000 rows and 512 rows; file size should be no larger
than 4 GB. Overall capacity for all data sets and analyses is 4
GB.
108 Monarch 14.2 Learning Guide
The export operation is executed and the Export Complete message box displays.
At this point, you can use your browser to access your Watson Analytics service and view
your exported table. You’ll be able to begin exploration of your data as soon as you select
a data set to work with.
Figure 4-101. Logging into the IBM Watson Analytics service.
EXPORTING TO IBM COGNOS ANALYTICS
Cognos Analytics is a cloud-based application developed by IBM that allows sharing of data
and information across workgroups as well as create and/or personalize dashboards and
reports on various types of devices.
Figure 4-102. The Welcome page of Cognos Analytics.
Exporting to Cognos Analytics is only possible through Monarch Complete and Monarch for IBM
Analytics.
Monarch 14.2 Learning Guide 109
Some information is necessary to successfully export and work with your data in IBM Watson
Analytics:
A correctly set up Cognos Analytics service
Service root URI – The URL of your Cognos Analytics service. This URL must be entered
into the field provided in the Server tab of the Settings dialog of Data Prep Studio.
Namespace – The security connection type employed for the service
Username
Password
Steps:
1. Go to the Server tab of the Settings dialog and enter the URL of the Cognos Analytics
Service you are connecting to. Select OK to save your changes.
2. Continuing from your tables in the previous exercise, export the table Data Processing +
Compensation to IBM Cognos Analytics.
3. Enter the necessary information into the dialog that displays.
Figure 4-103. Entering Cognos Analytics connection information.
NOTE
You will need to provide your login information only once per Data
Prep Studio session. Subsequent exports to the same service will
use the information you provided upon first export to Cognos
Analytics.
4. Select OK when you are finished.
110 Monarch 14.2 Learning Guide
The table you exported should display in My Content when you log into your Cognos
Analytics service.
Figure 4-104. The exported table displays in My Content.
EXPORTING DATA TO MICROSOFT POWER BI
Data Prep Studio now supports exports to Power BI by creating files that are automatically
placed in your Power BI workspace provided that you have a Microsoft Power BI account. Note
that you must have a Microsoft Power BI account to execute exports of this type successfully.
If you do not have one yet, you can sign up for an account here.
Steps:
1. Select a table to export to Microsoft Power BI.
2. From the Data Prep Studio toolbar, select Export > Microsoft Power BI.
The dialog in Figure 4-105 displays.
Monarch 14.2 Learning Guide 111
Figure 4-105. The PowerBI Export login dialog.
3. Enter a valid email or phone number and password into the fields provided in this dialog.
Select Sign in when you are finished.
The Authorize PowerBI Export dialog displays for first-time users.
Figure 4-106. The exported table displays in My Content.
112 Monarch 14.2 Learning Guide
4. Click Accept.
The export operation is performed and the table is added as a new dataset in your
Microsoft Power BI account.
Figure 4-107. The results of the current export operation.
EXPORTING DATA TO MONARCH SWARM
Monarch Swarm is a self-service data preparation and analytics platform that accelerates the
exploration and preparation of data, and delivers timely data discovery through automation
and distribution across an organization.
Workspaces and data sources may be exported to the Monarch Swarm Library.
To do so, select Export Data > Monarch Swarm.
The Monarch Swarm Export dialog displays. Confirm the details of the indicated Monarch
Swarm Server, modify the table/workspace name as you wish, and then click OK to complete
the export operation.
More details on Monarch Swarm may be found here.
Monarch 14.2 Learning Guide 113
EXPORTING TO TABLEAU SERVER
Data Prep Studio allows you to export to or update existing worksheets in Tableau Server.
However, to be able to do so successfully, you need to have installed the Tableau TABCMD
program first. Click here to download and learn more about the utility.
To export data to Tableau Server, follow the steps outlined below.
Steps:
1. Select the table you wish to export and then click Export Data from the Data Prep Studio
toolbar.
2. In the Export dialog that displays, click Tableau Server. The Tableau Server Export dialog
displays.
Figure 4-108. The Tableau Server Export dialog.
3. Enter all of the required information and then select OK.
More information on exporting to Tableau Server may be found here.
114 Monarch 14.2 Learning Guide
Switching from Data Prep Studio to
Monarch
To access Monarch from Data Prep Studio, select Application Menu > Switch to
Classic Mode and then choose Classic Mode. When launching Monarch Complete for the first
time, the Choose Startup View dialog displays.
Figure 4-109. The Choose Startup View dialog.
The Home screen of Monarch displays.
Figure 4-110. Monarch Classic’s Home screen.
When you specify Classic Mode as the startup view, sessions after the current one will
automatically display the Monach Classic view when the application is run. Conversely, if you
specify Data Prep Studio as the startup view, all sessions after the current one will
automatically display the Data Prep Studio view when the application is run.
If you wish to work with Data Prep Studio when the startup view is Monarch Classic, click the
Data Prep Studio button on the Home ribbon.
Monarch 14.2 Learning Guide 115
NOTE
You may also switch to Monarch Classic mode when in the Report
Discovery window. While defining fields in Report Discovery mode,
you may wish to open the report in Monarch to take advantage of
Monarch’s more-advanced field-capture abilities. To do so, click
the Edit in Classic Mode located on the toolbar of the Report
Discovery window.
Congratulations! You have just completed the Data Prep Studio lessons. We hope you have
found them to be a helpful introduction to Data Prep Studio’s capabilities.
For additional information not covered by this tutorial, we suggest you thoroughly explore
Data Prep Studio’s help system. To do so, simply click the following link:
http://docs.datawatch.com/dps/help/desktop/About_Monarch_Data_Prep_Studio.htm
More documentation on Data Prep Studio is also available on http://docs.datawatch.com/dps/.
The previous lessons reveal that users who wish to open database tables quickly, clean them,
and transform them for further application, such as in data visualization, may find Data Prep
Studio a great solution to their needs. However, while data extraction from reports and export
capabilities are also provided in Data Prep Studio, these features may not be adequate when
you want to extract data from some file types, such as .TXT, or export to a specific format not
supported by Data Prep Studio. To do these, you will need to work with Monarch Classic.
116 Monarch 14.2 Learning Guide
[5] Monarch Table Extractor
Lessons
Monarch Table Extractor is designed for ordinary people who wish to do extraordinary things
with PDF files.
Available only with a Monarch Complete with Table Extractor license, the application allows
you to identify tables in text-heavy PDF files, select them, modify them if you wish, and then
export them to Data Prep Studio for further data preparation.
Figure 5-1. A typical Monarch Table Extractor workflow.
Monarch Table Extractor provides easy-to-use interfaces to perform the following:
Automatically look for and define tables in a document
within a page,
in all pages, or
in specific pages.
Modify tables that have been defined (e.g., modify headers, rename columns, and delete
columns)
Create a custom table
Export tables to Monarch Data Prep Studio
The lessons outlined in this tutorial provide a very brief overview of what you can do with
Monarch Table Extractor. To learn more about this application, click here.
Monarch 14.2 Learning Guide 117
Launching Monarch Table Extractor
Launch Monarch Table Extractor by selecting Start > All Apps > Datawatch Monarch 14 > >
Datawatch Monarch Table Extractor.
The Datawatch Monarch Table Extractor start page displays.
Figure 5-2. The Monarch Table Extractor Start page.
Opening a Document
The easiest way to open and work with a document is to drag it to the Monarch Table
Extractor interface.
The following typical example describes how to open a pdf document and define tables, using
a file named Canaccord 042215.pdf. If you do not have access to this document now, simply
follow along with this tutorial.
Steps:
1. Navigate to the location of Canaccord 042215.pdf.
2. Drag Canaccord 042215.pdf to the Monarch Table Extractor start page.
118 Monarch 14.2 Learning Guide
Figure 5-3. Starting a Monarch Table Extractor session.
Canaccord 042215.pdf is loaded into Monarch Table Extractor.
Figure 5-4. The loaded document.
You can also do the following:
Select the Open PDF button on the Start page, and use the standard File Open dialog box
to locate your document and open it.
Drag
Here
Document Page Preview displays
the current page of a document.
Use this window to view a page and
its tables.
Page Thumbnails display
thumbnails (i.e., small versions of
the document pages) that you can
use to easily look for a specific
page.
Monarch 14.2 Learning Guide 119
Select Open from the Application Menu, and use the standard File Open dialog box to
locate your document and open it.
Figure 5-5. Monarch Table Extractor’s Application menu .
Defining Tables
Define one or all tables by selecting any one of the following from the Table Actions panel:
Auto Define Page
Find All Tables
Find Tables from Pages
For this session, let us auto-define a page. In this section, we will auto-define tables, verify
the tables, and delete one of them.
AUTO-DEFINING A PAGE
Select Auto Define Page to ask Monarch Table Extractor to look for and then automatically
define all tables in the current page.
Steps:
1. While Page 1 of Canaccord 042215.pdf is displayed, select Find Tables > Auto Define
Page from the Table Actions panel.
Figure 5-6. Auto-defining a page.
Table Actions
Auto Define Page
120 Monarch 14.2 Learning Guide
Monarch Table Extractor looks for data arranged in a tabular structure and converts these
into actionable tables.
Figure 5-7. Locating all tables in a PDF file.
VIEWING AND MODIFYING A TABLE
Data from the tables are displayed on the table panel.
In addition, a Page/Table list shows the page where a table is located and all the tables
defined for that page.
Steps:
1. Select Page 1 – Table 3 on the Page/Table List.
The table is highlighted on the document, and the values for the table are displayed on the
Table panel.
Expandable/collapsible
Page/Table List allows you to
select a table from a specific page.
Table Panel Menu displays
actions you can carry out on
tables.
Table panel
Data Preview Grid displays a
preview of extracted table data.
Monarch 14.2 Learning Guide 121
Figure 5-8. Selecting a specific table in Monarch Table Extractor.
On the document, the table looks like this:
Figure 5-9. A selected table in Monarch Table Extractor.
The table is surrounded by a dashed border with handles. Headers (which may be
separate from the table) are highlighted in pink. Columns are highlighted in blue.
2. Do one of the following to modify a table or its elements:
Right click on a table, header, or column to display context menu items.
Figure 5-10. Selecting a table to modify.
Click between columns to select a table a table.
122 Monarch 14.2 Learning Guide
Select a table, and then click on a column, to select the column.
Select a table, and then click on a header, to select the header.
Select specific text via drag-and-drop to display additional context menu items as well.
Figure 5-11. Selecting specific text via drag and drop.
The context menu items that display will allow you to
create a new column,
delete columns,
exclude values from a table,
combine tables,
define a table title,
modify headers, and
delete spaces from header and table values.
Monarch 14.2 Learning Guide 123
DELETING A TABLE
You may not need all the tables defined by Monarch Table Extractor. You can easily delete
tables via the context menu or the Table panel menu.
Steps:
1. Select Page 1 – Table 1 on the Page/Table List.
This table is highlighted:
Figure 5-12. Selecting a table to delete.
Although this looks like a table, the values are not related as columns or rows. We can
delete this table.
2. Right-click on Page 1 – Table 1 on the Page/Table List.
A context menu displays.
Figure 5-13. Deleting the selected table.
3. Select Delete Table from the context menu.
Exporting Tables
After defining your tables, you can export them to Monarch Data Prep Studio. Once the tables
are exported and loaded, you can work on them using Monarch Data Prep Studio's features.
Note that you MUST manually launch both Monarch Table Extractor and Data Prep Studio to
enable successful export.
124 Monarch 14.2 Learning Guide
Steps
1. Right-click on Page 1 – Table 3 on the Page/Table list.
A context menu displays.
Figure 5-14. Exporting a table in Monarch Table Extractor to Data Prep Studio.
2. Select Export Table to DPS.
Alternatively, you can select the table on the Table Panel, and then select Export Table
to DPS on the Table Panel Menu.
Figure 5-15. An alternative method to export tables to Data Prep Studio.
Monarch 14.2 Learning Guide 125
[6] Monarch Lessons
This chapter introduces you to the Monarch lessons.
These lessons are designed to quickly acquaint you with basic program operations. For
additional material not covered in this guide, consult the Monarch Help file by selecting File,
clicking on the drop-down button of the Help menu, and then selecting Help Topics from
the options that display.
Chapter 7 – Working in Report View
Shows you how to load a report file, navigate the report on screen, look up information in
the report, copy selected data to other applications, and print pages from reports.
Chapter 8 – Extracting Data from a Report
Shows you how to create a template to extract data from a simple columnar report, view
the data in the Table view, and save the template to a Monarch model file.
Chapter 9 – Special Data Extraction Techniques
Deals with special problems caused by address blocks, fields that run together in a report,
and data presented in multiple columns.
Chapter 10 – Working in Table View
Shows you how to load and navigate through a table, format fields, create headers and
footers, print table data, and copy data to other applications.
Chapter 11 – Working with Sorts
Shows you how to sort the table, create multiple sort definitions, select a sort definition,
and save sort definitions.
Chapter 12 – Working with Filters
Shows you how to use a filter to select records based on any field value, create multiple
filters, utilize value-based filters, select an active filter, and save filter definitions.
Chapter 13 – Working with Calculated Fields
Shows you how to create calculated fields to add information to the table database.
Chapter 14 – Working with Multiple Instances of a Report
Shows you how data extracted from multiple reports may be sorted and analyzed in Table
view.
Chapter 15 – Extracting Multiple Line Fields
Shows you how to extract a multiple line text block from a report. It also introduces memo
fields, which are used to hold the contents of a multiple line field, and covers exporting
and printing memo fields.
126 Monarch 14.2 Learning Guide
Chapter 16 – Summaries
Teaches you how to create a summary report from your Monarch data, specify summary
design preferences, and create quick summaries.
Chapter 17 – Advanced Summary Capabilities
Shows you how to reveal even more information about your data via Monarch’s advanced
summary features.
Chapter 18 – Exporting Operations
Discusses how to export reports, tables, and summaries.
Chapter 19 – Importing Data from HTML and External Databases
Discusses how to import data from various external sources.
Chapter 20 – Creating External Lookups
Discusses how external lookups are performed in Monarch.
Chapter 21 – Using Digital Signatures
Discusses how digital signatures are added to some exports in Monarch and viewed in
Microsoft Excel.
Chapter 22 - Using Pivot Tables
Discusses how pivot tables are added to some exports in Monarch and viewed in Microsoft
Excel.
Chapter 23 – Working with Asian (CJK) Character Sets
Discusses how to work with documents using Asian character sets
Chapter 24 – Using Monarch Utility
Discusses how to use Monarch Utility to perform a number of functions.
Installing Lesson Files
When installing Monarch, the last screen of the Monarch Setup program provides a checkbox
that asks whether to install the lesson files.
When this box is ticked, the Setup program will install the lesson files in the appropriate
folders under the Datawatch Monarch folder in the Public Documents area.
Monarch 14.2 Learning Guide 127
The Options Interface
The Options interface allows users to configure several universal Monarch settings, including
Folders, input information, views, and exports, among others.
Figure 6-1. The Options window.
Clicking specific buttons on the Options ribbon exposes settings you can modify. When you
have modified the settings as you wish, simply click Accept to accept your changes and
close the interface. You may specify several options before selecting Accept . Select
Cancel to disregard any changes you made and close the interface. In this case, none of
the previous settings are modified.
The following table summarizes the functions of each of the buttons in the Options ribbon.
Use this button…
To…
Accept
Accept the changes you made and close the
interface
Cancel
Cancel the changes you made and close the
interface
Folders
Expose folder settings
Input
Expose input settings
View
Expose view settings
Export
Expose export settings
128 Monarch 14.2 Learning Guide
Use this button…
To…
Clipboard
Expose clipboard settings
Security
Expose security settings
Datawatch Server Library
Expose Datawatch Server Library settings
Time Intervals
Expose time interval settings
Advanced
Expose advanced settings
Cache
Expose caching options
Themes
Expose theme settings
Help
Launch the Help file
More information on each of the settings available in the Options window is available here.
Monarch 14.2 Learning Guide 129
[7] Working in Report View
In this chapter, you will learn how to launch Monarch and work in Report view. Lesson topics
include:
Starting a Monarch session
Opening report files
Navigating through reports
Using bookmarks
Copying data from reports
Printing data from a report
Using PDF and XPS files
Most computer applications produce reports as output. Monarch is unusual because it uses
reports as input. A Monarch session begins when you open an existing report. The report
displays in Report view.
The Report view gives you a scrollable view of your report. You can move around, look things
up, copy data to other applications, and print pages on your local or network printer. In this
lesson, you will learn the basics of these operations. In later lessons, you will learn how to
extract and export data from reports.
Opening Report Files
In Monarch, you can open either one or several reports (up to 1,024 reports!) in a single
session.
OPENING A SINGLE REPORT FILE
To begin working with Monarch, you need to open a report file. We use the term “report file”
to describe any computer report stored on disk. These files are often referred to as print,
spool, TXT, PRN, SDF, PDF, and formatted or fixed width text files.
We have provided several report files for use with these lessons. In this lesson, we will first
open single report files and then open multiple report files.
130 Monarch 14.2 Learning Guide
NOTES
Monarch opens a report as a “read-only” file. Monarch can write a
new file with data extracted from the report, but the original
report remains safe because it cannot be altered.
You can also open reports in zip files or web and other locations.
If you have set up Datawatch Server Library credentials in the
Options interface, you can also open files located in the specified
Datawatch Server Library.
Let’s open a report file.
Steps:
1. On the File tab, select Open > Report > Computer.
Figure 7-1. The Open Report dialog.
The Open Report dialog appears. The Files list displays all the files in the Reports folder
with a DAT, PRN, RPT, or TXT extension. If you don’t see a list of files, make sure you are
viewing the Monarch > Reports folder, and the file type is set to Print Files
(*.dat;*.prn;*.rpt;*.txt), as shown in Figure 7-1.
2. Select Classic.prn, and then choose Open.
Monarch displays a softcopy of the report in Report view. The report shows customer
shipments for a distributor of classical music recordings.
Monarch 14.2 Learning Guide 131
OPENING MULTIPLE INSTANCES OF A REPORT
The procedure for opening multiple instances of a report is similar to that required to open a
single report.
Let’s start by opening the January shipping report.
Steps:
1. Assuming that you have launched Monarch, select File, click on the arrow of the Open
menu, and then select Report.
2. In the Open Report dialog that displays, click on ClassJan.prn, and then choose Open.
January’s shipping report displays in Report view.
Figure 7-2. Classical Music Distributors January shipping report.
Next we’ll open the February shipping report.
3. Select File, click on the arrow of the Open menu, and then select Report.
4. Click on ClassFeb.prn, and then choose Open.
For each additional report you open, Monarch displays the Retention Options dialog. This
dialog prompts you to discard or retain any currently open reports and model parameters
before opening the new report.
132 Monarch 14.2 Learning Guide
Figure 7-3. The Retention Options dialog.
To replace the existing report and model with a new report, you would leave the Retain
Current Reports and Retain Current Model check boxes unchecked. Since we want to open
a new report without closing the currently open report, we’ll choose to retain the current
reports.
5. Select Retain Current Reports, and then choose OK.
The February shipping report replaces the January report in Report view. Note that
Monarch does not create a separate window to display each report. Instead, all open
reports are viewed in one Report view, with a single report displayed at a time. When two
or more reports are open at the same time, Monarch displays the most recently opened
report. You can select another report to view by clicking on it in the Report selector. The
Document Selectors list all of the open reports, tables, and summaries in each Monarch
session.
So far we have opened two reports: ClassJan.prn and ClassFeb.prn. ClassFeb.prn was the
last report opened so that report is currently displayed in Report view.
Now let’s open the March shipping report.
6. Select File, click on the arrow of the Open menu, and then select Report.
7. Click on ClassMar.prn, and then choose Open.
This time the Retain Current Reports check box is already selected.
By preserving your most recent dialog settings, Monarch saves you a step each time you
open a new instance of the report.
8. Choose OK.
The March shipping report is opened and displayed in Report view.
Monarch 14.2 Learning Guide 133
Getting to Know Report View
Let’s take a minute to explore what you can find in the Report interface.
Figure 7-4. The Report interface.
The Report interface is divided into three distinct areas. The Report ribbon is located on the
top-most portion of the interface and spans the entire width of the screen. This ribbon will
allow you to perform many of the report functions made available by Monarch. The Report
selector displays as a panel on the left-hand side of the interface. You can choose specific
reports to view from this selector, a functionality that is especially handy when you have
multiple reports open. Finally, the actual report is displayed on the right-hand portion of the
interface.
The following table summarizes the functions of each of the buttons in the Report ribbon.
USE THIS BUTTON…
TO…
Report Design
Launch the Report Design interface
Auto Define
Automatically creates a template (or sets of
templates, if applicable) for an open report. Note
that this feature is only enabled in Monarch
Complete and that the resultant model is applied
to all other open reports.
Font Style
Select a font style
Font Size
Select a font size
Zoom to Fit
Automatically resize the report into the available
Report view using the best possible fit
Greenbar
Add greenbar shading to the report
134 Monarch 14.2 Learning Guide
USE THIS BUTTON…
TO…
Select All
Select all of the text in the report
Copy
Copy text from the report
Search
Search for information from the report
Find in Table
Find the table entry corresponding to a specific
line in the report
Previous Report
Go to the previous report if multiple reports are
open
Previous Page
Go to the previous page of a multiple-page report
Go to Page
Go to a specific page of a multiple-page report
Next Report
Go to the next report if multiple reports are open
Next Page
Go to the next page of a multi-page report
Bookmark
Add a bookmark to a specific record
Next Bookmark
View the next bookmark
Previous Bookmark
View a previous bookmark
Clear Bookmark
Clear all bookmarks
Report Index
Display or define the report index
PDF/XPS Options
Specify character spacing options when opening
PDF/XPS files
Open as a Table in Data Prep
Studio
Open the report as a table in Data Prep Studio
Help
Launch the Help file
Navigating Through Reports
Although the softcopy report looks just like its hardcopy cousin, you can use Monarch to zoom,
scroll, page, jump, and quickly find information within it. These tasks would be a lot more
difficult to carry out with a hardcopy report. Let’s look at some of the benefits of viewing and
exploring a softcopy report on screen using the three files we opened previously. Make sure
that you select the Report tab before you begin.
Monarch 14.2 Learning Guide 135
THE REPORT SELECTOR
Report view opens and Monarch also displays, by default, a panel on the left side of the
screen: the Report selector.
Figure 7-5. The Report selector.
Clicking on any report in the Report selector opens the report in Report view.
The Search Selection box allows you to search for open reports. This functionality would be
useful if, for example, you had a large number of open financial reports and want to search
specifically for sales reports for a certain year.
Figure 7-6. The Search Selection box allows users to search for individual reports in the Report
selector.
Right-clicking on the Report selector displays a context menu that will allow you to perform
the following operations:
Close a report
Print a report
Quick print a report
Preview a report before printing
Export (all files)
Export a selected file
View the File Information dialog (includes details on file path and name, size, lastmodified
date)
136 Monarch 14.2 Learning Guide
ADDING GREENBAR
Because Monarch operates in a graphical environment, you can adjust the display to enhance
readability and suit your own visual preferences. Monarch can display a greenbar effect on the
screen, which helps your eyes track across sparse columns in a wide report. If you prefer not
to display greenbar, however, you can easily remove it.
Steps:
1. On ClassJan.prn, select Greenbar from the Report ribbon.
2. The report displays with greenbar shading.
Figure 7-7. The report displayed with greenbar shading.
3. To remove greenbar shading, simply select Greenbar again.
NOTE
For the remainder of this Learning Guide, we will not use the
greenbar feature.
Monarch 14.2 Learning Guide 137
CHANGING FONTS AND FONT SIZES
Monarch allows you to change the font and font size used to display reports on screen.
Steps:
1. To change the font, in the Report ribbon, click the drop-down button on the Font Style
box.
Figure 7-8. Changing font styles.
Only non-proportional fonts with fixed letter spacing are available because report columns
may appear misaligned if proportionally spaced characters were used.
2. Select the Lucida Console font from the list.
The font style is changed throughout the entire report.
You may also change font size in order to zoom in or zoom out. Experiment with the font size
until you find the size you like best.
138 Monarch 14.2 Learning Guide
Steps:
1. Click the drop-down button on the Font Size box.
Figure 7-9. Changing font sizes.
Font sizes vary depending on the font style selected. If the size you want isn’t available,
use the font list to select another font. You can let Monarch select the font size for you
using the Zoom to Fit command. This selects the font size that matches the report width
to the display width, so you can view the entire width of the report on screen.
2. To change the font size to match the report width to the display width, from the Report
ribbon, select Zoom to Fit .
If the report is too wide to fit on screen even at the smallest available font size, Monarch
will select the smallest font size to fit as much of the report width as possible in the
available display area.
MOVING TO THE NEXT PAGE OF A REPORT
It can be a tedious chore to dig through page after page of a big hardcopy printout. With
softcopy reports on screen, however, your computer does most of the work for you. Monarch
lets you move around with just a few clicks of your mouse.
Like most Windows applications, Monarch provides a vertical scroll bar to help you scroll
through a report. You can scroll through the report by pressing the up or down arrow on the
scroll bar or by clicking and dragging the slider with your mouse. As you drag the slider, a
tooltip window displays the current page of the report. To display a particular report page,
drag the slider until the tooltip displays the desired page number.
Monarch 14.2 Learning Guide 139
For example, the following figure shows Page 8 of the ClassJan report.
Figure 7-10. Scrolling through a report.
To move through the different pages of a report, you can:
Use the Previous Page button
Use the Next Page button
Use the Go to Page button
When you select the Go to Page button, the Go to Page dialog displays.
Figure 7-11. The Go to Page dialog.
Enter the desired page (e.g., 8) into the Page Number field and then click OK. Monarch
displays the specified page in Report view. Go back to Page 1 before moving on with this
lesson.
Dotted lines indicate page breaks. Monarch relies on page break characters in the report file to
determine where breaks belong. If breaks are not explicitly defined in the report file, Monarch
breaks pages after a fixed number of lines.
To move left or right, press the left or right arrows on the horizontal scroll bar, or drag the
slider with your mouse.
140 Monarch 14.2 Learning Guide
MOVING THROUGH SEVERAL REPORTS
To move through different reports when multiple reports are open, you can:
Use the Previous Report button
Use the Next Report button
FINDING INFORMATION IN A REPORT
It’s not always easy to find information buried deep in a hardcopy report. If you’re looking for
a particular invoice in a report that is 75 pages long, for example, you can spend a lot of time
and effort reading each page and you may not even find the report you want. Fortunately,
Monarch can search reports at lightning speed to find whatever you want. Suppose you want
to find all references to the composer, Mozart. You can use the Search function for this.
Steps:
1. Select the ClassJan.prn report and ensure that you are on its first page.
2. Select Search from the Report ribbon.
A Search box displays on top of the Report view.
Figure 7-12. The Search panel.
3. Type Mozart in the Search field and then click Find Next.
Monarch 14.2 Learning Guide 141
The first occurrence of Mozart in the report will be highlighted. By default, search items
are returned in a top-down manner. To search in a bottom-up manner, check the Search
Up box among the Search options.
4. Continue clicking the Find Next button to locate the next instances of Mozart.
NOTES
You can make searches case-sensitive by selecting Match Case. To
reverse your search, i.e., to search for previous instances of the
word or phrase, check the Search up box before clicking Search.
The option Search all reports allows you to locate all instances of
the search item in all open reports.
5. Close the panel by selecting Search on the ribbon more.
Using Bookmarks
Monarch allows you to set bookmarks within a session that can be used to navigate quickly
between certain lines or sections of a report. You can even annotate bookmarks.
NOTE
Although bookmarks aren’t saved in model files, they are saved in
project files. When exporting to a PDF file while in the Report view,
bookmark comments will be displayed as comments within the
PDF file.
Let’s set bookmarks for Spinning Records and Musique Royale.
Steps:
1. Use the search tool to locate Spinning Records in the ClassMar.prn report.
2. Click in the left-hand margin next to CUSTOMER to highlight that line.
142 Monarch 14.2 Learning Guide
3. Select the Report tab. On the ribbon that displays, select Bookmark . The Add
Bookmark dialog displays. With this dialog you can add a comment to a bookmark. Let’s
try doing this.
Figure 7-13. The Add Bookmark dialog.
4. Type whatever text you’d like (e.g., This is a bookmark comment.) and then click the
OK button. A bookmark indicator appears on the left hand margin next to the highlighted
line of text.
5. Place your mouse pointer over the bookmark indicator.
The text you typed is displayed.
Figure 7-14. A bookmark is added to Spinning Records.
6. Use the search tool to locate Musique Royale, and then repeat Steps 2–4 to bookmark it.
Monarch 14.2 Learning Guide 143
NOTE
You can click the OK button on the Add Bookmark dialog without
actually entering a comment if you wish. To prevent the Add
Bookmark dialog from displaying on a specific bookmark, clear the
Show when adding check box.
You can move through bookmarks by clicking either Next Bookmark or Previous
Bookmark from the Report ribbon.
To remove a specific bookmark, select the bookmark you would like to remove and then
click the Bookmark button from the Report ribbon.
To clear all bookmarks, select Clear Bookmark from the Report ribbon.
Copying Data from Reports
Monarch makes it easy to transfer report data to other applications. Just copy and paste!
When you copy data from a report, Monarch creates both a text image and a worksheet image
with separate columns and cells in the clipboard.
Let’s copy a customer’s name and address into your word processor. You might use this
feature if you wanted to insert the address into a letter.
Steps:
1. Go to the first page of the ClassJan.prn report.
2. Click at the beginning of the customer’s name (Betty’s Music Store in this case) and
drag diagonally down to the right to highlight the entire customer name and address, as in
Figure 7-15.
Figure 7-15. Copying a portion of the report.
144 Monarch 14.2 Learning Guide
3. Select Copy from the Report ribbon.
4. Launch your word processor.
5. Position the cursor where you want the address to appear and use your word processor’s
Paste (CTRL + V) command to insert the address into the document.
Figure 7-16. Pasting to a word processor.
NOTE
If you wish to copy all of the text in the report, select the Select
All button on the Report ribbon before clicking the Copy
button.
If you wish to keep this document, save it before returning to Monarch.
4. Exit your word processor without saving the document and return to Monarch.
Monarch 14.2 Learning Guide 145
Printing from a Report
Monarch lets you print from the Report view. You can print a block of text, a page or series of
pages, or the entire report.
Let’s print a single page from the report. We’ll begin with Print Preview to see how the page
will look before producing the actual printout.
Steps:
1. Return to the first page of the report ClassJan.prn.
2. Select the File tab, click on the arrow of the Print menu, and then select Print
Preview from the options that display.
The Print Preview window displays.
Figure 7-17. The Print Preview Window.
146 Monarch 14.2 Learning Guide
3. Examine the Print Preview window to see if your settings for font size, margins, and page
orientation are appropriate to produce satisfactory output. If not, you can change the
settings until everything looks just right. We’ll change settings in a minute, but first, we’ll
see how the Print Preview window works.
4. Click Zoom to zoom in or out.
5. Click the Next Page or Previous Page icons to move forward or back.
Now, we’ll adjust our page orientation, margins, and font settings. On many printers, the
Classic report prints nicely with a portrait page orientation, half-inch margins, and a 9-
point font. You may need to experiment to find the right settings for your printer.
6. Select the drop-down button of the Page Orientation menu to confirm that the page
orientation is set to Portrait.
7. If you wish to change page margin settings, select Page Setup and adjust the margin
settings as necessary for your printer by double-clicking on the margin you wish to modify
and entering your desired value. Select OK when you are finished. For now, though, let’s
leave the margins as they are.
Figure 7-18. The Page Setup dialog.
8. When satisfied, close the Print Preview window by clicking the button on the upper
right-hand corner of the window.
9. Select the File tab, click on the arrow of the Print menu, and then select Print from
the options that display.
We’ll print Page 5.
Monarch 14.2 Learning Guide 147
Figure 7-19. The Print dialog.
10. Select the printer you would like to use.
11. Select the Pages radio button and then type 5 in the Pages box.
12. Select Print.
The selected page will be printed. Monarch prints report pages as composed in the report
file. Because Monarch adopts pagination from the report file, you must be careful to select
an appropriate font size and margin settings to ensure that the full report image will fit on
each page. Any lines that do not fit will be carried over to a subsequent page. Any
columns that do not fit will be truncated.
Closing Reports
During each Monarch session, you may wish to use several report files and thus have a need
to close other reports. Let’s do that now.
Steps:
1. Ensure that the report ClassMar.prn is selected in the Report selector.
2. Select File > Close > Report.
If you had other files open, such as a model, a database file, or a project, that you wish to
close, you can also select these from the options presented when you select Close.
3. A message box displays to ask you if you wish to close the ClassMar.prn report. Click Yes.
The selected report is closed and the Report selector displays the two remaining open
reports.
148 Monarch 14.2 Learning Guide
Repeat Steps 1 to 3 to close the ClassJan.prn and ClassFeb.prn reports.
If you had both report and model files open and want to avoid having to close each file
one by one, you can use the Close All command, which is called when you select File >
Close All . This command closes all of the open files in your Monarch session. You will
be asked whether or not you would like to save model changes if you defined templates
during the Monarch session.
Using PDF and XPS Files
Monarch has the ability to convert PDF/XPS files into text so that they can be utilized like any
normal text report in Monarch. You can then create data extraction templates to extract
information in exactly the same way as you would approach a text report file.
PDF/XPS is an open standard, so there are thousands of different applications that create
PDF/XPS files using many different techniques. This has the side effect of having PDF/XPS files
that may look identical on screen, but are structurally very different internally.
Monarch uses very sophisticated techniques to analyze and reformat the data, in order to
produce the best possible "Monarch-friendly" results to enable accurate trapping after
importing the data.
However, there may be some badly formatted and unusual PDF/XPS files that do not convert
accurately enough to trap easily in Monarch. Files that do not convert perfectly may still be
processed very effectively by Monarch via features such as the floating trap and calculated
fields. Let’s begin this lesson by importing a PDF file into Monarch.
Steps:
1. Select File, click on the arrow of the Open menu, and then select the Report option
to display the Open Report dialog.
2. Select the PDF files (*.pdf) option from the Files of Type drop-down list, select the
ClassJan.pdf file, and then click the Open button to open the PDF in the PDF Import
Options window.
Monarch 14.2 Learning Guide 149
Figure 7-20. The opened PDF file.
3. For now, simply select Accept on the PDF Import Options window to display the
report.
As we can see, ClassicJan.pdf is a PDF version of the ClassicJan.prn report, which we are
already familiar with.
To import an XPS file into Monarch, follow the same procedures outlined above, but
choose the XPS files (*.xps) option from the Files of Type drop-down list instead.
When opening a PDF/XPS file, Monarch performs an analysis of the file to try and determine
the optimum method of transforming the data accurately. In most cases, Monarch’s auto-
detection routines will produce the best results, but under certain conditions, adjustments to
the PDF/XPS import options may be necessary. For example, the following conditions may
require the PDF/XPS import options to be adjusted:
The PDF/XPS contains tables with tightly compacted columns.
The PDF/XPS contains multiple font sizes and the data of interest is in a smaller font than
most of the other text in the PDF, thereby causing the auto-calculated font size to be too
large.
Mixed mono- and variable-spaced fonts exist in the PDF/XPS where the data of interest
uses mono-spaced fonts.
Mixed freeform and tabular data exist in the PDF/XPS.
Let’s examine the PDF/XPS import options more closely, and see how adjusting them will
affect the data transformation.
150 Monarch 14.2 Learning Guide
THE STRETCH OPTION
The stretch option governs how much spacing is used during the conversion process. When
Monarch analyzes the PDF/XPS file, it tries to match the spacing as closely as possible to the
original document. However, there are many factors that can make it necessary to introduce
more spacing into the conversion than appears to exist in the original PDF/XPS file. Such
factors can include hidden data in the PDF/XPS, i.e., data which is not visible on screen but
still exists within the PDF/XPS file itself. This can be the result of columns that truncate the
data, for example. At first glance, it is not apparent that any data is missing, but Monarch will
convert all the data in the PDF/XPS file, not just what might be visible in a PDF/XPS viewing
application. In this case, in order to try and maintain proper column justification, Monarch will
have to recalculate and pad the spacing, as the original column spacing may not be enough to
hold the data safely.
In general, Monarch’s behavior is to use a larger amount of spacing (i.e., a higher stretch
value) than in the original document. This can make the document look like it is stretched
wider than the original PDF/XPS, but Monarch errs on the side of caution, so that columns do
not run into each other in the current document. This is also done so that if a later iteration of
the same report (or a similar one) contains wider data values, the model will still “work” with
it.
If you know your reports well, then you can decrease the stretch value to make the reports
look more presentable by avoiding horizontal scrolling or very small font sizes in the Report
view.
Stretch values may be increased or decreased via the PDF/XPS Import Options window, which
is launched when you select PDF/XPS Options in the Report ribbon.
THE MONO-SPACED OPTION
Mono-spaced refers to the fact that a mono-spaced font was used in the PDF/XPS. The
Monarch Report view uses mono-spaced fonts, which are fonts in which each character has the
same width. For example, in a mono-spaced font, the “o” and “i” characters would have the
same width (i.e., they would take up the same amount of horizontal space on a line). Other
terms for mono-spaced are fixed-width and non-proportional.
NOTE
The opposite of mono-spacing is proportional spacing, in which
different characters have different widths, e.g., in a proportionally
spaced font, the letter “o” would be wider than the letter “i.”
Note that the Mono-spaced option is already selected on the window. When you import a
PDF/XPS file into Monarch, Monarch tries to detect when mono-spaced fonts are used and
optimizes the conversion accordingly. In some cases, Monarch may not detect that mono-
spaced fonts were used for the PDF/XPS file. When this failure occurs, it is usually due to a
mix of mono-spaced and proportional fonts existing in the same PDF/XPS file. If you know that
the PDF/XPS file uses mono-spaced fonts, and the Mono-spaced option is not selected during
Monarch 14.2 Learning Guide 151
the import analysis, you can select this setting to force Monarch to optimize for mono-spaced
fonts.
While proportionally spaced fonts look more appealing, mono-spaced fonts are superior for
tabular data because the uniform width of each character makes alignment of columns easier.
NOTE
In general, PDF/XPS files generated using mono-spaced fonts will
convert more successfully, so if you are trying to optimize your
PDF/XPS producing application for Monarch, use mono-spaced
fonts. Some of the more common fonts include: Andale Mono,
Anonymous, Crystal, Bitstream Vera Sans Mono, Courier, Courier
New, Elronet Mono-space, Everson Mono Latin 6, Fixedsys, Lucida
Sans Typewriter, Lucida Console, and PrestigeFixed.
THE FREEFORM OPTION
The Freeform option tries to optimize text that is more freeform than columnar or grouped
columnar. A columnar document is a simple table format, where grouped columnar might be
something similar to one of the Monarch sample reports, such as Betty’s Music Store
(Classic.pdf).
A typical document that might benefit from using this setting would be an academic report
that is 95% text, but which contains a few tables you want to extract.
NOTE
This setting will sometimes work effectively on columnar
documents when the default settings do not produce good results.
When you select a PDF/XPS file from the Open Report dialog and then click Open, the
PDF/XPS is automatically imported into Monarch as a report file.
If we wanted to, we could now select a template sample and begin creating templates to
extract the PDF/XPS file’s data. Since we’ve done plenty of this already, let’s try importing a
PDF/XPS file into Monarch that will require us to modify the PDF/XPS import options. For now,
close the PDF report by selecting File, clicking on the arrow of the Close menu, and then
selecting Report.
152 Monarch 14.2 Learning Guide
CUSTOMIZING PDF/XPS IMPORT OPTIONS
Steps:
1. Select File, click on the arrow of the Open menu, and then select Report from the
options that display.
2. In the Files of type drop-down box, select PDF files (*.pdf).
3. Select the CustomerSummary.pdf file, and then click Open.
You can also perform the next steps with an XPS file. In this case, open
CustomerSummary.xps instead.
Monarch opens the PDF Import Options window and displays a sample of the PDF file’s
data. Let’s enlarge the window so that we can view more of the sample data.
Figure 7-21. Viewing the resized window.
Notice that for Bluegrass Records, the value in the Amount 1 column begins one space too
far to the right, so that it doesn’t line up properly with the Amount 1 values for the other
three customers.
Let’s assume that we know the font used to generate this PDF was Tahoma, which is a
proportional font. A quick visual investigation tells us this is not a freeform document, but
is more of a columnar report. Let’s try adjusting the Stretch setting to see if this improves
the conversion.
4. In the Stretch size indicator, shift the marker until the size reads 7.0. You may need to
resize the window to the right to see all of the data. Monarch applies the stretch setting
and shows the results on the sample page. Note that the Amount 1 value for Bluegrass
Records now lines up correctly with the Amount 1 values of the other customers. If you
wish to increase the font size in the window, use the Zoom size indicator to resize the font
as you wish.
Monarch 14.2 Learning Guide 153
Figure 7-22. Viewing the adjusted sample page.
5. Click the Accept button to close the PDF/XPS Import Options window and open the
PDF file as a report.
A closer look at all of the options available in the PDF/XPS Import Options window may be
found here.
This completes Chapter 6. If you plan to go on to Chapter 7 now, you may leave Monarch
open but select the File tab and then click Close All . Select No when asked if you want to
save changes to the model or project you created. If you plan to go on to Chapter 7 at a later
time, select File > Exit Monarch.
154 Monarch 14.2 Learning Guide
[8] Extracting Data from a
Report
In Chapter 4, you learned how to use the Report view to explore a report on screen. While this
is very useful, Monarch goes much further in giving you access to the data buried in your
reports. By defining data extraction templates, you can extract the data from your reports,
then analyze it or export it using the Table and Summary windows, which are discussed in
later lessons. In this chapter, you will learn how to extract data from a typical report. The
lesson topics include:
Determining what an extraction template is
Creating various templates
Displaying data in Table view
Saving your work in a model file
To get started on this lesson, load Monarch and open the Classic.prn report.
Getting to Know Report Design View
The Report Design interface is our jumping-off point for creating data extraction templates. It
is accessed by selecting Report Design from the Report ribbon.
Monarch 14.2 Learning Guide 155
Figure 8-1. Launching the Report Design interface and Template Editor.
Let’s take a minute to explore what you can find in the Report Design interface.
The Report Design interface is divided into five distinct areas:
The Report Design ribbon is located on the top-most portion of the interface and spans the
entire width of the screen. This ribbon will allow you to perform many of the report design
functions made available by Monarch.
The Template Selector displays as a panel on the left-hand side of the interface. You can
choose specific templates to view and modify from this selector, a functionality that is
especially handy when you have defined multiple templates.
The Template Editor is displayed in the middle of the interface. The Template Editor remains
disabled until you select a new template to create.
The right-hand side of the interface displays the general properties of a field in the Field
Properties panel. Each field you define in a template has its own properties. You can set new
properties or edit existing ones using this panel.
Below the Template Editor is a Data Preview panel. This panel automatically generates a
preview of what your table will look like, including all of its content, while you build templates
to extract the desired data from your report.
The figure below shows an example of what a Data Preview might contain:
156 Monarch 14.2 Learning Guide
Figure 8-2. The Data Preview tab provides users with a tabular sample of their extracted data
In this example, data from the Detail template are shown.
Figure 8-3. The Data Preview panel shows the data captured by the Order Number Level
(append) template.
Monarch 14.2 Learning Guide 157
Figure 8-4. The DataPreview panel shows the data captured by the Page Header template.
The following table summarizes the functions of each of the buttons in the Report Design
ribbon.
USE THIS BUTTON…
TO…
Accept
Accept the new template or changes to an existing
template
Cancel
Cancel the new template or changes to an existing
template
Template Name
Specify a new template name or modify an
existing one
Template Role
Display the role of the template selected
New Template
Specify a new template
Delete Template
Delete a template
Replace Sample Text
Select a new line in the report with which to base
a template
Clearing Template
For footers and appends, remove “assumed”
values copied up (for footers) or down (for
appends) when only intermittent data are
available. More information on this feature may be
found here.
Auto-Define Trap
Allow Monarch to automatically create traps for
selected lines in the report
Auto-Define Fields
Allow Monarch to automatically specify table fields
based on selected lines in the report
158 Monarch 14.2 Learning Guide
USE THIS BUTTON…
TO…
Multi-Column Region
Specify multi-column region settings
Run Report Verify
Verify that the boundaries of fields are properly
defined
Zoom Control
Increase or decrease the size of the font displayed
in the Template Editor
Report Colors
Specify template colors
Help
Launch the Help file
Extracting Data Using Templates
Imagine taking a piece of cardboard and cutting holes in it at certain locations. If you place
this imaginary cardboard template on a printed report, you will see only the information that
shows through the holes. Monarch uses electronic templates to extract information from your
report files in a similar manner. If your report is sorted on several levels, you define a
separate template for each sort level.
The Classic.prn report has a simple structure, so extracting information from it is relatively
easy. You can extract all the important information by creating a single detail template.
Figure 8-5. The Classic.prn report.
Classic.prn is a monthly shipment report for a distributor of classical music recordings. Like
many reports, Classic.prn is organized with multiple sort levels. The entire report is sorted by
customer. Within each customer, orders are sorted by ship date. Within each order, shipments
are itemized on detail lines. In addition, three header lines appear at the top of each page.
In this chapter, Monarch will draw data out of each level in the report based on templates that
you define. You will start by defining a detail template. Then you’ll define an append
Monarch 14.2 Learning Guide 159
template to extract fields from each sort level and a page header template to extract fields
from the page header. Fields from the append templates and the page header template will be
appended to fields from the detail template to produce database records.
We’ll use the sample line to identify all of the other detail lines throughout the report. To do
this, we’ll use a process called trapping. By setting a trap, you tell Monarch which lines to
capture and which to ignore.
A detail trap identifies common features shared by all of the detail lines throughout the
report, but not shared by other lines in the report, such as page header lines, lines containing
labels, or lines from higher sort levels. A proper detail trap will capture only those lines we
want while ignoring lines from headers and other sort levels.
In the Classic.prn report, we need to look for features that differentiate the order information
lines from the title, date, and page number lines at the top of each page. Specifically, we need
to look for characters, such as letters, numbers, or punctuation, which always appear at the
same position in the detail lines, but do not appear in any other lines.
Figure 8-6. The Template Editor allows users to define templates with which to trap data.
Selecting a new template to create or opting to edit an existing one activates the Template
Editor. In the Template Editor, the Template Name and Template Role fields are automatically
populated with the type of template we are going to create: Detail. The Template Selector on
the left side of the image also displays the template name. We can change this name by
simply changing the entry in the Template Name field.
Figure 8-7. The Template Selector.
Note also that most of the buttons that were previously deactivated in the Report Design view
are activated in the Template Editor.
160 Monarch 14.2 Learning Guide
The line you select for trapping displays on a yellow background on the Editor. This line is
called the Sample Text box. Above the Sample Text box is the Trap line, which we will use
later to create our traps. The different trap types we can use are found in the Action bar. To
the left of the Template Editor is the Line Selection area, which we will use extensively later
on to select lines to trap.
Figure 8-8. Specific areas of the Template Editor.
Several types of traps are available:
USE THIS TRAP…
TO…
Alpha trap
Trap any letter
Numeric trap
Trap any number
Blank trap
Trap blank spaces
Not blank trap
Trap any character that is not a space
Numeric OR trap
A special trap, used to trap numbers which may or
may not appear in certain positions. A good
example is an accounting report, where a number
may appear in a credit or debit column, but not
both. Using two numeric OR traps will trap a
number in either position.
Character trap
Trap special characters
NOT trap
Trap any character EXCEPT the specified trap
character
Exact match trap
Type any character or series of characters
Floating trap
Trap data from reports in which the data
placement is not fixed
Monarch 14.2 Learning Guide 161
Aside from simply creating traps, the Action bar allows you to perform other functions:
USE THIS BUTTON…
TO…
Trap Type
Specify the trap type to create: Standard Trap,
Regular Expression Trap, or Floating Trap
Regular Expression Trap
Options
Select options to apply when creating regular
expression traps: Ignore case, Explicit capture, or
Ignore Pattern Whitespace
Trap Line
Specify a different trap line on which to base a
template if the sample text is composed of
multiple lines
Reset Trap Line
Discard all changes made to the Trap line and
start again
Reset Fields
Discard all fields specified and start again
Delete Field
Delete a specific field
Edit Field
Launch the Field Definition window
To generate a table, Monarch produces one record for each detail line extracted from the
report. The detail line contains the data that changes most often in the report and is usually
the report’s lowest sort level. The append templates add, or append, information from higher
sort levels. In this lesson, we will extract fields from four levels in the report; the detail level,
two sort levels, and the page header.
Creating the Detail Template
The detail template extracts data from the lowest level in the report, which in this case are the
lines representing individual shipments.
To create the detail template, we’ll select a sample detail line and then use this line to identify
other detail lines and highlight the fields to extract.
Steps:
1. In the Report ribbon, select the Report Design button.
The Report Design view displays.
162 Monarch 14.2 Learning Guide
Note that in this view, most of the buttons in the ribbon are deactivated. The buttons are
activated when we create a new template or edit an existing one.
Figure 8-9. Most of the buttons in the Report Design interface remain deactivated until you
activate the Template Editor.
2. Click in the line selection area to the