Microsoft KB Archive/307008

= INF: How to Check for 64 K Children Before You Process a Dimension =

Article ID: 307008

Article Last Modified on 2/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q307008



SUMMARY
This article illustrates how you can determine the number of children before you process a dimension. In Analysis Services 2000, no member can have more than 64 K children. The solution for dimensions with members that contain more than 64 K children is to use the Analysis Services 2000 Automatic Member Grouping feature. However, you may wonder how you can determine whether the dimension has members that contain more than 64 K children. Over time, is there a way to test and issue a warning if a dimension has more than 64 K children before dimension processing?



MORE INFORMATION
The simplest solution is to run a query in the database. You can run a query manually during design time, and programmatically before updating dimensions during your periodic processing. Suppose you have snowflake dimension table A that joins to table B:

Table A dim_key parent_key (foreign key to B.parent_key) [other attributes] Table B parent_key [other attributes] The code is: SELECT B.parent_key, count(A.dim_key) as child_cnt FROM B inner join A ON (A.parent_key = B.parent_key) GROUP BY B.parent_key HAVING count(A.dim_key) > 64000 Alternately, you can use the Microsoft VBscript CountChildren.vbs script to count the dimension members in Analysis Services.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

' ' '  ChildrenCount.vbs '  childrencount.vbs -s Server -d Database -c Cube -dm Dimension -min Minimum Children '       Example: childrencount.vbs -s  -d &quot;FoodMart 2000&quot; -c Sales -dm Customers -min 60 '      '   Author : Marc ABEHASSERA - Microsoft Consulting Services '  Date : March 2001 ' '  Check the number of children per parent to avoid blocking situations '---

Dim oArgs Dim strTemp Dim txtSvr Dim txtDatabase Dim txtCube Dim txtDim Dim StrLogFileName Dim I, J

Dim cat Set cat = CreateObject(&quot;ADOMD.Catalog&quot;)

Dim cst Set cst = CreateObject(&quot;ADOMD.Cellset&quot;)

Set oArgs = WScript.Arguments

erromsg = &quot;syntax : childrencount.vbs -s Server -d Database -c Cube -dim Dimension -min Minimum Children&quot;

'Check the number of arguments If (oArgs.Count <> 10) Then WScript.Echo erromsg WScript.Quit (1) End If

strTemp = oArgs.Item(0) If strTemp = &quot;-s&quot; Then txtSvr = oArgs.Item(1) 'Server Else WScript.Echo erromsg WScript.Quit (1) End If

strTemp = oArgs.Item(2) If strTemp = &quot;-d&quot; Then txtdatabase = oArgs.Item(3) 'Database Else WScript.Echo erromsg WScript.Quit (1) End If

strTemp = oArgs.Item(4) If strTemp = &quot;-c&quot; Then txtcube = oArgs.Item(5) 'Cube Name Else WScript.Echo erromsg WScript.Quit (1) End If

strTemp = oArgs.Item(6) If strTemp = &quot;-dim&quot; Then txtdim = oArgs.Item(7) 'Dimension Name Else WScript.Echo erromsg WScript.Quit (1) End If

strTemp = oArgs.Item(8) If strTemp = &quot;-min&quot; Then StrMin = oArgs.Item(9) 'Minimum Number of Children Else WScript.Echo erromsg WScript.Quit (1) End If

J = 0

' Connect to the server

cat.ActiveConnection = &quot;Data Source=&quot; & txtSvr & &quot;;Provider=msolap; initial catalog=&quot; & txtDatabase & &quot;;Default Isolation Mode=1;Execution Location=3&quot;

StrSource = &quot; SELECT {[&quot; & txtDim &&quot;].members - Descendants(&quot; & txtdim & &quot;,, Leaves) } on 0 From &quot; & txtCube

cst.Source = StrSource Set cst.ActiveConnection = cat.ActiveConnection cst.Open ' Write some infos...

Wscript.echo &quot;Server : &quot; & txtsvr & &quot;, Cube : &quot; & txtcube Wscript.echo &quot;Dimension : &quot; & txtDim & &quot; on &quot; & Date & &quot; &quot; & Time Wscript.echo &quot;Members with more than &quot; & StrMin & &quot; children.... &quot; 'Wscript.echo

' For each cell, get the number of children for the member

For J = 0 To cst.Axes(0).Positions.Count - 1

If cst.Axes(0).Positions(J).Members(0).ChildCount > CLng(StrMin) Then ' Write the member if it has more than StrMin Children...    Wscript.echo cst.Axes(0).Positions(J).Members(0).LevelName & Chr(09) &  Chr(09) & cst.Axes(0).Positions(J).Members(0).ChildCount & Chr(09) & &quot; children under : &quot; & Chr(09) & cst.Axes(0).Positions(J).Members(0).Caption

End If

Next

Wscript.echo &quot;Done.&quot;

cst.Close Set cst = Nothing Set cat = Nothing

Additional query words: OLAP bihowto

Keywords: kbinfo KB307008

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.