SQL
ARCHITECTURE

Microsoft SQL Server Foundation

Pyramid's entire data platform is built on Microsoft SQL Server, one of the most trusted and widely deployed relational database engines in enterprise computing. This foundation provides the transactional integrity, query performance, and operational tooling that large-scale retail planning demands.

Every module in the Pyramid suite — from Merchandise Planning and Assortment through to Allocation and Replenishment — operates against a shared SQL Server database, ensuring one version of the truth across the entire planning lifecycle.

Relational Data Model

The Pyramid data model is designed around the core hierarchies of retail merchandising:

• Product hierarchy: Department, Class, Subclass, Style, Colour, Size — fully configurable to match each retailer's structure
• Location hierarchy: Channel, Region, Store — with support for store grading, attributes, and demographic segmentation
• Time hierarchy: Year, Season, Month, Week — supporting both pre-season planning and in-season forecasting
• Measure hierarchy: Sales, Stock, Intake, Margin, Open-to-Buy, Markdown — with custom calculation sets and formulae

This relational schema supports the full WSSI (Weekly Sales, Stock & Intake) process, enabling planners to manage sales, stock, intake, Open-to-Buy, permanent markdown, promotional discounts, and margin at every level of the hierarchy.

OLAP & Analytics Cubes

Pyramid integrates deeply with Microsoft SQL Server Analysis Services (SSAS) to deliver multidimensional analytics capabilities. The Analytics Multidimensional Data Warehouse sits at the heart of the platform, providing:

• Custom reporting with the same planning formulae available in the analytics data
• Dashboards via built-in BI tools, SSRS Reports, and Power BI integration
• SmartPlanning Suite integration for advanced forecasting and demand analysis
• Microsoft Excel pivot table connectivity for familiar ad-hoc analysis
• Cube Browser for direct OLAP exploration

The analytics cubes serve as the data source for forecasting algorithms, allocation methods, and replenishment calculations — ensuring that every automated decision is grounded in the same analytical foundation used by planners.

Forecasting & Data Mining

The SQL architecture supports Pyramid's forecasting engine through Data Mining Structures built on the analytics database. The forecasting process works as follows:

• The application requests a forecast with parameters: Product, Location, Period, and Measures
• The selected algorithm (Average Weekly Sales or Machine Learning) processes historical data from the analytics warehouse
• Forecasts incorporate sales curves, weekly weights, seasonality patterns, and lost sales adjustments
• Results are saved back to the database and made available across all modules

Supported forecasting methods include Time Series (Classic Multiplicative Model), Exponential Smoothing, Regression Trend Analysis, and Moving Weighted Average. The platform's roadmap includes additional Machine Learning algorithms using Python with feedback loops.

Integration & Interoperability

Pyramid's SQL-based architecture integrates with the broader enterprise technology stack:

• SmartRetail or third-party ERP systems for order management and master data synchronisation
• Microsoft Excel for direct pivot table connectivity and data export
• Power BI for advanced visual analytics and executive dashboards
• SSRS (SQL Server Reporting Services) for operational and scheduled reports
• Automated Purchase Order generation from Allocation and Replenishment calculations

All integration points leverage standard SQL Server protocols, making Pyramid compatible with existing enterprise data pipelines, ETL processes, and data governance frameworks.

Scalability & Performance

The SQL architecture is designed to scale with the world's largest retailers. Performance optimisations include:

• Efficient query execution across millions of SKU-store-week combinations
• Batch processing for replenishment calculations and forecast generation
• Configurable scheduling for automated tasks (daily, weekly, or custom intervals)
• Job monitoring with full history tracking and task status visibility
• Support for concurrent users across planning, allocation, and analytics workstreams

Pyramid has been battle-tested for over 20 years, powering merchandising operations for brands like Nike, Primark, Under Armour, and Converse — retailers with complex global supply chains and high-volume SKU portfolios.