Since Oracle’s roll out of Planning and Budgeting Cloud Service, there have been plenty of user friendly and unique features that Oracle hasn’t advertised. In this series, we will be exploring some of the tools that can make your job as a PBCS/EPBCS user or developer a lot simpler and provide an improved user experience. Part 1 covered the new capability to further limit the years and periods shown on input forms based on the Scenario dimension properties. Part 2 had a look at the new option to load multi-lingual labels for all your customized planning artifacts. Part 3 reviewed enhancements to interacting with Attribute Dimensions in a planning application. In this next blog, we will explore the brand new capabilities for maintaining cloud dimensions through the Data Management module.
Metadata Loads in Data Management
One of the great new features of PBCS as of the December 2017 release is that you can now load Metadata through Data Management. This feature is something the user community has been asking of Oracle for quite a while. While previous releases of PBCS supported a few different ways to maintain metadata, none of them gave the tool-driven mapping transformation capability that is now available with Data Management. This allows for significantly better automation of metadata updates, albeit only through flat files (for now, at least). Note: in this update, only regular, custom, and Smart List dimensions are supported for metadata loads.
Currently, this feature is only supported on the following EPM cloud products according to Oracle:
- Oracle Planning and Budgeting Cloud
- Oracle Enterprise Planning and Budgeting Cloud
- Oracle Financial Consolidation and Closing Cloud
- Oracle Tax Reporting Cloud
Here is the link to the December update: Oracle PBCS December 2017 Update
In order to use Data Management for metadata loads, I referenced Oracle’s EPM Cloud documentation for data management administration found here: Administering Data Management for Oracle Enterprise Performance Management Cloud
In this blog, we’ll walk step by step through how to use PBCS Data Management to load Metadata Flat Files.
1. Grab the metadata flat file you want to import. Something to note: the flat file should NOT contain any headers. So if you are taking a dimension export from another cube, be sure to remove the first line that contains all property names and member relationships (parent, dimension name, time balance, etc). Your load file should look something like the picture below and not contain the header information. This will be defined later.
2. Access Data Management through Navigator -> Integration -> Data Management.
3. Go to the Setup tab and create a new source system. Click Add, then fill in the Source System Name of your choosing (PK_Metadata_Load in this example), select file for Source System Type, and add a Source System Description of your choosing if necessary. Click Save.
4. Go into Target Application, click Add-> Dimension.
5. Choose the application you are loading dimensions for, and an optional prefix for the Target name. When you click OK, a target application will be created for every dimension in the target app.
6. Start with the first dimension you are going to load, and look at Application Details -> Dimension Details. This is where you will add all the properties you are importing from the flat file. Add/Delete until you have the desired set of properties. You can also Enable/disable properties from the flat file load by selecting/deselecting Select Property. For each property you will be loading, map the Data Table Column Name. It appears there is a max of 20 User Defined columns, meaning there is a limit on the number of properties that can be listed.
Note – In Application Options, you have the ability to select some application properties such as refreshing the database. Click save when you are finished.
- Navigate to Import Format and create a new one.
a. In the details, create a name for the Import Format.
b. From the Source, select the source you created for this metadata flat file in Source System.
c. For File Type, select a file type that is All Data Type. Do not use a numeric file type.
d. Select the Target that we created in the previous step.
e. Finally, select the file delimiter used, then Save.
- Based on the inputs in the Target, Mappings will automatically populate. In the corresponding rows, fill the Source Column info and its respective field/column in the metadata flat file.
a. You can manually add this information, or select Build Format, which will allow you to highlight and select the formats directly from the source dimension flat file.
b. If using Build Format: Upload the dimension flat file, select that file, and select OK.
c. From here, there are 2 options to assign the Source Dimension names. You can either choose Yes from the Assign selected text as Source Dimension Name, or manually assign the dimensions.
d. Regardless of the method, highlight the text in the panel, select assign dimension, and choose the Source Dimension Name, Target Dimension (which includes properties), and its corresponding field/column.
Once done, select OK, then once returning to the original page, select Save.
- Create a new Location and fill out the Name and Import Format, and the rest should auto-populate. Once complete, select Save and a partition key will generate.
10. Go back to Workflow->Data Load Mapping. Ensure you are in the correct Location as determined by the footer. If you are not, click the Location and choose the appropriate one.
11. Map, transform (if needed) and assign values of the properties to the corresponding properties in the target dimension. Note: even if you are doing a 1 to 1 mapping, you must create a like map using ‘*’ as the global pass through rule. Nothing will validate unless you do this step. Repeat this process for every property of each dimension you are loading. When complete with each Dimension, press Save.
12. Navigate to Data Load Rule and add a new rule. Add the Name, Category (category in this instance will not matter), the Import Format you are using for this metadata flat file, and the File Name by using select and then choosing/uploading the corresponding flat file you will be using.
Once done, press Save.
13. Navigate to Data Load Workbench. Ensure you are in the right Location as mentioned previously. Import and validate the data from the file by selecting Import. Choose whether to append or replace for the import mode. Additionally, choose whether you want this import to occur offline or online.
* If successful, you should see 2 gold fish appear below Import and Validate.
* If unsuccessful, navigate to Process Details and click Show for the log of the process that corresponds to your attempted metadata import. This will download a log. Scrub the log for errors and troubleshoot until successful.
14. After completing the Import/Validation step, select export and it will prompt you to choose if you want to execute this export online or offline. Choose the method and press OK. After this, the export will run.
As with the previous step, if successful, a gold fish will appear under Export.
Congratulations!! You’ve successfully uploaded metadata through Data Management using a flat file into PBCS. I expect future iterations and updates of Data Management to allow more streamlined methods of metadata loads and perhaps further integrations with SQL or other ERP/EPM systems. For now, this is a nice option to have to more easily allow you to move and transform metadata with a one-time setup. An added benefit is the ability to schedule the dimension imports in order to keep metadata in sync, automating a lot of manual work and keeping PBCS on track to meet enterprise level needs.
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
