Different granularity of Data in Single Fact table

vjhegde Wed May 23, 2012 4:20 am

Hi

I have very strange requirement where I need to create fact table with different granularity.
Let me explain you in terms how business operate.
Let say if I have Storage capacity of 100GB and I have 10 users who want 100GB each to store the data, what the system do is it allocate 100GB to all 5 users, that means system is over committing.
In report the data should look like

Storage-s1 - Capacity-100GB - users1 - 100GB
Storage-s1 - Capacity-100GB - users2 - 100GB
Storage-s1 - Capacity-100GB - users3 - 100GB
Storage-s1 - Capacity-100GB - users4 - 100GB
Storage-s1 - Capacity-100GB - users5 - 100GB
--------------------------------------------
Total - Storage Capacity-100GB users - 500GB
----------------------------------------------

second scenario, under utilization of storage capacity, let say storage capacity of 100GB being configured to 2 volumes to 25GB each.
In report the data should look like

Storage-s1 - Capacity-100GB - agg1 - 25GB
Storage-s1 - Capacity-100GB - agg2 - 25GB
--------------------------------------------
Total - Storage Capacity-100GB users - 50GB

I am using Cognos 10 for reporting solution. Solution and suggestions to this strange problem will be highly appreciated.