BICG University Course 301 “Dashboard/Report Application Development” Day 2 • ©Copyright 2005, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by any means, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors. BICG training materials are intended for the use of students who have paid for and have attended BICG training. Please contact BICG for information on receiving additional training materials: http://www.bicguniversity.com • 2 The “Key” to our Exercise Throughout our exercises, you’ll occasionally see yellow “Post-it” notes sprinkled throughout. These are topics that we may call out and discuss in class, or we might suggest that you review others on your own time. These “notes” are designed to further your knowledge about the Oracle BI Enterprise Edition; on BI Best Practices; on “Tips and Tricks” to enable you to rapidly develop applications using OBIEE; and other notable topics. We strongly believe that the better educated you are on each of these topics, the better prepared you’ll be. Our “notes” key is below: Light Bulb: These notes will provide advice, tips or tricks that we’ve developed over the years, or points to ponder. Question Mark: These are questions that we’ll be debating in class. Many of these will have no single correct answer, instead, they’re designed to expand your thinking about pertinent topics. Drafting Table: These are exercises that we’ll be working on in class. At BICG we believe in following standards and best practices, but we don’t believe in limiting creativity. These exercises are your chance to flex your creative muscles! Agenda Time 15 minutes 30 minutes 20 minutes 20 minutes 30 minutes 10 minutes 30 minutes 20 minutes 30-60 minutes 30 minutes 25 minutes 20 minutes 30 minutes 10 minutes 30 minutes 20 minutes 15 minutes Review Questions from Day 1 Exercise #1 Developing Dashboard Prompts – Page 5 Exercise #2 Adding “Is Prompted” Filters to Requests – Page 15 Exercise #3 “Best Practice Guidelines for Tables and Pivot Tables” – Page 22 Exercise #4 Complex Charts: Line-Bar Combinations – Page 30 Morning Break Exercise #5 Adding a Saved Filter and Formatting an Existing Request – Page 38 Exercise #6 A “Cheat” to Develop New, Complex Requests Based upon an Existing Request – Page 48 Lunch break Exercise #7 Complex Pivot Tables – Page 53 Exercise #8 Ranking and Top and Bottom Requests Page 61 Exercise #9 Combining Column Selectors with a Single Chart View – Page 67 Exercise #10 Bubble Charts – Page 71 Afternoon break Exercise #11 Advanced Dashboard Formatting – Page 77 Exercise #12 Using Guided Navigation to Create Dashboard Alerts – Page 83 Exercise #13 Using Guided Navigation to Display or Hide Dashboard Sections – Page 90 4 Topic Dashboard/Report Application Development Exercise #1 Developing Dashboard Prompts 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 5 Exercise Objectives: In this lesson we will review the process of creating Dashboard prompts, which allow the user to selectively filter the contents of a Dashboard page based upon selections made from drop-down lists, multi-select boxes, text entry fields, or calendar selections. We will also be adding Is Prompted filters to a request so it can receive the dashboard prompt selections. Content: 1. 2. 3. 4. Using the Dashboard Prompt editor. Allowing single selects, multi selects, or text entry. Adding the Prompt to a Dashboard. Adding “Is Prompted” filters to a request. 6 Step #1: Accessing the Dashboard Prompt Editor 1 1 To access the Dashboard Prompt editor, access Answers, and then select the Dashboard Prompt icon (looks like a drop-down list with a star). A list of available subject areas will appear. 1. 2. 3. Select the Answers link in the upper right hand corner. Prior to selecting a subject area (which will bring you into the request editors), select the Dashboard prompt editor from the upper left. Select the Paint subject area from the list that appears. 7 Step #2: Set the Prompt’s Scope 1 1 The Dashboard Prompt’s “Scope” must be set, either at a Dashboard level, or page level. Setting the scope to Dashboard, will cause the prompt to affect all pages within a Dashboard; setting it to Page, will cause the prompt to only affect the page in which it is placed. 1. Set the Prompt’s scope to “Page”. It is a BICG best practice to never (or nearly never) set a Dashboard prompt’s scope to Dashboard. Doing so can cause unintended effects, such as causing a prompt on one page to override a prompt on another page; and it can also confuse the end user since it won’t be obvious to the user how or where filtered settings were made, unless the prompt itself is on the same page they are viewing. 8 Step #3: Building the Prompt 1 3 2 1 You’ll notice that the “pick list” is available on the left, just as it is while developing request objects. Adding these fields to a Dashboard prompt is simply done by clicking, and the fields are added, stacked on top of another. Once selections have been added to the prompt, their position can be changed, or they can be removed using the up/down/or remove buttons on the far right. 1. 2. 2 Add Year, Region, District and Brand to the prompt. Leave the other controls in their default settings, and select the “Preview” button in the upper right hand corner. 3 You’ll also notice that you are able to change the control from a dropdown list (allowing only one selection at time), a multi-select, or a text box. 9 Step #4: Testing the Prompt 1 1 You’ll see that the default settings of “drop-down list box” allow the user to select one, and only one selection per prompt. 1. 2. 3. Close the preview window, and set the “Control” settings for all prompts to “Multi-Select”. Retest the prompt using the Preview button. Close the Preview window. 10 Step #5: Changing to Multi-Select 1 2 1. 1 The “Multi-Select” option replaces the drop-down arrow, with an ellipse button which when selected, opens a browse window allowing the user to make multiple selections. 2. 2 Double click your selection to add or remove it from your “Selected” box. Or you can click the arrows to add or remove one or more of your selected values after highlighting them. 3. 4. Click the ellipse button next to Regions. Select the Central Region from the list and click the back arrow to move it to “Selected”. You can also double click on your selection to add or remove it. click “OK”. Create a folder called ‘Prompts’. Save your prompt as “Prompt: Year, Region, District, Brand” into the ‘Prompts’ folder. 11 Step #7: Adding the Prompt to a Dashboard Page 1 1. 1 Prompts can be dragged and dropped into the Dashboard editor, just like another other request object. 2. 3. From your “My Dashboard”, select the Page Options / Edit Dashboard link. Drag and drop your new prompt into the top column. Select “Save”. 12 Step #8: Adding “Is Prompted” Filters to a request 1 1 The last selection within the Operator list is “is Prompted”. This selection allows the field to be filtered based on selections within the prompt. If no prompt selection is made for the field, the request will either be unfiltered on that field or will be filtered based on the request level filter on that field. 1. 2. 3. 4. Remember that when an element is contained within the request criteria, you can open the filter dialogue by clicking the filter icon on that field. If the field doesn’t exist, use the click shortcut. Using Exercise #11 (from Day 1) , open the request in Answers and remove any existing filters. Holding the key, select “Year” from the pick list. It will open the filter dialogue box. From the Operator list, select “Is Prompted”, which is the last selection in the list. Do the same for Region, District and Brand. Save Exercise #11. 13 The Result! 1. Select the Southern and Western Regions from the Region prompt, and select the “Go” button. What happened? If your answer is “one request changed but the other did not”, the reason is that we added the “Is Prompted” filters on one request but need to also them to the other request so it can “receive” the prompt selections. That’s for the next exercise. 14 Dashboard/Report Application Development Exercise #2 Adding “Is Prompted” Filters to Requests 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 15 Exercise Objectives: In this lesson we will review the process of adding what are called “Is Prompted” filters to requests, so that those requests can accept the selections made within Dashboard prompts. Content: 1. 2. 3. Saving a filter for reuse. Adding a saved filter to a request. Testing the Dashboard Prompt. 16 Step #1: Saving the “Is Prompted” Filter for Reuse 1 2 1. 2. 3. 4. Open Exercise #11 (from Day 1) and select the “Save Filter” button. Name this filter as “Filter: Exercise #2”. Save this request in the “My Filters” folder. Open the request for Exercise #10 (from Day 1). 1 Your filtered selections are now shown, each identified as “Is Prompted” filters. You can save any filter for reuse by selecting the “Save Filter” button. 2 17 Step #2: Adding the Saved Filter to Other Requests 1 1 The saved filter is now available as a selection from the pick list on the left. Selecting a saved filter opens the “Apply Saved Filter” dialogue box which shows the contents of the filter and gives two options. If the first option is checked, the saved filter will replace any existing filters previously added to the request. If it is unchecked, the saved filter will be added to the request without removing any existing filters. If the second option is checked, the contents of the filter will be added to the request. If it is unchecked, it will add a reference to the filter’s name instead of the contents. Also, if you modify the filter in the future, those changes will automatically be populated to any requests that have the filter “referenced”. This option also affects what the user sees which is explained on the next slide. 1. 2. 3. 4. Select the filter from the My Filters folder. Check both boxes, and select “OK”. Save the request. Return to your “My Dashboard” and select Central, Eastern and Southern regions from the region prompt. 18 Step #2: User Perspective for Filter Options From a user’s perspective, if the second option is checked, the filter will be displayed on the dashboard. In this example, there is a filter to exclude the ‘Enterprise’ brand. Often users will want certain things excluded but won’t want to see the filter with the request. The next slide shows how to do this. 19 Step #2: User Perspective for Filter Options From a user’s perspective, if the second option is un-checked, the filter will not be displayed on the dashboard. Referenced filters are used when users want filters included in requests but don’t want to see them on the dashboard. In this example, there is a filter to exclude the ‘Enterprise’ brand but it is not displayed. This option needs to be used carefully as it is possible that a user won’t realize that certain things are excluded from a request. 20 The Result! 21 Dashboard/Report Application Development Exercise #3 Best Practice Guidelines for Tables and Pivot Tables 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 22 Exercise Objectives: In this lesson we will review several basic best practice guidelines when developing tabular views and pivot tables. Content: 1. Setting tables and pivot tables to “stretch” to fit the Dashboard column in which they’re placed. 23 Step #1: Stretching Tables to “Fit” within the Dashboard Column 1 2 3 1 The width setting of the tabular request is accessed using the “Table View Properties” button. The width setting is hidden under the “Additional Formatting Options”, which is accessed by clicking the “plus” button. The width (and height, and padding) options can either be set to a number of pixels, or as a percentage. 1. 2. 2 3. 4. 5. 6. 3 Open Exercise #10 (from Day 1), and edit the tabular view. From the tabular editor, select the “Table View Properties” icon (looks like a hand, in the upper left corner). Click the “plus” button to the left of “Additional Formatting Options”. Enter 100% in the width field. Click “OK”. Save the request. 24 Step #2: Accessing Hidden Views from the Drop-Down 1 1 The compound layout in this request, now contains just the view selector. You can access the table and pivot table themselves by accessing them from the navigation drop-down list, and selecting the appropriate view to edit. 1. 2. 3. Open Exercise #11 – from Day 1 - and using the editor navigation dropdown, select “Table”. This will take you into the table editor. Make the appropriate changes to set the width to 100%. Once those changes are made, select the “Pivot Table” from the navigation drop-down. 25 Step #3: Setting Pivot Tables to fit 100% 1 2 3 1 The width setting for pivot tables is fairly well hidden, and is accessed by clicking the properties icon (looks like a hand) situated below the sections and above the rows. The width setting is hidden under the “Additional Formatting Options”, which is accessed by clicking the “plus” button. The width (and height, and padding) options can either be set to a number of pixels, or as a percentage. 1. 2. 3. 4. 2 Select the pivot tables property icon (looks like a hand). Set the pivot tables width to fit 100%. Select “OK”. Save the request. 3 26 Step #4: Setting Columns to 50% Width 1 1 You’ll notice that your Dashboard columns don’t quite look even – the tabular request has pushed the graph slightly to the left. It is a BICG best practice to set column widths to an equal size – 50% each for two column Dashboards. 1. To set the column widths, enter the Dashboard editor. 27 Step #4: Setting Columns to 50% Width 1 2 3 1 Column properties such as width, height and padding can be set by selecting the “Properties” option for each column (ensure that you select the properties button for the column, and not the section or request). The width setting is hidden under the “Additional Formatting Options”, which is accessed by clicking the plus sign. 1. 2. 2 3. 4. The width (and height, and padding) options can either be set to a number of pixels, or as a percentage. 5. 3 Select the “Properties” button for the column in the lower left. Select “Column Properties” (if you don’t see “Break” as the other option, you’ve clicked the wrong properties button). Expand the “Additional Formatting Options” and set the column width to 50%. Select “OK” and repeat the procedures for the column in the lower right. Select “Save” on the Dashboard editor. 28 The Result! 1 1 Compare the two images above – the Dashboard above did NOT have it’s column width’s equally set, and the Dashboard below has both columns set to 50% width. 29 Dashboard/Report Application Development Exercise #4 Complex Charts: Line-Bar Combinations 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 30 Exercise Objectives: In this lesson we will dive deeper into one of the more powerful chart types, line-bar combinations which allow the user to quickly determine the interaction of two metrics, something that is nearly impossible to do on a numeric request. We’ll also look at how to use these charts without improperly implying a “trend” where no trend exists. Content: 1. 2. 3. Creating a Line-Bar Combination Chart. Using a symbol in place of the line. Using conditional formatting. 31 Step #1: Creating a Line-Bar Combination Chart 1 2 3 1. 1 Line-Bar Combination charts are developed by placing an attribute (such as Region) in the x-axis, And a Metric (such as Dollars) in the y-axis (which equates to the bars), And another metric in the other yaxis (indicated by the wavy line, signifying the metric that will be plotted in the line). 2. 2 3 Create a new request and add a a line-bar combination chart with regions in the x-axis, and dollars and % Chg Year Ago Dollars in the y-axis. Filter on Year 2006 At BICG, we’re big fans of line-bar combos, because they can immediately provide insight into how two metrics interact, which is something very difficult to see on a numeric request. That said, we’re not big fans of line charts, unless it’s plotting real-time data. That’s because the line can incorrectly imply a trend, where no trend exists. 32 Step #2: Using a Symbol in Place of a Line 1 2 3 1. 1 The “Format Chart Data” icon (looks like a pencil, on 6 tipped on its side) allows you to modify the color and other options on the lines and bars. On the line-bar combo, you must select from the chart component drop-down. In this case, we’ll be working on the line. You must uncheck the “Use Default” checkbox, and then select a symbol type, and color of the symbol. Our favorite symbol is a triangle. The secret to hiding the line, is to then set the line width to zero pixels. 2. 3. 4. 2 Select the “Format Chart Data” icon. Uncheck the first line, and set the line width to zero. Select a symbol type and color. Select “OK”. 3 33 Step #3: View the Result 1. Reselect the “Format Chart Data” icon. 34 Step #4: Using Conditional Logic 1. 2. 3. 4. Select the “Conditional” tab on the Format Chart Data dialogue box. Select the “Add Condition” button, and select % Chg Year Ago Dollars. Set the first condition to “is less than or equal to 50”. Select “OK”. 35 Step #4: Using Conditional Logic 1. 2. 3. 4. 5. Set the symbol of the first condition to a red triangle. Add a second condition and set it to “greater than 50”, and set its symbol to a green triangle. Select “OK”. Click the ‘Save’ button and click the ‘New Folder’ button and create a new folder called ‘Day 2 Requests’ (save all requests built today into this folder). Save the request as ‘Exercise #4 – Line-Bar Example’ 36 The Result! You’ll notice that in the legend, the triangle is being shown in the color that you’d previously set. You can change this color to be one of the “conditional” colors, or you can set it as a neutral color such as gray. 37 Dashboard/Report Application Development Exercise #5 Adding a Saved Filter and Formatting an Existing Request 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 38 Exercise Objectives: In this lesson we will add our saved filter to a request that was built in Day 1. We will also be setting formatting for the request and doing some advanced work in the compound layout. Content: 1. 2. 3. 4. 5. Adding a saved filter. Setting width formatting. Finding hidden objects in the compound layout. Combining objects in the compound layout. Deleting objects from a request. 39 Step #1: Add the Saved Filter 1. 2. Open Exercise #13 (from Day 1). Add the saved filter from Exercise #2. 40 Step #2: Find the Table object 1 1 When you click the editor navigation dropdown, all objects that have been created previously for the request have a green circle. You may choose those objects from here to edit them directly. 1. Click the editor navigation dropdown and choose Table 41 Step #3: Format Table 1. Set the table properties to fit 100% within the column. 42 Step #4: Find and Format Pivot Table 1. 2. Use the editor navigation dropdown to edit the Pivot Table Set the pivot table width properties to fit 100% within the column. 43 Step #5: Create a Filters object 1 1 When you want to add something to the compound layout you need to use the Add View menu. If you happen to click an object in the editor navigation dropdown list that does not have a green circle, the object will be created but will not be attached to the compound layout. 1. Click the editor navigation dropdown and choose Filters. 44 Step #6: View the Filters Object We now have a Filters object for this request but it is not associated with the Compound Layout. The next slide will show you how to fix this. 1. Click the Compound Layout icon. 45 Step #7: Add the Filters Object to the Compound Layout 1 1. From the Add View menu, click the Filters icon 46 The Result! Because we had previously created the Filters object, it was added to the Compound Layout when we used the Add View menu. 1. Save your request into the Day 2 Requests folder as “Exercise #5 Region Summary” 47 Dashboard/Report Application Development Exercise #6 A “Cheat” to Develop New, Complex Requests Based upon an Existing Request 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 48 Exercise Objectives: In this lesson we will show a shortcut or “cheat” on how you can build upon existing request structures to shorten your workload as you develop newer requests. Content: 1. 2. 3. “Replacing” columns within an existing request. Modifying minor components of the request. Doing a “Save As” a new request. 49 Step #1: Starting with “Region Summary - Exercise #5”, the Multi-View Request 1 2 1 “Region Summary - Exercise #5” has many complex request objects, including a column selector, A view selector with four views, each of which has had very specific formatting set, including graph settings, tabular formatting, etc. This request is predominately based upon analysis of regions, with a tabular request, pivot table, and several charts, painting a different picture of the insight that can be gained from analyzing the regions. What would it take to redevelop this entire “package” for a brand analysis? 2 At BICG, we’re big fans of shortcuts, and we’re also big fans of analyzing information in multiple ways, until you’ve reached a moment of insight, and an action can be taken. We’ve found that it’s valuable to have similarly structured requests side by side so that you can easily compare information in multiple ways. Here’s how you get there, real fast. 1. Select the Criteria Tab. 50 Step #2: Replacing Columns 1. 2. 3. 4. Select the “Edit Formula” icon on the Region column. Delete the contents of the “Column Formula”. Select “Brand” from the pick list (you’ll see that it’s “formula” will now appear where Region used to be”. Select “OK”. You’ll notice that the column name has now changed to “Brand” without requiring you to check the “Custom Headings” box and entering a new name. 51 The Result! 1. 2. 3. Back on the Results Tab, you’ll notice that the column selector has even updated, replacing Region with Brand (although you’ll want to go in and drop the second “Brand” that was available as a replacement column). You’ll need to do some minor edits, such as renaming several of the views in the view selector, to have them refer to Brands, rather than Regions. Rename the views in the view selector as appropriate. Save the request as “Exercise #6 Brand Summary”. 52 Dashboard/Report Application Development Exercise #7 Complex Pivot Tables 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 53 Exercise Objectives: In this lesson we’ll dive deeper into Pivot Tables, and will look at how to use pivot tables to “shorten” long requests using multiple pivot tables and view selectors; and how to mimic a re-pivoting of the pivot table through the Dashboard, using column selectors. Content: 1. 2. Using pivot tables to “break up” wide requests. Using column selectors to “mimic” pivoting the pivot table through the Dashboard. 54 Step #1: Create a new Pivot Table Every request designer has run into the situation where the end users asked for “just a few more columns”, or, the end users couldn’t decide on how to limit the number of columns contained within a request, so they included them all. This is a trick we use to “shorten” long requests, in effect, breaking them into smaller, digestible chunks. 55 1. 2. 3. Create a pivot table identical to the one above. Make sure to add conditional logic, and the row and column totals. Add the saved filter from Exercise #2 to the request Notice that the measures “included” are all Unit measures, and the “excluded” measures are all Dollar measures. Step #2: “Duplicate” the Pivot Table 1 1 A little known (and little used) option within each editor is a “Duplicate, Specialize or Delete” option. Clicking this option lets you duplicate the view that you’ve just created, allowing you to do minor edits, without “reinventing the wheel” (i.e. resetting all formatting and design. 1. Select the “Duplicate, Specialize or Delete” button, and select “Duplicate View”. 56 Step #3: Swap the “included” and “Excluded” Metrics 1. 2. Swap the metrics that are in the “Included” and “Excluded” list. Return to the compound layout, and add a view selector. 57 Step #4: Add the two (or more) Pivot Tables to a View Selector 1. 2. 58 Add the two pivot tables to the view selector, naming them “Dollars” and “Units”. (“Dollars” should be first on the list) Add the caption: “Dollars or Units”. Step #5: Adding a Column Selector to Mimic a Re-Pivot 1. 2. 3. Add a column selector option to the Region and Brand columns, allowing the user to not only replace Region with Brand and Brand with Region, but multiple other attributes as well. Add a filter object to the compound layout. Save the request as “Exercise #7 - Dollars and Units”. 59 The Result! Shown are three of the dozens of permutations now available within this request, all available through the Dashboard. Add prompting on top of the “re-pivoting” and selecting between dollars and units, and the end user can now produce hundreds of permutations, without once calling IT. 60 Dashboard/Report Application Development Exercise #8 Ranking and Top and Bottom Requests 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 61 Exercise Objectives: In this lesson we’ll look at one of the most common requests, that is, to see the “top” or “bottom” performers within a certain category. Using Oracle Answers, we’ve developed short-cuts to provide these types of requests, without limiting the User. For example, if you’ve built a top ten request, and they want to see the next ten, do you need to build a new request? Content: 1. 2. Using “Page” controls to create top and bottom “n” requests. Using column selectors to increase the permutations of top and bottom requests. 62 Step #1: Create a New Request 1. 2. 3. 4. Create a request identical to the one above. Make sure to add conditional logic, and to sort low to high on the % Chg column. Also, enable column sorting on the request. Enable Alternating Row “GreenBar” Styling. Add the saved filter from Exercise #2 to the request. Click the compound layout button and edit the Table View. The next logical step would be to add a ranking column, and filter off only those rows greater than 10 (for example), but that limits you to only the top 10 (for example). What do you do if the user then wants to see the next ten? 63 Step #2: Using the Paging Function to Make a Top Ten 1. 2. 3. Access the “Table View Properties” icon in the upper left corner. Set the “Rows per Page” to 10. Select “OK”. 64 Step #3: Testing your Top and Bottom Request 1. 2. 3. Test your Top and Bottom request using the Preview button. Use the “Next Page” button to move from the “Top Ten” to the “Next Ten”. Return to the first page, and change the sort order on the % Chg column, to get a “Bottom Ten” request. 65 The Result! 1. 2. 3. 4. Add a column selector to the top of the compound layout, allowing the Market column to be replaced with District, Color, or UPC. Add a filter object to the compound layout. Test out your new Top and Bottom request using the Preview option. Save your request as “Exercise #8 Exceptions! Top and Bottom Performers”. 66 Dashboard/Report Application Development Exercise #9 Combining Column Selectors with a Single Chart View 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 67 Exercise Objectives: In this lesson we’ll use a few of the lessons learned from previous exercises, and will create a request with a single graph, and combine it with a column selector to allow the user to have multiple permutations of the chart. Content: 1. 2. Building a Pie Chart. Adding a Column Selector. 68 Step #1: Create a New Pie Chart with Custom Formatting 1. 2. 3. 4. Create a pie chart based on Brand and Dollars. Remove the Title Change Show Data Labels to Always Click Override Default Data Format. Notice that by Checking Custom Title and leaving it blank you removed the title and by overriding the Data Labels you changed the default view of two decimal places. 69 Step #2: Add a Column Selector 1. 2. 3. 4. 5. Add your filter from Exercise #2 in the Criteria tab. Add a column selector, allowing the user to replace Brands and Dollars with the columns above. Add a filter object to the compound layout. Test the request using the Preview option. Save the request as “Exercise #9 - Brand Results by District”. If you use a column selector to allow the user to replace the metric used on a Pie Chart, it is a best practice to only use ordinal numbers, rather than percentages or indexes. 70 Dashboard/Report Application Development Exercise #10 Bubble Charts 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 71 Exercise Objectives: In this lesson we’ll use one of our favorite methods of visualizing information, the bubble chart. The bubble chart uniquely allows a user to immediately (we mean immediately) interpret how three metrics interact, something that is virtually impossible to do on a number request. We like it so much, we made it part of our logo. Content: 1. 2. Building the bubble chart. Adding a tabular view, and a column selector. 72 Step #1: Selecting Metrics Appropriate to a Bubble Chart 1. Bubble charts are designed to plot three metrics, and how they interact, or contribute to one another. The bubble chart is based upon an x, y and a z-axis, the zaxis being displayed as the size of a bubble. That 3rd metric is most meaningful if it’s based on a ratio, a “percent of”, a “percent to” or some other metric that isn’t meant to be summed, rather it would aggregate using an average (or weighted average). 2. 3. 4. Create a new request with Total US, Region, Units, Dollars, and % Chg Year Ago Units selected. Add your saved filter from Exercise #2. Click the compound layout button Add a chart to the compound layout You’ll want to experiment with the three metrics that you use for plotting on the bubble chart. It’s our best practice to use additive numbers on the x and y-axis such as dollars or units, and non-additive numbers, such as averages, percentages, or ratios for the z-axis (the bubble size). 73 Step #2: Creating the Bubble Chart 1 1 The bubble chart axis palette offers more options than any other chart. The key columns to “check” are to place an additive metric under the “x-axis” column; an additive metric under the “y-axis” column; and a non-additive fact under the “bubble” column. Under the “Legend” column, check the element that you’d like to do a comparison across. Like any other chart legend, it’s a best practice to limit the number of elements to less than 20, or less than 10 if possible. 1. 2. 3. 4. 5. Check the box for “Dollars” under the x-axis column. Check the box for “Units” under the y-axis column. Check the box for “% Chg Year Ago Units” under the bubble column. Check the box for “Regions” under the legend column. Check the box for “Total US” under the “level axis” column (the first column). 74 Step #2: Creating the Bubble Chart 1 1 To force the chart axis to eliminate the “white space” below the lowest y-axis and x-axis actuals, it is a best practice to use the “zoom to data range” option found under the “Axis Scaling” icon. 1. 2. 3. Select the “Axis Scaling” icon (looks like a chart with a vertical green arrow). On both the left and bottom tabs, select “Zoom to Data Range”. Select OK. 75 The Result! The column ‘Total US’ is needed for the bubble chart but not displayed in the tabular view. To hide a column, go to the Criteria tab of a request and click column properties of the column you want to hide. Click the Column Format tab and click the hide checkbox. Hiding ‘Total US” removes the grouping. Therefore, it is fine to enable the alternating row greenbar formatting on this request. 76 1. 2. 3. 4. Add a formatted Tabular view under the bubble chart. Add a column selector, allowing the user to drop Region, and replace it with Brand or District; and allow the user to drop “% Chg Year Ago Units” with each of the other % Chg” columns. Add a filter object to the compound layout. Save your request as “Exercise #10 Performance by Dollars, Units and Percent Change.” Dashboard/Report Application Development Exercise #11 Advanced Dashboard Formatting 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 77 Exercise Objectives: In this lesson we’ll use a few of the more advanced Dashboard formatting techniques, including the use of sections, and forcing column sizes to fit. Content: 1. 2. Renaming sections and turning on the top border. Forcing column widths to fit. 78 Step #1: Create a “Day Two” Dashboard 1. 2. 3. 4. Select the “Add Dashboard Page” button from Edit Dashboard. Name the Dashboard page “Day Two”. Create one column across the top, and two columns beneath. Drag and Drop your Day 2 requests in the sequence above. 79 Step #2: Format the Sections 1 3 2 1 Clicking the “Rename” option on each section allows you to rename the section, and to display the section heading, which will turn on the solid blue heading at the top of each section. Check the “Display Section Heading” and, 1. 2. 3. 2 4. 3 Enter a new name for the section. It’s a BICG best practice to drop each request into its own section, and to rename the section using the request name. It is a BICG best practice to use the Edit Dashboard – Properties – Modify option to edit requests on a dashboard. It is not recommended to use the “Modify” link if displayed directly on a request. 80 5. 6. 7. On the dashboard, click Page Options\Edit Dashboard Select the “Rename” button on each section. Rename each section using the request name (omit the “Exercise # …” heading). For the prompt section, name it “Selections”. Check the “Display Section Heading” box after re-naming each section. Click Properties\Modify on each request on the dashboard. Remove the title bar from the compound layout. Remember to save each request. Step #3: Fit the Columns each to 50% 1 3 1 Clicking the “Properties” option on each column allows you to set the width of each column either to a pixel width, or to a percentage. 2 2 Expand the Column Properties dialogue box by clicking the plus sign next to “Additional Formatting Options”. On a two column Dashboard, setting the width of both columns to 50% will always keep the column width consistent, and will force the contained requests to fit within that column width. 81 1. Set both columns width to 50% 3 The Result! 82 Dashboard/Report Application Developer Exercise #12 Using Guided Navigation to Create Dashboard Alerts 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 83 Exercise Objectives: In this lesson we’ll use one of several methods of “Guided Navigation” to indicate to the Dashboard user when certain thresholds have been exceeded, and when action should be taken. Content: 1. 2. Creating an “Exceptions” request. Adding the Guided Navigation Link to a Dashboard page. 84 Step #1: Change Prompt Scope 2 1. 2. 3. Modify the Prompt at the top of the My Dashboard, Day Two page by clicking Page Options, Edit Dashboard, Properties, and Modify. Change the Prompt scope to be ‘Dashboard’ Save the prompt 85 Step #2: Create an “Exceptions” Request 1 1 “Exceptions” requests (requests that are used to search for exceptions, both good and bad) are a key part of designing good iBots and Guided Navigation. An exception request typically filters off either very good or very bad data, based upon one or more columns. Using prompts on the Dashboard page, you can allow the user to adjust exactly what constitutes an “exception”. 1. 2. 3. 4. Create a new request showing Year, District, and % Chg Year Ago Dollars. Add your saved filter from Exercise #2 as well as a new filter on the % Chg Year Ago column, which will filter the request to contain only rows where “% Chg Year Ago Dollars is less than or equal to 30 (percent)”. Add a filter object to the compound layout for this request. Save the request, naming it “Exercise #12 - Exceptions Request”. 86 Step #2: Add a “Guided Navigation Link” to your Dashboard 1 1 “Guided Navigation” links allow you to add hyperlinks to the Dashboard page which will appear or disappear based upon the content of an underlying exception request. This type of object can be dropped into any column and section on the Dashboard. 1. 2. 3. 4. Create a new section on the top of the right column by dragging ‘Section’ from the Dashboard Objects. Rename the new section “Alerts” and check the box to “Display Section Heading”. Drag and drop a “Guided Nav. Link” object to this new section. Select the “Properties” button on the Guided Navigation Link that you just added. 87 Step #3: Configure the Guided Navigation Link 1 2 3 4 1 Using the browse list to select a “Source Request” allows you to link this Guided Navigation Link to an exceptions request. The Guided Navigation Link’s behavior (whether it appears or not) will be determined by whether or not this attached request returns rows. The “Target” Request (meaning Request) or Dashboard is what your Guided Navigation Link will call. It can be the same request used in the source request above, or a more detailed request or Dashboard. The “Caption” is the text that will appear along with the Navigation link when/if it appears on the Dashboard. 88 1. 2. 2 3. 4. 3 Select your “Exceptions” request in both the Source Request and Target fields. Select the radio button which indicates that the link will appear “If the request returns rows”. Enter a caption similar to that shown above. Select “OK”. 4 The Result! 1. On the prompt, select 2006 for Year, and “CENTRAL REGION” AND “WESTERN REGION” for Region and click “Go”. Did your Guided Navigation Link appear? How about when you select 2006, and the “SOUTHERN REGION” and “EASTERN REGION”? 2. Click on the Guided Navigation Link to retrieve your Exceptions request. 89 Dashboard/Report Application Developer Exercise #13 Using Guided Navigation to Display or Hide Dashboard Sections 2130 Cliff Road, Suite 200, Eagan, MN 55122 www.biconsultinggroup.com 90 Exercise Objectives: In this lesson we’ll use another method of “Guided Navigation” on the Dashboard to hide or display complete Dashboard sections. Content: 1. Setting a Sections Properties using “Guided Navigation”. 91 Step #1: Display a Section based upon “Guided Navigation” 1 1 Applying “Guided Navigation” to a section will control whether the section will appear or disappear based upon the content of an underlying exception request. 1. Click on the section properties for the Guided Navigation Link we added in the last exercise. 92 Step #2: Configure the Guided Navigation Section 1 2 3 1 Set the section to reference the source request. 1. Select your “Exceptions” request in the Source Request. Select the radio button which indicates that the link will appear “If the request returns rows”. Select “OK”. 2 Set the source request to be referenced. 2. 3 Determine whether or not the section should be shown based upon the results of the Source Request request. 3. 93 The Result! 1. On the prompt, select 2006 for Year, and “CENTRAL REGION” AND “WESTERN REGION” for Region and click “Go”. Did your Section appear? How about when you select 2006, and the “SOUTHERN REGION” and “EASTERN REGION”? 2. Click on the Guided Navigation Link to retrieve your Exceptions request. 94 Questions & Answers Don’t forget to continually review the ‘BICG Best Practices Development Guide’ to help your team members get the most out of OBIEE! 95 Thank you for taking the Dashboard/Report Application Development course!! Please check out the BICG University site at http://www.bicguniversity.com for information on additional courses. 96 About BI Consulting Group Oracle’s largest consulting partner dedicated exclusively to Oracle BI EE. Collectively worked on more than 200 Oracle BI Enterprise Edition projects and more than 300 data warehouses and datamarts. Average consultant has 3+ years of Oracle BI Enterprise Edition experience, and more than 10 years of BI/DW experience. Every customer is referenceable including those who had previous failures with other consulting firms. Experts at both stand alone implementations and application implementations including Sales, Services, Finance, Pharma, and Marketing Analytics. One of the few consulting firms to have deployed against Oracle Finance and PeopleSoft. Contact Information: Michael Martin [President] 651-210-1790
[email protected] Amy Mayer [Senior Vice President] 612-237-9843
[email protected] 97 Notable BICG References Deployed Oracle RMW for Service Analytics after failed deployment by other Systems Integrator. Deploying Service Analytics and a custom data warehouse across international divisions. Multiple projects deployed across multiple divisions, including a Price Scenario application allowing “what-if” forecasting. Sales Analytics application against existing EDW. Deployment of Oracle’s Pharma Analytics, after failed implementation by another Systems Integrator. Deployment of Order Analytics against a new, custom EDW. Redeveloped dozens of existing Dashboards using BICG’s “best practices” methodology; and developed several new custom data warehouses. Integrating multiple source systems together in a unified frontend Dashboard. Other Notable Customers: • • American Express • • Fidelity • • Cbeyond Shopzilla Bayer Medtronic Emergency Response • • RBC Dain • UBC • Pharmaceutical Life Fitness Benderson Development 98