1 / 33

WebFOCUS Friday for Beginners

WebFOCUS Friday for Beginners. “Ad hoc” Querying and Reporting with WebFOCUS. Twanette Jurd 9 April 2010. My theory (or theories) …. BI Tools vs. BI Applications. Technology is not the answer …. the result of applying the technology provides the answers.

lionel
Download Presentation

WebFOCUS Friday for Beginners

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. WebFOCUS Friday for Beginners “Ad hoc” Querying and Reporting with WebFOCUS Twanette Jurd 9 April 2010

  2. My theory (or theories) ….

  3. BI Tools vs. BI Applications • Technology is not the answer …. • the result of applying the technology provides the answers. • Point and Click, Drag and Drop, and an intuitive and “functionality-rich” interface may be the requirement for a good BI tool …. • but a good BI application is one that provides a faster, easier way to get the information that is needed. Most users need applications, not tools

  4. How ad hoc does ad hoc need to be? • WebFOCUS has a number of really good ad hoc “tools” which users could use to build their own queries and reports. • But is that really what users want or need? Will the broader user base use them? • Furthermore, often data is simply too complex to simplify! And therefore we would not always be able to give users out-of-the-box tools to simply build their own queries and reports.

  5. Understand the real requirements • Perhaps the project team is envisioning a totally ad hoc environment, whereas business management is more interested in the delivery of standardized analysis? • We need to make sure the BI implementation’s deliverable matches the business demand and expectations – obviously! Self-service parameter-driven reports allow users to easily retrieve what they want in the format that they need, with limited training

  6. In support of my theory ….

  7. Information Builders White Paper • Assumption that the average business user has the know-how or the time to use BI tools • An end-user market that is flooded with misguided hype from the vendor community, indicating that, “BI tools are for everyone” • Users don’t have the time to work with a BI tool and navigate a data warehouse to produce the information they need

  8. Anecdote by Ralph Kimball • Ralph Kimball, an expert on data warehousing, says the following in his book, The Data Warehouse Toolkit; 2nd Edition: “The majority of the user base likely will access the data via pre-built parameter-driven analytic applications. Approximately 90 to 95 percent of the potential users will be served by these canned applications that are essentially finished templates that do not require users to construct relational queries directly.”

  9. WebFOCUS does ad hoc …. very well !!

  10. With Dialogue Manager you can parameterize virtually anything • WebFOCUS isn’t just a tool – it is a complete toolbox • Understand all the pieces: • APP commands, SET commands • CHECK FILE, USE • JOIN, DEFINE, TABLE, GRAPH, MATCH • MODIFY • And especially: • Dialogue Manager • Dialogue Manager • Dialogue Manager Don’t use a hammer to tighten the screw!

  11. Dialogue Manager Amper VariablesUser Variables • &variable • Single ampersand (&) precedes variable name • In effect throughout a single procedure • &&variable • Remain in effect throughout the FOCUS session • Change with -SET &&variable • Cleared by the LET CLEAR command • In a non-persistent connection, set in edasprof, or controlled by IBIF_persistentamp setting • &variable.&index • Indexed variables • Multiple selections for the same variable name

  12. Dialogue Manager Amper VariablesNames and Values • &variable names • Each name from 1-100 characters • Case sensitive • Variable values • Variables may be from 1 - 32K in length • Stored as alphanumeric values • Interpreted as integer or alphanumeric based on usage • SET DMPRECISION introduces decimal values

  13. Dialogue Manager Amper Variables System Variables • &DATE MM/DD/YY • &DATEYYMD YYYY/MM/DD • &YMD YYMMDD • &DMY DDMMYY • &MDY MMDDYY • &TOD HH.MM.SS • &FOCFOCEXEC <focexecname> • &FOCREL WebFOCUS release number • …and many more, check the manual.

  14. Dialogue Manager Amper Variables Statistical Variables • TABLE Command • &LINES, &RECORDS, &BASEIO, &FOCERRNUM • Operating System Command • &RETCODE • -READ • &IORETURN • Referencing a statistical variable implies –RUN • Needs execution to get a value

  15. Dialogue Manager Amper Variables Supplying Values for User Variables • EX focexec variable1=value1,variable2=value2 • -DEFAULT • -SET • -READ • -HTMLFORM

  16. DEFINE FILE CAR GP_PCT/P7.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100 ; END TABLE FILE CAR HEADING "Car Catalog" PRINT COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST GP_PCT ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE FPS_STYLE ENDSTYLE END • Table file car …. No parameters

  17. DEFINE FILE CAR GP_PCT/P7.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100 ; END TABLE FILE CAR HEADING "Car Catalog" PRINT DEALER_COST BY COUNTRY BY CAR BY MODEL ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE FPS_STYLE ENDSTYLE END • Table file car …. No parameters

  18. -DEFAULTH FP_COUNTRY = 'FOC_NONE' -SET &FP_COUNTRY_HDR = IF &FP_COUNTRY EQ 'FOC_NONE' THEN - 'All Countries' ELSE &FP_COUNTRY ; DEFINE FILE CAR GP_PCT/P7.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100 ; END TABLE FILE CAR HEADING "Car Catalog for &FP_COUNTRY_HDR " PRINT DEALER_COST BY COUNTRY BY CAR BY MODEL WHERE COUNTRY EQ '&FP_COUNTRY' ; ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE FPS_STYLE ENDSTYLE END • Table file car …. No parameters &FP_COUNTRY = 'ENGLAND'

  19. -DEFAULTH FP_BYFIELD = 'FOC_NONE' DEFINE FILE CAR GP_PCT/P7.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100 ; END TABLE FILE CAR HEADING "Car Catalog for &FP_COUNTRY_HDR " PRINT DEALER_COST BY &FP_BYFIELD NOPRINT BY COUNTRY BY CAR BY MODEL WHERE COUNTRY EQ '&FP_COUNTRY' ; ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE FPS_STYLE ENDSTYLE END • Table file car …. No parameters &FP_BYFIELD = 'HIGHEST DEALER_COST'

  20. -DEFAULTH FP_PRINTFIELD = 'DEALER_COST' DEFINE FILE CAR GP_PCT/P7.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100 ; END TABLE FILE CAR HEADING "Car Catalog for &FP_COUNTRY_HDR " PRINT &FP_PRINTFIELD BY &FP_BYFIELD NOPRINT BY COUNTRY BY CAR BY MODEL WHERE COUNTRY EQ '&FP_COUNTRY' ; ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE FPS_STYLE ENDSTYLE END • Table file car …. No parameters &FP_PRINTFIELD = ‘GP_PCT'

  21. -SET &FP_PRINTFIELD = 'DEALER_COST' ; -SET &FP_COUNTRY = 'ENGLAND' ; -SET &FP_BYFIELD = 'SEATS' ; DEFINE FILE CAR GP_PCT/P7.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100 ; END TABLE FILE CAR HEADING "Car Catalog for &FP_COUNTRY_HDR " PRINT &FP_PRINTFIELD BY &FP_BYFIELD NOPRINT BY COUNTRY BY CAR BY MODEL WHERE COUNTRY EQ '&FP_COUNTRY' ; ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE FPS_STYLE ENDSTYLE END • Table file car …. No parameters

  22. Architectural Considerations • Standards: • File names (fexes, masters, stylesheets etc.) • Amper variable (parameter) names • Program labels • Report / output formats • Styles and stylesheets • Program Headers / Documentation • Re-usable / generic code stored in “-INCLUDE” files • Parameter forms / screens: • Consistent layout • Logical flow • Ability to save reports variants (i.e. save parameters) • Navigation: • Consistent • Logical • Fast navigation: “favorites” and “search” functionality

  23. Ability to search for Menu Options Manage Report Variants “Favorites” for fast navigation

  24. Context menu per menu option

  25. Parameterise selections / filters

  26. Parameterise “measure” i.e. which “SUM” field

  27. Parameterise grouping, sort order and navigation

  28. Parameterise report and graph format

  29. Parameterise output destination

  30. Cutting Edge StratWare • Parameter-driven reporting templates sharing a standard "look-and-feel", significantly reducing the end-user learning curve for receiving information • Standardised look and feel for each report selection and display output • Enablement of meaningful analysis with detailed drill-down to any desired depth • Enhanced flexibility to consider multiple scenarios, enabled by powerful parametric functionality And with WebFOCUS …. You can do it too!

More Related