PowerBI Data Model for CCMDB

I am setting up a data model for CCMDB in PowerBI for some of the data I am doing for Dr. Robert's project. Needed to document some stuff and thought this was a better place for that than my email or similar. Rough notes for now.

state of setup

• I have things mostly set up to work with current data, ie I have not necessarily dealt with old style dates or dxs etc
• currently only working with content from CCMDB, CFE, Centralized, ie not labs or any legacy data

File Location

• currently on my desktop

copy to x:

Modelling

general notes about modelling in PowerBI

• requires a different way of thinking since it is designed around being able to slice and dice the data, ie if you set the model up right you should no longer need to decide if you calculate by admit or dispo, because you can just range right into the middle of things.

things modeled

• Hospitalization table to group data for easy access to first and last records of a hospitalization and all D_IDs in it
• L_Boarding which
  • locates links in the related Transfer Ready DtTm tmp entry and tags the initial one for calculating one kind of Med LOS
  • tags all ERs that are "initial" to avoid the negative numbers problem I was getting in created_variables LOS
  • I think I have it set up to do EMIPs correctly
• LOSs contains measures to calculate LOS_ER, LOS_Reg (legit med stay), and LOS_Over (time between initial transfer ready and the last dispo for the hospitalization

ER Delay and Overstay Need to make sure I do things this way and include this in the right pages, possibly Change from Service Location to Service, Boarding Loc and Transfer Ready DtTm tmp entry but a lot of other pages touch on this, so maybe it needs to be a template? It may not be consistent everywhere: Note from email from Julie 2024-20-24: I do not use any cut-off e.g. 2020-10-15 because the tmp has been populated by boarding data since July 2018 for ICU and Sept 2019 for Med. The cut-off Oct 2020 Patient Follow only applies for tmp Service but not for tmp Boarding loc. By the way. the patient follow started early at Grace Oct 1, 2020 (they piloted it), while HSC and STB started Oct15, 2020. Similar with EMIPs, I have two sources of ER-delay namely 1. from Accept and Arrive 2. from Tmp : first boarding dttm and second boarding dttm. If both have values, I use the one from the tmp source.

• SMW

• Cargo

• Categories