際際滷

際際滷Share a Scribd company logo

Dashboard reporting in easy

Download as PPT, PDF
Sayed Yunus
Sayed Yunus

This document provides design tips and tricks for creating effective dashboards in Oracle Business Intelligence Enterprise Edition (OBIEE). It discusses overall design considerations as well as specific tips for dashboard prompts, sections, reports, saved selections, branding, downloads, and printing. Usage tracking capabilities in OBIEE are also overviewed. The document shares best practices for leveraging various OBIEE widgets and features to build dashboards that clearly summarize and filter data for users.

1 of 44
Downloaded 23 times
Dashboard Reporting: Design Tips and Tricks Melinda Rojo Kathy Kimball December 5, 2008 1
Session Overview OBIEE offers a rich array of widgets for use in designing and developing dashboards, from dashboard prompts to view selectors to column selectors. This session will share the tips and tricks weve discovered on how best to use the powerful capabilities. December 5, 2008 2
Session Topics Overall Design Tips & Tricks Dashboard Tips & Tricks Answers Tips & Tricks Dashboard Terminology Usage Tracking Resources December 5, 2008 3
Overall Design Tips & Tricks Develop and implement standards for both dashboards and requests Summarize and then drill to detail Make prototypes Make use of visual indicators where appropriate and useful Reuse! Reuse! Reuse! Prompts Requests Filters Document requests and filters to facilitate reuse and maintenance December 5, 2008 4
Dashboard Tips & Tricks Prompts Section Properties Request Properties Saved Selections Branding Download Options Printing December 5, 2008 5
Dashboard Prompts Use prompts to filter results on either an entire Dashboard or the current Dashboard page December 5, 2008 6
Dashboard Prompts Tips & Tricks Select from many operators available Use SQL Results to set choices shown Use Server Variables to set defaults Set Presentation Variables Use constrain Use multiple prompts Example Only show departments and funds that a user is interested in. But still allow them to search on any. December 5, 2008 7
Dashboard Prompts December 5, 2008 8
Dashboard Section Properties Guided Navigation Can guide users to a related set of results either by static or conditional links Drill in Place Shows the new results directly in the dashboard rather than replacing the entire dashboard page Arrange Horizontally Displays all requests in this section horizontally December 5, 2008 9
Dashboard Report Properties Show View Option shows available views of a report Default is Compound Layout Report Links Option controls links displayed under reports December 5, 2008 10
Dashboard Saved Selections Use saved selections to allow users to view dashboard pages with the their most frequently used choices Applies to many aspects of a dashboard page Filters Prompts View selections Stored on BI Server Web Catalog in /root/users/username/_selections December 5, 2008 11
Dashboard Saved Selections Tips & Tricks Add a button to all dashboard pages allowing users to clear their selections December 5, 2008 12
Dashboard Branding OBIEE can be customized to your institution using styles, skins, and custom images Tips & Tricks Name your dashboard (bg_banner.jpg) http://obiee101.blogspot.com/2008/09/obiee-portal-customization-part-1.html Change Powered by Oracle on page footer (siebelbug.gif) http://obiee101.blogspot.com/2008/10/obiee-replacing-siebelbuggif-powered-by.html December 5, 2008 13
Dashboard Download Options Download to Excel and Download to Excel 2000 behave a little differently depending on the platform and browser used We recommend Mac users use the Firefox browser and the Excel 2000 link Excel and Excel 2000 file format is really *.mht; *mhtml though the extension is .xls Youll want to save the file as a Microsoft Excel Workbook before editing the file December 5, 2008 14
Download to Excel or Excel 2000 Formulas are not downloaded. The value in a cell is downloaded as that value, not as a formula. Monetary columns download as numbers. Values with commas are formatted as numbers and retain formatting that was displayed on the Dashboard, i.e., if two decimal places are shown then those appear in Excel. Monetary columns without a comma are formatted as general, which is a number but with no specific number format. The value does not change but decimal places are suppressed if zeros appear to the right of the decimal place. Columns that are numeric and contain values with leading zeros lose the leading zeros when downloaded to Excel. December 5, 2008 15
Conditional Formatting Indicators Weve had inconsistent results using conditional formatting indicators such as a flag or bubble and the image may move when the rows are sorted Conditional formatting indicators appear as a broken images when Download to Excel 2000 is used in both Firefox and IE 7 on Windows/PC No testing has been done on Macs yet December 5, 2008 16
Download to Data Download to Data creates a CSV (Comma Separated Values) file, which includes all of the columns included in the Answers request (not just the view displayed on the screen) Formatting from the screen will not be carried over to the file Additional download results have been documented on our website: http://polydata.calpoly.edu/dashboards/faqs.html December 5, 2008 17
Dashboard Printing PDF or HTML of displayed view Headers or Footers appear in PDF if added to the Compound Layout December 5, 2008 18
Headers and Footers Cal Poly footer includes: Saved Name (name of the request) Print Date Page Number Author Name (person running the dashboard request) December 5, 2008 19
Answers Tips & Tricks Column Selector View View Selector View Narrative View Compound Layout Saved Filters Saved Data Formats Value Interaction (Navigation) December 5, 2008 20
Column Selector View Use column selectors view to allow users to dynamically change which columns appear in a request December 5, 2008 21
Column Selector View Tips & Tricks Standardize on label text such as Show Column: Color code column selector to make visible to end users Standardize on common columns and sort in same order on all requests 損 Used Advanced Tab to edit XML and copy/paste Include all members of hierarchies in column selectors so they can jump to level Add column selector to a hidden field on a Pivot to sort results (otherwise only available on table view) Uncheck automatically refresh when a new column is selected so users have GO button December 5, 2008 22
View Selectors View Use view selector view to select a specific view of the results from among the saved views December 5, 2008 23
View Selectors Tips & Tricks Standardize on label text such as Select a View: Create view to display filters Create view with instructions before displaying data Create view to easily download data December 5, 2008 24
Narrative View Use narrative view to combine text with the results of a request Useful for referencing variables December 5, 2008 25
Compound Layout View Use compound layout view to assemble different views for display on a dashboard Tips & Tricks Remove the Title view Know your boundaries (upper, lower, left, right) 損 View Boundaries 損 Report Boundaries Use multiple compound layouts for requests with multiple layouts but the same criteria December 5, 2008 26
Compound Layout December 5, 2008 27
Saved Filters Use saved filters so they may be shared with multiple requests Document shared filters in a flow chart to show dependencies on specific saved filters December 5, 2008 28
Saved Filters PolyData Dashboards: Finance Revenue and Expense Summary by Period - Budget Revenue and Expense Summary Source: Fact Summary, Filters: 4, 5 My Revenue and Expense Page Source: Fact Summary, Filters: 5, 6 Revenue and Expense Summary by Period - Encumbrance Source: Fact Summary, Filters: 5, 6 Activity Summary by Year Source: Fact Summary, Filters: 4, 5a, 11 Revenue and Expense Summary by Period Actuals Source: Fact Summary, Filters: 5, 6 My Projects Page Transactions Budget Source: Fact Budget, Filters: 7 My Budget Transactions Page Transactions Encumbrance Source: Fact Encumbrance, Filters: 7 Transactions Actuals Source: Fact Actual, Filters: 7 My PO Transactions Page My Revenue and Expense Transactions Page Project Summary Source: Fact Summary, Filters: 12, 13 My Trial Balance Page Trial Balance Source: Fact Summary, Filters: 8, 9 My Open POs Page Trial Balance Summary by Period Transactions Trial Balance Source: Fact Summary, Filters: 8, 9, 10 Source: Fact Actual, Filters: 7, 8 Open PO Summary Open PO Detail Source: Fact Encumbrance, Filters: 1, 3 My Trial Balance Transactions Page Source: Fact Encumbrance, Filters: 1, 2, 3 Filters Legend: 1: Encumbrances Criteria 2: Encumbrances Drill 3: Encumbrances Prompt 4: Revenue and Expense Criteria 5: Revenue and Expense Prompt 5a: Revenue and Expense Prompt without FY 6: Revenue and Expense Summary by Period 7: Transactions Prompt 8: Trial Balance Criteria 9: Trial Balance Prompt 10: Trial Balance Summary by Period 11. Five Years Back Prompt 12. Projects Criteria 13. Projects Prompt VisioDocument December 5, 2008 Rev. 12/3/2008 29
Saved Filters Tips & Tricks Create to match all columns in a dashboard page prompt Create to match all columns in the criteria Create to match all columns in standard column selectors Create to use for development while building new requests Order of filters matters! December 5, 2008 30
Saved Data Formats Use saved data formats to apply, save, and re-use column properties on Value Format tab, Column Format tab, and Conditional Format tabs Scope of save can vary For a specific column As a system-wide default for this column As a system-wide default for this data type Stored in an XML file in the Web Catalog in the /root/system/metadata folder December 5, 2008 31
Saved Data Formats Tips & Tricks Save the email address field to display systemwide using mail-to format Will display on all requests using that column December 5, 2008 32
Value Interaction Use value interaction to specify what should happen when a user clicks on a column heading or column value Allows users to navigate to multiple requests or dashboards If multiple navigation paths, pop-up window displays caption Provides drill-to-detail capability December 5, 2008 33
Value Interaction Source Request Set Interaction Type to Navigate Add Navigation Target to an Answers request Add caption (if multiple targets) December 5, 2008 34
Value Interaction Target Request Use filter with operator of Is Prompted Include all possible columns 損 Columns in Pivot Axis 損 Columns in Prompts 損 Column Selectors Column names must match source request EXACTLY December 5, 2008 35
Dashboard Elements December 5, 2008 36
Dashboard Terminology Dashboard content separated by Functional Areas Dashboard Pages one or more related requests Dashboard Page Prompt controls requests on dashboard page Narrative text allows static and variable text Request query Hyperlinks - hierarchical drills, drill to detail options, and links to related information Filter selection criteria for request Column Selector view different columns of data View Selector select different reports within the same request Conditional formatting using green, yellow, red symbols Presentation Catalog Folder list of content related requests December 5, 2008 37
Usage Tracking OBIEE provides a usage tracking system with the standard installation Usage Tracking includes SQL statements to create tracking user and tables/views RPD containing Usage Tracking models Web Catalog for Usage Tracking dashboard December 5, 2008 38
Usage Tracking Installing the Usage Tracking components includes: Creating a database user to host the Usage Tracking objects Creating the Usage Tracking database objects Merging master RPD with Usage Tracking RPD Altering NQSConfig.ini to enable Importing Usage Tracking web catalog components Reference Blog for step-by-step instructions http://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html December 5, 2008 39
Usage Tracking December 5, 2008 40
Usage Tracking December 5, 2008 41
Resources Oracle Metalink 3.0 http://metalink3.oracle.com Oracle Business Intelligence obiee-101 http://obiee101.blogspot.com Business Intelligence Oracle http://oraclebizint.wordpress.com FAQs for PolyData Dashboards http://polydata.calpoly.edu/dashboards/faqs.html December 5, 2008 42
Questions? December 5, 2008 43
Contact OBIEE Technical Conference: http://polydata.calpoly.edu/dashboards/obiee_conf/index.html Email: polydata@calpoly.edu December 5, 2008 44

More Related Content

Dashboard reporting in easy

  • 1. Dashboard Reporting: Design Tips and Tricks Melinda Rojo Kathy Kimball December 5, 2008 1
  • 2. Session Overview OBIEE offers a rich array of widgets for use in designing and developing dashboards, from dashboard prompts to view selectors to column selectors. This session will share the tips and tricks weve discovered on how best to use the powerful capabilities. December 5, 2008 2
  • 3. Session Topics Overall Design Tips & Tricks Dashboard Tips & Tricks Answers Tips & Tricks Dashboard Terminology Usage Tracking Resources December 5, 2008 3
  • 4. Overall Design Tips & Tricks Develop and implement standards for both dashboards and requests Summarize and then drill to detail Make prototypes Make use of visual indicators where appropriate and useful Reuse! Reuse! Reuse! Prompts Requests Filters Document requests and filters to facilitate reuse and maintenance December 5, 2008 4
  • 5. Dashboard Tips & Tricks Prompts Section Properties Request Properties Saved Selections Branding Download Options Printing December 5, 2008 5
  • 6. Dashboard Prompts Use prompts to filter results on either an entire Dashboard or the current Dashboard page December 5, 2008 6
  • 7. Dashboard Prompts Tips & Tricks Select from many operators available Use SQL Results to set choices shown Use Server Variables to set defaults Set Presentation Variables Use constrain Use multiple prompts Example Only show departments and funds that a user is interested in. But still allow them to search on any. December 5, 2008 7
  • 9. Dashboard Section Properties Guided Navigation Can guide users to a related set of results either by static or conditional links Drill in Place Shows the new results directly in the dashboard rather than replacing the entire dashboard page Arrange Horizontally Displays all requests in this section horizontally December 5, 2008 9
  • 10. Dashboard Report Properties Show View Option shows available views of a report Default is Compound Layout Report Links Option controls links displayed under reports December 5, 2008 10
  • 11. Dashboard Saved Selections Use saved selections to allow users to view dashboard pages with the their most frequently used choices Applies to many aspects of a dashboard page Filters Prompts View selections Stored on BI Server Web Catalog in /root/users/username/_selections December 5, 2008 11
  • 12. Dashboard Saved Selections Tips & Tricks Add a button to all dashboard pages allowing users to clear their selections December 5, 2008 12
  • 13. Dashboard Branding OBIEE can be customized to your institution using styles, skins, and custom images Tips & Tricks Name your dashboard (bg_banner.jpg) http://obiee101.blogspot.com/2008/09/obiee-portal-customization-part-1.html Change Powered by Oracle on page footer (siebelbug.gif) http://obiee101.blogspot.com/2008/10/obiee-replacing-siebelbuggif-powered-by.html December 5, 2008 13
  • 14. Dashboard Download Options Download to Excel and Download to Excel 2000 behave a little differently depending on the platform and browser used We recommend Mac users use the Firefox browser and the Excel 2000 link Excel and Excel 2000 file format is really *.mht; *mhtml though the extension is .xls Youll want to save the file as a Microsoft Excel Workbook before editing the file December 5, 2008 14
  • 15. Download to Excel or Excel 2000 Formulas are not downloaded. The value in a cell is downloaded as that value, not as a formula. Monetary columns download as numbers. Values with commas are formatted as numbers and retain formatting that was displayed on the Dashboard, i.e., if two decimal places are shown then those appear in Excel. Monetary columns without a comma are formatted as general, which is a number but with no specific number format. The value does not change but decimal places are suppressed if zeros appear to the right of the decimal place. Columns that are numeric and contain values with leading zeros lose the leading zeros when downloaded to Excel. December 5, 2008 15
  • 16. Conditional Formatting Indicators Weve had inconsistent results using conditional formatting indicators such as a flag or bubble and the image may move when the rows are sorted Conditional formatting indicators appear as a broken images when Download to Excel 2000 is used in both Firefox and IE 7 on Windows/PC No testing has been done on Macs yet December 5, 2008 16
  • 17. Download to Data Download to Data creates a CSV (Comma Separated Values) file, which includes all of the columns included in the Answers request (not just the view displayed on the screen) Formatting from the screen will not be carried over to the file Additional download results have been documented on our website: http://polydata.calpoly.edu/dashboards/faqs.html December 5, 2008 17
  • 18. Dashboard Printing PDF or HTML of displayed view Headers or Footers appear in PDF if added to the Compound Layout December 5, 2008 18
  • 19. Headers and Footers Cal Poly footer includes: Saved Name (name of the request) Print Date Page Number Author Name (person running the dashboard request) December 5, 2008 19
  • 20. Answers Tips & Tricks Column Selector View View Selector View Narrative View Compound Layout Saved Filters Saved Data Formats Value Interaction (Navigation) December 5, 2008 20
  • 21. Column Selector View Use column selectors view to allow users to dynamically change which columns appear in a request December 5, 2008 21
  • 22. Column Selector View Tips & Tricks Standardize on label text such as Show Column: Color code column selector to make visible to end users Standardize on common columns and sort in same order on all requests 損 Used Advanced Tab to edit XML and copy/paste Include all members of hierarchies in column selectors so they can jump to level Add column selector to a hidden field on a Pivot to sort results (otherwise only available on table view) Uncheck automatically refresh when a new column is selected so users have GO button December 5, 2008 22
  • 23. View Selectors View Use view selector view to select a specific view of the results from among the saved views December 5, 2008 23
  • 24. View Selectors Tips & Tricks Standardize on label text such as Select a View: Create view to display filters Create view with instructions before displaying data Create view to easily download data December 5, 2008 24
  • 25. Narrative View Use narrative view to combine text with the results of a request Useful for referencing variables December 5, 2008 25
  • 26. Compound Layout View Use compound layout view to assemble different views for display on a dashboard Tips & Tricks Remove the Title view Know your boundaries (upper, lower, left, right) 損 View Boundaries 損 Report Boundaries Use multiple compound layouts for requests with multiple layouts but the same criteria December 5, 2008 26
  • 28. Saved Filters Use saved filters so they may be shared with multiple requests Document shared filters in a flow chart to show dependencies on specific saved filters December 5, 2008 28
  • 29. Saved Filters PolyData Dashboards: Finance Revenue and Expense Summary by Period - Budget Revenue and Expense Summary Source: Fact Summary, Filters: 4, 5 My Revenue and Expense Page Source: Fact Summary, Filters: 5, 6 Revenue and Expense Summary by Period - Encumbrance Source: Fact Summary, Filters: 5, 6 Activity Summary by Year Source: Fact Summary, Filters: 4, 5a, 11 Revenue and Expense Summary by Period Actuals Source: Fact Summary, Filters: 5, 6 My Projects Page Transactions Budget Source: Fact Budget, Filters: 7 My Budget Transactions Page Transactions Encumbrance Source: Fact Encumbrance, Filters: 7 Transactions Actuals Source: Fact Actual, Filters: 7 My PO Transactions Page My Revenue and Expense Transactions Page Project Summary Source: Fact Summary, Filters: 12, 13 My Trial Balance Page Trial Balance Source: Fact Summary, Filters: 8, 9 My Open POs Page Trial Balance Summary by Period Transactions Trial Balance Source: Fact Summary, Filters: 8, 9, 10 Source: Fact Actual, Filters: 7, 8 Open PO Summary Open PO Detail Source: Fact Encumbrance, Filters: 1, 3 My Trial Balance Transactions Page Source: Fact Encumbrance, Filters: 1, 2, 3 Filters Legend: 1: Encumbrances Criteria 2: Encumbrances Drill 3: Encumbrances Prompt 4: Revenue and Expense Criteria 5: Revenue and Expense Prompt 5a: Revenue and Expense Prompt without FY 6: Revenue and Expense Summary by Period 7: Transactions Prompt 8: Trial Balance Criteria 9: Trial Balance Prompt 10: Trial Balance Summary by Period 11. Five Years Back Prompt 12. Projects Criteria 13. Projects Prompt VisioDocument December 5, 2008 Rev. 12/3/2008 29
  • 30. Saved Filters Tips & Tricks Create to match all columns in a dashboard page prompt Create to match all columns in the criteria Create to match all columns in standard column selectors Create to use for development while building new requests Order of filters matters! December 5, 2008 30
  • 31. Saved Data Formats Use saved data formats to apply, save, and re-use column properties on Value Format tab, Column Format tab, and Conditional Format tabs Scope of save can vary For a specific column As a system-wide default for this column As a system-wide default for this data type Stored in an XML file in the Web Catalog in the /root/system/metadata folder December 5, 2008 31
  • 32. Saved Data Formats Tips & Tricks Save the email address field to display systemwide using mail-to format Will display on all requests using that column December 5, 2008 32
  • 33. Value Interaction Use value interaction to specify what should happen when a user clicks on a column heading or column value Allows users to navigate to multiple requests or dashboards If multiple navigation paths, pop-up window displays caption Provides drill-to-detail capability December 5, 2008 33
  • 34. Value Interaction Source Request Set Interaction Type to Navigate Add Navigation Target to an Answers request Add caption (if multiple targets) December 5, 2008 34
  • 35. Value Interaction Target Request Use filter with operator of Is Prompted Include all possible columns 損 Columns in Pivot Axis 損 Columns in Prompts 損 Column Selectors Column names must match source request EXACTLY December 5, 2008 35
  • 37. Dashboard Terminology Dashboard content separated by Functional Areas Dashboard Pages one or more related requests Dashboard Page Prompt controls requests on dashboard page Narrative text allows static and variable text Request query Hyperlinks - hierarchical drills, drill to detail options, and links to related information Filter selection criteria for request Column Selector view different columns of data View Selector select different reports within the same request Conditional formatting using green, yellow, red symbols Presentation Catalog Folder list of content related requests December 5, 2008 37
  • 38. Usage Tracking OBIEE provides a usage tracking system with the standard installation Usage Tracking includes SQL statements to create tracking user and tables/views RPD containing Usage Tracking models Web Catalog for Usage Tracking dashboard December 5, 2008 38
  • 39. Usage Tracking Installing the Usage Tracking components includes: Creating a database user to host the Usage Tracking objects Creating the Usage Tracking database objects Merging master RPD with Usage Tracking RPD Altering NQSConfig.ini to enable Importing Usage Tracking web catalog components Reference Blog for step-by-step instructions http://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html December 5, 2008 39
  • 42. Resources Oracle Metalink 3.0 http://metalink3.oracle.com Oracle Business Intelligence obiee-101 http://obiee101.blogspot.com Business Intelligence Oracle http://oraclebizint.wordpress.com FAQs for PolyData Dashboards http://polydata.calpoly.edu/dashboards/faqs.html December 5, 2008 42
  • 44. Contact OBIEE Technical Conference: http://polydata.calpoly.edu/dashboards/obiee_conf/index.html Email: polydata@calpoly.edu December 5, 2008 44