Search code examples
schemapentahoolap-cubemondrian

Merge 2 facts in cube?

Is it possible to merge 2 facts tables to create a cube in a Mondrian schema example the case of sales and cost ?

Solution

  • It is usual to have both sales and cost measures in one fact table and add them both as measures to one cube.

    If you cannot have them both in the same fact table, they need to have common dimension(s), so they can be joined together in Virtual Cube (up to Mondrian 3.8). Time dimension is usual:

    DHW

    • Table fact_sales: date_key, ..., sales
    • Table fact_cost: date_key, ..., cost
    • Table dim_date: date_key, day_of_month, month, year, ...

    Mondrian OLAP schema

    • Dimension [Date]: table dim_date, primary key column date_key, level year year, level month ...
    • Cube [Sales]: table fact_sales, dimension [Date] usage date_key, measure sales
    • Cube [Cost]: table fact_sales, dimension [Date] usage date_key, measure cost
    • Virtual Cube [Sales and Cost]: virtual cube dimension [Date], virtual cube measure [Sales.sales], virtual cube measure [Cost.cost]