Editing an XMLA File Through Powershell using Get and Set-Content

Question

I have been tasked at work to automate the monthly maintenance. Basically I need to edit the date values in an xmla file to the next month.

This is the relevant section in the XMLA file:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Type>ProcessFull</Type>
        <Object>
            <DatabaseID>OLAPQA</DatabaseID>
            <CubeID>Model</CubeID>
            <MeasureGroupID>TRANSACTIONS</MeasureGroupID>
            <PartitionID>TRANSACTIONS 201410</PartitionID>
        </Object>
    </Process>
    <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Type>ProcessFull</Type>
        <Object>
            <DatabaseID>OLAPQA</DatabaseID>
            <CubeID>Model</CubeID>
            <MeasureGroupID>TRANSACTIONS</MeasureGroupID>
            <PartitionID>TRANSACTIONS 201411</PartitionID>
        </Object>
    </Process>
    <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Type>ProcessFull</Type>
        <Object>
            <DatabaseID>OLAPQA</DatabaseID>
            <CubeID>Model</CubeID>
            <MeasureGroupID>TRANSACTIONS</MeasureGroupID>
            <PartitionID>TRANSACTIONS 201412</PartitionID>
        </Object>
    </Process>

I need to write a windows powershell script with the following logic:

1. Get current date and verify if it’s the first day of the month
2. If yes, then calculate the previous day (in order to calculate previous 2 year/month values )
3. Search XMLA file to replace previous year/month combinations with new values

I have solved number 1 and 2 but I am having some trouble with number 3.

This is my code:

$con = Get-Content .\Process2Periods.xmla
$con | % { $_.Replace("((Get-Date).AddMonths(-2) | Get-Date -Format "yyyyMM")", ("((Get-Date).AddMonths(-1) | Get-Date -Format "yyyyMM")") } | Set-Content .\Process2Periods.xmla

How I got to this was running the following code which worked unlike the code above:

$con = Get-Content .\Process2Periods.xmla
$con | % { $_.Replace("201410", "201411") } | Set-Content .\Process2Periods.xmla

My question is how to make this dynamic instead of hard coding the values into the script. I will need this to change the three tags every month. The code works with the string values but how do I take the output of some code as the string value? Should I use variables?

Thanks in advance.

Answer 1

I am having trouble understanding the logic of what you are trying to do. I see that you are changing the month of every value up by one. So I am going into this assuming that you are going to up the month of every date by one month at the first of the month. I don't understand number 2 since I don't see you doing any manipulation of days.

$path = .\Process2Periods.xmla
$xmla = Get-Content -Path $path 
$now = (Get-Date -day 1)  #-day 1 is there to trigger next If. Remove for prod.
If($now.Day -eq 1){
    # It is the first of the month. 
    $xmla | ForEach-Object{
        # Output each line back to the variable while editting the found dates
        If($_ -match "<PartitionID>\D+(\d{6})</PartitionID>"){
            $_ -replace $matches[1], ('{0:yyyyMM}' -f [datetime]::ParseExact($matches[1],"yyyyMM", $null).AddMonths(1))
        } Else {
            $_
        }
    }
} | Set-Content  $path 

Take the contents of the file and process them through the foreach-Object loop assuming its the first of the month. Output the normal lines to output but if you find a line that had the PartitionID tags the we pull the 6 digit out. Next we write the manipulate string back to output with an updated date. The fun part is how the match and replace works which I will try to break down.

1. matches[1] contains the first capture group from the match. In our case 6 digits.
2. Take those digits and convert them to a [datetime] object using parse exact.
3. Increase the months by 1
4. Take the new date and convert it back to a "yyyyMM" string using the -f parameter.
5. Replace the matched digits with the new date.

I'm sure something is wrong and this was not your intention but like i mentioned before I dont really understand what you are doing. If you can make sense of what I should you I would think we can make this work with what you really want.

Answer 2

Try this:

$con  = Get-Content C:\Process2Periods.xmla
$lastMonth = (Get-Date).AddMonths(-1) | Get-Date -Format "yyyyMM"
$thisMonth = (Get-Date) | Get-Date -Format "yyyyMM"
$con | % { $_.Replace($lastMonth,$thisMonth) } | Set-Content .\Process2Periods.xmla