Import Azure Rate Card to vROPs
Import Azure Rate Card to vROPs
I’ve been working with a customer over the last day or so to import their Azure cost information (or Azure rate card) into vROPs so that they can run a what-if analysis to plan a migration to the public cloud.
Within vROPs we have the option to upload our public cloud rate cards into the service, so the the data that is provided from the service reflects the actual costs that your procurement teams have negotiated with the public cloud hyperscalers.
Now the format that vROPs that wants this rate card in is very particular, any errors in the format will result in errors when trying to upload the rate card. The formate that vROPs is expecting the rate card can be downloaded when editing the cloud provider information. An excerpt is below.
Unfortunately the format that we can get the data from Azure does not match the format that vROPs wants it in, so we’re going to need to transform it.
Transforming the Data – Azure VM Sizes
As you can see from the above excerpt part of the information that vROPs wants can be pulled using PowerShell and the AzureRmVMSize cmdlet, as luck would have it I blogged about this the other day. With the data captured, we can create a new spreadsheet and create a connection to the AzureVmSizes.csv that is generated by the PowerShell. With the connection made we can drop into PowerQuery to transform the data so that we can use it. The data initially looks like;
Working logically through what vROPs is expecting, we create new column to capture the RAM and disk footprint in GB and cleanup the instance names, the steps required are a little long winded, I’ve included the PowerQuery advanced editor syntax below
let
Source = Csv.Document(File.Contents("C:\Users\conyardsi\OneDrive - VMware, Inc\AzureVmSizes.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LocationDisplayName", type text}, {"Location", type text}, {"Name", type text}, {"NumberOfCores", Int64.Type}, {"MemoryInMB", Int64.Type}, {"MaxDataDiskCount", Int64.Type}, {"OSDiskSizeInMB", Int64.Type}, {"ResourceDiskSizeInMB", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each not Text.Contains([Name.2], "_Promo")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name.1] = "Standard")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","_"," ",Replacer.ReplaceText,{"Name.2"}),
#"Uppercased Text" = Table.TransformColumns(#"Replaced Value",{{"Name.2", Text.Upper, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Uppercased Text",{{"NumberOfCores", "vCPUs"}, {"Name.2", "Instance Name"}}),
#"Inserted Division" = Table.AddColumn(#"Renamed Columns", "Division", each [MemoryInMB] / 1024, type number),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Division",{"LocationDisplayName", "Location", "Name.1", "Instance Name", "vCPUs", "MemoryInMB", "Division", "MaxDataDiskCount", "OSDiskSizeInMB", "ResourceDiskSizeInMB"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Division", "Ram GB"}}),
#"Inserted Division1" = Table.AddColumn(#"Renamed Columns1", "Division", each [ResourceDiskSizeInMB] / 1024, type number),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Division1",{{"Division", "Total Storage GB"}})
in
#"Renamed Columns2"
Which leaves us with an instance name we can use as a primary key to merge in with the Azure costing data.
Transforming the Data – Azure VM Costs
The next part of the information that vROPs wants populated in the templates is the costs for each of the Azure virtual machine instances. The Azure costing data set includes a list of everything that I could purchase across all regions. This defiantly contains the information I need but it does need me to trim it down.
As before I’ll work through the data as presented to transform it to the output that I need. This is a little more long winded for this data set, as there is quite a lot of information that I don’t need. I’ve included the query set below.
Source = Excel.Workbook(File.Contents("C:\Users\conyardsi\OneDrive - VMware, Inc\Azure Rates May 2020.xlsx"), null, true),
#"Azure Rates May 2020_Sheet" = Source{[Item="Azure Rates May 2020",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Azure Rates May 2020_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Service", type text}, {"Unit of Measure", type any}, {"Included Quantity ", Int64.Type}, {"Part Number", type text}, {"Unit Price", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Service], "Virtual Machines")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Service", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Service.1", "Service.2", "Service.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Service.1", type text}, {"Service.2", type text}, {"Service.3", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Service.2", "Instance Name"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each not Text.Contains([Instance Name], "Priority")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Service.3], "UK")),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows2",{{"Service.3", Text.Trim, type text}, {"Instance Name", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","Â","",Replacer.ReplaceText,{"Unit Price"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Unit Price", Currency.Type}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type2","Hours","",Replacer.ReplaceText,{"Unit of Measure"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Hour","",Replacer.ReplaceText,{"Unit of Measure"}),
#"Trimmed Text1" = Table.TransformColumns(#"Replaced Value2",{{"Unit of Measure", Text.Trim, type text}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Trimmed Text1",{{"Unit of Measure", type number}}),
#"Inserted Division" = Table.AddColumn(#"Changed Type3", "Division", each [Unit Price] / [Unit of Measure], Currency.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Division",{{"Division", "Instance Price / hour"}}),
#"Uppercased Text" = Table.TransformColumns(#"Renamed Columns1",{{"Instance Name", Text.Upper, type text}}),
#"Filtered Rows3" = Table.SelectRows(#"Uppercased Text", each not Text.Contains([Instance Name], "/"))
in
#"Filtered Rows3"
After transforming the data I have my subset of information that I can use to populate the vROPs template.
Transforming the Data – Merge
To bring the Virtual machine costs and the sizing information together merge the queries, using ‘Instance Name’ as the key to join the data.
Expand out the information from the VM Sizes query that we’re interested in, vCPU, Ram GB and Total Storage GB (I also like to include the primary key value for simple verification)
There are still a few rows where I don’t have published sizes for virtual machines, the ‘AzureVmSizes.Instance Name’ column will list these misses as ‘null’. with the data sets I worked with there where 5 machines this applied to, each of which was identified as a fringe use case and could be discounted.
Transforming the Data – Azure Storage
To pull through the storage costing data I make a second connection to the downloaded Azure rates, but this time I’m building out a query to filter on the storage values in the regions I’m interested in.
let
Source = Excel.Workbook(File.Contents("C:\Users\conyardsi\OneDrive - VMware, Inc\Azure Rates May 2020.xlsx"), null, true),
#"Azure Rates May 2020_Sheet" = Source{[Item="Azure Rates May 2020",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Azure Rates May 2020_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Service", type text}, {"Unit of Measure", type any}, {"Included Quantity ", Int64.Type}, {"Part Number", type text}, {"Unit Price", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Service], "Managed")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Service], "Stack")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Service], "Standard") or Text.Contains([Service], "Premium")),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each not Text.Contains([Service], "LRS")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each not Text.Contains([Service], "Preview")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows4", "Service", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Service.1", "Service.2", "Service.3", "Service.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Service.1", type text}, {"Service.2", type text}, {"Service.3", type text}, {"Service.4", type text}}),
#"Filtered Rows5" = Table.SelectRows(#"Changed Type1", each Text.Contains([Service.4], "UK")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows5","Â","",Replacer.ReplaceText,{"Unit Price"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Unit Price", Text.Trim, type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Trimmed Text",{{"Unit Price", Currency.Type}}),
#"Filtered Rows6" = Table.SelectRows(#"Changed Type2", each ([Service.4] = " UK South" or [Service.4] = " UK West")),
#"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows6","1 /Month","DISK_PER_MONTH",Replacer.ReplaceText,{"Unit of Measure"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," Managed Disks","",Replacer.ReplaceText,{"Service.1"})
in
#"Replaced Value2"
Bingo
Transforming the Data – Template import
As far as I can tell vROPs is looking for the data populating the template to be in the raw, rather than populated by queries or formulas. Which is annoying but something we can work with. What this effectively means is working with the exported query data and copying this into the template format, before passing it into the vROPs template. To do this I add a ‘Storage Working Sheet’ and ‘Working Sheet’ containing the template headers to the workbook containing query generated tables.
The simply copy the data from the query generated sheets into the template working sheets, a boring job – but a damn sight quicker than looking up this data manually an inputting line by line. Once complete, just copy the template formatted data into a blank vROPs rate card workbook and upload into vROPs and validate.
typical errors I’ve seen during validation is when empty formatted cells are present in the workbook, as per the below output.
Validation Error Invalid value entered in Instance_2.0: B5726. Suggested value format : 12345 Invalid value entered in Instance_2.0: B5727. Suggested value format : 12345 Invalid value entered in Instance_2.0: B5728. Suggested value format : 12345 Invalid value entered in Instance_2.0: B5729. Suggested value format : 12345 Invalid value entered in Instance_2.0: B5720. Suggested value format : 12345 Invalid value entered in Instance_2.0: B5721. Suggested value format : 12345 Invalid value entered in Instance_2.0: B5736. Suggested value format : 12345....
To rectify this simply reopen the workbook and clear the contents and formatting of any cells outside your data set.
Now when using the vROPs Migration Planning to Public Cloud what-if scenario, the costs will reflect your organisations negotiated rates.
Thanks
Simon