Passing a hierarchy parameter to a data cube

#1

I have a data cube, based on an SQL query, with public parameters/placeholders.

The idea is to have filters in a dashboard to pass those parameters to the cube. It works fine for single parameters (eg. enter a number). My issue is when those parameters need to come from a hierarchy.

This is basically my set up:

  • data cube with a query like ‘select … where level1=$level1$ and level2 = $level2$’ (not that level1 and level2 are not part of the output of the cube)
  • data cube consisting of all levels of my hierarchy (which actually goes up to level3)
  • dashboard which wants to read the hierarchy data cube to pass 2 levels in one go to the data cube

Setting up my parameters (dashboard parameters or ‘connect filter’ on my value filter), I have no way to bind them to the cube parameters (it kinda makes sense I suppose, as they are not the same type). Is there a way to do this?

Alternatively, as I have an “update filter” button, so if there is no UI way, I will script this - get the parameters from my filter and pass them to the data cube, as a click action.

1 Like
#2

You’re almost exactly describing the purpose of our bridge parameters, i suggest giving this page a go. This is definitely more of an advanced concept but it’s extremely flexible and you can basically do whatever you need.

https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/using-a-bridge-parameter

Bridge parameters can be added to a data cube to be inserted between a transform’s parameter and/or where the parameter values come from. You can use a bridge parameter to convert values from a hierarchy or another type not directly compatible with your query or transform before passing them. For example, a hierarchy member filter on a metric set or dashboard selects an entire hierarchy member, which your database query or stored procedure won’t be able to recognize without first converting it.

#3

Thanks Jeff, this is precisely what I was looking for, it works flawlessly and quite easily as well.

You were not joking a few months ago, when for quite a few of my questions you answered “you can do everything and more with scripting” :slight_smile:

1 Like