Our RPD model uses many aggregate tables per LTS for performance and routing reasons. Those aggregates are rollups or detail so the model satisfies "routing" for the a combination of fields to the right table. We also use the Priority Groups in the LTS fact table to further guide the black box behavior.
I now have 2 tables (same but used because they are partitioned for different date keys)
Core Question:
The 2 tables should only be used when a certain dimensional field (degenerate dimension) is being used AND only for a subset of customer ids. Those tables do not contain all customers. How do I do that properly.
However most of the columns that are already mapped for the other physical tables in the LTS also are available. I don't want to model a mirror fact and duplicate all those columns.
Currently I have it modelled as FACT LTS aggregates, joined to the customer dimension and one special field is mapped in the Fact Attribute Degenerate dimension. So sometimes it uses those fact tables when customer ids are filtered in a query that do not exist in that aggregate fact table.
Fact LTS source
