Purpose

This tutorial covers using Oracle Map Builder and Oracle Map Viewer to build and embed maps for use in Oracle Business Intelligence analyses and dashboards.

Time to Complete

Approximately 90 minutes

Introduction

In this Oracle by Example (OBE) tutorial you learn how to use Oracle Map Builder to build a map, use Oracle Map Viewer to bring the map online for integration with Oracle Business Intelligence (OBI), and then embed the map into a Map view in an OBI analysis. Please note that this tutorial provides only a basic introduction to Oracle Map Builder and Oracle Map Viewer for the purposes of building a map and then integrating the map into OBI. For more detailed information about Map Builder and Map Viewer, please refer to the Resources section at the end of this OBE.

Prerequisites

To import the required schemas for this OBE into your Oracle database, perform the following steps. In this tutorial you use a Map Viewer demo schema, mvdemo , and an OBI schema, obieemap . All instructions for accessing and importing these schemas are provided in this tutorial.

Sign in to SQL*Plus as a system user and create a database user named mvdemo . Use the following script for reference:
CREATE USER mvdemo IDENTIFIED BY mvdemo DEFAULT TABLESPACE USERS;

Open a command window, change the directory to the location of mvdemo.dmp , and use the following command to import the data into user mvdemo:
imp mvdemo/mvdemo file=mvdemo.dmp full=y ignore=y

If the above command fails due to character set related issues (such as IMP-00016 imp: charset conversion error), you may need to set the NLS_LANG environment variable to American_America.WE8ISO8859P1 temporarily. For instance, on Windows you can type the following in the DOS window before issuing the above imp command again:
set NLS_LANG=American_America.WE8ISO8859P1
You can ignore all other warnings from the imp command, including one that says "Unexpected end of export file encountered". The imported data is ready to be used.

Verify if the script mcsdefinition.sql has been run in your database. If not, run the script mcsdefinition.sql .
Explanation: If your database has never run this script before, you will need to run it as DBA role. To verify if this script has been run, you can log into the database (as any user), and execute the following query:
select name from user_sdo_cached_maps;
If the query produces an error that says "table or view does not exist" then this script has never been run on the database. If it does not return such an error (even if no rows were selected as shown in the screenshot), then you do not need to run the script mcsdefinition.sql.

If you do need to run this script, simply log on as a DBA, and execute the script. It will create the view USER_SDO_CACHED_MAPS for all users. This view is used to hold the map tile layer definitions and is required by Map Viewer.

Unzip mvdemosql.7z and then copy mvdemo.sql to a location on your machine. In this example mvdemo.sql is copied to D:\mvdemo\mvdemo11R1 . Run mvdemo.sql . This script populates all the necessary spatial metadata, copies the predefined styles, themes, and base maps into the proper user views, and creates spatial indexes for the imported tables. It also creates several (cached) map tile layer definitions in the view USER_SDO_CACHED_MAPS so that all the Oracle Maps tutorials will work. Here is how to run the script from a SQL*Plus session while logged in as user mvdemo:
SQL> @D:/mvdemo/mvdemo11R1/mvdemo.sql

Sign in to SQL*Plus as a system user and create a database user named obieemap . Use the following script for reference:
CREATE USER obieemap IDENTIFIED BY obieemap DEFAULT TABLESPACE USERS;

Open a command window, change the directory to the location of obieemap.dmp , and use the following command to import the OBI data into user obieemap:
imp obieemap/obieemap file=obieemap.dmp full=y ignore=y

Verify the import. Connect as obieemap with password obieemap and use the following script as a reference:
select table_name from user_tables;

By default map_data_source name= should point to "mvdemo" . If not, change it to map_data_source name="mvdemo" . Uncomment the data source definition by removing the XML comment tags, and then modify the database connection and login information to reflect your mvdemo schema. Use the screenshot as a reference. Make sure you have an exclamation point “!” in front of the supplied login password value. Then next time you restart Map Viewer it will automatically obfuscate this password.

Enter the connection information for your environment. Use the screenshot for reference. For the environment used to build this tutorial, the information is:

Connection Name: mvdemo
User: mvdemo
Password: mvdemo
Host: localhost
Post: 1521
SID: orcl

In this set of steps you use Oracle Map Builder to create a geometry theme based on the STATES table. A theme is a visual representation of a particular data layer. Typically, a theme is associated with a spatial geometry layer, that is, with a column of type SDO_GEOMETRY in a table or view. In this example, a geometry theme named THEME_STATES is associated with a spatial column named GEOM in the STATES table in the mvdemo schema.

If necessary, click the Show Data button at the bottom of the screen to display the Data Navigator.

Click OK to close the Edit Info Columns dialog. The column/name pair is added to the THEME_STATES geometry theme. This is the most critical step for OBI integration. You defined the unique key column in the map data (STATE_ABRV) that will align with an OBI presentation attribute. Later in this OBE you complete this integration using the OBI Presentation Services Administration page.

Click OK to close the Edit Info Columns dialog. The column/name pair is added to the THEME_CITIES geometry theme. Again, this is the most critical step for OBI integration. You defined the unique key column in the map data (CITY) that will align with an OBI presentation attribute. Later in this OBE you complete this integration using the OBI Presentation Services Administration page.

Enter the following scale ranges for the themes:

THEME_CITIES: Min Scale: 75,000,000 Max Scale: 0
THEME_STATES: Min Scale: 150,000,000 Max Scale: 0

Select the Preview tab and click the green button to display the map. Note at the bottom of Map Builder application the scale values for current visualization. The resulting map should contain just the themes that are in the scale range. Play with the zoom in and zoom out options to see the map results. The screenshot shows the map zoomed in to California.

In this set of steps you use Oracle Map Builder to create a tile layer. A tile layer is a map definition to be used in an Oracle Maps application. Each tile layer is associated with a base map. Please note that the steps for creating a tile layer can also be performed using the Map Viewer Admin interface. However, that method is not presented in this tutorial.

Right-click the Tile Layers node in the Metadata Navigator and then select Create Map Tile Layer to open the Select Base Map dialog.

Click OK to close the Select BI Key Columns dialog. Confirm that Sample Data is visible for the STATE_ABRV Layer Key. There need not be any direct relation between the column used in the spatial schema and the column mapped in Oracle BI. You just need to ensure that the attributes match. In this case, the State column comes from the OBIEEMAP schema and the STATE_ABRV column (layer key) comes from STATES table in the mvdemo schema.

Use the OBIEEMAP subject area to create the following new analysis with two measures:
Geo > State
Sales > Dollars
Sales > Units Ordered

This tutorial showed you how to use Oracle Map Builder and Oracle Map Viewer to build and embed maps for use in Oracle Business Intelligence analyses and dashboards.

In this tutorial, you have learned how to:

Resources

Please refer to the following resources for more information about the topics covered in this OBE:

Credits

To help navigate this Oracle by Example, note the following: