Need help with non additive measures --cognos dynamic cube designer

[Dineshpullagura]

Hello,

I have a scenario, where i need to find the number of existing customers and total customers in the system which are non-additive measures. I need to show these measures across two more dimensions along with the time dimension.

Existing customers: It is number of customers present in the system till yesterday. I should be able to roll it up to month level (number of customers registered til last month since the start ), quarter level (number of customers registered till last quarter since start) and year level(number of customers till last year since start)

Total Customers : It is number of customers present in system to date, I should be able to roll it up to month level, quarter level and year level.

To achieve this, i calculated a rolling sum of customers based on the origination date of customer. I set my aggregation levels in the cube as first for the existing customers measure and last for the total customers measure. With this i am able to show the count on time dimension. But, i am not able to add another dimension because, my fact table contains the rolled sum based on time and not on the other two dimensions.

My requirement is, if the report shows that there are 50 existing customers till last year, i should be able to show how many customers in each segment and each state. But, my fact table stores only the new customer segment and state along with the rolling sum (if a customer is added on a day, my fact table contains the rolling sum till that date, date, segment of that customer and state of that customer). As i am setting my aggregation rule as last and first for existing and total customers respectively, cube is picking up only the last or first row in my fact table with the corresponding date of that level.

The solution i could think of is storing the rolling sum on all the three dimensions (if i have 4 segments and 50 states, i need to store 200 rows for every new customer added to the system). An additional dimension will make this even worse.

Is there any other way i can implement this. Please reply if something is not clear.

Looking forward for any help.

Thank you for your time.