Calculated attributes in Customer Dimension?

View previous topic View next topic Go down

Calculated attributes in Customer Dimension?

Post  boernard on Wed Mar 28, 2012 8:53 am

Hi there,

I am building a DWH for an e-commerce shop. The customer dimension is connected to the transaction fact table and i am wondering if I should implement calculated figures in the customer dimension.
For example:
customer_lifetime_value
total_revenue_by_customer
total_amount_of_returns
abc_analysis_segment
...

Is that ok to do so or are there any pitfalls?

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  BoxesAndLines on Wed Mar 28, 2012 9:34 am

The pitfall is you have to update it every time you receive a new transaction.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  ngalemmo on Wed Mar 28, 2012 10:04 am

Segmentation analysis I presume...

Updating a presumably very large customer table on a periodic basis can be unwieldy on most DBMS platforms. You may want to consider 'shadow' dimensions. Basically, another table with the same PK as the dimension it is shadowing. Or, another way to think of it, is as an aggregate fact table with only one dimension FK.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  boernard on Wed Mar 28, 2012 12:09 pm

Thanks for the reply! Yes, thought of that "shadow dimension". I think I will put all the information in the customer dimension.

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re:Calculated attributes in Customer Dimension?

Post  hkandpal on Wed Mar 28, 2012 9:01 pm

How frequently you plan to update the summary number, will it be as the detail is updated for a customer or will it be once a day or weekly or monthly ? How big is your customer table and How many transactions are happening.

thanks

Himanshu

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  boernard on Thu Mar 29, 2012 3:25 am

I plan to update the figures every night. We have aroun 20-40k customers, numbers growing. The calculation itself shouldn't be a too big burden for the server as i plan to implement this in PLpython / pandas (big recommendation for analysis by the way!)

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  umutiscan on Thu Mar 29, 2012 4:02 am

One pitfall is having a rapidly changing dimension because of rapidly changing attributes.

Customer facts like total_revenue_by_customer, total_amount_of_returns etc might be put in a fact table and calculated periodically. This will be useful for trend analysis and some aggregation operations.

If you can easily calculate customer_lifetime_value, I don't prefer to put this information as a new column on customer dimension. This may be calculated at reporting level with a simple database function.

I recommend you to read the article below

http://www.kimballgroup.com/html/articles_search/articles1999/9908bIE.html?TrkID=IE199908_1

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Often changed dimension information

Post  cpv on Mon Apr 23, 2012 8:38 am

Simple scenario:
Customer dimension
FACT table of sales activity (purchases, credits - NOT payments, applied transactions, etc.)

If I wanted more information about the customer e.g. high balance, avg days to pay then I would have two options:

(1) Create aggregate formula of purchases, credits, applied payments, etc. and then update dimension each time customer id transactions change
(2) Create separate aggregate fact table with two columns - high balance, avg days to pay with one primary key linked to customer dimension - and one record for each key

It appears that solution number 2 would be more efficient than having to update the dimension each time a transaction for that dimension occurs, whereas in option number 2, only the secondary fact table changes.

Are there any downfalls with option number 2(e.g. we now have two fact tables - sales, and aggregate customer payment info). Am I even looking at this correctly?

cpv

Posts : 1
Join date : 2012-04-23

View user profile

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  umutiscan on Mon Apr 23, 2012 11:46 am

If two facts are required, you have to create them.
But I don't like to create too much aggregated facts, If the data is not huge or aggregated facts are not used often.




umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Calculated attributes in Customer Dimension?

Post  ngalemmo on Mon Apr 23, 2012 2:48 pm

CPV, option 2 is technically correct. What is being described is an aggregate fact table with one dimension (the customer).

It's a matter of deployment: Which structure performs better for typical queries? That will depend a lot on specific hardware, so it would need some testing to pick the best choice.

Also, the solution does not work very well if customer is a type2 dimension. You wind up having to aggregate on the natural key, and then wind up choosing to generate a fact table based on the type2 key or simply updating the dimension table using the natural key. The latter may take longer, but if it helps query performance, it may be worth the bother.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Calculated attributes in Customer 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