Hidden Features of Brio Intelligence

1. Hidden Features of Brio Intelligence Mark OstroffPrincipal Systems Consultant

2. Morgan: Develop a new agenda format slide Agenda Unused Documented Features Feature Side-Effects “Undocumented” Features

3. Unused Documented Features • Creating global JS code • Incremental Drilling • Creating reusable custom metadata settings • Writing back to the database (under strict code control, of course) • Trapping client run-time errors • “Hidden” Brio Object Model items • Using the BRIOUNIQ repository table • Setting the ODS max. processing memory B R I O C O N F I D E N T I A L

4. Creating global JS code • Problem: • How to define JS code once that applies to all BQY documents • Solution: • Create a Custom Menu • How to: • Define using Tools | Customize menu • Terminate all code lines with semi-colon • No comments allowed • Definitions stored in BQTOOLS.INI B R I O C O N F I D E N T I A L

5. Incremental Drilling • Problem: • How to provide drilling without fetching huge amounts of data • Solution: • Combine pre-defined drilling with Drill-to-Detail • How to: • Set a Topic or MetaTopic as a Dimension • Turn on the Drill-to-Detail option • Create a query and change the data function of the facts to be a summary function B R I O C O N F I D E N T I A L

6. Creating Reusable Metadata Settings • Problem: • How to set up custom metadata settings so they can be reused • Solution: • Add the settings to the BQMETA0.INI file so they appear in the MetaData Wizard • How to: • Use the Custom setting in the MetaData Wizard to create the initial settings • Once the settings are working, add them to the BQMETA0.INI file (and make a backup copy) • Set up your network to copy the INI file to content creators’ desktops B R I O C O N F I D E N T I A L

7. Writing back to the database Under strict code control, of course • Problem: • Need to update the data from within a BQY document • Solution: • Use the SendSQL() method • How to: • Create a dashboard application that uses the SendSQL() method • Control the database connection • Control what fields can be updated • Use exception handling and transaction control • Validate the new values before writing to the database B R I O C O N F I D E N T I A L

8. Trapping run-time errors • Problem: • Need to control how run-time errors are handled • Solution: • Use TRY-CATCH exception handling • How to: • Wrap any code that might generate an error at run-time inside a try{} catch {} block • You can also use a try{} finally{} block for global cleanup • Most common usage: • Trapping DB connection errors B R I O C O N F I D E N T I A L

9. “Hidden” Brio Object Model Items • Problem: • Not all BOM Objects appear in the Object Browser • Solution: • Use the Help system and the Brio Object Model & EIS Development Reference Manual • Why: • The Object Browser only displays objects that exist • Since all JS code is developed in BQ Designer or Explorer, none of the Insight/QuickView/FreeView objects will exist during development • Hence, no web-only objects will appear in the Object Browser B R I O C O N F I D E N T I A L

10. Using the BRIOUNIQ Repository Table • Problem: • Heavily used Brio servers with multiple document publishers may create duplicate ID numbers • Solution: • BRIOUNIQ table was added in v6.2.3 • Server upgrades will not auto-create the BRIOUNIQ table • How to: CREATE TABLE BRIOUNIQ (UNIQUE_ID type) INSERT INTO BRIOUNIQ (UNIQUE_ID) VALUES (value) Where "type" is the appropriate integer numeric data type for the database server and "value" is the new starting unique ID value. B R I O C O N F I D E N T I A L

11. Setting ODS Max Processing Memory • Problem: • How to optimize ODS performance when dealing with larger BQY file result sets • Solution: • Increase the Max Memory per Process setting • How to: • Launch the Brio Server Admin Tool • Right-click on the ODS name, choose Modify • Change the Max Memory on the Settings tab or • Hand edit the ODS.INI file and change the BQ_MAXIMUM_PROCESSING_MEMORY param B R I O C O N F I D E N T I A L

12. Morgan: Develop a new agenda format slide Agenda Unused Documented Features Feature Side-Effects “Undocumented” Features

13. Feature Side-Effects Using existing features in new ways • Creating JS routines that respond to changes • Aggregate navigation • Eliminating fields from the drill path on-the-fly • Exporting a section vs. a document • Surfacing the Windows File-Save dialog • Using alternative color settings • Showing all numbers as positive, yet have correct totals • Creating self-maintaining BCS messages • Optimizing ODS/BCS Repository access B R I O C O N F I D E N T I A L

14. Creating generic JS routines • Problem: • Need to create generic JavaScript so users can change section names, add new sections, etc. without breaking the code • Solution: • Reference everything by array index and type code constant • How to: • Everything has a Type property • Every collection can be used as an array B R I O C O N F I D E N T I A L

15. Aggregate navigation • Problem: • Need to auto-adjust SQL query to access aggregate tables when appropriate • Solution: • Use the OnPreProcess() method to modify the SQL that gets sent to the database server • How to: • Add JS code to OnPreProcess to parse the SQL to determine how to modify • Save the original SQL so it can be restored in the OnPostProcess() method B R I O C O N F I D E N T I A L

16. Eliminating drill-path fields on-the-fly • Problem: • Only want certain fields to appear in the drill path without creating a Table section (since that would increase BQY file size due to data duplication) • Solution: • Use the OnPostProcess() method to remove fields from the Results section • How to: • Use the Columns[“xxx”].Remove() method • Use the OnPreProcess() method to add the fields back in so computed items calculate properly B R I O C O N F I D E N T I A L

17. Exporting a section vs. a document Controlling HTML exports • Problem: • Want to produce HTML that looks like a BQY file • Solution: • Use Export as Web Page instead of Export to HTML • How to: • Interactively: • Use File | Export | Document as Web Page • In JavaScript: • For Export as Web Page, use the ActiveDocument.Export() method • For Export to HTML, use the ActiveDocument.Sections[“xxx”].Export() method B R I O C O N F I D E N T I A L

18. Surfacing the Windows File-Save dialog • Problem: • Do not want to hard code the export type or location • Solution: • Use the Export() method without parameters • How to: • Code your Export() method with no values within the function’s parentheses: ActiveDocument.Export() ActiveDocument.Sections[xxx].Export() B R I O C O N F I D E N T I A L

19. Using Alternative Color Settings • Problem: • Need to set an EIS object to a color other that the standard BqColorType settings • Solution: • Use the decimal equivalent of the RGB code • How to: • MyColor = <favorite RGB number> • Rect1.Fill.Color = MyColor • Can use 0x### syntax for Hex-based numbers B R I O C O N F I D E N T I A L

20. Showing all numbers as positive While having correct totals • Problem: • Some reports need to show both debits and credits as positive numbers, but the combined numbers should still create proper totals • Solution: • Create a custom number format where positive and negative numbers display the same way • How to: • Use Format | Number menu, then select “Custom” from the category list • Define the format as: #,##0.00;#,##0.00 or: $#,##0.00;$#,##0.00 B R I O C O N F I D E N T I A L

21. Creating self-maintaining BCS messages • Problem: • Don’t want to redefine BCS jobs when email recipients change • Solution: • Use email groups to send BCS messages • How to: • Assign the BCS to a user account • Define email groups for that user account • When defining BCS jobs, always use email groups instead of individual email addresses B R I O C O N F I D E N T I A L

22. Optimizing ODS/BCS Repository access • Problem: • Need to optimize the speed of accessing the Brio Server Repository • Solution: • Limit the Repository OCE to only access Brio Repository tables • How to: • Modify the Repository OCE, using Advanced Options • Set filters to limit access based on: • Owner of the Repository tables • Table Names that start with BRIO or V7_ B R I O C O N F I D E N T I A L

23. Feature Side-Effects Functionality via OLE Automation • Formatted exports • Mailing labels • Slideshows with data B R I O C O N F I D E N T I A L

24. Formatted Exports • Problem: • Need to control the format of data exported to, say, Excel • Solution: • Use OLE Automation instead of Export • How to: • Create an OLE Automation Object • Copy the selected section to the Windows Clipboard • Set the Paste location and paste the data into the target application • Use OLE Automation commands to format the target B R I O C O N F I D E N T I A L

25. Mailing Labels • Problem: • Need to create mailing labels from Brio query results • Solution: • Leverage the mail merge capabilities of your word processor • How to: • Create a generic mail merge document, using a standard CSV file as the data source • In Brio, use OLE Automation to export the data set to the same CSV file name • Use OLE Automation to then launch the word processor and execute the mail merge B R I O C O N F I D E N T I A L

26. Slideshows with Data • Problem: • Need to dynamically create slideshows with Brio data in them • Solution: • Use OLE Automation in conjunction with pre-defined slideshow template files • How to: • Copy the data to the Windows Clipboard • Add a new template-based slide to the slideshow • Paste the data into the new slide • Use OLE Automation to control any needed formatting B R I O C O N F I D E N T I A L

27. Feature Side-Effects Integration with other Brio products • Report surfing • Creating a BQY Job Factory in Brio Portal • Creating BQY-based Portal exception alerts B R I O C O N F I D E N T I A L

28. Report Surfing • Problem: • Need to leverage the power of SQR for querying large data sets, but still allow analysis via Brio Insight • Solution: • Set the SQR code to output a BQD file • How to: • A BQD file is a CSV file with a special header • Use the <javascript> and </javascript> tags to create an OnStartUp() method B R I O C O N F I D E N T I A L

29. Creating a Portal BQY Job Factory • Problem: • Need to execute BQY files under Portal Job Factory control • Want to provide an HTML Parameter Collection Form • Solution: • Create an SQR job that launches BrioQuery • How to: • Publish a generic SQR program that gathers parameters and launches the Brio Intelligence executable B R I O C O N F I D E N T I A L

30. Creating Portal Exceptions from BQY’s • Problem: • Need to generate exception alerts from BQY files • Solution: • Execute the BQY from a Portal Job, and have the BQY file write the exception file as part of its execution • How to: • Create an OnShutDown() method that checks the results for exception conditions • Write a properly formatted OUTPUT.PROPERTIES file if an exception is detected B R I O C O N F I D E N T I A L

31. Feature Side-Effects Integration with other software products • Improving BQY performance via virus scan • Using your browser to send email B R I O C O N F I D E N T I A L

32. Improving BQY Performance Via proper Virus Scan setup • Problem: • Brio takes a long time to launch • Solution: • Make sure your virus scanning software does NOT check on Brio INI files • How to: • Eliminate the following files from your virus scans: • BQFORMAT.INI • BQMETA0.INI • BRIOQPLG.INI • BRIOQRY6.INI B R I O C O N F I D E N T I A L

33. Using your Browser to send Email • Problem: • Want to send email without using OLE Automation • Solution: • Use the MailTo command in your browser • How to: • Use the Application.Shell() method to invoke the browser • In the command line parameter option, use the appropriate mailTo parameters to build the email B R I O C O N F I D E N T I A L

34. Morgan: Develop a new agenda format slide Agenda Unused Documented Features Feature Side-Effects “Undocumented” Features

35. “Undocumented” Features Features not found in the standard documentation set • Adding custom functions to Computed Items • Bulk processing of BQY documents • Error logging • Passing parameters into a BQY file • Optimizing ODS performance • Additional ODS Settings • Additional BCS Settings • Bulk updating of BCS job passwords B R I O C O N F I D E N T I A L

36. Adding Custom Functions to Computed Items • Problem: • Need to add new functions to those available for use by Computed Items • Solution: • Modify the base classes in the OnStartup() method • How to: • Define the function as a member of the Number or String class • Use the JavaScript “prototype” designation in the function definition • Use FieldName.Function() syntax when creating the Computed Item B R I O C O N F I D E N T I A L

37. Bulk Processing of BQY Documents Integrating with 3rd party products / systems • Problem: • Need to automate the running of one or more BQY files (from a process other than the Broadcast Server) • Solution: • Execute Brio using command line options • How to: • Create a JavaScript file of OnStartup() commands • Launch the Brio executable as a daemon • Windows: • brioqry.exe -daemon "a b" -nosplash -jscript script.js • Unix: (requires Xvfb or Xvnc) • DISPLAY=‘hostname’:1.0; export DISPLAY; • bq -nosplash -jscript script.js B R I O C O N F I D E N T I A L

38. Error Logging • Problem: • Need to document all run-time errors • Solution: • Use DbgPrint to capture all execution messages • How to: • Create a blank file named DbgPrint (no extension) • Place in same directory as executable • Brio Clients: Brio Client Program Directory • Brio Plug-ins: Browser Executable’s Directory • Broadcast Server: BCS Program Directory • OnDemand Server: • Set BQ_START_LOG=debug • Stop and Restart the ODS B R I O C O N F I D E N T I A L

39. Passing Parameters into a BQY File Controlling how the ODS operates • Problem: • Need to modify the execution of a BQY based on values passed to it • Solution: • Use the OnStartUp() method to parse the URL line for parameters • How to: • Call the ODS document with &ParamName=value structure • Capture the parameters with Session.URL[“xxx”] • Parse each multi-value parameter with split() method B R I O C O N F I D E N T I A L

40. Optimizing ODS Performance • Problem: • ODS performance slow under moderate/heavy loads • Solution: • Increase the Java memory settings • How to: • Add “-Xms64m -Xmx256m” to the ODS and ODS Process Factory command lines • Insert after the reference to the java executable • Windows: • In RegEdit, modify cmdline and ProcFactCmdLine params • Unix: • Edit the ODS.START shell script B R I O C O N F I D E N T I A L

41. Additional ODS Settings Used in ODS.INI • This INI file is stored in the Server sub-directory under the install location • All other ODS.INI settings can be found in the Brio Server Administrator’s Guide • Backup the ODS.INI file before making changes • Backup the ODS.PWD file as well B R I O C O N F I D E N T I A L

42. Additional ODS CGI Settings Used in /etc/ODSCGI.INI (Unix) • Windows servers use the same parameters, but store them in the Windows Registry. • Make sure you have backed up your registry before making any changes. • Take extreme care when modifying any settings in the Registry. B R I O C O N F I D E N T I A L

43. Additional BCS Settings Used in BQServ1.INI B R I O C O N F I D E N T I A L

44. Bulk Updating of BCS Passwords • Problem: • When DB passwords expire, need to update BCS without having to hand edit every job • Solution: • Create a Password table for the BCS to use • Tell the BCS to use this table for its passwords • How to: • Add the PwdSQL parameter to each Processing OCE in the BQServ1.INI file [OCE 1] PwdSQL=Select UPWD from MYPWDS where UID=:USER and BRIOOCE=:OCE and BCS=:SERVER • Control access to the password table via standard RDBMS Grants and Revokes B R I O C O N F I D E N T I A L

45. Hidden Features of Brio Intelligence Mark OstroffPrincipal Systems Consultant