Create a gist now

Instantly share code, notes, and snippets.

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