通过Powershell检查cube中每个度量是否都有值

 

[void][reflection.assembly]::LoadFile("C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll") 

$conn = New-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection;

 

write-host "Server(default localhost):"
$strServer= read-host
write-host "Database(default DBName):"
$strDatabase = read-host
write-host "Cube(default CubeName, press 'a' for ALL):"
$strCube=read-host

if ([System.String]::IsNullOrEmpty($strServer))
{
    $strServer="localhost\r2"
}
if ([System.String]::IsNullOrEmpty($strDatabase))
{
    $strDatabase="DBName"
}
if ([System.String]::IsNullOrEmpty($strCube))
{
    $strCube="CubeName"
}

write-host "Connect server:" $strServer ", database:" $strDatabase ",cube:" $strCube

$conn.ConnectionString = "Provider=MSOLAP.3;Persist Security Info=True;Data Source= " + $strServer + " ;Initial Catalog= " +$strDatabase;
$conn.Open();

write-output "Opened."

foreach ($cu in $conn.Cubes)
{
    if ($cu.Type -eq [Microsoft.AnalysisServices.AdomdClient.CubeType]::Cube)
    {  
        if (($cu.Name -eq $strCube) -or ($strCube.ToUpper() -eq "A"))
        {
            write-output "---------------------------------------"
            write-output "Cube:" $cu.Name;
            write-output "---------------------------------------"
           
            $total=0
            $zero=0
           
            foreach ($m in $cu.Measures)
            {
                $total++
               
                $strMDX = "SELECT " + $m.UniqueName + " ON 0 FROM [" + $cu.Name + "]"
                #write-host $strMDX
               
                $comm=$conn.CreateCommand()
                $comm.CommandText=$strMDX
                $reader=$comm.ExecuteReader()
                $t = $reader.Read()
               
                $result=0
               
                if ($reader[0] -eq $null)
                {
                    $result=0
                }
                else
                {
                    $result = [System.Convert]::ToDouble($reader[0])
                    $reader.Close()
                   
                    if ($result -gt 0)
                    {
                        #write-output $result
                        $zero++
                        continue
                    }
                }
               
                $name=$m.Name
                write-output "$name : $result"
               
                #write-output $m.Name
                $reader.Close()
            }
           
            write-output "------------------------------------$zero/$total"
        }
    }
}

 

Keypoints:

如何在powershell引入并且使用adomd.net对象。

如何声明一个.net对象。

如何使用.net枚举。

如何使用.net下实例或者静态方法。