TAGS

Recent Posts

Archives

Importing Supporting Detail from PeopleSoft into Oracle Planning and Budgeting Cloud Service (PBCS)
Posted on November 9, 2016
Author: Richard Pallotta, Performance Architects

One of Performance Architects’ Oracle Planning and Budgeting Cloud Service (PBCS) customers needed to import existing supporting detail from their PeopleSoft application to PBCS, but the only way they could get data out of PeopleSoft was in CSV format. PBCS requires XML-formatted data for the import, so data needed to be transformed for PBCS to ingest the information.

There are many useful tools available to convert CSV data to XML, but we wanted to provide a way to do this using commonly available tools.  In this case, we used Excel and a text editor.

This blog entry includes the basic steps to create a CSV-to-XML solution using Excel and a text editor based on this customer case study:

  1. Download a template XML file from PBCS
  2. Edit the template XML file and make a schema for Excel
  3. Open the PeopleSoft CSV file in Excel; open the XML schema in Excel; and map the CSV file
  4. Export the CSV file as an XML file
  5. Edit the XML file in a text editor
  6. Upload the XML file to PBCS

The rest of the content delves into the details for each step:

  1. Download a template XML file from PBCS

To make things easier, I’ve downloaded an existing set of supporting level detail data. This will serve as a template for your specific PBCS model and provide guidance for ultimately creating the XML file you’ll need to import.

But first, a little context for those who may not be familiar with the concept of “supporting detail.” This is additional information that exists below the level-0 members in PBCS, usually under “Accounts.” This information is stored in the PBCS relational repository (not Essbase), although the detail will consolidate to a numeric value that is stored in Essbase.

Let’s view a very basic web form intended to strip away everything but what is necessary to illustrate this task. Cells with supporting level detail are displayed in the web form with a green background color:

This web form has accounts in the rows and periods in the columns. Right-clicking on an account with a green cell will show the detail for account AC_4010:

Let’s add a sibling and a few more data points in the web form so we can get a more complete sense of the data we’re going to download:

We want to reverse-engineer the detail shown in this web form, and there are significant differences between the simplified and classic PBCS interfaces in regard to downloading content. Since a lot of good documentation is available on this functionality, I’ll leave that beyond the scope of this blog. We’re going to focus on transforming the data into the XML format that PBCS needs.

  1. Edit the template XML file and make a schema for Excel

Once you download the XML file, you’ll see it is named “Supporting Detail.xml.” We’re going to use this as our guide to create the XML mapping schema (sometimes you’ll see this type of schema called an “xsd file”).  Use a good text editor (one that will color-code the text and make your life easier) to open the XML file. The PBCS XML file is comprised of several primary sections marked by opening and closing tags:

  • columnDetails
  • dimMappings
  • planType
  • columnDetail
  • columnDetailItem

Similar to matching parentheses in a math formula, these sections must be opened and closed with tags. For example, the very top of the page starts with <columnDetails> and the very bottom closes with </columnDetails>. Spelling and upper / lower case letters definitely count here, so be careful.

The first thing we’re going to do is make a copy of this file, then make a simple schema file that we can use in Excel to map the PeopleSoft CSV file so we can export it as an XML file for PBCS to use.

Step 1: Open Supporting Detail.xml and save it as MySchema.xml

Step 2: Delete row 1 of the file and insert these two rows of text in lines 1 and 2 instead (Excel seems to require these for building the schema):

<?xml version=”1.0? encoding=”UTF-8? standalone=”yes”?>

<data-set xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>

Step 3: Delete the rows at the beginning and end of the file that says <columnDetails> and </columnDetails>

Step 4: Delete the rows with <dimMappings> and </dimMappings>, and everything between it:

The above is an example of my dimensional model (yours is probably going to different).

Step 5: Delete the row at the top of the file with <planType planName=”some name here”> and the one at the end of the file that says </planType>.

Step 6: Refer back to your original file because the next section needs some search-and-replacing. If the original file has a line (like above) that shows “Scenario” is being mapped as “DIM1”, then replace “DIM1” with “Scenario” in your new file. Change it from this:

To this:

Do the same for the rest of the dimensions. You only have to do this in the very first <columnDetail> section; the rest of the sections are redundant for the purpose of making the schema:

Leave everything alone in the <columnDetailItem> sections:

These contain supporting detail items including values, their positions, consolidation operators, and the description text of the item itself. The “Column Detail” and “Column Detail Item” sections operate in unison, just like a row in a data file that has a point of view (POV) and values.

Step 8: Delete everything after the closing </columnDetail> tag to the end of the file. That is, from the next line that starts with <DIM1> all the way to the end. Basically, we just need one complete set of tags to create the schema.

Step 9: Add the closing </data-set> tag to the last line of the file.

That’s it for the XML file. Just to review, these are the sections we should have:

First row:

  • <?xml version=”1.0? encoding=”UTF-8? standalone=”yes”?>
  • An opening <data-set> tag in row 2, and a closing </data-set> tag in the last row
  • One set of opening and closing <columnDetail> tags
  • Multiple opening and closing <columnDetailItem> tags

Here’s my file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <columnDetail>

      <SCENARIO>Bud</SCENARIO>

      <ACCOUNT>AC_6000</ACCOUNT>

      <DEPARTMENT>DP_4640</DEPARTMENT>

      <PERIOD>Oct</PERIOD>

      <VERSION>Working</VERSION>

      <YEARS>FY18</YEARS>

      <FUND_CODE>Fund_Code_NA</FUND_CODE>

      <PROGRAM>Program_NA</PROGRAM>

      <PROJECT>Project_NA</PROJECT>

     <columnDetailItem>

      <VALUE>100</VALUE>

      <POSITION>0</POSITION>

      <GENERATION>0</GENERATION>

      <OPERATOR>1</OPERATOR>

      <LABEL>Apples</LABEL>

     </columnDetailItem>

   </columnDetail>

   <columnDetail>

      <SCENARIO>Bud</SCENARIO>

      <ACCOUNT>PERIOD</ACCOUNT>

      <DEPARTMENT>DP_4640</DEPARTMENT>

      <PERIOD>Oct</PERIOD>

      <VERSION>Working</VERSION>

      <YEARS>FY18</YEARS>

      <FUND_CODE>Fund_Code_NA</FUND_CODE>

      <PROGRAM>Program_NA</PROGRAM>

      <PROJECT>Project_NA</PROJECT>

     <columnDetailItem>

      <VALUE>100</VALUE>

      <POSITION>1</POSITION>

      <GENERATION>0</GENERATION>

      <OPERATOR>1</OPERATOR>

      <LABEL>Pears</LABEL>

     </columnDetailItem>

   </columnDetail>

</data-set>

Excel seems to really want spacing to be perfect, so use this example as a guide. Also, Excel refused to highlight the entire column when I dragged the mapping over, unless there was more than one set of <columnDetailItem> tags.

  1. Open the PeopleSoft CSV file in Excel; open the XML schema in Excel; and map the CSV file

The PeopleSoft supporting detail file should be a tab or comma-delimited text file; make sure to open it in Excel. If it doesn’t already have a header row, add one. For your own convenience, put the field names that correspond to the dimensions, values, labels, etc. into the Excel sheet.

Next, select the “Developer” tab in Excel and press “Source.” An XML pane will open on the right side of the worksheet:

Then, import the XML schema. Press the “XML Maps” button in the XML pane:

Press the “Add” button, and browse for the XML file you just created:

Don’t be concerned if you see this message, just press “OK”:

You’ll see something like this in the XML pane:

Now is a good time to clean up the CSV file. If you need to delete any extraneous columns or rearrange anything, you’ll need to do this before you start to apply the XML schema to the file. Add columns for “Position,” “Generation,” and “Operator” if they’re not already there. There should be a column for “Value,” which represents the data point coming from PeopleSoft.

The order of the columns in the Excel file should match those in the XML pane. Otherwise, you may get an export error in Excel when you try to convert the data.

“Position” is the relative location of the supporting detail member below the account and starts with “0.” The first, second and third siblings will be “1,” “2” and “3,” respectively. You’ll need to sort and process the CSV file to look for non-unique rows and sequentially insert a position value; that’s out of the scope of this document but an important thing to do. Here is an example of where I had to change a position value:

Now, left-mouse click and drag the column detail icons from the XML pane to the corresponding column in the Excel grid, e.g., row one of the column you’re mapping. If the entire Excel column doesn’t change color, there’s something wrong with your schema XML file and you’ll have to re-check it:

Repeat this for every column in the file. When it’s done, it should look similar to this:

  1. Export the CSV file as an XML file

Export the CSV file to XML format. Go to the “Developer” tab in Excel and press “Export”:

If all has gone well, you’ll not get an error message and will be prompted to save the file.

  1. Edit the XML file in a text editor

Before we can import the XML file to PBCS, we need to make a few changes. Open the XML file you just exported from Excel in a text editor. Remember that PBCS download XML file named “Supporting Detail.xml” that we saved as a backup? Use that one.

Open the Supporting Detail.xml file and copy this section, from row 1 to the row with the <planType> tag:

Paste that section into the XML file you just exported from Excel. Replace everything above the <columnDetail> tag. That is, replace rows 1 and 2 in your new XML file with what’s shown above.

Every dimension tag (Scenario, Account, Department, etc.) in the XML file to be uploaded to PBCS has to be replaced with its corresponding name in the dimension mappings shown above (DIM1, DIM2, DIM3, etc.) For example:

<SCENARIO>Bud</SCENARIO> becomes <DIM1>Bud</DIM1>
<ACCOUNT>AC_4409</ACCOUNT> becomes <DIM2>AC_4409</DIM2>
<DEPARTMENT>DP_7670</DEPARTMENT> becomes <DIM3>DP_7670</DIM3>, etc.

Do this for every instance for each dimension tag in the file; do a global search-and-replace based in your text editor using the mapping shown so that every tag that starts out like this:

Becomes this:

Now we have to make sure all our opening and closing tags are completed. When we pasted the dimension mapping into this file we over-wrote a tag called <data-set> and added 2 new ones called <columnDetails> and <planType>.

Go to the second-to-last row and insert <planType>
Go to the very last row of the file and replace </data-set> with </columnDetails>

  1. Upload the XML file to PBCS

That’s it, you’re ready to upload the XML file to PBCS! Your planners will have their old PeopleSoft supporting detail available as a reference for them.

 

Share
© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply

Your email address will not be published. Required fields are marked *