Non Roll - Up Measures

[Silvm1]

I have a fact table with about 70 mil records. I am trying to create a cube based on two measures. One should be summed across all dimensions and the other should be summed across all dimensions except time. It should always take the latest for the approriate time frame. I.E. If I am viewing by year, I would see the data at the last value for each year, not the sum of the enitire year.

Is there any way to do this in AS?

Thanks for your help!

 

[MDXer]

Calculated Cells will give you what your looking for. Calculated cells are somewhat difficult to explain and unfortuantely the SQL BOL isn't that great at explaining them in my opinion. I'll try one and post the process sometime this weekend.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Silvm1]

Great Thanks!

Also, everytime I try to add a dimension to the cube from the fact it says, unable to count members of level. Is this because of the table size? Is there a way around this?

 

[MDXer]

Yes this is more than likely caused by the size of the fact table. The best method to use is to use dimension tables that join to the fact. a dimension table can be as easy as doing a

Select Distinct
ColumnName
Into dimDimensionName
From FactTable

To really do it the best way you would want a Primary Key that would serve as the memberID and also as a surogate key that joins to your fact table.



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck