Planning Analytics

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 4 days ago
    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
    ------------------------------