All topics
16 / 55
RPDadvanced

Logical Table Sources & Fragmentation

Explain how fragmentation content rules split logical fact/dimension data across multiple physical sources (e.g., by region or date range) for query efficiency.

Aggregate navigation splits data by grain. Fragmentation splits it by content — the same grain, different subsets, in different physical tables.

It's like a library that keeps this year's newspapers at the front desk for quick access but ships everything older than a year to an off-site archive — a librarian (the BI Server) knows exactly where to look based on the date you ask for, and can even combine both sources if your request spans the boundary.

Key Concepts

1
The usual case is time.
    logical fact "Sales"
      LTS "Current"    -> F_SALES_CURRENT   (this year, fast storage)
      LTS "Historical" -> F_SALES_ARCHIVE   (prior years, cheaper
                                             storage, possibly another
                                             database)
2
Content filters tell the server which holds what.
    LTS "Current"     WHERE Time.Year = VALUEOF(CURRENT_YEAR)
    LTS "Historical"  WHERE Time.Year <  VALUEOF(CURRENT_YEAR)
3
Then the server routes by the query's filters:
4
    a query for this year          -> Current only
    a query for 2019               -> Historical only
    a query for 2019 to now        -> both, and the results are
                                      combined
5
That last case is the point. The report author asked one question and the server issued two queries against two tables — possibly in two databases — and unioned them.
6
Without fragmentation, the report author has to know which table to use, and a query spanning the boundary needs a hand-written union that breaks when the boundary moves.
7
Other reasons to fragment.
    by region      data resident in different countries, for
                   regulatory reasons
    by business    a merged company whose two halves are still on
                   separate systems
    by source      part of the entity from one application, part
                   from another
8
Fragmentation must be complete and non-overlapping, and this is where it goes wrong.
9
    a gap      some data is in no LTS -> those rows silently vanish
    an overlap some data is in two    -> those rows are double-counted
10
Neither raises an error. The filters are a declaration the server trusts, exactly like content levels and physical joins.
11
So the boundary condition matters: if "current" means this year and the archive job runs in February, there is a window where January's data is in neither, and the numbers for January are simply wrong until somebody notices.
12
The setting to know. Fragmentation can be declared as complete — meaning the LTSs together cover everything — which lets the server query only the relevant ones. Declared incomplete, it queries all of them and combines, which is correct and slower.
13
What the interviewer is probing.1. "What is fragmentation content used for?" Probing: the mechanism. Stalls: "Splitting." Moves up: telling the server which slice of data each source holds — current year in one table, history in another — so it queries only what it needs.
14
2. "What happens if the fragmentation is declared wrong?" Probing: the consequence. Stalls: "An error." Moves up: double counting or missing rows, because the server either queries both sources or the wrong one.
15
3. "What is the difference between value-based and complex fragmentation?" Probing: the detail. Stalls: "Nothing." Moves up: value-based splits on a simple column predicate; complex covers arbitrary expressions, with the complexity flag telling the server whether sources overlap.
16
4. "When would you use several logical table sources?" Probing: the fit. Stalls: "Never." Moves up: aggregates, fragmented history, and federating the same measure across two physical systems.
Use cases:fragmentationpartitioningadvanced-modeling