innov-7: can you create a report in less than 15 seconds? rakesh godhani software architect
TRANSCRIPT
INNOV-7: Can you create a report in less than 15 seconds?
Rakesh GodhaniSoftware Architect
© 2006 Progress Software Corporation2INNOV-7 Can you create a report in less than 15 seconds?
Agenda
Business case / challenges Fundamentals of EasyAsk BIRT How EasyAsk brings it all together The little extras that go a long way Q & A
EasyAsk Ad Hoc Reporting
© 2006 Progress Software Corporation3INNOV-7 Can you create a report in less than 15 seconds?
Huge capital investment in business data Few people have access
• Technical barriers– DB schema– SQL– Query/reporting tools
Time• Data – what, where, how to get it, transform it• Reports – layout, fonts, headers, footers,
subtotals, page numbers, etc.
Business case / challenges - Technology
© 2006 Progress Software Corporation4INNOV-7 Can you create a report in less than 15 seconds?
Business case / challenges - Complicated
More data – more complex• Joins, where clause etc.
IT – Technically savvy• Resource & budget constraints
• Please as many people as possible
© 2006 Progress Software Corporation5INNOV-7 Can you create a report in less than 15 seconds?
Business case / challenges - IT
IT (cont.)• ‘One report fits many’ approach
– Extraneous columns – clutter– Missing columns/values– Requests for follow-on queries
Further delays
All this results in …
© 2006 Progress Software Corporation6INNOV-7 Can you create a report in less than 15 seconds?
Business case / challenges - Frustration
USER FRUSTRATION
© 2006 Progress Software Corporation7INNOV-7 Can you create a report in less than 15 seconds?
Fundamentals Of EasyAsk
Ease of information access to a broader set of users
Lower the technical requirements• SQL
• DB Schema
Talk in a more familiar tongue• Business concepts
EasyAsk Goals
© 2006 Progress Software Corporation8INNOV-7 Can you create a report in less than 15 seconds?
EasyAsk is Smart
When talking about concepts, EasyAsk figures out:• Tables, columns, column values
• Relationships (joins)
• Formulas (e.g. age)
• Grouping
• Security
© 2006 Progress Software Corporation9INNOV-7 Can you create a report in less than 15 seconds?
EasyAsk Natural Language Ad-Hoc Query
ConceptualView
LogicalView
PhysicalView
DBMS
Translates Natural Language Questions into SQL
English SQL
EasyAsk
Using a dictionary in which each definition shows how the word relates to the database
© 2006 Progress Software Corporation10INNOV-7 Can you create a report in less than 15 seconds?
Sample Interpretation
Report
Employee
Name
And
Salary
Grouped
By
State
City
(built-in)
Stemmed root of Employees
First & Last Names
(built-in)
Column of Employees
(built-in)
(built-in)
Column of Employees
Column of Employees
© 2006 Progress Software Corporation11INNOV-7 Can you create a report in less than 15 seconds?
Generated SQL
SELECT PUB."Employees"."State" as "State", PUB."Employees"."City" as "City", PUB."Employees"."First_Name" as "First Name", PUB."Employees"."Last_Name" as "Last Name", "Salary" as "Salary"
FROM PUB."Employees"
ORDER BY 1, 2
© 2006 Progress Software Corporation12INNOV-7 Can you create a report in less than 15 seconds?
Sample2 Interpretation
Who
buys
Dairy
But
Not
Meat
Products
Cust First & Last Names
Cust.CustID = Orders.CustID
Value of Category Name
(built-in)
(built-in)
Value of Category Name
Table
© 2006 Progress Software Corporation13INNOV-7 Can you create a report in less than 15 seconds?
Generated SQL
SELECT PUB."Customers"."Company_Name" as "Company Name", PUB."Customers"."First_Name" as "First Name", PUB."Customers"."Last_Name" as "Last Name"
FROM PUB."Customers"
WHERE (exists(SELECT *
FROM PUB."Products", PUB."Line_Items", PUB."Orders", PUB."Categories"
WHERE ("Category_Name"='Dairy') and PUB."Customers"."Cust_Id"=PUB."Orders"."Cust_Id" and PUB."Orders"."Order_Id" = PUB."Line_Items"."Order_Id" and PUB."Products"."Product_Id" = PUB."Line_Items"."Product_Id" and PUB."Categories"."Category_Id" = PUB."Products"."Category_Id") and not exists(SELECT *
FROM PUB."Line_Items", PUB."Orders", PUB."Products", PUB."Categories"
WHERE ("Category_Name"='Meat') and PUB."Customers"."Cust_Id"=PUB."Orders"."Cust_Id" and PUB."Orders"."Order_Id" = PUB."Line_Items"."Order_Id" and PUB."Products"."Product_Id" = PUB."Line_Items"."Product_Id" and PUB."Categories"."Category_Id" = PUB."Products"."Category_Id"))
© 2006 Progress Software Corporation14INNOV-7 Can you create a report in less than 15 seconds?
Supported Languages
Dutch English French German Italian Portuguese Spanish
© 2006 Progress Software Corporation15INNOV-7 Can you create a report in less than 15 seconds?
EasyAsk Review
Masks the complexity of the underlying DBMS
Allows use of accustomed terminology Enables data access to broader set of users.
© 2006 Progress Software Corporation16INNOV-7 Can you create a report in less than 15 seconds?
BIRT
Business Intelligence Reporting Tool• Eclipse plug-in – Open source
• Two Major releases
What is it?• Report creation tool
• Reports can be saved & re-executed
• Easy to use layout tool – WYSIWYG
• PDF Generation
© 2006 Progress Software Corporation17INNOV-7 Can you create a report in less than 15 seconds?
BIRT Issues
Suffers same pitfalls as all other reporting tools• SQL & Schema knowledge
But it has a query builder• Great for simple lists, BUT as more
information is required, the SQL gets complicated quickly
– Multiple tables, more complex where & group clauses
© 2006 Progress Software Corporation18INNOV-7 Can you create a report in less than 15 seconds?
BIRT Issues (cont.)
Query & report tightly coupled• Additional column requires design change
• Easy-to-add parameters to restrict the where clause, but the column display tends to be fixed.
© 2006 Progress Software Corporation19INNOV-7 Can you create a report in less than 15 seconds?
How EasyAsk Brings It All Together
EasyAsk Ad Hoc Reporting – Takes a user question and…• Computes the SQL
• Identifies additional information that is used to DYNAMICALLY create a report design
• Report design can be effected by templates (report styles)
• Refreshes the report and presents it to the user
© 2006 Progress Software Corporation20INNOV-7 Can you create a report in less than 15 seconds?
THIS IS THE MAGIC!
EACH QUERY RESULTS IN A
CUSTOM REPORT
© 2006 Progress Software Corporation21INNOV-7 Can you create a report in less than 15 seconds?
Design Information Returned By EasyAsk
EasyAsk Returns• SQL
• English column names and order
• Grouping information
• Identifies columns that need totaling
© 2006 Progress Software Corporation22INNOV-7 Can you create a report in less than 15 seconds?
TRANSLATION
ENGINE
Typical EasyAsk Data Retrieval
RETRIEVAL
ENGINE
DBMS
DICTIONARY
QUERY
SQL +
RESULTS
‘percentage of sales by state?’
© 2006 Progress Software Corporation23INNOV-7 Can you create a report in less than 15 seconds?
Ad Hoc Report Construction
DBMS
DICTIONARY
QUERY
SQL +
REPORT
TRANSLATION
ENGINE
REPORT
ENGINEREPORT
GENERATOR
DATA RET &FORMATTING
TEMPLATE
ENGINEDESIGNREPORT
RETRIEVAL ENGINE
© 2006 Progress Software Corporation24INNOV-7 Can you create a report in less than 15 seconds?
Sample Report
© 2006 Progress Software Corporation25INNOV-7 Can you create a report in less than 15 seconds?
DEMO
© 2006 Progress Software Corporation26INNOV-7 Can you create a report in less than 15 seconds?
The Little Extras
Templates• Can change look & feel to mirror your
applications
• Certain rules must be followed– Styles must have well-known names
Header, footer, defaultgroupheader/footer, groupheaderlevel1 … n, groupfooterlevel1 … n
– Must be in <install-dir>/server/reports/templates
© 2006 Progress Software Corporation27INNOV-7 Can you create a report in less than 15 seconds?
The Little Extras (cont.)
Template (cont.)• Every custom generated report can have that
look & feel
Emailing Scheduling
© 2006 Progress Software Corporation28INNOV-7 Can you create a report in less than 15 seconds?
Any Query?
Almost any query• Not crosstabs or comparisons … yet
Let’s not forget charts either.
© 2006 Progress Software Corporation29INNOV-7 Can you create a report in less than 15 seconds?
In Summary
Removes the technical hurdles (SQL, DB SCHEMA, Reporting Tool)
Reduces the burden on IT Leverages acquired wealth of information
across a broader set of users Makes life easier for users
• resets focus to making business decisions• provides EASY access to data • removes task of acquiring & formatting the
information with which to base their decisions.
EasyAsk Ad Hoc Reporting
© 2006 Progress Software Corporation30INNOV-7 Can you create a report in less than 15 seconds?
Questions?
© 2006 Progress Software Corporation31INNOV-7 Can you create a report in less than 15 seconds?
Thank you foryour time
© 2006 Progress Software Corporation32INNOV-7 Can you create a report in less than 15 seconds?