Thursday, June 12, 2014

Mapviewer Integration with OBIEE 11g (11.1.1.6 and higher)

With the release of OBIEE 11.1.1.6, mapviewer comes pre-configured with weblogic and the only installation steps required are:


  • installation of navteq mapdata to your oracle database
  • establish a column based relationship between the map and a subject area

You do not have to modify any weblogic XML files, install any mapviewer jar files, or do any configuration within weblogic. Below is a step by step guide on how to configure and use mapviewer on your 11.1.1.6.x box:

Step 1: Download the mapviewer 'mvdemo' data into your Oracle database

Oracle provides pre-configured maps, coordinates, and navteq data that use can use in your reports. You need to download the MVDemo Sample Data Set .

Step 2: Create the mvdemo schema in your Oracle database

We're going to import the navteq map data into an mvdemo schema in step 3. Let's go ahead and create the mvdemo schema by typing the following command into your SQLPlus prompt:

grant connect, resource, create view to mvdemo identified by mvdemo

Note that you will need the appropriate privileges to create this account. If denied, try logging in as sysdba by typing the following command into sqplus:

CONNECT / AS sysdba

Step 3: Import navteq data dump to your Oracle database

Unzip the MV Demo Sample Data Set you just downloaded, and note the location of the 'mvdemp.dmp' file. This is the file we will import to the database.


Step 3.1)
Find the imp utility on your machine. It is usually located in your $ORACLE_HOME\product\11.x.x\dbhome_1\BIN folder

Step 3.2) Navigate to that folder via command line and run the following command:

imp mvdemo/mvdemo@ORCL file=mvdemo.dmp full=y ignore=y
where ORCL is your database SID
and file=mvdemo.dmp is the path (including mvdemo.dmp) to the dump file

You should get the following result:


Step 4) Import the map meta data

The Map Viewer Sample Data Set comes with city, county, state, highway, and other geographical map based data that you can impose onto the map. We're going to import this data set by running the following command in sqlplus:
:
@mvdemo.sql

Note that you must include the path of the mvdemo.sql file e.g. @C:\folder\folder1\mvdemo\mvdemo.sql

Step 5) Add your Oracle Database as a MapViewer Data Source

No, we're not doing this in weblogic - Mapviewer data source configuration is still done in your http://localhost:9704/mapviewer location


You'll arrive at a landing page like below, where you must click the 'Admin' button to log in:




5.1) login user your weblogic username/password

5.2) You should arrive at a home page with a link to view Datasources. Click it and you'll appear at:

Name = name/description of your data source
Host = hostname/ip address of your database
Port = database port number
SID = service ID of your oracle database (by default it is orcl)
user/password: If you followed my above steps, it will be mvdemo/mvdemo
# Mappers and Max Connections specify how many simultaneous users can connect to the MapViewer db. For diagnostic purposes I would make this relatively high, and once development is complete you can adjust as needed.
Step 6) Modify the mapViewerConfig.xml file to include the new data source
I'm a little surprised as to why this must be done manually, if anyone has any insight - please feel free to leave feedback. After you add the data source as outlined in step 5, you must then modify the mapViewerConfig.XML file to include said datasource, otherwise when the BI Server is rebooted, your datasource connection will be removed!
Luckily, this step is not too difficult
6.1) In :7001/mapviewer , log into your Admin screen and navigate to Management -> Configuration
Then add the following XML to the bottom of the config file, right above the </MappperConfig> line.
  <map_data_source name="mvdemo"
                   jdbc_host="db1.my_corp.com"
                   jdbc_sid="orcl"
                   jdbc_port="1521"
                   jdbc_user="scott"
                   jdbc_password="!tiger" 
                   jdbc_mode="thin"
                   number_of_mappers="3"
                   allow_jdbc_theme_based_foi="false"
   />

Modify each line using the inputs you provided in step 5, but in the jdbc_password input should have a ! infront of it as that is Oracle's indicator to encrypt the password upon restart.
Step 7)  Import a map layer into Answers 

We've completed all of the back end work required to create a map. Now we'll go into Answers -> Administration -> Manage Map Data and import a layer (theme) that we'll use for our map.
A theme is a visual representation representation of the data, and arguably the most important component in creating a map. In this example let's use the "THEME_DEMO_COUNTIES" layer, which will give us the ability to impose a dataset over various Counties in the USA.
Step 8) Specify the BI Column used to associate the THEME_DEMO_COUNTIES layer to a dataset

The theme we're using, "THEME_DEMO_COUNTIES" stores attributes of Counties (county name, county lines, etc) which we can visualize on a map. We need to identify a way to 'join' the data set in OBIEE to the dataset of THEME_DEMO_COUNTIES.
After saving the layer you just created, click the 'Edit' button (pencil) to bring up the screen below.
Notice there is 'Layer Key' with the following column values; County, Fips, Population, State.  We are going to use 'County' as the map column to join to the subject area.
Next we need to specify a column from our subject area which contains 'County'.  
Step 9) Specify the Background Map

In steps 7 and 8 we specified a theme (visual representation of the data) and identified how to join the map data to our subject area (via County column). Now we need to specify which map we're the theme will use.
In the 'Background' tab, create a new background map and specify 'DEMO_MAP' as the background map.
After saving, edit the map ensure the THEME_DEMO_COUNTIES has successfully been applied:
It will default to the middle of the USA but I decided to zoom into California :)
Step 10) Create a Report using the County Column 

Now we're ready to create the report! Create a new analysis, select the County column you specified in step 7, and a fact column which joins to the county dimension. Click the results tab, then New View -> Maps.  
The result below outlines only California because the dataset I created uses only California Counties. 
Note that I did not go into the MapBuilder tool, which you can use if you want to create custom themes and maps (e.g. map of a building, school, casino, etc). But this works great for a proof of concept!
 
 
 
Ref :  http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/configmap.htm
 
 
 
Thanks
Saleem Syed (SS)

Applying OBIEE 11.1.1.6.5 Patches to Windows Server Environment

Oracle  released a new patch set for OBIEE 11g – 11.1.1.6.5. The patch set can be downloaded from Oracle Support. This patch set is available for Oracle Business Intelligence Enterprise Edition 11.1.1.6.0, 11.1.1.6.1, 11.1.1.6.2, 11.1.1.6.2 BP1 and 11.1.1.6.4. This new patch set comprises of following patches:

  • Patch 14696072 (1 of 7) Oracle Business Intelligence Installer. (generic)
  • Patch 14733356 (2 of 7) Oracle Real Time Decisions. (generic)
  • Patch 14678543 (3 of 7) Oracle Business Intelligence Publisher. (generic)
  • Patch 14733390 (4 of 7) Oracle Business Intelligence ADF Components. (generic)
  • Patch 14733413 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x. (port specific)
  • Patch 14665284 (6 of 7) Oracle Business Intelligence. (port specific)
  • Patch 14733370 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer (generic)
The instructions to apply the above patches are identical, and are contained in the readme file for patch 14696072.

Here are the steps to apply the patches to a Windows 64 bit install of OBIEE:
Step 1:Back up the OBIEE install and shutdown all OBIEE processes including weblogic.

Step 2: Download the zipped patch files from Oracle Support.
Download patch files
Download patch files

Step 3: Unzip the files and put them in your {OBIEE_HOME}/Oracle_BI1 folder. Example: C:\OBIEE11g\Oracle_BI1
Unzip the files and put them in your {OBIEE_HOME}/Oracle_BI1 folder
Unzip the files and put them in your {OBIEE_HOME}/Oracle_BI1 folder

Step 4: Clear the files that are associated with the Oracle BI EE Catalog Manager binary cache. Go to {ORACLE_HOME}\Oracle_BI1\bifoundation\web\catalogmanager\configuration\ and remove the following files if they exist:
  • org.eclipse.osgi
  • org.eclipse.equinox.app

Step 5: Open a Command window and before applying the patches. Ensure you are in the {ORACLE_HOME}\Oracle_BI1 folder:
Set environment variables
Set environment variables
set ORACLE_HOME=C:\obiee11g\Oracle_BI1
set PATH=%ORACLE_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk
set PATH=%JAVA_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch;%PATH%

Step 6: Apply the sever patches in the following order:
C:\obiee11g\Oracle_BI1>cd 14696072

C:\obiee11g\Oracle_BI1\14696072>opatch apply
OPatch succeeded.

C:\obiee11g\Oracle_BI1\14696072>cd ../14733356

C:\obiee11g\Oracle_BI1\14733356>opatch apply
OPatch succeeded.

C:\obiee11g\Oracle_BI1\14733356>cd ../14678543

C:\obiee11g\Oracle_BI1\14678543>opatch apply
OPatch succeeded.

C:\obiee11g\Oracle_BI1\14678543>cd ../14733390

C:\obiee11g\Oracle_BI1\14733390>opatch apply
OPatch succeeded.

C:\obiee11g\Oracle_BI1\14733390>cd ../14733413

C:\obiee11g\Oracle_BI1\14733413>opatch apply
OPatch completed with warnings.

C:\obiee11g\Oracle_BI1\14733413>cd ../14665284

C:\obiee11g\Oracle_BI1\14665284>opatch apply
OPatch completed with warnings.

C:\obiee11g\Oracle_BI1\14665284>cd ../14733370

C:\obiee11g\Oracle_BI1\14733370>opatch apply
OPatch succeeded.

Step 7: Check the patches have been applied. Confirm that all the Oracle BI 11.1.1.6.5 patches shown above appear in the list of applied patches (along with any other patches that have previously been applied to the BI System).
C:\obiee11g\Oracle_BI1>opatch lsinventory

Step 8: Copy the BIPublisherDesktop32.exe and BIPublisherDesktop64.exe files from the ORACLE_BI_HOME/clients/bipublisher/repository/Tools directory to the MW_HOME/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Tools directory.

Step 9: Start up OBIEE services and validate the patch has been applied.
Validate the patch has been applied
Validate the patch has been applied.

NOTE:  please clear the browser cache before you login and test the new patchset.



This Patches Applying process is same even for the next pacthes released and   till 11.1.1.6.12.

Thanks
Saleem Syed (SS)