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
------------------------------
#PlanningAnalyticswithWatson