Oracle PBCS text file export from ASO plan type utilizing Data Management

While recently working on a project to migrate an on-premise Hyperion Planning application to Oracle Planning and Budgeting Cloud Service (PBCS) I ran into a requirement to export data from the application to a downstream data warehouse. On premise this is handled with a BSO export calculation script. One of the changes we implemented when moving the app to the cloud was to take advantage of the ASO reporting cube plan type to eliminate the need to run aggregations on the BSO plan type. This decision however caused a challenge for exporting the data.

When evaluating options we recognized that while we could use a business rule to export the data from the BSO plan type, not all the data we needed was available since we were not aggregating the BSO cube. In light of this we decided we would extract the data from the ASO reporting cube. The challenge with this approach however was that we could no longer use the business rule export since it is not supported for ASO plan type, furthermore the default export feature of PBCS was both slow and resulted in an output format the downstream system was not happy with.

Considering our options I came to look at Data Management (DM). For those of you not familiar with DM it is basically a simplified version of Financial Data Quality Management (FDMEE) that is included with PBCS. DM does provide a mechanism for exporting data from a PBCS plan type to a text file. While this process is documented relatively well, for BSO plan types, by Oracle and on other blogs, there are some unique steps required when pulling from an ASO plan type that are not covered well in the resources I looked at.

In this tutorial I am going to walk through the steps necessary to export ASO reporting cube data into a delimited text file.

Let's start by logging into PBCS and opening the data management workbench from Navigator.

Fig. 1


[Sidebar: Oracle is in the process of updating the DM interface in PBCS. Some of the steps I am going to discuss can be accomplished in the new interface, however some important ones cannot. For that reason and to reduce confusion I am going to base this tutorial on the existing legacy interface. Perhaps at some future date when all steps can be completed in the new interface I will update this post with new screenshots.]


The first thing we are going to do is go to the Setup tab and register a Target application.

Fig. 2

You will note there is a Source System option and it would be logical for you to assume that we would need to set up a source for our target. In this case however we are not going to register a source, instead we are going to register two target applications, one for the text file export and the other for the ASO plan type, I know that may seem a bit counterintuitive, but it's the way it has been designed and why we are having this tutorial.

Let's start with adding a target application for the text file we are going to output.

Below Target Application Summary, click the Add button.

Fig. 3

Then choose Local 
Fig. 4

Select 'Custom Application' for the Type

Fig. 5

Then give the application a name

Fig.6

The next step is to go to the Application Details and add dimensions to the Dimension Details tab. You will need to add the dimensions from your PBCS application that you want to include in the export file. Take care to properly define the Target Dimension Class and the Data Table Column Name.

Fig. 7

After you add your dimensions, be sure to hit save and then go to the Application Options tab. Select your column delimiter and enter a name for your output file. Be sure to hit save when you are done.

Fig. 8 

Congratulations, you've completed setting up the output file application, now let's set up the ASO plan type target application. Go back up to Target Application Summary and hit the Add button.

Fig. 9

Again, select Local

Fig. 10


This time we are going to select 'Essbase' and select our reporting cube from the drop down list. Note that if you have more than one ASO plan type you will see them listed here and need to select the one you want to export from. Once you have selected the correct values, click OK

Fig. 11


[Sidebar: Now you might be wondering why we selected Essbase instead of Planning for the application type. If you select Planning you will wind up working with the BSO plan types. This is well documented in other blogs. Because we want to use the ASO plan type, we need to select Essbase. I assume this is because behind the scenes the ASO plan type is in a separate Essbase application. While a BSO Essbase application allows multiple databases in a single application, ASO does not. Therefore, even though Oracle wraps PBCS around it to make it easy for customers, the underlying architecture is an individual Essbase application and much like the cheese, it stands alone; therefore we need to select Essbase as the application type.]


You'll see that your Dimension Details are automatically imported from the ASO outline. Take care to note that the Target Dimension Class and the Data Column Name values are set properly. You will see that they do not exactly match the target application we created earlier for the text file export. Unlike in the first target application Years and Scenario are generic classes with user defined data columns.


Fig. 12

Save your target application and then refresh members.

Fig. 13

Go to Process Details and ensure the member refresh was successful

Fig. 14

Congratulations, you have created the target application for your ASO plan type. We will use this application as the source for our custom text application we created.

This last step of creating the ASO target application is the one that I found to have the least documentation available. Now that we have overcome that hurdle the rest of the steps are pretty well documented by Oracle, nonetheless I will list them here for completeness.


The next step is to create an import format that will allow us to map the dimensions in the ASO target application to the dimensions in the text file export.

  1. Return to the Set up tab and go to Import Format and click the Add button.
  2. Give the import format a name.
  3. Click the magnifying glass next to *Source and select the ASO plan type target application we created in the previous step.
  4. Click the magnifying glass next to *Target and select the text file export target application we created in the first step.
  5. Click Save
  6. Select the correct dimensions from the drop down list in the Source column for each row to match it to the dimensions in the Target column.
    1. Note that Period, Year, and Scenario are not included.
  7. Click Save


Fig. 15

Now we need to create a Location.
  1. Click 'Location' below 'Integration Setup' on the Set up tab
  2. Click the Add button
  3. Give the location a name
  4. Click the magnifying glass next to *Import Format and select the import format we created in the previous step
    1. Note that the source and target are automatically populated in a way that makes sense this time (i.e. the ASO cube is shown as the source and the text output is shown as the target.)
  5. Click Save


Fig. 16


Next we have to define the Data Load Mapping
  1. Go to the Workflow tab and click on data load mapping
    1. Note that the 'Dimensions' drop down list is on 'Account'
  2. Select the 'Like' tab
  3. Click the Add button
  4. Enter an asterisk(*) for both Source and Target
  5. Enter a rule name
  6. Click Save
  7. Repeat steps 1-6 for all dimensions in the 'Dimensions' drop down, except for Data.

Fig. 17

We are now ready to set up a Data Load Rule to export our data

First we have to select our location
  1. Go to Workflow tab and click on 'Data Load Rule'
  2. Click on Location on the bottom of the screen
  3. Select a period and category that has the data you want to export and click OK


Fig. 18

Now we can create our rule
  1. Click Add
  2. Enter a name for the Data Load Rule
  3. Use the source options tab to create dimensional filters that will limit the data you export
  4. Click Save

Fig. 19

We are now ready to run the export by clicking the 'Execute' button
Select your rule execution options and then click 'Run'

Fig. 20

  • Go to Process Details to see the status of the export
  • Look for the green check mark indicating the process was successful
  • If the process was not successful, view the log by clicking on the word 'Show' in the Log column to determine what went wrong


Fig. 21

  • Scroll all the way to the last column on the right and click on 'Download' to get the output file

Fig. 22

Review the output 

Fig. 23


Once you have confirmed the rule exports the correct data, you can automate the export using the EPM Automate utility.


Post a question or a comment if you have one.

Happy Coding!







Comments

TS said…
One watchout...if you are exporting more than 5M records, the Essbase export will fail due to a know limitation. This will appear to be a DM error but obviously DM is just surfacing it.
Yes TS I agree and we did run into that. The error you speak of is the QUERYRESULTLIMIT. In PBCS this is set to 5,000,000 cells. Since DM uses MDX to query the results from the ASO cube, we are governed bu this setting. We submitted an SR to Oracle to increase the limit in the Essbase.cfg but they won't do it. To work around it we send multiple requests to PBCS via EPM Automate so we can break the file down into multiple smaller files. In our case we could run a single month but if we tried to run all 12 months at the same time we got the error. Fortunately we can use scripting to make it tolerable.

Thanks for the contribution.
Anonymous said…
Hi Gary, thanks for the blog post. I too have ran into the 5M limitation, and went through the SR process. Bottom line, the planning team was not able to up that limit, since it stems from an Essbase limitation. We ended up having to break the output into multiple extracts bellow 5M, which was a tedious an iterative process.

Have you had success exporting text values via DM (e.g. smart list text, or the text of members set to text?


Thanks again,

Luis
Hi Luis, Sorry but I have not had the requirement to export text values yet so I haven't tried to tackle it.

As for the 5M cell limit, it is a bit frustrating since that is an Essbase cfg setting. They chose 5M as the number they feel is adequate and now don't want to change it for "system stability" reasons. We modify this setting all the time on our platform for different apps with no ill effect. I'm told the PM team is evaluating increasing the limit across the board for all PBCS apps. Perhaps that will help some use cases.

Regards,
Aravind said…
Hi Gary
Very nicely articulated document. However, when we tried this, we are running into the below error. Can you please advise?

Cannot perform cube view operation. Essbase Error(1260046): Unknown Member [Period].[Oct] used in query

Regards
Aravind
I would say this is likely a problem with the period mappings. Suggest you go back and double check that step.
Are you able to export on any other periods?
Scott Williams said…
Hello Gary,

Thanks for the post. I have not had a chance to test it myself, so I apologize for asking this before testing myself.

Does the file get dropped into the inbox/outbox so we could used the Listfile command to get the file name and download it via scripting?

Thanks,
Scott
Aravind said…
Hello Gary

We checked and updated the period mapping and it is working fine now.

Thanks.

Regards
Aravind
Unknown said…
Hi Gary,

Thanks for the post - it saved us a lot of time and pain.

Do you have any idea how to change the column headings, from the UDn headers to something more useful? The team that is receiving our extracts is complaining about the headers and we can change them manually every time we do an extract, but we would prefer something more automated.

Thanks,
Mike McDonell
Sorry Mike I do not know how to change the column headings, not sure if you can or not. If you cannot figure it out, you could write a quick power shell or python script to do it after the export is complete.
Hi Scott - it does go to the inbox/outbox and I know we use EPM automate to pull the file down.
Anonymous said…
Thanks Gary for this post . This is really helpful , however I'm getting the same error : - Cannot perform cube view operation. Essbase Error(1260046): Unknown Member [Period].[May] used in query as Aravind said. I understand there is some problem with period mapping as you and Aravind said but I'm not sure if I need to do any special kind of period mapping for ASO export or not.
My Mapping is working fine for BSO to ASO data Transfer and BSO export.

-PB
Jucelei said…

Hi,
This error you resolve by putting the full member path. Example [Period].[Total_Year].[Q1]
Select the member using the option "Use Qualified Member Name = Yes in the select member in the filters




Jucelei said…
This error you resolve by putting the full member path. Example [Period]. [Total_Year]. [Q1]. [May] in the filters
Chiru said…
Hi Gary,

This is a very clear explanation but I have doubt regarding this is : Can we export YTD and QTD data as well using the data management? Where YTD and QTD are members in a period dimension. I tried mentioning the period as generic in my target application and specfying the period members along with the YTD and QTD members in DM Rule filters but its getting failed at them import as Its not able to catch the period.

Could Help on this?

Regards
Chiranjeevi
Chiru said…
Hi Gary,

This is a very clear explanation but I have doubt regarding this is : Can we export YTD and QTD data as well using the data management? Where YTD and QTD are members in a period dimension. I tried mentioning the period as generic in my target application and specfying the period members along with the YTD and QTD members in DM Rule filters but its getting failed at them import as Its not able to catch the period.

Could Help on this?

Regards
Chiranjeevi
Thanks for this helpful blog.
Can we export all 12 periods of data in one extract file?

Thanks