Process Dimensions in an Analysis Service databaseMarch 4, 2009 Part 1 of this article series “Microsoft Windows PowerShell and SQL Server 2008 AMO” illustrated how to install Windows PowerShell and connect to an Analysis Service Servername. Part 2 demonstrated 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 Analysis Service Database using Windows PowerShell and AMO. Part 5 explained how to backup an Analysis Service database using Windows PowerShell and SQL Server 2008 AMO. Part 6 of this article series examines 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 of this series explains how to process a dimension in an Analysis Service database. Now 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 “PowerServer3” host. [System.Reflection.Assembly]::LoadWithPartialName
Click for larger image Let’s list all of the databases available on the “PowerServer\SQL2008” Analysis server 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
Click for larger image Connect to the “Adventure Works 2008 DW” database on the Analysis Service instance “SQL2008” on the host “PowerServer3” as shown below. [Refer Figure 1.2] $advdb=$database["Adventure Works 2008 DW"]
Let’s list all of the dimensions in the “Adventure Works 2008 DW” database as shown below. [Refer Figure 1.3] Here we are using the Analysisservices.Dimension class to get all of the information about the dimensions. $dimensions=New-object Microsoft.AnalysisServices.Dimension $dimensions=$advdb.dimensions $dimensions|select Name Result: Result: Name ---- Promotion Product Customer Geography Reseller Sales Territory Employee Scenario Department Organization Account Date Source Currency Sales Reason Reseller Sales Order Details Internet Sales Order Details Clustered Customers Subcategory Basket Analysis Sales Channel Destination Currency Sales Summary Order Details
Now let’s find all of the dimensions, its state and the last processed date using the Windows PowerShell script block as shown below. [Refer Figure 1.4] $dimensions|select name,state,lastprocessed
Set the process type to “ProcessFull” and process the dimension “Date” as shown below. [Refer Figure 1.5]
$ProcessType="ProcessFull"
foreach ($dim in $dimensions)
{
if ($dim.Name -eq "Date")
{
Write-host "Processing Dimension: $dim"
$dim.Process($ProcessType)
}
else
{
Write-host "Skipped Processing Dimension: $dim"
}
}
From the result, you see that the script block processed the dimension “date” and skipped the rest of the dimensions. [Refer Figure 1.6]
Let’s find all of the dimensions, its state and the last processed date as shown below. [Refer Figure 1.7] From the result, you see that the last processed date and time information of the dimension “Date” is updated to the current date and time. $dimensions|select name,state,lastprocessed
Process all of the dimensions in the “Adventure Works 2008 DW” database on the Analysis Service instance “SQL2008” on the host “PowerServer3” by executing the following Windows Powershell script block. [Refer Figure 1.8] \
$ProcessType="ProcessFull"
foreach ($dim in $dimensions)
{
$date1=get-date
write-host "---------------------------------------------"
Write-host "Processing Dimension: $dim"
$dim.Process($ProcessType)
$date2=get-date
write-host "Processing took " ($date2-$date1).Hours " Hours, " ($date2-$date1).Minutes " Mins, " ($date2-$date1).Seconds " Secs "
}
}
From the above results, you see the dimensions that are processed and the time it took to process each dimension. Let’s find all of the dimensions, its state and the last processed date using the Windows PowerShell script block as shown below. [Refer Figure 1.9] From the result, you see that the last processed date and time information of all the dimensions are updated to the current date and time. $dimensions|select name,state,lastprocessed Result Name State LastProcessed ---- ----- ------------- Promotion Processed 2/19/2009 2:11:37 AM Product Processed 2/19/2009 2:11:42 AM Customer Processed 2/19/2009 2:11:47 AM Geography Processed 2/19/2009 2:11:48 AM Reseller Processed 2/19/2009 2:11:50 AM Sales Territory Processed 2/19/2009 2:11:51 AM Employee Processed 2/19/2009 2:11:53 AM Scenario Processed 2/19/2009 2:11:54 AM Department Processed 2/19/2009 2:11:55 AM Organization Processed 2/19/2009 2:11:57 AM Account Processed 2/19/2009 2:11:58 AM Date Processed 2/19/2009 2:12:00 AM Source Currency Processed 2/19/2009 2:12:01 AM Sales Reason Processed 2/19/2009 2:12:09 AM Reseller Sales Order De... Processed 2/19/2009 2:12:11 AM Internet Sales Order De... Processed 2/19/2009 2:12:11 AM
ConclusionThis article has illustrated a given dimension and all of the dimensions on an Analysis Service database. The next part of the series will discuss how to process a cube. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")
