sep_epic story5: PowerBI or BUST!

Develop the ability for the admin user to:

[ ] Upload a data file containing p-chart information.
[ ] Run an experiment that puts data into data variables in Sim UI (create a p-chart model)

7/14 - @lzim @staceypark @lijenn @jamesmrollins - Reviewed various ways to look at the results information, with different qualities (such a time series decomposition and regression analysis). This is not completely decided. To provide a complete story, we may need to include a variety of charts in the tt report. There are no apparent technical limitations to this potential development task.

Decided:

Review @dkngenda reports at the Monday, 19 July meeting for further discussion.
May need tt3 reports in about 6 - 8 weeks.

@lzim @staceypark @lijenn @anazariz @hirenp-waferwire

7/20 Report of conversation with @dkngenda regarding tt3 reports.

Summary

@lzim specified three graph types for the tt3 reports
- Time series decomposition graph
- Linear regression indicating LOESS and trend line.
- p-Chart
Each chart is produced by crunching raw numbers through r-code functions.
R-code can be executed in the Sim UI (maybe), through an R-Base docker in GitHub, or native in r-notebook.
SQL standard queries would need to be established drawing data from the CDW.

Alternative 1

User downloads CDW produced raw data for a given team.
The user drags and drops the file into the Sim UI dashboard using the TT setup function.
The Sim UI, using a web-hook grabs the data and sends it to GitHub using the GitHub API.
The GitHub API delivers data to a given repo, which triggers an R-code processing action.
The processing action, opens a R-base docker and executes previously validated r-code to produce textured* data in terms of time series decom, LOESS and P-chart.
The GitHub API then moves the data from the repo to the Sim UI, which received the data and renders the appropriate chart in the tt3 report.

Alternative 2

User downloads CDW produced raw data for a given team.
The user drags and drops the file into the Sim UI dashboard using the TT setup function.
The data is uploaded into the Sim Ui Data API.
R-code is executed using the r-studio API, resulting in textured* data.
Textured* data is rendered in appropriate charts in html.

*Note: Textured data means data that has been processed through an R-code function resulting in desired statistical features.

@jamesmrollins & @dkngenda @staceypark @lijenn @anazariz @hirenp-waferwire

BLUF: As outlined, Due to concurrent demands, I do not believe this will be possible in time for the delivery of the NIH tt3 reports due in ~ 6 weeks via the Sim UI as described above. In fact, tt3 visualizations are actually a more minor deliverable relative to our larger priorities and capacity constraints between now and 9/1.

Instead it will be necessary to deliver our goals in ~6 weeks via our NEW "Data UI" on Power BI as described below, achieving other priorities simultaneously._ 🚀

When we meet today at 3:30PM during the Sim UI workgroup we will divvy up tasks necessary to synthesize our tt3 visualization goals within these larger critical Team PSD needs, which is actually the straightest line to delivering what is needed for tt3 and migration to Microsoft 365 & Power BI as planned since 2019 given i, ii, iii, iv, v): i) compliance - who governs our existing resources and how we would achieve these objectives within those requirements ii) definitional - how the definitions vary by sta3n, sta6a, team iii) technical - how the CDW is accessed with what codebase (SQL for facility vs. SQL/VBA for data UI vs. R) through which server (facility a01 vs. FRE SQL33 vs ORD research workgroups), by whom (Team PSD member vs. other OMHSP partners vs. MTL Facilitator vs. _MTL Learner) and why in the MTL program. iii) user permissioning - given who uses which Team PSD and workflows to do what, and who permissions these workflows, these are described in Team PSD documentation & MTL documentation. iv) documentation - as we have seen, despite our team focus in the Jan 2021 6 month roadmap on story 2 (perm tables), story 3 (CDW documentation, & story 5 (planned Power BI migration, settled for required Sharepoint Online migration), we currently have not achieved our Team PSD objects for the document_teams column of the documentent_tracker and retain the risks of lack of redundancy in the Team PSD members who can assist with synthesis of existing resources address planned migration to the Microsoft 365 ecosystem & updated tt3 visualizations.

TEAM @jamesmrollins & @dkngenda @staceypark @lijenn @anazariz Please take time to increase your familiarity with any details unfamiliar to you before today's 3:30PM meeting and come prepared with questions (ultimate goal: shared fluency) with the details described below. As you will see, it requires synthesis of multiple dependencies that have not yet been synthesized by prior Team PSD efforts.

NEXT STEPS: Integrate tt3 visualizations in the deliverables of the Power BI Story 13 Go Live on 09/01/2021

This focuses additional Team PSD members beyond @lzim @anazariz @lijenn & @staceypark on resolving critical blocking dependencies to to the Modeling to Learn national quality improvement initiative (i.e., not just tt3 reports) by focusing on our primary need: local team_data access via a cached Power BI data model, rather than legacy Excel based local queries with chronic bugs and performance issues - given the ongoing rise in bugs, we cannot support the 14 VA facilities planning to begin on 09/01/21 without this.
In addition to focusing more Team PSD members (e.g., @dkngenda & @jamesmrollins) on the single shortest path to resolving our greatest risks, there is also a considerable wealth of resources from CDW, BISL, and Power BI that we have engaged and a ready to provide weekly support through their office hours, which occur multiple times per week.
@anazariz describes capacity constraints with managing current facility file and data UI bugs that have prevented our use of CDW/BISL/Power BI resources to meet our migration away from Excel to date. We are now in regular communication with them and they are awaiting our effective use of them through these channels to permanently abandon long query times, chronic bugs and other issues with our Excel-based workflows.

Dependencies: a) We have a backlog of both facility & data UI bugs/tasks on Sharepoint Online that prevent propagation and readiness to support our next VA wave (described below). b) To understand this synthesis as "straightest line" to achieve multiple goals for the team effort expended requires fluency with the underlying i) compliance, ii) definitional, iii) technical, iv) user & permissioning, v) documentation and as observed on Monday we have not achieved this team-wide fluency (yet) We will 😅

Clarifications for translating among Team PSD members: a) tt3 visualizations will need to occur via Power BI reports run on our PTSD_OMHO workgroup via the A01 CDWWork Production server (Note per above for CDW fluency: When communicating with VHA CDW/BISL/Power BI Staff, it will be clearer not to use "raw" to describe our use of CDW data, as "CDW raw data" has a specific meaning and is not what we use, we use "CDW Production data," hosted on the main VHA Operations Server known as "a01" via our CDW workgroup "PTSD_OMHO."

CDW production data is pre-processed. CDWWork data is typically not available to non operations staff. With Cora and Andrew intentionally worked to build off of OMHSP PERC data tables to ensure immediate real-time consistency between MTL and other OMHSP data dashboard.
As described on Monday, @anazariz comments his SQL code base to indicate where he has made changes from our historical code base.
Both Cora and Andrew remain in VHA working with data and have historically been very ready to help us. In addition, Anthony maintained his VHA badge to provide as necessary consultation as a VHA WOC volunteer.
Our workflows for accessing the primary tables diag, enc, hf, meas are at the sta3n facility level. (not sta6a or team).
We currently have relevant bugs #2115, #2089, #2092 that as of COB last night prevent i) regular consistent national propagation of HF table necessary for viewing EBPsy and EBPharm - At present, @staceypark & @lijenn are producing alternative files for co-planning meetings this week using the Data UI, which accesses data via an alternative process. ii) As a result, I have asked @dkngenda for further assistance with review of the SQL source on PTSD_OMHO on #2115, which has been open for 14 days. Splashpage_monthly_update_readme.docx (Master document #626) used to nationally propagate our facility.xlsb (binary) files. iii) As we confirmed at Monday's 8AM workgroup leads meeting, this complete SQL source code file producing the four primary tables is splashSourceIncrementalUpdate.sql**
Permissioning of operations access to PTSD_OMHO is granted by myself via approval processes for VHA National Data Systems. All Team PSD members have current access, inclusive of VHA programmers now working with VINCI (Andrew) or PERC (Cora), who have retained this access to provide assistant when needed, and inclusive of PERC programmers who helped us set up our initial queries when working with VHA OHT.
Among our MTL user base, the only access to national operations access to CDA a01 are facilitators and include OMHSP QIICs and Systems SMEs (David, Jane & Debbie).

b) Our R21 R Notebook codebase for these visualizations are at the level of the sta6a clinic (consistent w/our analyses proposed in tests of specific aims, and not at our level of quality improvement intervention, which is the specific team. This distinction is important - see "c" below.

Data used in our R Notebooks for the R21, R01 and IIR are all accessing ORD workgroups on CDW server B02, which is not operations access governed by VHA National Data Systems, but rather research access that is governed by VINCI via the DART process, and only after approval by Stanford IRB via e-Protocol and local and national VHA Office of Research and Development (ORD).
As all Team PSD members know, Operations data can never be used for research and research data would not practically be used for operations (the B02 research server is pre-processed differently than the A01 operations servers).
Any changes to our research use of data require compliance processes that may exceed 6 months, but again are irrelavent as our pressing priorities are related to Operations/quality improvement.
While our R code for visualizations could be repurposed for an operations purpose and/or managed all within the VHA domain via our VINCI workspace or our VHA GitHub enterprise workspace. This would not practically be a fruitful/efficient direction due to the i, ii, iii, iv, dependencies outlined above.

c) As we also reviewed together through much of Monday 7/19 extended 8AM Workgroup Leads meeting, Team data tables used in Modeling to Learn are not primarily produced via our PTSD_OMHO workgroup rather MTL capitalizes on existing infrastructure for "row-level permissioning" via LSV servers on the Field Reporting Enclave (FRE SQL33), which enables VHA frontline providers (aka MTL "learners") to access data for their local VHA sta3n facility.

MTL admin @lijenn and @staceypark grant OMHSP permissions to teams of MTL learners accessing their team data folder on Sharepoint online at mtl.how/data (accessible only within VHA with badge engaged).
When MTL admin or facilitators support these teams, the codebase checks first for local LSV FRE SQL33 access, and if it finds none, it defaults to the larger PTSD_OMHO national permisssions we grant to MTL admin and facilitators. As we reviewed at quant_operations last week retaining this ability of our team to support users at other VHA Facilities will be necessary in Power BI as well.
Moreover, effective engagement with CDW/BISL/Sharepoint Online & Power BI workgroups will need to clarify these important details regarding how MTL works: national facilitators, local teams, to help with set up on Power BI, rather than through our existing Sharepoint Online & Excel tools.
As a result of these key differences in the ways we support our different MTL user bases, the Data UI currently runs local team LSV queries on the basis of MTL team selections in the ClinicSelection and SPReferrals tabs.
It is because local team access to local team data is a key value proposition of Modeling to Learn core to our participatory philosophy that I requested that @anazariz focus any efforts to develop a Power BI MVP #2112 on development of the ClinicSelection and SPReferrals selection workflows/interface in the Power BI environment. This is the most challenging, but most important need. And, via our work on Story 5 we previously confirmed with BISL, Power BI and Sharepoint Online staff this this is doable - START HERE!!!!!
Because the teams run these queries, and these local LSV queries have time limits - teams receive timeout errors when teams are big, and queries are run during restricted windows before 12noon Eastern time.
These longrunning queries have raised our need for support to CDW/BISL support staff with their interest in helping us resolve them via a) query improvements, and b) migration to Power BI.
As the team reviewed via our extended 8AM Workgroup meeting Monday 7/19 - The Data UI.xlsm (macro) files are also nationally propagated* and run via embedded SQL & VBA code. This is described in the file "Data UI propagation.docx" (Master Document #627) and the code is reviewed by selecting the "Developer" option within the "DataToolFRE23_nevermore_online.xlsm"
Per @anazariz the Power BI environment uses SQL, a GUI-like approach to setting up the Data Model and for some task DAX code - no VBA, which is a legacy approach to visualization Per the VHA Power BI - All the necessary resources to move our SQL/VBA-based resources to Power BI are well-supported and documented.
Because we have not made progress on these tasks, we are currently getting buried by bugs on the legacy platforms that further tax capacity. Although the performance issues with the legacy platforms was well-understood and motivated the desired migration to Power BI, the extent of these issues halting national propagation of facility.xlsb and data_ui.slxm propagation since April 2021, and issues with Story 2, 3, & 5 has only recently been identified at hq over the last month and subsequent to Anthony's departure. For example, we have #2115 still active after 14 days.
Despite the long-running queries, issues with chronic bugs, etc. this file is currently "working" and in lieu of successful national propagation by the quant_workgroup @lijenn & @staceypark are using it as a replacement for the facility.xlsb file for current co-planning sites.

Summary

With the exception of the latest information above about bugs and workaround and associated impacts on current teams and current co-planning, the basic workflow outlined above is not new and was established between 2016 & 2019 by our team, OMHSP and OHT (Office of Healthcare Transformation.
Although we desired to improve further between 2019 & 2021 via Stories 2, 3 & 5 with planned migration to Microsoft 365 apps, such as Power BI, we are behind on this priority.
The backlog of bugs & delays is preventing sufficient forward momentum on Power BI, but more critically, is a risk to beginning our next set of VHA facilties (!! eek 😬 ) in Modeling to Learn.
We retain all legacy assistance of Cora, Andrew, Anthony, PERC, Lindsey & Stacey.
We have gained substantial Microsoft 365 support in VHA and they are aware we need their help now.
We will take an "All Hands" approach to resolving this by 09/01/21 and we have several meetings set up to gain help with this!

Shared Team PSD Fluency needed for Story 13 - "Power BI or Bust" 🤣

Given my observations of our efforts to address these bugs in our team meetings, it will be
I expect that @staceypark & @lijenn are fully fluent in every dependent detail above.
I expect that @anazariz & @dkngenda are partially fluent in the dependent details above.
I expect that @jamesmrollins is the least fluent in the dependent details above.

At 3:30PM Sim UI meeting today - As the most fluent Team PSD members, @lzim @staceypark & @lijenn will answer questions about these details, documentation and tasks and then move to divvy up responsibilities for Story 13 "Power BI or Bust

Note: hq_workgroup is supporting 6 meetings today before the 3:30PM Sim UI meeting.
This our plan for attendance at today's meetings - freeing up time for @jamesmrollins @anazariz & @dkngenda to prepare for the 3:30PM: qual_workgroup (Stacey), co-planning (Lindsey & Jenn), co-facilitator prep (Jenn), quant_workgroup (Lindsey & David), D365 (Lindsey, Stacey & Jenn), & Support Workgroup (Lindsey, Stacey & Jenn) today AND running backup data using the data_ui.xlsm file for our co-planning meetings tomorrow.**
@anazariz @dkngenda @anazariz - Come prepared to ask questions at 3:30PM. Please read carefully the #1961 details above to develop questions, _including any referenced bugs or documentation_, and read the #2112 Power BI ROM @anazariz developed.
Additional helpful resources include LucidCharts 2021 Roadmap of User Storiesm MTL guides at https://mtl.how/ and soon - the VHA CDW, BISL and Power BI resources - more detail on that this afternoon.
All, please _LIMIT "side huddles" with hq_workgroup AND among less fluent workgroup members, until great shared Team PSD fluency is achieved - thanks!_

I know this is mostly a regurgitation of the above, but represents my understanding of the problem and related tasks. It also has my questions.

FYI: @anazariz @dkngenda

Facts

There are several outside-the-team staffers (Cora and Andrew) and volunteers (Anthony) who will lend a hand as necessary.
All Team PSD and the volunteers above have necessary access and permissions to access the ops side of the CDW.
MTL Users, facilitators, QIICs and Systems SMEs have access to CDW.
The R21 Notebook codebase targets the sta6a clinic level, not the team level.
R21, R01 and IIR accessing CDW servers reserved for research only purposes. There is a multi-step (and time consuming) protocol that governs access to and use of this data.
The team level is the target for quality improvement data and visualizations.
Operations data can never be used for research.
Because of shift to Power BI priority, we will not repurpose the research codebase for operational quality reporting purposes.
Team Data is mostly generated from the Field Reporting Enclave (FRE SQL33), which is at the sta3n facility level.
Jen and Stacey manage permissions for MTL learners to access this information.
The team data queries the FRE SQL33 data first, and finding none, escalates to PTSD-OMHO for data fulfillment.
Based on user selections, queries must be able to reach from the team level to the clinic level in some use cases (SPReferrals).
Local LSV queries have time limits. Long running queries must be run before 12 PM EST.
Data propagation is explained in Data UI propagation.docx. Code is available in the "Developer" option within the DataToolFRE23_nevermore_online.xlsm.
We have many stacked up data ui bugs.
This is putting next research phase at risk.
This is an "All Hands" alert.

Specified Tasks

tt3 visualizations will occur via Power BI reports run on PTSD_OMHO via A012 CDWWork Production server.
Do not refer to data from the CDW as "raw" data - it is CDW Production Data.
The above is significant because "raw" data is only available to non-operations staff.
Effectively engage with CDW/BISL/Power BI workgroups, clarify MTL details and set up Power BI as an alternative to our current SharePoint and Excel based tools.
Regarding task #2112, the priority is the development of the ClinicSelection and SPReferrals selection workflows/interface in the Power BI environment.
Seek CDW/BISL assistance with improving queries and migration to PowerBI.

Implied Tasks

Develop a test plan to ensure queries are returning the appropriate results in shortest time possible.
Test Team Data Table outputs and ensure proper cell addressing for data components.
Update mtl.how/data re-direct
Update See and Say guides for sessions 1, 2, and 3.
Update the mtl_how_facilitator_one_pager.pdf
Update the mtl_how_data_cheatsheet.pdf
Provide facilitator training on the new PowerBI tools (maybe).

Questions

I am at your service - how can I help?
Will we want to display the tt3 PowerBI visualizations in the Sim UI and print the related output?
Do we want to move any of the other tt report elements to PowerBI?
In the past, @lzim mentioned that she wanted PowerBI to look like the Data UI spreadsheet as much as possible - should that be a part of our planning assumptions?
I read from issues #1915 - #1939 that we have validated necessary PowerBI licenses, SharePoint on-premises has been successfully migrated to SharePoint 365 and we have inherited the necessary ATO. Have we done any PowerBI test reports or visualizations?

Thanks @jamesmrollins I really appreciate your quick work on this 🐇

Please see my notes, which are intended to "nip in the bud" 🌹 any potential for misunderstanding.

FYI: @anazariz @dkngenda @lijenn @staceypark

Facts

MTL Users, facilitators, QIICs and Systems SMEs have access to CDW.

Yes, but to develop using Power BI app, it important to understand that CDW access is different for different user groups: MTL Learners (access CDW via LSV FRE SQL133 only), other users via A01 PTSD_OMHO.

Because of shift to Power BI priority, we will not repurpose the research codebase for operational quality reporting purposes.

Potentially misleading, would clarify: We will not repurpose R code via GitHub to produce visualizations in the Sim UI. But, to achieve migration to Power BI, any work described in Story 2 (perm tables, rather than temp), Story 3 (CDW documentation) and Story 5 (Power BI/Sharepoint Online migration) SHOULD be.

We will require repurposing applicable work and SQL codebase, through PTSD_OMHO a01 operations server when applicable or via LSV FRE SQL33 or from ORD to one of these. This will be necessary to resolve all data challenges and accomplish tt3 visualizations of "EBP Initiation" and "EBP Dose" in Power BI.

Team Data is mostly generated from the Field Reporting Enclave (FRE SQL33), which is at the sta3n facility level.

Would clarify - Team Data is generated by teams via the DataToolFRE23_nevermore_online.xlsm, which queries national CDW data enlisting the existing permissioning (not managed by Team PSD) of CDW via FRE SQL 33 to frontline staff for access only to their own facility data (sta3n) - from which they produce a narrower team dataset using the ClinicSelection & SPReferrals tabs of DataToolFRE23_nevermore_online.xlsm. When MTL facilitators use DataToolFRE23_nevermoreonline.xlsm to generate Team Data, it runs the same SQL queries_, but enlists different permissions & servers that I and VHA National Data Services grant access to via our CDW a01 workgroup named PTSD_OMHO.

Jen and Stacey manage permissions for MTL learners to access this information.

hq manages access to team folders on Sharepoint Online that contain a facility (sta3n-specific) version of DataToolFRE23_nevermore_online.xlsm, but we do not the permission to access FRE SQL 33 (see above).

This is all cloud-based and we are inheriting the VA's larger umbrella "Authority to Operate" (ATO) to use Power BI This is great!
To inherit this ATO, VA DevSecOpps (CDW/BISL/Power BI) expects OMHSP/Team PSD to provide additional efforts, guidance, and policies for our MTL facilitators and learners to protect PHI, which we do by training and by permissioning the team folders that contain PHI to specific teams, whereas the larger facility files (de-identified viaDiag, vizEnc, vizHF, and vizMeas) are not permissioned as this is not necessary and we do not need to (want to) add this additional work.

The team data queries the FRE SQL33 data first, and finding none, escalates to PTSD_OMHO for data fulfillment.

Only for Team PSD & MTL facilitators, not learners, who do not have national access. _Team PSD & MTL facilitators must use PTSDOMHO to support a team from another facility other than their own home facility.

Based on user selections, queries must be able to reach from the team level to the clinic level in some use cases (SPReferrals).

For specificity, would clarify SPReferrals often track sta3n ("facility") referrals, not just sta6a ("clinic") referrals.

tt3 visualizations will occur via Power BI reports run on PTSD_OMHO via A012 CDWWork Production server.

More complex:

Still to be determined how we will run PTSD_OMHO (national a01 CDW PROD server) Power BI reports. Replicating Power BI visualizations at the facility level for diag, enc, HF and Meas tabs of our facility.xlsb files where there is no permissioning and no PHI = this is relatively easy.
Team- or clinic-specific reports are trickier given that all current team visualizations happen in the viz tabs of DataToolFRE23_nevermore_online.xlsm via FRE SQL33 Therefore, how this is addressed, is more likely to be determined based on how we develop the ClinicSelection and SPReferrals functionalities that define teams in a Power BI-based Data Model, reflecting the priority to have an Power BI MVP of ClinicSelection and SPReferrals.
All our efforts to produce sta6a EBP initiation and EBP completion visualizations occur in SQL code on ORD servers The SQL code itself can be repurposed for EBP Initiation & EBP Completion tt3 visualizations, but permissioning plans would need to be determined per the above re: ClinicSelection and SPReferrals and of course then run through the appropriate server whether A01 or FRE SQL33

Questions

I am at your service - how can I help?

In consultation w/CDW/BISL/Power BI, help Team PSD to develop Power BI ClinicSelection and SPReferrals tab MVP for building teams ASAP within the server and governance structure that exists in VA and consistent w/how MTL resources are used by our 3 primary users - admin, facilitators, teams.

Integrate existing VBA/SQL codebase into SQL and Power BI Data Model that meet core MTL local team data functionalities outlined above.

Effectively engage with CDW/BISL/Power BI VHA resources on behalf of Team PSD to answer our questions with their online trainings and resources. Use them effectively at their office hours to resolve any blockages we encounter while minimizing impact on MTL documentation and training, which can rapidly exceed our capacities. NOTE: In all previous meetings with them, they indicate that all the guidance is generally available to migrate all of these functionalities

Will we want to display the tt3 Powe rBI visualizations in the Sim UI and print the related output?

We will determine this once we are getting any visualizations at the team level in Power BI. 😅

Do we want to move any of the other tt report elements to Power BI?

No.

In the past, @lzim mentioned that she wanted Power BI to look like the Data UI spreadsheet as much as possible - should that be a part of our planning assumptions?

Should it influence our planning = Yes. To be effective, the key is understanding how and why this requirement exists. This planning/design/development requirement exists for four primary reasons that are easy to understand based on our prior development efforts on Team PSD.

The existing resources were developed with years of iterative MTL learner (user) input & deviations must be understood by considering why the resources exist the way they do now from no less than 3 MTL user points of view (POV): learner, facilitator, admin/support workgroup.
The existing resources were developed to empower local team access to data within existing VHA data governance, permissioning infrastructure and OMHSP definitions. We cannot change how VHA governs data and we cannot change that MTL is about supporting teams and gain greater control of use of local team data.
MTL is underway now, growing, and OMHSP facilitators are limited in their ability to re-train much - Our old rules apply, the change should be such an easy-to-understand improvement that our user-base welcomes the improvement 😺

BTW: What is the main improvement everyone sought in Power BI starting in 2019? Achieving the "ClinicSelection" and "SPReferrals" functions for building team data in a Power BI data model, which uses caching to eliminate waiting for queries. It is for this reason that the MVP should start here - it's what everyone is waiting for!
The associated pain and stress of not achieving this via our work to date has become paramount, but the above has been the guiding vision for 2 years.
1. A single change in any Data resource may require up to 10 depending documentation changes, rapidly exceeding our capacities.

IF WE ALL UNDERSTAND THESE ONGOING Team PSD _PRINCIPLES (no real change here 👍), then it's all about how specifically we can achieve the needed Power BI implementation in practice.

EASY: Produce the eight viz and data tabs (diag, enc, HF, meas) of the facility.xlsb file in Power BI. This is "easy" because they include no PHI and require no permissioning to make available nationally via PTSD_OMHO on a01.
MEDIUM HARD: Produce the same eight viz and data tabs (diag, enc, HF, meas) of the data_ui file that DO include PHI and enlist LSV permissioning
HARDEST AND MOST IMPORTANT: The reason I requested the focus be on a Power BI MVP for ClinicSelection and SPReferrals tabs used to define what a team by a team in the field is that selecting these columns took years of iteration _and is the primary value add of MTL in supporting local teams. AND - we have to reconcile the permissioning issues, etc. AND - this is where we have to repurpose the most code because all the VBA will not be usable in Power BI.

I read from issues wk2 may_epic story_5 task: ATO for PHI/PII #1915 - wk? may_epic task: story_5 SharePoint Online - dev to prod release process #1939 that we have validated necessary PowerBI licenses, SharePoint on-premises has been successfully migrated to SharePoint 365 and we have inherited the necessary ATO. Have we done any PowerBI test reports or visualizations?

Yes. @anazariz recreated the 4 viz tabs of the facility file in Power BI and showed us last week.
No one has to do much to go to the Power BI resources online and get started, as CDW/BISL/Power BI want us to migrate to Power BI, have a huge team set up to support us. There are no specialty licenses, permissions or ATOs. We confirmed all that several months ago, which is great. - 🔥

@lzim @staceypark @lijenn

knowledge check on the following items

PTSD_OMHO is our operational database (space) that's located on CDW production server AO1.
The Facility file is produced using Enc, Diag, Meas and HF tables that are produced by the splashSourceIncreametalUpdate.sql code. This SQL code sources data directly from the PTSD_OMHO database.
David is tasked with reviewing splashSourceIncreametalUpdate.sql code with particular attention to why there is missing HF data.
_Splashpage_monthly_updatereadme.docx documents facility file propagation process.
Data UI files are produced by excel macros consisting of SQL & VBA code that source data from LSV FRE SQL33 based on ClinicSelection and SPReferrals tabs. Ability to view local data using this selection is main MTL value proposition.
Data UI propagation.docx describes the propagation process for DataUI

Data permissioning and access

Questions

What do I need to know/do to safely run chuncks of the splashSourceIncreametalUpdate.sql while checking this code?
During code review what Should I be looking for?
Where can I find the facility.xlsb produced by this SQL file

Question1:

Which of the following modes are we going to be using?

Power BI has two modes of working with data: Import Mode and DirecQuery Mode. The design decision between these two modes has a major impact on scalability.

1) Import Mode is similar to how Facility works. Data is retrieved (or refreshed on a regular basis) and the end user works with the cached data. BISL recommends this mode because it reduced the load on the on-site database (CDW) database. In this mode the end user connectivity to the database (CDWWork, LSV, etc.) becomes less relevant because there is no such connectivity. All security is enforce via RLS, Power BI Data Model and 365 Individual/Group roles.

2) DirecQuery Mode is similar to the Data UI. The data is fetch on-demand. In this mode the end user connectivity to the database (CDWWork, LSV, etc.) becomes relevant. This mode is useful when the back-end data is changing frequently (e.g. daily transactions). End User provisioning in this modes is controlled via the database as well as the 365 Individual/Groups roles. In this mode RLS is implemented via the LSV database, which contains views to CDW that are segmented based on user's National vs Local permission.

Question2:

Are open to a redesign of the Facility/Data UI architecture?

The Facility (Aggregate) vs. Data UI (Detail) architecture was done in that fashion for security reason and access levels. These can be re-thought in Power BI. Example: Design a base model at the lowest level and then use that model to design an aggregate model. Control who has access to each model via 365 roles.

Question3:

One giant data model segregated via sta3n RLS or multiple individual models each with a separate sta3n? Please see these Lucid charts.

one all

Please note that from a user perspective it makes no difference what method is use. The user sees his/her own data regardless of the architecture. There are pros/cons, which we can discuss.

7/21 sim ui workgroup meeting

During code review what Should I be looking for?

Start by looking at all the code Ash added/changed from the original historical file, which Ash commented

Where can I find the facility.xlsb produced by this SQL file

TeamPSD > splash_pages > SplashMothership_online and TeamPSD > splash_pages > DataToolFRE23_nevermore_online

Sim UI Meeting 7/21

Record of assignments available in #2112. All assigned tasks below are due week 4 July.

Decided:

We will use Import Mode in PowerBI. However, we will need a permissions policy for PowerBI since all these will now managed at that level.
Row level versus national level permissions will need to be defined in PowerBI.
(HQ)@lijenn and @staceypark should start implementing user permissions as soon as possible in the premium workspace PTSD_OMHO in PowerBI. HQ will investigate whether a single permission setup will suffice, or if this drives yet another team setup.
We should set up DEV/TEST/PROD workspaces to support our PowerBI pipeline. @jamesmrollins will investigate recommendations by PowerBI on how to set this up (whether in a single workspace or with multiple workspaces) and update the Team PSD manual.
@dkngenda will work on reviewing splashSourceIncreametalUpdate.sql code with particular attention to why there is missing HF data.
We will replicate the tabs in the current Data UI in the PowerBI MVP. This means:
- Selectors are at the column headers
- The team selection tab looks exactly like the current spreadsheet (buttons and etc).
The columns below will be added to the viz and data tabs based on inputs from the ClinicSelection tab.
- Column E - Physical Location
- Column F - Primary Stopcode
- Column G - Secondary Stopcode
- Column H - Default Provider
- Column I - Inactivation Date
Data UI sta3a files will be automatically refreshed monthly.
Facility files will need to be automatically refreshed monthly.
Data will need to be provided with minimal wait time.
National users will continue to be permissioned by Team PSD.
A generic role would need to be created that would allow general population access to the facility files.
Team Data "viz" and "data" tabs will be able to be cached by our MTL learners and Facilitators.
@anazariz will produce the MVP of the Clinic Selection tab and SP referrals tab by next Thursday, Quant Operations Meeting 29th July. Reference #2112
@dkngenda the Data UI "nevermore" SQL/VBA code is producing the missing May and June HF data, but the Facility "Splashpage" source is not - INCLUDING in PowerBI.

Cross-ref #2112 - Thanks @staceypark and @jamesmrollins for tracking the questions and decisions covered at today's meeting here on the card 🏆 (James, this trophy means "You two are champions") 🤣 (you know what this means).

@staceypark

Some of this may be helpful for @jeffhoerle getting oriented to the Master files and the discussion of Power BI in August.

But, otherwise, this may be referenced in Story 13/Power BI cards, and otherwise be closed.

closing this one & cross-ref #2262

lzim / teampsd