| Developer: Business Intelligence
Building BI Dashboards with Oracle Database 10g, Oracle Discoverer, and Oracle Portal
Create your own BI dashboard with software you can download, today, from OTN.
If you've been following the release of Oracle Business Intelligence 10g or keeping an eye on industry trends, you're probably aware that everyone is talking about business intelligence (BI) dashboards. BI dashboards bring together reports and graphs from several data sources and present information to users in a simple-to-understand, unified manner. The demand for BI dashboards from the user community has therefore led to numerous new features and improvements in Oracle's business intelligence tools, and the simplified creation of BI dashboards is one of the key new features of Oracle Business Intelligence 10g.
This article explains the features of a BI dashboard, discusses the benefits to users, and steps through the creation of a dashboard with Oracle Database 10g; Oracle Business Intelligence Discoverer (Oracle Discoverer), a component of Oracle Business Intelligence 10g; and Oracle Portal.
What Is a Business Intelligence Dashboard?
Stephen Few provides a concise definition of a BI dashboard in his Intelligent Enterprise article " Dashboard Confusion " (March 20, 2004),
A dashboard is a visual display of the most important information needed to achieve one or more objectives, consolidated and arranged on a single screen so the information can be monitored at a glance.
BI dashboards usually contain data summarized to a high level to enable users to quickly understand the larger trends affecting the organization, with the ability to "drill down" to greater levels of detail as required. It's important that the dashboard display information in a clear, concise, and intuitive manner and that the information display can be customized to a user's particular requirements. All these requirements are met by the new release of Oracle Business Intelligence 10g.
BI dashboards are typically used for displaying metrics defined by the organization, such as products sold by region, defects per thousand shipped, or student grades by faculty. Typically, these metrics are expressed as key performance indicators (KPIs), and a typical dashboard brings several KPIs together across aspects of the business.
From an Oracle perspective, BI dashboards are being positioned as a way to bring together all of an organization's business intelligence products into a single, integrated view of the business and serve as an integration point for corporate performance management products such as Oracle Enterprise Planning and Budgeting, Oracle Financial Analyzer, and Oracle Balanced Scorecard. An Oracle BI dashboard uses Oracle Portal to bring together data from relational, OLAP, and XML data sources; has a unified look and feel; allows you to use familiar tools such as Oracle Discoverer to author your reports; and uses as its underlying technology the enterprise-quality Oracle Database 10g and Oracle Application Server 10g.
By using Oracle Business Intelligence technology to build its BI dashboard, your organization can take advantage of the following key product features:
So what is involved in building a BI dashboard, and what does one look like?
Oracle Business Intelligence 10g Dashboards
With Oracle Business Intelligence 10g, a BI dashboard usually consists of one or more Oracle Portal pages containing several Oracle Discoverer and other portlets and, in some instances, a control on the page to allow users to pass parameters to the portlets. Figure 1 shows a typical BI dashboard.
This example includes four Oracle Discoverer worksheets in separate portlets and a product values selector on the left that lets users specify the product for which the reports are run. When a user clicks on a product, all of the portlets refresh and display data for that particular product.
To build a BI dashboard, you must first install Oracle Business Intelligence 10g (10.1.2 or later) and the following components:
All of the above are available standalone or as components of Oracle Application Server 10g (10.1.2); Oracle BI Tools are also available as components of Oracle Developer Suite 10g (10.1.2.0.0).
With Oracle Application Server 10g Release 2, product setup and licensing have changed from previous Application Server releases and a typical BI dashboard installation now has three tiers: an Identity Management and Metadata Repository tier (equivalent to the Infrastructure tier of previous releases); an Oracle Application Server middle tier (containing Oracle Portal, Oracle HTTP Server, Oracle Application Server Containers for J2EE [OC4J], Oracle Web Cache, and others); and a Business Intelligence tier, containing the Oracle Discoverer server elements. Oracle Application Server tiers are now available for licensing and installation separately, and it is now possible to install only the Business Intelligence tier if you would like to run just a standalone Oracle Discoverer server without integration with Oracle Single Sign-On and Oracle Portal. However, for the purposes of building a BI dashboard, you need Oracle Portal and Oracle Identity Management, so you need to install all three tiers.
In addition, you will need an Oracle database, preferably Oracle Database 10.1.0.3 or later. Oracle BI dashboards can incorporate both relational and OLAP data held in the Oracle RDBMS, but this article initially looks just at relational data and a traditional Discoverer End User Layer (EUL), and its examples use the End User Layer and Discoverer Administrator Tutorial data, which you can install when you install Oracle Business Intelligence Discoverer Administrator.
Finally, to publish Oracle Discoverer worksheets on an Oracle Portal page, you must first associate the Oracle Application Server Business Intelligence instance with an Oracle Identity Management and Metadata Repository instance and then register Oracle Discoverer Portlet Provider with your Oracle Portal installation. Details of how to do this are outside the scope of this article but can be found in the online documentation .
Creating a Business Intelligence Dashboard
Our dashboard example uses Video Store Discoverer Administrator data to provide some data and workbook examples. Video Store is a hypothetical chain of video rental stores used to illustrate business scenarios and comes with sales and inventory data suitable for our dashboard example.
Our sample dashboard will be used by a member of the Video Store executive management team at the head office and will provide an at-a-glance view of the two most important key performance indicators for the branch network: (1) dollar profit by product by location and (2) margin percentage by product by location. These KPIs will initially be displayed at a summarized, companywide level, and executives will be able to drill into the data to look at individual branch performance. In addition, they will also want to be able to customize their view of the data to suit their particular preferences.
Dashboard Creation Steps
Once the Oracle Business Intelligence 10g software has been installed and the Oracle Discoverer tutorial EUL and data have been loaded into your database, you are ready to create your first business intelligence dashboard.
In our example, you're going to display a graph, a cross-tab, and a set of gauges that show the performance of the business by department and you'll provide a way for the users to choose the department for which the reports are displayed.
The steps for putting the dashboard together are as follows:
Creating the Portal Page
Oracle Portal is an out-of-the-box solution for building Web portals on the Oracle Application Server platform. Oracle Portal is a declarative, "no coding needed" development environment that enables you to build Web pages that incorporate information and data from inside and outside your organization, provides a single entry point for applications, and incorporates Oracle's single-sign-on technology to provide a single user ID and password across all of the organization's Web-based applications.
Oracle Portal pages comprise portlets, "windows" to other Web pages; applicationsor in our case, business intelligence reports; and regions that contain documents and links to other pages. Portlets are built to open, published standards, and several third-party suppliers such as Business Objects and MapInfo provide portlets that integrate with Oracle Portal.
Oracle Portal portlets take advantage of Oracle Application Server customization, so users can customize their view of data, storing their personal changes as deltas against a permanently stored single original copy. Portlets can also communicate with other Oracle Portal page elements, so you can "wire together" all of our reports and have them respond to a single parameter selector on the page.
Your first step, therefore, is to navigate to the Oracle Portal installation, log on, and then create a new page to hold the dashboard. In our example, you create a simple page, with placeholders for our Oracle Discoverer portlets, and a drop-down list that lets users decide for which department the reports are run.
First you create the basic layout of the page and add a logo to the top and a bit of introductory text in the top left-hand portlet to welcome users to their dashboard page (Figure 2).
Define a Connection
Oracle Discoverer portlets require an Oracle Discoverer connection to be defined, either by users directly or via Oracle Application Server Control. In our example, you've created a public connection to the Video Store schema (Figure 3).
There are two Oracle Discoverer worksheets you will add to the BI dashboard: one that displays the dollar profit by region for a particular department and one that displays the profit margin by the same criteria (Figure 4).
Both of these worksheets have had a Department parameter added to them, which allows the user to specify the Video Store departments (Video Sale, Video Rental, Laser Disk Sale, Snacks, Game Rental, or Beverages) for which the worksheet is run.
Add Worksheets to the Portal Page
The next step is to add these worksheets to the portal page. From the Oracle Portal builder interface, add an Oracle Discoverer Worksheet portlet to the bottom right of the page. After specifying the public connection, you select the worksheet to be displayed in the portlet (Figure 5).
Next, select a default value for the Department parameter (Figure 6). Note the Page Parameters Mapping headingthis is where you map the worksheet portlet to a generic portlet parameter, which you'll later map to a portal page parameter that is used throughout the BI dashboard page.
After you choose to display just the graph within the worksheet and finish the rest of the wizard, our dashboard is now displaying the first worksheet. You then repeat the process for the bottom right-hand portlet but this time display the cross-tab of data from the same worksheet. The BI dashboard now looks like Figure 7.
For the remaining portlet, you are going to use a new feature, Oracle Discoverer gauges (Figure 8). Gauges are new with Oracle Business Intelligence 10g and are available only when data is being deployed through Oracle Discoverer portlets. In our instance, you need to display a set of gauges to show whether the profit margin for each department is below, on, or above target.
To add an Oracle Discoverer gauge portlet to our BI dashboard, you add the portlet to the top left-hand region, specify the connection, select the profit margin analysis worksheet, and map the worksheet's parameter to the same generic portal parameter as the other two portlets. Then you specify how the gauges are generated and displayed (Figure 9).
This specifications tells the portlet to use the column items as the series, the row items as the values, and the Margin Percentage item as the measure. You manually set minimum and maximum values for our gauge and specify low and high thresholds of 60 percent and 80 percent. After supplying the wizard specifications now have a set of performance gauges on the BI dashboard (Figure 10).
Creating a Parameter Control
With our BI dashboard, users should be able to specify the department for which the Oracle Discoverer worksheets are run. When you chose the worksheets for the dashboard, you added a Department parameter to each of the worksheets and mapped it to a generic portlet parameter, which are then mapped to portal page parameters, so that you can enable portlet-to-portlet communication, in effect "wiring" individual portlets together. Once you've done this, you can create a Simple Parameter Form portlet, which enables a user to easily specify a parameter value that updates all of our different Discoverer portlets at the same time.
So, now that you have added all of our Oracle Discoverer Worksheet portlets to our page and bound their parameters to a generic portlet parameter, you need to create a means for users to change this parameter. To do this, you first add a Simple Parameter Form portlet (Figure 11) to the top left of the page, under the "Welcome" text.
When you proceed to define the parameter form (Figure 12), it has one active field, a drop-down list of all the available departments.
You now create a page parameter called departmentPP and map the Simple Parameter Form field and the generic portal parameters used by each of the Oracle Discoverer portlets to this page parameter (Figure 13).
Finally, you need to tell Oracle Portal to pass the value from our simple parameter form back to the BI dashboard page when the user clicks on the Submit button (Figure 14).
Deliver the BI Dashboard to Users
Now that you have created the portal page, added Oracle Discoverer worksheets, and wired them together with a shared parameter control, you can now make the BI dashboard available for users (Figure 15).
Users can use the Department parameter control at the top left of the page to select the department for which the dashboard reports are run, and they can also take advantage of the customization feature to change the type of graph displayed (Figure 16).
If required, the user can click on the Analyze link at the bottom of each portlet for an up-to-date view of the detail-level data behind each portlet. (Note: You need to configure Oracle Discoverer to use Oracle Single Sign-On in order to use the Analyze facility.)
Hints and Tips
This article has explained what a business intelligence dashboard is all about and how you can easily build one by using Oracle Database 10g, Oracle Business Intelligence 10g, and Oracle Portal. You've built a simple portal page framework, published some Oracle Discoverer worksheets as portlets, and wired them together by using a shared Department parameter. You can create your own dashboard with software you can download today from OTN, and you can easily install the example data when you install Oracle Business Intelligence Discoverer Administrator.
Mark Rittman is a Certified Oracle Professional DBA and works as a technical account manager at SolStonePlus , specializing in developing business intelligence and data warehousing applications by using Oracle Database, Oracle Application Server, Oracle Discoverer, Oracle Warehouse Builder, and Oracle OLAP. Outside of SolStonePlus, Mark chairs the UKOUG BI and Reporting Tools SIG and runs a Weblog dedicated to Oracle BI and data warehousing technology. Rittman is an Oracle ACE and is a regular speaker at Oracle user events in the UK, Europe, and the United States.