Using Windows PowerShell and AMO to create an SSAS Database

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:\>

connect to Analysis Server using Windows PowerShell using AMO and create a database

Figure 1.1

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]

the database was created

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

query all the databases available on the server using Windows PowerShell and SQL Server 2008 AMO

Figure 1.3

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.

try to delete a database on the Analysis server by using Windows PowerShell and SQL Server 2008 AMO

Figure 1.4

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]

the database is deleted

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.

a Windows PowerShell script that would create a database on any Analysis server and database that we pass as parameter

Figure 1.6

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 :

results

Figure 1.7

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]

the “Marketing” database was created with the description used when creating the database

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

Database  Finance  already exist on the server

Figure 1.9

Conclusion

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

»


See All Articles by Columnist
MAK

Latest Articles