ProClarity (6.2) Hanging when Adding Grand Totals

I have a view set up that has approx 40 rows and 15 columns. Prior to applying the view - i have filtered the grid to remove empty rows and columns. The data comes back very quickly. However - when I add grand totals to the columns and rows and apply - the application says "Running Query" but never comes back. Is there an option that I can set?

Sorry I don't have a quick answer for this.

I opened up Proclarity on my desktop and connected to AdventureWorks and followed steps similar to what you've desribed. The Grand Totals are calculated in the MDX and not by Proclarity itself. Here is the MDX Proclarity generated for this:

Code Snippet

WITH
MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS
'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Date].[Calendar].[All Periods].CHILDREN }, [Date].[Calendar] ) } }, { [Date].[Calendar].[All Periods].CHILDREN } ) )',
SOLVE_ORDER = 1000
MEMBER [Product].[Product Categories].[All Products].[ Grand Total] AS
'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Product].[Product Categories].[All Products].CHILDREN }, [Product].[Product Categories] ) } }, { [Product].[Product Categories].[All Products].CHILDREN } ) )',
SOLVE_ORDER = 1000
SELECT
NON EMPTY { { [Date].[Calendar].[All Periods].CHILDREN }, ( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON COLUMNS ,
NON EMPTY { { [Product].[Product Categories].[All Products].CHILDREN }, ( [Product].[Product Categories].[All Products].[ Grand Total] ) } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

So, I think you need to first determine if this is a Proclarity issue or if this is an SSAS issue. Open an MDX query window in SQL Server Management Studio and run this query. Does it behave just as poorly in the query window as it does in Proclarity? If it does, then you can focus on SSAS (kinda).

I suspect the problem is in how the MDX is attempting to assemble the Grand Totals. In the example above, you could get the same values back with a much simpler expression.

So, what options do you have? Well, always make sure you are on the latest service pack of SSAS to insure you get the best performance possible. If you are publishing these results as a "static" view in a Proclarity workbook, you could rewrite the query for better performance. Other than that, I'm not sure what else you can really do with this.

B.

|||

A colleague of mine contacted ProClarity and they said the Grand Totals display had known bugs.

I know its not the same issue, but presumably its related.

Its to do with how aggregations are handled.

Processing multiple rows and multiple columns in SSIS

I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!

My first suggestion would be to run through the BOL tutorial for SSIS. This will show you some information on the data flow task and some of the things it can help you do. I would agree with the previous post that you should be using a data flow task, with an ado.net connection source, the misc. transformations that will manipulate your data and then your destination.

You might consider picking up one of the various SSIS books if you are going to be doing any amount of work in this environment (either the wrox professional or Kirk Haselden's books are both good starts).

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and
I'm getting what looks like a fatal error in SQL Server. It shows up with
this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put o
n sp3, it doesn't happen.Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes a
nd I'm getting what looks like a fatal error in SQL Server. It shows up wit
h this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Serv
er is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3,
it doesn't happen.|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.
Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.
|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.