OneStream - formats to load data

 We can load data using below methods into OneStream

1. Fixed Format
2. Delimited format
3. Connectors
4. Data Management

Fixed format, where you can find fixed spaces in the file which we will prepare or get from Sources.

Delimited Format, where you can find delimiters in the file, which we can the settings

Connectors, where you can write business rule to connect to the source system, using connection string details.

Data Management, where you can import, validate, load, etc to load data into OneStream.

Thanks

Mady

Oracle EPM Planning (PBCS): Unable to download the file: details: Invalid file : FCC_2385.log

When trying load data from FCC to PBCS, I found below error in the logs, states that Unable to download the file along with Invalid log file.

Generally, This error occurred whenever we trying to pull data with no filters in Data load rule, so that lot of members/data will be involved to data load process from Source to Target.

 Planning log:

2024-07-31 09:34:05,019 ERROR [AIF]: Unable to download the file: {"details":"Invalid file : SFCC_2385.log.","status":1,"links":[{"href":"https://epm-test-axxxxx.epm.us.oraclecloud.com:443/interop/rest/11.1.2.3.600/applicationsnapshots/SFCC_2385.log/contents","action":"GET","rel":"self","data":null}]}

2024-07-31 09:34:05,023 INFO  [AIF]: EssbaseService.extractData - END (false)

2024-07-31 09:34:05,029 FATAL [AIF]: Error in CommData.extractDataFromSource

Traceback (most recent call last):

  File "<string>", line 2862, in extractDataFromSource

RuntimeError: false

URL in the above log showing invalid file, so when I verified in the FCC log, it is sating as below

FCC log:

2024-08-01 03:00:16,759 ERROR [AIF]: Cannot perform cube view operation. Essbase Error(1024015): Request [MdxReport] from user [epm_default_xxx_admin] was terminated by an admin user

2024-08-01 03:00:16,801 INFO  [AIF]: EssbaseService.extractData - END (false)

2024-08-01 03:00:16,801 ERROR [AIF]: false

2024-08-01 03:00:16,814 INFO  [AIF]: FDMEE Process End, Process ID: 2,385

Solution that Oracle suggested:

1. In the Data load rule, create filters for each dimensions to export less number of records.
2. Create multiple Data Load rules to export the data to the target application by applying different set of filters as needed.

Thanks,

Mady

EPM Planning - PBCS - How to create source application to FCC from Planning

 Creating source application is small part only, bit tricky I found, may be it is easy for others. It may be useful at least for beginners. Sometimes we forget things with on going activities, so you can found steps here..

Go to Data Exchange >> Actions >> Applications

Click on Add

Enter FCC URL, with out HyperionPlanning in the URL, enter username, password then click Refresh button which highlighted in the below screen shot.

Select your application in the next popup screen as shown below, then select Plan Types and Prefix if required.

Click OK, it will be popup in right side as Application Registered successfully.

Thanks,

Mady

OIC - How to schedule an Integration

 Scheduling an Integration in OIC also bit tricky, if you are working as a beginner. There are few limitations, as per Oracle mentioned, we cannot schedule integration within 10 minutes if you are using 

Click on 3 lines once you activated the Integration.

In the actions list, you can schedule, click on schedule.

Click on Edit button on the Top-right corner

You can select Simple or iCal option to schedule as per you requirement.

In Simple selection, you can schedule the integration to execute in frequency of below mentioned and showed in screen shot.

• Hours and Minutes
• Days
• Weeks
• Months

In Case of, you want to schedule other than the default options in Simple, you can select iCal option, where you can update Expression to schedule the integration. 

For Example: To schedule integration to execute in weekdays, 2 times a day. you can use below code.

FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR;BYHOUR=2,14;BYMINUTE=30;

Usage commands:

INTERVAL

BYSECOND

BYMINUTE

BYHOUR

BYDAY

BYMONTHDAY

BYYEARDAY

BYWEEKNO

BYMONTH

BYSETPOS

WKST

enddate = date

enddate =/ date-time ;An UTC value

byseclist = seconds / ( seconds *(“,” seconds) )

seconds = 1DIGIT / 2DIGIT ;0 to 59

byminlist = minutes / ( minutes *(“,” minutes) )

minutes = 1DIGIT / 2DIGIT ;0 to 59

byhrlist = hour / ( hour *(“,” hour) )

hour = 1DIGIT / 2DIGIT ;0 to 23

bywdaylist = weekdaynum / ( weekdaynum *(“,” weekdaynum) )

weekdaynum = [([plus] ordwk / minus ordwk)] weekday

plus = “+”

minus = “-”

ordwk = 1DIGIT / 2DIGIT ;1 to 53

weekday = “SU” / “MO” / “TU” / “WE” / “TH” / “FR” / “SA”

Reference: https://docs.oracle.com/en/cloud/paas/integration-cloud/integrations-user/define-integration-schedule.html

Thanks,

Mady

Oracle EPM Planning/PBCS - Data not showing up in Data Management Workbench

 In Data Management, workbench if it shows, Gold fish, we will be happy as much as we earn Real GOLD or Gold Fish. Why because, Data Management will show this gold fish after lot of steps only, if you do correctly. Otherwise, even you can't find out the issue at which step it is failing, not every time, but some times. 

This post is about simple step only but, we must know that we need to enable these option to see data, otherwise even though Gold fish is showing up, you can't see data in workbench, you don't even know about what happening, whether it is working or not.

If you are extracting data from Planning, and data not showing up in workbench, then check below steps to enable or see the data.

Navigate >> Click on Data Exchange in Application section >> Actions >> Applications >> Click ... 3 dots(last column, under actions) in particular application then Select Application Details >> Select options >> Select Workflow Mode as full, then only you can see data in Data Management Workbench.

Thanks

Mady

Oracle EPM Planning/PBCS - Error Resolutions in Data Management

 While we work on Data Management, more often we will face these issues:

As these Message Number and Contents are self-explainer, as I found some information, I am posting here, 2 issues solution.

Error 3303: This issue is about member not found, which means member itself is missing in the database. 

Solution: So, We can add member in dimension, and try to load again.

Error 3335: Mapped target dimension member does not exists (or) the same member name exists in more than one dimension. 

Solution: Check whether the mapping is defined properly, target value mentioned in Mapping should exist in Target Application. Also, check that the same member name does not exists in more than one dimension. 

We will add more....

Thanks,

Mady

Data Extract to File using Data Management - Data Exchange

As we know, Oracle updating cloud applications with latest updates regularly. In same way, Data Extract to file using Data Management also got updated. Here you can find new steps.

Go to Navigate >> Application tab >> Select Data Exchange

In Data Exchange tab, Click on Actions drop down and select Applications. you can refer below screen shot.

Click on + sign to create source or target applications

Select Data Export and select Data Export to File in the list below, as we are here exporting data to File.

Now, browse and select the file, in which format you want to export data. Like Account, Account alias, Customer, Customer Alias, ...... Data

Now, go to Data Management create Data load rule, after creating import format, location. Do update mappings as per your requirement, then you are ready to extract data from PBCS application to File.

Once Data load rule execution is completed, you can find the file in below location.

Navigate >> Application >> Overview >> Actions >> Inbox/Outbox Folder

you can download the file to local machine to verify data using excel.

Thanks,

Mady

PBCS - Application metadata build and data load automation

PBCS: 

Application metadata build and data load automation.

 We can do EPM automation using 2 methods as we explored:

 1. EPM Automate 

  - Need server to place files and for installing EPM Automate
  - Create automation script to execute the metadata and data load tasks
  - Schedule and execute tasks as per requirement.
  - We can send an email to send about status of the script.

     For example: We have created a script using EPM Automate, which will perform below tasks

    1. Login URL, Set log files to write script activity - Send email in case of login fails.
    2. Execute business rule to set variables
    3. Execute Data management rules to load metadata of 4 dimensions
    4. Refresh database
    5. Execute Data management rules to load data
    6. Execute Business Ruleset
    7. Upload log and err files to outbox folder
    8. Send email to receipts with attachments along with Timestamp

2. EPM Automate without Server

  - Instead of server, we can use OIC - Oracle Integration Cloud to move files to Cloud
  - We can create groovy script to run metadata, data load tasks, and execute using OIC
  - Schedule and execute OIC Integration as per requirement.
  - We can send an email to send about status of the Integration, 
    where we have advantage of to set Abort on error of each task if fails and to send an email.
For example: We have create few integrations, let me explain one of them.
  - To load Subledger Revenue Data into system:
  1. Subledger Revenue Data file will be pulled from ERP system, so we have create a integration to             pull data ERP 
     and place that in to our DM/inbox folder - Here we are not using any server, file directly place in             to EPM from ERP.
  2. We have created Data management load rule to load Subledger Revenue data
  3. Create a groovy business rule script to execute the Data Management rule with help of                             substitution variables, either for one month or multiple months.
  4. Create a Job to execute the same groovy script.
  5. Now in OIC, we have an Integration - to pull file from ERP to EPM and place it into inbox                    folder. To execute pre dataload business rule, Dataload, Post dataload rules. At each step, it will             wait until process completes, in case of failing any step it will abort the integration and                     send an email to receipts.

Thanks,

Mady

Oracle Data Management - Target Options

 We can create different types of target in the Data Exchange. 

EPM Local: To make current application as Target, we can use this method.

EPM Cloud: To connect to any other cloud application, use this method. Where we can update URL, username, password, domain etc. 

Data Source: Have lot of in built adaptors, to create as a Target.

Data Export: To export data from application, use this option.

Dimensions: To build dimensions, use this method, where system will give 6 different types of dimensions along with Custom option.

EPM Local:

EPM Cloud:

Data Export:

Data Source:

Dimensions:

Thanks,

Mady

Oracle Cloud EPM Planning - Load metadata using Data Management

 Loading metadata also one of the major task to automate the process.

Create Source application as File and Target application as Dimensions.

once Dimensions selected, we have to select application name and Prefix. Once you click on OK, system will create 6 types of EPM Dimension Target will get create automatically.

ACCOUNT, ENTITY, SMARTLIST, VERSION, SCENARIO, and CUSTOM

We have to use Custom as our Target to build dimensions other than mentioned above.

Import Format, used to build the dimension is:

We have selected only Member Name, Parent, Alias and Data Storage.

As we know, create location and period mappings as per the requirement. Create Data Rule, and update mappings accordingly. In case of any rows getting blanks in any column of the file, we can use below logic in mapping. 

#SQL

CASE

WHEN ICP IS NULL THEN ' '

ELSE ICP

END

Finally, loading metadata will be done with 3 golden fishes. :)

Thanks,

Mady

Oracle Integration Cloud - Business Rules in Planning Cloud

 If you want to automate business rules to execute in sequence or part of any process, you can use Oracle Integration Cloud.

For example: If you want to execute business rule to load data every month, based on the substitution variable and also wants to run calculation using another business rule. you can do all these steps using OIC, where you can pull substitution variable, execute business rules and good part is, if any step is failed, you will get notification with email, which is not available in general scheduling options.

I will try to document and update in next posts about OIC Automation, this is mainly about PBCS, FCCS.

Thanks,

Mady