An example of how to use Azure Data Factory to retrieve usage data from the Azure Management API and store the results in Azure Data Lake.
A Service Principal with Enrollment Reader permissions assigned at the Enrollment level is required before deploying this example.
Take note of the service principal's tenant ID, application ID and secret as they're required for the next step. The Enrollment ID variable can be obtained from from Cost Management and Billing in the Azure Portal or the EA Portal
| Parameter | Default Value | Notes |
|---|---|---|
| Aad Resource Id | [parameters('managementApiUri')] | Same value as Management API URI |
| Blob Container Name | [concat('ccm', uniqueString(resourceGroup().id, resourceGroup().location))] | |
| Data Factory Name | [concat('datafactory', uniqueString(resourceGroup().id, resourceGroup().location))] | |
| Enrollment Id | Required | Obtained from Cost Management and Billing in the Azure Portal or the EA Portal |
| Keyvault Name | [concat('keyvault', uniqueString(resourceGroup().id, resourceGroup().location))] | |
| Management Api Uri | https://management.azure.com | Modify to support different Azure clouds |
| Service Principal Id | Required | The Application ID of the service principal (not the object id) |
| Service Principal Secret | Required | The password for the service principal obtained during creation |
| Service Principal Tenant Id | Required | The ID of Azure AD Tenant containing the service principal |
| Storage Account Name | [concat('storage', uniqueString(resourceGroup().id, resourceGroup().location))] |
#!/bin/bash
location='westus'
subscription='workload'
resourceGroupName="ccm-lab"
az account set --subscription $subscription
az group create --name $resourceGroupName --location $location
az deployment group create \
--mode Incremental \
--resource-group $resourceGroupName \
--template-file azuredeploy.json \
--parameters @azuredeploy.parameters.jsonInitializing the dataset is a two-step process. Initialize the dataset by executing the "GetUsageRange" pipeline and providing the desired date range.
- UsageStartDate - Set to the first date to download - for example the beginning of current FY or quarter.
- UsageEndDate - Set to the current date.
Once the dataset is initialized create a daily schedule for the GetUsageCurrent pipeline and publish the changes.
To import the data into Power BI first create a function to decompress each file in Power BI
fnUnpackData
(zipData) =>
let
Source = Binary.Decompress(zipData, Compression.GZip),
#"Imported" = Csv.Document(Source,[Delimiter=",", Columns=50, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported", [PromoteAllScalars=true])
in
#"Promoted Headers"
Then create data tables with the following definitions and correct data lake URI
Amortized Costs
let
Source = AzureStorage.DataLake(https://DataLakeUri),
Filter = Table.SelectRows(Source, each [Name] = "amortizedcost.csv.gz"),
Unpack = Table.AddColumn(Filter, "Custom", each fnUnpackData([Content])),
#"Removed Columns" = Table.RemoveColumns(Unpack,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Expand CSV" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"BillingAccountId", "BillingAccountName", "BillingPeriodStartDate", "BillingPeriodEndDate", "BillingProfileId", "BillingProfileName", "AccountOwnerId", "AccountName", "SubscriptionId", "SubscriptionName", "Date", "Product", "PartNumber", "MeterId", "ServiceFamily", "MeterCategory", "MeterSubCategory", "MeterRegion", "MeterName", "Quantity", "EffectivePrice", "Cost", "UnitPrice", "BillingCurrency", "ResourceLocation", "AvailabilityZone", "ConsumedService", "ResourceId", "ResourceName", "ServiceInfo1", "ServiceInfo2", "AdditionalInfo", "Tags", "InvoiceSectionId", "InvoiceSection", "CostCenter", "UnitOfMeasure", "ResourceGroup", "ReservationId", "ReservationName", "ProductOrderId", "ProductOrderName", "OfferId", "IsAzureCreditEligible", "Term", "PublisherName", "PlanName", "ChargeType", "Frequency", "PublisherType"}, {"BillingAccountId", "BillingAccountName", "BillingPeriodStartDate", "BillingPeriodEndDate", "BillingProfileId", "BillingProfileName", "AccountOwnerId", "AccountName", "SubscriptionId", "SubscriptionName", "Date", "Product", "PartNumber", "MeterId", "ServiceFamily", "MeterCategory", "MeterSubCategory", "MeterRegion", "MeterName", "Quantity", "EffectivePrice", "Cost", "UnitPrice", "BillingCurrency", "ResourceLocation", "AvailabilityZone", "ConsumedService", "ResourceId", "ResourceName", "ServiceInfo1", "ServiceInfo2", "AdditionalInfo", "Tags", "InvoiceSectionId", "InvoiceSection", "CostCenter", "UnitOfMeasure", "ResourceGroup", "ReservationId", "ReservationName", "ProductOrderId", "ProductOrderName", "OfferId", "IsAzureCreditEligible", "Term", "PublisherName", "PlanName", "ChargeType", "Frequency", "PublisherType"}),
#"Filtered Rows" = Table.SelectRows(#"Expand CSV", each ([ReservationName] <> "")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"BillingAccountId", Int64.Type}, {"BillingAccountName", type text}, {"BillingPeriodStartDate", type date}, {"BillingPeriodEndDate", type date}, {"BillingProfileId", Int64.Type}, {"BillingProfileName", type text}, {"AccountOwnerId", type text}, {"AccountName", type text}, {"SubscriptionId", type text}, {"SubscriptionName", type text}, {"Date", type date}, {"Product", type text}, {"PartNumber", type text}, {"MeterId", type text}, {"ServiceFamily", type text}, {"MeterCategory", type text}, {"MeterSubCategory", type text}, {"MeterRegion", type text}, {"MeterName", type text}, {"Quantity", type number}, {"EffectivePrice", type number}, {"Cost", type number}, {"UnitPrice", type number}, {"BillingCurrency", type text}, {"ResourceLocation", type text}, {"AvailabilityZone", type any}, {"ConsumedService", type text}, {"ResourceId", type text}, {"ResourceName", type text}, {"ServiceInfo1", type any}, {"ServiceInfo2", type text}, {"AdditionalInfo", type text}, {"Tags", type text}, {"InvoiceSectionId", type any}, {"InvoiceSection", type text}, {"CostCenter", type text}, {"UnitOfMeasure", type text}, {"ResourceGroup", type text}, {"ReservationId", type text}, {"ReservationName", type text}, {"ProductOrderId", type text}, {"ProductOrderName", type text}, {"OfferId", type text}, {"IsAzureCreditEligible", type logical}, {"Term", Int64.Type}, {"PublisherName", type any}, {"PlanName", type any}, {"ChargeType", type text}, {"Frequency", type text}, {"PublisherType", type text}})
in
#"Changed Type"
Actual Costs
let
Source = AzureStorage.DataLake(https://DataLakeUri),
Filter = Table.SelectRows(Source, each [Name] = "actualcost.csv.gz"),
Unpack = Table.AddColumn(Filter, "Custom", each fnUnpackData([Content])),
#"Removed Columns" = Table.RemoveColumns(Unpack,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Expand CSV" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"BillingAccountId", "BillingAccountName", "BillingPeriodStartDate", "BillingPeriodEndDate", "BillingProfileId", "BillingProfileName", "AccountOwnerId", "AccountName", "SubscriptionId", "SubscriptionName", "Date", "Product", "PartNumber", "MeterId", "ServiceFamily", "MeterCategory", "MeterSubCategory", "MeterRegion", "MeterName", "Quantity", "EffectivePrice", "Cost", "UnitPrice", "BillingCurrency", "ResourceLocation", "AvailabilityZone", "ConsumedService", "ResourceId", "ResourceName", "ServiceInfo1", "ServiceInfo2", "AdditionalInfo", "Tags", "InvoiceSectionId", "InvoiceSection", "CostCenter", "UnitOfMeasure", "ResourceGroup", "ReservationId", "ReservationName", "ProductOrderId", "ProductOrderName", "OfferId", "IsAzureCreditEligible", "Term", "PublisherName", "PlanName", "ChargeType", "Frequency", "PublisherType"}, {"BillingAccountId", "BillingAccountName", "BillingPeriodStartDate", "BillingPeriodEndDate", "BillingProfileId", "BillingProfileName", "AccountOwnerId", "AccountName", "SubscriptionId", "SubscriptionName", "Date", "Product", "PartNumber", "MeterId", "ServiceFamily", "MeterCategory", "MeterSubCategory", "MeterRegion", "MeterName", "Quantity", "EffectivePrice", "Cost", "UnitPrice", "BillingCurrency", "ResourceLocation", "AvailabilityZone", "ConsumedService", "ResourceId", "ResourceName", "ServiceInfo1", "ServiceInfo2", "AdditionalInfo", "Tags", "InvoiceSectionId", "InvoiceSection", "CostCenter", "UnitOfMeasure", "ResourceGroup", "ReservationId", "ReservationName", "ProductOrderId", "ProductOrderName", "OfferId", "IsAzureCreditEligible", "Term", "PublisherName", "PlanName", "ChargeType", "Frequency", "PublisherType"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expand CSV",{{"BillingAccountId", Int64.Type}, {"BillingAccountName", type text}, {"BillingPeriodStartDate", type date}, {"BillingPeriodEndDate", type date}, {"BillingProfileId", Int64.Type}, {"BillingProfileName", type text}, {"AccountOwnerId", type text}, {"AccountName", type text}, {"SubscriptionId", type text}, {"SubscriptionName", type text}, {"Date", type date}, {"Product", type text}, {"PartNumber", type text}, {"MeterId", type text}, {"ServiceFamily", type text}, {"MeterCategory", type text}, {"MeterSubCategory", type text}, {"MeterRegion", type text}, {"MeterName", type text}, {"Quantity", type number}, {"EffectivePrice", type number}, {"Cost", type number}, {"UnitPrice", type number}, {"BillingCurrency", type text}, {"ResourceLocation", type text}, {"AvailabilityZone", type any}, {"ConsumedService", type text}, {"ResourceId", type text}, {"ResourceName", type text}, {"ServiceInfo1", type any}, {"ServiceInfo2", type text}, {"AdditionalInfo", type text}, {"Tags", type text}, {"InvoiceSectionId", type any}, {"InvoiceSection", type text}, {"CostCenter", type text}, {"UnitOfMeasure", type text}, {"ResourceGroup", type text}, {"ReservationId", type text}, {"ReservationName", type text}, {"ProductOrderId", type text}, {"ProductOrderName", type text}, {"OfferId", type text}, {"IsAzureCreditEligible", type logical}, {"Term", Int64.Type}, {"PublisherName", type any}, {"PlanName", type any}, {"ChargeType", type text}, {"Frequency", type text}, {"PublisherType", type text}})
in
#"Changed Type"
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent