locked
SSAS Proclarity three hierarchy blocks one gives correct counts RRS feed

  • Question

  • Dear experts i have SSAS cube against SQLServer DB. I am using Proclarity6.3 i have created three hierarchies in BIDS each with diff drill levels. After several weeks i happened to have a frightening discovery that two of the hierarchies are not giving correct counts

    This by no means, mean that count compared to DB. actually say i have applied a column region with north south east west. when i select each filter individually, the count is correct but when i ctrl+select two filters the combined count is not correct, Form three of hierarchy blocks One Hierarchy block is giving correct combined-filters counts, other two are not.

    i am good enough in proclarity to know ....
    1. press icon like three stacked-cubes icon next to filter for getting combined count, the icon turns into ladder form.
    2. "Default" or "All" should not be accidentally selected with ctrl+select
    3. It also has nothing to do with false/hide/show/ null values in BIDS.

    i have written no MDX.  except some calculated-members for doing AVG. if Proclarity is building some MDX behind seen can u guide me how to see !?!.

    How come i got one hierarchy right and two wrong. is there is some hidden property i m not remembering. Could it be like in early stages of BIDS composing i changed ShowNullValue or similar ____ to something and coincidently i created the hierarchy same time but i might reverted the property at some stage and coincidently created another hierarchy. now "though" the property is in plain sight but each hierarchy has "then" property embedded in it !?!

    We are loosing faith in proclarity because other anoyances aswell like drill to detail failing when several filters are applied, though there are tips for that which dont work always.

    Please if you could uncover the mysterious combined-filter count behaviour of SSAS/Proclarity.

    Thank you.

    Tuesday, September 15, 2009 2:21 PM

All replies

  • Yes, as you have correctly guessed you'll want to take a look at the MDX.  You can do this by going to "View|MDX" after the query runs in ProClarity.  When you're combining those members, the MDX is likely using the aggregate function to show the combined value.  Not having access to your data it's hard to speculate as to why that function would not be showing expected values, but you'll likely want to do some testing in Management Studio to see if you can isolate the problem.

    If the problem does indeed turn out to be related to aggregate, there are several threads in the ProClarity TechNet forum having to do with the aggregate function (in particular when implementing totals in ProClarity), which you might search for and read through to gain an understanding of some of the caveats of the aggregate function.  You might also check out the whitepaper at http://blogs.technet.com/proclarity/attachment/3081222.ashx which also discusses the aggregate function.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 30, 2009 4:50 AM
  • Ben grateful for your contribution. Though your document is rare as i never stumbled upon it in rigorous google searches... at page 4 the small screen shot of hierarchy attributes filter is tempting but in reality is not practicle since even the single filter is scattered in hierachy's tree structure's various nodes. The screen grab is on Hierarchy, not individual attribute.

    I have prepared a word document with screen shots. how can i upload it in this thread !?! anyway i have iploaded it here...
    http://filedb.experts-exchange.com/incoming/2009/09_w38/181335/Proclarity-CORRECT-and-BUGGY-HIE.doc

    i have narrowed down the problem but not resolved, need your guidance over it. I removed attribute "Category" from Hierarchy-2, processed and redeployed cube. Now Hierarchy-2 is also giving correct counts on Multiselect. i can not figure out what pathetic is wrong with Hierarchies ideologies in SSAS. If someone says (though no one will) i should not do "multi selection" in filters of Hierarchy attributes.. then i will say i am doing same on "Assignment" in Hierarchy 1 & 2 but that is not misbehaving. I am considering to remove hierarchies from cubes untill resolved. Users should just add/remove fields to columns/rows in Proclarity afterall drill-to-detail is also very buggy of Proclarity. I am also planning to test this scenario in SQLServer-2008 in few days.
    http://filedb.experts-exchange.com/incoming/2009/09_w38/181400/ProclarityHierarchyCountStupidit.GIF
    http://filedb.experts-exchange.com/incoming/2009/09_w39/184872/ProclarityHierarchyCountStupidit.GIF

    Gracious...
    Wednesday, September 30, 2009 5:57 AM