I am trying to build multiple hierarchies, each being the previous one with one extra level.
The context is that I have some data with a 3-level hierarchy. The 1st level has 500 elements, the 2nd 5000 and the last one 6M and counting. I think I could build one hierarchy only based on the lowest level and use whichever level is relevant in my data cubes, but as I do not always use the 3rd level, I am afraid that reading 6M rows to have only 5k uniques is a massive waste of time. I already in the past had issues with text search in the hierarchy filters.
This is my top level, L1:
create table if not exists l1 (
id serial primary key
, name varchar(256) not null unique
)
I made a cube out of it and a hierarchy out of the cube. Easy peasy.
This is my 2nd level, l2:
create table if not exists l2 (
id serial primary key
, l1_id int references l1
, cid int not null
, name varchar(256)
, unique (l1_id, cid)
I made a cube out of it, and promoted l1_id to the l1 hierarchy I defined just before. So far so good.
When I am trying to make a hierarchy out of this cube, l1 being top level and l2 being the 2nd level, I cannot find a way to get the name defined in the l1 hierarchy to be the caption of the l1 level. You can see that the top level only has IDs, the second level has a proper caption.
Is there a way to do what I want?
Cheers,