locked
Proclarity locking on large query RRS feed

  • Question

  • We have ecountered a problem with Proclarity when a user uses a larger query with a calculated measure, which causes it to lock up.  I dug though the profiler for the messages where it is locking up, and found that this call was the one that the server was dying on.

    WITH

     

    MEMBER [Measures].[ProClarityytiralCorP] as

    'COUNT( { NONEMPTY( { [Dim Drop Date].[Drop Period].DEFAULTMEMBER } *

    { [Dim Order Type].[OT Desc].[All].CHILDREN } *

    { [Dim Location].[MDL].[All].CHILDREN } *

    { [Dim Location].[Agent No].[All].CHILDREN } *

    { [Dim Package].[Package Code].[All].CHILDREN } *

    { [Dim Package].[Package].[All].CHILDREN } ,

    { [Dim Location].[Region].&[027] } *

    { [Measures].[Mail Count], [Measures].[Premiums], [Measures].[Conversions Count], [Measures].[Conversion Rate] } ) } )'

    SELECT

     

    { [Measures].[ProClarityytiralCorP] } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS

    FROM

     

    [OLAP Tracking]

    However when I pull the query that would be executed to return the actual rows, it runs without any issue in 3 seconds.  Is there a way to turn off the pre-select above that tries to warn the user if they are going to exceed 1000 rows?  Or is there some other solution that would help?

    SELECT

     

    { [Measures].[Mail Count], [Measures].[Premiums], [Measures].[Conversions Count], [Measures].[Conversion Rate] } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,

    {

    NONEMPTY( { [Dim Drop Date].[Drop Period].DEFAULTMEMBER } * { [Dim Order Type].[OT Desc].[All].CHILDREN } * { [Dim Location].[MDL].[All].CHILDREN } * { [Dim Location].[Agent No].[All].CHILDREN } * { [Dim Package].[Package Code].[All].CHILDREN } * { [Dim Package].[Package].[All].CHILDREN } , { [Dim Location].[Region].&[027] } * { [Measures].[Mail Count], [Measures].[Premiums], [Measures].[Conversions Count] } ) } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS

    FROM

     

    [OLAP Tracking]

    WHERE

     

    ( [Dim Location].[Region].&[027] )

    CELL

     

    PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

    Thursday, September 23, 2010 7:46 PM

Answers

  • Hello,

    Try turning off the "Check query result size before executing," option in Pro options.  Please let us know the results.

    Thanks,

    -Joey

    • Marked as answer by CRUecke Friday, September 24, 2010 6:23 PM
    Thursday, September 23, 2010 9:49 PM

All replies

  • Hello,

    Try turning off the "Check query result size before executing," option in Pro options.  Please let us know the results.

    Thanks,

    -Joey

    • Marked as answer by CRUecke Friday, September 24, 2010 6:23 PM
    Thursday, September 23, 2010 9:49 PM
  • This worked perfectly.  The query returned the results in 3 seconds with no lockups.  Thanks for the quick answer on this one.
    Friday, September 24, 2010 6:23 PM