BI Tools: Tableau, Power BI, and Looker Questions

One-to-many (1:*):

• Cardinality: one row in Table A matches zero or many rows in Table B. In Power BI it’s shown as “1” on the one-side and “*” on the many-side.
• Ambiguity causes: duplicate keys on the “one” side or multiple active relationships between same tables creating ambiguous filter flow.
• Example: DimCustomer (CustomerID unique) → Sales (CustomerID duplicates). Use for transactions, master-detail reporting; filters flow from DimCustomer to Sales.

One-to-one (1:1):

• Cardinality: one row in A matches at most one row in B; shown as “1” — “1”.
• Ambiguity causes: accidental 1:1 where keys aren’t enforced (hidden duplicates), or circular relationships when chaining multiple 1:1s.
• Example: Employee → EmployeeDetails where each employee has exactly one details row. Often safe but verify uniqueness before setting.

Many-to-many (:):

• Cardinality: rows in A can match multiple in B and vice-versa; Power BI can show “” — “” or be implemented via a bridging (intermediate) table.
• Ambiguity causes: direct : creates ambiguous filter directions and double-counting; lack of a proper bridge table or composite keys.
• Example: Products and Promotions where a product can be in many promotions and a promotion can include many products. Best practice: create a bridge (ProductPromotion) with single-direction relationships or use composite/unique keys and manage filter direction.

General tips:

• Prefer star schema: dimension tables unique (1) and fact tables many (*).
• Resolve ambiguity with bridge tables, deactivate extra relationships and use USERELATIONSHIP, or set single-direction filters when appropriate.
• Always validate uniqueness in the “1” side before declaring cardinality.

USERELATIONSHIP activates an inactive relationship inside a CALCULATE so time‑intelligence or filtering can use an alternate date without changing the model. It does not permanently change the model — it only applies for that measure evaluation.

Measure to compute total sales by ShipDate (assumes there's an active relationship Sales[OrderDate] → Calendar[Date] and an inactive Sales[ShipDate] → Calendar[Date]):

Total Sales by ShipDate := 
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], Calendar[Date])
)

Explanation:

• CALCULATE changes filter context; USERELATIONSHIP temporarily activates the inactive ShipDate→Calendar date link.
• Visuals using Calendar[Date] will then show sales aggregated by ship date instead of order date.

Edge cases / notes:

• Ensure Sales[Amount] exists; use SUMX if Amount is computed.
• If multiple inactive relationships are needed, add multiple USERELATIONSHIP calls inside CALCULATE.
• USERELATIONSHIP only activates one relationship between two columns — it won’t create virtual joins across tables.

Approach (summary)

• Row-level security (RLS) in Looker: use user_attributes + access_filters to restrict what rows a user can query.
• Column-level security (CLS): hide sensitive fields via model/explore permissions, conditional fields, or access_grants combined with derived tables and field-level permissioning.
• Centralize roles with access_grants for reusable permission groups.

Implementation details

1. User attributes

• Create user_attributes for organization_id, region, role, data_sensitivity_level.
• Populate via SSO (SAML/OIDC) or scheduled sync from HR/IDP.

2. Row-level: access_filters on explores/models

• In the model or explore, add access_filter referencing a user_attribute:

# model.lkml (example)
explore: orders {
  access_filter: {
    field: orders.organization_id
    user_attribute: organization_id
  }
}

• For multi-value filters, use contains_any or join permissions table.

3. Access Grants (for complex joins / derived tables)

• Define access_grant with allowed values; reference in derived tables or sql_where to enforce at SQL level:

access_grant: region_access {
  user_attribute: regions
}

• Use yesno or match to gate sensitive explores/fields.

4. Column-level security

• Remove sensitive fields from public explores and create a restricted explore (group-limited) that includes them.
• Or make fields conditional:

dimension: ssn {
  sql: ${TABLE}.ssn ;;
  hidden: no
  access_grant: sensitive_data_access
}

(Use access_grant to hide/show fields.)

Audit & Testing Plan

1. Inventory: catalog all sensitive tables/columns and required row filters.
2. Unit tests: create LookML tests for required access_filters present on explores and for presence of access_grants on sensitive fields.
3. Automated smoke tests: for each persona (admin, manager, analyst, external), run queries against key explores and assert returned rows/columns match expected (use API to run queries programmatically).
4. Penetration tests: attempt access via API and via SQL runner impersonating user_attributes to validate enforcement.
5. Dashboard-level checks: verify tiles don’t expose unauthorized fields by testing dashboards in each role context.
6. Monitoring / logging: enable query logging, monitor anomalies (users accessing unexpected regions/columns), and schedule quarterly audits.
7. Deployment gating: require security review and automated tests pass before merging LookML changes.

Key reasoning

• Use user_attributes for dynamic per-user policies; access_filters convert those to SQL WHERE clauses so enforcement happens in DB.
• access_grants centralize complex permission lists and allow field gating.
• Combining LookML controls with source-of-truth user attributes + automated tests and logs ensures robust, auditable security across explores and dashboards.

To calculate Total Sales in a single base currency, handling nulls and using exchange rates, you can create a measure like this:

Total Sales (Base) =
SUMX(
    Sales,
    COALESCE( Sales[Amount], 0 )                // treat null amounts as 0
    * COALESCE( RELATED( ExchangeRate[Rate] ), 1 )  // convert using related rate; default 1 if missing
)

Explanation (plain language):

• SUMX(Sales, ...): iterates row-by-row over the Sales table and sums the expression for each sale. Use SUMX because we need to convert each row's amount before aggregating.
• COALESCE(Sales[Amount], 0): replaces null/blank Amount values with 0 so they don't break the sum.
• RELATED(ExchangeRate[Rate]): pulls the matching exchange rate from the ExchangeRate table; this assumes a relationship exists from Sales[Currency] → ExchangeRate[Currency].
• COALESCE(..., 1): if no rate is found, default to 1 to avoid null multiplication (you may prefer to return BLANK() or flag missing rates depending on business rules).

Alternative if no relationship exists:

Total Sales (Base) =
SUMX(
  Sales,
  COALESCE(Sales[Amount],0)
  * COALESCE(
      LOOKUPVALUE( ExchangeRate[Rate], ExchangeRate[Currency], Sales[Currency] ),
      1
    )
)

Notes:

• Ensure ExchangeRate[Rate] represents rate to your chosen base currency and is up-to-date.
• Consider returning BLANK() when a rate is missing to surface data quality issues instead of silently using 1.

A star schema is a dimensional model with a central fact table (transactional, numeric measures) surrounded by denormalized dimension tables (descriptive attributes). Power BI’s VertiPaq favors this shape because its columnar, dictionary-encoded storage and in-memory engine get maximum compression and fastest scan/aggregation on wide, read-optimized tables.

Core components:

• Fact table: contains facts/measures and foreign keys to dimensions. Grain must be explicit (e.g., one row = one order line).
• Dimension tables: user-friendly attributes (Customer, Product, Date). Typically one row per business entity.
• Grain: the atomic level of analysis; determine which measures are valid and which dims join.
• Relationships: usually one-to-many (dimension 1 → fact *). Also many-to-many use bridging tables, and inactive relationships can be activated in DAX.

Why VertiPaq favors star:

• Compression: repeating attribute values in dimensions and low-cardinality columns compress extremely well with dictionary encoding.
• Query performance: queries scan only needed columns; simple 1:* joins across keys are implemented efficiently via in-memory storage and aggregate pushdown.
• DAX simplicity: measures written against a single fact with filter context from dimensions are straightforward (SUM, CALCULATE). Avoids complex joins and LOOKUPVALUE overhead.

Example mapping (commerce):

• Fact_OrderLine (grain: one order line) — OrderLineID, OrderID, CustomerID, ProductID, DateKey, Quantity, UnitPrice, LineTotal
• Dim_Customer — CustomerID, CustomerName, Region, Segment
• Dim_Product — ProductID, SKU, Category, Brand, Price
• Dim_Date — DateKey, Date, Year, Quarter, Month, DayOfWeek

Typical measure:

• Total Sales = SUM(Fact_OrderLine[LineTotal])
  With this star, slicers on Dim_Date or Dim_Product filter the fact efficiently, yielding fast visuals, small model size, and simple, maintainable DAX.