All topics
12 / 55
RPDbeginner

Physical Layer Joins & Connection Pools

Understand how physical table joins, keys, and connection pools are configured to enable correct and efficient query generation.

The BI Server trusts the physical layer completely. A join declared here is used to generate SQL, and if it is wrong the SQL is wrong.

Physical joins are like the actual plumbing between rooms in a house — you can't just declare two rooms connected on a floor plan (BMM) if there's no real pipe between them. Connection pools are like separate water lines for the kitchen versus the sprinkler system, so watering the lawn doesn't cut off the kitchen's water pressure.

Key Concepts

1
And wrong is usually silent.
    a missing join condition  -> rows multiply. Totals are too high.
    the wrong join type       -> rows disappear. Totals are too low.
2
Neither produces an error. The dashboard renders, the numbers are wrong, and somebody reconciles them against another system weeks later.
3
Joins are declared, not inferred. The Administration Tool can import foreign keys from the database and propose joins, which is the right starting point — but many warehouses have no declared constraints, so the joins are entered by hand from knowledge of the data.
4
A complex join expresses the condition explicitly, including multi-column and non-equality conditions.
5
Connection pools define how the server reaches the database.
    connect string
    credentials, or pass-through
    maximum connections
    call interface and isolation level
6
Several pools against one database is normal, and the reason is isolation:
7
    interactive pool   50 connections, short timeout -- dashboards
    batch pool         10 connections, long timeout  -- scheduled reports
8
Without the split, one scheduled report running for forty minutes can hold connections that interactive users need, and the dashboards queue behind a report nobody is watching.
9
Pass-through against a service account is the design question interviewers ask.
10
    shared service account   the database sees one user. Simple.
                             All row-level security must then live in
                             the RPD, because the database cannot
                             distinguish who is asking.
    pass-through             each user's own database credentials.
                             Native database security applies, and
                             the database log shows who ran what.
11
The trade is real. Pass-through gives you the database's own security and auditing, and costs connection pooling — a pool per user is not a pool — plus every user needing a database account.
12
Most deployments use a service account and enforce security in the RPD, which is why row-level security there is a topic of its own.
13
One practical note. Connection pool credentials are stored in the RPD, which is why the RPD file is treated as a secret and why access to the Administration Tool is controlled.
14
What the interviewer is probing.1. "What is a connection pool for?" Probing: the mechanism. Stalls: "Connections." *Moves up:* holding the data source connection, credentials and settings such as maximum connections and the call interface.
15
2. "Why have more than one connection pool per source?" Probing: the practical design. Stalls: "No reason." Moves up: to separate initialisation blocks from user queries, so a heavy query does not starve logins — and to apply different limits.
16
3. "What is the difference between a physical and a complex join?" Probing: the distinction. Stalls: "Nothing." Moves up: physical joins are foreign key relationships between physical tables; complex joins express a relationship by expression rather than fixed key columns.
17
4. "What is an alias table and why use one?" Probing: the modelling discipline. Stalls: "Renaming." Moves up: a second reference to the same physical table so each role, such as order date and ship date, has its own join path without ambiguity.
Use cases:physical-layerconnection-poolsjoins