Friday, August 11, 2017

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.

Tuesday, April 18, 2017

Data Visualization - Smart Insights

Smart Insights have been available in Oracle Data Visualization Desktop for a few releases and now available in Oracle Analytics Cloud (OAC)

So what are Smart Insights?
• They provide an at-a-glance assessment of your data
• Allows analysts to quickly understand the information the data contains
• You can easily see how measures are distributed in various attributes/dimensions
• Provides starting point for further data analysis

In looking at tabular data it is difficult to see patterns and distribution of measures across dimensions

When we look at the same data via Smart Insights we can see how the data is distributed across attributes/dimensions.
So how does one access Smart Insights?

First add a data source to a project.  Then switch to Prepare Mode. Finally switch the view from Data to Visual.

The Visual Mode creates a series of simple data visualizations.  The initial views are the number of rows of data distributed across all the attributes/dimensions in the data set.

The view can easily be changed from row count to other measures/facts in the data set by changing the Summarize by.
Additional properties that help with analyzing the data is flagging whether show null rows in an attribute/dimension.  The include others option is useful when the data has many different values in an attribute since the number of values on an axis also referred to as binning is limited.

The Binning of values on the X and Y Axis follow these
• Number of Bars Depends on Data Distribution
• Normally 10 bars are shown and all other data is displayed in a bar called Other
• If 20% or more of the data falls into the Other the system will break that data into the number of bars needed

The style of the visualizations align to the type of data in the attribute/dimension.
• Non-numeric or Text - Horizontal bar chart
• Date and Time - Line chart
• Numeric - Vertical bar chart

From the samples shown throughout this post one can see how beneficial they are to easily understanding the data, finding initial patterns and providing a starting point for further data analysis.

Monday, December 12, 2016

Oracle Data Visualization Desktop Connecting to Essbase

With the December update to Oracle Data Visualization Desktop came the beta connection to Essbase.
This is a nice first step to OLAP connections but definitely has a way to go.
To create the connection to Essbase create a new Connection
Select Essbase
Provide Connection Information.  I just put the name of the server running Essbase. (IP address works too.)
Select your Cube
Create new Project
And view the Project Details

For Essbase users there are a few things to note:

  • Measures Dimension is flattened
  • Gen1 of the hierarchy is dropped
  • UDAs come in as separate columns on Dimension
Inspecting the Data Source provides very little information and there are no options to pick a dimension to use as measure.

This new connection is a great first step but harkens back to the days of OBIEE 10 connecting to Essbase.

I am looking forward to kicking the tires on this connection in some DVD Projects.

Wednesday, November 30, 2016

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:

Tuesday, September 6, 2016

BICS - Deliveries

Recently I signed back into BICS to work on an internal project and decided to poke around to see what is new.  I am bad at reading the "What's New" and tend to just jump in and see what changed with the latest maintenance update for August 2016.
One thing I noticed is the new feature within the Console interface.

This feature harkens back to OBIEE 10.x feature labeled "Delivers" which was renamed to "Agents" in 11.x.
Funny how old names are recycled...
In this case the two are not equal.  Deliveries, like most new features in BICS versus on-prem OBIEE, is a scaled down version of the on-prem counterpart.  Like its name "Deliveries" suggests, it is a way to send the output of a report/analysis to a recipient where as Agents has many more features and options available.

Deliveries is a great first step into scheduled email delivery of content to users.

Within the BICS Catalog view of Analyses there is a new "right click" menu item - Email.

On-prem OBIEE we have "Schedule"

This is where I have the biggest frustration...Why difference in names over similar features...

On-Prem there are Analyses and BICS we have Analyses and Dashboards.

So why "Schedule" for on-prem and "Email" for BICS?

Yes, I understand that OBIEE on-prem there is not full parity of features with BICS for Analyses and Dashboards yet the names are the same.  So then why the difference for sending of OBIEE/BICS content to someone via email?

OK, I will get off my soapbox...

The Deliveries option within BICS is a very simplified version of OBIEE Agents/Scheduler.

First the interface; much more simplified and limited options in BICS.

versus the Agent options within on-prem OBIEE which allows for conditional logic and delivery options.

This new feature is very much welcome.  It allows BICS content to be shared with others who may not have BICS license.  Though, a feature that I wish was added is the ability to add content into BICS users Home Page or Header like within OBIEE Agent Destinations tab.

From an Administrative perspective I do like the interface to see the status of "Deliveries".

While BICS Deliveries has a long way to go to match OBIEE Agents; it is a welcome first step.

Wednesday, June 1, 2016

Countdown to KScope16

In just over 3 weeks from the date of this post KScope16 will take place in Chicago.  Once again I was privileged to serve as the Content Lead the Business Intelligence and Data Warehousing track.  I was very fortunate to once again work with a great group of people to review and select the sessions for this years BI & DW track. 

Michelle Kolbe (@MeKolbe)
Michael Rainey (@mRainey)
Nicholas Hurt (@Nicholas_Hurt)
Christian Berg (@Nephentur)
Christian Screen (@ArtOfBI)
Kevin McGinley (@kevin_mcginley)

This team took time out of their already busy lives to review over 90 sessions for 28 slots.  They gave up weekend time to get on a conference call to slug it out for their favorite sessions and finally coming together to pick what we felt would be sessions that helped showcase the BI & DW Track.

While you can see the Session Line up here I thought I would share some interesting facts about the BI & DW Sessions for KScope16.  For starters it was decided to spin off a separate track for Big Data and Advanced Analytics.  This allowed for the two tracks to focus on content core to those themes.

This year between BI/DW and BD/AA tracks KScope will have 7 hours of Hands On Labs with 5 of the labs being a 1 hour session.

15 Different Companies are represented as Primary Speakers.

13 of the 24 Primary Presenters have some form of Oracle Ace Designation covering 18 sessions.

Aside from the track sessions we had pick a topic and presenters for the Thursday Deep Dive.

The final bit of business I had to complete for KScope16 was to work with Vasu Murthy (@vmurthy), our Oracle Liaison, to set up the Sunday Symposium.  This was my second year working with Vasu, he was great in helping line up Oracle Product Management Team to put together a full day of sessions.  The line up can be found here.

I am really excited to see all my ODTUG Friends in Chicago for KScope16.  KScope has been my professional development home since 2010 in Washington D.C. and I am proud to have been a part of BI Track content selection for the past 5 years.

I hope to see you in Chicago!

Thursday, May 12, 2016

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