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.
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. One must create a geometry theme by clicking on a table. Right click opens the possibility to create a geometry theme. 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:)
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:)
