README

Star schema dimensional modeling for Laravel. Define fact tables, dimension tables, and query analytics data with a fluent API.

Works with MySQL, PostgreSQL, and SQLite.

Installation

composer require skylence/laravel-star-schema

Publish the config file:

php artisan vendor:publish --tag="star-schema-config"

Run migrations:

php artisan migrate

Seed the date dimension:

php artisan star-schema:seed-dates

Quick Start

1. Define a Fact

A fact represents a measurable business event — an order, a payment, a stock movement.

namespace App\StarSchema\Facts;

use App\Models\Order;
use Illuminate\Database\Eloquent\Builder;
use Skylence\StarSchema\Contracts\FactDefinition;

class SalesOrderFact implements FactDefinition
{
    public function name(): string
    {
        return 'sales_orders';
    }

    public function sourceModel(): string
    {
        return Order::class;
    }

    public function query(): Builder
    {
        return Order::query();
    }

    public function measures(): array
    {
        return [
            'total_amount' => 'Total Amount',
            'quantity'     => 'Item Count',
        ];
    }

    public function dimensions(): array
    {
        return [
            'customer_id' => CustomerDimension::class,
            'product_id'  => ProductDimension::class,
        ];
    }

    public function degenerateDimensions(): array
    {
        return [
            'order_number' => 'Order Number',
        ];
    }

    public function dateColumn(): string
    {
        return 'ordered_at';
    }

    public function grain(): string
    {
        return 'One row per order';
    }
}

2. Register It

In config/star-schema.php:

'facts' => [
    App\StarSchema\Facts\SalesOrderFact::class,
],

3. Query It

use Carbon\CarbonImmutable;
use Skylence\StarSchema\StarQuery;
use Skylence\StarSchema\Enums\Range;

// Revenue per day for the last 30 days
$trend = StarQuery::fact('sales_orders')
    ->between(
        CarbonImmutable::now()->subDays(29),
        CarbonImmutable::now(),
    )
    ->perDay()
    ->sum('total_amount');

// Returns Collection<TrendValue> with gap-filled dates
foreach ($trend as $point) {
    echo "{$point->date}: {$point->value}\n";
}

// Using preset ranges
$trend = StarQuery::range('sales_orders', Range::Last30Days)
    ->perWeek()
    ->avg('total_amount');

// Single scalar value
$total = StarQuery::fact('sales_orders')
    ->between($from, $to)
    ->scalar('total_amount');

// Growth rate vs previous period
$growth = StarQuery::fact('sales_orders')
    ->between($from, $to)
    ->growthRate('total_amount');
// => ['current' => 15000, 'previous' => 12000, 'growth' => 25.0]

Fluent Query API

All time-series methods return Collection<TrendValue> where each TrendValue has date (string) and value (float|int) properties.

Date Ranges

The Range enum provides common presets:

Artisan Commands

# Seed date dimension (2020-2035 by default)
php artisan star-schema:seed-dates
php artisan star-schema:seed-dates --start-year=2015 --end-year=2040
php artisan star-schema:seed-dates --fiscal-start=4  # April fiscal year

# Aggregate facts into snapshot rows
php artisan star-schema:aggregate
php artisan star-schema:aggregate --fact=sales_orders --grain=monthly
php artisan star-schema:aggregate --from=2025-01-01 --to=2025-01-31

# Sync dimension tables from source models
php artisan star-schema:sync-dimensions
php artisan star-schema:sync-dimensions --dimension=customer

# Prune old snapshots based on retention config
php artisan star-schema:prune

Configuration

See the full configuration reference.

Key settings in config/star-schema.php:

return [
    'table_prefix' => 'star_',        // Table name prefix
    'connection'    => null,           // Dedicated analytics DB connection

    'date_dimension' => [
        'start_year'             => 2020,
        'end_year'               => 2035,
        'fiscal_year_start_month' => 1,   // 1=Jan, 4=Apr, 7=Jul
        'locale'                 => null, // 'nl', 'fr', 'de', etc.
        'holidays'               => [],   // Array or callable
    ],

    'retention' => [
        'daily'     => 90,    // Keep daily snapshots for 90 days
        'weekly'    => 365,   // Keep weekly snapshots for 1 year
        'monthly'   => null,  // Keep forever
        'quarterly' => null,
        'yearly'    => null,
    ],

    'facts'      => [],  // Register FactDefinition classes
    'dimensions' => [],  // Register DimensionDefinition classes
];

Advanced Usage

• Defining Dimensions — Dimension definitions, SCD types, hierarchies
• Snapshot Aggregation — Pre-computed snapshots, scheduling, retention
• Database Adapters — Multi-database support and custom adapters
• Configuration Reference — Full config options

Database Support

Testing

composer test

License

MIT