Part 1 of this series on Microsoft
Windows PowerShell and SQL Server 2008 AMO illustrated how to install Windows
PowerShell and connect to an Analysis Service Servername. Part 2 illustrated 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 illustrated how to backup an
Analysis Service database using Windows PowerShell and SQL Server 2008 AMO. Part 6 of this article series
illustrated how to restore an Analysis Service database from a backup file with
no password protection. Part 7 discussed how to restore an Analysis
Service database from a database backup file with password protection. Part 8 covered how to process a
dimension in Analysis Service database and Part 9 illustrated how to process a
single cube and also process the entire Analysis service database. Part
10 examined the various types of detach and attach methods to detach
and attach an Analysis Service database. Part
11 illustrated how to script dimension from a SQL Server Analysis Service
instance to XMLA format file. This installment is going to illustrate how to
script a cube from a SQL Server Analysis Service instance to XMLA format file.
Let's begin by connecting
to the Analysis Service server PowerServer\SQL2008 using Windows PowerShell
and AMO as shown below. [Refer Figure 1.0] Here you 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")
Figure 1.0
Now let's
list all the databases available on the Analysis server PowerServer\SQL2008 as shown below. [Refer Figure 1.1]
$database=$server.databases
$database|select-object name
Result
Name
----
Advent2
Adventure Works 2008 DW
Adventure Works 2008 -3
AAA
Adventure Works Test
Adventure Works 2008 -2
MyCube
xyz
Marketing
Finance1
Finance2
Finance3
Marketing1
MyDb123
Figure 1.1
Connect
to the database Adventure
Works 2008 -2 on the Analysis Service instance
SQL2008 on the host PowerServer3 as shown below. [Refer Figure 1.2]
$advdb=$database["Adventure Works 2008 -2"]
Figure 1.2
Now let's assign the cube object Adventure Works to the $Adventcube variable
as shown below.
$Adventcube = $advdb.Cubes.FindByName("Adventure Works")
Prepare and setup the Scripter Object as shown below.
$stringbuilder = new-Object System.Text.StringBuilder
$stringwriter = new-Object System.IO.StringWriter($stringbuilder)
$xmlOut = New-Object System.Xml.XmlTextWriter($stringwriter)
$xmlOut.Formatting = [System.Xml.Formatting]::Indented
$scriptObject = New-Object Microsoft.AnalysisServices.Scripter
Use the scriptCreate
method to generate the script for creating the cube Adventure Works as shown
below.
$MSASObject=[Microsoft.AnalysisServices.MajorObject[]] @($Adventcube)
$ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)
Now let's
store the generated XML on to a file using the PowerShell cmdlet Out-File as
shown below.
$stringbuilder.ToString() |out-file -filepath c:\AdventDb_Adventureworks_cube.xmla
You can open
and see the content of the file c:\AdventDb_Adventureworks_cube.xmla as shown
below. [Figure 1.3]
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Adventure Works 2008 -2</DatabaseID>
</ParentObject>
<ObjectDefinition>
<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<ID>Adventure Works</ID>
<Name>Adventure Works</Name>
<Annotations>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:UseDiagramDefaultLayout</Name>
<Value>true</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowRelationshipNames</Name>
<Value>false</Value>
</Annotation>
<Annotation>
...
...
..
..
</Action>
</Actions>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Cube>
</ObjectDefinition>
</Create>
Figure 1.3
Create
the PowerShell script C:\AMOScripts\Generate_Dim_Script.ps1 that generates a
script for any cubes in any database. Copy and paste the below given code to C:\AMOScripts\Generate_Cube_Script.ps1
as shown below. [Refer Figure 1.4]
param
(
[string] $servername,
[string] $databasename,
[string] $cubename,
[string] $location
)
[string] $mydate=date
$mydate=$mydate.replace(":"," ")
$mydate=$mydate.replace("/"," ")
$mydate="_" +$mydate.replace(" ","_")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
$database=$server.databases
$advdb=$database["$databasename"]
$AdventCube = $advdb.cubes.FindByName("$cubename")
$stringbuilder = new-Object System.Text.StringBuilder
$stringwriter = new-Object System.IO.StringWriter($stringbuilder)
$xmlOut = New-Object System.Xml.XmlTextWriter($stringwriter)
$xmlOut.Formatting = [System.Xml.Formatting]::Indented
$scriptObject = New-Object Microsoft.AnalysisServices.Scripter
$MSASObject=[Microsoft.AnalysisServices.MajorObject[]] @($AdventCube)
$ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)
$filename=$location+$cubename+"_"+$mydate+"_cube.xmla"
$stringbuilder.ToString() |out-file -filepath $filename
Figure 1.4
Let's get
to the C:\AMOScripts location using set-location cmdlet as shown below.
Set-Location C:\AmoScripts
Execute
the C:\AMOScripts\Generate_Dim_Script.ps1 with Servername
PowerServer3\SQL2008, database name Adventure Works 2008 -2, cube name Adventure Works and with the location name
C:\Data\ as shown below. [Refer Figure 1.5]
.\Generate_Cube_Script.ps1 "PowerServer3\SQL2008" "Adventure Works 2008 -2" "Adventure Works" "C:\Data\"
Figure 1.5
When the
script is executed, you see that the XMLA format file for the cube Adventure
Works is generated under C:\Data folder as shown below. [Refer Fig 1.6]
Figure 1.6
You can open
and see the content of the file c:\data\
Adventure
Works__04_08_2009_20_44_42_cube.xmla as shown below. [Figure 1.7]
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Adventure Works 2008 -2</DatabaseID>
</ParentObject>
<ObjectDefinition>
<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<ID>Adventure Works</ID>
<Name>Adventure Works</Name>
<Annotations>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:UseDiagramDefaultLayout</Name>
<Value>true</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowRelationshipNames</Name>
<Value>false</Value>
</Annotation>
...
...
...
...
...
</Action>
</Actions>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Cube>
</ObjectDefinition>
</Create>
Figure 1.7
You can also
see that the file name is generated on the fly with the cube name and time
stamp.
Conclusion
This installment
illustrated how to generate a script for a given cube of the Analysis Service
database.
»
See All Articles by Columnist MAK