Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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)? –  Jack Douglas May 10 '11 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. –  Endy Tjahjono May 10 '11 at 11:42
add comment

2 Answers

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.

share|improve this answer
    
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? –  Endy Tjahjono May 10 '11 at 13:14
    
yes they will be multiplied too - how would you want them to be treated? –  Jack Douglas May 10 '11 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. –  Endy Tjahjono May 10 '11 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 –  Jack Douglas May 10 '11 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. –  Endy Tjahjono May 10 '11 at 15:10
show 2 more comments
up vote 2 down vote accepted

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
share|improve this answer
    
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. –  Jack Douglas May 11 '11 at 7:53
    
+1 looks good to me - I think you should accept your answer :) –  Jack Douglas May 11 '11 at 15:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.