How to append increment to the name dynamically?

Question

I am creating DB/SSAS Cube snapshots for users weekly. For example, given "CubeABC", the user will find a snapshot of its previous iteration available to select as well:

CubeABC
CubeABC-Snapshot-06-28-2021-0400AM

I have recently enabled a feature that allows them to retain more than 1 snapshot, for example, if they specify the retention rate variable as 2, then 2 snapshots are retained at a time. If they change the retention to 1, then the OLDEST snapshot is deleted, and so forth on a weekly basis.

e.g. retention_rate = 2

CubeABC
CubeABC-Snapshot-06-28-2021-0400AM
CubeABC-Snapshot-07-05-2021-0400AM

e.g. retention_rate = 1

CubeABC
CubeABC-Snapshot-07-05-2021-0400AM

They connect to the cube in excel. However, there is a slight inconvenience that emerged: Since the Snapshots have a timestamp, and the timestamp continuously changes weekly, they have to edit the connection everytime to reflect the existing snapshot on the server, since the current one they were connected to say 2 or 3 weeks ago, e.g. CubeABC-Snapshot-06-28-2021-0400AM would be gone by now.

So I am considering the following idea: append an "increment" instead of a timestamp to the snapshot:

CubeABC-Snapshot-1
CubeABC-Snapshot-2
etc..

where snapshot 1 in this case would be the oldest snapshot and snapshot 2 would be the most recent, assuming a retention_rate = 2 that is.

How would I implement a "dynamic increment" (e.g. 1, 2), which replaces $dateStamp here: $CUBE = "$CUBE-Snapshot-$dateStamp".

Here is the existing code I have:

#Check if there is existing snapshots first, and if there is, delete any snapshots > retention_rate

$matchingCubes = $AnalysisServer.Databases.Where({ $_.Name -like "*$CUBE-Snapshot*" })
if ($matchingCubes.Count -eq 0) { 
    Write-Host "`r`n No $CUBE Snapshot(s) found on $Target_Server server." -foregroundcolor yellow -backgroundcolor black
}
elseif($matchingCubes.Count -gt 0 -and $matchingCubes.Count -ge $retention_rate) {
    ($matchingCubes | Sort-Object Name -Descending | Select-Object -Skip ($retention_rate - 1)).Drop()

    Write-Host "`r`nThe following $CUBE Snapshot(s) were DELETED successfully from $Target_Server server!`r`n" -foregroundcolor yellow -backgroundcolor black
    Write-Host $($matchingCubes  -split "`n ")
}
else {
    Write-Host "`r`nNo $CUBE Snapshot(s) found older than $retention_rate retention interval(s) to Delete on $Target_Server server!" -foregroundcolor yellow -backgroundcolor black
    Write-Host "`r`nExisting $CUBE Snapshot(s) detected on $Target_Server server:`r`n"
    Write-Host $($matchingCubes  -split "`n ")
}

#Create New Snapshot
$CUBE = "$CUBE-Snapshot-$dateStamp"
Restore-ASDatabase -Server $Target_Server -RestoreFile $BFile -Name $CUBE -Security:$SecurityChoice -AllowOverwrite -ErrorAction Stop

Here is what Ive tried:

# Rename remaining snapshots
if ($retention_rate -ge 2){
    # get remaining Cube snapshots left after cleanup
    # $matchingCubes = **refreshed list of remaining cube snapshots (if needed)**.  Maybe Drop() does this already?

    # rename in order of age, oldest being renamed to $CUBE-Snapshot-1
    # sort ascending so that the highest number (oldest) is processed first    
    $matchingCubes | Sort-Object Name | ForEach-Object -Begin {$i = 1} -Process {
        $_ | Rename-Item -NewName ("$CUBE-Snapshot-$(($i++))")
    }
}

#Create New Snapshot
# Newest Cube should have largest digit.  We can use $retention_rate
$CUBE = "$CUBE-Snapshot-$retention_rate"
Restore-ASDatabase -Server $Target_Server -RestoreFile $BFile -Name $CUBE -Security:$SecurityChoice -AllowOverwrite -ErrorAction Stop

Error:

Rename-Item : Illegal characters in path. Rename-Item : Cannot rename because item at '<DatabaseID>CubeABC-Snapshot-2</DatabaseID>' does not exist.

Answer 1

Yes, so I'm thinking your databases would look something like the following (since both the name and ID need to be unique)

So on July 20 you would do something like the following:

1. drop Cube-Snapshot-2.
2. rename Cube-Snapshot-1 to Cube-Snapshot-2
3. backup and restore Cube to Cube-Snapshot-20210720
4. rename Cube-Snapshot-20210720 to Cube-Snapshot-1
5. reprocess the main database