Using Windows PowerShell and AMO to create an SSAS Database

January 7, 2009

Be advised that this article is intended for a test environment, not for production!

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 [of this article illustrated how to use Windows PowerShell and AMO to get the various Server properties of SQL Server Analysis Service. Part 3 of this article illustrated how to use Windows PowerShell and AMO to get database properties of SQL Server Analysis Service. Part 4 of this article is going to illustrate how to create an Analysis Service Database using Windows PowerShell and AMO.

Let’s connect to Analysis Server using Windows PowerShell using AMO and create a database “Finance” as shown below. [Refer Figure 1.1]

[System.Reflection.Assembly]::LoadWithPartialName
 ("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("PowerServer3\SQL2008")
$server.databases.add("Finance")
$db=$server.databases.item("Finance")
$db.update()
$db.description = "Finance Department Data Mart"
$db.update()

Note: PowerServer3 is the Analysis Server with named instance SQL2008. Please replace the server name with the Analysis server name in your environment. Please replace the database name with the actual database name that you want to create on the Analysis server in your environment.

Result

DismissedValidationRules    : {}
DismissedValidationResults  : {}
LastUpdate                  : 1/1/0001 12:00:00 AM
AggregationPrefix           :
ProcessingPriority          : 0
EstimatedSize               : 0
Visible                     : True
Language                    : 0
Collation                   :
DataSourceImpersonationInfo :
MasterDataSourceID          :
Dimensions                  : {}
Cubes                       : {}
MiningStructures            : {}
Assemblies                  : {}
DataSources                 : {}
DataSourceViews             : {}
Accounts                    : {}
Roles                       : {}
DatabasePermissions         : {}
Translations                : {}
ReadWriteMode               : ReadWrite
DbStorageLocation           :
Parent                      : POWERSERVER3\SQL2008
LastProcessed               : 1/1/0001 12:00:00 AM
State                       : Unprocessed
IsLoaded                    : True
CreatedTimestamp            : 1/1/0001 12:00:00 AM
LastSchemaUpdate            : 1/1/0001 12:00:00 AM
Description                 :
Annotations                 : {}
ID                          : Finance
Name                        : Finance
Site                        :
SiteID                      :
OwningCollection            : {Adventure Works DW 2008, MyCube, sample, xyz...}
Container                   :

PS C:\> $db=$server.databases.item("Finance")
PS C:\> $db.update()
PS C:\> $db.description = "Finance Department Data Mart"
PS C:\> $db.update()
PS C:\>

When you connect to SQL Server Analysis Server using SQL Server Management Studio, you see that the database “Finance” was created with the description we used when creating the database. [Refer Figure 1.2]

Now let’s query all the databases available on the server using Windows PowerShell and SQL Server 2008 AMO as shown below. [Refer Figure 1.3]

$server.databases | select name

Result

Name
----
Adventure Works DW 2008
MyCube
test
sample
xyz
Finance

Let’s try to delete a database on the Analysis server by using Windows PowerShell and SQL Server 2008 AMO. [Refer Figure 1.4]

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

Note: PowerServer3 is the Analysis Server with named instance SQL2008. Please replace the server name with the Analysis server name in your environment. Please replace the database name with the actual database name that you want to delete from the Analysis server in your environment.

Caution: Please do not execute this script on your production environment.

When you connect to SQL Server Analysis Server using SQL Server Managerment Studio, you see that the database “test” was deleted. [Refer Figure 1.5]

Now let us create a Windows PowerShell script that would create a database on any Analysis server and database that we pass as parameter. Create a file C:\AMOScripts\create-ssasDB.ps1 using notepad and copy and paste the code below to that file and save it. [Refer Figure 1.6]

#Objective: To create a database on the given Analysis Server 
#Created by: MAK
#Create Date: 9/9/2007
param
([string] $Servername, [string] $databasename, [string] $description)
## Add the AMO namespace
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName) 
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
else
{

$dbstring=$server.Databases |select-object name| select-string -simplematch $databasename

if ($dbstring)
{
 Write-host "Database " $databasename " already exist on the server $Servername"
 break
}
else
{
$server.databases.add($databasename)
$db=$server.databases.item($databasename)
$db.update()
$db.description = $description
$db.update()

}
}

Please download the script from here.

Now let us create the database “Marketing” on the server PowerServer3\SQL2008 Analysis Service instance using the create-ssasDB.ps1 script as shown below.

.\create-SSASDB.ps1 "PowerServer3\SQL2008" "Marketing" "Data Mart for Marketing Department"

You will see results similar to the output shown below. [Refer Figure 1.7]

DismissedValidationRules    : {}
DismissedValidationResults  : {}
LastUpdate                  : 1/1/0001 12:00:00 AM
AggregationPrefix           :
ProcessingPriority          : 0
EstimatedSize               : 0
Visible                     : True
Language                    : 0
Collation                   :
DataSourceImpersonationInfo :
MasterDataSourceID          :
Dimensions                  : {}
Cubes                       : {}
MiningStructures            : {}
Assemblies                  : {}
DataSources                 : {}
DataSourceViews             : {}
Accounts                    : {}
Roles                       : {}
DatabasePermissions         : {}
Translations                : {}
ReadWriteMode               : ReadWrite
DbStorageLocation           :
Parent                      : POWERSERVER3\SQL2008
LastProcessed               : 1/1/0001 12:00:00 AM
State                       : Unprocessed
IsLoaded                    : True
CreatedTimestamp            : 1/1/0001 12:00:00 AM
LastSchemaUpdate            : 1/1/0001 12:00:00 AM
Description                 :
Annotations                 : {}
ID                          : Marketing
Name                        : Marketing
Site                        :
SiteID                      :
OwningCollection            : {Adventure Works DW 2008, MyCube, sample, Finance
                              ...}
Container                   :

When you connect to SQL Server Analysis Server using SQL Server Management Studio, you see that the “Marketing” database was created with the description we used when creating the database. [Refer Figure 1.8]

Now let’s try to create a database that already exists on the server. Execute the script below. You will get a message indicating that the database already exists on the server. [Refer Figure 1.9]

.\create-SSASDB.ps1 "PowerServer3\SQL2008" "Finance" "Test"

Results

Database  Finance  already exist on the server PowerServer3\SQL2008

Conclusion

This article has illustrated how to create an Analysis Service Database using Windows PowerShell and AMO.

» See All Articles by Columnist MAK