---
title: OLAP Use Case
layout: default
parent: Getting Started
published: false
nav_order: 3
---
# OLAP Analytics
This guide demonstrates an Online Analytical Processing (OLAP) workflow using
Oxibase: creating a persistent database, defining a schema, loading data from a
file, performing analytical queries, and exporting results.
## Prerequisites
- Oxibase installed (see [Installation]({% link _docs/tutorials/installation.md %}))
- A CSV file with sample data (we'll create one)
## Step 1: Create a Persistent Database
Create a database file instead of using in-memory mode:
```bash
mkdir -p data
oxibase --db "file://./data/analytics.db"
```
This starts Oxibase with persistent storage in `./data/analytics.db`.
## Step 2: Create Schema
Define tables for sales data:
```sql
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price DECIMAL(10,2)
);
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
region TEXT
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
quantity INTEGER,
unit_price DECIMAL(10,2),
sale_date DATE,
product_id INTEGER,
customer_id INTEGER
);
```
## Step 3: Load Data from File
{: .warning-title }
> WIP
>
> Loading data from file is not yet supported. Please follow [this
> issue](https://github.com/oxibase/oxibase/issues/21) to get
> updates on the status of this feature.
Create sample data files and load them:
First, create `products.csv`:
```
id,name,category,price
1,Laptop,Electronics,999.99
2,Mouse,Electronics,29.99
3,Book,Media,19.99
4,Chair,Furniture,149.99
5,Headphones,Electronics,79.995,Headphones,Electronics,79.99
```
Create `customers.csv`:
```
id,name,email,region
1,Alice Johnson,alice@example.com,North
2,Bob Smith,bob@example.com,South
3,Carol Davis,carol@example.com,East
4,David Wilson,david@example.com,West
5,Eve Brown,eve@example.com,North
```
Create `sales.csv`:
```
id,product_id,customer_id,quantity,unit_price,sale_date
1,1,1,1,999.99,2024-01-15
2,2,1,2,29.99,2024-01-16
3,3,2,1,19.99,2024-01-20
4,4,3,1,149.99,2024-02-01
5,5,4,1,79.99,2024-02-05
6,1,5,1,999.99,2024-02-10
7,2,2,1,29.99,2024-02-15
8,3,3,3,19.99,2024-02-20
9,4,1,1,149.99,2024-03-01
10,5,5,2,79.99,2024-03-05
```
Load the data (assuming CSV files are in the current directory):
```sql
COPY products FROM 'products.csv' WITH CSV HEADER;
COPY customers FROM 'customers.csv' WITH CSV HEADER;
COPY sales FROM 'sales.csv' WITH CSV HEADER;
```
## Step 4: Analyze Data
Perform analytical queries:
### Sales by Category
```sql
SELECT
p.category,
COUNT(s.id) as total_sales,
SUM(s.quantity * s.unit_price) as total_revenue,
AVG(s.quantity * s.unit_price) as avg_sale_amount
FROM sales s
JOIN products p ON s.product_id = p.id
GROUP BY p.category
ORDER BY total_revenue DESC;
```
### Customer Analysis with Window Functions
```sql
SELECT
c.name,
c.region,
COUNT(s.id) as purchase_count,
SUM(s.quantity * s.unit_price) as total_spent,
RANK() OVER (ORDER BY SUM(s.quantity * s.unit_price) DESC) as spending_rank,
LAG(SUM(s.quantity * s.unit_price)) OVER (ORDER BY c.name) as prev_customer_total
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
GROUP BY c.id, c.name, c.region
ORDER BY total_spent DESC;
```
### Time-based Analysis
```sql
SELECT
strftime('%Y-%m', sale_date) as month,
COUNT(*) as monthly_sales,
SUM(quantity * unit_price) as monthly_revenue,
SUM(SUM(quantity * unit_price)) OVER (ORDER BY strftime('%Y-%m', sale_date)) as cumulative_revenue
FROM sales
GROUP BY strftime('%Y-%m', sale_date)
ORDER BY month;
```
### Top Products by Revenue
```sql
WITH product_revenue AS (
SELECT
p.name,
p.category,
SUM(s.quantity * s.unit_price) as total_revenue,
COUNT(s.id) as sales_count
FROM products p
JOIN sales s ON p.id = s.product_id
GROUP BY p.id, p.name, p.category
)
SELECT
name,
category,
total_revenue,
sales_count,
ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as revenue_rank
FROM product_revenue
ORDER BY total_revenue DESC;
```
## Step 5: Export Results
Export analytical results to CSV:
{: .warning-title }
> WIP
>
> Export data to a file is not yet supported. Please follow [this
> issue](https://github.com/oxibase/oxibase/issues/21) to get
> updates on the status of this feature.
```sql
-- Export sales summary by category
COPY (
SELECT
p.category,
COUNT(s.id) as total_sales,
SUM(s.quantity * s.unit_price) as total_revenue
FROM sales s
JOIN products p ON s.product_id = p.id
GROUP BY p.category
ORDER BY total_revenue DESC;
) TO 'category_sales.csv';
-- Export customer analysis
COPY (
SELECT
c.name,
COUNT(s.id) as purchase_count,
SUM(s.quantity * s.unit_price) as total_spent
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;
) TO 'customer_analysis.csv'
WITH (
FORMAT CSV,
HEADER,
DELIMITER ',',
ENCODING UTF8
);
-- Export top products by revenue
COPY (
WITH product_revenue AS (
SELECT
p.name,
p.category,
SUM(s.quantity * s.unit_price) as total_revenue,
COUNT(s.id) as sales_count
FROM products p
JOIN sales s ON p.id = s.product_id
GROUP BY p.id, p.name, p.category
)
SELECT
name,
category,
total_revenue,
sales_count,
ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as revenue_rank
FROM product_revenue
ORDER BY total_revenue DESC;
) TO 'top_products.parquet' WITH (FORMAT PARQUET, COMPRESSION SNAPPY);
```