• Post category:BI Publisher
  • Post comments:1 Comment
  • Post last modified:June 23, 2020
  • Reading time:10 mins read
You are currently viewing Access contents of UCM file using BI Report
Access contents of UCM file using BI Report

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
image 2 1024x197 - Access contents of UCM file using BI Report
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:

image 3 1024x392 - Access contents of UCM file using BI Report
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)
image 4 - Access contents of UCM file using BI Report
G_1 Data Set

G_2 UCM Snapshots:

image 5 - Access contents of UCM file using BI Report

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:

image 7 - Access contents of UCM file using BI Report

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

image 6 1024x538 - Access contents of UCM file using BI Report
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.

image 8 - Access contents of UCM file using BI Report
etext template to concatenate multiple files

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

image 9 - Access contents of UCM file using BI 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

image 10 1024x463 - Access contents of UCM file using BI Report
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.

image 11 - Access contents of UCM file using BI Report
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

image 12 - Access contents of UCM file using 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.

image 13 1024x193 - Access contents of UCM file using BI Report
Consolidated file is uploaded to UCM upon successful completion

Consolidated File Contents:

image 14 - Access contents of UCM file using BI Report
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

  • In visible box by plugintheme