Last week we had an Oracle Spatial Workshop at Scamander. The goal of the evening was to highlight a fundamental different technique in the Oracle database.
Instead of the usual WHERE-clause and JOIN syntaxis the database provides a set of similar tools but based on the spacial technique.
For the attendants it proved to be very descriptive to be able to execute an insert-statement and to display the result with the push of a button.
The relative simple techniques addressed was enough to show that this is a very different technique and requires a different point of view on data. Just as the “regular” SQL-toolset in the Oracle database, this is a complex and broad topic.
We used Oracle Spatial tools. Mapviewer serves for the display of maps with navigation and other functionality, but we need something to compile the maps, layers, styles, etc. For that purpose Oracle provides for a tool called Mapbuilder (source: Oracle Wiki)
To give an example:
** INSERT Vierkant (square)
** INSERT Circel (circle)
** INSERT the Union of the two inserted objects (square, circle)
** CREATE a buffer around the UNION:
** SELECT addresses within the square:
It was nice to have a visual representation of the subject; Oracle Spatial. We are certainly no experts at this time like Maarten Jan, but we have a good impression of what the capabilities of Oracle Spatial are.
Next time we will make a link between Oracle Spatial and Oracle BI EE. Hopefully we can use Oracle BI 11g for that.
- Spatial operators
- Spatial aggregate functions
- Spatial Data Types and Metadata
- SDO_GEOM Package (Geometry)
Last week I have been blogging about Multiple Fact Reporting on (Non-)Conforming dimensions. Thanks to Nicolae I was triggered to do some further investigation on this topic. He had a question; What happens when you want to filter on a non-conforming dimension?
When you filter an a non-conforming dimension, you could get a null value for the fact which has all the dimensions as conforming.
Is this not exactly how the Oracle BI-server works? We have created one logical fact table and two different logical table sources (LTS). The Oracle BI-server creates seperate queries for each LTS in a Oracle BI Answers query.
Query I connects fact table 1 to all the dimensions, including a filter on the non-conforming dimension.
Query II connects fact table 2 one to the conforming dimensions only. Because there is no physical relationship between fact table 2 and the non-conforming dimension, it is not possible to filter query II on dtnc1.value3 = ‘3CCC3’ as well.
The results of both queries:
Lucky enough for me Nicolas did some investigating himself to:
- Oracle BI EE Forum
- Siebel IT Toolbox
It looks like the most easy solution is to filter on the fact table which has all the dimensions as conforming. In this case, that would be; Fct1 Value1.
I will try to ask some other sources what their solution is to this ‘problem’.
More to come.
There is a lot of rumour on Twitter (hashtags #OBIEE, #11g ) about the launch of the new 11g version of Oracle BI EE yesterday. There will be an official launch event in London next month. As Mark Rittman already mentioned, this will probably be a marketing launch. The software on OTN will follow later.
Oracle is testing our patience. The expectations of this new product are very high.
I can’t wait!!
Update 31 August 2017
Although published about 7 years ago, this subject still seems a source of questions. To understand the concepts of Oracle BI (OBIEE) better, it is good to check out the following blogposts as well. They will link to other blogs “Don’t forget the Logical Layer” & “Going to the core of Oracle Analytics” and will help you better understand the basis of OBIEE. This basis is an absolute must to get the most out of OBIEE.
There are some questions, which are popping up at the Oracle BI EE Forums regularly. One of those questions is;
*** How to model multiple facts against (non-) conforming dimensions?
I will try to work things out. Click on the images to see more detail.
Note: I am aware of the extra white space between the images. That’s not intended functionality, but lack of knowledge of WordPress.
Picture the following:
There are two fact tables and three dimension tables. FACT_TABLE_1 has two conformed dimension tables; DIM_TABLE_CONF_1 and DIM_TABLE_CONF_2 and one non-conformed dimension table DIM_TABLE_NON_CONF_1.
FACT_TABLE_2 has two conformed dimension tables; DIM_TABLE_CONF_1 and DIM_TABLE_CONF_2.
The Physical Model would have the following structure:
Based on the Physical Model we could construct the following Logical Model:
I have created one fact table which contains Logical Table Sources (LTS) for FACT_TABLE_1 and FACT_TABLE_2
As you can see I have created Dimensions (Hierarchy’s) for each Dimension Table.
FACT_TABLE_2 has no physical relationship with DIM_TABLE_NON_CONF_1. Therefore you should set the logical levels for FACT_TABLE_2 to the ‘Grand Total’-level of DIM_TABLE_NON_CONF_1. This way the Oracle BI Server won’t look for a join between DIM_TABLE_NON_CONF_1 and FACT_TABLE_2.
If you want to avoid nulls, set the detail levels for the facts. Set the ‘Grand Total’-levels for the metrics as well.
If we take a look at Oracle BI Answers, we can create a report which contains data from the following tables;
Now we can bring data from DIM_TABLE_NON_CONF_1 into this report. It is impossible to devide data from FACT_TABLE_2 over this dimension. Therefore the data will be the same for every value of this dimension.
It’s possible to report on facts and dimensions which not have a physical relationship to each other. Just make sure you create dimensions (hierarchy’s) for every dimension table. Next to that you should set the logical levels for your logical tables.
Source: Oracle MOS HTML Knowledge Management