Access contents of UCM file using BI Report
Access contents of UCM file using BI Report

 1,062 total views

In this article we will look into getting the contents of file on UCM using BI Report.

Normally the vendor input files are uploaded to UCM and then they would be picked up for processing by HDL or payroll flows to process the file and load data into fusion application.

Sample use case would be the payroll batch loader automation where in the vendor file is uploaded to UCM and then processed using Load batch from file and transfer batch flows. PBL is being decommissioned by 20D and hence we can use similar functionality for HDL transformation.

If the third party system is preparing the DAT file and putting in the zip file on UCM, then you can use the Initiate Data Loader flow to upload the file to HDL.

Now if there are multiple vendors and if the individual files are getting uploaded to UCM. And you want to consolidate all of them into a single file and initiate the process only once.. Can you do it? Or can the HDL formula read two input files?

The HDL formula flow (Load data from file) can take only one Content ID as input. So if there are multiple files, then you need to submit the process multiple times.

So, it boils down to getting a single file on the UCM which we can achieve using BI Report?? Do you believe it?? You can read the contents of the files in UCM and append them to make a single file and upload it back to UCM.

Note: Currently it is possible to read only the .TXT files from UCM in BI Report. The file extension should be .txt only. It cannot read the .csv, .dat, .zip etc. So you can request the vendor to send in .txt format only and mostly all vendors support multiple formats and it shouldn’t be an issue.

Now Let’s look into how to access files on UCM in BI Report.

Let me upload two text files on to UCM.

File1.txt
LABC2020/01/014712/12/31 UBICACION 1HCM001COMMONCOA
BBCD2020/01/014712/12/31 HCM001COMMONPUESTO 1FULL_TIMEAREG

File2.txt
Regular EarningsUS Legislative Data GroupE142019/06/292019/07/12
Overtime EarningsUS Legislative Data GroupE122019/06/292019/07/12
Files uploaded to UCM and Content IDs are generated fusion hcm
Files uploaded to UCM and Content IDs are generated

Now we will access these files using BI Report and combine them into single file and upload it back to UCM. And then it can be used by any other flows which is beyond the scope of this article.

Now let’s navigate to BI Report and create the datasets using Content Server.

Pre-requisite to add the Content Server Data Set is there is should be another SQL dataset which should have the document id fetched. And then the Content Server data set will link to the SQL data set and get the document content for the document id set by the other data set.

Data Model Snapshot:

Snapshot of Data Model with SQL Data Set and Content Server Data Set Linked to it
Snapshot of Data Model with SQL Data Set and Content Server Data Set Linked to it

G_1 SQL Query and Snapshots:

select 'Dummy' Key, a.* from revisions a where ddocname in (:CONTENT_IDS)
G_1 Data Set
G_1 Data Set

G_2 UCM Snapshots:

Here the DID from G_1 should be selected in the Document ID. Which will be used as key to get the document text from UCM.

We will add a parameter to get the multiple content ids:

Now, we will go to Data page and test it for a couple of Content IDs: UCMFA02586299,UCMFA02586300

Data Screen showing up the contents of File1 and File2
Data Screen showing up the contents of File1 and File2

Now a simple etext template can be created to append all DOCUMENT fields from G_2 level.

etext template to concatenate multiple files
etext template to concatenate multiple files

Now, if we run the report for the Content IDs, we get the output like below:

Concatenated Output on Report
Concatenated Output on Report

Now, as we got the consolidated output, next step is to write the bursting query to upload the file to UCM

Setting up bursting to upload the concatenated file to UCM
Setting up bursting to upload the concatenated file to UCM

Bursting Query:

SELECT
'Dummy' as KEY -- Split Key from data model SQL
,'Test' template -- Report template name
,'en-US' locale
,'TEXT' output_format -- Output type
,'WCC' del_channel -- To send to content server
,'FA_UCM_PROVISIONED' parameter1 -- server name (BI Administration > Manage BI Publisher > Delivery > Content Server)
,'FAFusionImportExport' parameter2 -- security group name
,'HCM_IMPL' parameter3 -- Author of the file
,'Consolidated File1&2' parameter5 -- Title
,'File3' parameter6 -- Output file name
,'' parameter7 -- Comments (Optional)
,'' parameter8 -- Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one
,'false' parameter9 -- Custom metadata (true/false). Specify ‘false’
,'' parameter10 -- Folder Path
FROM dual

Once bursting query is setup, we need to enable the bursting option in the BI Report properties.

Enable bursting in BI Report properties
Enable bursting in BI Report properties

Then we can schedule this report with the input parameters to test this functionality.

Click on More > Schedule for BI Report

Schedule BI Report with content IDs

We need to schedule the report since bursting doesn’t work if we open the report. Only with schedule/through payroll flow, bursting will work.

Consolidated file is uploaded to UCM upon successful completion
Consolidated file is uploaded to UCM upon successful completion

Consolidated File Contents:

File Contents
File Contents

Finally we are done with the demonstration of the BI Report to get the UCM file contents and display on the output.

Hope this helps when you get similar requirement.

Catalog files for reference: Download Link

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has One Comment

  1. Sunil Chilu

    Thank you Sri, This is very interesting…

Leave a Reply