comp-14: easyask ® natural language ad- hoc query & reporting: a user’s experience dr. larry...
TRANSCRIPT
COMP-14: EasyAsk® Natural Language Ad-Hoc Query & Reporting: A User’s Experience
Dr. Larry R. HarrisProgress EasyAsk
Adrian O’BrienCardif Pinnacle
© 2007 Progress Software Corporation2 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation3 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk Natural Language
LogicalView
PhysicalView
DBMS
SQL
ConceptualViewNatural
EasyAsk
Translates Natural Language Questions into SQL/ABL
Using a dictionary in which each definition shows how the word relates to the database
© 2007 Progress Software Corporation4 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Fundamentals of EasyAsk
Ease of information access to a broader set of users Lower the technical requirements
• SQL• DB Schema
Use the language people already know!• English• French, German, Portuguese in Beta• (Spanish, Italian, Dutch) as required
EasyAsk Goals
© 2007 Progress Software Corporation5 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Usage ExamplesUsage Examples
Business users need to find information• “Give me a pie chart of the sales in each
region for last month” Managers monitor their business
• Sends email alerts if key measurements are out-of-bounds
Administrators understand how EasyAsk is being used and how to improve it• “Show me last week’s questions that were not
answered.”
© 2007 Progress Software Corporation6 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation7 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Selected CustomersSelected Customers
© 2007 Progress Software Corporation8 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation9 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Key EasyAsk Constructs
Query Builder• Saved Requests
• Finding a Column
The Map• How tables will be joined together
User Key• What columns will be displayed when a table
name is referred to in a query
Dictionary Inheritance
© 2007 Progress Software Corporation10 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation11 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Deploying EasyAskDeploying EasyAsk
Security Performance Range of Expression Database Design Idiosyncrasies Usage Examples Integration
© 2007 Progress Software Corporation12 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Security
Dynamic Role-Based Security Each User Group Provides:
• Row Level Security
• Column Level Security
Standard Reports still work, but will provide null results for secure data items
Passed Ceridian Security Audit
© 2007 Progress Software Corporation13 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Performance
Generates very high quality SQL Eliminates any possibility of a non-specified
join Governors for:
• CPU Time
• Result Set Size
© 2007 Progress Software Corporation14 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Range of ExpressionRange of Expression
The way the User enters the question is correct way The following questions all provide the same answer:
• How many customers bought seafood in each state?
• What’s the number of seafood customers in every state?
• By state, give me a count of the customers who bought seafood.
• Count the seafood customers in each state.
© 2007 Progress Software Corporation15 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Database Design IdiosyncrasiesDatabase Design Idiosyncrasies
What What percent of employees with employee deferral of employees with employee deferral contributions are contributions are eligibleeligible??SQL-> SELECT count(distinct sd0002.dbo. employee .employee_id) as "Count", (100 * count(distinct sd0002.dbo. employee .employee_id)) / (Select count(distinct sd0002.dbo. employee .employee_id) From employee Where sd0002.dbo. history_view .cash<>0 and {fn UCase(sd0002.dbo. history_view .portion_code)}='EEDF' and sd0002.dbo. history_view .category='02') as "Percent" FROM sd0002.dbo. employee , sd0002.dbo. history_view WHERE (sd0002.dbo. history_view .cash<>0 and {fn UCase(sd0002.dbo. history_view .portion_code)}='EEDF' and sd0002.dbo. history_view .category='02' and {fn UCase(sd0002.dbo. employee .status)} in ('ACT', 'NPA', 'INA', 'PEN')) and sd0002.dbo. employee .client_nbr=sd0002.dbo. history_view .client_nbr and sd0002.dbo.employee.employee_id=sd0002.dbo. history_view .employee_id
Users must carry around a “Cheat-Sheet” to remember the design idiosyncrasies of the database
What if a new eligible code is created?
© 2007 Progress Software Corporation16 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation17 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation18 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Agenda
What is EasyAsk? Who is Using EasyAsk? Building a Dictionary Deploying EasyAsk EasyAsk Reports New EasyAsk Features
EasyAsk Ad-Hoc Query & Reporting
© 2007 Progress Software Corporation19 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
New EasyAsk Features
Query Builder – Help Users Find the Column• Search Capability for Columns
• Hide all but the most meaningful columns
• Show columns relevant to a specific report category
© 2007 Progress Software Corporation20 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Thank You!
Questions?
© 2007 Progress Software Corporation21 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
An overview of
EasyAsk
within Cardif Pinnacle
Presented By:
Adrian O’Brien, General Manager MI & Projects, Cardif Pinnacle
© 2007 Progress Software Corporation23 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Who are Cardif Pinnacle
Cardif Pinnacle are the British subsidiary to Cardif, the insurance arm of BNP Paribas.
The French bank BNP Paribas is one of the World’s largest banks
Cardif Pinnacle has 750 employees and also has branches in Ireland and South Africa. EasyAsk is used in all three locations
Established in 1971, Cardif Pinnacle is one of the UK's leading providers of creditor insurance and product warranties. In recent years the company has developed other product lines including Medical Cash Plans, Pet Healthcare and Personal Accident cover.
Cardif Pinnacle also markets competitive, tax efficient investment products via IFAs and direct to the public … and Financial Telemarketing Services provides contact centre and outsourcing services to leading Banks, Building Societies and Finance Houses.
© 2007 Progress Software Corporation24 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
Why did we purchase EasyAsk?
Cardif Pinnacle purchased EasyAsk along with a fast access data store Aruna to:
• Overcome delays in reporting• Rationalise reporting• Give ownership of data to our business users
Typical problems we faced before the introduction of EasyAsk were:
• Reports running slowly and clogging up our systems• Inefficient use of IT resource• Users finding workarounds because they couldn’t get their
request done within an adequate time frame• External clients and internal users becoming frustrated as we
could not provide meaningful MI quickly• Business users unable to adequately explain their business
requirements to IT• Data hidden and not properly understood by key people within
the business
© 2007 Progress Software Corporation25 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Proof of Concept
In 2003 we set what we considered to be a real challenge:
To transfer data to our new Aruna Date store, create the required EasyAsk dictionary and remove the top twenty five slowest running reports from our IT systems within a two month period.
The challenge was met with plenty of time to spare!
It also demonstrated previously un-thought of benefits such as:
• Rationalising and simplifying our terms and definitions- Changing the way the organisation thinks:
Our Senior Finance Manager states:
“EasyAsk helps us better understand our business and improve our processes”
© 2007 Progress Software Corporation26 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk and Aruna
1.Aruna can integrate data from multiple databases and business applications, EasyAsk can cut and slice views of this in any way we wish
2. Aruna processes queries at super fast speeds
3. EasyAsk allows simple plain-language querying
4. Reports which took hours to run on our administration systems now run via Aruna and EasyAsk in seconds or minutes at the most
The combination of Aruna and EasyAsk allows our business users to take ownership
of information
© 2007 Progress Software Corporation27 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – More than one natural way to query
For the one finger typist just point and click and the table and field names get written for you
© 2007 Progress Software Corporation28 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Training and Support
We provide full internal training for EasyAsk – but it only takes an hour
Hold user’s hand for one month - Then throw them into the sea!
Email request box – when they have real trouble swimming
Turnaround time – one day or less for simple requests – five days for complex reports
Many times faster than producing reports by other means
If we do have to use IT then report specification defined by MI information analyst rather than ordinary user
We currently have 130 users querying EasyAsk
Support - around 30 hours per week
© 2007 Progress Software Corporation29 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Controlled Freedom
EasyAsk can provide any level of security you require
Some tips:
• KISS
• Network groups, User groups, subject groups – the business should decide – IT should guide
• Some tables only available to some groups
• Some dictionaries only available to some groups
• Master updateable dictionary – child dictionaries security setting for users to write queries or just run reports
© 2007 Progress Software Corporation30 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Keep the Wolves Happy
Drivers for Change
• Legislation and the need to demonstrate corporate governance
• Internal and External demands for speed increase over time.
• Data and the demands for information - always more, never less!
EasyAsk provides the flexibility to adapt to an ever changing corporate and global environment
© 2007 Progress Software Corporation31 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Sherlock Holmes & Dr Watson
You all have them – those clever people who look at data in a different way, discover issues and recommend new ways to do business
For Example:• Data Quality Issues• Data Quality Measurement• Data Mining• Digital Dashboard• Progress Developers use EasyAsk• Interaction with other tools – Excel for example
EasyAsk has released the potential of our employees and empowered them in the use of data in a number of
diverse and unexpected ways
© 2007 Progress Software Corporation32 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Excellent with Excel
A big percentage of our user base use Excel
• EasyAsk displays like Excel and has many of the features of Excel
• Export to Excel sheet, or copy and paste• Multiple sheets – just like Excel so keep multiple queries• Refresh sheet so you can update one or all queries on
the fly• The above allows monthly reporting at the click of a
button rather than re-querying
If you want your company’s chief accountant to be your buddy introduce her / him to EasyAsk!
© 2007 Progress Software Corporation33 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Economies of Scale
Many users – teach one another
No need for third party reporting tools
EasyAsk can link to all systems
One querying tool across the company saves time and money in regard to training, software costs, licence maintenance, IT support, software rollouts etc.
Employees are able to move across the company more easily
EasyAsk – Easy Savings!
© 2007 Progress Software Corporation34 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – MI/BI View of the World
Data transformation - from a transactional to an analytical view data
EasyAsk the perfect tool for working with a data warehouse
Aruna a perfect fit with EasyAsk
Digital Dashboard and KPIs
Leverage and Control of MI and BI solutions
Master Data Management
EasyAsk helps us to control the flow of information and prevent proliferation of non-standard MI solutions
© 2007 Progress Software Corporation35 COMP-14 EasyAsk Natural Language Ad Hoc Query & Reporting: A User’s Experience
EasyAsk – Summary
He’s still waiting for his report
I’m off to San Francisco!
EasyAsk - Flexible
EasyAsk - Natural
EasyAsk - Scalable
EasyAsk - Ownership
EasyAsk - SQL Developer
EasyAsk - Building Blocks
EasyAsk - Easy Support
EasyAsk - Controlled Freedom
EasyAsk - Easy Investigations
EasyAsk - Excellent Excel
EasyAsk - Economies of Scale
EasyAsk - Master Data Management