Aggregating Many To Many

Question

Aggregating Many To Many

Rick Todd

Hall of Fame

Points: 3302
More actions
June 22, 2007 at 1:36 pm

#117449

I guess I didn't explain myself very well when I posted this the first time. Maybe a pseudo-diagram will help.
Here's a very rough outline of what the fact table would look like:
SaleLocationCode int FK,
SaleDate int FK,
SaleTime int FK,
ProductPackageCode int FK,
ProductCode int FK,
PaymentMethodCode int FK,
DiscountCode int FK,
AmountOfSale decimal(16,2)
Quantity int
So, the SaleLocation, SaleDate, SaleTime, and ProductPackage are all 1 to 1, as in 1 line in the Fact table for each combination. The Quantity also appears at this level. So if I had a fact table that consisted only of those, I'd be all set, as Quantity would be additive, and would only appear once for each line.
Unfortunately I need the ProductCode dimension for further analysis, and the AmountOfSale, which appears at the same level. This means that I'll now have 1 to many SaleLocation, SaleDate, SaleTime, and ProductPackage entries, and therefor Quantity entries for each ProductCode/AmountOfSale entry.
To further complicate matters, I can have 0 to many DiscountCodes and PaymentMethods, causing 0 to many rows for all of the above.
So given this, how do I keep the AmountOfSale and Quantity additive? I wrote some SQL in the extract (well, Transform really) to divide the AmountOfSale and Quantity by the number of rows in the many-to-many relationships, but then I end up with rounding errors.
I guarantee I'm not the first person to have seen this problem, so thanks in advance for enlightening me!

Rick Todd

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

So if I understand you correctly, you have multiple facts which are actually the same barring a few columns which separate them?

if so, would using a multi cast, get distinct rows, aggregating the distinct rows, and re-merge not work?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Rick Todd Hall of Fame Points: 3302 More actions · Answer 2

I'll try that today Crispin, thanks. I figure it's not a unique problem, I was just hoping someone could guide me in the right direction. Hopefully you have.

Rick Todd