INF: How to Check for 64 K Children Before You Process a Dimension (307008)

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: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

' ----------------------------------------------------------------------------
'
'	ChildrenCount.vbs
'	childrencount.vbs -s Server -d Database -c Cube -dm Dimension -min Minimum Children
'        Example: childrencount.vbs -s <ServerName> -d "FoodMart 2000" -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("ADOMD.Catalog")

Dim cst 
Set cst = CreateObject("ADOMD.Cellset")

Set oArgs = WScript.Arguments

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

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

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

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

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

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

strTemp = oArgs.Item(8)
If strTemp = "-min" 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 = "Data Source=" & txtSvr & ";Provider=msolap; initial catalog=" & txtDatabase & ";Default Isolation Mode=1;Execution Location=3"

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


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

Wscript.echo "Server : " & txtsvr & " , Cube : " & txtcube
Wscript.echo "Dimension : " & txtDim & " on " & Date & " " & Time
Wscript.echo "Members with more than " & StrMin & " children.... "
'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) & " children under : " & Chr(09) & cst.Axes(0).Positions(J).Members(0).Caption

	End If


Next

Wscript.echo "Done."

cst.Close
Set cst = Nothing
Set cat = Nothing