Calls vs. Sales

[RichardDBA]

Hi.
I’m having a conceptual problem with tracking sales vs. call center calls.
Each record in the fact table represents a call to the call center.
In this record are various facts and foreign keys that map to marketing campaigns, etc.
The product sold ID is NULL on no sale, and filled in for a sale.
When creating MDX to retrieve data by campaign, for example, to track the number of calls for a campaign vs. it’s sales, that works fine (because there’s a column labeled sale that’s either 0 or 1 for the sale and I just sum it). This way I get the conversion percentage of calls to sales.

But when creating MDX to track the product sold, as soon as I do a crossjoin on campaign and products sold, for example, I lose the total calls- the number of calls is the same as the sales (similar results as a SQL join). Am I doing something wrong or is it conceptually impossible to track this type of metric down to the product when there are NULLs in the fact table? I’ve tried converting the nulls to a NONE category, but that doesn’t stop the crossjoin from not giving me the desired results, plus I then have to filter out NONE as a product.
Is that why a lot of sample warehouses have a separate sales cube?

Thank you,
Richard

[Rawhide]

Post your query that includes the cross join.

[RichardDBA]

Ok a little update:
Sorry for the delay. I wanted to make sure I was describing correcly the problems I’m having.

Here is the sample MDX:
select
[Measures].allmembers on columns,
NONEMPTYCROSSJOIN ( [Media].[Media].members , [SalesItems].[Description].allmembers) on rows
from MediaSalesByProduct

The Media dimension is a multi-table dimension where calls and sales are rolled up for about 4 levels.
This works well and I can navigate up and down the dimension. Looks good.
When I crossjoin with SalesItems (products sold), the result looks fine until I want to find out the calls of the parents. Then I get stuck as there seems to be an invisible crossjoin with even the parents!
When I ask the parent what the total calls are (for conversion rates), it comes back with info for each parent/SalesItem combination. MDX:
Member measures.parentname as '[Media].CurrentMember.parent.properties("name")' -- finds the right parent!
MEMBER Measures.MediaSales as '([Measures].[Sales], [Media].CurrentMember.parent)' -- I want the aggregate but I don’t get it!

I guess fundamentally I’m hung up on these concepts:
1) Joining a null on a dimension limits the facts (and the whole cube) to just the non-nulls.
2) Converting the null to an “unknown” gives me all the facts but… when I crossjoin with a product sold dimension I still lose all the non-null (or unknown) records in the mdx output.
3) If I build just a sales cube to remove 1 & 2 above, and crossjoin dimensions, I still have trouble getting aggregate amounts from the parents.

There’s got to be a way to track "possible sales" vs. "sales" and conversion rates for all dimensions and their levels.

Well that’s it.
Help if you can and I’ll appreciate it.
Richard