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

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

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

MDX Select - To use in Client App

We have a client app that used MDX to display data in the fromtend in grid. And we have requirement to filter by a dimension value (multiselect) + also display the same in the grid. But I'm aware that this is not possible using basic MDX.

I'm trying to acheive something similar to below query i.e. A dimension in slicer Axis and also in rows. Is it possible?

SELECT { [Measures].[LTD Value]} ONCOLUMNS,

NONEMPTY

(

[Currency].[Currency].[Currency].Members ,

[Trade].[Source Trade Code].[Source Trade Code].Members

) ONROWS

FROM [MyCube]

WHERE (

[Calendar].[Calendar].[Date].&[2007-01-04T00:00:00]

,{[Currency].[Currency].[Currency].[EUR], [Currency].[Currency].[Currency].[GBP] }

);

thanks,

Arun

You can define an explicit set on an axis and do a cross product of that set against another:

Code Snippet

select

[Measures].[Reseller Sales Amount] onColumns,

{[Date].[Calendar].[Month].[May 2004],

[Date].[Calendar].[Month].[June 2004]} * [Product].[Category].[Category].MembersonRows

from [Adventure Works]

If you need to make the set more dynamic, look at the STRTOMEMBER() and STRTOSET() functions.

Thanks,

Bryan

|||

Hi Arun,

Have you tried using a sub-select MDX query, like:

select

{[Measures].[Average Rate]} on 0,

[Destination Currency].[Destination Currency Code].[Destination Currency Code] on 1

from (select

{[Destination Currency].[Destination Currency Code].[EUR],

[Destination Currency].[Destination Currency Code].[GBP]} on 0

from [Adventure Works])

where [Date].[Calendar].[Date].[January 4, 2004]

--

Average Rate
EUR .92
GBP 1.46

|||Thanks guys. Both the above solution works.

MDX help required

Hi,

I have a requirement for which i need to write an MDX. The scenario is, i have a fact table with dimensions. The FactStudent consists of keys from dimensions like location, ranks dimension and period dimension. i want to know the students who have got same rank for an year and previous year (to check consistency in performance). how should be the MDX for getting this info.please help.

Thanks & regards,

Vivek S

Some reading for you

http://www.databasejournal.com/features/mssql/article.php/10894_2238011_1

Bit outdated, but you should be able to use the basics with AS2005 as well.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.