Map Reporting Application – Maintain Master Data in Planning Forms

There is a seldom used feature in Planning, It’s not new, it’s been around for years. It’s a simple feature with a simple objective, but how it does this adds a level of power to Planning that most people don’t even know they have.  The feature I'm talking about is “Map Reporting Application”.  This little options, tucked away in the Administration menu, ok so it’s right there, not very hidden, is a very fast, effective, easy to configure method of moving your data from Planning into an ASO application for reporting, but that’s not all! No there are some more perks to this feature. 

Since it’s a planning feature, you’re planning administrators can run this on demand, with just a couple of clicks.  No more needing to go to the ODI/FDMEE person to get a job run for that one off unscheduled requirement. Mappings are easy to configure, simple drop downs and selections, anyone can do it. It can be scheduled / Automated through the command line utility.” Smart List to Dimension mapping”, this is my favorite part.  That’s right; you can take your smart list values and actually map data on those into dimensions in your reporting application.

So let’s take a minute and put Smart List mapping into context, from a business perspective, what does this allow you to do? Well, have you ever had planning users say “I want to manage those attributes” or “Why can’t I just change the stage of that project myself?”. Using Smart Lists you open up those opportunities to the users, and you don’t buy anything you don’t already have. You can  now give your business  users master data management ability ( including time-varying  tracking) , inside of planning, that they never had before and since these are going to be ‘account’ members, you can apply any security to this you find useful.  So if user John Doe wants to update customer 1, and you don’t want him to touch Jane Smiths customer 2, just apply dimension level security and you’re set.

With that said, let’s get into the walk-thorough on how this is done.

The first think you need to do is create your Planning and ASO applications, I'm not going to go through how to do that, but you do need to make sure your ASO application has extra dimensions for anything you want to map from Planning.  My sample applications here are pretty basic, a couple of scenarios, version, months, years and this:

In my ASO I have added the additional members to map to my ‘Attributes’ that will come from the smarts lists in the mapping in planning. The rest of the outline is identical except for Scenario, where we have monthly forecast scenarios instead of a single Forecast scenario (more on this later).

Now we need to build a way to update our new attributes, we are going to store these in the account dimension of planning, but we don’t want to have to update every possible intersection where we are going to use the attributes.  Attributes applied to one dimension should generally span all other dimensions.  The way I work around this is by using two sets of accounts, one for storing and updating the members, and a second for dynamically reporting on them.  I do this with a simple member formula on the dynamic set of members pointing to a default intersection.  

 In my example, the attributes are going to be applied to the Customers so I use a reference to “Entity_None”->”BegBalance”. This will allow my attribute to still vary by Year, Scenario and Version, but stay consistent across all periods and entities.  If you wanted to fix in those dimensions, you could specify a member in each and use that intersection to store the input.  If you wanted the attribute to vary by month, you just need to remove the BegBalance reference and store inputs at monthly level.

You also need to make sure that this dynamic member is set up with the data type as Smart List and pointing to the same smart list as the stored member so when planning looks at that value, it has a Smart List value to use, not just an index number.

Talking about Smart Lists, let’s take a look at how we set those up.  I have created a smart list for each of the attributes I want to map, and one key to making this all work, is to make sure you set a “#MISSING Drop Down Label”.  This label needs to be a member of your ASO dimensions and will be used to map any data where you have not set any attributes. In the example ASO outline you may have noticed I added members for Unknown attributes, those will be my catch all buckets to make sure we don’t lose any data in the load. You can always check these unknown members in your ASO to assign those attributes after.

Next you need to populate your smart list with some entries. You can do this manually here, or take advantage of another one of the Map Reporting Applications features and refresh the smart list right out of your ASO cube outline. You may have noticed this little button before and wondered why it didn’t do anything; well we’re going to answer that next. But first, since all of our prerequisites are now met to start mapping, let’s do that.

Go to Administration->Map Reporting Application and click the Create button

Here you need to select which plan type you are mapping from, and what your target application will be, by default your Essbase server that the planning application is using will be shown, but you can click the green + to add other servers if required. If everything is there and good, click on Next.

Here is where the magic starts. By default any dimensions that match will be mapped dimension to dimension, and to L0 Descendant members, you can change this to fit you needs. We can also set up our smart list to dimension mappings here.

When you select Smart List to Dimension, you will have a list of all available Smart Lists in the next box, selecting a Smart List will then show you all accounts that are using that Smart List. Remember when you set up the Smart List to Dimension mappings to select the dynamic members, not the stored ones. Go through the rest list and select all the mappings you want to set up, you can select to map only a specific year/scenario if you wish, or maybe only a specific set of accounts. Once you have everything set up, click on next. 

The last screen will show you any remaining Dimensions. 

Notice here that I'm using a substitution variable to map the Scenario. That’s right, you can even use substitution variables if you wish.  In my example this allows me to map the Scenario Forecast in planning to a specific monthly forecast in the reporting application. Click on finish and you are all set to go… or are you?

If you didn’t already add your Smart List entries it’s time to go back to our Smart Lists and synchronize them to the now mapped ASO Application, select your smart list, and click the Synchronize button

Accept the warning, which basically says that L0 members from your ASO will be loaded to the smart list entries (yup that’s what we want) next you should see this error:

That’s ok, the reason you are getting this is that we set the missing label in advance and it matches an L0 member in our ASO, that’s what we want.  Note that this error will not pop up every time you synchronize the smart list, only when the Unknowns are added in the first time.

Our smart list entries are now loaded, I would recommend setting the Unknowns to ID 0, since forms love to save 0’s, especially if using SmartView, and this will result in data not mapping if there is no 0 member in your Smart List, 0 is not #MISSING.

Once added to the Smart List, entries will not be removed by synchronizing even if the member is deleted from the ASO later. Another thing to note on this is that as you know, there are restrictions on Smart List Entry Names, no space, no special characters.  If you want to use these in your ASO member names, you can still use this, since the mapping is based on the Label and not the Name, but you will need to manage the smart lists manually.

All of our setup is now done, so let’s add some attributes! I’ve set up a basic form; you can make this as simple or complex as you wish, as long as it gets the job done. Again, because we are in Planning, Audit logs provide the ability to track any changes to the ‘master data’ maintained in Planning forms.

So now we are ready to run the mapping.  We have two choices when we push data, we can do a just a push, which will add our data into any existing data, or we can do a clear data and push, which will do a partial clear on the ASO and push our data in.  I’m a big fan of Clear and Push but there are a lot of instances where you want to do a straight push too. 

Moving on with our example, we will use clear and push (even though the cube is empty right now)

Once it’s completed, you will get a notification, the time will vary depending on the size of your databases. You can also check the status in Job Console, where you will get some more details about the activities that happened, time taken for each part and any rejections that were found.

So taking a look at our ASO you can see our data here, with all of our attributes, just the way you always envisioned it.

A couple of closing notes:

One question I get a lot on this is, what if my smart list gets too big, wont it limit the entries? Well there is no documented limit to Smart Lists (that I have found) and I personally have worked with smart lists that contain over 1000 entries, I don’t think this will be an issue.  I would add to that, working with very large smart lists in IE can be difficult, I normally recommend using Smart View in those cases.

The next question I get is “Great, I set up this awesome mapping, but now I have to run it manually, why can’t you just schedule it to run every hour?” well, you can, there is a planning utility “PushData” and you can schedule this job to run, using the scheduler of your choice with these parameters

Push Data [-F:PasswordFile] /U:UserName /A:SourceApplication /M:ApplciationMapping [/C]

Most of these are straight forward, using /C will enable you to run clear and push, instead of a straight push.

I hope this helps you, if you have any question, please leave a comment below