Skip to content

Instantly share code, notes, and snippets.

@dgosbell

dgosbell/update-SsasLastProcessed.ps1

Created May 13, 2015 11:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dgosbell/8f4e2a6aa022a6fdd288 to your computer and use it in GitHub Desktop.
Save dgosbell/8f4e2a6aa022a6fdd288 to your computer and use it in GitHub Desktop.
Download ZIP
This script will update the specified target table the last processed date and time of every other table in the SSAS Tabular database. For more details see http://darren.gosbell.com/archive/2015/05/13/ssas-tabularndashper-table-lastprocessed-dates.aspx
Raw
update-SsasLastProcessed.ps1
<#
.SYNOPSIS
Updates a LastProcessed table in an SSAS Tabular database
.DESCRIPTION
This script uses AMO to loop through each table in the specified
database and pushed the table name and the LastProcessed Date/time
value into the specified table. Requires
.NOTES
File Name : update-ssaslastupdated.ps1
Author : Darren Gosbell
Requires : PowerShell V3
.LINK
http://darren.gosbell.com
.PARAMETER serverName
The target server which holds the database to be processed
.PARAMETER databaseName
The target database which will be scanned for the LastProcessed information
.PARAMETER tableName
The target table into which the last updated information will be pushed
.EXAMPLE
.\update-SsasLastUpdated.ps1 "localhost" "AdvWrksLastProcessed" "LastProcessed"
#>
param (
[Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$false)]
[string] $serverName,
[Parameter(Position=1, Mandatory=$true, ValueFromPipeline=$false)]
[string] $databaseName ,
[Parameter(Position=2, Mandatory=$true, ValueFromPipeline=$false)]
[string] $tableName
)
# the following
#$serverName = "localhost"
#$databaseName = "AdvWrksLastProcessed"
#$tableName = "LastProcessed"
# If you have don't have the SSAS 2012 client libraries install you can fall back to this
#[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > out-null
#[Reflection.Assembly]::LoadWithPartialName("System.IO")
# load .Net libraries
add-type -AssemblyName "Microsoft.AnalysisServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -assemblyName "System.IO"
# connect to server/database/table
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($serverName)
$db = $server.Databases.FindByName($databaseName)
if ($db -eq $null) {
write-error "Database '$databaseName' not found."
exit
}
$dim = $db.Dimensions.FindByName($tableName)
if ($dim -eq $null) {
write-error "Table '$tableName' not found."
exit
}
# create data rows
$lastProcessed = ""
foreach ($dim in $db.Dimensions)
{
if ($dim.Name -ne $tableName)
{
$lastProcessed += @"
<row>
<TableName>$($dim.Name)</TableName>
<LastProcessed>$($dim.LastProcessed.ToString("yyyy-MM-ddTHH:mm:ss"))</LastProcessed>
</row>
"@
}
}
# push data to the server
$xmla = @"
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100="http://schemas.microsoft.com/analysisservices/2008/engine/100" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<Type>ProcessFull</Type>
<Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<DatabaseID>AdvWrksLastProcessed</DatabaseID>
<DimensionID>$($dim.ID)</DimensionID>
</Object>
<Bindings xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100="http://schemas.microsoft.com/analysisservices/2008/engine/100" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Binding xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DatabaseID>AdvWrksLastProcessed</DatabaseID>
<DimensionID>Query_a7d04127-d040-4118-acce-29ea3abe8ac8</DimensionID>
<AttributeID>TableName</AttributeID>
<KeyColumns>
<KeyColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>TableName</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>TableName</ColumnID>
</Source>
</NameColumn>
</Binding>
<Binding xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DatabaseID>AdvWrksLastProcessed</DatabaseID>
<DimensionID>Query_a7d04127-d040-4118-acce-29ea3abe8ac8</DimensionID>
<AttributeID>LastProcessed</AttributeID>
<KeyColumns>
<KeyColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>LastProcessed</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>LastProcessed</ColumnID>
</Source>
</NameColumn>
</Binding>
</Bindings>
<DataSource xsi:type="PushedDataSource">
<root Parameter="InputRowset"/>
<EndOfData Parameter="EndOfInputRowset"/>
</DataSource>
</Process>
</Command>
<Properties>
</Properties>
<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
<Parameter>
<Name>EndOfInputRowset</Name>
<Value xsi:type="xsd:boolean">true</Value>
</Parameter>
<Parameter>
<Name>InputRowset</Name>
<Value xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="TableName" name="TableName" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="LastProcessed" name="LastProcessed" type="xsd:dateTime" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
$LastProcessed
</Value>
</Parameter>
</Parameters>
</Execute>
</Body>
</Envelope>
"@
$xmlaStrm = new-object System.IO.StringReader $xmla
$res = $server.SendXmlaRequest( [Microsoft.AnalysisServices.XmlaRequestType]::Undefined, $xmlaStrm)
$res.MoveToContent() | out-null
$s = $res.ReadInnerXml()
$res.close()
## Check for errors
$Namespace = @{soap="http://schemas.xmlsoap.org/soap/envelope/"}
$n = select-xml -xml ([xml]$s) -XPath "//soap:Error" -Namespace $Namespace
if ($n.Length -gt 0)
{
$errs=""
$n | % { $errs += $_.node.Description + "`n"}
write-error $errs
}
else
{
"Update Suceeded"
}
$server.Disconnect()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment