Question on msmdsrv.ini file settings

Question

Question on msmdsrv.ini file settings

Mossy-552515

SSCommitted

Points: 1919
More actions
March 3, 2009 at 11:51 pm

#201022

Hi,
We recently had a problem running a query in SQL Server 2008 SSAS that resulted in an error message saying that the '4,294,967,296 tuples exceeded'. No results were produced. After testing we found the exact same query on a cube worked fine in SQL 2005 but not SQL 2008.
Went through MS support and we were asked to alter the following settings in the msmdsrv.ini file for SQL 2008:
The original value for both settings was 0. This did fix our problem and we are happy with the result. However we have to document why and of course may need to redo this in the future to get around other issues. So my question is this:
Does anyone out there know what these settings are for and what they alter?
As always my thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 1

What settings did you change?

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Mossy-552515 SSCommitted Points: 1919 More actions · Answer 2

Sorry about that - my bad.

The original settings in the file were

NamedSetShallowExistsMode

AxisSetShallowExistsMode

- both were set to 0 and we changed both to 1

and restarted the SSAS service for it to take effect. It seemed to resolve our issue.

Scott

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 3

For the first one I found this in the BOL (Breaking Changes to Analysis Services Features in SQL Server 2008):

NamedSetShallowExistsMode

Issue Type

The shallow exists function now works differently with named sets that contain enumerated members or crossjoins of enumsets.

Issue Description

In SQL Server 2005 Analysis Services (SSAS), the shallow exists function did not work with named sets that contained enumerated members or crossjoins of enumsets. For backward compatibility with the original release version and SP1 of SQL Server 2005 Analysis Services (SSAS), set the configuration property "ConfigurationSettings\OLAP\Query\NamedSetShallowExistsMode" to 1, or for backward compatibility with SQL Server 2005 Analysis Services (SSAS) SP2, set it to 2.

Not sure what the other one is about or at least can't find anything yet on it. Might need to see what your MDX actually looks like and the named sets and calculations you are referencing in your statement.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Mossy-552515 SSCommitted Points: 1919 More actions · Answer 4

Thanks Dan,

Muchly appreciated for this. Out of interest the quesry being run is:

With

Set [SetAxisX] As '{[Measures].[Procedure Count]}'

Set [SetAxisY] As '{{NONEMPTY( {[Patient].[T2 Episode].[T2 Episode].AllMembers}

* {HIERARCHIZE ( [Patient].[Admission Date].[Admission Date].AllMembers )}

* {[Patient].[Discharge Date].[Discharge Date].AllMembers}

* {[DRG].[DRG Code].[DRG Code].AllMembers}

* {[Patient].[Discharge Status].[Discharge Status].AllMembers}

* {[Revenue Source].[Revenue Source].[Revenue Source].AllMembers}

* {[Patient].[Age Years].[Age Years].AllMembers}

* {[Episode Type].[Episode Type].[Episode Type].AllMembers}

* {[Patient].[Unit Ward].[Unit Ward].AllMembers} , [SetAxisX] )}}'

Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'

Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'

Select

[NewSetX] On Columns ,

[NewSetY] On Rows

From [Procedure]

WHERE ( [Period].[Fiscal].[Fiscal Month].&[200903] ,[Phase].[Phase].&[11], [DRG].[DRG Hierarchy].&[20] )

I guess the Shallow functions being referred to are the NONEMPTY functions. Again thanks for the help.

Scott

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 5

That is quite the MDX. As far as NewSetY goes, you already have the NONEMPTY statement in the SetAxisY and you shouldn't need the other NewSetY at all because there should always be a value returned for that measure and you could just reference the measures unless there is a reason you want to set this up in a named set, just make sure you update the reference to this in SetAxisY.

SELECT

{[Measures].[Procedure Count]} ON COLUMNS

,NON EMPTY

[SetAxisY] ON ROWS

FROM [Procedure]

WHERE

(

[Period].[Fiscal].[Fiscal Month].&[200903]

,[Phase].[Phase].&[11]

,[DRG].[DRG Hierarchy].&[20]

)

As far as all of the crossjoins go in the SetAxisY you should see better performance if you group the common dimension references together so that all of the Patient attributes are grouped together. I am not sure how this is being used for delivery, but you should see better performance. Refer to this posting my Mosha about ordering your crossjoin - Optimizing order of sets in MDX crossjoins.

Also, you might want to take a look at this posting by Mosha - To quote or not to quote (in expressions of MDX calculations). You can use his online MDX Studio, http://mdx.mosha.com/[/url], to format, parse, and actually analyze your MDX to suggest performance or syntax improvements.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Mossy-552515 SSCommitted Points: 1919 More actions · Answer 6

Thank Dan,

Will do some reading and some tuning. Thank you for the info. Cheers.

Scott