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 4, 2009

Process Dimensions in an Analysis Service database

By Muthusamy Anantha Kumar aka The MAK

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
	("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")

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

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"]

Connect to the
Figure 1.2

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

list all of the dimensions in the
Figure 1.3

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

find all of the dimensions, 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 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"
}

}

Set the process type to
Figure 1.5

From the result, you see that the script block processed the dimension “date” and skipped the rest of the dimensions. [Refer Figure 1.6]

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

find all of the dimensions, its state and the last processed date
Figure 1.7

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 "

}

}

Process all of the dimensions in the
Figure 1.8

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

the result shows that the last processed date and time information of all the dimensions are updated to the current date and time
Figure 1.9

Conclusion

This 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.

» 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