We have a Dimension. The Dimension represents a full collection of codes (Lets say 10 000 codes).
For querying purposes, lets say we needed 500 codes for a specific KPI out of the Dimension. It will take a long time to select only the codes we need to filter by.
We decided to create a kind of 'rule table' that contains only the codes we need for a specific KPI (lets say 500 of the codes were selected from the dimension and a relationship is created in the cube between the rule table and the dimension). Thus we should be able to pull in that table as the filter. But it does not quite work.
The relationship:
Dimension: primary_key and the rest of the columns (including 'Code' that is used for KPIs)
Fact_Table: foreign_key to the dimension's primary_key.
Rule_Table: foreign_key to the dimension's primary_key and 'Code' as column.
Firstly can this work? and then..
Which will be a better option to use. A individual View or a individual table for every KPI's codes (Rule Table)?
Or is there a better way to do this?
I would design a KPI dimension with it's own key, granularity is one row per KPI. Then I would design a 'rule bridge' table which has foreign keys to both the existing dimension and KPI dimension.