Monthly Archives: March 2016

Oracle BI Cloud Service (BICS) Data Loader: Under the Hood

March 23, 2016

Author: Doug Ross, Performance Architects

Oracle’s Business Intelligence Cloud Service (BICS) offers several different ways to load data into the complementary cloud database schema that comes with a subscription to BICS. The most basic data loading utility is the Data Loader which is accessible off the home screen after logging into BICS. This post describes the data load process and then digs into what goes on behind the scenes when data is loaded. For anyone responsible for managing the database in their own BICS environment, this should be useful information.

Data Loader Basics

The Data Loader allows for uploading data from either a cut-and-paste operation or uploading CSV, TXT, ZIP, or Microsoft Excel files. ZIP files can only contain TXT or CSC data files and the first file in the ZIP archive will be loaded by the Data Loader.

Accessing the Data Loader begins with clicking on the “Load” button on the lower left of the BICS home screen.

DR

The Data Loader Home Page

The BICS Data Loader home page shows a summary of all data load activities including links to show the actual data records loaded and any records that were not loaded due to errors.

DR 2
Date:

  • Date: The date / timestamp of when the load was performed
  • User: The application user who performed the data load
  • Destination Table: The name of the table in the associated Oracle Cloud database into which the load was performed
  • Inserted Records: The count of records which were successfully inserted into the target table
  • Updated Records: The count of records which were successfully updated in the target table
  • Not Loaded Records: The count of records which had errors while performing the load; this includes records which had errors while inserting and updating
  • Processed Records: The count number of records processed; this includes inserted, updated and not loaded records

Clicking on the “Inserted Records” value for a load job will show a detail screen listing all of the data that was successfully loaded into the database. A dropdown list will then shows updated or error records.

DR 3

After viewing this detailed data summary, you may ask a few important questions, especially if you are responsible for maintaining the cloud database, such as:

  • Where is the detailed information about the data loads stored?
  • Is it necessary to keep duplicate copies of every record loaded by the data load in the database tables?
  • How do we go about purging the data load information when it is no longer needed?

Under The Covers

By using the “Object Browser” in the Oracle Application Express (APEX) environment associated with the BICS cloud database, we see three tables named “EBA_ADL_LOADS,” “EBA_ADL_LOAD_MAPPINGS,” and “EBA_ADL_LOAD_RECORDS.”

DR 4

Clicking on “EBA_ADL_LOADS” and then selecting the “Model” tab from the view shows the relationship between the three tables.  The “EBA_ADL_LOADS” table is the master table, while the other two tables contain the details about each load.

DR 5

“EBA_ADL_LOADS” contains one row for each attempted data load and stores who performed the load, when the load was performed, and what table was loaded.

DR 6

“EBA_ADL_LOAD_MAPPINGS” contains one row for every column loaded in each instance of a data load.  The table has a foreign key reference between “EBA_DATA_LOAD_ID” and the “EBA_ADL_LOADS.ID” column.

DR 7

Most of the columns are self-explanatory except for these two:

“UPDATE_ROW_ON_MATCH” is a flag that determines whether to use the column as a key to identify rows that will be updated versus inserted during a load.

“DATA_TRANSFORMATION” is a numeric code that relates to which optional basic transformation rule was selected for the column during the data load process.

  • 1 = Uppercase
  • 2 = Lowercase
  • 3 = Trim Spaces
  • 4 = Format as number

“EBA_ADL_LOAD_RECORDS” contains one row per row inserted by each data load.

Let’s repeat that statement just to be clear: “EBA_ADL_LOAD_RECORDS” contains one row per row inserted by each data load.

This might be a cause for concern over time as data is loaded into the Oracle Database Cloud instance and there may be limitations on available storage space within that instance.

The structure of “EBA_ADL_LOAD_RECORDS” includes a primary key ID column; an “ACTION” column showing whether the row was inserted or updated; an “ERROR” column containing the error message that may have occurred when inserting the row; and a “SEQ_ID” containing the sequential row number from the data loaded.  The table also maintains a foreign key reference between “EBA_DATA_LOAD_ID” and the “EBA_ADL_LOADS.ID” column.

In addition, there are 50 columns prefixed with “C00%” that map to the individual columns in the “EBA_ADL_LOAD_MAPPINGS” columns.  This is why a data load using the BICS Data Loader tool is limited to 50 columns, as this is a limitation imposed by the underlying tables used to track the data loads.

DR 8

DR 9

The combination of the three “EBA_ADL_%” tables forms the metadata that controls the BICS data load process.

Do we really want to keep duplicate copies of every record loaded by the BICS Data Loader in our database?

In most cases, the answer would presumably be “No.”  The Oracle Cloud Database instance that is provided with BICS is limited to 50 GB of space.  Retaining duplicate copies of every row from every data load would reduce the actual available space for analysis by half or more.

It is important to note that the “EBA_ADL_%” tables are only utilized when the data is loaded via the BICS loader.  Loading data via other methods like Oracle APEX Data Workshop, Oracle SQL Developer carts, or REST API services will not cause information to be loaded into those tables.

How do we clean up the metadata?

There does not appear to be any purge utility for the data load tables included within the BICS environment. Hopefully Oracle will add this functionality at a later point.

For now, the only way to purge the data appears to be via direct “DELETE” or “TRUNCATE” statements executed within the Oracle Database Cloud APEX environment.

For a complete purge of the data load information, the following SQL commands can be executed within the APEX environment:

DELETE FROM EBA_ADL_LOAD_RECORDS;

DELETE FROM EBA_ADL_LOAD_MAPPINGS;

DELETE FROM EBA_ADL_LOADS;

To delete only the data related to a specific load, first identify the ID column from the “EBA_ADL_LOADS” table of the load to be purged.

SELECT * FROM EBA_ADL_LOADS;

DELETE FROM EBA_ADL_LOAD_RECORDS WHERE EBA_ADL_LOAD_ID = value;

DELETE FROM EBA_ADL_LOAD_MAPPINGS WHERE EBA_ADL_LOAD_ID = value;

DELETE FROM EBA_ADL_LOADS WHERE ID = value;

COMMIT; — If necessary

It is also possible to create a PL/SQL package to purge the tables based on more complex criteria like dates or target table names.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

A Quick Look at the Oracle Business Intelligence Cloud Service (BICS) Architecture

March 16, 2016

Author: John McGale, Performance Architects

The Oracle Business Intelligence Cloud Service (BICS) architecture is highly integrated into Oracle’s cloud ecosystem. Your BICS application is a “tenant” called a “Service Instance” that is sharing a slice of a larger multi-tenant instance of Oracle Business Intelligence Enterprise Edition (OBIEE) called a “BI POD”. Each POD hosts multiple service instances.

JM 2
The BI “POD”

Each BICS Service Instance is its own “application” with its own Web Catalog Slice, RPD, Security Store Stripe and Identity Domain. Each Service Instance maintains a dedicated BI Server process. All other processes related to your service are shared with other tenants on the POD.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Why are Cloud Services Popular?

March 9, 2016

Author: John McGale, Performance Architects                                                  

There is a great deal of information to understand if you are just beginning your journey to cloud-based software solutions. One of the first things to understand is that there are varying levels of services within the “cloud.” Cloud deployment strategies are often categorized either as:

  • Infrastructure-as-a-Service (IaaS)
  • Platform as-a-Service (PaaS)
  • Software-as-a-Service (SaaS)

JM 1
Breakdown of Cloud Services

Oracle Business Intelligence Cloud Service (BICS) is truly software-as-a-service (SaaS) and is comprised of infrastructure-as-a-service (IaaS) and platform-as-a-service (PaaS) components that Oracle manages as part of the entire infrastructure. The key takeaway here is that these differing levels of service can be used separately, or in any number of combinations and levels of sophistication that culminate at the SaaS tier of service.

Demand Drivers for Cloud Services

Before we discuss BICS, I would like to review, especially for those new to cloud services, the reasons why there is such a large drive to the cloud in the marketplace. Is cloud better, or is it just different than traditional on-site computing?

In a way, “cloud” harkens back to the days of large mainframe processing, where users shared time on a terminal and shared a set of applications that were part of a central IT service. But the levels of sophistication and personalization really give the user a sense that they are in control of their slice of that processing power.

I believe there are several key factors involved in this migration to the cloud at an organizational level, the first of which is application deployment speed and agility that the cloud provides. Cloud software offers the immediate availability of an environment without any having to wait for environment procurement, installation, and application deployment. This removes not only the up-front installation time, but also future downtime due to maintenance and upgrades.

Second is the concept of “elasticity.” Cloud software can scale on-demand based on workload at any given time.

Specialization is another key factor. Cloud solution providers are focused on the delivery and maintenance of a product or set of products within a key business area. Organizations can rely on industry experts to provide a higher-value deliverable in a shorter amount of time.

The idea of getting enterprise processing power at a lower total cost of ownership (TCO) is very appealing as well. Subscription-based pricing and the ability to pick applications (the Apple model) allows organizations to garner substantial savings over traditional IT contracts. This also falls in line with the expectations of the next generation of the business workforce: that IT should be something that you can buy in a modular fashion without committing to features and functions that are not part of what you are doing.

Last is the notion of “availability.” Availability here means application “uptime” from the perspective of distributing information on a massive scale to anyone having an internet connection and a web browser. Once again, the next generation of the workforce expects that applications should just work and be available. When Google, Twitter, or Facebook experience latency or even downtime, this causes major distress in the new and upcoming end user communities.

Common Cloud-Related Concerns

Long before the marketing term “cloud” existed, the IT community would regard this practice as “remote hosting”. The word “remote” has an entirely different connotation, and is a more accurate term in describing where your personal and corporate information is being transmitted and stored.

Data security was – and is – the single largest concern with organizations considering embracing the cloud in some capacity, especially considering the frequent news headlines of large corporations getting breached. Security concerns include confidentiality, integrity, availability, transmission, storage, and retrieval of information. The second largest concern after security is on-site integration, where a quick movement to the cloud is not feasible. Often, this is due to the incompatibility of large enterprise systems with proprietary cloud solutions.

General lack of control of data is another concern. This can be mitigated to the degree that organizations maintain data governance and controls.

Lastly, the maturity of cloud vendors is another consideration. Many solutions are not robust enough to support an entire enterprise. Many are departmental or “point” solutions. However this will not deter department within organizations from embracing cloud solutions. In fact, we usually see the opposite.

Corporate versus Departmental Concerns

A typical complaint we hear from line of business users is that large IT business intelligence (BI) is not meeting the agility needs of the line of business. Some of these complaints include: the need for self-service; BI is inflexible and slow; there is too much reliance on IT for new content; the information doesn’t support business decisions; and that enterprise tools are overly complex.

From the IT team’s perspective, software licenses and infrastructure need to be managed in relation to enterprise IT standards. Often, the parameters that central IT is forced to work within are due to IT budget constraints and the constant need to consolidate infrastructure onto one-size-fits-all enterprise system standards.

Cloud systems bring agility and control through rapid deployment. Allowing business flexibility while maintaining compliance, IT can maintain a curated metadata layer and a single version of the truth; leverage existing IT skills, and mitigate security concerns through integrating on-site and cloud data and information. The lines of business can get the agility they need to run the business through self-service, allowing rapid analytics prototyping and collaboration and sharing between IT and lines of business.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Join us at Oracle Higher Education User Group (HEUG) Alliance 2016!

March 3, 2016

Author: Melanie Mathews, Performance Architects

We are just days away from the Oracle Higher Education User Group (HEUG) Alliance 2016 conference in Seattle! Alliance is the largest meeting of Higher Education, Public Sector, and Federal users of Oracle applications in the world. If you are attending Alliance, click here to schedule a 15-minute meeting with one of Performance Architects’ team members and we will be in touch to put some time on our calendars!

We will be exhibiting at Booth #429 and we also are presenting with our clients:

  • Tuesday, March 8, 2016, 2:15 PM – 2:45 PM: Virginia Commonwealth University (VCU) Planning & Reporting in the Cloud: An Oracle Planning and Budgeting Cloud Service (PBCS) Case Study
  • Tuesday, March 8, 2016, 1:00 PM – 2:00 PM: Clemson University: A Year down the Road with Oracle Hyperion Planning and Public Sector Planning and Budgeting (PSPB)

We are running special demos at our booth of our QuickStart offerings for Oracle BI (OBI 12c or BI Cloud Service / BICS) and EPM (Hyperion or Oracle Planning and Budgeting Cloud Service / PBCS) for Higher Education. If you’re unable to join us at the show and want to learn more about these QuickStart offerings, register for our Performance Architects Oracle BI Cloud Service (BICS) QuickStart for Higher Education webinar on Wednesday, March 16th, 3:00 PM EST and/or our Performance Architects Oracle Planning and Budgeting Cloud Service (PBCS) QuickStart for Higher Education webinar on Tuesday, March 22nd, 3:00 PM EST.

Follow us on Twitter (@PerfArchitects), Facebook (https://www.facebook.com/PerformanceArchitects/), or LinkedIn (https://www.linkedin.com/company/performance-architects-inc-) to keep up with us during the event, and stay tuned for our post-show blog with updates and news from the conference.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.