TECHNOLOGY: Business Intelligence

As Published In May/June 2011

Putting Business Intelligence on the Map

Map geographies and business information together to see results.

Most business intelligence applications include an element of spatial, or geographic, data—with sales and performance data often analyzed in terms of dimensions such as customer location, geography, and sales territory. Oracle Database, Enterprise Edition 11g Release 2, with the included Oracle Locator feature and the Oracle Spatial database option, can store spatial data such as maps and points of interest along with your data for analysis, and the Oracle Fusion Middleware MapViewer feature can render this spatial data in the form of Web-based interactive maps.

Oracle Business Intelligence Enterprise Edition 11g, part of Oracle Fusion Middleware, is preintegrated with MapViewer and gives you the ability to create business intelligence analyses and dashboards that include maps. This article looks at how to create map views and combine them with other data visualizations by using Oracle Business Intelligence Enterprise Edition 11g.

If you would like to try out the examples in this column, you can download the sample data and installation instructions. Download the sample spatial data and maps used by the examples separately from Oracle Technology Network. 

Creating a Custom Point Layer Map

In the first example, you are analyzing retail data for stores in your organization. Most of your stores are in the San Francisco Bay Area, and you will now create a map view that places each store in its correct location, along with a legend and a store icon colored either red, amber, or green, depending on how well the store is performing. Prior to creating this map, you will need to configure Oracle Business Intelligence Enterprise Edition 11g to work with MapViewer and associate the maps and layers within your maps with subject area columns in your data set. The sample data download provided with this column contains instructions on how to perform this configuration, and the configuration is also described in Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1). 

To create the first map view, do the following: 

1. From the common header menu on the Oracle Business Intelligence Enterprise Edition 11g home page, select New -> Analysis.

2. When prompted, select the Sales – Fact Sales subject area, and then from the Subject Areas panel on the left-hand side of the screen, choose the following columns for your analysis:

   Dim Stores.Metro City
   Dim Stores.Latitude
   Dim Stores.Longitude
   Fact Sales.Revenue 

3. Move your cursor over the Dim Stores.MetroCity column in the Selected Columns area on the right-hand side of the screen, and select Filter from the menu. Use the New Filter dialog box to create the following filter:  

   Metro City is equal to/is in
   SAN FRANCISCO

   Then switch to the Results tab and confirm that a list of store locations in San Francisco, along with revenue figures, is displayed. 

4. Now you can create your map: locate the Views panel at the bottom left of the screen, and select New View -> Map. When the map view is displayed, select OBIEE_SF_MAP1 from the list of maps shown at the top of the view.   

5. The new map view is now ready for editing. A panel on the right-hand side enables you to define map formats. Click the Delete button next to the default OBIEE_CITY map layer to remove it from the list of formats, because you will create your own format in the following steps. Then click the Add New Map Formats button to add an image map format, using a custom point layer. This will enable you to place icons on the map, based on latitude and longitude coordinates. 

6. In the Image (Custom Point Layer) dialog box that appears, enter the following settings to place the stores in your sample data set on the map: 

   X (e.g. Longitude) : Longitude
   Y (e.g. Latitude)     : Latitude
   Tooltips                   : Longitude,
                                       Latitude,
                                       Revenue
   Vary Image By       :  Revenue
   Bin Type                  :  Percentile Binning
   Bins                          : 3

   In the Select Image dialog box, select the Red, Yellow, and then Green circles (traffic lights) for the 0-33%, 33%-66%, and 66%-100% bins, respectively. 

7. Click OK to finish this map definition. Then use the pan and zoom buttons on the left-hand side of the map to locate downtown San Francisco, as shown in Figure 1.  

Figure 1: The store icons on this map view of San Francisco reflect performance data.

You have now created your first map view. Once you have viewed the map, click the Home link at the top of the common header menu to return to the Oracle Business Intelligence Enterprise Edition home page. 

Creating Thematic Maps

Your first map, OBIEE_SF_MAP1, used x and y coordinates to plot the location of your stores in the San Francisco Bay Area. In this next example, you will display a larger-scale map of the continental U.S. and place pie charts over the states in which the company operates, with the charts showing the breakdown of sales across product categories. 

To create this map, do the following: 

1. Select New -> Analysis from the Oracle Business Intelligence menu at the top of the screen, and select Sales – Fact Sales as the subject area.

2. From the Subject Areas panel on the left-hand side of the screen, choose the following columns to add them to the Selected Columns area: 

   Dim Stores.Country and State Code
   Dim Products.Product Category
   Fact Sales.Revenue

   Because you will be mapping stores located in all of the company’s U.S. territories, you do not need to apply a filter to the selection criteria.

   Switch to the Results tab and confirm that a list of states, together with product categories and revenue amounts, is shown.

3. To create the map, locate the Views panel and click the New View button. Select Map from the list of views, and select the OBIEE_WORLD_MAP as the map to be displayed within the map view.

4. As with the previous map view, navigate to the Map Formats panel and delete the default map format. Click the Add New Map Formats button, but this time select Pie Graph from the list of formats. Then, when prompted, select OBIEE_STATE from the list of map layers available.

5. In the Pie Graph dialog box that appears, set the following for the pie charts:  

   Tooltips          : Country and
                              State Code;
                              Revenue
   Slice Size       : Revenue
   Graph Size     : 15
   Slices              : Product Category

Click OK to save the settings, and then with the map view displayed, use the pan and zoom buttons to display the U.S. West Coast. Locate Oregon on the map, place your cursor over the pie chart, and then click the legend. Note that the state name and the product categories are all hyperlinks—you can use these to pass filter values to other analyses, as you will see in a moment. For now, though, save this map to the Presentation Server catalog, using the name Country and State Map. 

Using Master-Detail Actions

Because map views are fully integrated into Oracle Business Intelligence Enterprise Edition 11g, you can use links displayed in map legends to pass parameters to other views. By doing this, you can make selections by using the map view and use these to change the values displayed in associated chart, table, and other views. 

In this final example, you will use the Country and State Map you created previously to drive changes in two other views. To do this, start by creating two additional analyses that you will later link to the map view. 

1. First create an analysis using the Dim Products.Product Category, Dim Times.Month, and Fact Sales.Revenue subject area columns, and display the results as a horizontal bar chart. Place Dim Products.Product Category in the Graph Prompts area of the graph layout, and save this analysis to the Presentation Server catalog, naming the analysis Chart Detail.   

2. Then create another analysis, this time based on the Dim Stores.Country and State Code, Dim Stores.RegionName, and Fact Sales.Revenue columns. Using the Table view that is provided by default for a new analysis, use the Layout Editor to move Dim Stores.Country and State Code to the Table Prompts area. Save this analysis to the Presentation Server catalog, naming it Table Detail. 

Now you can start to add links between the map and these new analyses, using the new master-detail linking feature introduced in Oracle Business Intelligence Enterprise Edition 11g.

To make use of master-detail linking for these examples, first configure the analysis containing the map to broadcast master-detail event messages for the Product Category and Country and State Code columns and then configure individual views within the last two analyses you created to listen for these events: 

1. From the common header menu, select Open -> Open and navigate to where you saved the Country and State Map. Then, with the analysis open for editing, select the Criteria tab to show the list of columns used in the analysis.   

2. Move your cursor over the Product Category column in the Selected Columns area, and select Column Properties from the menu. When the Column Properties dialog box appears, select the Interaction tab and then change the Value Primary Interaction setting to Send Master-Detail Events. When the Specify Channel setting appears, enter MDCAT. 

3. Now repeat the above step for the Dim Stores.Country and State Code column, this time entering MDSTATE for the Specify Channel setting. Then, with these changes made, save the analysis back to the Presentation Server catalog.

4. Setting the map analysis to broadcast master-detail events is the first part of this process; the second is to configure individual views within the target analyses to listen for them. To do this, using the common header menu, select Open -> Open, navigate to the Chart Detail analysis, and select the Results tab to show the bar chart. Click the Edit View button for the chart view, and then click Edit Graph Properties. 

   Then, in the Graph Properties dialog box, check the Listen To Master-Detail Events checkbox and enter MDCAT in the Event Channel text box. Click OK to close the dialog box, and then save the analysis back to the Presentation Server catalog. 

5. Now repeat the previous step for the Table Detail analysis by editing the table view within the analysis and clicking the Table View Properties button to display the Table Properties dialog box. Check the Listen to Master-Detail Events checkbox as before, but this time enter MDSTATE for Event Channel. This will ensure that this table view responds to state and country selections made on the map. Save the analysis back to the Presentation Server catalog once you have finished making the changes. 

To view the analyses together and check that the two detail-level analyses respond to selections made in the map view, select Dashboard -> My Dashboard from the common header menu and use the dashboard editor to add the three analyses to a new dashboard page, with the map displayed in a section just above the two detail-level analyses, as shown in Figure 2.
 

Figure 2: This dashboard page displays pie charts and detail-level analyses.

Save and then run the new dashboard, which should look like Figure 2. Move your cursor over the pie chart displayed over California, and click the legend to display the action links. Click the Gifts product category, and watch how the bar chart listens for and displays your product category selection. Now click the USA_CA link in the legend, and see how it changes the values displayed in the table. You can use map views to display data with a geographic component and use map selections to drive filter value changes in other analyses on the dashboard.

Conclusion

Most business intelligence applications include an element of geographic data, and the new integrated mapping feature in Oracle Business Intelligence Enterprise Edition 11g makes it easy to visualize this data in the form of rich, interactive maps. With master-detail links, users can connect map views to other views, creating an interactive query environment built around geographic and other data.

Send us your comments