This can be a long complex discussion so I’ll begin with basic approached I’ve seen work in the past. Take time in the planning on paper creating a architectural diagram and data diagram will help speed the process and determine pain points that need resolved.
1 - Since you are dealing with 3 systems bringing the data central in a normalized format will make the problem simple. Creating a flat file, i.e. CSV file or similar where all systems reporting are dropping like data into like fields. For example since you have on Premise and Cloud, have a field that toggles for you which source or data type is being reported.
If you are trying to mix, NU, Device, Per User into a single metric for and overall documented compliance % it’s a real challenge. If however you create a separate compliance percentage line in your central reporting, aka dashboard then leave them separate. Key here is to normalize your data from Flexera (use SQL), ServiceNow may require a mid server to stage your queries, (I’m not recalling off top of my head how ours was done).
Numerify, Tableau, Microsoft products are all good products to take the normalized data from each, consolidate it then render as a Compliance Dashboard. Drop centralized data to a data blob, Azure or other areas where the reporting and source systems can reach. We started with key fields created or generated from each source like, Publisher, Owner (who is responsible for the metric),License name (Include product, license type, contract type), # of Licenses, # of Consumed (These two calculations take time to create in a remote dashboard so start with some simple ones to test your architecture processes.
Our dashboards where more to govern the SAM program maturity and automate for management a general overall compliance positions. Reviews, work remained in the source system. Take baby steps building this out for the simple calculations, centralize it and maybe report the complex stuff manually at first. then over time create, test, evaluation how to improve. We had many releases over a couple years to get to a decent place, improvements continue as systems change, license types come and go. Treat it as a journey vs a one and done.
Thanks for your insights! Your approach is very helpful. Here are my thoughts:
Planning Diagrams: We’ll start by creating architectural and data diagrams to identify pain points.
Centralized Data: Normalizing data into a CSV format is a great idea. Did you face any challenges normalizing data from on-premises and cloud sources?
Separate Metrics: We’ll keep compliance metrics separate for different license types and use SQL for Flexera data normalization. For ServiceNow, we’ll explore using a MID server.
Visualization Tools: We’re considering Tableau and Power BI. Do you have a preference for ease of use and integration?
Data Storage: Using Azure for centralized data storage sounds solid. Thank you.
Incremental Approach: We’ll start with simple calculations and gradually build complexity.
1 - Diagrams will serve you well
2 - Yes, so start slow. Example I would start with a simple export from Flexera. If you’re on premise I can share a couple queries (sanitized of course) to target say the top publishers.
3 - Unless ServiceNow is storing your compliance data, you can pause on pulling in that data. I’m not familar with your data sources but happy to discuss offline. You’ll want a process that will run ServiceNow queries, Excel can do this as well as most reporting tools. However if you’re a large org you might have a data management team that will drive how you manage data in transit, at rest, etc for security reasons.
4 - Both of your options will work, Power Bi will have lower learning curve, skills can be repurposed for other ITAM work and may integrate into your company well IF PowerBi is part of your Microsoft offerings allowing all to view from it.
5 AWS is storage we used, again your data management team might have input if none exists either of those will work. Consider historical data you will want to store long term. Having compliance data lingering long term can hurt/help up to your org to decide retention limits
6 - Start with a simple SQL query from Flexera, again if you’re on premise. I heard they will soon have access to the backend DB for customers. Last year I checked it was not yet there. Using this simple data pipe into PowerBi just to evaluate. Consider this a feasibility test adding additional elements as you wish. I would start with the top 3-5 publishers you have in your org that need constant monitoring. You could score them on levels of risk those you report, in the end I find I never went beyond 15-20 publishers for the automation portion, as those need more constant oversight. Leave the others for manual review during your quaterly or other cadence licensing self audits.
Thanks for the detailed feedback and for the practical tips. Very valuable stuff indeed, and I appreciate your offer to share sanitized queries and discuss further offline. Will take your advice on starting slow with simple exports and focusing on key publishers.
We’ll also explore Power BI for its ease of use and integration potential, and consult with our data management team about storage and security.
Thanks again for your support. I’ll reach out if we need to discuss anything further!