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

Using Windows PowerShell and AMO to create an SSAS Database

By Muthusamy Anantha Kumar aka The MAK

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



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