Measure Slicing Problem

Hi,

Please let me know whether the following requirement is possible in SQL Server Analysis Services 2005.

I have a fact table from which I have 4 measures M1, M2, M3 and M4. I have a dimension say D1,

Now I want that while browsing cube data the dimension D1 should slice M1 but it should not slice M2, M3 and M4.

What I feel is if we add the dimension to the fact table in Dimension Usage then it will slice all the measures coming from the fact table. If we remove the linkage of the dimension with the fact table in the Dimension Usage then the dimension will not slice any of the measures coming from that fact table.

The only solution that comes to my mind is by creating a duplicate of the fact table. But I dont want to do this as it would increase redundancy and make the DSV more complex.

Please let me know if I can do it in some other way.

Thanks in advance.

Regards,

Abhishek.

If M2-M4 are not related to D1 it sounds like they should be in a different fact table. However you should be able to achieve a similar outcome, buy overriding the context of D1 using a scope statement like the following.

SCOPE ({Measures.M2, Measures.M3, Measures.M4});

this = (D1.[All]);

END SCOPE;

Which pretty much says that for M2:M4, return the value of D1 at the top level, regardless of the current context of D1

|||

Hi,

Can you please tell me where should I write this scope statement.

Shoul I write it where I create the calculated member.

Thanks

Abhishek

|||You can only use scopes in the calculations section of the cube. You can either switch to script view and enter them or click the button on the tool bar that lets you add a new script command.|||

"The only solution that comes to my mind is by creating a duplicate of the fact table" - how about adding a Named Query on the fact table, which eliminates the field for D1 in the fact table? Then a new measure group could be created with this Named Query as the fact table, and measures M2, M3, M4. If the original fact table is like:

FT: <D1, D2, D3, D4, M1, M2, M3, M4>

then the Named Query could be like:

select D2, D3, D4, sum(M2) as M2, sum(M3) as M3, sum(M4) as M4

from FT

group by D2, D3, D4