Aggregation of facts, use as dimension

View previous topic View next topic Go down

Aggregation of facts, use as dimension

Post  us1 on Tue Apr 21, 2009 8:01 pm

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.

us1

Posts : 5
Join date : 2009-04-14

View user profile

Back to top Go down

Re: Aggregation of facts, use as dimension

Post  BoxesAndLines on Wed Apr 22, 2009 11:18 am

How about making Customer a dimension?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Aggregation of facts, use as dimension

Post  us1 on Wed Apr 22, 2009 11:34 am

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?

us1

Posts : 5
Join date : 2009-04-14

View user profile

Back to top Go down

Re: Aggregation of facts, use as dimension

Post  Kateryna on Wed May 06, 2009 3:09 pm

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

Kateryna

Posts : 5
Join date : 2009-05-05

View user profile

Back to top Go down

Re: Aggregation of facts, use as dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum