This discussion is archived
5 Replies Latest reply: Jan 16, 2013 10:54 AM by user617289 RSS

Allowing duplicate members in outline or anything else?

user617289 Newbie
Currently Being Moderated
Essbase 9.3.1 + OBI EE 10g
I have such dimension in outline
Dim1
-Par1(+)
-Child1(+)
-Child2(+)
-Child3(+)
-Par2(~)
-Child1(+) (shared)
-Child2(+) (shared)
-Par3(~)
-Child2(+) (shared)
-Child3(+) (shared)

and also have dimension Period
Period
-Year1(+)
-mon1.Year1(+)
-mon2.Year1(+)
......
-mon12.Year1(+)
-Year2(+)
-mon1.Year2(+)
-mon2.Year2(+)
......
-mon12.Year2(+)


and have dimension Measures
Measures
-cost(~)

Problem:
"Child1", "Child2" and "Child3" are the children of the "Par1" for all of the "Years"

"Child1" is the child of the "Par2" for all of the "Years"
"Child3" is the child of the "Par3" for all of the "Years"

But "Child2" is the child of the "Par2" when the period="Year1"
and "Child2" is the child of the "Par3" when the period="Year2".

So when the combination is Year1->Par2.Child2->cost i want to see the real result and when the combination is Year2->Par2.Child2->cost i want to see #Missing or Zero(0).

But when i use shared members i always get decimal result even if i apply formula to cost(depending on value of the Period dim) because physically the is only one value for all of the shared members in database.
Instead of shared members to solve this problem I can add to children of "Dim1" the name of their parents(ex. "Child2(Par2)" and "Child2(Par3)") and then apply specific formulas to "cost". But business users dont want to see these addings and cut them in their reports.

So if i allow essbase to use duplicate members will it resolve my problem or there are another ways to do it?
  • 1. Re: Allowing duplicate members in outline or anything else?
    sunil k Journeyer
    Currently Being Moderated
    Hi,

    Child2 (under Par2 and Par3 ) can not be a shared member. it rather has to be a calculated member. You would need to use different member names under Par2 and Par3 (e.g. Par2_Child2, Par3_Child2) and have formula on these members. I do not think you need formula on Cost or any other dimension member to drive the result you are looking for.

    If you have to have same member names, you can try by switching on 'Allow Duplicate Members' and having formula on Child2 instances under Par2 and Par3. I would rather suggest you to convince your users for different member names than trying to allow duplicate members.

    Regards,
    Sunil
  • 2. Re: Allowing duplicate members in outline or anything else?
    user617289 Newbie
    Currently Being Moderated
    Thanks for reply.I i think you are right that child2 has to be a calculated member.
    But why do you suggest me to convince my users for different member names? Are there any problems in using duplicate members in essbase+obiee?
    Look for example
    Dim1=Country
    Par1,Par2,Par3 - Economical Areas
    Par1 - Whole World(all the countries)
    Par2 - European Union(EU)(countries included in it at least onces(for one year) in the the examined period)
    Par3 - Commonwealth of Independent States(CIS)

    And we have three countries RU-RUSSIA, ES-SPAIN, DE-GERMANY.
    Period=2 Years(2010,2011)
    RU was in CIS for 2 years and wasnt in EU,
    DE was in EU for 2 years and wasnt in CIS
    and ES was in EU in 2010 and in CIS in 2011

    If we have different member names our dimension looks like

    Country
    -Whole World
    -RU-RUSSIA
    -ES-SPAIN
    -DE-GERMANY
    -European Union
    -ES-SPAIN(EU)
    -DE-GERMANY(EU)
    -Commonwealth of Independent States
    -ES-SPAIN(CIS)
    -RU-RUSSIA(CIS)

    First, if we choose zero level of dim we get 7 counties(users have to make additionl filer on Economical Areas) to get 3 countries.
    Second, if user choose EU and drill through it he get
    -ES-SPAIN(CIS)
    -RU-RUSSIA(CIS)
    and have to cut (CIS) manually in his business report.
    Thats why i want to try allow duplicate members. But if it isnt work properly in obiee(mdx to physical cube) i tell my users about it.
    Did anybody try anything similar and may be face with any bug or problem?
  • 3. Re: Allowing duplicate members in outline or anything else?
    sunil k Journeyer
    Currently Being Moderated
    Personally, i would not prefer to use duplicate members (unless there is no other option) because i would have to use fully qualified names in reports/scripts/calculations etc. It could become difficult to maintain in long run with change in structure/outline. That is my general guideline - I am sure you can give it a try if duplicate members are not present at large scale in your outline.

    Which version of ESSBASE are you using? You can try to see if Varying Attribute would address your requirements – you may have to redesign your outline a little bit. - Realized that you are on 9.3.1 so this may not be am option as I do not think varying attribute was introduced back then

    Regards,
    Sunil

    Edited by: sunil k on Jan 14, 2013 12:44 PM
  • 4. Re: Allowing duplicate members in outline or anything else?
    user617289 Newbie
    Currently Being Moderated
    You are right. Im on 9.3.1.
    And thanks for varying attribute i'll check it.
  • 5. Re: Allowing duplicate members in outline or anything else?
    user617289 Newbie
    Currently Being Moderated
    Hi again.
    Use this method (without allowing duplicate members) and unfortunately cant stay with it.
    1. I always create aggregational views after importing the data into cube.
    But now after arrgregation i cant open dim Country.(got the error "Cannot send data") in EAS and OBI. In essbase.cfg two parameters(NetDelay.. and NetRetry...) are equal 75000.
    Made two tricks in windows registry - the same. Understood that it was because of one member of level 1(c99) which has 228 childen and each children has such formula

    "IIF(substring([Times].CurrentMember.Member_Name,3,4) ="02",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="03",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="04",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="05",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="06",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="07",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="08",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="09",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="10",c732,IIF(substring([Times].CurrentMember.Member_Name,3,4) ="11",c732,MISSING))))))))))"

    after filtering on all other members(even together) i got the result. But i dont understand why...Other 14 level-1 members also has children(max 178) and they queries normally.

    More detailed dim Country

    Country(Hierarchies Enabled)
    -c00(stored)(+)(256 members)
    --c100
    --c103 
    --.......
    --c900
    -c10(Dynamic)(~)(10 members)
    --c100(c10)(+)(formula above)
    --c300(c10)(+)(formula above)
    .........................................
    -c20(Dynamic)(~)(178 members)
    --c100(c20)(+)(formula above)
    --c200(c20)(+)(formula above)
    .............................................
    -c99(Dynamic)(~)(228 members)
    --c100(c99)(+)(formula above)
    --c200(c99)(+)(formula above)
    .............................................

    If i dont make aggr views i got the result even with "c99" in different combination (but sometimes also got the error "cannot send data")


    2. But the main trouble is obiee pivot table.

    If i choose level-2 members of this dim with different measures and other dim members it shows the result.
    But after i set function SUM(before, after etc) in pivot i get "no data found".
    except stored member "c00" - with it i get my SUM.

    If i add filer with ONE! year inside i get my SUM. If more than one or none - "no data found"
    except stored member "c00" - with it i get my SUM.

    So now i see only one way...The whole dim Country is Stored
    In Measure "Cost" i add big formula like "IIF(substring([Times].CurrentMember.Member_Name,3,4) ="02" and IIF([Country].CurrentMember.Member_Name,3,4) ="c100(c10)", c100...
    for each member...and there will be a lot of measures (CostN...) course the formula is very huge...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points