Aggregation of facts, use as dimension

This example has two facts, each w/ several dimensions:

1) Customer - date created, etc.
2) Purchases - purchase date, price, etc.

Customer grain = individual customer and Purchase grain = one product purchased. I want to setup my data model/schema so that I could setup the following analysis in my cube:

Filter = purchase date
Row label = # of purchases
Measure = # of customers

So essentially I want to look at customers segmented by number of purchases. I cannot turn Customer into an accumulating snapshot w/ a count of purchases because I am interested in restricting purchases to a given date range.

The SQL isn't hard to do:

SELECT Purchases,
COUNT(CustomerID) AS Customers
FROM (
SELECT Customer.CustomerID,
COUNT(Purchase.PurchaseID) AS Purchases
FROM Customer
INNER JOIN Purchase
ON Customer.CustomerID = Purchase.CustomerID
WHERE Purchase.DatePurchased BETWEEN @StartDate AND @EndDate
GROUP BY Customer.CustomerID
) AS d
GROUP BY Purchases
ORDER BY Purchases ASC

How do I accomplish this? Through some sort of intermediate fact table? A bridge table? Any thoughts would be appreciated. Let me know if clarification is needed.

How about making Customer a dimension?

BoxesAndLines wrote:How about making Customer a dimension?

The Customer Fact has several dimensions, e.g. preferences, contact information, industry, etc.

But that still does not answer my question. We actually currently have a "Number of Purchases" attribute in the Customer Dimension but we are unable to apply filters to that aggregate, e.g. we cannot look at Number of Purchases in last month, or Number of Purchases where Price > $50, etc.

Another example would be BankingCustomer and BankingAccounts (two fact tables). I would want to structure that data model to answer questions, e.g. how many Customers had 1/2/3/etc. bank accounts? Then add filters, e.g. where the bank account was created in TX or was opened in Jan-08.

Do these examples make sense? Has anyone worked on a similar problem?

I think the analysis you try to do not for a cube. For the cube it should look like: How many customers fall into category 0-10 purchases, 10-100 purchases, etc during a day, week, month, etc

Then the schema can be something like this

Dim_purchase_category
----------------------
Id
Category

Fact_purchase
--------------
ClientId
PurchaseDate
Price
DayPurchaseCategory_Id
WeekPurchaseCategory_Id
...

Dim_Client
---------
...

The granularity of your original analysis is too much details for the cube and you already have a proper structure for the ordinal SQL (By the way you do not need Client table in the inner SQL). Why do not use a reporting tool and a report on the basis of this SQL to analyze details and a cube for more general picture?

=Kate