# `Selecto.Builder.Sql.Olap`

OLAP dimension optimization SQL patterns for star and snowflake schemas.

Provides optimized JOIN patterns for analytical workloads, focusing on
fact table performance and dimension table efficiency. Handles both
star schema (denormalized dimensions) and snowflake schema (normalized
dimension hierarchies) patterns.

Phase 4: Full OLAP dimension implementation with query optimization

## Supported Patterns

- **Star schema dimensions**: Direct fact-to-dimension joins optimized for aggregation
- **Snowflake dimensions**: Multi-level normalization chains with proper JOIN ordering
- **Dimension filtering**: Optimized WHERE clause placement for analytical queries
- **Fact table hints**: Query hints and ordering for large fact table performance

## Star vs Snowflake

**Star Schema** - Denormalized dimensions for query performance:
```
fact_table -> dimension_1 (all attributes in one table)
           -> dimension_2 (all attributes in one table)
```

**Snowflake Schema** - Normalized dimensions for data integrity:  
```
fact_table -> dim_level_1 -> dim_level_2 -> dim_level_3
```

## Examples

    # Star schema: sales facts with denormalized customer dimension
    config = %{
      type: :star_dimension,
      source: "customers",
      display_field: "full_name",
      dimension_key: "customer_id"
    }
    
    # Snowflake schema: product hierarchy with normalization
    config = %{
      type: :snowflake_dimension,
      source: "products", 
      display_field: "name",
      normalization_joins: [
        %{table: "categories", key: "category_id"},
        %{table: "brands", key: "brand_id"}
      ]
    }

# `build_dimension_filter_optimization`

Build dimension-aware WHERE clause optimization.

In OLAP queries, WHERE clause placement significantly affects performance.
Dimension filters should be applied early, while fact table filters
need careful consideration of index usage.

## Filter Placement Strategy
- Dimension filters: Applied at JOIN time for early elimination
- Fact filters: Applied after JOINs for optimal fact table index usage  
- Time dimension filters: Special handling for partitioned fact tables

## Examples

    build_dimension_filter_optimization(%{
      dimension_filters: [
        {"customers.region", "=", "North America"},
        {"products.category", "IN", ["Electronics", "Books"]}
      ],
      fact_filters: [
        {"sales.amount", ">", 1000},
        {"sales.date", "BETWEEN", ["2023-01-01", "2023-12-31"]}
      ]
    })

Returns: `{optimized_where_iodata, filter_params}`

# `build_fact_table_optimization`

Build fact table optimization hints and JOIN ordering.

Fact tables in OLAP systems are typically very large, so JOIN ordering
and query hints are critical for performance. This function adds
database-specific optimizations for fact table queries.

## Optimizations Applied
- Fact table scanned first (for selective WHERE conditions)
- Dimension tables joined in order of selectivity
- Query hints for large table handling
- Index hints for dimensional foreign keys

## Examples

    build_fact_table_optimization(selecto, :sales_facts, %{
      large_fact_table: true,
      primary_dimensions: [:time, :customer, :product],
      estimated_rows: 10_000_000
    })

Returns: `{query_hints_iodata, optimization_params}`

# `build_olap_join_with_optimization`

Build OLAP-optimized join with pattern detection.

Main entry point for OLAP join building. Detects whether to use
star or snowflake patterns based on configuration and applies
appropriate optimizations.

## Pattern Detection
- Star schema: Single dimension table with denormalized data
- Snowflake schema: Multiple normalization tables in chain

Returns: `{from_clause_iodata, params, ctes}`

# `build_snowflake_dimension_join`

Build snowflake schema dimension join with normalization chain.

Snowflake schemas normalize dimension data across multiple tables to
maintain data integrity. This requires chaining multiple JOINs to
reconstruct the full dimensional context.

## Normalization Chain Handling
- Primary dimension table joined to fact
- Secondary normalization tables joined in sequence
- Proper JOIN ordering to avoid Cartesian products
- Optimized for referential integrity queries

## Parameters  
- `selecto`: Main selecto struct
- `join`: Primary dimension identifier
- `config`: Snowflake dimension configuration with normalization_joins
- `fc`: Current from clause iodata
- `p`: Current parameters list
- `ctes`: Current CTEs list

Returns: `{updated_from_clause, updated_params, updated_ctes}`

# `build_star_dimension_join`

Build star schema dimension join optimized for OLAP queries.

Star schemas prioritize query performance by denormalizing dimension data
into single tables. This creates direct fact-to-dimension joins that are
optimal for aggregation queries and analytical workloads.

## Optimizations Applied
- Dimension tables joined directly to fact table
- Dimension filters pushed down for early elimination  
- Display fields aliased for clear result presentation
- Faceted filtering enabled for interactive analytics

## Parameters
- `selecto`: Main selecto struct (contains fact table info)
- `join`: Join identifier (dimension name)
- `config`: OLAP dimension configuration  
- `fc`: Current from clause iodata
- `p`: Current parameters list
- `ctes`: Current CTEs list

Returns: `{updated_from_clause, updated_params, updated_ctes}`

---

*Consult [api-reference.md](api-reference.md) for complete listing*