The SQL table has 5 columns

Key-part-1: Integer

Key-part-2: Integer

Metric-1: double

Metric-2: double

The Unique Key is Key-part1 + Key-part-2

A couple common queries which are executed on the table are:

(1) Select Key-part-2 from MyTable where Key-part-1 = 10;

(2) Select Metric-1 from MyTable where Key-part-1 = 10;

(3) Select Metric-2 from MyTable where Key-part-1 = 10 and key-part-2 = 100;

I am trying to think through the best way to convert this table into palo dimensions and cube:

Option 1: Dense Cube

Dimension-1 = values for Key-part-1

Dimension-2 (Metrics Dimension) = values Key-part-2, Metric-1, Metric-2

*I have tried this approach, but when inserting into the cube I end-up over-writing the previous values i.e. palo seems to assume that Dimension-1 is a unqiue-key. So when executing select (1) always get only 1 answer.

Option 2: Sparse Cube

Dimension-1 = values for Key-part-1

Dimension-2 = values for Key-part-2

Dimension-3 (Metrics Dimension) = values Metric-1, Metric-2

*Have tried this approach, but select (1) seems to giving strange results: for a key-part-1 of 450 dimension elements and key-part-2 of 900 dimension elements of which only 2 metrics have been populated for each key-part-1 value. When I retrieve the metrics, using key-part-1, I get 826 results - while I expected either 2 or 900. This feels like a defect.

Option 3: Dummy Dimension to create a Dense Cube

Dimension-1 = values for Key-part-1

Dimension-2 (Dummy Dimension) = Create a new set of dimension values based on the number of metrics to be populated, to ensure the Dimension-1 + Dimension-2 is unique.

Dimension-3 (Metrics Dimension) = values Key-part-2, Metric-1, Metric-2

*Have not tried this: seems like a long way around

Option 4: Option 2 + DFilter

Use option 2 (sparse cube) and then use a dimension filter to retrieve Key-part-2.

*Have not tried this: I wish I knew if I can filter-out empty values.

Any advice on how best to approach this?