Mixing data at different levels

Question

I've edited to rephrase this question:

We want to store trade and sub-trade data. So to give an idea of data, we have input data like this:

Trade Data (Relational Store)

| TradeKey1 | TradeLevelMeasure1 |
| TradeKey2 | TradeLevelMeasure2 |

Sub-Trade Data

| TradeKey1 | SubTradeId1 | Measure2 | Measure3 |
| TradeKey1 | SubTradeId2 | Measure2 | Measure3 |    
| TradeKey2 | SubTradeId1 | Measure2 | Measure3 |    

Any we're looking for the best solution to model this in AP.

If we use relational stores to create 2 stores with the same layout as above (with the Sub-Trade store being the main ACTIVE_PIVOT store) and join them based on TradeKey, then we end up aggregating incorrectly on the Trade-Level measures, as the trade is copied into the cube for each Sub-Trade entry. (E.g. TradeLevelMeasure1 has double the correct value as it exists in the cube for both sub-trade entries)

A solution we've come up with is to use a single store, and add a new dimension to indicate the trade level (Trade or SubTrade). So we get something like this:

| Trade    | TradeKey1 | TradeLevelMeasure1 |             |          |          |
| SubTrade | TradeKey1 |                    | SubTradeId1 | Measure2 | Measure3 |
| SubTrade | TradeKey1 |                    | SubTradeId2 | Measure2 | Measure3 |
| Trade    | TradeKey2 | TradeLevelMeasure2 |             |          |          |
| SubTrade | TradeKey2 |                    | SubTradeId1 | Measure2 | Measure3 |

This way we should be able to aggregate correctly and then expand on the sub-trade measures when required.

Does this seem like a viable solution, or is there a better way to achieve this?

Answer 1

Your dataset contains measures that do not have the same dimensionality, and thus do not belong to a single cube and its fixed topology.

Measures defined at the trade level only aggregate among trades, while sub-trade measures only make sense in the context of sub-trades.

The ActivePivot Distributed Architecture offers an elegant way to federate heterogeneous cubes on the fly: ActivePivot Polymorphic Distribution.

In short you will define two simple cubes, one at the trade level, with the trade measures, and one at the sub-trade level that aggregates only sub-trade measures. ActivePivot Polymorphic Distribution will merge them on the fly into a virtual cube, joining on the shared dimensions while also making available the unique measures in each cube.

You will find a presentation of ActivePivot Distributed Architecture, from the Quartet FS User Group 2012 at http://www.youtube.com/watch?v=VnZoelJulM4. For documentation you can start at http://support.quartetfs.com/confluence/display/AP4/ActivePivot+Distributed+Architecture.