OLAP Cube多事实表场景下，库存周转率KPI计算方案咨询

Great question! You absolutely don't have to merge your sales and inventory fact tables into a single Cube—there are several valid approaches depending on your OLAP tool, data granularity, and performance needs. Let's walk through the options:

Option 1: Use a Multi-Fact Cube #

Most modern OLAP tools (like SSAS, Power BI Premium, or Mondrian) support multi-fact Cubes where you can include multiple fact tables, as long as they share common dimension tables (e.g., Date, Product, Store).

• Set up relationships between each fact table and the shared dimensions (make sure the join keys are consistent, like using the same ProductID or DateKey across both tables).
• Define your measures separately: one from the sales fact table (e.g., Total Sales Amount) and one from the inventory fact table (e.g., Average Inventory Quantity).
• Calculate the inventory turnover directly in the Cube by combining these measures—just remember to align units (e.g., multiply inventory quantity by Unit Cost from the Product dimension to get inventory value, so you're comparing sales value to average inventory value).

This approach keeps your raw fact tables intact and avoids data duplication.

Option 2: Create a Derived Fact Table (Pre-Aggregated) #

If your OLAP tool has limited support for multi-fact structures, or you want to optimize query performance, you can build a derived fact table at the data warehouse layer:

• Write a SQL view or ETL job that aggregates both fact tables to a common granularity (e.g., daily per product). For example:

  SELECT 
    s.DateKey,
    s.ProductID,
    SUM(s.SalesAmount) AS TotalSales,
    AVG(i.InventoryQuantity) AS AvgInventory
  FROM SalesFact s
  JOIN InventoryFact i ON s.DateKey = i.DateKey AND s.ProductID = i.ProductID
  GROUP BY s.DateKey, s.ProductID
  

• Import this derived table into your Cube as a single fact table, then define the turnover measure as TotalSales / (AvgInventory * p.UnitCost) (joining to the Product dimension for unit cost).

This simplifies the Cube structure but adds maintenance overhead (you'll need to refresh the derived table as new data comes in).

Option 3: Calculate in the Frontend Tool #

If you prefer to keep your Cubes focused on single facts, you can connect your frontend analytics tool (e.g., Tableau, Power BI Desktop) to both Cubes (or the multi-fact Cube) and compute the turnover there:

• Ensure both datasets share consistent dimension values to avoid mismatches.
• Create a calculated field in the frontend that combines the sales amount from one dataset and inventory quantity from the other.

Note: This can lead to performance issues with large datasets, and you'll need to be careful to avoid accidental Cartesian products when joining datasets.

When to Merge Fact Tables? #

Merging is only recommended if the two fact tables have exact matching granularity (e.g., each row represents a single transaction that includes both sales and inventory adjustments). If their granularities differ (e.g., sales are individual orders, inventory is daily snapshots), merging will create redundant data or incorrect aggregations—so skip this approach in that case.

Key Considerations #

• Dimension Consistency: Always ensure both fact tables use the same dimension keys (e.g., don't mix ProductID with ProductSKU unless they're mapped correctly).
• Unit Alignment: Inventory quantity and sales amount need to be in the same unit (value) for a meaningful turnover calculation—use your product dimension's cost or price to convert quantity to value.
• Performance: Multi-fact Cubes may require more query processing, while derived fact tables offer faster query times but need ETL maintenance.

内容的提问来源于stack exchange，提问作者Mohammed El Mehdi Kherrachi