Now let’s list all of the databases available on the Analysis server “PowerServer\SQL2008” as shown below. [Refer Figure 1.1]
$database=$server.databases
$database|select-object name
Result
Name
----
Advent2
Adventure Works 2008 DWsss
Adventure Works 2008 -3
Adventure Works 2008 -2
AAA
Adventure Works Test
Adventure Works 2008 DW
MyCube
xyz
Marketing
Finance1
Finance2
Finance3
Marketing1
Figure 1.1
Now let us connect to the “Adventure Works 2008 DW” database on the Analysis Service isntance “SQL2008” on the host “PowerServer3” as shown below. [Refer Figure 1.2]
$advdb=$database["Adventure Works 2008 DW"]
Figure 1.2
Now let’s list all of the cubes in the “Adventure Works 2008 DW” database as shown below. [Refer Figure 1.3] Here we are using the AnalysisServices.cube class to get all of the information about the dimensions.
$Cubes=New-object Microsoft.AnalysisServices.Cube
$Cubes=$advdb.cubes
$Cubes|select Name
Result:
Name
----
Adventure Works
Mined Customers
Figure 1.3
Now let’s find all of the Cubes, its state and the last processed date using the Windows PowerShell script block as shown below. [Refer Figure 1.4]
$Cubes|select name,state,lastprocessed
Figure 1.4
Set the process type to “ProcessFull” and process the “Adventure Works” cube as shown below. [Refer Figure 1.5]
$ProcessType="ProcessFull"
foreach ($cube in $Cubes)
{
if ($Cube.Name -eq "Adventure Works")
{
Write-host "Processing Cube: $cube"
$Cube.Process($ProcessType)
}
else
{
Write-host "Skipped Processing Cube: $cube"
}
}
Figure 1.5
From the result, you see that the script block processed the “Adventure Works” Cube and skipped the rest of the Cubes. [Refer Figure 1.6]
Figure 1.6
Now let us find all of the cubes, its state and the last processed date as shown below. [Refer Figure 1.7]. The result shows the last processed date and time information of the “Adventure Works” cube has been updated to the current date and time.
$cubes|select name,state,lastprocessed
Figure 1.7
Now let’s find how long it took the process the “Adventure Works” cube by using the Windows PowerShell script block as shown below. [Refer figure 1.8]
$ProcessType="ProcessFull"
foreach ($cube in $Cubes)
{
if ($Cube.Name -eq "Adventure Works")
{
$date1=get-date
write-host "---------------------------------------------"
Write-host "Processing Cube: $cube"
$Cube.Process($ProcessType)
$date2=get-date
write-host "Processing took " ($date2-$date1).Hours " Hours, " ($date2-$date1).Minutes " Mins, " ($date2-$date1).Seconds " Secs "
}
else
{
Write-host "Skipped Processing Cube: $cube"
}
}
Figure 1.8
Now let’s process all of the cubes and dimensions of the Analysis Service Database using the below Windows PowerShell script block. [Refer Figure 1.9]
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")
$database=$server.databases
$advdb=$database["Adventure Works 2008 DW"]
$date1=get-date
write-host "---------------------------------------------"
Write-host "Processing Database : $advdb"
$ProcessType="ProcessFull"
$advdb.Process($ProcessType)
$date2=get-date
write-host "Processing took " ($date2-$date1).Hours " Hours, " ($date2-$date1).Minutes " Mins, " ($date2-$date1).Seconds " Secs "
Figure 1.9
Check processing dates of all of the cubes and dimensions using the Windows PowerShell script shown below. [Refer Figure 1.10]
$Cubes=New-object Microsoft.AnalysisServices.Cube
$Cubes=$advdb.cubes
$Dimensions=New-object Microsoft.AnalysisServices.Dimension
$Dimensions=$advdb.dimensions
$cubes|select name,state,lastprocessed
$dimensions|select name,state,lastprocessed
Figure 1.10
Conclusion
This installment of our series illustrated how to process a given cube on Analysis Service database. It also illustrated how to process all of the cubes and dimensions on Analysis Service database.
» See All Articles by Columnist MAK
