how to force a hierarchy to be refreshed thru javascript?

#1

Hi,

I have a Hierarchy that lists countries -> country zones -> places used in a filter in a Dashboard, BUT I need to be able to allow my end-users to add places if they are not already listed, adding the new value to the table is not an issue, but as far as I can see the Hierarchy is not reloaded when the value is added, even if I set the corresponding DataCube to ignore the cache. Is there a way to force refresh the content of the Hierarchy in order to have the brand new value added showing up in the filter? I also use the very same Hierarchy for a Bridge Parameter to be passed to a Stored Procedure, that’s why it’s the Hierarchy itself that has to be refreshed and not just the dropdown menu in the Dashboard.
I guess that the DataCube has to be refreshed first and then the Hierarchy, but I have no clue on how can I do both thru javascript. :man_shrugging:
Thanks in advance for any help.
Olivier

#2

What is the storage type of your data cube ?

Regards,
Ric

#3

Hi Ric, The DataCube is set with no storage at all, and to ignore the cache, in order to have the data reloaded each time, but it seems that once loaded the Hierarchy doesn’t reload unless you edit it. The DataCube is built on a SQL query.
I have tried to add values manually to the SQL table, and unless I edit the Hierarchy, the new values were not showing up, even if the DataCube is set to ignore the cache, it seems that the Hierarchy doesn’t reload as soon as a value has been added. But why?? That’s why I ask for a way to force refresh the Hierarchy (just as if I was performing a check-out, check-in in fact) but thru script, in order for me to invoke it from the Dashboard, as soon as a new value is added by an end-user.

#4

The discrepancy in behavior and the fact that the hierarchy isn’t instantly updated in your filters is actually due to the fact that hierarchies are cached separately from data result sets. The most common form of caching and the one most refer to when mentioning bypassing/disabling caching from within the UI would be the results/data cache. The lesser known cache we have is the hierarchy cache, which is what would be leading to the exact behavior you’re seeing.

While it isn’t something that we expose in the filter control properties, there is a bypass hierarchy cache property that you can access via script. That said, it only is exposed on the hierarchy value picker/explorer. It isn’t exposed to the metric set/data cube.
https://www.dundas.com/support/api-docs/js/#API%20Reference/dundas/controls/ParameterHierarchyPicker/Properties/bypassHierarchyCache.html

Otherwise, the alternative is to disable the hierarchy cache outright instance-wise through the config setting (Hierarchy Cache Absolute Expiration can be set to zero). Of course, that could come with potentially significant performance hits – just something to keep in mind.

image
image.png817×452 9.99 KB

#5

Thanks Christian, I’ll give it a try on Tuesday, when I’ll be back from my travel.