OBIEE Reports and Dashboards Best Practices

 

 Dashboard - Performance

Practice Reasoning Avoid designing dashboards that return too much data Dashboards should show summary data with ability to drill into or link to detailed information with more reports. Avoid designing requests that use overly complex queries Derived metrics should be in the data source layer first, the metadata layer second, and report layer last. Use guided navigation to link to smaller dashboards Guided navigation can be set at the dashboard or at the report level. Ensure global filters have a default value Setting value to nothing or all will return all the data and impact performance. Setting a default values creates a smaller data set enhancing readability. All embedded reports on a dashboard issue their queries at dashboard load time Take this into consideration when choosing to embed large reports or link to them. Use multiple dashboard pages to separate large reports from each other when performance becomes an issue.
Dashboard - Readability Practice Reasoning Use fewer columns More columns in a request are not necessarily better. OBI is not a spreadsheet and should be viewed as such. Simplicity can tell the reader what is going on in one look. Use charts to simplify views of data, not to confuse them Charts can allow for easier understanding of data. Common mistake is to add charts to fill up white space or show different metrics than the associated table. Use conditional formatting to focus attention on data outside given parameters Do not just produce reports. Drive users towards actionable data based upon conditions. Eliminate white space and scrolling wherever possible Set report widths to 100%. If you have to scroll - your report is too large. Use alternating row formatting, 'Green Bar', on grids to enhance readability.
Dashboard - Security Practice Reasoning Use security groups to filter out irrelevant or unwanted dashboards Focus on providing data relevant to a user's role. Ask why a user should see a dashboard instead of why not. Organize dashboards by roles to help users locate the data they are interested in Executive dashboard. Management dashboard. Individual Dashboard. Use groups for access instead of individuals Assigning ownership of an object should be at the group level. Create parent groups with sub groups as members to control access. Individuals assigned to objects should be the exception.
Metadata - Business Model and Mapping (BMM) Layer Practice Reasoning Avoid fact-to-fact joins Poor performing. Use conforming dimensions to solve fact-to-fact requests. Prefer ETL sourced derived metrics to BMM Layer derived metrics Calculate once vs. on every query. Common mistake is to do all the work in the BMM Layer. Databases can perform functions more efficiently than Oracle Business Intelligence's application server. Rename columns Renaming columns at the BMM Layer saves time and is easier to manage. Avoids the creations of alias column names in the Presentation Layer. Alias column names can cause confusion in troubleshooting situations. Use complex joins whenever possible Provides Oracle Business Intelligence the ability to the choose the logical table sources that will produce the most efficient SQL. Metadata - Performance Practice Reasoning Disable logging in production Results in performance degradation. When logging is needed for troubleshooting, pre-production environments can be used. Metadata - Physical Layer Practice Reasoning Establish foreign key relationships before moving object to BMM layer BMM layer inherits foreign key relationships when object are dragged from Physical Layer to BMM Layer. Establish uniform naming conventions for tables e.g Dim_tablename, Fact_tablename, View_tablename. Allows for a cleaner, more organized repository. Allows developers and support individuals to easily determine object intent. Nullable flag on physical columns must be set correctly Controls how SQL is generated. Properly set flag enhances performance. Use table aliases at all times Associates one physical object to one business model object. Used to eliminate circular joins. Allows physical tables to be easily managed in shared environments. Metadata - Presentation Layer Practice Reasoning Organize presentation tables by entities that make sense to business users Time, product, sales, sales facts. Use subgroups to further organize the data: Time - Calendar Date, Time - Fiscal Date. Follow the "Rule of 7" Tables should be organized to show seven or less columns per level. Tables with too many columns become unreadable. Sort Columns Sort columns such that it makes sense to the end user. Metrics should be sorted by most commonly used metrics first. Attributes can be sorted by commons elements, hierarchy, and finally alphabetically. Answers Standard A top level shared folder will exist for each project. The project shared folder will be named using the project specific moniker. A 'Dashboard Reports' sub-folder will be created under the project's top level folder. Sub-folders under 'Dashboard Reports' will be created mimicking dashboard names and organization. Report names will mention the dashboard they reside upon. Prompts and filters will be named according to their object types. Metadata - Business Model and Mapping (BMM) Layer Standard A top level business model folder will exist for each project. The project business model folder will be named using the project specific moniker. Metadata - Physical Layer Standard All project database names will be prefixed with their project specific moniker. All physical tables will have at least one alias table to do work from. All alias table names will be prefixed with the table type: Dim - dimension table Fact - fact table Fact_Agg - aggregate tables Metadata - Presentation Layer Standard All project presentation catalog folder names will be prefixed with their project specific moniker. Metadata - Other Standard Common variable names will be prefixed with SYS. Common init block names will be prefixed with SYS. Project variable names will be prefixed with the project specific moniker. Project init block names will be prefixed with the project specific moniker.