question

Cataster-7485 avatar image
0 Votes"
Cataster-7485 asked CarrinWu-MSFT commented

How to append increment to the name dynamically?

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.










windows-server-powershellsql-server-analysis-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Cataster-7485, just put the same thread in here, in case anyone who want to check this question. And we will check your question as soon as possible, so there is no need to post a duplicate question. Or you could edit your post if you would like to add more information. Thank you for your understanding.

0 Votes 0 ·

Hi @Cataster-7485, dose the answers from DarrenGosbell help you? If yes, could you please do "Accept Answer"? It will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered Cataster-7485 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DarrenGosbell-0123 Thanks Darren, I did confirm the Update() works, however, even though $matchingCubes | gm shows that Name and Update() are property/method, this code here doesnt appear to be integrating well in the solution. No renaming is occurring:

                     $matchingCubes | Sort-Object Name | ForEach-Object -Begin {$i = 1} -Process {
                         $_.Name = "$CUBE-Snapshot-$(($i++))"
                         $_.Update()
                     }
0 Votes 0 ·
DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered Cataster-7485 edited

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DarrenGosbell-0123
Ok I think I know what may be happening. Apparently, the Name gets changed but the ID remains the same as the old cube name. When i tried setting the ID to the same name as the updated cube name, i got this error:

 $cube_name.ID = "xy"

Exception setting "ID": "The 'ID' property cannot be changed when the 'NamedComponent' object is in a collection.

Btw here is where Im trying to integrate the logic but something is odd

 $matchingCubes = $AnalysisServer.Databases.Where({ $_.Name -like "*$CUBE-Snapshot*" })
 
 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 ") #<------ITS NOT EVEN EXECUTING THIS STATEMENT even though it comes before any renaming we're doing?? WHY?
     $matchingCubes | Sort-Object Name | ForEach-Object -Begin {$i = 1} -Process {
         Write-Host "`r`nRenaming $($_.Name) to $CUBE-Snapshot-$(($i++)) ...`r`n"
         $_.Name = "$CUBE-Snapshot-$(($i++))"
         $_.Update()
         Write-Host "`r`nNew Name: $($_.Name)`r`n"
     }

Something is strange. How come this statement "Existing $CUBE Snapshot(s) detected ...." is not executing unless remove the rename snippet

0 Votes 0 ·
DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered DarrenGosbell-0123 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DarrenGosbell-0123 wait then this becomes a problem for the context of this original post if the ID cannot be changed...

Because what is happening is Restore-ASDatabase -Server $Target_Server -RestoreFile $BFile -Name $CUBE -Security:$SecurityChoice -AllowOverwrite -ErrorAction Stop command is overriding the Snapshot Name and ID alike. If the ABF (backup) file cube name is "CubeABC" and ID is "CubeABC", and im giving a custom name here for the restore operation, then not only is it restoring the backupfile with a new name, but it is also altering the very essence, ID, of the Cube to match the name as well.

If renaming the ID is not an option, then how else can I approach this problem/feature requested by the users?


0 Votes 0 ·

This is not really a common pattern. One option is to build the snapshotting into your data warehouse and then just have a single cube with a snapshot dimension.

The other option is to do the whole process with a series of backup/restore operations and not do any re-naming.

first backup snapshot 1 and restore it as snapshot 2 (overwriting the existing snapshot 2)
then back up the main database and restore it as snapshot 1 (overwriting the existing snapshot 1)
then re-process the main database to bring in the new data

0 Votes 0 ·
Cataster-7485 avatar image
0 Votes"
Cataster-7485 answered Cataster-7485 published

@DarrenGosbell-0123
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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered Cataster-7485 commented

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

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DarrenGosbell-0123
But that's the problem: the Name I'm passing in restore operation is also seemingly altering the ID as well...
I confirmed this, both the Name and ID match after the restore/snapshot operation is completed

0 Votes 0 ·

But that's the problem: the Name I'm passing in restore operation is also seemingly altering the ID as well...
I confirmed this, both the Name and ID match after the restore/snapshot operation is completed

Yes, this is how the restore works. You can think of it as creating a new blank database and then populating it with data from the abf file. If it did not do this there would be no way of restoring a copy of an existing database as the ID would always clash.

That is why I'm suggesting using a different naming convention (like having a date based suffix) for the backup/restore operations and then doing the rename to your generic naming convention (eg. Cube-1, Cube-2) so you don't clash on the ID/name during the renames.

0 Votes 0 ·

@DarrenGosbell-0123
But the date will always change. I have it this way currently.
It's working as intended but users figure itd be more convenient if they didnt have to reconnect to the new snapshot which has a different date the next week, then the following week, etc...
Hence why renaming the ID as well is necessary

0 Votes 0 ·
Show more comments
DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered DarrenGosbell-0123 commented

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

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



image.png (3.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DarrenGosbell-0123
yep i completely follow you here, thanks for taking the time to expand on it. Honestly you have been the most helpful throughout this process, thank you so much!
So just to confirm

rename Cube-Snapshot-20210720 to Cube-Snapshot-1

Do I need to alter anything in the logic here to accomplish this:

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

I don't believe you would use a loop for step 4. You could use a loop like that to do step 2.

But in step 4 it would just be a restore with the date suffix then rename to snapshot 1 (assuming 1 is always the most recent snapshot)

0 Votes 0 ·