Topic Thread

Expand all | Collapse all

How best to support filtering of dimension members by attribute values, or by values from a dimension in another cube?

  • 1.  How best to support filtering of dimension members by attribute values, or by values from a dimension in another cube?

    Posted Wed September 11, 2019 02:47 PM
    I am trying to come up with the most optimal design of a set of cubes to support the use case below.

    The objective is to derive Cost of a Facility based upon its attributes.

    Attribute examples: Start Date, Type, etc.

    I am able to create a Cost cube that displays the Cost, which is based upon rules within the cube that use the Facility attributes as inputs.

    For example: If Start Date < 1/1/2002 and Type = 'Well', Cost = $100K

    So far it has been straightforward.

    I also need to provide the ability to filter on the facilities based upon their attribute values.

    For example, show me the Cost of facilities of Type ''Battery'.

    I don't see a way to filtering using attribute values.

    I could consider a hierarchy based on each attribute, but the challenge would be that I would need multiple such alternate hierarchies - concern is that I would only be able to filter on one hierarchy and not multiple at the same time (or can I and how?)

    I could combine all hierarchies into one mega-hierarchy -

    Type -> Start Date -> xxx

    For a total of 15 attributes, this would be cumbersome - also the structure would not be flexible as I am structuring them in a fixed way.

    An alternate design I have considered is one where Facility and Attributes (as one dimension, or each attribute as a separate dimension) belong in a separate Attributes cube, entirely separate from the cost cube (Cost cube has Facility and Cost metrics as its only dimensions).

    I could potentially filter on an attribute type here and narrow down on the Facility. 

    I would need to join the results of the filtering on the Facility Attributes Cube with the Cost cube, in a single Cube view, to make this work.

    Questions:

    1. Is there a way to filter on member attribute values  (e.g., Type = ''Battery") in a Cube View and how do I do it?
    2. Is it possible to filter members in one Cube View based upon the filtered results of another Cube? (I.e., two synchronized views with a common Facility element - one set of Facilities is filtered based on values in the Attribute cube, resulting in the Facilities getting filtered in the Cost view)
    3. Is there another way I could design a solution for this use case?

    ------------------------------
    Shubho Ghosal
    ------------------------------


  • 2.  RE: How best to support filtering of dimension members by attribute values, or by values from a dimension in another cube?

    Posted Tue September 17, 2019 02:08 PM
    Hi,
    1.  As long as your are using PAW, you can have multiple hierarchies for the same dimension in the same view.  Once you have created the attribute hierarchies, simply click on the dimension and click add related hierarchy, and you now have another hierarchy you can use as a filter or display in the view based on the attribute.  
    2. Play with the "Synchronize" in PAW if you want selections to be the same across cube views in PAW.



    ------------------------------
    Scott Brown
    ------------------------------



  • 3.  RE: How best to support filtering of dimension members by attribute values, or by values from a dimension in another cube?

    Posted Thu September 19, 2019 10:37 AM
    Thanks, Scott, for your suggestions. Will be sure to try the alternate hierarchies out.

    ------------------------------
    Shubho Ghosal
    ------------------------------