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 have a Fact / Fact Less table that has a list of Products likley to be purchased by client.

  • Client ID Product ID
  • 1 1
  • 1 2
  • 2 2
  • 2 5
  • etc...

I need to to allow the users to find clients that have , from the example, the 2 product ID that does not have a 1 Product listed. I get a file from the source system that is very simialr to the table above and I know I could create a table that looks like this below (assumign I only have a few products, and I do in this case...):

  • Client ID Product ID Listed
  • 1 1 Y
  • 1 2 Y
  • 2 2 Y
  • 2 5 Y
  • 1 3 N
  • 1 4 N
  • 1 5 N
  • 2 1 N
  • 2 3 N
  • 2 4 N

I could have two Role Playing Dimension based on the Listed flag that would all them to say show me all the clients that are Listed in 2 but not Listed in 1. However, I'm sure there is a much better way.

share|improve this question

migrated from stackoverflow.com Feb 20 '12 at 17:01

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

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
  • BloggsJ and JonesB want to buy Widgets

  • SmithF wants to buy a Dohickey but not a Widget

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.

share|improve this answer
add comment

Your Answer

 
discard

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