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?

What's the MDX being executed when this happens?

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]

Let me see whatwe can find on this. Thanks.

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.

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 onceI have some solid answers for you.

Kind Regards,
Amanda Ruffner

Thanks Amanda!

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

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. Im 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 vastlyimproved 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 youre seeing some other type ofproblems with it, we should investigate those too. Amanda is out this week, but I can do some investigating.

-Joey

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. Im 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 vastlyimproved 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 youre seeing some other type ofproblems with it, we should investigate those too. Amanda is out this week, but I can do some investigating.

-Joey

• Marked as answer by Sean.FlanaganMicrosoft employee, Moderator Monday, July 27, 2009 2:51 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. Im 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 vastlyimproved 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 youre seeing some other type ofproblems with it, we should investigate those too. Amanda is out this week, but I can do some investigating.

-Joey

• Marked as answer by Sean.FlanaganMicrosoft employee, Moderator Monday, July 27, 2009 2:51 PM

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

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.

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.

seems we don't have an answer yet. Any update?

My client ended up going back to 6.1.  That was the only way around it.

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

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