ADF View Objects as a Repository Data Source using OBIEE 11.1.1.6 and JDeveloper 11.1.1.6
The Application Development Framework (ADF) within Oracle Fusion Middleware 11g provides a data abstraction technology called ADF Business Components, that provide a Java object later over database SQL queries. Since the 11g release of OBIEE the Oracle BI Repository can access ADF Business Components through what are called "view objects", giving you the ability to create repository subject areas using the same java data abstraction objects that your Fusion Middleware applications use (which is how Oracle have put together the new Oracle Transactional BI element of the BI Apps 11g), as shown in the diagram below.
In the following examples I'll be using Oracle JDeveloper 11g along with OBIEE 11.1.1.6, both on Windows, to connect OBIEE's BI Repository to an ADF application in order to use it as a data source. Let's start by opening up JDeveloper 11g and creating a new Fusion Web Application (ADF), so that we get a Model project for developing the ADF Business Components, and a ViewController project for providing some web pages for testing. Stepping through the various wizard pages and accepting the default choices, I then end up with a basic ADF Fusion Web project, looking like this:
In this first example we'll create a couple of ADF business components that provide firstly, a view over the SH.PRODUCTS table, and then another view that combines this table with the SH.SALES and SH.CUSTOMERS tables to create a set of data suitable for pivot tables or graphs. Let's start though by navigating to the Application Resources pane and adding a new JDBC connection through to the SH schema in an Oracle database.
Now we can create our first ADF Business Component, to expose the SH.PRODUCTS table using a ADF view object. To do so, I right click on the Model project within the application and then select ADF Business Component > Entity Object, then step through the wizard steps selecting the database table to base the object on, and then using the options at the end to create view objects for these entity components, with attributes for each of the columns in the source table.
I then repeat this for the SH.CUSTOMERS, SH.COUNTRIES and SH.SALES tables, creating ADF business component entity and view objects for each one, and then create one more view object that joins the customer and countries view objects into a single, combined view object. Once this is done, my ADF project has a set of business components listed in the Projects panel, the connection through to the database in the Application Resources panel, and if I double-click on the AppModule application module that provides access to all of these business components, you can see the joins that are automatically put in place to link customers to sales, products to sales, countries to customers and so on.
The AppModule application module object is the data model through which OBIEE will access the ADF view objects, and if you look carefully at the screenshot, you might notice that all of the joins (or "view links") created automatically by JDeveloper point the opposite way to what we'd need for OBIEE - nothing actually joins from the sales table through to dimension tables; instead, the joins go the other way. Now as we'll see later on, joins set up in this way will cause us a problem when we import this data source into the BI Repository, so as an extra step for this phase I'm going to define a bunch of additional view link that join the SalesView view object through to the ProductsView and CustomersAndCountries view objects, by right-clicking on the SalesView object, selecting File > New > Business Tier > View Link, and then defining a view link that joins the Sales view to the Products view, and then another view link that joins the Sales view to the CustomersAndCountries view, so that my Application Module now looks like this once I add all view objects to the Data Model panel on the right:
The list of view objects in the Data Model panel are what the BI Administration tool will import metadata for when we connect to the ADF application later on, and as you can see there's many references to the various underlying database tables within the list. We'll see later on what this means in terms of which items we eventually work with, and why we just carried out that additional step to create joins from the sales to the dimension tables (view objects).
So far we've made a couple of adjustments to the ADF application to make it suitable for using as an OBIEE data source (creating joins between the fact and dimension sources that point in the right direction), and the process of making this ADF application's view objects available as a BI Repository data source involves a few more steps; we'll need to install a special shared library on the WebLogic server that hosts the ADF application, and we'll also need to make a couple more changes to the ADF application to work with this library. As such, at a high-level enabling OBIEE view object data access for a particular ADF application involves the following steps:
- Deploy the special shared library "OBIEEBroker" to the WebLogic domain that hosts the application, which the ADF application then uses to expose its data to the BI Repository
- Configure the ADF application's Application Module to use a JDBC datasource, then create a JDBC datasource of that name in the OBIEE WebLogic domain
- Within the ADF application, add a servlet that uses the OBIEEBroker shared library to expose the view objects to OBIEE's BI Server
- Redeploy the ADF application to the application server, now with the new servlet, and then use the BI Administration tool to connect to this and import the view objects' metadata into the physical layer of the BI Repository.
To keep my example simple, I'm going to use the WebLogic domain that hosts OBIEE to then host the ADF application, but bear in mind that in real-life, the WebLogic Server instance that comes with OBIEE is limited-use, meaning that you can't use it to host real production applications unless you convert the license to a full-use license.
Deploying the OBIEEBroker shared library to the WebLogic domain (which also needs to contain the Oracle Application Core (Webapp) and Oracle JRF libraries (included by default with OBIEE 11.1.1.6's WebLogic Domain) is typically done through WebLogic Administration Console, with the oracle.bi.integration.adf.ear file that contains the library found at [middleware_home]\Oracle_BI1\bifoundation\javahost\lib\obisintegration\adf.
The next step is to create a configuration entry for the application module in the ADF application that provides access to the view objects, so that the module can also use a JDBC datasource defined in the WebLogic domain to connect to the underlying source database (by default the module just uses a direct JDBC URL connection, but OBIEE needs it to use a proper, defined JDBC connection hosted in WebLogic). Make a note of the name you give this datasource (for example, java:comp/env/jdbc/SH_ConnectionDS) as you'll need the JNDI name element of this (jdbc/SH_ConnectionDS) later on when creating the datasource using WebLogic Server Administration Console.
We'll create the JDBC datasource on the WebLogic server shortly, but for now we need to do some configuration work within the ADF project to enable data access from OBIEE using the OBIEEBroker shared library. The first step is to take the ADF business component definitions within the Model project and bundle them up into a business component archive, which then gets deployed into a couple of JAR files that then provide access to the components for other modules in the final application.
So at this point we've taken the application's business components and packaged them up into JAR files for reusability, and now we're going to create the servlet that will use these business components and provide access to them via the OBIEEBroker shared library.
To do this, I create a new project within the application in JDeveloper, choose the Web Project option, and then choose Servlet 2.5\JSP2.1 as the servlet type. When I then come to name the context root that the servlet will be accessed by, this defines the application name that the BI Server will access the application by, as opposed to accessing it by its usual name (defined by the context root assigned to the ViewController module).
Next we need to configure this new project to include a reference to the business components archive JAR files we created earlier, so that it can make use of these data access components (which is the whole purpose of this servlet, obviously).
Then we have to edit the web.xml configuration file within the project to register the servlet provided by the OBIEEBroker shared library, replacing the entries within the <web-app></web-app> element. In the example I've used below, I've chosen to remove the restriction where these servlets are only available to certain application roles by entering DISABLE_BI_WHITELIST_ROLE_CHECK as the parameter value of the application role whitelist, I've disabled the feature where access is restricted to just a set of named application roles, but if you want to add this restriction you can list out the application roles between the <param-value></param-value> tags within the <context-param></context-param> element.
<?xml version = '1.0' encoding = 'windows-1252'?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5" xmlns="http://java.sun.com/xml/ns/javaee">
<context-param>
<description>This holds the Principals (CSV) that a valid end user should
have (at least one) in order to query the ADF layer from BI.</description>
<param-name>oracle.bi.integration.approle.whitelist</param-name>
<param-value>DISABLE_BI_WHITELIST_ROLE_CHECK</param-value>
</context-param>
<filter>
<filter-name>ServletADFFilter</filter-name>
<filter-class>oracle.adf.share.http.ServletADFFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>ServletADFFilter</filter-name>
<servlet-name>OBIEEBroker</servlet-name>
<dispatcher>FORWARD</dispatcher>
<dispatcher>REQUEST</dispatcher>
</filter-mapping>
<servlet>
<servlet-name>OBIEEBroker</servlet-name>
<servlet-class>oracle.bi.integration.adf.v11g.obieebroker.OBIEEBroker
</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>OBIEEBroker</servlet-name>
<url-pattern>/obieebroker</url-pattern>
</servlet-mapping>
</web-app>
Once complete, your web.xml file should look like the screenshot below, and you're now at the point where you can deploy the updated ADF application, with this new data access servlet, to the target application server.
There's now just two more configuration steps to do with the ADF application. The first creates a deployment profile for this updated version of the application, and includes a reference to the new web app and the OBIEEBroker servlet, whilst the second registers a dependency on the two key WebLogic shared libraries that provide the main ADF data access framework, and the specifics required for OBIEE to access these ADF view object data sources. Note that the first of these two libraries (oracle.applcore.model) seemed to be missing in the 11.1.1.5 version of OBIEE meaning that we could never get this feature to work, but it's back in 11.1.1.6 and seems to work fine.
Now we're at the point were we can deploy the updated ADF application to the WebLogic domain, which in this case is the one also used by our OBIEE 11.1.1.6 installation. Before we do this though, we need to create a JDBC datasource with a name corresponding to the JNDI name for the datasource specified in the new Application Module configuration defined earlier, which in the case of this example was "jdbc/SH_ConnectionDS", connecting through to the SH sample schema. This is done through WebLogic Administration Console, with the final JDBC datasource deployed to all servers in domain and listed alongside any other JDBC datasources within the domain.
Now you can deploy the application to the WebLogic server. Right-click on the global application and select Deploy, then choose the deployment profile you created earlier. When you come to choose the targets to deploy to, make sure you only deploy to those servers that have got the oracle.applcore.model and oracle.bi.integration.adf shared libraries deployed to them - on my installation, oracle.applcore.model is only deployed to the managed server cluster, and you'll get a deployment error if you try and deploy the application to the administration server as well.
So now we can move over to the BI Administration tool, and try and connect to this new ADF View Object data source. There's a couple of bits of information we'll need to create the connection:
- The Application Module name that provides access to the view objects, which in our case was called AppModule and needs to be referenced using it's fully qualified Java package name, which is "my.sampleADF.app.model.AppModule"
- The Application Module Configuration name, the extra configuration that we added previously to the application module that referenced the new JDBC datasource we just configured using WebLogic Administration Console. In this example, the configuration name is "AppModule_JDBC_DS"
- The JDBC Data Source name that the Application Module configuration uses (or you can leave blank if you just want to use the default JDBC datasource for that application module, which is what we'll do)
- The URL to access the application's OBIEEBroker servlet, which in this case is http://10.10.10.10:9704/SimpleADFAppVO/obieebroker
- A username and password to connect to the application server; in this case, weblogic/welcome1
Let's open up the BI Administration tool now and create a new connection through to the ADF application, using these details. Select File > Import Metadata, choose OracleADF_HTTP as the Connection Type, and then enter in the connection details. Once complete, press Next to proceed to the screen where you select the view objects who's metadata you wish to import; in most cases, there will be several views with similar names so just select one of each, then press Next to proceed. On subsequent pages you can use some new (experimental, to mind mind) features which map these objects into a business model, configure the repository for the BI Apps 11g and so on - ignore these and just press Finish to complete the import.
At this point your repository's physical model will have a number of references to ADV view objects, sharing the same name but with numbers after them., So which ones do you import, and why are there so many?
It comes back to the Application Module that we created earlier on, with its multiple references to view objects caused by view links (joins) between them. When the customers view object links to the sales view object, a reference to each are created in the application module, and another reference to the sales view object is created when the products view object references it. Crucially though, OBIEE considers these two sales view objects to be different physical layer tables, so you can't use them to join products and customers to sales as sales. Instead, this is why we added joins from the sales view object through to products, and then through to the combined customers and countries view object, so that we had a single reference to sales that then joined out to the dimension source objects; so therefore, when we now match the view object references in the application module to what's been imported into the BI Repository physical layer, it's easy to pick out the imported objects that will become the data source for our business model, as these are the ones that, when I check, have the right joins set up between them.
So now we can create the business model, and then the presentation model, for this data, defining the default aggregation for the two measures as I do so. When you try and validate the business model though, you'll notice that the joins are back to front - it thinks the sales table is the dimension table whilst the two dimensions are facts. This is because the join I set up in JDeveloper makes the sales table the master table in the master/detail relationship - to fix this, I go into the business model and re-join the sales logical table to the two dimension logical tables this time creating the direction of the join properly; there may be a more efficient way of doing this and if anyone knows ADF better than myself, please feel free to suggest how to set up the joins in JDeveloper so that they don't need this last bit of adjustment (I think it comes down to how you set the 1:* cardinality of the join, but I'd be interested to hear if there's a way to avoid creating these additional joins in the first place).
At this point if you check global consistency for the repository you'll hit two more issues, one you need to fix and one that we'll ignore for a moment. The view objects that represent fact table sources (SalesView1, 2 and 3) don't have primary keys defined for them but the BI Administration tool insists you do, so create them as appropriate (in this case, a composite primary key based on the product and customer IDs); the other warning is about a SQL Bypass Database but we'll cover that in a moment. Once done, save your repository, place it online if it's not already, and we'll try out a query.
If everything's set up correctly you should get some numbers back, as per the screenshot above. What you'll notice is though is that … it's really slow. The reason why will become apparent when you look in the query log file; what you'll see is the logical SQL query coming through, and then the call to the ADF application to retrieve the required attributes. However, the ADF application doesn't perform any aggregation, instead sending back all of the detail-level rows and having the BI Server perform the aggregation instead.
]]
[2012-10-14T09:52:22.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 45c97e564b475761:22a69728:13a5bf84463:-8000-0000000000001621] [tid: 14d4] [requestid: 42220011] [sessionid: 42220000] [username: weblogic] ############################################## [[
-------------------- SQL Request, logical request hash:
49b4d059
SET VARIABLE QUERY_SRC_CD='Report';SELECT
0 s_0,
"Sales History from ADF"."AppModule.CustomersAndCountriesView2"."CustCity" s_1,
"Sales History from ADF"."AppModule.ProductsView2"."ProdCategory" s_2,
"Sales History from ADF"."AppModule.SalesView3"."AmountSold" s_3
FROM "Sales History from ADF"
ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY
]]
[2012-10-14T09:52:22.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 45c97e564b475761:22a69728:13a5bf84463:-8000-0000000000001621] [tid: 14d4] [requestid: 42220011] [sessionid: 42220000] [username: weblogic] -------------------- General Query Info: [[
Repository: Star, Subject Area: Sales History from ADF, Presentation: Sales History from ADF
]]
[2012-10-14T09:52:22.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 45c97e564b475761:22a69728:13a5bf84463:-8000-0000000000001621] [tid: 14d4] [requestid: 42220011] [sessionid: 42220000] [username: weblogic] -------------------- Sending query to database named my.sampleADF.app.model.AppModule_AppModule_JDBC_DS (id: <<5118>>), connection pool named Connection Pool, logical request hash 49b4d059, physical request hash 28c5caab: [[
<?xml version="1.0" encoding="UTF-8" ?>
<ADFQuery queryid="11942-4827" locale="en">
<Parameters>
</Parameters>
<Projection>
<Attribute><Name><![CDATA[CustCity]]></Name><ViewObject><![CDATA[AppModule.CustomersAndCountriesView2]]></ViewObject></Attribute>
<Attribute><Name><![CDATA[ProdCategory]]></Name><ViewObject><![CDATA[AppModule.ProductsView2]]></ViewObject></Attribute>
<Attribute><Name><![CDATA[AmountSold]]></Name><ViewObject><![CDATA[AppModule.SalesView3]]></ViewObject></Attribute>
</Projection>
<JoinSpec>
<ViewObject>
<Name><![CDATA[AppModule.CustomersAndCountriesView2]]></Name>
<ViewLink>
<Name><![CDATA[AppModule.SalesCustomersAndCountriesStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.CustomersAndCountriesView2]]></ComplexJoinName>
</ViewLink>
</ViewObject>
<ViewObject>
<Name><![CDATA[AppModule.ProductsView2]]></Name>
<ViewLink>
<Name><![CDATA[AppModule.SalesProductStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.ProductsView2]]></ComplexJoinName>
</ViewLink>
</ViewObject>
<ViewObject>
<Name><![CDATA[AppModule.SalesView3]]></Name>
<ViewLink>
<Name><![CDATA[AppModule.SalesCustomersAndCountriesStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.CustomersAndCountriesView2]]></ComplexJoinName>
</ViewLink>
<ViewLink>
<Name><![CDATA[AppModule.SalesProductStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.ProductsView2]]></ComplexJoinName>
</ViewLink>
</ViewObject>
</JoinSpec>
</ADFQuery>
]]
Why is this? It's because the ADF application isn't designed to respond to aggregation requests from the BI Server (function push-down as we refer to it), so any functions required by the BI Server have to be performed after the ADF application returns all of the data required for the function.
The way around this is to define an SQL Bypass Database for the connection pool used by the ADF data source in the repository, that points to the same physical database that the ADF view objects connect to. When you configure an SQL bypass database in this way, the BI Server extracts the SQL for the ADF view object when it connects to the ADF application, takes this SQL and wraps any aggregation and other functions around it, and sends the SQL directly to the view object's database, bypassing ADF and running much more efficient queries.
To define an SQL bypass database, first create a connection to it within the Physical layer of the repository, defining the physical database and a connection pool setting. Then, edit the connection pool for the ADF physical database and select this database as the SQL bypass database, like this:
Once you've set the SQL bypass database for the connection pool, you can re-run the query again, and this time it should return results within a few seconds. If you look at the query log again, you'll see this time a reference to an SQL bypass database being defined, and the actual SQL query sent to this database, based on the SQL used to define the view objects used in the query.
]]
[2012-10-14T10:37:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 45c97e564b475761:22a69728:13a5bf84463:-8000-0000000000002114] [tid: 1754] [requestid: 5a130001] [sessionid: 5a130000] [username: weblogic] -------------------- General Query Info: [[
Repository: Star, Subject Area: Sales History from ADF, Presentation: Sales History from ADF
]]
[2012-10-14T10:37:57.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 45c97e564b475761:22a69728:13a5bf84463:-8000-0000000000002114] [tid: 1754] [requestid: 5a130001] [sessionid: 5a130000] [username: weblogic] -------------------- Sending query to database named my.sampleADF.app.model.AppModule_AppModule_JDBC_DS (id: SQLBypass Gateway), connection pool named Connection Pool, logical request hash 2415f206, physical request hash 8b5cc1ff: [[
<?xml version="1.0" encoding="UTF-8" ?>
<ADFQuery mode="SQLBypass" queryid="24464-5705" locale="en">
<Parameters>
</Parameters>
<Projection>
<Attribute><Name><![CDATA[CustCity]]></Name><ViewObject><![CDATA[AppModule.CustomersAndCountriesView2]]></ViewObject></Attribute>
<Attribute><Name><![CDATA[ProdCategory]]></Name><ViewObject><![CDATA[AppModule.ProductsView2]]></ViewObject></Attribute>
<Attribute><Name><![CDATA[QuantitySold]]></Name><ViewObject><![CDATA[AppModule.SalesView3]]></ViewObject></Attribute>
<Attribute><Name><![CDATA[AmountSold]]></Name><ViewObject><![CDATA[AppModule.SalesView3]]></ViewObject></Attribute>
</Projection>
<JoinSpec>
<ViewObject>
<Name><![CDATA[AppModule.CustomersAndCountriesView2]]></Name>
<ViewLink>
<Name><![CDATA[AppModule.SalesCustomersAndCountriesStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.CustomersAndCountriesView2]]></ComplexJoinName>
</ViewLink>
</ViewObject>
<ViewObject>
<Name><![CDATA[AppModule.ProductsView2]]></Name>
<ViewLink>
<Name><![CDATA[AppModule.SalesProductStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.ProductsView2]]></ComplexJoinName>
</ViewLink>
</ViewObject>
<ViewObject>
<Name><![CDATA[AppModule.SalesView3]]></Name>
<ViewLink>
<Name><![CDATA[AppModule.SalesCustomersAndCountriesStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.CustomersAndCountriesView2]]></ComplexJoinName>
</ViewLink>
<ViewLink>
<Name><![CDATA[AppModule.SalesProductStarFK1]]></Name>
<ComplexJoinName><![CDATA[AppModule.SalesView3_AppModule.ProductsView2]]></ComplexJoinName>
</ViewLink>
</ViewObject>
</JoinSpec>
</ADFQuery>
]]
[2012-10-14T10:37:58.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 45c97e564b475761:22a69728:13a5bf84463:-8000-0000000000002114] [tid: 1754] [requestid: 5a130001] [sessionid: 5a130000] [username: weblogic] -------------------- Sending query to database named ADF VO SQL Bypass DB (id: <<1106>>), connection pool named SHConn, logical request hash 2415f206, physical request hash 1ecc951f: [[
WITH
SAWITH0 AS (select T46492.C304898130 as c3,
T46492.C100315219 as c4,
T46492.C426174690 as c6,
T46492.C111943045 as c7
from
(SELECT V11235427.CUST_CITY AS C304898130, V423473769.PROD_CATEGORY AS C100315219, V295179950.QUANTITY_SOLD AS C426174690, V295179950.AMOUNT_SOLD AS C111943045, V295179950.ROWID AS PKA_RowID0, V11235427.CUST_ID AS PKA_CustId0, V423473769.PROD_ID AS PKA_ProdId0 FROM (SELECT Sales.PROD_ID, Sales.CUST_ID, Sales.TIME_ID, Sales.CHANNEL_ID, Sales.PROMO_ID, Sales.QUANTITY_SOLD, Sales.AMOUNT_SOLD, Sales.ROWID FROM SALES Sales) V295179950, (SELECT Customers.CUST_ID, Customers.CUST_FIRST_NAME, Customers.CUST_LAST_NAME, Customers.CUST_GENDER, Customers.CUST_YEAR_OF_BIRTH, Customers.CUST_MARITAL_STATUS, Customers.CUST_STREET_ADDRESS, Customers.CUST_POSTAL_CODE, Customers.CUST_CITY, Customers.CUST_CITY_ID, Customers.CUST_STATE_PROVINCE, Customers.CUST_STATE_PROVINCE_ID, Customers.COUNTRY_ID, Customers.CUST_MAIN_PHONE_NUMBER, Customers.CUST_INCOME_LEVEL, Customers.CUST_CREDIT_LIMIT, Customers.CUST_EMAIL, Customers.CUST_TOTAL, Customers.CUST_TOTAL_ID, Customers.CUST_SRC_ID, Customers.CUST_EFF_FROM, Customers.CUST_EFF_TO, Customers.CUST_VALID FROM CUSTOMERS Customers, COUNTRIES Countries WHERE Customers.COUNTRY_ID = Countries.COUNTRY_ID) V11235427, (SELECT Products.PROD_ID, Products.PROD_NAME, Products.PROD_DESC, Products.PROD_SUBCATEGORY, Products.PROD_SUBCATEGORY_ID, Products.PROD_SUBCATEGORY_DESC, Products.PROD_CATEGORY, Products.PROD_CATEGORY_ID, Products.PROD_CATEGORY_DESC, Products.PROD_WEIGHT_CLASS, Products.PROD_UNIT_OF_MEASURE, Products.PROD_PACK_SIZE, Products.SUPPLIER_ID, Products.PROD_STATUS, Products.PROD_LIST_PRICE, Products.PROD_MIN_PRICE, Products.PROD_TOTAL, Products.PROD_TOTAL_ID, Products.PROD_SRC_ID, Products.PROD_EFF_FROM, Products.PROD_EFF_TO, Products.PROD_VALID FROM PRODUCTS Products) V423473769 WHERE V295179950.CUST_ID = V11235427.CUST_ID AND V295179950.PROD_ID = V423473769.PROD_ID) T46492),
SAWITH1 AS (select sum(D1.c6) as c1,
sum(D1.c7) as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH0 D1
group by D1.c3, D1.c4)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select distinct 0 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c2 as c4,
D1.c1 as c5
from
SAWITH1 D1
order by c3, c2 ) D1 where rownum <= 65001
]]
Apart from using ADF view objects as a data source for OBIEE, you can also use the BI Repository as a data source for ADF business components, and you can combine the two in an ADF application to return, say, actuals numbers from an ADF application with forecast numbers coming from Essbase via the BI Repository. For now though, what are the conclusions from the above exercise?
Firstly - it works, which is more than can be said for the 11.1.1.5.x releases of OBIEE, which for some reason broke the link between ADF and the BI Server such that whilst metadata import worked, you couldn't actually retrieve any data. Once you know what you're doing it's reasonably straightforward to do (the docs detail the setup process well), but it took me a long while to work out what was wrong with the joins between the view objects and how to make them work properly with OBIEE. The SQL bypass database feature is essential as otherwise, unless you've got a very small dataset behind the ADF application it'll just run too slow, but they key takeaway from me is that you need to specifically enable this feature with your ADF application, through the obieebroker web application and the changes to VOs and the Application Module, before it'll work. Nothing too complicated or dramatic but something to bear in mind when considering using ADF applications as a data source.
Well that's it for me for a while now around OBIEE; for a few months now I've actually been working more with Oracle Data Integrator than OBIEE, so I've got a bunch of blog posts in mind about running ODI in a complex, high-availability environment with release management and deployment requirements; check back in a while for some new posts around this different type of topic.