Merge 2 facts in cube?

Question

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

Answer 1

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]

Answer 2

If you can use Mondrian 4 you can specify the exact SQL statement that should be executed to retrieve data for fact table. Therefore you can do the join on the database level, like below:

 <PhysicalSchema>
    <Query alias="FACT">
        <ExpressionView>
            <SQL dialect="generic">
                select f.*, f2.measure_2 from FACT f INNER JOIN FACT2 f2 ON f.id = f2.fact_id
            </SQL>
        </ExpressionView>
    </Query>
</PhysicalSchema>

See the full example here: http://thejavatar.com/mondrian-4-in-pentaho-bi-server/