Oracle EPM Planning Mapping Smart Lists to Attributes

Intro

It has been a little while since my last blog post, but hopefully not too long. Given the time I thought I would share something that is not default functionality, but something that I am sure a lot or at least some would find very useful in their Oracle EPM Planning (Previously aka PBCS or EPBCS) adventures.

Planning has really cool default functionality that allows you to map a Smart List selection to a dimension. The way this works is that you create a reporting cube where you may want more detail/dimensions to report on. This then allows you to push data from your more summarised cubes to reporting cubes with more detail and dimensions using a mapping from Smart List selections to Dimensions. I won’t go into too much detail about this standard functionality, that can be found as part of the documentation.

So where does this get interesting? Glad you asked…If you didn’t ask, you are about half way through and you should just keep reading it does get interesting I promise.

Lets start with some examples:

• Imagine each project in your project dimension may have an attribute of a project manager. For the sake of this blog lets assume we are not loading this attribute from source and we need a solution to allow users to assign project managers to projects themselves without the need for admin level involvement.
• Another example is the mapping of PL to Balance Sheet Accounts to enable an Account trace of where the data came from or where it went.

In this post we will tackle a solution for this, to allow us to handle attribute assignment through smart list assignment. This solution takes the idea of the Smart list assignment from the product default Smart list to dimension mapping, but then applies some cool scripting of its own to deliver a cool solution. Really hoping that Oracle provides a solution for this at some point as part of the tool and would be even cooler if this can be inspiration for that…

And if you are thinking why would I want to manage attributes assignment using Smart Lists here are some reasons:

• Attribute dimensions are excellent tools for establishing descriptive capability of a dimension member and to then enable slice and dice capability by this attribute
• You may want a solution that gives you the power of an attribute dimension without using another dimension or reporting application
• You may not have a reporting cube to provide greater detail with your data analysis and wish to use attributes on the current cube(s)
• You want users to control the attribute assignment
• Attribute management can be handled through the Planning front end or through the Smart View Dimension Editor, the functionality is very manual in nature and is usually assumed by an administrator type user and introduces a layer of metadata management to the solution, especially when will be managed within the solution and won’t be loaded from source
• In short manual non-user tasks = a pain for administrators
• A solution would give control to users to easily view & update and make the administrators happy
• Final reason(s) because its cool, it makes perfect logical sense to do it and a clever solution exists

The solution does require processing outside of Planning front end, so would recommend an automated scheduled solution to run on a scheduled basis (nightly, hours, etc…) or you get clever with the EPM Agent or a similar solution to allow it to be triggered on demand (note: this post does not cover scheduling or on-demand trigger, but will cover the code samples that can be used).

How to Enable Smart List to Attribute Conversion

In this example I have used EPM Automate and some PowerShell, but to be clear I am not telling you how to do it, this is but an example and I am sure there are many clever people that can expand and make this even better and if this happens then great, my purpose would be achieved.

Below I have given examples of the code to:

• Extract data from Planning using EMP Automate and create a file. In case you are wondering this is the data containing the Smart List assignment.

Tip: To keep this simple ensure you are using a no year or start year and no period or beg balance extract for this to keep it a simple and small extract.

• Simple yet flexible PowerShell code to convert the file extracted into a loadable metadata file
• Import Metadata code using EPM Automate

I have not included detailed setup of the creation of the form with the Smart Lists assignment or the extract from Planning. I assumed this was a basic task.

See it in Action

Here is the process with some simple screens in action, this will also give an idea of file formats. In this example I did not using project manager sample, but rather a PL and Balance Sheet account mapping. I am sure the applications are many!

Conclusion

Whilst I really hope the functionality continues to evolve and grow overtime as it has since the tool has gone to the cloud to incorporate functionality to bring more power to users like attribute assignment, in the meantime we get to come up with some clever ideas, try them and then share them for others to benefit.

A Message

Like a Jedi, always be in search of knowledge and wisdom! I wish the Force is always with you!

Advertisement