Combining retail sales and payment OLAP cubes

Question

I'm using SQL Server 2005 Standard Edition Analysis Services (SSAS). I'm trying to build cubes from a retail OLTP database that can answer these kinds of questions:

• List down all products that were paid with certain type of payment (which products are bought with voucher type X, etc)
• Create a breakdown of frequency of payment type used to pay for product X (how many times cash is used to pay for product X, how many times debit, credit, voucher, etc)

There are 3 main transaction tables: SALESHEADERS, SALESDETAILS, and SALESPAYMENTS.

• SALESHEADERS contains sales date, cashier, POS code, etc.
• SALESDETAILS contains header code, product code, quantity, unit price, gross, discount, etc.
• SALESPAYMENTS contains header code, payment type code, payment amount, etc.

One transaction can contain many products. One transaction can be paid with multiple types of payment. There is no connections between detail and payment except through header (sales ID).

Currently I have created 2 cubes, one sales cube and one payment cube.

Sales cube is created by joining the header and detail. Payment cube is created by joining the header and payment.

Both cubes are performing well, but they can't answer the questions at the top of my post.

Answer 1

You need to start off with a cross join of detail and payment, because you say "products in any transactions that includes a certain type of payment are considered to be paid with that payment type", so you are effectively asking for parts of a transaction to be counted multiple times if there are multiple payment types used.

Answer 2

Thanks to @JackPDouglas for the guidance, I think I may have an answer to my own question. It's a hack but I think it will work:

The user is interested only in some types of payments and some types of products. I will attach subquery only for those payment types in the detail view:

select
    case when exists (
        select top 1 1
        from salespayments
        where salesid = D.salesid
        and paytypeid = 6
    ) then 1 else 0 end as IsPaidWithVoucher,
    D.productid,
    D.qty,
    ...
from salesdetails D
inner join salesheaders H on D.salesid = H.id
...

Also some subqueries for products in the payment view. Thoughts?

EDIT

As suggested by @JackPDouglas, the subquery can be moved from the 'select' section to the 'from' section to make it a join:

select
    isnull(P.IsPaidWithVoucher, 0) as IsPaidWithVoucher,
    D.productid,
    D.qty,
    ...
from salesdetails D
inner join salesheaders H on D.salesid = H.id
left outer join (
    select distinct salesid, 1 as IsPaidWithVoucher
    from salespayments
    where paytypeid = 6
) P on D.salesid = P.salesid
...

Or it can be made into a separate view too:

create view SalesPaidWithVoucher as
select distinct salesid, 1 as IsPaidWithVoucher
from salespayments
where paytypeid = 6