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.