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.

FDMEE Custom Reports...A.K.A. BI Publisher

For the past several months I have been working with FDMEE.  In the course of the project it has become necessary to document our functional design of the FDMEE setup and integrations.

I am a firm believer in automating that which can be automated.  Fortunately for me the design of an FDMEE implementation is stored in the supporting tables within the FDMEE Schema that is created with during the FDMEE install and is updated as integrations are built. 

When I reviewed the reports that come Out of the Box in FDMEE I found that they met some of my needs but were also lacking in layout and data elements.  So I spent some time looking into how the reports in FDMEE are generated.

The report engine in FDMEE is an OEM version of Oracle BI Publisher.  The nice thing about BI Publisher is that it separates the query design from the report layout design from the translation files.  Then at runtime these are brought together to produce a report output. 

The enterprise version of BI Publish is very robust allowing for a large number of sources with several different layout design tools and layout template types producing many different report outputs to a variety of destinations.

BI Publisher within FDMEE is limited to SQL Query against the FDMEE Schema using MSWord RTF Templates to produce output in either PDF, HTML, Excel or XLSX format to the local file system FDMEE\outbox\reports.

The next series of posts I will show how to create a custom report within FDMEE using the following steps:

• Creating a Query Definition
• Designing a Report Layout
• Creating a Report Definition

Fun with Jython and FDMEE – Getting ResultSet Column Names

When working with the fdmAPI call the documentation indicates that return is a ResultSet.

But what is in the ResultSet?

Sometimes the documentation indicates the table that ResultSet is based on.

Well you can open your favorite SQL Tool and query the FDMEE repository tables if you know what to look for.

In the case of the getLocationDetails call; it queries from the TPOVPARTITION table.

 

But can you get that information without a SQL Tool?  How can you do it within Jython? 

After a bit of searching the Python/Jython coding sites I found a method on the ResultSet that provided the information I needed.  The method ResultSet.getMetaData() provides the structure to get information needed.  The .getMetaData() coupled with metadataCollection.getColumnCount() and metadataCollection.getColumnName(##).

So, after a bit of trial and error, I came up with the following.

This produces a list.

 

So having these column names and if you look back on my first post on Fun with FDMEE and Jython where I used the method ResultSet.getString("ColumnName"), I can get the value of any field of the ResultSet without using SQL Tool and I can get creative with my Custom Scripts.

Until Next time...Happy Coding!


Update 6/3/2016:
------------------------
I just got to reading the 11.1.2.4.200 FDMEE Admin Guide published April 2016 and noticed the nice little block of code at the end of the description for getLocationDetails(BigDecimal pPartitionKey) within the Using the JAVA API section. 

 

 

Fun with Jython and FDMEE

The past few months I have had the opportunity to assist in FDM to FDMEE migrations and during that time I have been able to explore Jython scripting.
 
I have loved programming since I was in high school and wrote my first program on an Apple 2.  Yeah, a very long time ago.  No matter where my career path has taken me I seem to find a way back into some form of programming.
 
As part of the migration efforts I needed to convert the FDM VBScript to Jython.  While some of the Event Scripts could have been kept in VBScript it was cleaner to convert to Jython for 2 reasons...VBScript is not strategic for Oracle with FDMEE and I like a puzzle.
 
One of the migrations I had was from FDM Classic with the Essbase Source Adapter to FDMEE 11.1.2.4.  This particular client had stored email server and email addresses in the integration settings to be used to send an email upon failure during any part of the data loading done by FDMEE.
 

 
The one thing I did not want to do was to embed this information in my each one of my Event Scripts - AftImport, AftValidate, AftExportToDat, AftLoad, AftCheck...
 

 

 
So where could I store this information and yet make it accessible to easily modify once.  Why not the Integration Options for a Location?
  

 
If I look at the TPOVPARTION table which stores location information is the 4 Integration Option values.

A simple SQL statement

So how do I get these easily without having to write SQL in my Jython?

Enter the FDMEE API and particularly the 

 

     getLocationDetails(BigDecimal pPartitionKey)

 

 

So what does this look like in Jython in my Eclipse editor?

 

And the Output...

 

So putting it into my FDMEE Event Scripts...

Now the neat thing about this approach is the if there are different people responsible for different locations they could get individual emails when there is an issue with their location by switching out the hardcoded fdmLocID to the fdmContext["LOCKEY"].

Well that was a fun diversion from BI and since I am having fun with Jython and FDMEE I may find some other neat tidbits to share in the future.