locked
lookupcube function diplaying same values MDX RRS feed

  • Question

  • 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 BandSr Wednesday, May 23, 2012 2:30 PM
    Wednesday, May 23, 2012 2:27 PM

Answers

  • 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 BandSr Monday, May 28, 2012 3:23 PM
    Wednesday, May 23, 2012 3:43 PM

All replies

  • 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 BandSr Monday, May 28, 2012 3:23 PM
    Wednesday, May 23, 2012 3:43 PM
  • 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:55 PM
    Wednesday, May 23, 2012 3:52 PM
  • 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

    • Proposed as answer by Lola Wang Thursday, May 24, 2012 6:28 AM
    Wednesday, May 23, 2012 4:48 PM