Create subcube action from currently sliced cube?

Hi guys! I'm wondering if its possible to create an action that would create a subcube by right clicking on a measure cell? For example, the user narrows the cube to 1000 accounts in a particular market. Now they want a cube based on only these 1000 accounts... so they right click on the "1000" cell, pick actions (just like drill through) and there's a chioce to create a subcube. And advice?

Thanks!

You can create a statement action that could execute some MDX against your Cube, but depending on your client browser, the statement action may not be exposed to the end user. Another option would be to create a reporting action that would launch an SSRS report that executed some MDX against your cube. Even if you were able to create an action that would create a subcube, I don't see how your front end tbrowser would be able to use it. The only thing that I've encountered that may solve your problem is by using proclarity. I believe Proclarity gives you the option to browse the cube and then save selected dimension members as named sets.

Hope this helps.

Van Dieu

Create Percentage Calculated Measure... Getting the current Dimension

Hello,

I created a cube for surveys and would like to create a measure for the percentage of people (surveys) that answered Q1, Q2, Q3, etc. I have the following calculated member:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered Q2]

AS Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[Surveys Count]

)

Then Null

Else ( [Q2].[Units].CurrentMember,

[Measures].[Surveys Count])

/

(

// The Root function returns the (All) value for the target dimension.

Root (), [Measures].[Surveys Count])

End ,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

Do I have to specify the dimension in the script (like [Q2].[Units].CurrentMember) ? That means I have to create one percentage calculation member for each of the questions (dimensions). Is there a way to get the current dimension?

I hope I explain my question clearly, please let me know if I can explain further.

Thank you very much,

Sincerely,

Annie

There is no such concept as "the current dimension". Any given cell in a cube is always defined by a coordinate of all attributes of all cube dimensions. From your description, it seems like you created one dimension per survey question. Then a coordinate in your cube space would consist of members from all question dimensions. You might have multiple measure groups in the cube, in which case a cube dimension may or may not relate to a given measure group.

In any case, the CurrentMember component in the tuple expression in your example is redundant and can be safely removed.

|||

Thank you so much for the prompt reply.

As I'm fairly new to MDX. Do you mean change the script to:

-

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered]

AS Case

// Test to avoid division by zero.

When IsEmpty([Measures].[Surveys Count]) Then 0

Else [Measures].[Surveys Count] /(Root (),[Measures].[Surveys Count])

End,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

-

This then gives an "#value" error.... Could you be a bit more specific?

Thank you so much,

Sincerely,

Annie

|||

The Root() function without argument returns a tuple containing members from all attribute hierarchies, including the measures hierarchy. The [Measures].[Surveys Count] component in your tuple will cause a duplicate hierarchy error.

Create non-aggregation (Atomic) measure ?

Hi, All,

There are two dimensions and one measure group. In the measure group, I have only one measure which has no aggregation function on it. What I want to get is list all the measure detail based on the dimension attributes.

I have created this cube in SSAS 2005 successfully, but when I browsed it in browser,

I got error " The query could not be processed. Internal error: an unexpected error occured (file 'mdaggeng.cpp', line 515, function
'MDAggEngine::ComputerValueOrEvaluatot')"

Any idea on this?

Thanks

I'm not sure what is causing this error, but the primary value of SSAS is it's aggregations.

If you truely have one measure group, with one measure and don't want and aggregates done, then you would get better performance out of a SQL table.

|||

Thanks Darren for the advice.

The situation is that we have two cubes which are in different granularity but connected by "Conformed" Dimension. What required to do is to browse these cubes to retrieve the "atomic" (Non Aggregation) measures with the dimension attributes into a data set. And from the dataset, the reporting process will do the calculations. You are right, we can implement this directly against the SQL tables. But we are requested to show business users with "Cube-like" interface. I have created a cube to combine the two cubes in different granularity, but I have the difficult to bring up the measures. All the measures don't show up. In other words, the measures look like empty.

Do I miss something here?

Create Customer Ranking in Cube

Hey guys,

is there a way i can create a Rank Member or measure in my cube based on sales for that customer, no matter what time heirarchy we are looking at.

I have reporting requirements where they want to see all customers, ordered by Sales Amount. It could be at a year level, month level, or even a week/day level..

Im new to SSAS and MDX, i have looked at a few examples posted here, but cannot work out where i put the script in my cube. Is it a measure or do i do it as a calculated column in the DSV.

Any help is much appreciated.

Thanks

Scotty

Here's an Adventure Works ranking query, which uses a query-scoped calculated measure (assuming that the Customer hierarchy is on the rows of the query):

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select [Date].[Fiscal Year].Members *

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Geography].[Country].Members on 1

from [Adventure Works]

--

All Periods All Periods FY 2002 FY 2002 FY 2003 FY 2003 FY 2004 FY 2004 FY 2005 FY 2005
Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank
Australia $9,061,000.58 2 $2,568,701.39 1 $2,099,585.43 1 $4,383,479.54 2 $9,234.23 3
Canada $1,977,844.86 6 $573,100.97 3 $305,010.69 6 $1,088,879.50 6 $10,853.70 2
France $2,644,017.71 5 $414,245.32 6 $633,399.70 4 $1,592,880.75 5 $3,491.95 6
Germany $2,894,312.34 4 $513,353.17 5 $593,247.24 5 $1,784,107.09 4 $3,604.83 5
United Kingdom $3,391,712.21 3 $550,507.33 4 $696,594.97 3 $2,140,388.50 3 $4,221.41 4
United States $9,389,789.51 1 $2,452,176.07 2 $1,434,296.26 2 $5,483,882.67 1 $19,434.51 1

>>

|||Please remove this line, as it is wrong:

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Obviously, even if [Internet Sales Amount] is NULL, the rank will never be NULL - it is always an integer value.

|||

Ok guys, thanks for the replys..

I added the following into a calculated measure that i called CustSalesRank .. (taken from the 1st line of code) is that Correct?

When i added this to the expression box in the calculated member, i get a red undeline on the "with" statement.. What am i doing wrong?

CREATE MEMBER CURRENTCUBE.[MEASURES].[CustSalesRank]

AS with member [measures].[CustSalesRank] as

Rank([Customer].[Customer Group Name].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Group Name]),

[Measures].[Sales Amount], BDESC)),

select [Date].[Fiscal Hierarchy].Members *

{[Measures].[Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Group Name].Members on 1

from [Sales By Market],

VISIBLE = 1 ;

--

|||

the error i get when i try to deploy is:

Error 2 MdxScript(Sales By Market) (17, 5) Parser: The syntax for 'with' is incorrect. 0 0

|||

Hi Mosha,

You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Brunswick (null) 5
Manitoba (null) 6
Ontario $36.96 4
Quebec (null) 7

versus:

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Ontario $36.96 4

>>

|||

> You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

Deepak - this is very very dangerous path. NON_EMPTY_BEHAVIOR is not a semantic feature, it is a performance hint. It doesn't cause rows to be removed by NON EMPTY. They might get removed sometimes when NEB is defined incorrectly, like in the example above, but it is purely a hint, and in other situations they won't get removed. So you will get inconsistant and even wrong results.

To properly remove rows with no sales, there must be specific IIF for that, i.e.

with member [Measures].[CustSalesRank] as

IIF( IsEmpty([Measures].[Internet Sales Amount]), NULL, Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Now NON_EMPTY_BEHAVIOR is correctly defined. Another idea is to optimize performance here would be to remove Order from inside Rank, and use version of Rank with 3 parameters.

|||

Mosha,

Thanks for your input.. Could you please kindly answer my question for me.. I just need to know where i put this code, i put it in a calculated measure and it didnt work. I removed all things from the select statement and down, but im not sure if it is working correctly..

Also, with all this code talk, im not 100% sure what i should be putting in now...

Thanks

Scotty

|||

For Adventure Works, you can put something like that inside MDX Script:

CREATE [CustSalesRank] = Rank([Customer].[Customer Geography].CurrentMember,[Customer].[Customer Geography].CurrentMember.Level.Members,[Measures].[Internet Sales Amount]);

|||

Thanks Mosha.. this worked great..

If i may ask for one more piece of advise, when i do this,, all the customers that have 0 sales are still shown with the lowest rank number.. in my case 81 . what i can i add to expression that will supress customers with 0 sales..?

Thanks

scotty

|||

Thanks for clarifying that, Mosha - I had (mistakenly) assumed that, in AS 2005, NON_EMPTY_BEHAVIOR would cause those rows to be removed.

On the use of Rank() with 3 parameters in lieu of Order() - has the semantics of Rank() changed in AS 2005, because BOL still says the following:

http://msdn2.microsoft.com/en-us/library/ms144726.aspx

>>

SQL Server 2005 Books Online

Rank (MDX)

Updated: 17 July 2006

...

The Rank function does not order the set.

>>

|||

The comment that Rank function doesn not order the set is correct, although I can see how it can be misleading. It probably refers to the fact that internal implementation of Rank doesn't need to order the set in order to find the rank. Please see detailed discussion about Rank and algorithms behind it in this blog:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx

|||

Thanks - since the same comment also appeared in AS 2000 BOL, it could be confusing. But the MDX Solutions chapter, referenced in your blog entry, draws this distinction:

"..The semantics for this function have changed between Analysis Services 2000 and 2005. When the expression is provided, it is used to determine if ties exist and what the right rank should be. In Analysis Services 2000, the expression was used when the tuple was found to search neighbors in the set and determine fair ranking numbers.."