In order to do what you're thinking of, you would have to make a factless fact table with the cartesian product of all possible combinations of products and the customers. If you have a big portfolio of products this could get quite large.
In fact, you're asking a question roughly equivalent to WHERE NOT EXISTS in SQL, which you won't get out of a cube through straight slice-and-dice operations. However, you can query it with MDX. If you make up a little database like the one shown below and build a cube over it:
create table DimCustomer (
CustomerID int not null
,Customer varchar (50)
)
go
create table DimProduct (
ProductID int not null
,Product varchar (50)
)
go
create table FactSalesLeads (
CustomerID int not null
,ProductID int not null
,PlaceHolder int not null default 1
)
go
insert DimCustomer (CustomerID, Customer) values (1, 'BloggsJ')
insert DimCustomer (CustomerID, Customer) values (2, 'SmithF')
insert DimCustomer (CustomerID, Customer) values (3, 'JonesB')
go
insert DimProduct (ProductID, Product) values (1, 'Widget')
insert DimProduct (ProductID, Product) values (2, 'Dohickey')
insert DimProduct (ProductID, Product) values (3, 'Whoflungdung')
go
-- BlogsJ wants to buy a Widget and a Dohickey
insert FactSalesLeads (CustomerID, ProductID) values (1, 1)
insert FactSalesLeads (CustomerID, ProductID) values (1, 2)
-- SmithF wants to buy a Dohickey but no Widget
insert FactSalesLeads (CustomerID, ProductID) values (2, 2)
-- JonesB wants to buy a Widget and a Whoflungdung
insert FactSalesLeads (CustomerID, ProductID) values (3, 1)
insert FactSalesLeads (CustomerID, ProductID) values (3, 3)
go
Now, put up a cube over the database with Customer and Product dimensions and a measure group for the sales leads. A MDX query like the following would show you which customers wanted to buy Widgets but not Dohickeys:
// Doing a 'where not exists' in MDX with set operations
//
with
// Customers likely to buy a Widget
set [WidgetLeads]
as {filter ([Customer].[Customer].Children
,[Product].[Widget])}
// All customers except those likely to buy a Dohickey
set [NotInterestedInDoHickey]
as {except ([Customer].[Customer].Children
,{filter ([Customer].[Customer].Children
,[Product].[Dohickey])})}
// Just a bit of fluff so the list goes down the columns
select [Measures].[Place Holder]
on Columns
// Intersection of the sets gives leads for a Widget who are
// not expected to purchase a Dohickey
,intersect ([WidgetLeads], [NotInterestedInDoHickey])
on Rows
from [Leads]
The process is slightly roundabout in MDX because it does not have a 'WHERE EXISTS` operator. You can do it in terms of set operations though, as demonstrated in the snippet of MDX.
Some OLAP front-end tools, such as the late and much lamented Proclarity would allow the user to define sets in the tool and then do the operation manually. If you don't have access to that I can't easily think of a way that materially improves on MDX queries like this one.
One option would be to build a report that takes the combination of products you want as parameters and returns the list. You could also implement that in SQL.
You could also possibly do something with a dynamic named set in SSAS2008+ where you calculated the set of products not present in the customers' preferences. This would be computed as the total set of all products minus the total set of products filtered by the preferences in the set of customers.
You can calculate the sets of customers by selecting those with a particular preference or set of preferences. Showing the non-empty members of the non-preferred list, perhaps filtered by some specific subset of interest would allow you to show them along a different axis.
This would adjust to producing a list of products where no customers in the selected set were considered to be leads. Unfortunately I don't have a running SSAS2008 instance to hand to demo this, so it's left as an exercise to the reader.
