How to do dicing and aggregate at the same time across 2 dimensions?

[simkimsia]

This is a question I first posted at StackOverflow

but I thought I will cross-post here to get a faster response.

Situation

Using Python 3, Cubes 1.1, and Postgres 9.5,

These are my datatables in pictorial form

Here is in text format

The relationships are:

1. Store belongs to Sales
2. Product belongs to Sales
3. Sales has many Store
4. Sales has many Product

Store table

------------------------------
| id  | code | address       |
|-----|------|---------------|
| 1   | S1   | Kings Row     |
| 2   | S2   | Queens Street |
| 3   | S3   | Jacks Place   |
| 4   | S4   | Diamonds Alley|
| 5   | S5   | Hearts Road   |
------------------------------

Product table

------------------------------
| id  | code | name          |
|-----|------|---------------|
| 1   | P1   | Saucer 12     |
| 2   | P2   | Plate 15      |
| 3   | P3   | Saucer 13     |
| 4   | P4   | Saucer 14     |
| 5   | P5   | Plate 16      |
|  and many more ....        |
|1000 |P1000 | Bowl 25       |
|----------------------------|

Sales table

----------------------------------------
| id  | product_id | store_id | amount |
|-----|------------|----------|--------|
| 1   | 1          | 1        |7.05    |
| 2   | 1          | 2        |9.00    |
| 3   | 2          | 3        |1.00    |
| 4   | 2          | 3        |1.00    |
| 5   | 2          | 5        |1.00    |
|  and many more ....                  |
| 1000| 20         | 4        |1.00    |
|--------------------------------------|

What I expect

I want to use cubes to be able to do a display by pagination in the following manner:

Given the stores S1-S3:

-------------------------
| product | S1 | S2 | S3 |
|---------|----|----|----|
|Saucer 12|7.05|9   | 0  |
|Plate 15 |0   |0   | 2  |
|  and many more ....    |
|------------------------|

Note the following:

1. Even though there were no records in sales for Saucer 12 under Store S3, I displayed 0 instead of null or none
2. I want to be able to do sort by store, say descending order for, S3
3. the cells indicate the SUM total of that particular product spent in that particular store.
4. i also want to have pagination

What I tried

This is the configuration I used:

"cubes": [
    {
        "name": "sales",
        "dimensions": ["product", "store"],
        "joins": [
            {"master":"product_id", "detail":"product.id"},
            {"master":"store_id", "detail":"store.id"}
        ]
    }
],
"dimensions": [
    { "name": "product", "attributes": ["code", "name"] },
    { "name": "store", "attributes": ["code", "address"] }
]

This is the code I used:

 result = browser.aggregate(drilldown=['Store','Product'],
                               order=[("Product.name","asc"), ("Store.name","desc"), ("total_products_sale", "desc")])

I didn't get what I want.

I got it like this:

----------------------------------------------
| product_id | store_id | total_products_sale |
|------------|----------|---------------------|
| 1          | 1        |       7.05          |
| 1          | 2        |       9             |
| 2          | 3        |       2.00          |
|  and many more ....                         |
|---------------------------------------------|

which is the whole table with no pagination and if the products not sold in that store it won't show up as zero.

My question

How do I get what I want?

Do I need to create another data table that aggregates everything by store and product before I use cubes to run the query?