Hi All,
My employer, a state government agency, is charged every month for all licenses assigned to us by the state’s IT department. My management asked me to help analyze the licenses to see patterns and ways to reduce costs.
The data source is a PostgreSQL database view. To prepare it for analytics, its data structure required re-shaping. I’m curious if the way I reshaped it could be improved upon.
I’ve provided an image of the data structure (see “Data Structure Diagram…”). I added color overlays to help show the relationships among the columns. The three right-most columns define the Service to which the licenses provide access. The five left-most columns provide details about the service. Depending on the service, the use of the detail columns change; it’s as if those columns play different roles depending on the service.
For example, if the Service is MG Support, details are needed only from column A, and within column A, only if the value’s prefix is Project.
Another example: If the service is End User Software, then details from 3 columns are needed, but only rows with applicable prefixes.
My management was particularly concerned about End User services, especially Entitlements.
I think a data model that makes sense as my reshaping goal is attached. But I wonder if another data model could serve the analysis better.
And I wonder if the code I used to transform the data could be improved. Would anyone like to give this a go?
Attached are the Excel data source and the PBIX file. The latter has a data source parameter to make it easier for you to connect to it. Most of the transformation is done in the query named reshaping.
License Charge Analysis Generalized v2.pbix (226.5 KB)
License Charge Analysis Generalized.xlsx (164.7 KB)