I have a cube which has ROLAP as the Design Storage type. I have 4
dimensions set up on it:
time_dimension
area_dimension
organisation_dimension
class_dimension
All dimensions are currently set up as MOLAP.
I am using SQL Server 2000's built in Analysis Service Cube Browser to browse data.
Initially, when no filter has been put on, the all {area,time,organisation,class}_dimension all shows up as 6,100,200. The numbers under individual 'nodes' for all hirerarchy also adds up to this number.
The same also applies if I put a filter on time_dimension for 2002.
However, when I put a filter on the time_filter to 2003, The following happens:
- In area_dimension, all area_dimension shows up as 870, and the nodes
underneath the area_dimension adds up to 6010
- In all other dimension, the all {class,time,organisattion} dimensions
shows up as 870 and the number under those nodes also adds up to 870.
Next, I closed the cube browser and reopen it again (without making Any data changes)
- In area_dimension, all area_dimension shows up as 6010, and the nodes underneath the area_dimension also adds up to 6010
- In all other dimension, the all {class,time,organisattion} dimensions shows up as 6010 but the numbers under those nodes only adds up to 870.
Worst still, none of the number actually correspond to the number of records in my measures table. e.g. For some organisations, there are some organisation where the measure in the cube<the number of records in fact table. But there is 1 which is very much larger, when a filter for Jan2003 is applied.
The Aggregate Function I selected is COUNT. I just want to count the number of records associated with each area, time, organisation, class.
(i.e., if there are 1000 records for the area London, 1000 should appear against London, not 1, hence I am not using SELECT DISTINCT)
I tried changing the dimension to ROLAP, but when I did this for the Time_dimension, a warning pops up saying
Setting Storage Mode to ROLAP requires the following additional changes:
Set Member Keys Unique to True o the level 'Minute'
Set Changing to True on this dimension
I selected No because
1) There could be records with same time field associated with different organisation, areas, classes
2) Doin so causes my MDX query filtering on time to fail with errors like "unknown error 0 (Minute)"
Chaging to ROLAP on the other 3 dimensions doesn't help.
What could possibly have caused the data inconsistency??? How could I rectify this? Should I use ROLAP or MOLAP on my dimensions? I DO need updates to fact tables to show up immediately
dimensions set up on it:
time_dimension
area_dimension
organisation_dimension
class_dimension
All dimensions are currently set up as MOLAP.
I am using SQL Server 2000's built in Analysis Service Cube Browser to browse data.
Initially, when no filter has been put on, the all {area,time,organisation,class}_dimension all shows up as 6,100,200. The numbers under individual 'nodes' for all hirerarchy also adds up to this number.
The same also applies if I put a filter on time_dimension for 2002.
However, when I put a filter on the time_filter to 2003, The following happens:
- In area_dimension, all area_dimension shows up as 870, and the nodes
underneath the area_dimension adds up to 6010
- In all other dimension, the all {class,time,organisattion} dimensions
shows up as 870 and the number under those nodes also adds up to 870.
Next, I closed the cube browser and reopen it again (without making Any data changes)
- In area_dimension, all area_dimension shows up as 6010, and the nodes underneath the area_dimension also adds up to 6010
- In all other dimension, the all {class,time,organisattion} dimensions shows up as 6010 but the numbers under those nodes only adds up to 870.
Worst still, none of the number actually correspond to the number of records in my measures table. e.g. For some organisations, there are some organisation where the measure in the cube<the number of records in fact table. But there is 1 which is very much larger, when a filter for Jan2003 is applied.
The Aggregate Function I selected is COUNT. I just want to count the number of records associated with each area, time, organisation, class.
(i.e., if there are 1000 records for the area London, 1000 should appear against London, not 1, hence I am not using SELECT DISTINCT)
I tried changing the dimension to ROLAP, but when I did this for the Time_dimension, a warning pops up saying
Setting Storage Mode to ROLAP requires the following additional changes:
Set Member Keys Unique to True o the level 'Minute'
Set Changing to True on this dimension
I selected No because
1) There could be records with same time field associated with different organisation, areas, classes
2) Doin so causes my MDX query filtering on time to fail with errors like "unknown error 0 (Minute)"
Chaging to ROLAP on the other 3 dimensions doesn't help.
What could possibly have caused the data inconsistency??? How could I rectify this? Should I use ROLAP or MOLAP on my dimensions? I DO need updates to fact tables to show up immediately