microsoft
[Power BI] tags separation
I am unable to seggregate tags into columns and link them in the powerbi model to pull cost per tag
@saleemmy I recently created an article in our community about this topic. Check it out at the link: https://techcommunity.microsoft.com/t5/azure-infragurus/passo-a-passo-para-criar-um-filtro-de-tag-no-finops-hub/ba-p/4249215
@arthursilvany thanks for the article, but i adapted another methodology. Transform Data--> costdetails-->select tags column-->rightclick-->transform-->JSON-->this will split all tags into columns, each column header is tag name and fill the cells below the tag values. It worked correctly for me. Also I created a csv file with two columns find and replace. All tag names in find column and corrected names in replace column. this also help to normalize and consolidate the tag names to what I need. Similarly I did normalization and consolidation of tag values to find and replace incorrect values with correct values as needed.
One thought I've had about how to automate this might be to add an array of tags to extract in config, then have the report read from that and extract them as needed. This would support version updates of reports, so you could keep getting the latest without having to customize each report every time you update. Would that help you here?
@flanakin that would be a good idea to extract out the tags from the cost report and separately build it as config. I think it would reduce the file size as well make refresh faster.
When you say version updates of the powerbi reports, what changes keep coming up in these reports? If a powerbi report is configured and published, it keeps refreshing and update the dashboards.
@saleemmy We added a "PromotedTags" step in the CostDetails query in Power BI reports to help with this. We still need to do more to up-level this into a cross-version configuration setting, but hopefully this gives you a stepping stone to easily identify what tags you want to promote to dedicated columns. Does this give you the foundational aspects you're looking for?
Hey @flanakin, I'm helping a customer implement the FinOps Toolkit, and even though their Subscriptions are tagged, I'm not seeing those tag names / values anywhere within any of the tag-based fields. Should the Subscription tags be included in the ingestion/export?
@AErmie If the Tags column doesn't have values, that sounds like a problem in Cost Management. I don't think we do anything that would impact that. If there was a bug, I would expect to hear about that a lot more. Let me know if you're still seeing this, tho. The data should absolutely be there.
Sorry for missing this. Trying to get better at monitoring all the changes across Github 😓
Hey @flanakin, sorry for the delay in responding. We just re-deployed the FinOps Toolkit (v0.8.0 in "private" mode). We set up the Data Factory's Managed Identity with "Department Reader" at the Enterprise Agreement level.
While we've experienced some challenges with getting it to work (opened some new GitHub Issues concerning), we at least home some backfilled data in the ADX now.
When I look in the ADX Ingestion database, specifically the Costs_final_v1_0 table, and at the Tags property, I see it does contain the values that we have applied (though this may be because we've applied tag inheritance to the subscription's Resource Groups and Resources).
What we ultimately would need, though, is some guidance on creating/adding a new ETL that would extract our custom tag values (ie. account_coding, billing_group, ministry_name) into their own column (like I've done in the past with modifying the PromotedTags step). This will enable us to use these as actual filters in the dashboards/reports.
@AErmie Are you looking at doing that in Power BI, ADX dashboards, or in other queries? You can still use PromotedTags in Power BI, so that option should be good. We can also add an example into the dashboard, if that would be helpful as a short-term solution.
Long-term, we're using #666 to track a new feature to create new tag rules, where we'll add the ability to create new tags and extract tags into columns in the actual data. We've put a little thought into this, but there's still more work to be done. I don't have an ETA yet. We'll likely implement an extensibility model before we do that. We're discussing those options currently.
Hey @flanakin, we originally explored doing that using the Power BI reports (in v0.6.0 and using the Storage source). Now we're trying to think/plan future-forward and long-term, so we've re-deployed v0.8.0 using ADX. While I am familiar with achieving the desired results in Power BI (by modifying the PromotedTags), I am not familiar with how to accomplish the same for the ADX dashboards, or for the Power BI report (using ADX as its source).
An example for the ADX dashboard would be very much appreciated, and at least help us to continue to explore the options available.
The Extract important tags would meet our exact needs. We were thinking of trying to create a custom ETL for the Data Factory, to somehow inject/insert additional columns for the data, so that we could use this as an effective row-level data security. That way, we wouldn't have to create a bunch of custom data sources/reports (ie. one for each and every Government Ministry!). The perfect scenario would allow us to use a generic report/dashboard, grant access, and the data presented would be filtered based on the Ministry of the user.
Happy to provide feedback on potential ideas/approaches the team is considering, and also to test them in a Government-based implementation of the FTK.
@AErmie Power BI with ADX has a similar approach right now. Under the covers the code is different, but the configuration is mostly the same.
I don't think we have this in the ADX dashboard yet, but adding that would be easy, if it's a requirement for you.
Doing this in ADX would likely be simpler. You could create an update policy on the Costs_final_v1_0 table that would generate a new table with the new columns. This is the approach I've been thinking about.
Hey @flanakin, adding that to the ADX dashboard would be appreciated, that way we can effectively apply the same filtering based on our enforced/inherited tags.
If the team could possible provide an example/guidance on creating update policies on tables, etc. that would be appreciated.
Hey @flanakin, now that we have successfully ran the backfill for our data, the next step is to customize the ADX dashboard and PowerBI reports with our specific tag separation.
I've done this in the past for the PowerBI reports (when we were testing with the Storage Account method). Since we are now using the ADX PowerBI reports, then the data in ADX (aka the source) is what needs to be modified.
You mentioned that we "could create an update policy on the Costs_final_v1_0 table that would generate a new table with the new columns." Any reference examples on how to do that?
@AErmie sorry, missed this comment. The method is the same today. Edit the promoted tags in the Costs query in Power BI. We haven't added this to the dashboard yet, but the code can be copied from PBI to ADX.
Look at the IngestionSetup_v1_0.kql file in the repo. Copy the Costs_transform_v1_0 function and the Costs_raw update policy. Change the update policy to point to the Costs_final_v1_0 table and change the function to pull from Costs_final_v1_0 and add any custom columns you need. You don't need anything from the current transform function. That's just a template to follow.