Design and build an Azure hosted data warehouse. Work with client resources (me) to divide up the work for familiarity and cost reduction.
Rough specification of work required:
Skills needed: SQL, PowerBI, SSRS, SSIS, SQL on AZURE, SFTP via AZURE, data warehousing design
1 - Billing Data - sFTP files from vendor to sql server and load into data staging tables
2 - VRAD data - each month VRAD (vendor) provides a flat file of all transactions, build a process that watches for this file to exist and then load it to SQL server. The file comes from a vendor website, and must be downloaded manually, AFIK, but if there is a way to automate that process that would be great.
3 - Schedule data - sFTP files from QGENDA vendor - upload to database
4 - create tables which provide categorization of locations from billing data - for example, Clinic 1, Clinic 2, Clinic 3 are tagged and can be reported on together as "All Clinics".
5 - create tables which group similar exams together, for example, XRAY of left leg, XRAY of right leg are both "Leg XRAY" category. CPT codes from the billing system can be used to categorize, i.e. left and right leg XRAY have the same CPT code
6 - provide tables and process for grouping referring doctors into practices. Billing data will have physician names and NPI codes. User will manually map doctors into groups such as Doc A, Doc B are both in "Physician Practice Group 1"
7 - Tag each charge with what seat or shift the physician was working at when the exam was performed. Billing system will provide performing physician name and NPI. QGENA schedule will provide list of physicians who were working on specific dates/times and what "seat" they were assigned to at that time. Handle situations where study date/time is out of range of scheduled date/time range
8 - Report of hours worked and RVU (from billing) volume for employee physicians. Compute pay based on RVU amounts per given formula.
9 - create tables for grouping/renaming of modalities - i.e. DR and CR = XR
10 - Normalize referring doctor names - using NPI - example: last name of doc changes over time, or other variations from different sources
11 - create a website/report from QGENDA data showing physician schedule in summary form
12 - import exam information from EPIC - using FTP - match to billing data as much as possible, some data will not match, use updated info for enhanced reporting - like turn time from EPIC which is not available on billing data
13 - Load files from FLUENCY database - to provide additional details to billing info
14 - create data warehouse with fact = patient exam, dimensions: referring physician, performing physician, date of service, billing posting date, modality, study, visit, patient, charges, payments, payor, insurance, insurance type, others
15 - map contract bills to a correct location code - billing system tracks them all as "contracts" but the true location is stored elsewhere in the billing data
16 - create 5-10 power bi or SSRS reports as needed
About the recuiterMember since Sep 17, 2017 Anh Dung
from Omnogovi, Mongolia