The Data Wrangling Project
Data wrangling is the process of transforming and mapping data from one "raw" data form into another format with the intent of making it more appropriate and valuable for a variety of downstream purposes such as analytics. The goal of data wrangling is to assure quality and useful data.
Because many communities are looking to develop their own reports, dashboards, and visualizations, ACRE Consulting is now offering tools to help communities utilize their data more easily by providing wrangled datasets.
What is a Dataset?
Data in HIFIS is stored in a variety of tables, sometimes with incomprehensible names and purposes like HIFIS_Services_SP_Programs and HIFIS_Client_ContactEventTypes. While the database is (mostly) documented, it can be quite time-consuming to look up how all of the data is stored, and to make sure it's being properly referenced.
To make your data more usable, special types of tables called views can also be created. Views combine data into preconfigured datasets that correctly connect and cross-reference multiple related pieces of data.
Improve your security, too!
Besides ease of use, another benefit to creating views can be enhanced security. Some reporting needs are so complex that they normally could not be completed without using a SQL query.
That means that your database administrator would need to grant you, or other people in your community, permission to run SQL queries on your data. That creates the potential for users to access data they shouldn't be able to, or directly modify or even delete data within your database!
By setting up views, you're predefining what data is acceptable to use, and what the limits a user can do with it are (no erasing the database!). This can allow your database administrator to set more restrictive server permissions.
Microsoft Power BI
Does your community use Power BI? Views make dashboard development in Power BI much easier.
Views can also be used in Tableau and other reporting tools too!
Available Datasets
Appointments Dataset
Data Wrangling Project
This simple view compiles information from the Appointments module into one convenient dataset. It includes all the relevant information about each Appointment, including client information, location information, notes, and who the appointment was with.
Bed List Dataset
Data Wrangling Project
This dataset is a fixed version of vw_BedList. It's not quite a bug, but the data in vw_BedList has been collected in a way that makes it difficult to use. This cleaner dataset simply lists all the beds, and if there's a client in them, it lists the client's stay information. No mess, no fuss. We also include an updated version of the official HIFIS Bed List report that references this new view.
Calls and Visits Log Dataset
Data Wrangling Project
This simple view compiles information from the Calls and Visits Log module into one convenient dataset. It includes all the relevant information about each log, including client information, subject and comments, geographic region, service provider, and more.
Clients Privacy Dataset
Data Wrangling Project
This dataset is an improved version of vw_ClientBasics that respects the privacy of clients. It includes names, gender, DOB, age, and citizenship, veteran, and indigenous statuses. In addition, it suppresses data for hidden clients and clients with declined - anonymous consent and indicates whether clients are hidden and/or declined.
Custom Tables (Client Vitals) Dataset
Data Wrangling Project
The view combines data from all Custom Tables attached to the Client Vitals screen in your community, making it much easier to utilize your custom data.
Clients Services Dataset
Data Wrangling Project
This dataset is a fixed version of vw_ClientsServices. For several years now there has been a bug present in vw_ClientsServices, which is referenced by many reports. The bug? This view doesn't include Group Activities, but it should. We've created an identical version of the view that also includes Group Activities.
Family Members Dataset
Data Wrangling Project
This view compiles information about the family memberships of clients in HIFIS. Each record represents a client's family membership, with applicable date ranges, along with their family role. It also pulls basic information about the family head and each family member, including age and gender.
Family Stays Dataset
Data Wrangling Project
The view combines data from the Admissions module and also the Families module. The following logic is used: when the client booked in, were they in a family? And if so, did any other members of the same family book in at the same shelter at the same time? If the answer to both of these questions is a yes, then the stay is interpreted as a family stay.
Goods & Services (Merged) Dataset
Data Wrangling Project
This view compiles and merges information from the Goods & Services module into one convenient dataset. It includes all the relevant information about each Goods & Services transaction, including client information, program information, and the user that entered the transaction.
Goods & Services (Services) Dataset
Data Wrangling Project
This simple view compiles information from the Goods & Services module into one convenient dataset. It includes all the relevant information about each Services transaction, including client information, program information, and the user that entered the transaction.
Goods & Services (Goods) Dataset
Data Wrangling Project
This simple view compiles information from the Goods & Services module into one convenient dataset. It includes all the relevant information about each Goods transaction, including client information, program information, and the user that entered the transaction.
Housing Placements Dataset
Data Wrangling Project
This view pulls in data from over 40 fields that are relevant to Housing Placements, so you have an easy way to reference everything you need, all in one place. It includes several useful measurements, including search duration and total subsidy, as well as listing caseworkers, landlords, housing type, address and geographic region, and more.
Incidents Dataset
Data Wrangling Project
This simple view compiles information from the Incidents module into one convenient dataset. It includes all the relevant information about each Incident, including involved clients and staff, emergency service involvement, notes and recording staff.
Program Entry Dataset
Data Wrangling Project
This view pulls data from all services where a Program has been added. For each Program, a client's Program Entry Date is determined as the earliest date that a client ever began receiving a service associated with that Program. In addition, the view calculates what the client's most recent Housing Status was at the time of Program Entry. Intended for HPP-funded communities.
Turnaways Dataset
Data Wrangling Project
This simple view compiles information from the Turnaways module into one convenient dataset. It compiles both identified and anonymous Turnaways together, making it easier to summarize. It includes all relevant information, including reasons for service and turnaway, requested by staff, and referral information.
VI-SPDAT v2 Dataset
Data Wrangling Project
The dataset pulls data from the VI-SPDAT (Adult) version 2 and organizes it into a single view, listing overall data about the VI-SPDAT (i.e. caseworker, date and time) along with answers to individual questions stored in columns. It also calculates the number of VI-SPDATs the client has completed, and retrieves the trimorbidity score along with other section scores.