INNOV-7: Can you create a report in less than 15 seconds?

1. INNOV-7: Can you create a report in less than 15 seconds? Rakesh Godhani Software Architect

2. Agenda EasyAsk Ad Hoc Reporting • Business case / challenges • Fundamentals of EasyAsk • BIRT • How EasyAsk brings it all together • The little extras that go a long way • Q & A INNOV-7 Can you create a report in less than 15 seconds?

3. Business case / challenges - Technology • 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. INNOV-7 Can you create a report in less than 15 seconds?

4. Business case / challenges - Complicated • More data – more complex • Joins, where clause etc. • IT – Technically savvy • Resource & budget constraints • Please as many people as possible INNOV-7 Can you create a report in less than 15 seconds?

5. 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 … INNOV-7 Can you create a report in less than 15 seconds?

6. Business case / challenges - Frustration USER FRUSTRATION INNOV-7 Can you create a report in less than 15 seconds?

7. Fundamentals Of EasyAsk EasyAsk Goals • 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 INNOV-7 Can you create a report in less than 15 seconds?

8. EasyAsk is Smart • When talking about concepts, EasyAsk figures out: • Tables, columns, column values • Relationships (joins) • Formulas (e.g. age) • Grouping • Security INNOV-7 Can you create a report in less than 15 seconds?

9. EasyAsk Natural Language Ad-Hoc Query Conceptual View EasyAsk DBMS Logical View Physical View English SQL Translates Natural Language Questions into SQL Using a dictionary in which each definition shows how the word relates to the database INNOV-7 Can you create a report in less than 15 seconds?

10. Sample Interpretation (built-in) Stemmed root of Employees First & Last Names (built-in) Column of Employees (built-in) (built-in) Column of Employees Column of Employees Report Employee Name And Salary Grouped By State City INNOV-7 Can you create a report in less than 15 seconds?

11. 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 INNOV-7 Can you create a report in less than 15 seconds?

12. Sample2 Interpretation Cust First & Last Names Cust.CustID = Orders.CustID Value of Category Name (built-in) (built-in) Value of Category Name Table Who buys Dairy But Not Meat Products INNOV-7 Can you create a report in less than 15 seconds?

13. 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")) INNOV-7 Can you create a report in less than 15 seconds?

14. Supported Languages • Dutch • English • French • German • Italian • Portuguese • Spanish INNOV-7 Can you create a report in less than 15 seconds?

15. EasyAsk Review • Masks the complexity of the underlying DBMS • Allows use of accustomed terminology • Enables data access to broader set of users. INNOV-7 Can you create a report in less than 15 seconds?

16. 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 INNOV-7 Can you create a report in less than 15 seconds?

17. 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 INNOV-7 Can you create a report in less than 15 seconds?

18. 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. INNOV-7 Can you create a report in less than 15 seconds?

19. 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 INNOV-7 Can you create a report in less than 15 seconds?

20. THIS IS THE MAGIC! EACH QUERY RESULTS IN A CUSTOM REPORT INNOV-7 Can you create a report in less than 15 seconds?

21. Design Information Returned By EasyAsk • EasyAsk Returns • SQL • English column names and order • Grouping information • Identifies columns that need totaling INNOV-7 Can you create a report in less than 15 seconds?

22. Typical EasyAsk Data Retrieval ‘percentage of sales by state?’ DICTIONARY QUERY TRANSLATION ENGINE RESULTS SQL + DBMS RETRIEVAL ENGINE INNOV-7 Can you create a report in less than 15 seconds?

23. Ad Hoc Report Construction TRANSLATION REPORT ENGINE REPORT DATA RET & REPORT DESIGN REPORT FORMATTING GENERATOR ENGINE ENGINE DICTIONARY QUERY DBMS SQL + RETRIEVAL ENGINE TEMPLATE INNOV-7 Can you create a report in less than 15 seconds?

24. Sample Report INNOV-7 Can you create a report in less than 15 seconds?

25. DEMO INNOV-7 Can you create a report in less than 15 seconds?

26. 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 INNOV-7 Can you create a report in less than 15 seconds?

27. The Little Extras (cont.) • Template (cont.) • Every custom generated report can have that look & feel • Emailing • Scheduling INNOV-7 Can you create a report in less than 15 seconds?

28. Any Query? • Almost any query • Not crosstabs or comparisons … yet • Let’s not forget charts either. INNOV-7 Can you create a report in less than 15 seconds?

29. In Summary EasyAsk Ad Hoc Reporting • 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. INNOV-7 Can you create a report in less than 15 seconds?

30. Questions? INNOV-7 Can you create a report in less than 15 seconds?

31. Thank you foryour time INNOV-7 Can you create a report in less than 15 seconds?

32. INNOV-7 Can you create a report in less than 15 seconds?