Part 2 - Database

This is the second in a series of posts on Maps in OBIEE.  Part 1 - Map Basics is here.

In the database geometry objects are stored in a special column type called SDO_GEOMETRY that is specifically for geometric data.  The database stores geometry objects, such as States, as an ordered set of points, which are then connected by line segments by Map Viewer to render the shape.

The type, and its associated metadata views and functions, are provided by the MDSYS schema which is included with your database installation.  Although the SDO_GEOMETRY type is associated with the MDSYS schema it can be used by any database user to create geometry columns in any schema.  The only requirement is that there must be a spatial index on the table that contains the geometry type and there must be an entry in the USER_SDO_GEOM_METADATA view that tells the database that a particular table and column contain geometric data in order for functions and rendering operations to work correctly.

The geometry type is analogous to an XML type in that it is stored as a column in a table but also has its own internal structure that can be addressed by various database functions provided specifically for it.

X/Y Grid

Using an example from the documentation, if we look at the polygon named cola_b rendered on an xy grid we can see that its definition should include the points (5,1, 8,1, 8,6, 5,7, 5,1).

If we look at the COLA_MARKETS table in the database and look at the row that contains cola_b we can see that  the SHAPE column (which is an SDO_GEOMETRY type) does indeed contain those points.  Checking to see we have an entry in the USER_SDO_GEOM_METADATA view and that we have a spatial index on the table we can create a map view of our COLA_MARKETS table right inside of SQL Developer.

COLA_MARKETS table in the database

Map based objects such as a US State are merely a more complex example of a polygon that have a reference to a projection system (SRID) for rendering the earth’s 3 dimensional surface on a 2 dimensional plane.  If we look at a stored geometry for Colorado we can see that the only difference between it and the simple polygon we created for cola_b above is a reference to an SRID code and the number of points (longitude and latitude in this case) needed to render it.

That covers the basics of what you need to know about geometry and the database for now.  The SDO_GEOMETRY column type is used to store geometry definitions in the database and the USER_SDO_GEOM_METADATA view gives the database the information it needs to create spatial indexes.

I’ll show you in another post that lines, points, circles, etc. can also be associated with and rendered on maps and it’s pretty easy to create and use these objects to add even more insight to your map visualizations.  Additionally, we'll talk about several important and useful database procedures & functions that are provided for validating (and fixing if needed) your geometric data which is especially helpful when we import freely available shapefiles into our database to create our own maps for use inside of OBIEE.

Part 1 - Map Basics

This is the first in a series of posts on Maps in OBIEE.  Part 2 - Database is here.

Many people mistakenly believe that implementing map views in Oracle Business Intelligence (OBI) is difficult, requires additional licensing on the Oracle database and requires them to pay for content from third party map providers. Third party provided map data and Oracle Spatial and Graph (an additional option for the Enterprise Edition of the database) may offer significant value to your organization but neither of these options are necessary to create and use maps with OBI. Country, state or province, county, city and even neighborhood level map data is freely available and easily found in a number of places.

Oracle Locator is included with all versions of the Oracle Database. Oracle Map Viewer is included with OBI and the Map Builder and Map Editor tools included with Map Viewer make it possible to import and customize maps.  Maps views are produced through an integration between the Oracle Database, Map Viewer and OBI but the hard part of the integration is already done so all that is really required to utilize the power of maps is an understanding of the components involved in the integration and how they interact with each other.

As with most things Oracle, what at first seems complicated is actually pretty simple once an understanding is developed. Maps are like this. At the heart of map views measures such as dollar or quantity are dynamically associated with geometric shapes and rendered using colors, styles and other visualizations in such a way as to enhance the meaning of the underlying data. That’s a complicated task. Thankfully all that is needed to make OBI maps work is a desired geometric shape (US States as an example) and the attribute of that shape (State name for example) that will be associated with the data from OBI. Everything else involved is just giving the various components involved what they need to do their part of the task.

Let’s take a look at a figure from the MapViewer documentation that helps to describe the architecture involved in producing maps and then discuss the important things to understand at each layer. OBI is the “Client” application in our scenario so it passes map rendering requests to MapViewer which in turn interacts with the database to get the map definition information it needs to render a map and pass it back to OBI.

MapViewer (the “Middle Tier”) needs to know what map definition to use and how that definition is linked to OBI data. This linkage is defined in the Map Administration page of OBI and ties a field (or fields) from a subject area to a map layer. 

The layers are associated with a Background Map and those are the objects seen when constructing Map Views.

Map Builder is used to create the layers that the user interacts with in OBIEE and set the fields that will be used to join OBIEE data to the geometry objects that are stored in an Oracle Database.

MapViewer is a J2EE application that comes pre-deployed with the included WebLogic instance that OBI is deployed on.  Although it is co-located with OBI it can also be deployed on a standalone server running WebLogic, Glassfish or Tomcat.  MapViewer maintains the connections to the database(s) containing geometric data and handles the actual work of rendering map views.

Oracle Database stores the geometric definition of shapes and what colors, line types and text styles to use when rendering them. Additionally, various functions can be performed on geometric data such as distance calculations or merging several states into a territory.

So that lays out a basic understanding of the moving parts involved with creating Map views inside of OBI Answers.  OBI is a client application asking Oracle MapViewer to render an interactive map for it based on some business data. Oracle MapViewer fetches the map definition and styles from the Oracle Database and uses that information to render a map that it passes back to OBI.

SampleApp v607 Webinar Replay

In case you missed Dan’s demonstration of Oracle Business Intelligence using the latest release V607 from the Oracle BI SampleApp team, the replay is available now. Featuring hundreds of dashboards, analyses, and scripts, SampleApp is known in the Oracle BI consulting community as THE go to platform for showcasing the full capabilities of Oracle BI. Release V607 of SampleApp runs on version (first 12c patch release) of Oracle Business Intelligence Enterprise Edition.

Dan also presented additional free visual analytic demo tools for BI and analytic professionals, including the new BI Public Demo Store. This platform is geared towards Oracle BI (and Data Visualization Desktop) users, allowing them to learn new techniques, and even to download additional visualizations into Oracle's Data Visualization Desktop tool.

All content shown is available for free from Oracle and Vlamis.


12c OBIEE is coming soon

My name is Arthur Dayton and I joined Vlamis Software Solutions in December this past year.  I just returned from the BIWA summit at Oracle’s headquarters in Redwood Shores, CA where I had the opportunity to hear directly from the Business Intelligence product development teams about their strategic objectives and what they will be working on in the next 18-24 months to achieve those objectives.

Having been a customer, and now working with customers, of Oracle it can feel at times like they aren’t hearing what you have to say about how the products can be improved to better satisfy customer demands and compete against other product sets that appear to be leapfrogging them with key functionality.  I’m happy to say that my perception of Oracle in this area and the reality are actually very different and that the development teams have been listening and are working very hard to improve on the end user experience inside of OBI and some of the pain and suffering involved with administering the development lifecycle across environments.

Visual Analyzer (VA) and Business Intelligence Cloud Services (BICS) were topics of emphasis as well as Oracle Transactional Business Intelligence (OTBI).  VA is a particularly exciting innovation coming in the 12c version of OBI and addresses many of the difficulties associated with end users ease of use, ease of creating visualizations and most excitingly for end users the ability to quickly and easily combine external data sources with existing OBI data!  This addresses a fundamental truth that a purely centralized data architecture, that often applies the 80/20 rule to ETL development, has ignored and, in my opinion, is a driving force behind slow adoption rates.  The heavy lifting that IT has done to give the end users 80% of what they need is only valuable in the context of the 100%.  So if we can’t enable the users to finish the work that IT will likely never get to, or can’t respond to in a timely manner, then OBIEE is reduced to just another way to barf data into Excel forcing the end users to do the rest of what they need offline.  This takes  all of the amazing capabilities of the BI Server out of the picture and tanks the ROI of the system because there is no way to bring the “finished” data back online without another long IT project that will begin promptly in the next 8-12 months.  The historical justification for this approach has been that we can’t possibly maintain a system that we let the end users add data to so hence we end up maintaining a system that the end users won’t use.

The end users have spoken.  What they do want from IT is vetted, reliable and easy to access enterprise information with the capability to deliver that data across distance, device and platform.  What they don’t want is a loss of agility and the ability to be responsive and innovative in order to get it.  In essence they require a “data development kit” that gives them a flexible means to satisfy their information needs using a common standard.  In the same way we in the development community have become accustomed to utilizing open source libraries that can extended to accommodate our particular needs the data consumption community has demanded the ability to do the same.

With 12c, end users of OBIEE will find it much easier to take advantage of the huge efforts that have been put into creating a curated source of the truth, and all of the wonderful visualization and enterprise delivery capabilities of OBI, without having to sacrifice the ability to add data that didn’t make the cut for the data warehouse team.  I know this will come with its own challenges and to some it may appear as a return to the old decentralized approach to BI that leads to maintenance nightmares and a divergence from organizational coherence.  But we must find a way for users of BI systems to have the best of both worlds that takes advantage of highly curated data in combination with user generated data sources.  Necessity is the mother of invention after all and if we are to survive as BI professionals we need to adapt to the reality of the market place.  Our end users live in a brave new world where the ability to capitalize on information is increasingly the differentiator between success and obsolescence.  Data development cycles that require months or years to apply simply will not be acceptable in this new world and I believe that Oracle has gotten that message and will deliver the tools to its customers to accommodate that reality. 

Register now for the Upcoming KCOUG Meeting

Hello to all of our Kansas City Area readers!

Now is the time to register for the next Kansas City Oracle User Group Meeting on Tuesday, August 26, 2014, 6:00pm - 7:30pm.

Location: Burns & McDonnell Auditorium
9300 Ward Parkway
Kansas City, MO

This meeting will be focused on the user group by having round table discussions on the following topics:

  • Best practices for development discussion with hands on demos
  • OBIEE best practices and demos
  • Performance Tuning: tools available and demos

KCOUG meetings are absolutely free and are a great way to network with other Oracle users in the Kansas City area. Come and learn about others' experiences with Oracle!