Tabular cube information - discrepancy of LastSchemaUpdate date

Question

Hi,

in a recent time I have noticed that date in LastSchemaUpdate in SSMS is different that checking via PowerShell or XMLA script.

Many cubes show XMLA and Powershell scripts the same date of new cube deployment, but SSMS shows completely different date and it is not even close to two mentioned ways before.

The way I checked it until now, was Properties of cube, page Database and the second date from the bottom.

Can you show me where I can find the most correct date of cube's LastSchemaUpdate?

Best regards,

Answer 1

The LastSchemaUpdate property of an Analysis Services cube provides the date and time of the last schema change made to the cube. There might be discrepancies between SSMS and other methods due to caching or different ways of querying the property.

To get the most accurate LastSchemaUpdate date, you can use an XMLA script, as it directly queries the Analysis Services server. You can execute the following XMLA script in SQL Server Management Studio (SSMS):

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DBSCHEMA_CATALOGS</RequestType>
  <Restrictions />
  <Properties>
    <PropertyList>
      <Catalog>YOUR_CUBE_NAME</Catalog>
    </PropertyList>
  </Properties>
</Discover>

Replace YOUR_CUBE_NAME with the name of your cube. This script will return a list of catalogs (databases) with their properties, including the LastSchemaUpdate date.

Alternatively, you can use PowerShell with the SQLAS provider to retrieve the LastSchemaUpdate date:

$serverName = "your_server_name"
$cubeName = "your_cube_name"
$loadInfo = Add-Type -AssemblyName "Microsoft.AnalysisServices.AdomdClient"
$connectionString = "Data Source=$serverName;Provider=MSOLAP;"
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection($connectionString)
$conn.Open()

$cmd = $conn.CreateCommand()
$cmd.CommandText = @"
  <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DBSCHEMA_CATALOGS</RequestType>
    <Restrictions />
    <Properties>
      <PropertyList>
        <Catalog>$cubeName</Catalog>
      </PropertyList>
    </Properties>
  </Discover>
"@

$reader = $cmd.ExecuteReader()

while ($reader.Read()) {
    $lastSchemaUpdate = $reader["LAST_SCHEMA_UPDATE"]
}

$reader.Close()
$conn.Close()

Write-Output "Last Schema Update: $lastSchemaUpdate"

Replace your_server_name and your_cube_name with the appropriate values. This PowerShell script connects to the Analysis Services server, retrieves the catalog information, and outputs the LastSchemaUpdate date.

These methods should give you the most accurate and up-to-date LastSchemaUpdate information for your cube.