Is 1:n easy, hard, or impossible in a cube?

[dparizo]

I'm new to OLAP and have a pretty basic question. I'd like to know if a 1:n relationship is easy to incorporate into a cube. Specifically:

Imagine I have a cube designed primarily on sales transactions. To keep it simple, lets say I have a TransactionID, Date, ProductID, $ amount, and CustomerID to describe the sale. Let's say that this transaction table currently has 1000 rows, which means 1000 sales transactions.

Now, let's say separately, I have a table that links salespeople to the transaction. Two columns, TransactionID and RepID. Because more than one rep can be involved in a sale, this table can have the TransactionID appear any number of times. So, while the transaction table currently has 1000 rows, let's say this table currently has 2200 rows, which means an average of 2.2 reps per transaction.

I want to represent this 1-many relationship in my cube, so that I can:

1) Create a report that totals up to 1000; showing, say, transactions by ProductID.
2) Create a report that totals up to 2200; showing, say, transactions by individual rep.

Is this easy, hard, or impossible in a cube?

Thanks!

 

[MDXer]

You would best served by having your datamart have one row pertransaction per sales rep which will total to your 2200 rows. A distinct count measure on transID will return 1000 distinct transactions while a count on repid would report 2200.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck