Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 18, 2009

Processing an Analysis Service Cube using Windows PowerShell and SQL Server 2008 AMO

By Muthusamy Anantha Kumar aka The MAK

Part 1 of our “Microsoft Windows PowerShell and SQL Server 2008 AMO” series illustrated how to install Windows PowerShell and connect to an Analysis Service Servername. Part 2 illustrated how to use Windows PowerShell and AMO to get the various Server properties of SQL Server Analysis Service. Part 3 discussed how to use Windows PowerShell and AMO to get database properties of SQL Server Analysis Service and Part 4 illustrated how to create an Analysis Service Database using Windows PowerShell and AMO. Part 5 illustrated how to backup an Analysis Service database using Windows PowerShell and SQL Server 2008 AMO. Part 6 of this article series illustrated how to restore a Analysis Service database from a backup file with no password protection. Part 7 of this article series illustrated how to restore an Analysis Service Database from a database backup file with password protection. Part 8 illustrated how to process a dimension in analysis service database. Part 9 of this article series illustrates how to process a single cube and also process the entire Analysis Service database.

Let’s connect to the Analysis Service server “PowerServer\SQL2008” as shown below. [Refer Figure 1.0] Here we are loading the Analysis Service Manage object DLL and connecting to the Analysis Service server instance “SQL2008” on the host “PowerServer3”.

[System.Reflection.Assembly]::LoadWithPartialName
	("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")

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

list all of the databases available on the Analysis server
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"]

connect to the
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

list all of the cubes  in the
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

find all of the Cubes, its state and the last processed date using the Windows PowerShell script block
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"
}

}

Set the process type to
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]

the script block processed the
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

find all of the cubes, its state and the last processed date
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"
}

}

find how long it took the process the
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 "

process all of the cubes and dimensions of the Analysis Service Database using the below Windows PowerShell script block
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

Check processing dates of all of the cubes and dimensions using the Windows PowerShell script
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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date