Data Structure Reshaping Challenge

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)

Hi @JulieCarignan ,

Thank you for reaching out to the community.

While we wait for other members to share their insights, we’ve taken the liberty of using “Explain Simply ,” one of the tools available within Data Mentor (you can explore it here: https://mentor.enterprisedna.co/explain-simply . It generated the following results:



Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on.

Cheers,

Enterprise DNA Support Team

Thank you. Please clarify whether I have access to Data Mentor with my subscription.

Hi @JulieCarignan ,

Your account is equivalent to an EDNA Learn Plus, which includes limited access to Data Mentor & EDNA Chat—basic features only (15 queries per month).

image

When accessing Data Mentor, and you are prompted to “Upgrade”, you can proceed by selecting with Limited Access to continue using the available features.

Additionally, if you’re interested in full access, you may explore our à la carte offers for Data Mentor on its pricing page link here: https://mentor.enterprisedna.co/pricing?currency=usd

Let us know if you have any other questions!

Best regards,

EDNA Support Team