Month: May 2017

EPBCS Data Maps – How to improve the Headcount Transfer to Reporting

Data Maps is a great feature in PBCS that allows you to seamlessly move data between plan types and to your reporting databases. You can map smart lists to dimensions in ASO reporting cubes to convert the accounts to dimensions so that you can easily report in Smart View and Reports. It works great when you have similar dimensionality but if you really have extensive mapping you should use Data Management to move the data between cubes. As we use EPBCS, and take advantage of the out of the box content we have hit a few performance issues with data maps. Here is a recent solution we devised to resolve slow transfers of data between the Workforce cube and the ASO Workforce reporting cube.

OVERALL PERFORMANCE

Essbase is not great at extracting members that are tagged as dynamically calculated. You will see this if you execute a data map and all of the sudden a quick map is now taking a considerable time to run. Most likely there are dynamical calculated members recently added to your selection. Luckily you can disable dynamically calculated members in the map. To enable this option edit the Data Map and click the Options button. In the window that displays click the option to Exclude Dynamic Calc Members.

Then run the map again and it should be back to what you are use to. However you may be missing some data that you expect to be there. If you cannot discover what the dynamic calc members are, create a data export calc script with the same source point of view and the option DataExportDynamicCalc OFF. Run the script in calc manager and the log tab will inform you what members where excluded from your export. You can then investigate how you can include them in your data map as stored members.

OWP_Headcount Data for Reporting

As your plan and forecast grows you will see that this map will get slower and slower. It is created out of the box for the Workforce Planning module. Its purpose is to move the headcount data from the WFP cube to the ASO reporting cube that is supplied with the solution. The main reason this is slow is because the source of the headcount data is a dynamically calculated account.

 

The reporting cube and the WFP cube share the parent OWP_Total Headcount. In the WFP cube it is a dynamic calculated parent account and in the ASO cube it is a level 0 account. The data map is not optimally designed, and you cannot select the exclude dynamic calc option because no data will move over.

Here is the solution I discovered.

Step one edit the data map. Then click the red x to the right of the accounts dimension.

This will move the Account dimension to the Unmapped Dimension section. Click on the hyperlink OWP_Total Headcount and open the member selector.

Clear the current selection and the select every level 0 member under OWP_Total Headcount except OWP_Departed Headcount.

Then on the Target select the account OWP_Total Headcount.

Then click the Save and Close option. You will then get an error message that you cannot save the map because there is an invalid member OWP_Total Headcount.

This error is a defect because in the ASO cube the member is a level 0 and without a formula .

Since that member was created by WFP you cannot edit any part of it. To work around this error temporarily point the map to the account OWP_Compensation Expenses. This should let you save the map.

Now that it is saved navigate to Variables. Create a substitution variable at the All Cubes level called TARGET_HC_ACCOUNT and set the value to OWP_Total Headcount.

Click ok, and then edit the Headcount map again. Edit the account dimension on the target side of the map. Enable the Substitution Variables selector and select the substitution variable TARGET_HC_ACCOUNT.

 

 

 

Now when you click Save and Close the Data Map will save!!!

 

SHAZAM!!!!

Take a look at some results from a client app below:

The out of the box method took almost 12 minutes mostly all extracting that dynamically calculated data. With the modifications it ran with the same results in 9 seconds!

CONCLUSION

Dynamically calculated members are great for reporting but they have no place in data exports, and obviously do not belong in Data Maps! I hope that this solution helps you with your implementation of EPBCS or even in your own custom apps.

Thanks for reading. Have a great Memorial Day Weekend and remember:
“A hero is someone who has given his or her life to something bigger than oneself. “ Joseph Campbell

Part 2 – Where is my import/export security utility? How to Import Security

UPDATE

After I posted this, I realized that I was not checking for XML special characters and if you had one in your metadata it would error when importing into PBCS. I have corrected this and updated the downloaded script. Sorry if this caused any frustration.

This is part two of the import/export utility for PBCS. Extracting security is fine, but now we want to import the security back into our system. As you may remember we extracted all of the group security and placed it all into one comma separated file using the Powershell utility in the last post.

We are going to utilize LCM to import the security back but we have to get the files and the archive in the correct format. Some key points to remember with the import:

  • LCM will import all of the user security from the file.  So even though the export creates a file for each group you can import it all in one file.
  • There does not seem to be a way to clear the security before you load it.  This was possible in the utility.  (I will keep playing with this.)
  • So based on the last point it is a merge of the security in the system. 

 

CONVERT CSV TO XML

PBCS requires a very basic but specific XML format. I have written a Powershell Script to convert the CSV into the correct format for planning to accept. To utilize the solution you will need two files located here SEC2XML.

The download has two files. Please place these files in the same directory.

  • listing.xml – is a file that will be used to build an archive file that can be loaded to your PBCS environment. 
  • sec2xml.ps1 – is the Powershell script that will convert your csv file into the correct xml format and build an archive based on your original download that can be loaded back into PBCS.

This script will accept two parameters. The first is the path and name of the csv file. The second is the path to the original archive that you extracted application folder. A bit more detail on that parameter below:

This is an example of an unzipped lcm extract of just my security groups. The second parameter is looking for the path of the application folder in this structure in the below screenshot is HP-TOYS. TOYS is the name of my planning application.

Below is an example of the syntax to run the script:

/sec2xml.ps1 /Users/anthonymanfredi/Dropbox/POWERSHELL/SECURITY/securitymac.txt /Users/anthonymanfredi/Dropbox/POWERSHELL/BLOG/ZIP/HP-TOYS

  • /sec2xml.ps1 = The name of the script
  • /Users/anthonymanfredi/Dropbox/POWERSHELL/SECURITY/securitymac.txt = The path and name of my security csv file.
  • /Users/anthonymanfredi/Dropbox/POWERSHELL/BLOG/ZIP/HP-TOYS = Unzipped application folder.

 

The script does quite a bit of magic but here is a flow chart highlighting the steps:

The script will convert the csv file back into the proper xml format. Next it utilize your original zip extract to build a zip file to load your new security upload. I would recommend just using the groups export to simplify the process. The script will delete any existing xml security files in the groups folder and then place the newly created XML file in the directory. I deleted the other files so that there would be no contention on load and the latest updates will come from the new csv file.

The next step will copy the listing.xml in the info folder. This file will let planning know about the new export that you want to load.

Once that is complete it will zip the structure and put the file security_update.zip in the same directory you executed the Powershell from.

 

IMPORT THE SECURITY

Now that the archive is built you can login to the PBCS application and navigate to the Migration section.
Once you are there select the Snapshots tab and upload your Archive.

Now that its uploaded you should be able to navigate the archive and see the security like below:

Import the snapshot when your ready and the application security will be updated

Couple of notes:

  • You will need to delete the snapshot in PBCS or rename it when you run the process again. You can’t have the same archive twice.
  • When you extract the original archive place it in its own folder so the Powershell script will not accidentally zip up extra files.  

CONCLUSION

I hope this helps everyone with their security in PBCS. Please don’t try this for the first time in PROD!! Run it in test first. If you have any questions or problems let me know!!