# Combining retail sales and payment OLAP cubes

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.

• What is the definition of "products that were paid with certain type of payment" given that there is no direct link between the two (is it that products in any transaction that included a certain type of payment are considered to be paid with that payment type, for example)? May 10, 2011 at 10:54
• Yes, exactly, products in any transactions that includes a certain type of payment are considered to be paid with that payment type. May 10, 2011 at 11:42

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.

• But what about the other measures like quantity and gross? Won't they be multiplied too? You mean it can't be combined into the same cube? May 10, 2011 at 13:14
• yes they will be multiplied too - how would you want them to be treated? May 10, 2011 at 13:22
• Actually measures like quantity and gross are more important than this request, so if the solution skews quantity or gross (the more important figures), I'd rather not use OLAP and create a report from the OLTP just for this request. I'm new with OLAP and still don't know the limits of OLAP. May 10, 2011 at 13:30
• OLAP is not going to give different results to any other approach - basically it is just about pre-computing aggregates (ie summaries). The query "List down all products that were paid with certain type of payment", if it is including quantities, it needs to choose how to split them between payment types - you could for example proportion them based on the proportion of each payment type in the transaction, but only you know how you want this done, and the questioon is the same whatever approach you use May 10, 2011 at 13:35
• OK I got your point. So in this case I need to cross join detail with payment, then recalculate quantity, gross, etc to its proportion so when summed, the total remains the same. Will this be feasible? The tables have millions of rows. May 10, 2011 at 15:10

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
``````
• I think you can dispense with the `select top 1 1` and just use `select *`. If you prefer a join (and databases are good at joins so you might find it is faster) you can `left outer join` what you have with something like `select distinct sales_id, 1 as IsPaidWithVoucher from salespayments where paytypeid = 6` and drop the subquery. Just note that 'IsPaidWithVoucher' is either 1 or `null` rather than 1 or 0. May 11, 2011 at 7:53