OBIEE & Essbase Substitution Variables

In one of my first blog post I indicated that I may deviate from my primary topic to write about other topics that may get my attention.

This topic is one that has taken my attention away from my recent series of past posts on the topic of Maps with OBIEE.

When working with OBIEE and relational databases it is common for a Connection Pool to be dedicated for repository variables.  In fact there is an option that RPD Developers will set so that the Connection Pool for used for Initialization Blocks that populate the repository variabes is different than the Connection Pool for the data.  This serves a few purposes, foremost is that the user ID for the Initialization Blocks and Repository Variables can be set so that it is purely used for this administrative data.

When working with Essbase as a data source I have found that it is more convenient to have a completely separate "database" in the physical layer to support Repository Variables and more specifically Essbase Substitution Variables.

Depending on the scope of the Essbase variable, the naming convention for the BI Server variable is as described below.
Server instance scope
<server name>:<var name>
Application scope
<server name>:<app name>:<var name>
Cube scope
<server name>:<app name>:<cube name>:<var name>

So that my RPD is not specifically tied to an environment and Essbase Server Name I will rename the "database server" and connection pool to a more business purpose name.  Also note that since this imported database will solely be used for Substitution Variables set the name so that there are no spaces in the name.

Finally the initialization block and variable information will need to be updated to match the new name of the "database".  This removes environment issues when migrating Dev > Test > Prod.
 

If there are Application or Cube level substitution variables then import that cube into this "database". 

For the cube(s) that will be used for OBI Analysis; they should be imported into a separate "database" in the Physical Layer.  There will need to be some clean up in the variable manager, delete any repository variables and initialization blocks that are created for those cubes that are used in OBI Analysis.

 

Working with 3rd Party Shapefiles

The past few posts may have seemed rather fundamental, however if these basic tasks are not done it is impossible to be successful in deploying maps within OBIEE.

When I've done OBIEE and Spatial Analytic presentations at conferences I am often asked where can one get more maps.  I covered online sources for map shapefiles in this previous post.

With MapBuilder you now have 2 options for using map shapefiles.  The original and my preferred use is to import shapefiles into the Oracle database as a table with geometry data and build necessary themes from that base table.  In version 11 of MapBuilder many enhancements were add and most notably with regard to ShapeFiles is Custom Geometry Themes.  I will leave you to the documentation to explore this option or wait until I write up a blog post on it.


Importing Shapefiles

The shapefile used in the following example was downloaded from U.S. Census Bureau.
 
http://www.census.gov/geo/maps-data/data/tiger.html
 
As indicated in the shapefile post the Cartographic Boundary Shapefiles will be sufficient.
 

 
The next option is the map scale and for OBIEE maps.  The 20m = 1:20,000,000 resolution level will be fine. 
 

 
Download and save to a location on your computer and extract the .zip file.

 
You can look at the metadata that is included with the map by opening the .dbf file with Excel.
 

 
Note that this data file contains administrative boundary information for the 50 states and the District of Columbia and Puerto Rico.
 
Looking though the data we need to find column(s) that contain unique data.  This is important when linking to OBIEE tabular data. 
 
The key columns in this data set that we can work with are:

• STATEFP - State FIPS
• GEOID
• STUPS (abbreviation)
• NAME

As a note when working with US County data the State and County FIPS or a composite County - State Abbreviation will be needed since a County Name is not unique.
 
Now that the shapefile has been extracted it needs to be uploaded into the mvdata schema on the Oracle Database.
 
Launch MapBuilder and load the connection to the mvdata schema.

From top menu select Tools > Import Shapefile
 

Step 1 of the Import Shapefile is the Data Selection dialog.

Keep the option set to Single FIle and Click the Shapefile button

An open file dialog window will appear. Navigate to your saved and extracted Shapefiles and Select the .shp file

The Geometry table name is auto populated based on file name it can be changed, just be aware of database naming requirements.  Click Next

Set the SRID.  This is set from the .prj file within the Shapefile collection.  Keep the checkbox for Create/Update spatial index checked.  Click Next

A theme can be automatically created upon import.  For the sake of this exercise it will be skipped and saved for later example.  Click Next

Review the activities to be executed.  Click Finish

An import status window will display

Once the import is complete click the Refresh button to reload MapBuilder

In Lower left corner click the Show Data toggle and Expand the Spatial Tables and Geometry Tables.

Find the imported table.  Right click and select Preview

On the right side of the toolbar there is a green arrow to start the query to preview the map

The icons in upper left of toolbar allow for zoom and pan. 

Zoom in on the Map to get a better view.

The Identify tool in the Preview control allows you to see the attribute data that accompanies the map.

Now that there is a spatial data loaded to our MVDATA schema we are ready to create a Map Theme that we can use as a Map Layer in OBIEE, but that is the topic for another post.

 

Map Builder

This post will focus on MapBuilder.

For map views to be used within an OBI Analysis there needs to be some basic backend components set up.

MapBuilder helps with this by creating and managing the Map Metadata.

Download the MapBuilder tool from here - for these blog posts I will be working with the 11.x version.

The one thing I found confusing when I first started working with Map Views was aligning objects in one tool with objects in another tool.  As quick refernce this picture shows objects in MapBuilder that align to OBIEE Map Data Management.

Definition of the objects within MapBuilder:

Theme

● Collection of geometries and includes information for visually representing a layer (default formats, labels)
● The tables defining shapes (geometry columns) are assumed to be in the Oracle DB

Basemap

● Logical collection of themes with associated scale and order
● Mapviewer engine generates tile images based on the basemap definition

Tile Layer

● An instance of basemap presented as image by the Mapviewer engine


When working with MapBuilder the tool is making entries into the following views.

USER_SDO_MAPS – Stores definitions of base maps

USER_SDO_THEMES – stores definitions of individual map layers or themes

USER_SDO_STYLES – stores individual map symbols and other types of styles such as LINE patterns, COLOR definitions and thematic mapping styles

USER_SDO_CACHED_MAPS – Stores maps in Tiles for faster retrieval

Launch the mapbuilder.jar from the downloaded file.  MapBuilder opens to the following view. Click the Connection control to create a connection to the MVDATA schema that was created in the previous post.

Click the Add button

Provide your connection information for the MVDATA Schema

Test the connection

Click Load to connect and return to main screen

Expand Tile Layers and you will see the Oracle Elocation_Map layer we added in our MapViewer post.

We now have MapBuilder configured.  In the next post we will look at loading Shapefiles into our Database as a Geometry table that we will then use to make a Map Theme that we can use in OBIEE as a Map Layer.