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

I think I answered this in your other post about renaming a database using powershell https://learn.microsoft.com/answers/questions/481605/how-to-rename-ssastabular-cube-in-powershell.html

Answer 2

I just double checked and setting the name then calling update() works on my machine.

Can you output some log statements to make sure the loop is doing what you think it should be?

$matchingCubes | Sort-Object Name | ForEach-Object -Begin {$i = 1} -Process {
                         write-output "renaming $($_.name) to $CUBE-Snapshot-$(($i++))"
                         $_.Name = "$CUBE-Snapshot-$(($i++))"
                         $_.Update()
                     }

Did you make sure to refresh the database list after renaming to make sure you were not looking at cached information?

Answer 3

That is correct. You can only change the name property, you cannot change the ID property of a live database. But as long as you use methods like FindByName() this should not be a problem.

The ID property gets set to the same value as the name when creating or restoring a database, but it cannot be altered at any other times. So if your databases are not too big you could back-up, delete, then restore with the new name to keep these in synch.

Answer 4

@Darren Gosbell
I could not post this as a reply...sooo annoying! This Docs site is broken...
Anyways, to your suggestions:
we actually had the 1st option implemented last year, however the cube size grew so large that performance became super bad and users were facing lags. We urged the developers to remove the historical dimension from the cube and only retain contemporary data to optimize the cube as much as possible. It went from 80GB -> 22GB now which is MUCH better than last year. However tis did bring up the snapshotting demand of course...

The 2nd option is interesting. we already re-process the main database today anyways.
One thing to note is i still end up back at the original problem with this backup/restore approach because it depends on retention_rate. I actually already backup the main database and then restore it. Thats how I implemented the snapshot feature. For 1 snapshot only, this works great because I can set the snapshot name to somethign generic that will always be overwritten and the users dont have to reconnect because the naming convention is constant.

However, the problem arises with retention_rate = 2 or 3 or 4 etc...

• First I have a separate script I use to create a weekly backup, which would require me to implement retention logic there.
• Additionally, we have to worry about increased space for those extra backups on our shared drive. We are talking with a current retention_rate of 4, and performing this weekly on 2 cubes for now (more in the future potentially), 8 cube backups taking over 100GB in space! We have 25+ cubes that get backed up weekly as well, and with that, our backup capacity will run out..

There's gotta be another way...maybe using TMSL to rename both Name /ID?

Answer 5

There's gotta be another way...maybe using TMSL to rename both Name /ID?

No, the ID property is immutable once the object has been created.

But this is also only an issue if your ID and Name clash. So if you have a database with ID=Cube1 and Name=Cube2 then you cannot restore another database as either Cube1 or Cube2 as it will clash with the properties of the first database.

But as long as you restore your databases with a different naming convention. eg Cube-yyyymmdd you could then rename them to your generic names without a problem