Oracle Spatial Workshop – Impression

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.

Useful Links

Oracle MOS HTML Update 07062010

 Document ID   Title   Doc Type   Modified Date 
950086.1 Error “You are not authorized to view this page” when opening up OBIEE Anaswers or dashboard PROBLEM 28-MAY-10
1108438.1 Do We Need To Install The Fusion MiddleWare 11g Repository Creation Utility (RCU) With OBIEE 10g (10.1.3.4.1) And Weblogic Server? HOWTO 28-MAY-10
790831.1 unable to run ibots as repository user PROBLEM 27-MAY-10
751922.1 About deleting the old alerts from the User’s Dashboard HOWTO 27-MAY-10
1108515.1 Oracle BI Scheduler Error nQSError 68019 Authentication Failed When Trying To Save An iBot After Setting Up The Scheduler PROBLEM 24-MAY-10
1111583.1 Admin User Not Able To See Some Of The Ibot Job PROBLEM 27-MAY-10
796842.1 [nQSError: 75005] Failed to Send AUTH Command. 5.7.3 Authentication Unsuccessful Running iBots and no Email Message is Sent by Them PROBLEM 28-MAY-10
1110617.1 Job Manager Launch Fails PROBLEM 26-MAY-10
763793.1 [nQSError: 59001] TimestampAdd operation is not permitted on DOUBLE PRECISION operand(s) PROBLEM 27-MAY-10
758343.1 OLTP Activity created date is incorrectly displayed in Analytics. HOWTO 27-MAY-10
780242.1 Unable to constrain prompt when ‘Show SQL Result’s option is selected HOWTO 27-MAY-10
1109385.1 Current_fcst_date Variable Erroneously Set To YYYY-MM-DD HH:MI:SS.000. The .000 is causing EUE9D5XW error in prompts PROBLEM 25-MAY-10
781288.1 Facing issues with adding a OR condition to a filter in a query PROBLEM 27-MAY-10
817183.1 Passing the selected Prompt values from one dashboard to other dashboard HOWTO 27-MAY-10
1110355.1 How To Resolve The Error – OPR4ONWY U9IM8TAC OI2DL65P Subquery Contains Too Many Values For The IN Predicate HY000 When Using A Subquery. HOWTO 26-MAY-10
1091476.1 How To Globaly Set Formatting Options In OBIEE HOWTO 25-MAY-10
740919.1 Analytics – Issues with save selection functionality in dashboard PROBLEM 24-MAY-10
729852.1 Sum Total greater then actual number – Analytics PROBLEM 24-MAY-10
1108194.1 Unable To Format Labels Of A Total Rows Section Of A Pivot Table. PROBLEM 24-MAY-10
799173.1 Apply a numeric value from prompt to be used in a calculated column in a request HOWTO 27-MAY-10
816973.1 BI Office Plug-in converting text values to date PROBLEM 27-MAY-10
759131.1 Multi-Sort on table headers with Ctrl-click does not work for Mac Firefox PROBLEM 24-MAY-10
741897.1 Answers AVG Function not displaying result with decimals PROBLEM 24-MAY-10
742235.1 USER GETS AN ACCESS DENIED ERROR MESSAGE WHEN TRIES TO SWITCH DEFAULT DASHBOARD PROBLEM 24-MAY-10
1108594.1 OBIEE Dashboard Date Prompts & Date Presentation Variables Errors after Upgrade to Version 10.1.3.4.1 HOWTO 26-MAY-10
744300.1 Blank page appears when user navigates from one dashboard page to another PROBLEM 24-MAY-10
739830.1 Missing company record on the dashboard PROBLEM 24-MAY-10
751935.1 How to reset the default setting of the multi-select prompt HOWTO 27-MAY-10
815755.1 Query failing for user having ‘(apostrophe) in their name PROBLEM 27-MAY-10
1091253.1 Date Prompt Calendar Search Incorrectly Returns Format YYYY-MM-DD HH:MI:SS And Error – A Date Was Expected QABPH2PO PROBLEM 26-MAY-10
729853.1 How to use UI colors of Siebel Analytcis 7.8 after BI 10g upgrade PROBLEM 24-MAY-10
956342.1 [NQSERROR: 46029] FAILED TO LOAD THE DLL LIBNQSDBGATEWAYOCI10G.SO RUNNING REQUEST PROBLEM 28-MAY-10
1101553.1 IE6 Client Intermittently Hangs And/Or Crashes With OBI Dashboards And Answers After Adobe Flash 10 Upgrade PROBLEM 28-MAY-10
1108451.1 ALERT: Technical Notes for Patch 9492821 (QF 475) – Modifying Reports to conform to DATE & DATETIME format Enhancements in 10.1.3.4.1 and higher versions BULLETIN 26-MAY-10
1102353.1 How to Enable Change Password link in My Account in Settings HOWTO 28-MAY-10
750541.1 Data Type issue in Direct Database Request using a SUM function in the SQL query PROBLEM 27-MAY-10
751094.1 Multi-Select Fields in Dashboard Prompt does not work with NULL values in the selection PROBLEM 27-MAY-10
751946.1 ‘CASE WHEN’ columns do not display data in pivot table view PROBLEM 27-MAY-10
803874.1 Issues with Filter in a??Sales – Orders and Invoicesa?? for Promised Month PROBLEM 27-MAY-10
1111386.1 Ora-00928: Missing Select Keyword At Oci Call Ocistmtexecute [Nqserror: 17010] Running Request PROBLEM 27-MAY-10
528540.1 nQSError: 17011 PROBLEM 25-MAY-10
1102315.1 Overriding Default Aggregation Of Sum to Avg Does Not Work with OBIEE Answers Report HOWTO 28-MAY-10
1111405.1 Migrating OBIEE Repository across environments BULLETIN 27-MAY-10
747939.1 Not possible to open PDF Attachments when file name is long and contain special characters PROBLEM 24-MAY-10
785318.1 BI Office executing a request even before prompt is applied HOWTO 24-MAY-10
1112495.1 Physical Schema Xxx Is Not Referenced In Any Project. PROBLEM 28-MAY-10
1110707.1 OBIEE RPD’s consistency check hangs with multiple levels of hierarchy HOWTO 26-MAY-10
740367.1 How to create cumulative line chart? HOWTO 24-MAY-10
1102665.1 Po Quantity Is Different In Cycle Lines Subject Area And Po Subject Area PROBLEM 28-MAY-10
948283.1 OBI server cache cannot be shared among users with individual DB connections HOWTO 27-MAY-10
787366.1 Why are temp tables being created by the analytics/OBI system? BULLETIN 24-MAY-10
796166.1 How to Force Excel Add-in to Ask for Userid/Password to Analytics Everytime a Refresh is Run? HOWTO 28-MAY-10
1097725.1 Direct Database request in Anwers replaces image URL with text string HOWTO 27-MAY-10
1110354.1 Security Validation exception error when we access Bi Publisher From Obiee PROBLEM 26-MAY-10
1107170.1 Obiee Connectivity With Sql Server 2008, [nQSError: 16023] The ODBC function has retuned an error. PROBLEM 24-MAY-10
1110554.1 Alias Tables Are Joined In Queries Unnecessarily PROBLEM 28-MAY-10
751139.1 Requesting a new version of SQLServer_nQ_Clock.sql for Usage Tracking HOWTO 27-MAY-10
1110374.1 Cast As Date Returning Datetime PROBLEM 26-MAY-10
1112396.1 How to Pass Parameter To a Stored Procedure In Siebel Analytics 7.8 HOWTO 28-MAY-10
759098.1 ABILITY TO PASS PRESENTATION VARIABLES INTO CHART TITLES HOWTO 27-MAY-10
743691.1 MERGE BUTTON IS GREYED OUT IN A MULTI USER DEVELOPMENT ENVIRONMENT PROBLEM 24-MAY-10
1112444.1 Create Target List functionality fails when Japanese characters are used in the request filter PROBLEM 28-MAY-10
1104817.1 Nqserror 14026 Unable To Navigate Requested Expression Error With Out Of The Box Spend And AP Invoice RPD PROBLEM 24-MAY-10
729856.1 About error: Item already exists in catalog HOWTO 24-MAY-10
1058283.1 Navigating From OBIEE-More products > BI Publisher Get Error” Reporting Login: java.net.ConnectException: Connection refused” PROBLEM 24-MAY-10

Multiple Fact Reporting on (Non-)Conforming dimensions – Part II

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:

Query I connects fact table 1 to all the dimensions, including a filter on the non-conforming dimension.

Query II:

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:

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.

Oracle BI EE 11g – Launch

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!!

Multiple Fact Reporting on (Non-)Conforming dimensions

****

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:

Physical Diagram

Based on the Physical Model we could construct the following Logical Model:

Logical Diagram

I have created one fact table which contains Logical Table Sources (LTS) for FACT_TABLE_1 and FACT_TABLE_2

Logical Model

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.

Logical Table Source – Fact I
Logical Table Source – Fact II
Logical Column – Fact II

If we take a look at Oracle BI Answers, we can create a report which contains data from the following tables;

  • DIM_TABLE_CONF_1
  • DIM_TABLE_CONF_2
  • FACT_TABLE_1
  • FACT_TABLE_2
Oracle BI Answers – Conformed Dimension

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.

Oracle BI Answers – (Non-) Conformed Dimension

*** Summary:

 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.

Oracle MOS HTML Update 30052010

 Document ID   Title   Doc Type   Modified Date 
950086.1 Error “You are not authorized to view this page” when opening up OBIEE Anaswers or dashboard PROBLEM 28-MAY-10
1108438.1 Do We Need To Install The Fusion MiddleWare 11g Repository Creation Utility (RCU) With OBIEE 10g (10.1.3.4.1) And Weblogic Server? HOWTO 28-MAY-10
790831.1 unable to run ibots as repository user PROBLEM 27-MAY-10
751922.1 About deleting the old alerts from the User’s Dashboard HOWTO 27-MAY-10
1108515.1 Oracle BI Scheduler Error nQSError 68019 Authentication Failed When Trying To Save An iBot After Setting Up The Scheduler PROBLEM 24-MAY-10
1111583.1 Admin User Not Able To See Some Of The Ibot Job PROBLEM 27-MAY-10
796842.1 [nQSError: 75005] Failed to Send AUTH Command. 5.7.3 Authentication Unsuccessful Running iBots and no Email Message is Sent by Them PROBLEM 28-MAY-10
1110617.1 Job Manager Launch Fails PROBLEM 26-MAY-10
763793.1 [nQSError: 59001] TimestampAdd operation is not permitted on DOUBLE PRECISION operand(s) PROBLEM 27-MAY-10
758343.1 OLTP Activity created date is incorrectly displayed in Analytics. HOWTO 27-MAY-10
780242.1 Unable to constrain prompt when ‘Show SQL Result’s option is selected HOWTO 27-MAY-10
1109385.1 Current_fcst_date Variable Erroneously Set To YYYY-MM-DD HH:MI:SS.000. The .000 is causing EUE9D5XW error in prompts PROBLEM 25-MAY-10
781288.1 Facing issues with adding a OR condition to a filter in a query PROBLEM 27-MAY-10
817183.1 Passing the selected Prompt values from one dashboard to other dashboard HOWTO 27-MAY-10
1110355.1 How To Resolve The Error – OPR4ONWY U9IM8TAC OI2DL65P Subquery Contains Too Many Values For The IN Predicate HY000 When Using A Subquery. HOWTO 26-MAY-10
1091476.1 How To Globaly Set Formatting Options In OBIEE HOWTO 25-MAY-10
740919.1 Analytics – Issues with save selection functionality in dashboard PROBLEM 24-MAY-10
729852.1 Sum Total greater then actual number – Analytics PROBLEM 24-MAY-10
1108194.1 Unable To Format Labels Of A Total Rows Section Of A Pivot Table. PROBLEM 24-MAY-10
799173.1 Apply a numeric value from prompt to be used in a calculated column in a request HOWTO 27-MAY-10
816973.1 BI Office Plug-in converting text values to date PROBLEM 27-MAY-10
759131.1 Multi-Sort on table headers with Ctrl-click does not work for Mac Firefox PROBLEM 24-MAY-10
741897.1 Answers AVG Function not displaying result with decimals PROBLEM 24-MAY-10
742235.1 USER GETS AN ACCESS DENIED ERROR MESSAGE WHEN TRIES TO SWITCH DEFAULT DASHBOARD PROBLEM 24-MAY-10
1108594.1 OBIEE Dashboard Date Prompts & Date Presentation Variables Errors after Upgrade to Version 10.1.3.4.1 HOWTO 26-MAY-10
744300.1 Blank page appears when user navigates from one dashboard page to another PROBLEM 24-MAY-10
739830.1 Missing company record on the dashboard PROBLEM 24-MAY-10
751935.1 How to reset the default setting of the multi-select prompt HOWTO 27-MAY-10
815755.1 Query failing for user having ‘(apostrophe) in their name PROBLEM 27-MAY-10
1091253.1 Date Prompt Calendar Search Incorrectly Returns Format YYYY-MM-DD HH:MI:SS And Error – A Date Was Expected QABPH2PO PROBLEM 26-MAY-10
729853.1 How to use UI colors of Siebel Analytcis 7.8 after BI 10g upgrade PROBLEM 24-MAY-10
956342.1 [NQSERROR: 46029] FAILED TO LOAD THE DLL LIBNQSDBGATEWAYOCI10G.SO RUNNING REQUEST PROBLEM 28-MAY-10
1101553.1 IE6 Client Intermittently Hangs And/Or Crashes With OBI Dashboards And Answers After Adobe Flash 10 Upgrade PROBLEM 28-MAY-10
1108451.1 ALERT: Technical Notes for Patch 9492821 (QF 475) – Modifying Reports to conform to DATE & DATETIME format Enhancements in 10.1.3.4.1 and higher versions BULLETIN 26-MAY-10
1102353.1 How to Enable Change Password link in My Account in Settings HOWTO 28-MAY-10
750541.1 Data Type issue in Direct Database Request using a SUM function in the SQL query PROBLEM 27-MAY-10
751094.1 Multi-Select Fields in Dashboard Prompt does not work with NULL values in the selection PROBLEM 27-MAY-10
751946.1 ‘CASE WHEN’ columns do not display data in pivot table view PROBLEM 27-MAY-10
803874.1 Issues with Filter in a??Sales – Orders and Invoicesa?? for Promised Month PROBLEM 27-MAY-10
1111386.1 Ora-00928: Missing Select Keyword At Oci Call Ocistmtexecute [Nqserror: 17010] Running Request PROBLEM 27-MAY-10
528540.1 nQSError: 17011 PROBLEM 25-MAY-10
1102315.1 Overriding Default Aggregation Of Sum to Avg Does Not Work with OBIEE Answers Report HOWTO 28-MAY-10
1111405.1 Migrating OBIEE Repository across environments BULLETIN 27-MAY-10
747939.1 Not possible to open PDF Attachments when file name is long and contain special characters PROBLEM 24-MAY-10
785318.1 BI Office executing a request even before prompt is applied HOWTO 24-MAY-10
1112495.1 Physical Schema Xxx Is Not Referenced In Any Project. PROBLEM 28-MAY-10
1110707.1 OBIEE RPD’s consistency check hangs with multiple levels of hierarchy HOWTO 26-MAY-10
740367.1 How to create cumulative line chart? HOWTO 24-MAY-10
1102665.1 Po Quantity Is Different In Cycle Lines Subject Area And Po Subject Area PROBLEM 28-MAY-10
948283.1 OBI server cache cannot be shared among users with individual DB connections HOWTO 27-MAY-10
787366.1 Why are temp tables being created by the analytics/OBI system? BULLETIN 24-MAY-10
796166.1 How to Force Excel Add-in to Ask for Userid/Password to Analytics Everytime a Refresh is Run? HOWTO 28-MAY-10
1097725.1 Direct Database request in Anwers replaces image URL with text string HOWTO 27-MAY-10
1110354.1 Security Validation exception error when we access Bi Publisher From Obiee PROBLEM 26-MAY-10
1107170.1 Obiee Connectivity With Sql Server 2008, [nQSError: 16023] The ODBC function has retuned an error. PROBLEM 24-MAY-10
1110554.1 Alias Tables Are Joined In Queries Unnecessarily PROBLEM 28-MAY-10
751139.1 Requesting a new version of SQLServer_nQ_Clock.sql for Usage Tracking HOWTO 27-MAY-10
1110374.1 Cast As Date Returning Datetime PROBLEM 26-MAY-10
1112396.1 How to Pass Parameter To a Stored Procedure In Siebel Analytics 7.8 HOWTO 28-MAY-10
759098.1 ABILITY TO PASS PRESENTATION VARIABLES INTO CHART TITLES HOWTO 27-MAY-10
743691.1 MERGE BUTTON IS GREYED OUT IN A MULTI USER DEVELOPMENT ENVIRONMENT PROBLEM 24-MAY-10
1112444.1 Create Target List functionality fails when Japanese characters are used in the request filter PROBLEM 28-MAY-10
1104817.1 Nqserror 14026 Unable To Navigate Requested Expression Error With Out Of The Box Spend And AP Invoice RPD PROBLEM 24-MAY-10
729856.1 About error: Item already exists in catalog HOWTO 24-MAY-10
1058283.1 Navigating From OBIEE-More products > BI Publisher Get Error” Reporting Login: java.net.ConnectException: Connection refused” PROBLEM 24-MAY-10

Source: Oracle MOS HTML Knowledge Management

MOS HTML updates

Thanks to Timur Akhmadeev’s blog I found the solution for the broken links in the MOS HTML updates mail from Oracle Support.

Links in an email are like this:

https://supporthtml.oracle.com/ep/faces/secure/kmDocumentDisplay.jspx?id=1062675.1

but should be like this:

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1062675.1

– note the extra slash between km and DocumentDisplay.

For more details, check Timur’s blog.