Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
lookupcube function diplaying same values MDX

Answered lookupcube function diplaying same values MDX

  • Wednesday, May 23, 2012 2:27 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Friends,

    we have a budgets in the Finance cube for the store

    below Query displaying same values could anyone tell me where I'm doing mistake .

    WITH 
MEMBER [Measures].[Bugets from Finance] as
lookupcube("Finance","([Shared - Store].[Name].Currentmember,
[Measures].[Amount - GL Entry])")
  MEMBER [Measures].[Budgets TY] AS 
   iif((isempty([Measures].[Bugets from Finance]) or 
    [Measures].[Bugets from Finance]=0),null,
    Sum
    (
  
      (
        [Store].[Name].CurrentMember
       ,[Time].[Fiscal Hierarchy].[Fiscal Year].&[2012].&[Fiscal Half Year 1].&[2012-02].&[2012-05].&[2012-18]
      )
     ,[Measures].[Bugets from Finance]
    ) 
    )
       
 SELECT 
    { [Measures].[Budgets TY] } ON 0,
([Store].[Name].[Name].members-{[Store].[Name].&[]}) ON 1
FROM [Retail]


    • Edited by BI Siva Wednesday, May 23, 2012 2:30 PM
    •  
     

All Replies

  • Wednesday, May 23, 2012 3:43 PM
     
     Answered
    Sign In to Vote
    0

    I think the issue is that in your Finance Cube you have a dimension called [Shared Store] while in your query you are using [Store], so the query engine isn't sure they are the same.

    Is it possible to test this by adding the same named dimension, or try and use the LinkedMember function in the lookupcube function. I haven't tried it so am not sure it will work.

    • Marked As Answer by BI Siva Monday, May 28, 2012 3:23 PM
    •  
     
  • Wednesday, May 23, 2012 3:52 PM
     
      Has Code
    Sign In to Vote
    0

    Hello,

    If the results are the same as to compare with "[Store].[Name].[Name].members on 0" , then most probably  "-{[Store].[Name].&[]}"  does  remove nothing from the set.

    It seems like the sum() function is missing .children, leaf members references, or some kind of iteration. The [Store].[Name].CurrentMember being a temporaly constant. If the expression:

    [Time].[Fiscal Hierarchy].[Fiscal Year].&[2012].&[Fiscal Half Year 1].&[2012-02].&[2012-05].&[2012-18]

    is set, maybe a coma is missing. Otherwise, try to wrap it with brackets {} and add  .members  to the expression.

    Philip,



    • Edited by VHteghem_Ph Wednesday, May 23, 2012 3:54 PM
    • Edited by VHteghem_Ph Wednesday, May 23, 2012 3:55 PM
    •  
     
  • Wednesday, May 23, 2012 4:48 PM
     
     Proposed Answer
    Sign In to Vote
    0

    Any reason you're using the LookupCube function? It really is a dangerous command. It exists only because of legacy days with MSAS 2000 where your cube could only address a single fact table at a time. You'd be far better off creating a linked measure group to retrieve that measure than using the LookupCube statement. Wish Microsoft would deprecate this command. See this blog post http://www.bidn.com/blogs/DustinRyan/ssis/814/lookupcube-function-mdx-query-performance-test and particular look at the comment by Greg Galloway.

    HTH, Martin

    http://martinmason.wordpress.com