Oracle BI EE not only helps you to visualize information using charts and graphs, but it also gives you the opportunity to add geographical dimension to your reports.
With today’s globalization of business, many organizations have their branch offices located in various countries or even across different continents. Viewing individual branches and their performance, presented on the geographical map, can give companies further insights into their business. Oracle provides a powerful engine that can analyze and display the data from your data warehouse on the map or even integrate it with Google maps.
Let’s start with a simple overview of the architecture and continue with a demo drill down report that automatically focuses the map and draws the selected items from the report.
Architecture
So what do you need in order to view geographical data in OBIEE?
Apart from Oracle database and Oracle Business Intelligence installation, there are 2 additional components required for rendering maps in your BI tool:
- Oracle Spatial
- MapViewer
Oracle Spatial provides an SQL schema MDSYS and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle database.
MapViewer basically contains a rendering engine that “reads” data from Oracle Spatial schema and draws them on the screen as defined in the database. MapViewer also includes MapBuilder tool that is used to define the way different map layers and features on the map will appear.
Visualizing Drill Down on the Map
Next you need to know how to create a simple drill down report with navigation path and integrate it with an embedded map in Oracle Business Intelligence dashboard.
Assuming that you already have Oracle Spatial available in your Oracle database and MapViewer properly installed and configured, you need to perform the following steps to integrate and visualize your geographical information with OBI reports:
- First of all, create 2 or more Answers in OBI that contain different levels of hierarchy that you will use for navigated drill down. In the example shown, we have selected geographical hierarchy State->County->City and the measure that we want to show are the sales for all shops in the chosen region.
- Create a map that will accept the parameter from the dashboard and display the appropriate shops and regions on the map. This step requires certain understanding of JavaScript in order to use JavaScript APIs provided for MapViewer. For each Answer created in step 1, assign one map that will accept the parameter from the Answer.
- Use MapBuilder to define theme based FOI layer for each map, in our case for: city, county and region, and ensure that each theme is loaded in the corresponding map. Therefore when one of the maps is viewed, appropriate set of FOIs will be rendered on the map. For example, if we load the map with theme “sales for the cities”, depending on the city that is passed as a parameter, all the shops in that city will be drawn on the map. Use class MVThemeBasedFOI in the javascript to load the themes defined.
- Embed each map represented as HTML page with Javascript using Narrative view in the corresponding Answers you have created earlier. Pass the value of the geographical region to the map and display features related to this area on the map. See the picture bellow for details.
- Set the navigation drill path in the answer to point to the one with lower detail answer page.
By successfully completing the last step, you will have a final result which shows information from the reports on the map as you drill through the report.
For example, by clicking on the report selecting the county “Los Angeles”, OBI will take us to another report showing only data for Los Angeles county and the map will render and focus to the sales and branches only in this selected county. We can continue further down the hierarchy by choosing the city (e.g. Santa Monica) within the chosen county in the given report.
If you have any questions or feedback about using Oracle Spatial, feel free to leave a comment below
Posts
