Is it possible to decode EPM AddIn cache file names?

[Former Member]

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?

[former_member186338]

What do you mean by this answer?

[former_member186338 · Accepted solution]

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!

[Former Member · Accepted solution]

It seems that the GetPropertyValue API call is slow because of the size of our BUSUNIT dimension (over 50,000 members). Slimming down your code to only return ID (via GetHierarchyMembers) and property "PARENTH1" took a little over two minutes to complete. This is too slow for use in any of our forms/reports, and seems generally to be a less efficient way of retrieving property data compared to reading it in all at once.

My current solution reads the cache file into an array using an ADODB stream, then adds those values to the dictionary, which - as you mentioned Vadim - remains in memory for later use as long as the Input Form stays open. Doing this takes about one second. Still not ideal, but an acceptable tradeoff for the performance gains we would see elsewhere.

[former_member186338 · Accepted solution]

"My current solution reads the cache file" but to my mind it's impossible to "decode" the cache file names... The names are just GUID's...

[former_member186338 · Accepted solution]

Just tested very simple code for 5,400 members and single PARENTH1:

Dim epm As New FPMXLClient.EPMAddInAutomation
Public Sub LoadMem()
Dim strConn As String
Dim strDim As String
Dim strMem() As String
Dim strProperty As String
Dim strProp() As String
Dim lngTemp As Long
Dim lngTimeStart As Double

strConn = "XXXXXXXXXXXX"
strDim = "COORDER"
strProperty = "PARENTH1"
lngTimeStart = Timer
strMem = epm.GetHierarchyMembers(strConn, "PARENTH1", strDim)
ReDim strProp(0 To UBound(strMem))
For lngTemp = 0 To UBound(strMem)
    strProp(lngTemp) = epm.GetPropertyValue(strConn, strMem(lngTemp), strProperty)
Next lngTemp
Debug.Print Timer - lngTimeStart
End Sub

Result on average: 0.86 sec - ONLY!

For 50,000 will be about 8 sec - not 2 min...

For sure 8 sec is not very fast but can be acceptable in some cases...

[Former Member · Accepted solution]

Yes, but those GUIDs change every time Metadata is refreshed, so a static path to the cache file cannot be defined. Obviously the EPM Add-in makes a reference to the path when the Metadata is refreshed, I was hoping there was a way to do something similar so the path to the exact cache file I need could be determined without having to open each file and check if it contains the correct property values.

[Former Member · Accepted solution]

Ok, looking at your code jogged my brain a bit. We are using late binding for all of our VB for compatibility reasons, but on large loops like this it would definitely add overhead vs early binding.

I changed my test code to use early binding and it ran in ~18 seconds, though subsequent refreshes took the same amount of time as before (~2 minutes) despite using early binding. Potentially better than before, though still not ideal, as our forms already take a noticeable amount of time to load.

Outside of using DoEvents in the loops, are there any ways to do this in the background?

[former_member186338 · Accepted solution]

Unfortunately no way! Without changing the code of EPM Add-in 🙂

[former_member186338 · Accepted solution]

Looks like GetPropertyValue is requesting info directly from the server (not from local cache). Then the speed depends on the server connection speed etc...

Another option is to use some badi that will fill some property on the base level based on the parent level. In this case you will have to read property only for the base members used in the particular report...

P.S.

"Looks like GetPropertyValue is requesting info directly from the server (not from local cache). Then the speed depends on the server connection speed etc..." - Incorrect, just tested! Property values are coming from local cache!

[Former Member · Accepted solution]

That's too bad, though it is the answer I expected. I'll have to get creative, or move on to some lower hanging fruit.

[former_member186338 · Accepted solution]

You can decomplile the EPM code using ILSpy 🙂

[Former Member · Accepted solution]

Woah, that's an interesting idea. I'll play around with that and see if it leads anywhere. Thanks for the help Vadim, you are very insightful, as always 🙂

[Former Member · Accepted solution]

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

[former_member186338]

"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????

[Former Member]

We have a separate security model that allows access to be granted at varying levels of the BUSUNIT hierarchy, with lower members inheriting the access of their parents. To clarify, as it pertains to this example, we use this only to provide a warning to users if they are about to send data to a BUSUNIT they do not have write access to. This is instead of EPM throwing an error after the save is attempted.

[former_member186338]

Really strange idea... Why not to use normal BPC security???

[Former Member]

Not disagreeing there 🙂

It does give our groups very fine control over what data can be seen/edited by all users, which for our main model is a few thousand people.

[former_member186338]

I have another idea but I need a detailed scenario of you security setup with samples...

[Former Member]

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.