[Sharing] Actuarial - DCS & Alteryx

[caltang]

I've done some work with actuarial transformation of processes from Excel, Access, Data Conversion System (DCS), and I've been learning about Prophet via Excel Macros. I hope to share some of what I've seen, and I think it might be useful to some actuaries looking for this information. Some actuaries told me that my SEO is pretty strong when you look for actuarial and alteryx - most of my posts pop up. So I hope to consolidate information and help make this post as the idea hub / some tips. 

Point #1: Large Datasets

One thing I noticed working with Actuarial related data is that the fact data is often huge and is in .txt files. One way to resolve this is to use .yxdb, as it is native to Alteryx and can compress your data + save you space on your computer. It's also faster for processing, and you can read about it here. 

If your Actuarial team all share similar inputs, then it's worth making it a YXDB converter for your team and put it on the Server. If you don't have a Server, then what you can do is that most IT teams drop these files to users and you can get a point person to run the converter for your dataset so the rest of your team can use it for their own reporting. If they have a DB that you call from, that's even better. 

Point #2: Access SQL Conversion

Access is a great DB and if used correctly, is a powerful tool in itself. But it's usually pretty slow for most actuaries because the data has grown so huge and causes your Access to lag out for hours. Replicating Access requires you to know some SQL, but most of it is quite straightforward. I've attached a cheat sheet from DataCamp which I think will be helpful as well. 

If you're doing transformation and you're short on time, try to replicate the process as fast as possible. But if you're given the luxury of time, then I would suggest for you to do a code review and try to understand what is required for inputs, outputs, and middle steps to get from A to B. Legacy code is not optimized, so a lot of room to transform. 

Point #3: Navigating DCS

DCS has its own library to understand how it works. It's not a direct replication of work since DCS requires some intervention from the end user for them to key in certain values. So when you transform from a DCS process, it's important to have an actuary who can tell you the steps as well. Domain knowledge plays a big role here.

Now, in terms of transformation itself, you will notice that most DCS is written with poor code practices - and the language is kinda like Visual Basic. So when I say poor code practices, I mean things like:

1. No proper indentation - you don't know where it really starts and ends.
2. Sometimes the codes are redundant - they will have a statement below the 200 lines of code nullifying the output of the previous 200 lines.
3. Most of it is hard coded to suit a product or certain code that is specific to companies / dates / half baked measures. 

So it is a real challenge to get it sorted. I highly recommend freeing up at least 2 weeks with an Actuary to get the process studied and built from scratch in Alteryx. Try to build in phases and then link them up as macros or chain apps to accelerate the process and keep it clean for end users. 

I'll write more on this topic when I have more time, but for now these are three of basic stuff I've found to be useful to actuaries.