OAC - Import Essbase Cloud to OAC-BI Admin Tool

I finally have time to play with Oracle Analytics Cloud Enterprise Edition (OAC EE).  

I have been interested in setting up my OAC BI instance to work off an Essbase Cloud cube.

So; First Things First...Download the latest version of the OBIEE Admin Tool that supports BICloud

http://www.oracle.com/technetwork/middleware/bicloud/downloads/index.html

At the time of this post it was 12.2.2.0.20

Keep an eye out for updates...

I have an Oracle Analytics Cloud Enterprise Edition instance that allows me to work with an RPD for Data Modeling instead of the built in data modeler that we know from BICS.  The BICS Data Modeler does not let me connect to Essbase Cloud Service.  I also have an Oracle Analytics Cloud Essbase Cloud Service.  I need to make my Essbase Cloud Cubes available as subject areas in my OAC-BI instance.

The cube I will start with is the Sample.Basic from my Essbsae Cloud server.

So starting up the Admin Tool I need to create a new RPD.  I selected 'No' for the Import Metadata because I like to create my Physical Layer one piece at a time. 

In the Physical Layer I created a New Database...

Since I am connecting to an Essbase Cloud instance I selected the latest version of Essbase that is available in the list of Database Types,

Next I created the connection pool.  I put the Public IP address for my Essbase Cloud Instance

I import the Essbase metadata by right clicking on connection pool

I authenticated to the Essbase Cloud instance and navigated to my Cube to import and started the Import.  The process started and up popped a message.

So it was time to see what following the Import Metadata option from File menu after deleting everything in the Physical Layer.

I selected the available Connection Type - Essbase 9+ 

 

set my connection to the public IP

Once again navigated to my cube and imported it

This time it worked

And my cube shows up fine in the Physical Layer.

After renaming the Database, Connection Pool adjusting Dimension properties to suit my needs I followed same process as an on-prem cube in the RPD.

It is time to save all my work and like any good RPD Developer I click yes on the Global Consistency Checker.

And what do we have here...?  The warning indicates that my Database Type is not correct.

I checked the Physical Layer properties of the database, I see that i have a couple more options than I did when I selected Import Metadata.  I selected the most recent release version available in the list.

This time when I saved and allowed for Global Consistency Check I did not get any warnings or errors!

So it is time to submit an SR and  deploy to the cloud!  But that is topic for another post....

FDMEE Custom Reports: Query Definition

When creating a new custom report one first needs data and the Query Definition is the starting point in the process.  As I wrote in the first post of this series the report engine for FMDEE Reports is Oracle BI Publisher.  The query engine within BI Publisher generates and XML file and that XML file is joined up with the Layout Template and Translation Template to produce the Report output.

As with most of the ERP/EPM systems on the market the database supporting the
application can be quite intimidating and the vast number of tables confusing.  Fortunately most custom reports are derivatives of existing reports and therefore using the existing Query Definitions as a starting point can be beneficial in learning the tables used and their purpose.

For this exercise I am interested in a report to list Locations and the Data Load Rules associated with that Location. 

In the Query Definition section there is a Query that looks similar to what I need.  I will copy the SQL from the 3 text boxes.  The three different boxes allow for the use of inline parameters from prompts in the WHERE clause.

Since my FDMEE Repository is on Oracle Database I will paste the SQL into SQL Developer.  Using [Ctrl] [F7] keys I can view the SQL in a nicely formatted layout.

Since I like to work in a Graphical view of my queries I will switch to the Query Builder view. This allows me to see the tables and joins easier.

The following image show some of the more frequently used tables within the FDMEE database/schema and what information they contain.

So now that I have a better understanding of the tables.

The report I want to produce is Data Load Rules per location and since the Data Load Rules are tied to Category I would like to know that as well.

Since my report only needs 3 columns I will create a new query in SQL Developer and drag the AIF_BALANCE_RULES, TPOVPARTITION and TPOVCATEGORY tables into the Query Builder and Join them as shown below.  Then I will select the columns I am interested in from the tables and finally I will set the sort order.  One tip with Reports it is faster to sort the data on query rather than within the report layout.

I will need to switch back to the Worksheet view in order to collect the SQL for my query.

In FDMEE I will create a new query definition named Location Data Load Rules and paste into the 2 text areas; the SELECT statement up to the ORDER BY into the Select Clause and paste the ORDER BY statement into the Group By/Order By Clause.

Save the Definition and then click the Validate Query to make sure that FDMEE does not have any issues with the query.

In order to work with the BIPublisher MSWord Template Builder I will need a sample data file. The Query Definition interface has a Generate XML button. Clicking this button allows me to create the sample XML data file. Typically it will return 25 rows of data.

When I open the file I can see the data structure of the query. In the case the result of my simple query there is the Location name, the Category name and the Data Load Rule name for each row of data. 

Now that have a sample data set I can create the layout for my report. But that is the topic for another post.