Skip to Content
avatar image
Former Member

Is it possible to decode EPM AddIn cache file names?

The Goal: To eliminate the need for nested EPMMemberProperty formulas

The Background: Within our Input Forms, we have code to check user's security and perform different actions based on that. The crux of the problem is that security may be granted to a specific, high-level, hierarchy node (BUSUNIT). We need to be able to determine if a user has security granted to either the member selected, or a parent (potentially multiple levels removed). We have solved this issue by using nested EPMMemberProperty formulas looking up the "PARENTH1" property to essentially flatten out the hierarchy. Then we check security against both the selected member(s), as well as all of the parents, all the way up to the top of the hierarchy.

The Issue: Our current solution is slow. It only gets worse the more formulas are present.

The (Potential) Fix: Using a VBA Dictionary object that contains hierarchy members as keys, and that member's "PARENTH1" property as the values.

The Catch: Populating the dictionary with the GetPropertyValue API call is incredibly slow. Alternatively, the cache files could be read directly, however with the names encoded it takes too long to find the right file.

The Question: Is it possible to decode the cache file names so our code could know which file it should read without searching through each file in the cache folder?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 29, 2017 at 04:39 PM

    And some theory:

    "The Catch: Populating the dictionary with the GetPropertyValue API call is incredibly slow. Alternatively, the cache files could be read directly, however with the names encoded it takes too long to find the right file."

    Why slow?

    If you look on my code here: https://blogs.sap.com/2014/06/04/bpc-nw-10-vba-to-get-dimension-members-list-and-properties/

    And remove scan for all properties it will be fast enough... You will run this code only once on workbook open and keep the dictionary in memory!

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      For anyone interested, I was able to solve this issue, though with some important caveats. I have attached my module for reference.

      Summary

      GetPropertyValue API call for 60,000 dimension members took ~20 seconds in an optimal scenario. My code for the same number of dimension members takes ~1.5 seconds.

      Solution follows these steps:

      1.Determine which folder on client machine has the cache file we require (Business Unit and PARENTH1)

      a.Loop through all XML files in Metadata folder (C:\Users\[user name]\AppData\Local\EPMOfficeClient\Metadata) and return the folder path of the XML file with the highest version number. This should be an indication that this is the folder for your production environment.

      i.We are working with a shared dimension here so we do not care which model this particular Metadata folder is for.

      b.Once we have narrowed our search to one folder, search through all cache files for a unique string that indicates we have the right file. For my purposes, line four of the cache file has the string “HIR HIR Y”. Once a match is found, return the path of the specific cache file as a string

      2.Pass string path to cache file to procedure that loops through it and creates a Dictionary

      a.Open the cache file and create a dictionary in VB where Key = Business Unit, and Value = PARENTH1 Property

      i.Declare Dictionary as Public at top of module so it stays in memory for future use

      ii.I used ADODB.Stream with great success here, and was able to read almost 60,000 lines of a text file into a dictionary in less than one second

      3.Instead of using EPMMemberProperty formulas or the GetPropertyValue API call, just use BSUParentPropertyDictionary(YourBSU).

      a.This method is exceedingly fast; a 60,000 row by 13 column array was populated in ~3 seconds

      4.Limitations/Dependencies

      a.There must be a unique string in your desired cache file

      b.If unique string in cache file changes or is no longer unique, code will probably break

      c.Attempting to build dictionary right after Clearing Metadata Cache will fail, as procedure requires local cache files. I suggest only attempting to build the dictionary on file open, or during a refresh

      d.No performance improvement if working with small sets of data, this actually adds 1-1.5 seconds to search for file and build dictionary.

      bascheckusersecurity.txte.Early binding is used for maximum speed

  • Mar 29, 2017 at 04:21 PM

    "We have solved this issue by using nested EPMMemberProperty formulas looking up the "PARENTH1" property to essentially flatten out the hierarchy. Then we check security against both the selected member(s), as well as all of the parents, all the way up to the top of the hierarchy." - Ups, you have a very strange security approach :) What do you try to achieve this way????

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Vadim Kalinin

      It is a beast, and the decision to go that route was made by those much above my pay grade.

      The good news is that our users love it, so at the end of the day it seems better to work around the system as it currently exists instead of trying to push for a structural change.