Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MDX issue

Posted on 2010-11-22
26
Medium Priority
?
227 Views
Last Modified: 2012-06-21
I tried to write the scope statement using different approaches, however haven’t been able to overcome the issue. An example of how the scope statement is rewritten :
SCOPE ([dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION]
    ,[dDataType].[Category Data Type].&[Forecast]);
THIS = [dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION_];                
END SCOPE;
 
Attached is the document with the issue description. Thanks for your help in Advance!
000TD-Issue--1-.docx
0
Comment
Question by:kirannandedkar
  • 13
  • 9
26 Comments
 

Author Comment

by:kirannandedkar
ID: 34186691
This is a Microsoft BI environment, where we’re using SQL Server 2008 Analysis Services for cube building. The below scenario uses MDX in the SSAS Cube.
0
 

Author Comment

by:kirannandedkar
ID: 34194612
Please help me
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34195807
Your scope expression will not work as you are referencing CurrentMember values for [dDataType].[Category Data Type]. Unless these are included in the query there will be no current member for them. You could try nested Scopes (rather than explicitly testing the current member values) but I think this might have the same problem. I will try to find an alternative for you later.

Tim

p.s. as an aside, rather than using:

[dDataType].[Category Data Type].CurrentMember.Name = "Budget"

use

[dDataType].[Category Data Type].CurrentMember is [dDataType].[Category Data Type].&["Budget"]

as it's more efficient
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:kirannandedkar
ID: 34196251
No this doesnt work either. Please help
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34203638
Did you try nested scopes?

i.e.

SCOPE ([dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION]);  

SCOPE ({[dDataType].[Category Data Type].&[Budget], [dDataType].[Category Data Type].&[Forecast]});

THIS = ([dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION_]);

END SCOPE;

END SCOPE;

I have tried this in an equivalent calculation and it works with the value only in the slicer.

Tim
0
 

Author Comment

by:kirannandedkar
ID: 34203905
Still no luck... Same result...
0
 

Author Comment

by:kirannandedkar
ID: 34204185
Here is how I think the scenario got closer. I created a MEMBER like below:
 
CREATE MEMBER CURRENTCUBE. [Measures].[000TD_FC_TEST] AS
(([dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION_]
,[dDataType].[Category Data Type].&[Forecast]),[Measures].[Balance])
 
And then create a Scope Statement:
 
SCOPE ([dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION]);
THIS = [Measures].[000TD_FC_TEST];
END SCOPE;
 
And this solves some of the problem as it gives me the desire result when slicing using “Forecast”. However it messes up the solution if I put “Budget” or any other Category in the slicer, it still  gives the same results like “Forecast”.
 
Then I tried something like below:
SCOPE ([dOrganisation].[Organisation Hierarchy].[Reporting Unit].&[BELUX HYPERION]);
SCOPE ([dDataType].[Category Data Type].&[Forecast]);
THIS = ([Measures].[000TD_FC_TEST]);
END SCOPE;
END SCOPE;
 
But this one won’t work either. Any suggestions on this. Thank you!
0
 

Author Comment

by:kirannandedkar
ID: 34217320
anybody expert there to help
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34217751
Hi,

Your calculated member isn't really closer to sorting your problem out - you have simply created a tuple that explicitly returns the value for Belux hyperion_ sliced by Forecast. But it won't be sliced by anything else - it's not an approach that will resolve your problem.

What I don't understand is why the nested scope solution I provided didn't work - in my recreation of the problem it worked fine.
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34218196
I have replicated the problem using Adventure Works: the idea being that for June and July, the measures for 'Bikes' is set to be the same as the values for 'Clothing'. Calculation is:

SCOPE ([Product].[Category].&[1]);

SCOPE ({[Date].[Month of Year].&[6],[Date].[Month of Year].&[7]});
THIS = ([Product].[Category].&[3]);
END SCOPE;
END SCOPE;

MDX queries to check this:

For June Bikes = Clothing:

SELECT ({[Measures].[Internet Sales Amount],[Measures].[Internet Order Count]}) on rows,
NONEMPTY ([Product].[Category].[Category].MEMBERS) on columns

FROM [Adventure Works]

WHERE ({[Date].[Month of Year].&[6]})

                                       Accessories      Bikes      Clothing      Components
Internet Sales Amount      $65,200.93      $35,206.48      $35,206.48      (null)
Internet Order Count      1,723      778      778      (null)

(note that month is only included in the where condition)

For August, Bikes has it's own value:


SELECT ({[Measures].[Internet Sales Amount],[Measures].[Internet Order Count]}) on rows,
NONEMPTY ([Product].[Category].[Category].MEMBERS) on columns

FROM [Adventure Works]

WHERE ({[Date].[Month of Year].&[8]})

                                      Accessories      Bikes      Clothing      Components
Internet Sales Amount      $52,056.61      $1,824,214.67      $23,335.39      (null)
Internet Order Count      1,323      932      519      (null)

Isn't this what you are trying to achieve?

Tim

0
 

Author Comment

by:kirannandedkar
ID: 34229692
Please see attachment and reply me ASAP
mydoc.doc
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34231564
Hi,

As I understand it, if you have custom rollups then they will take precedence - I don't think you'll be able to work around them by attempting to provide other calculations (as you've been attempting). You would need to modify the custom rollups themselves to 'build in' the mapping between the Belux hyperion members.

Tim
0
 

Author Comment

by:kirannandedkar
ID: 34236771
can you show how to do it please
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34236893
Maybe - but I haven't used custom rollups myself. You would need to post the calculations to begin with...
0
 

Author Comment

by:kirannandedkar
ID: 34236938
Please can somebody give me the solution as i am now tired of this.
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34237007
I don't think you can expect a solution if you don't post the requested information. Can you post the custom rollup code?
0
 

Author Comment

by:kirannandedkar
ID: 34264727
Please find attached code. Please change extension of attached file  to .mdx
Copy-of-Formula.txt
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34280759
Looks like you will have to replicate the logic in the scope statements into these calculations.
You may be able to place these calcs inside the scope statement to simplify the checks on Forecast  and Budget but modifying these calculations will be necessary.
0
 

Author Comment

by:kirannandedkar
ID: 34280797
but cant you give me logic for it?
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34280873
I'm not going to rewrite your query for you - it will take too much time. You have the logic, it's just plugging it into the existing calculations.
0
 

Author Comment

by:kirannandedkar
ID: 34281309
Thanks for your help again! I did try modifying the calculations just like I did in the Scope statement, however it looks like we cannot achieve this simply within our cube. I've got to do this at the Report Level.
 
In the scope statement we cannot specify the SubCube query (the slicer pane in the SSAS Browser). We opened up a case with MS and got the above conclusion.
0
 

Author Comment

by:kirannandedkar
ID: 34514465
ok close it
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 34581178
Question PAQ'd and stored in the solution database.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question