Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
44
Views
7
Comments
1322506
1322506 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:52PM in Oracle Analytics Cloud and Server

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

pastedImage_0.png

pastedImage_1.png

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    "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
    Robert Angel Rank 8 - Analytics Strategist

    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
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    1322506 Rank 3 - Community Apprentice

    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
    1322506 Rank 3 - Community Apprentice

    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
    Thomas Dodds Rank 8 - Analytics Strategist

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

  • 1322506
    1322506 Rank 3 - Community Apprentice

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