Fact missing data and facts listing a row per row linked to bridge table, is it the normal behaviour?

[brian-barba-hernandez]

Hi there,

I recently started using the cubes library and it's been very useful so far. Aggregates are working as expected, I can get the results when slicing and cutting and the library behaves and performs nicely.

I'm using the slicer server under a docker container for testing purposes. Recently I have to turn a dimension having hstore into a key value model using a bridge for the multiple values.

Aggregates keep working as expected but, when getting a single fact I only get a single row from the bridge, I guess it's because it only returns a "dimension.key" and "dimension.value" when I was expecting something like:

"dimension": [
  {
    "key": "key1",
    "value": "value1"
  },
  {
    "key": "key2",
    "value": "value2"
  },
...
  {
    "key": "keyn",
    "value": "valuen"
  }
]

When listing facts I get the same fact several times and each one have a different "dimension.key" with the corresponding "dimension.value" depending on the volume in the bridge table.

I followed the instructions to join the bridges as stated in https://cubes.readthedocs.io/en/latest/schemas.html?highlight=bridge#many-to-many-relationship

Is that the normal behaviour due to some limitations or may I have something wrong?

[ThamaluM]

@brian-barba-hernandez It's very difficult to understand your problem. Better if you can provide the parts of your model that configure the dimension and the relevant joins. Add the result you got along with the expected results

[brian-barba-hernandez]

@ThamaluM Well, I can't provide the exact model due to an NDA but I can provide an example: I have some facts and then a many to many relationship for dictionaries. So I have:

{
  "dimensions": [
    "date_created", "date_modified", "dynamicproperties"
  ],
...
  "joins": [
    {
      "master": "dim_dynamicproperties_group_id",
      "detail": "bridge_dynamicproperties.dynamicproperties_group_id"
    },
    {
      "master": "bridge_dynamicproperties.dynamicproperties_id",
      "detail": "dim_dynamicproperties.dynamicproperties_id"
    }
]
...

And the dimension is defined like this:

{
  "name": "dynamicproperties",
  "label": "Dynamic Properties",
  "description": "Entity containing the keys and values",
  "info": {},
  "levels": [
    {
      "name": "key",
      "label": "Dynamic Property Key",
      "key": "key",
      "nonadditive": "any",
      "attributes": ["key"]
    },
    {
      "name": "value",
      "label": "Dynamic Property Value",
      "key": "value",
      "nonadditive": "any",
      "attributes": ["value"]
    }
  ],
  "hierarchies": [
    {
      "name": "properties",
      "levels": ["key", "value"]
    },
    {
      "name": "key",
      "levels": ["key"]
    },
    {
      "name": "value",
      "levels": ["value"]
    }
  ],
  "default_hierarchy_name": "properties"
}

When querying the fact I don't get all keys and values, only one. When listing the facts I get the same fact as many times as keys and values stored in dimension.

[ThamaluM]

I think I understood your problem. When bridge tables and dimension tables present final queries are performed on a joined table. When the tables joined it's the original table with repeat rows for each corresponding dimension values. Then what you got can be expected. I think this is the normal behavior. Whole idea of fact table is to have a 1NF normalized (atomic facts) table. Then a single fact in the final representation will not show several keys which can be divided into further rows. I suggest you may define a dimension on fact table primary key and use cut through it to get what you need. (Shortly, your expected result cannot be recorded as a single record/fact in a Relational database.)

[brian-barba-hernandez]

@ThamaluM Thanks for your response, I thought bridges would be handled differently to the dimensions when joining the tables. Now I got the clear picture that I cannot achieve what I need.