Is it possible to have complex queries on flattened tables referencing dimension tables?

zunzun Community Edition User

So I have a dimension table like this:

create table my_dimension_table
(
    main_key           int NOT NULL,
    linked_id              int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (main_key) ENABLED
 )

and my flattened table is like so

create table my_flattened_table
(
    mod_date          date,
    some_value      varchar(50),
    main_key           int NOT NULL,
    linked_key.       DEFAULT USING (SELECT CASE WHEN (my_dimension_table.linked_id IS NULL) THEN my_dimension_table.main_key ELSE my_dimension_table.linked_id END
                                           FROM my_dimension_table
                                           WHERE (my_flattened_table.main_key = my_dimension_table.main_key)),

 )

This doesn't seem to work. Is there something similar I can do to achieve this?

So background on what I'm trying to do here:
Basically we perform complex queries on my_flattened_table.linked_key and do many group by. This field needs to be equal to my_dimension_table.linked_id when its not null, otherwise equal to my_dimension_table.main_key.

Any suggestions appreciated!

Answers

  • That's not supported, but you could try creating two column references in your flattened table to both of the columns in the dimension. A flattened table reference has to return only 1 record, but it will just return null if it joins on nothing, which would still be OK.

  • marcothesanemarcothesane - Select Field - Administrator

    I think you can achieve it using a NVL() function. If a flattened column is NULL because the matching - found - column of the joined row in the dimension table is NULL, then your expression works.
    If it is NULL because the join fails, the flattened column remains NULL.

    We are in the lucky situation where the replacement of the flattened column is the join column, so we can use the fact table's join column as the second parameter of an NVL() function. See the whole scenario here.

    drop table if exists my_dimension_table CASCADE;                                                                                                                                                          
    -- out DROP TABLE
    create table my_dimension_table
    (
        main_key           int NOT NULL,
        linked_id          int,
        CONSTRAINT C_PRIMARY PRIMARY KEY (main_key) DISABLED
     )
    ;
    -- out CREATE TABLE
    
    drop table if exists my_flattened_table CASCADE;
    -- out DROP TABLE
    create table my_flattened_table
    (
        mod_date        date
      , some_value      varchar(50)
      , main_key        int NOT NULL
      , linked_key      INT DEFAULT USING (
           SELECT CASE WHEN my_dimension_table.linked_id IS NULL THEN my_dimension_table.main_key ELSE my_dimension_table.linked_id END
           FROM my_dimension_table
           WHERE my_flattened_table.main_key = my_dimension_table.main_key
        )
      , linked_key_nvl   INT DEFAULT USING (
           NVL((SELECT my_dimension_table.linked_id FROM my_dimension_table WHERE my_flattened_table.main_key = my_dimension_table.main_key),my_flattened_table.main_key )
        )
     )
    ;
    -- out CREATE TABLE
    
    INSERT INTO my_dimension_table
              SELECT 1,11
    UNION ALL SELECT 2,NULL::INT
    UNION ALL SELECT 4,42
    ;COMMIT;
    -- out  OUTPUT 
    -- out --------
    -- out       3
    -- out COMMIT
    
    INSERT INTO my_flattened_table(mod_date,some_value,main_key)
              SELECT DATE '2022-08-05','should have 11', 1
    UNION ALL SELECT DATE '2022-08-06','should have  2', 2
    UNION ALL SELECT DATE '2022-08-07','should have  3', 3
    UNION ALL SELECT DATE '2022-08-08','should have 42', 4
    ;COMMIT;
    -- out  OUTPUT 
    -- out --------
    -- out       4
    -- out (1 row)
    -- out COMMIT
    
    SELECT * FROM my_flattened_table order by 1;
    -- out   mod_date  |   some_value   | main_key | linked_key | linked_key_nvl 
    -- out ------------+----------------+----------+------------+----------------
    -- out  2022-08-05 | should have 11 |        1 |         11 |             11
    -- out  2022-08-06 | should have  2 |        2 |          2 |              2
    -- out  2022-08-07 | should have  3 |        3 |            |              3
    -- out  2022-08-08 | should have 42 |        4 |         42 |             42
    
  • zunzun Community Edition User

    thanks will try this out!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file