PowerBI Data Model for CCMDB: Difference between revisions
Jump to navigation
Jump to search
Ttenbergen (talk | contribs) Created page with "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 =..." |
Ttenbergen (talk | contribs) |
||
| Line 13: | Line 13: | ||
* 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. | * 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 | === things modeled === | ||
* Hospitalization table to group data for easy access to first and last records of a hospitalization and all D_IDs in it | * 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 | * 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]] | ** 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]] I was getting in [[created_variables]] 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 | ** 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 | * 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 | ||
{{DT| | |||
* Need to make sure I do things this way and include this in the right pages: | |||
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. }} | |||
[[Category:PowerBI]] | [[Category:PowerBI]] | ||
[[Category:Transfer Ready]] | |||
Revision as of 18:51, 11 December 2024
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
|
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. |