BI Cloud Connector – Custom Object Data Extraction

May 2, 2019 | 17 minute read
Ulrich Janke
Consulting Solution Architect SaaS A-Team
Text Size 100%:

Introduction

In various blog posts we discussed the usage of BI Cloud Connector (BICC) in Oracle SaaS. In our cloud applications for ERP, HCM, EX etc exist various options to store flexible data as we know. One option follows the concept of flexible fields as known as Flexfields. Those are either Key Flexfields (KFF), Descriptive Flexfields (DFF) or Extensible Flexfields (EFF). These data are stored in database tables where the structures are fixed (columns named SEGMENT, ATTRIBUTE etc) and the content can be flexibly defined. BICC will handle the extraction of these flexible values in the same way as all other data: the underlying database tables are exposed as VO’s (View Objects) and whenever a flexfield is part of that structure it can be found in the according view object depending on the requirement to be published or not.

There exists also another option for a flexible definition of data – mainly used in Oracle Engagement Cloud (CRM Sales, Service etc): Custom and Child Objects as defined in AppComposer. These underlying data structures are using more dynamic metadata and BICC provides an alternate approach to access these data for extractions on a more logical level. This article will explain how to register these objects in AppComposer and in BICC before you can extract them.

As a practical sample for the usage of a BICC based data extraction for custom objects I’m referring to a business case from one of my previous blogs. One standalone custom object will be created to represent the knowledge about any competitor products. Another object will be created as a child object to Accounts to carry the information about competitors installed base at this customer.

This article handles the end-to-end configuration from object creation in AppComposer till the data extraction in BICC.

Creating Custom Objects in CX

This section shows the configuration steps to create the two custom objects via AppComposer. Its usually the first step and required to add custom data to the Accounts standard object in CRM Sales. These data will be extracted via BICC as shown further down in this blog.

Standalone Custom Objects

In a sandbox we create first an independent custom object that holds the information about any competitor products we will reference later from the custom child object in Accounts. The details of fields are not too important to be handled here. We will collect just some basic information like a Product ID and a Product Name next to the competitor information. For the implementation of a high sophisticated custom model the fields for Competitor (ID, Name) should refer to the standard Competitor business object. For an easier handling and simplification, we’ve not implemented this link in the sample below as the main focus is on the custom data handling in BICC.

Once created we will use the new page to enter some core data about products from competitors. The new page icon will appear in the homepage of the user after activating the sandbox where this creation has been created in.

These are the custom master data we will refer later within our competitive information in Account object. Later in this blog we will find the same data being extracted via BICC into CSV files. The data represent our best knowledge about competitor’s products and is not restricted within one single competitor.

Once this common standalone custom object has been created we continue creating another object that will reside in the context of an account.

Child Objects

This other custom object will become a Child Object under the standard CRM Sales object Account. It will be created with the fields as listed in screenshot below. For a high sophisticated object design it makes sense to link the product names to those objects as being created under Competitor Products and to link the competitor to the standard object Competitor. Also, here I did not implement the cross referencing between these entities as it doesn’t make a difference for the data extraction via BICC later.

The screen shot below shows the icon for the custom page – the location where sales and service representatives may enter their knowledge about competitive products being (co-)installed at their own customer sites. The custom page is accessible under the Edit Account functionality.

On the screenshot below, we can see how these detail data entry for competitive information would look like after entering them.

Screenshot below shows the data list as being entered for a dedicated Account and later in this article we will see these data extracted via BICC.

Important: Once the custom object registration has been finished the sandbox must be published before we can do the next step and register Custom Subject Areas. These custom objects won’t be visible in Custom Subject Area unless published.

Creation and Publishing of Custom Subject Areas

The previous section handled the general creation of the custom objects in App Composer and the data entry. In the next step before these objects become accessible within BICC we have to register them as Custom Subject Areas object by object.

Standalone Objects

Standalone objects will appear directly in the list of choices when creating a new Custom Subject Area. In a sequence of steps, the fields and any possible aggregations can be enabled and entered, as shown in screenshot below.

Before submitting the creation information of a new Custom Subject Area, the summary page will give a final view on the new object as shown in screenshot below. Press the Submit button in order to get this new Customer Subject Area registered in BI.

As shown in the screenshot below the process for Custom Subject Area registration becomes initiated and once the initiation has been done the further registration runs as an asynchronous batch job.

Child Objects

As a next step we will add the Custom Object for the Installed Competitor Products as a new Custom Subject Area. As we remember that object was created as a Child Object of Accounts.

As shown in screenshot below the difference to a standalone custom object is that we have to choose the parent object first – here the object Account. Then we have to add a Child Object from the list of registered objects as shown in screenshot.

From that stage onward, the list of activities looks familiar to those of a standalone object.

The fields from child object must be chosen to be embedded into the subject area as shown on the two screenshots below.

Another standard task is the security setup to allow or restrict the access to this new custom subject area. As you can see in screenshot below the default goes the fact that Everyone is being able to read these objects. In case this must be restricted there is an option to revoke access from public and to add dedicated users with reading permissions.

Before submitting the new subject area, the summary screen as shown below shows the relationship of new object to the standard object Account.

Again, and similar to the previous submitting of a custom object, the AppComposer handler for Custom Subject Area will compile all information from its dynamic data structures and prepare the creation of the according Custom Subject Area.

Once the creation of these both new Custom Subject Area was successful the Status column should show OK and the Active Flag column must be set to Yes for each of the Custom Subject Areas. This is a pre-requirement to access these objects from BICC later. Screenshot below shows the status as it has to be before we continue to register the Custom Data Sources in BICC.

Now we are ready to use our new Custom Objects in BICC as explained in the next section.

Using Custom Data Sources in BICC

Registration of Custom Offerings and Data Sources in BICC

These new custom objects must be registered as Custom Data Sources in BICC before we will be able to initiate an extraction run on them. In the beginning we have to be clear about the idea to add these new custom data stores to existing standard offerings. Alternately we may prefer to create a Custom Offering if not existing yet. This has the significant benefit that we can use this custom offering as a container for all sorts of custom objects and can keep them separated from the other data sources in the standard offerings.

This task starts opening the page for management of offerings and data stores as it can be found in the menu tab as shown below.

The screenshots below and the following explanations will highlight how to create a custom offering containing custom data sources. Once the page Manage Offerings and Data Sources has been opened choose the menu item Actions -> Create Offering.

 

Enter some useful names for this new custom offering as shown below.

As a next step register the custom data sources in BICC. Most tricky part might be to determine the internal Name of this custom subject area. As the screenshot below shows, the Custom Object Competitor Products becomes an custom subject area with the Data Store Key named CrmAnalyticsAM.ExtensibilityAM.CompetitorProducts_c. We have to type exactly this name in the field Data Store Key to make it accessible from BICC. At this point it makes also sense to assign it to our freshly created custom offering for an easier accessibility.

In case we’re struggling identifying the Data Store Key name please have a look at the bottom of this blog. In chapter Appendix: Determining object names for Custom Subject Areas I’m providing some hints how to determine the name.

In the second part of this creation page we are able to choose the columns to be included in this new custom data store and also identify the primary key for this object.

As a final step of this Data Store creation task we might want to check the results. This can be done by hitting the bottom Test Data Store as shown below. The test will run a data extraction across a few limited records and provide a zipped extraction file. The screenshot below shows the result as being opened as uncompressed CSV file in Excel. Once this test succeeded we can be sure the new custom data source works as expected.

We have to redo the same step again for the other custom object we’ve created. As we know this specific object for Installed Competitor Products is depending on the standard object Account and used as a child object there. For the naming convention we see no difference between this child object and a standalone custom object. This specific object name as shown below is CrmAnalyticsAM.ExtensibilityAM.InstalledCompetitorProducts_c.

Once finished the registered custom data stores will appear in our custom offering as shown below and we are ready to run our extracts including these custom objects.

Running a BICC extraction for Custom Data Sources

Finally, we can run extractions purely on or including our custom object as shown below.

Sample below shows the configuration of the extraction using only the both custom data sources.

The output will be stored in UCM in our sample as shown below.

Once started with the execution mode Immediately it can take minutes to hours for completion. The differences will be made by the amount of data being extracted. In sample below with only a few records from our previously created test data the request could be completed within minutes.

The results of this extraction have been stored in UCM as configured. Screenshot below shows the extracted data in single files for each custom object.

As documented in one of our previous BICC articles these files can be downloaded by using a script provided in that blog.

The data from this extraction run will look like shown in screenshot above and are the same data as being entered earlier in this article.

Appendix: Determining object names for Custom Subject Areas

It might be sometimes tricky and could look complicated to provide the correct name for the Custom Subject Area we’ve created. On other hand its essential to enter the exact name in BICC. Otherwise these objects won’t be found.

The description below is a quick guide how to obtain these values by using our standard BI utilities in SaaS. It may be that the method for name determination might improve in future, but for the time being this solution can be seen as a little workaround.

The starting point is the Reports and Analysis menu item reachable via the Tools menu.

In the main page for Reports and Analytics we must use the button Browse Catalog to find our custom objects.

The method here is to create an Analysis using the Custom Subject Areas without the necessity to save this Analysis for further usage. When rendering some test data in our design page this activity will be logged. In the log files we will find the internal names of these Custom Subject Areas.

After opening the Browse Catalog link you have to click the New menu item and there to activate the Analysis menu.

In screenshot below, we have added the custom subject area for Competitor Products and opened the dialog box to add more areas like Installed Competitor Products. It is important to remember that the Custom Subject Area start with the Name Custom: in the list of values.

In the design page under menu tab Criteria we create an analysis using our custom subject areas and select a few columns for reporting as shown below.

Once done we click on the Results tab and will see some test data. At that moment or BI runtime components have accessed these custom subject areas by their internal names and those names can be found in runtime log files.

For every custom subject area, we perform this test design and obtaining of test data. After having it done on custom subject area Competitor Products as shown in screenshot above, we must redo these same activities again for the other custom subject area called Installed Competitor Products. The two screenshots below show these tasks for this additional custom subject area.

Under the main menu item Administration in BI we will find the log files as being created when accessing the test data. To access this information, we have to open the menu item Manage Session under Session Management.

In the screenshot below, we can see that the session has created some log information. The link View Log can be used to open the log information in a text window.

After opening the log information in a text window, you can find the physical Custom Subject Area name inside those logging as shown in both screenshots below. These are the values to be used in BICC later when creating the custom data stores.

Conclusion

In Engagement Cloud we have with AppComposer a very powerful feature to create custom objects. This blog was intended to show the end-to-end flow from creating custom objects till extraction of those data in BI Cloud Connector. These features are available right now in every actual SaaS Engagement Cloud and can be used in customer environments.

Ulrich Janke

Consulting Solution Architect SaaS A-Team


Previous Post

Extend HCM Cloud with workflow process in Oracle Integration Cloud

Mani Krishnan | 5 min read

Next Post


PCS to OIC Migration guidelines

Sushil Shukla | 5 min read