Monthly Archives: January 2016

A simple introduction into creating spatial report

I write this to show you how to use Oracle Map Builder and Map Viewer to create spatial reports within Oracle BIEE.
I assume you have a set of tables that can be used to generate future spatial reports. I use three tables:
[1] Edge that contain a filiaal_num as an identifier and a point geographical object (geometry_pcode) that indicates the longitude/ latitude of that object. This object stored in a field with a special spatial type “MDSYS”.”SDO_GEOMETRY”.
[2] Block_Groups that contain a code as an identifier and a polygon geographical object (geometry) that provide a series of longitude/ latitudes on an area. This object is also stored in a field with a a special type “MDSYS”.”SDO_GEOMETRY”.
[3] Omzet, which has two foreign keys, one referring to filiaal_num and one referring to code along with a numeric value omzet.
Here for a table creation file.
I also assume that one has created special indices on the table EDGE and Block_Groups. To create such indices, one must verify if the view USER_SDO_GEOM_METADATA contains the required metadata. This can be verified with this query: select * from USER_SDO_GEOM_METADATA where table_name in (‘EDGE’,’BLOCK_GROUPS’); If that returns two rows, you are fine to create the index. If not, such content must be created by:
Insert into USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) values (‘BLOCK_GROUPS’,’GEOMETRY’,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(‘X’,-180,180,0.5),MDSYS.SDO_DIM_ELEMENT(‘X’,-90,90,0.5)),’8307′);
Insert into USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) values (‘EDGE’,’GEOMETRY_PCODE’,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(‘LONG’,-180,180,0.005),MDSYS.SDO_DIM_ELEMENT(‘LAT’,-90,90,0.005)),’8307′);
After that the indices can be created by (example):
CREATE INDEX edge_idx ON obiee_navteq.edge(geometry_pcode) INDEXTYPE IS mdsys.spatial_index;

I also assume you have created the necessary objects in the presentation layer (screenshot from OBIEE Administration tool.
Untitled

One then starts creating the layers. One layer for the filiaal_num with its corresponding point geographical object and one layer for the code with its corresponding polygon geographical object.
We use mapbuilder to create these layers. The layers are indicated as geometry themes in mapbuilder.
Mapbuilder is a jar file that can be downloaded on your client machine. One must first create a connection to OBIEE. I used a custom string to do so (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.36)(PORT =1521)) ) (CONNECT_DATA = (SERVICE_NAME =pdborcl))).
Right click on geometry theme opens the possibility to create a new theme. You see some base tables. This list corresponds to the entries in the USER_SDO_GEOM_METADATA view. As EDGE and BLOCK_GROUPS are entered in that view, we must recognize them. I created two geographical themes: themSIM1 for BLOCK_GROUPS and themeSIM2 for EDGE. I understand that under “Advanced”, we must indicate the identifier that will be used to link the OBIEE data as shown in the presentation layer to the geographical theme. In total I have:
For themeSIM1 (on Block_Groups) ;
Rendering: Color = CB_SEQ4_Orange_1
Labelling: column = Gemeente
Advanced; Info Column = Code
For themeSIM2 (on EDGE)
Rendering: Color=C.RED
Labelling:column = Straatnaam
Advanced:Info_Column = Filiaal_Num

The layers are now created, time to position them on a predefined map.
Open in the BI Analytics environment in Administration “Manage Map Data”. It will be opened with something like http://van-maanen.com:7780/analytics/saw.dll?ManageMapData.
Import the layers (themeSIM1 and themeSIM2).
Then edit the layers and create the link between the presentation layer and the geographical themes.
For themeSIM1:
Layer key = code
Geometry Type = Polygon
BI Key Columns = Subject Area Tom; BI key = code
For themeSIM1:
Layer key = code
Geometry Type = Polygon
BI Key Columns = Subject Area Tom; BI key = code
Layer Key = Filiaal_num
Geometry Type = Point
BI Key Columns = Subject Area Tom; BI key = Filiaal_Num

In Background Maps, import a background map. Let us take Bing, which is a predefined map.
Then, add the two layers within that background map.
Indicate on which zoomlevels the information must be made visible.

Click ok.

Did it work?
Yes: it did (see below:)
Untitled2

However, note that labels nor the colours that we have chosen are shown in the map. This can only be shown if we return to map builder.
We left off in map builder when the geographical themes were created. We now create a base map from them in map builder.
Create a base map in map builder. As we work in the Netherlands, we must take 10 000 000 as minimum scale for themeSIM1 and a much lower figure (say 250000) for themeSIM2.
Then create a so-called tile in either mapviewer or mapbuilder.
Bring it online in mapviewer.
Then, switch back back to manage map data in the administration console in BI Analytics. Import the tile that has been created. Click on edit to verify that both geometric layers are included.
Did it work?
Yes: it did (see below:)
Untitled3

Using OBIEE spatial reporting

Using spatial reporting in OBIEE is not for the faint of heart. However it is also rewarding. The reports look great; it is new and henceforth exciting. It is challenging but the outcome stands out. However on the route to the reports, some obstacles must be overcome. Let me provide some thoughts on the obstacles that I encountered.
The first obstacle is the decision what tools to use. One must use next set of tools: [a] the mapbuilder which is an independent java tool that can be run from any client and which creates some layers that will be shown on a map, [b] the mapviewer which is used to create a map with the layers that were created by the user and [c] the “manage map data” that can be found under the “map data management” in the administration of Oracle BI presentation. To know when to use what tool was not trivial. First, the layers must be created in the mapbuilder; then one must decide on the map (to be set up in the mapviewer) and finally the map must be linked to the data in the manage map data part. It took me some time to figure this out.
Then the decision on how to set up the maps. Do we take predefined map (such as the Bing map, the Google map, the Navteq map) or do we create our own maps? Let me provide an example on a map that is created on top of an existing predefined map:
Untitled
and then an example with a map that is created by yourself:
Untitled1.
This choice has some consequences. Do you accept the default settings that are more or less imposed by the predefined map or do you want to set your own standards, labels, colours etc? Both choices have pros and cons. Something to think about. If one settles for a own map creation, one might consider using this manual. It helped me to create some maps that I created myself. If you use the predefined maps, it is somewhat trial and error. But it worked out for me.
Another choice is the model to use. One must create a model that is propagated from physical layer to the presentation layer and that can be found in the subject area. In the subject area, we must have some labels that refer to either points in space or areas in space. As an example, one must have labels that refer to shops and which can be seen as points and municipalities that are areas and for which we must have labels. Such labels are important. Somewhere down the line we must link these labels (shops, municipalities) to spatial objects (points, polygons) in order to render them on screen.
A final choice is where the spatial data (longitudes/ latitudes) might be derived from. Do we download them/ Do we copy them from an existing source or do we create them ourselves. Everything is possible. In short: choices, choices, choices!