RPD - Model a LTS table source to only be used for a subset of customer ids - 12c

1322506

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

Thomas Dodds

"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."

Sounds like fragmentation ....  https://www.rittmanmead.com/blog/2007/06/obiee-data-modeling-tips-2-fragmentation/

Robert Angel

Mine was the initial same thought, but the note of "should only be used when a certain dimensional field (degenerate dimension) is being used" bothered me - usually when I have modelled fragmentation it is time sliced, like a union (all) query between two distinct data sets, with each having full data for its periods.

Fighting man flu at present but this model did not seem wholly sound to me, am I missing something in my ague?

Thomas Dodds

You're not off base ... the degenerate dimension part is a little tricky ... depends on how the whole thing is modeled ... and yes, usually have see fragmented fact based on ranges of time (ie: don't fetch from massive history table unless use is requesting historical data) which keeps the usable row counts down and performance up!

1322506

Fragmentation does not work from the perspective of time slice.  But perhaps fragmentation by a subquery of a certain disinct set of cusomer ids.  Not sure if this is feasible and works or how.  But a thought I am going to entertain. 

Thoughts>

1322506

And we have many aggregrates for that logical fact table.  I remember something around you cannot fragment where there are multiple aggregates under the LTS

Thomas Dodds

True ... but the way you have formed your ask this is doesn't fit strictly in this star ... you need a new star.

1322506

You are correct unfortunately.  Working with our dw team to see if they can snowflake there.  Or create a new star.