{"id":1182,"date":"2016-03-15T20:03:59","date_gmt":"2016-03-15T20:03:59","guid":{"rendered":"http:\/\/62.131.51.129\/?p=1182"},"modified":"2016-03-15T20:03:59","modified_gmt":"2016-03-15T20:03:59","slug":"everything-is-sql-the-story-of-obiee","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1182","title":{"rendered":"Everything is SQL &#8211; the story of OBIEE"},"content":{"rendered":"<p>As one can imagine, SQL is the main engine to start loading a report in OBIEE. In principle, one has two kinds of queries. One query runs against the Oracle DBMS. This is the physical query that we know from any Oracle client. It can be found on the main main. Choose New > Analysis > Create Direct Database query. See image:<br \/>\n<a href=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2016\/03\/Untitled.png\" rel=\"attachment wp-att-1186\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2016\/03\/Untitled.png\" alt=\"Untitled\" width=\"353\" height=\"512\" class=\"alignnone size-full wp-image-1186\" \/><\/a><br \/>\nIf one enters the page where such query may be formulated, one sees a rather Spartan looking page where the query may entered. It is very rudimentary, but, heck, what do you want more. Look at my interface page:<br \/>\n<a href=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2016\/03\/Untitled-1.png\" rel=\"attachment wp-att-1188\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2016\/03\/Untitled-1.png\" alt=\"Untitled\" width=\"639\" height=\"523\" class=\"alignnone size-full wp-image-1188\" \/><\/a><br \/>\nThe name of the so-called connection pool can be found in the Oracle BI Administration Tool. The actual results from the query can be found in the &#8220;results&#8221; tab. <\/p>\n<p>Another type of SQL is the logical SQL. This can be found in Administration > Manage Sessions. To avoid, results being returned from cache, one may think of starting the Call SAPurgeAllCache() command. This can be run from Administration > Issue SQL.<br \/>\nUnder Administration > Manage Session, one may find the so-called logical SQL. This may look like:<\/p>\n<pre>\nSET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='\/users\/prodney\/TomFinaal6',PREFERRED_CURRENCY='USD';SELECT\n   0 s_0,\n   \"1_TOM\".\"WINKEL1\".\"COUNTRYID\" s_1,\n   \"1_TOM\".\"WINKEL1\".\"FILNR\" s_2,\n   \"1_TOM\".\"WINKEL1\".\"ID\" s_3,\n   \"1_TOM\".\"WINKEL1\".\"OPGEBNR\" s_4,\n   \"1_TOM\".\"WINKEL1\".\"PLAATS\" s_5,\n   \"1_TOM\".\"WINKEL1\".\"REGIO\" s_6,\n   \"1_TOM\".\"WINKEL1\".\"REGIONR\" s_7,\n   \"1_TOM\".\"WINKEL1\".\"SOORT\" s_8,\n   \"1_TOM\".\"WINKEL1\".\"WINKELGROEP\" s_9,\n   \"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\" s_10,\n   AGGREGATE(NTILE(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\",4) BY \"1_TOM\".\"WINKEL1\".\"COUNTRYID\") s_11,\n   AGGREGATE(NTILE(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\",4) BY \"1_TOM\".\"WINKEL1\".\"OPGEBNR\") s_12,\n   AGGREGATE(NTILE(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\",4) BY \"1_TOM\".\"WINKEL1\".\"REGIONR\") s_13,\n   CAST(NULL AS INTEGER) s_14,\n   REPORT_SUM(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\" BY \"1_TOM\".\"WINKEL1\".\"COUNTRYID\") s_15,\n   REPORT_SUM(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\" BY \"1_TOM\".\"WINKEL1\".\"ID\") s_16,\n   REPORT_SUM(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\" BY \"1_TOM\".\"WINKEL1\".\"OPGEBNR\") s_17,\n   REPORT_SUM(\"1_TOM\".\"WINKELWEEKOMZET\".\"OMZETAH\" BY \"1_TOM\".\"WINKEL1\".\"REGIONR\") s_18\nFROM \"1_TOM\"\nWHERE\n((\"JAAR\".\"JAAR\" = 2015) AND (\"WINKELWEEKOMZET\".\"OMZETAH\" > 0))\nORDER BY 1, 2 ASC NULLS LAST\nFETCH FIRST 5000001 ROWS ONLY\\\n<\/pre>\n<p>This can be run from Administration > Issue SQL.<\/p>\n<p>In the log, one sees the logical SQL, followed by a translation into a physical SQL.<\/p>\n<p>This Physical SQL needs a little rewriting and it can be fired directly from any SQL client.<\/p>\n<p>This translation is very useful as it may help to identify the fields that are used to create a report from.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As one can imagine, SQL is the main engine to start loading a report in OBIEE. In principle, one has two kinds of queries. One query runs against the Oracle DBMS. This is the physical query that we know from any Oracle client. It can be found on the main main. Choose New > Analysis [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1184,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-1182","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-warehousing"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1182","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1182"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1182\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1182"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}