Date attributes in non date dimensions

View previous topic View next topic Go down

Date attributes in non date dimensions

Post  abutmah on Tue Mar 19, 2013 3:00 am

I have a case similar to the following:

  • Say that you have a customer dimension which contains attributes related to the customer and a calender hierarchy of Customer Subscription Date.
  • Another dimension is promotions dimension which contains attributes related to products promotions and a calender hierarchy of Promotion Launch Date.
  • A Fact that contains CustomerMeasure and PromoMeasure (for example) + other measures.


what is the best design to allow Excel users (connected to this cube) to find the total of the CustomerMeasure for those customers subscribed in 2012, and compare it with the total of PromoMeausre for promotions launched in 2012 ?

I don't think it is a good practice to separate Customer Subscription Date Dimension and another date dimension for Promotion Launch date; because customer subscription date and the launch date are fix and will never change + those dates are on a higher granularity than the Fact table level and having them in the fact table is useless redundant data + it will be harder to get the subscription date of a customer or a promotion date.

leaving the date attributes in the dimension (customer and promotion) will not facilitate what i'm asking for, because if i select the customer subscription date hierarchy and filter on 2012, along with the CustomerMeasure and the PromoMeasure (as measures), this will show the CustomerMeasure for customers subscribed in 2012 (which is what I want) with the PromoMeasure of the promotions they used even the promotions were created in 2011 or 2013 (which is not what i want). And if I select the promotion launch date calender a long with the CustomerMeasure and the PromoMeasure, i will get just the PromoMeasure of promotions launched in 2012 (which is what i want) with the CustomerMeasure of all the customers who used those promotions (launched in 2012) whether the customers were subscribed in 2012 or another year (which is not what i want).

I'm really struggling when I deal with date attributes in non date dimensions, so if you have any link to a good paper that talks about this subject, please post it.

By the way I'm using SSAS 2008R2.

Thanks in advance

abutmah

Posts : 1
Join date : 2012-10-20

View user profile

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