Understanding how physicians are performing in relation to patient experience is a key metric for hospital groups. Our client is no exception. They wished to make greater use of the Return Data received from Press Ganey, specifically the physician questions and responses.

The good thing is the client already has a regularly scheduled process that gets the Return Data from Press Ganey and loads it into their database. The problem is the raw XML data loaded into tables is an undesirable format. So how do we get the raw XML data into a format that is viable for a dashboard to query?

I handle the problem like an ETL project. I treat the loaded data as an extraction. Then I can transform that data into the desired format needed by the client. I do it this way because I already have a source data of record in the daily XML retrieval process. The final step is to write an SSIS package that manages a set of stored procedures that transforms the data into usable tables. Then the BI team can create the dashboards.
This success of this solution enabled me to set a methodology that can apply to other Press Ganey Return Data.