The data you store in your Oracle database is only useful if you can retrieve and display it in a helpful format. This guide will take you through the basics of setting up an OBIEE 11g analysis and adjust the display to make the results more meaningful.
Before building your OBIEE 11g Analysis report, you will need:
- An Oracle 11g database that is installed and running.
- Properly installed and configured Oracle Business Intelligence 11g (OBIEE) tools.
- OBIEE credentials with the appropriate level of permissions.
Once you have each of these pre-requisites in place, you are ready to proceed.
Creating the OBIEE 11g Analysis
Log into the OBIEE tool-set using your develop credentials to open the “welcome” screen.
- Select Analysis from the Analysis and Interactive Reporting section of the Create screen:
- From the pop-up, select Sample Sales Lite:
OBIEE will now create a blank Sample Sales Lite project that uses XML files as a data source.
- Expand the tree view in the left-hand pane of the new project window:
Sample Sales Lite -> Time -> More time objects
Then click and drag Per Name Year into the Selected Columns pane on the right.
- Expand Sample Sales Lite -> Base Facts from the left and drag Revenue to the Selected Columns pane.
- The results of this analysis are immediately available by clicking the Results tab at the top of the screen. The amounts for each year are shown in the Compound Layout panel on the right-hand side of the screen.
At this point you have successfully created your first analysis. In most cases you are going to want to add more data to your analysis to make it more useful. Once you are comfortable with these basic steps, you can move on to the next stage.
Adding more data to your OBIEE 11g analysis
Return to the Criteria tab from where we will select some more data to display.
- Open Sample Sales Lite -> Products from the Subject Areas pane and drag Products into the Selected Columns section. Place it between the Time and Base Facts columns you already have selected.
- Click the Results tab again to see how adding an additional column affects the results generated.
Surprisingly, the Products column is not displayed as you might expect. This is because of the default view set-up of the Compound Layout.
- Click the “pencil” icon just above the results table to open the Edit View dialog.
- A new subpanel called Layout opens at the bottom of the right-hand pane.
Scroll down to the Columns and Measures section where you will find Product Type placed in an Excluded section.
Simply drag Product Type up from the Excluded section and drop it in between Time and Base Types like so:
You will notice that the Results pane above refreshes automatically to show the new Product Type column. You will need to repeat this step every time you alter the default view.
Organizing your output
Returning to the Layout pane, you can further adjust the way in which results are displayed. Drag the Per Name Year object out of the Columns and Measures section and drop it into the Sectioned Report divider.
Now you will have a table containing Product Type and Revenue for each year in the dataset.
Adjusting display of your analysis
Return to the Criteria tab. Click Yes if you are prompted to save changes to the view editor. Repeat steps 1-4 of the Adding more data to your analysis
instructions above, but this time adding Product from Sample Sales Lite -> Products. Don’t forget to alter the default display to show the new column. Next:
- Click Done in the top right-hand corner to save your changes so far.
- Go back to the Criteria tab then click on the setting icon for the Revenue object:
Select Column Properties.
- The Column Properties dialog box will appear, allowing you to adjust the way that data is displayed in the analysis. Make sure you have the Data Format tab open, then click the Override Default Data Format checkbox. Change the settings as follows:
- Select Currency from the Treat Numbers As dropdown.
- Set the Currency Symbol to $ English – United States.
- Leave the Negative Format and Decimal Places defaults.
- Click OK to save your changes.
- You should now return to the familiar analysis project screen. Click the Reports tab to view your new display configuration changes in effect.
As intended, you will now have proper formatting of the Revenue column including currency symbol and comma delimiters for numbers.
Providing end-user filtering
At this point your analysis is displaying every Product Type, Product and Revenue, all grouped by year. For added flexibility, you may like to allow your users to filter by a single year of their choice instead. To do this:
- Click the Edit icon at the top of the results table in the Compound Layout pane.
- In the Layout pane, scroll down to the Section area to locate the Per Name Year item.
Then just drag the Per Name Year item up to the Table Prompts section.
- Now in the Results pane you will see a new Per Name Year dropdown that allows you to select a year and sort results based on that selection.
Improving the results display
Sometimes you will want to highlight certain values within the results generated because they are of greater interest. This example will show you how to highlight Revenue results above $150,000.
- Open the Criteria tab and click the “properties” icon at the top of the Revenue column. Select Column Properties from the menu.
- Select the Conditional Format tab of the Column Properties dialog, then click the Add Condition button.
Select the Revenue option from the menu that appears.
- In the New Condition dialog, apply the following settings:
- Set Operator to is greater than or equal to
- Type a Value of $150000
- Click OK to save the changes
- In the next screen you will be able to apply various formatting settings for records meeting your new condition. Click the Background Color option and select a color – we picked red – then click OK.
- Click OK on the Column Properties dialog to apply the changes.
- Click the Results tab to view the changes in effect. Now any of the Revenue results with a value of $150,000 or higher will display with a bright red background.
- Click the “floppy disk” icon at the top-right corner of the screen to save your analysis.
- 8. In the Save As dialog you will be able to decide where the analysis will be stored. Use the My Folders folder in the tree view on the left to save the analysis for yourself, or Shared Folders if the analysis is to be used by other members of your team.
Watch the OBIEE training video on how to create an 11g analysis
This OBIEE online training tutorial was created by our lead instructor Julie Johnson. Take a look at our other recent OBIEE tutorial on how to work with KPIs. If you are interested in an OBIEE training course we offer both online and on-site classrooms.Please Share This Knowledge With Others!