locked
Non Empty Crossjoin cannot be unchecked? RRS feed

  • Question

  • I have ProClarity Web Professional 6.3.2217 querying a 2008 cube.  When more than one attribute is placed on the rows, the rows are automatically filtering the empty rows.  When this same view is pointed toward a 2005 cube this does not happen.  The empty rows are shown.

    I noticed that on the 2008 cube the Options/Non Empty Crossjoin box is checked when the grid is filtered.  But when I "uncheck" the box it does not stick.  I can uncheck it and the rows are still filtering empties.  I go back to the option and it is checked again.

    Has anyone else run into this querying a 2008 cube?

     

    Friday, January 23, 2009 3:40 AM

Answers

  • The 'forced NONEMPTY' issue has been resolved in the 2229 hotfix for ProClarity Professional and Server. What you'll want to do is install the 2229 pro/pas hotfix or later, then open the view in ProClarity Professional and uncheck the 'Use non-empty crossjoin' box. That should clear the NONEMPTY.

    http://support.microsoft.com/kb/2450921


    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Joey B. Pruett Monday, February 13, 2012 7:55 PM
    • Marked as answer by Joey B. Pruett Monday, February 13, 2012 7:55 PM
    Thursday, February 9, 2012 11:56 PM
  • Dave,

    It looks like part of what you're seeing is expected behavior.  I'm not sure why one of your queries doesn't show the NONEMPTY and one does.  To my knowledge, we should always use NONEMPTY when querying a 2008 cube.  There is no way to turn it off except in the MDX.  The problem is that we make it look like you can turn it off by un-checking the box – just a design oversight as we tried moved to support 2008 without major product changes.  If I remember right, the box is re-checked when you go back into the options after running a 2008 query.  I’m not sure which version this began in – but I could check the list of hot fixes and see which one introduced 2008 support.  Maybe the 6.3 RTM version did?  This change was made because of the improvements in the AS engine that vastly improved the filtering.  If all queries go against 2008, and some use the filtering and some don't - and it doesn't make sense why, we should investigate.  If you’re seeing some other type of problems with it, we should investigate those too.  Amanda is out this week, but I can do some investigating.

     

    -Joey

    • Marked as answer by Sean_Flanagan Monday, July 27, 2009 2:51 PM
    Monday, June 22, 2009 7:20 AM

All replies

  • What's the MDX being executed when this happens?
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 26, 2009 10:35 PM
  • Hi Ben,

    It is very strange.  With the first query I have 5 customers chosen from the first customer attribute on the rows.  This yields me three records, one with data and two blank.

    With this query I have 5 customers chosen in the setup panel

    SELECT { [Measures].[SALES Cases] } ON COLUMNS ,

    { [Customer].[Customer Name and Number].&[CUSTOMER A], [Customer].[Customer Name and Number].&[CUSTOMER B], [Customer].[Customer Name and Number].&[CUSTOMER C)], [Customer].[Customer Name and Number].&[CUSTOMER D],[Customer].[Customer Name and Number].&[CUSTOMER E] } ON ROWS 

    FROM [Sales]

    But then when I put another attrubute, like City, on the rows, nested after customer, the view automatically suppresses empties and only shows one record.  (the one with data)  The "non empty cross join" option is always checked and it won't stay unchecked.  The mdx doesn't have nonemptycross join in it, but it is doing a non empty.  The client wants the empties to show.  But they are automatically suppressing, even without a filter.  This is querying a 2008 cube.

    SELECT { [Measures].[SALES Cases] } ON COLUMNS ,

    { NONEMPTY( { [Customer].[Customer Name and Number].&[CUSTOMER A], [Customer].[Customer Name and Number].&[CUSTOMER B], [Customer].[Customer Name and Number].&[CUSTOMER C],[Customer].[Customer Name and Number].&[CUSTOMER D], [Customer].[Customer Name and Number].&[CUSTOMER E] } * { [Customer].[City].[All Customers].CHILDREN } , { [Customer].[Company].&[001] } * { [Measures].[SALES Cases] } ) } ON ROWS 

    FROM [Sales]

     

    Thursday, January 29, 2009 12:38 AM
  • Let me see what we can find on this.  Thanks.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, January 31, 2009 12:59 AM
  • Just FYI, we haven't forgotten about this, but we also don't have much in the way of an answer yet.  Still working on it though.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 19, 2009 5:10 PM
  • Hello Dave,

    Thanks for alerting us about this issue.  I have been able to reproduce the scenario using a test cube and am pursuing investigation.  I'll keep you posted as to the progress once I have some solid answers for you.

    Kind Regards,
    Amanda Ruffner
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 10, 2009 10:11 PM
  • Thanks Amanda!
    Tuesday, March 10, 2009 10:29 PM
  • Hi Dave,

    Please disregard my latest reply.  I was testing connecting to a SQL 2008 server, however there were some SQL 05 cubes in the mix.  I am successful in reproducing the NEC issue anytime I connect to a SQL 2008 cube.  Our Senior Escalations Engineer is actually looking into this issue now.  We will keep you posted.

    Kind Regards,
    Amanda
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, March 30, 2009 5:04 PM
  • Dave,

    It looks like part of what you're seeing is expected behavior.  I'm not sure why one of your queries doesn't show the NONEMPTY and one does.  To my knowledge, we should always use NONEMPTY when querying a 2008 cube.  There is no way to turn it off except in the MDX.  The problem is that we make it look like you can turn it off by un-checking the box – just a design oversight as we tried moved to support 2008 without major product changes.  If I remember right, the box is re-checked when you go back into the options after running a 2008 query.  I’m not sure which version this began in – but I could check the list of hot fixes and see which one introduced 2008 support.  Maybe the 6.3 RTM version did?  This change was made because of the improvements in the AS engine that vastly improved the filtering.  If all queries go against 2008, and some use the filtering and some don't - and it doesn't make sense why, we should investigate.  If you’re seeing some other type of problems with it, we should investigate those too.  Amanda is out this week, but I can do some investigating.

     

    -Joey

    • Marked as answer by Sean_Flanagan Monday, July 27, 2009 2:51 PM
    Monday, June 22, 2009 7:20 AM
  • Hi Joey,

    Do we have any update on this issue. We have just upgraded to ProC v6.3.2217 with SSAS 2008 and after a number of issues this is the last on the list to be resolved.

    David
    Wednesday, October 7, 2009 10:49 AM
  • We just migrated to 2008 and are also experiecing this problem although, it is not simply adding non empty to the query, it is actually inserting nonemptycrossjoin() function.  if I uncheck the option and publish seems to be fine but when save as my view from web, automatically re-checks and view returns no result if view has muttiple hierarchies on axis.  uncheck the box, results return.
    Sunday, January 31, 2010 8:19 PM
  • Are you using ProClarity 6.3 SP3?  If so, when connected to a SSAS 2008 cube, the queries should always be using "NONEMPTY" instead of NECJ.  As a side note, as of ProClarity 6.3 SP2, when connected to an SSAS 2008 cube, the "Use NonEmptyCrossJoin" option box will always be checked, though the queries should in fact be using NONEMPTY.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 11, 2010 6:48 PM
  • seems we don't have an answer yet. Any update?
    Monday, June 28, 2010 2:39 PM
  • My client ended up going back to 6.1.  That was the only way around it.
    Monday, June 28, 2010 2:51 PM
  • Hello,

    our customers do need to see the empty lines in the results. Can I request a fix so that it is choosable how the MDX should be generated, with NONEMPTY or without.

    This is very urgent.

    Markus

    Thursday, January 19, 2012 1:00 PM
  • The 'forced NONEMPTY' issue has been resolved in the 2229 hotfix for ProClarity Professional and Server. What you'll want to do is install the 2229 pro/pas hotfix or later, then open the view in ProClarity Professional and uncheck the 'Use non-empty crossjoin' box. That should clear the NONEMPTY.

    http://support.microsoft.com/kb/2450921


    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Joey B. Pruett Monday, February 13, 2012 7:55 PM
    • Marked as answer by Joey B. Pruett Monday, February 13, 2012 7:55 PM
    Thursday, February 9, 2012 11:56 PM