Oracle BI Cloud Service Part II: Loading Data

.

In the first post of the Oracle BI Cloud Service (BICS) series we reviewed some of its key features. In the following posts, we will explain how the service actually works, starting with the data provisioning.

All the data used in the BI Cloud Service has to be stored in the Database Schema Service or Database as a Service, so uploading your data to the cloud is the first step towards a cloud OBI. There are several ways to do it, depending on your specific requirements:

Data Loader.

The default method of uploading your data to the cloud is by using the built-in Data Loader accessible from the BICS homepage.

BICS_II 1

Figure 1: Accessing the Data Loader tool (from Oracle BI Cloud Service - A First look )

 

Data Loader is an easy-to-use browser-based application that allows you to upload data from files (txt, csv, xsl, xlsx) with a maximum of 500,000 rows and formatted as number, date, or character (for the moment only UTF-8 encoding is supported). There is also the possibility to perform simple transformations such as Uppercase, Lowercase, Trim Spaces, and Format as a Number.

You can use Data Loader to perform full manual refresh, incremental data loadings, upsert (update/insert), add data to the model, view the load history and correct errors.

SQL Developer

Those who have worked with other Oracle Database products will already know SQL Developer. Now the application features a new cloud connection that you can configure to load data from files (with no row limit) and relational sources to your cloud service. It runs on your local machine, so you need to download it from the OTN downloads webpage and install it locally. Once configured, SQL Developer connects to the cloud service through a set of RESTful web service calls and can be used to load data using SFTP (SQL*Loader utility). You can also create “carts” with multiple objects to be uploaded, and schedule incremental loads.

 

BICS_II 2

Figure 2: Loading carts (from Loading Relational Tables Using SQL Developer)

BI Cloud Service (BICS) Data Sync

Data Sync is another application for loading your data to the cloud service and available for download on the OTN downloads webpage. It is a wizard-driven tool supporting the load of data from CSV files and relational sources into the Database Schema Service. It also supports the load and merge of data from different sources: DB2, SQL Server, MySQL, Teradata, and TimesTen, apart from Oracle relational sources.

Use BICS Data Sync to perform incremental data loads, rolling deletes, and loads according to appended load strategies. You can also schedule and monitor data loads (called “jobs”).

Be aware however that Data Sync is not officially released and Oracle does not support it and does not guarantee that it will be supported in future releases (future upgrades are not guaranteed either), so care should be taken when using it.

BICS_II 3

Figure 3: BICS Data Sync (from Loading On-Premises Relational Sources and Files to the Cloud Using Oracle BI Cloud Service Data Sync)

BICS & Database Schema Service REST APIs

You can also use the BI Cloud Service and Database Schema Service REST APIs to define a customized API and programmatically load data into the Database Schema Service. With the BICS REST API, you can update statistics, drop or create indices on tables, insert, update, upsert, and delete records. You can also combine the API with any other application and script in any programming language, offering endless possibilities such as invoking applications and scripts with an on-premises scheduler of integrating the load process with ETL tools.

Similarly, with the Database Schema REST API you can call out SQL queries to read data and return results, call PL/SQL scripts to read, write, modify or delete data, and define your own customized API to be invoked from your on-premises environment. Moreover, using the APEX_WEB_SERVICE package within a PL/SQL block, you can invoke any REST/SOAP API supported by cloud applications, and retrieve data from external systems.

PL/SQL Scripts

Finally, you can use PL/SQL scripts to load data from external, generic web services. Scripts can be created using SQL Workshop, a robust browser-based tool part of APEX that allows you to create and execute SQL queries and database procedures against objects in the Database Schema Service.

 

Check out part III of the BICS series in which we will cover how you can model your data in the cloud according to your business requirements.

 

Oracle BI Cloud Service Part I: Introduction & Key Features

.

Last year Oracle released the Business Intelligence Cloud Service (BICS) as part of its Platform as a Service (PaaS) offering, and it has been releasing upgrades regularly since then, some with important new features. In this BICS post series we will be giving an overview of the BI cloud service and walking you through some of its key features and functions:

  • Part I: Introduction & Key Features
  • Part II: Loading Data
  • Part III: Modeling Data
  • Part IV: Creating analyses, reports and dashboards
  • Part V: Managing the Service & Conclusion

Cloud technologies are not something from the future anymore but a reality, with some operating systems like Google Chrome OS relying almost completely on cloud applications. Trends indicate that the shift towards cloud environments will accelerate even more in the next years, so companies are rushing to release cloud versions of all their software solutions. Oracle has been releasing many of its software platforms into the cloud over the past years, and last year was the turn of OBI with the Business Intelligence Cloud Service.

Captura 1

Figure 1: Business Intelligence Cloud Service Web Page

 

 

Below are some of its key features.

The service is 100% cloud-based, with almost no local installs needed (some data loading tools might be required locally), and it comes bundled with the Oracle Database Schema Service, as BICS can only report against data stored in the cloud. With the Database Schema Service you get 1 schema on Oracle Database 11g with 50GB of storage, full PL/SQL support, multiple tools for loading the data, Oracle Exadata hardware running in the background, RESTful web services, and Oracle Application Express (APEX), but no SQL*Net access. Since the last release, there is also the possibility to connect the BI service to a Database as a Service (DBaaS), which includes a dedicated virtual machine running Oracle Database 11g or 12c with full SQL*Net access and all the storage capacity we may need.

One of the most interesting features of the service is that it is 100% mobile ready: all the content you create in the cloud will be immediately available in any iOS or Android device downloading the Oracle BI Mobile App from the App Store or Google Play and with no extra programming required (touch gestures such as zoom or swipe are already built in).

Capture 2

Figure2: 100% mobile ready. 

(From the Oracle BI Cloud Service & Data Discovery Webinar from Oracle Deutschland B.V. & Co. KG,

available at http://oracle.arrowecs.de/fileadmin/arrowecs/Redaktion/pdf/hersteller/Oracle_Hardware)

 The service comes with two instances: PROD and pre-PROD for developing and testing, and provides automatic full system backup & restore, role based grain security, and simple self-administration. All the patching and system maintenance is handled by Oracle so you don’t have to worry.

So what are the benefits of the BI Cloud Service compared to the traditional on-premise OBI? The first benefit we can think of is the huge cost reduction: you only have to pay a monthly subscription of $250 per month and per named user (10 users minimum) for the BI Cloud Service plus $1000 per month for the 50GB of cloud storage in the Database Schema Service, so a minimum of $3500 per month, which is much cheaper than an OBIEE license. Not having to purchase annual licenses and being able to pay just for the storage capacity you require are big bonuses too. Moreover, working in the cloud means working with a standardised and consolidated infrastructure (there is no need for servers, storage, or any other network components). Therefore, you get to dramatically reduce your capital and operation expenditures compared to the expenditures you would incur with on-premises OBI.

As you need fewer database elements and you get standardised operating systems, servers, database versions and configurations, the administrative burden is also dramatically reduced. Also given that the system maintenance is fully managed by Oracle, you get guaranteed system availability. This means that overall the environment will be more reliable and manageable, resulting in lower risks for the user.

Finally, another key benefit of using the BI Cloud Service is agility. The rapid deployment of environments for development and production and the shorter upgrade cycle make the system highly agile, and thanks to the Oracle Real Application Clusters (RAC) you get adaptive cloud databases for workload volumes.

Be sure to check out the rest of our BICS series posts for more detailed information on the basic features of the service.

 

Oracle Financial Analytics: General ledger to sub-ledgers navigation

.

When implementing Oracle Financial Analytics, it has been noticed that it is a common requirement to enable navigation from general ledger to sub-ledgers reports to obtain information on specific transactions. One of the main reasons for this is to reconcile general ledger to individual sub-ledgers.

The general ledger is the master set of accounts that summarise the sub-ledger transactions. It is used to add information into the financial statements and journal entries.  A sub-ledger is a detailed record of transactions for an individual account that contains details of transactions for an account.

Sub-ledgers serve as support for amounts posted to the general ledger. It is important to reconcile the general ledger balances to the sub-ledger balances on a regular basis to spot possible discrepancies.

In this blog article, I will describe how this process can be approached in OBI Apps. I will describe the OBI Apps data model and the configuration required. Also, I will describe the creation of sample navigation from general ledger to sub-ledger by the creation of an intermediate analysis.

The animation below shows the expected navigation from GL Account balance to Payables details.

Financial Analytics

Note: This information applies when the data source is Oracle EBS.

OBI Apps allows this navigation by default using the ‘GL Journal ID’ column as a link between subject areas. It is available in general ledger transaction subject area and in the following sub-ledgers (In some cases, ‘GL Journal ID’ has to be exposed to the presentation layer):

  • Accounts Payable
  • Accounts Receivable
  • Revenue
  • Purchase Orders
  • Fixed Assets (Available in OBIA 11g)

Understanding the OBIA model

‘GL Journal ID’ is a unique identifier of an EBS journal entry line and it is available in the linkage table W_GL_LINKAGE_INFORMATION_G.

Sub-ledger transaction tables are linked to W_GL_LINKAGE_INFORMATION_G at source distribution level.

Finantial Analytics

For each sub-ledger transaction fact, there is a logical “transaction detail” dimension comprising the transaction fact and the W_GL_LINKAGE_INFORMATION_G table, ‘GL Journal ID’ is available in this dimension. The figure below shows the physical source “Fact_W_AP_XACT_F_GLJournalID” of the logical dimension “Dim – AP Transaction Details”.

Finantial Analytics

With this configuration, it is possible to have ‘GL Journal ID’ available in the sub-ledger transaction subject areas. And since ‘GL Journal ID’ is also available in “Financial - GL Detail Transactions” subject area, it is possible to configure the navigation from general ledger to sub-ledger transactions by using the OBIEE Navigation functionality.

Creating GL to sub-ledger navigation

‘GL Journal ID’ is available in the two subject areas that we want to link, but the level of detail between the source and the target reports are not the same, which is why it is necessary to create an intermediate analysis to link the two reports. This process is described in this section.

  1. Create the GL report and enable navigation.

Using the subject area “Financial - GL Detail Transactions”, create a new analysis that shows the Balance by Accounting Document Source of a selected account for a selected period. Accounting Document Source is used to identify the sub-ledger source.

Finantial Analytics

  1. Create an Intermediate analysis that contains the ‘GL Journal ID’.

Using the subject area “Financial - GL Detail Transactions”, create an analysis that contains the ‘GL Journal ID’ column. Account and period filters need to be created as well. This report will be used to filter the sub-ledger Transactions.

Finantial Analytics

  1. Create a detailed sub-ledger report

Using one of the sub-ledger subject areas, create a new analysis and filter the results by existing report. Use the analysis created in step 2 as the “Saved Analysis”.

 

image 6 Finantial Analytics

  1. Enable navigation

In the source report (created in step 1), using Action Links, enable navigation to the different sub-ledger reports. The example below shows the navigation to AP Transaction report when Account Document source is Payables.

Finantial Analytics

In conclusion, the OBI Apps data model is prepared to enable the link between General ledger and sub-ledger reports, but it has to be configured at analysis level. This feature can be used for the reconciliation process between General ledger and sub-ledgers.

If you want to go further, create a report that shows summaries sourced by general ledger, a second column that shows sub-ledger totals and a third column that shows the difference between these two amounts, if the value of this column is not ‘0’ navigate to details to discover the discrepancies.

 

Oracle BI Mobile App Designer v2 – Key Features

.

Introduction

Oracle BI Mobile App Designer (MAD) is the tool integrated on OBIEE 11g to perform analytics on mobile devices, as explained on a previous blog article, this article will extend the Oracle BI Mobile App Designer explaining the new features of the Mobile App Designer V2.

This version of bimad comes into the patch 18794832

Redesigned UI

The layout of the tool has completely changed, now we can add components on the pages using the “+” button on the top of the page designer, filters and calculated fields will appear on the left hand side of the window, below the datasources.This new design is much intuitive than the previous one:

Oracle MAD

Oracle MAD

MAD 3

 

Exploration Template

This new page template will allow us to make an exploratory analysis on the data by adding filters on the right hand of the layout, for numerical data, the filter displays as an slidebar, for categorical data, we can choose several values from the list of the available filters

Oracle MAD

 New Map Component

This is probably one of the most useful changes on this version, it comes by default with a map component, so there is no need to code your own plugins to visualize maps, we can choose between region maps and location maps, which allow us to display lat-long coded data

Oracle MAD

there are several country maps you can download from the oracle OTN here:

http://www.oracle.com/technetwork/middleware/bi-foundation/default-2397753.html

the map component of the first releases of MAD V2 did not have zoom button features on the map, so you may need to download and install the April’15 patch to use it, you will have just to deploy the new .ear file. Can be downloaded here:

https://support.oracle.com/epmos/faces/PatchDetail?_afrLoop=280648419500544&patchId=20823740&_afrWindowMode=0&_adf.ctrl-state=qiyeq9qlu_66

Calculated Fields and filters

Now we will be able to create calculated fields and filter. Using the Formula editor we can create custom fields, this fields will be available as any other element in the data source.

Oracle MAD

To create a Filter select the filters tab in the right pane and click the + icon, you can the apply this filter to the different charts added into the layout.

Oracle MAD

These filters can be added to another components on the data tab into the properties of the component

Plugin SDK

This new feature allow you to create your custom plugins without the need to have a complete environment. It comes with a test server based on node.js that will help you to develop your own plugins. You can download and follow the guide to install it by clicking on Create plugin and the download bimad.zip. Once coded you can deploy them as in the previous version, pasting them into the /plugins folder of the bipublisher

Oracle MAD

Default settings for the soft delete option in Oracle BI APPS

.

During one of our last OBI Apps Finance implementation and customization projects we were confronted with an uncommon issue. Even though we had run many validation processes and had acquired the agreed approvals within the development and UAT environments, some metric issues appeared after going live with the Production environment.

The first issue we noticed was related to an Accounts Payable (AP) report, which contained the number of in-validated invoices; the metric shown within the Oracle BI report was greater that the real number on in-validated invoices found in the Oracle E-Business Suite database (EBS). Finally AP users confirmed that they do not always follow the EBS best practices and some in-validated invoices were manually deleted instead of setting them to the “cancel” status prior to the deletion.

The second issue appeared in the Accounts Receivable (AR) business area. In this case we identified the mismatching within the aging snapshot table; this table holds the outstanding AR balance information and it was showing outstanding amounts where the balance should be 0. In this case no manual deletions were done, AR users modified payment terms from EBS front-end and when modifying the payment term on the invoice, a new record was created and the old payment schedule record deleted.

We could see that both issues are related to the deletion of records within the EBS source system. In an out-of-the-box Oracle BI APPS implementation, records that are deleted in the source system are not removed from the Oracle Business Analytics Data Warehouse. In case you want to flag these records as deleted (soft delete) in the Data Warehouse, you must enable the related primary extract and delete mappings (the soft delete feature is disabled by default).

Description of the Primary Extract and Delete Informatica PowerCenter mappings:

The following graph describes how the primary extract and delete Informatica PowerCenter mappings interact with the database tables:

graph1

Primary and Delete Informatica PowerCenter mappings description:

  • The “_Primary” mappings perform a full extract of the primary keys from the EBS source system and load the result into the primary extract (_F_PE) table.
  • The “_IdentifyDelete” mappings identify deleted records in the source by doing brute force comparison between primary extract table (_F_PE) and the target table (_XACT_F) and load the results into a staging table (_XACT_F_DEL).
  • The “_SoftDelete” mappings update the delete flag column with a value 'Y' on the  target table (_XACT_F) for all the records that were  identified as 'deleted', driving from the staging area table (_F_DEL).

How to enable Primary Extract and Delete sessions on Tasks:

In order to enable the Primary Extract and Delete Informatica PowerCenter mappings you will have to apply changes to the Data Warehouse Administration Console (DAC) application.

Here is the list of steps required:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
  2. Display the Tasks tab.
  3. Query for all tasks containing the string 'Delete' or 'Primary'.

Accounts Payable (OBA APPS 7.9.6.4)

-        SDE_ORA_APTransactionFact_ExpenseDistribution_Primary

-        SDE_ORA_APTransactionFact_LiabilityDistribution_Primary

-        SDE_ORA_APTransactionFact_PaymentSchedule_Primary

-        SDE_ORA_APTransactionFact_Payment_Primary

-        SIL_APTransactionFact_IdentifyDelete

-        SIL_APTransactionFact_SoftDelete

Accounts Receivable (OBA APPS 7.9.6.4)

-        SDE_ORA_ARTransactionFact_ARSchedules_Primary

-        SDE_ORA_ARTransactionFact_Adjustments_Primary

-        SDE_ORA_ARTransactionFact_CreditMemoApplication_Primary

-        SDE_ORA_ARTransactionFact_ReceivableApplication_Primary

-        SIL_ARTransactionFact_IdentifyDelete

-        SIL_ARTransactionFact_SoftDelete.

4. Deselect the Inactive check boxes for those tasks.

Find below a screenshot which shows the AP primary extract tasks, being “Inactive” by default:

graph2

5.Reassemble your subject areas and rebuild your execution plans.

Additional considerations

In order to have a smooth implementation of the soft delete option, you may have to perform some additional tasks.

1. Review the “Sql Query” attribute in the Source Qualifier transformation within the SDE Primary mappings:

  • Make sure that the WHERE clause in the SQL query is correctly specified
  • Double check that the SQL query matches with the customizations you may have already applied to your Oracle BI APPS environment

2. Within DAC, select the “_Primary” tasks and verify that the “Truncate Always” checkbox for the target primary extract table (_F_PE) is selected just for the first task in the execution plan.

Example for the AP business area:

  • SDE_ORA_APTransactionFact_ExpenseDistribution_Primary
    “Truncate Always” checkbox checked
  • SDE_ORA_APTransactionFact_LiabilityDistribution_Primary
    “Truncate Always” checkbox NOT checked
  • SDE_ORA_APTransactionFact_PaymentSchedule_Primary
    “Truncate Always” checkbox NOT checked
  • SDE_ORA_APTransactionFact_Payment_Primary
    “Truncate Always” checkbox NOT checked

The screenshots below show how to check and un-check the “Truncate Always” option for the target table within the AP business area:

graph3

graph4

3. Impact on aging tables:

  • If the soft delete option has been enabled after the system was already in PRODCUTION, you may have some wrong information in the aging tables (it happened to us with the Accounts Receivables business area) so you will have to build a SQL DDL that deletes the related records within the aging table.
  • Double check that the “Sql Query” attribute in the Source Qualifier transformation within the PLP mappings that load the aging tables contains a filter for the soft delete DELETE_FLG field (DELETE_FLG='N')

4. Review your RPD:

Oracle BI APPS repository by default comes with filters on the soft delete DELETE_FLG field (DELETE_FLG='N') however it would be good to double check.

You may also want to take advantage of this soft delete DELETE_FLG field and build reports in order to analyse them.

Find below a screenshot showing the filter on the DELETE_FLG field within the repository:

graph5

Conclusion

The soft delete feature in Oracle BI APPS is disabled by default, so the best approach would be to analyse and agree with the business users if they will require the soft delete feature to be enabled and in case they need it, apply the required changes as soon as possible (before the deployment to the PRODUCTION environment) in order to minimise the impact.

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav