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 Apr 15, 2009

Script dimensions using Windows PowerShell and AMO

By Muthusamy Anantha Kumar aka The MAK

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

This installment is going to illustrate how to script dimension 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")

loading the Analysis Service Manage object DLL and connecting to the Analysis Service server instance
Figure 1.0

Now let us list all of 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

list all of the databases available on the Analysis server
Figure 1.1

Now let's 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"]

connect to the database “Adventure Works 2008 -2” on the Analysis Service instance
Figure 1.2

Let's assign the dimension object Employee to the EmployeeDim variable as shown below.

$EmployeeDim = $advdb.dimensions.FindByName("Employee")

Prepare and set up 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

Now let's use the scriptCreate method to generate the script for creating the dimension Employee as shown below.

$MSASObject=[Microsoft.AnalysisServices.MajorObject[]] @($EmployeeDim)
$ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)

Store the generated XML to a file using the PowerShell cmdlet Out-File, as shown below.

$stringbuilder.ToString() |out-file -filepath c:\AdventDb_Employee_Dim.xmla

You could open and see the content of the file c:\AdventDb_Employee_Dim.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>
    <Dimension 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>Dim Employee</ID>
      <Name>Employee</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>
...
..
..
..
..
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <Source xsi:type="ProactiveCachingInheritedBinding" />
      </ProactiveCaching>
    </Dimension>
  </ObjectDefinition>
</Create>

the content of the file c:\AdventDb_Employee_Dim.xmla
Figure 1.3

Let's create the PowerShell script C:\AMOScripts\Generate_Dim_Script.ps1 that would generate the script for any dimension in any database. Copy and paste the below given code to C:\AMOScripts\Generate_Dim_Script.ps1. [Refer Figure 1.4]

param
(
[string] $servername,
[string] $databasename,
[string] $dimensionname,
[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"]

$EmployeeDim = $advdb.dimensions.FindByName("$dimensionname")



$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[]] @($EmployeeDim)

$ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)
$filename=$location+$dimensionname+"_"+$mydate+"_dim.xmla"
$stringbuilder.ToString() |out-file -filepath $filename

create the PowerShell script C:\AMOScripts\Generate_Dim_Script.ps1 that would generate the script for any dimension in any database
Figure 1.4

Now 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”, dimension name “Geography” and with the location name “C:\Data\” as shown below. [Refer Figure 1.5]

.\Generate_Dim_Script.ps1 "PowerServer3\SQL2008" "Adventure Works 2008 -2" "Geography" "C:\Data\"

Execute the C:\AMOScripts\Generate_Dim_Script.ps1
Figure 1.5

When the script is executed, you see that the XMLA format file for the dimension Geography is generated under C:\Data folder as shown below. [Refer Fig 1.6]

the XMLA format file for the dimension Geography is generated
Figure 1.6

You could open and see the content of the file

c:\data\ Geography__04_08_2009_20_26_02_dim.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>
    <Dimension 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>Dim Geography</ID>
      <Name>Geography</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>
...
...
...
...
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <Source xsi:type="ProactiveCachingInheritedBinding" />
      </ProactiveCaching>
    </Dimension>
  </ObjectDefinition>
</Create>

content of the file c:\data\ Geography__04_08_2009_20_26_02_dim.xmla
Figure 1.7

Notice also that the file name is generated on the fly with the dimension name and time stamp.

Conclusion

This article has illustrated how to generate a script for given dimension of the Analysis Service database.

» 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