Mastering Data Quality controls in SAS ALM on Viya

[#1]

Do you ever get that scary feeling when you find out your well-thought-out ALM analysis is built on questionable data? Totally been there. It is like putting up a beautiful house on quicksand, no matter how great your design is, things are definitely going to get shaky.

The purpose of this post is to explain some of the data quality features of SAS ALM on Viya and also give you some tips on how to set up strong data quality controls in your organisation.

Whether you're an advanced ALM user or just starting out, you will see how you can take advantage of these cool tools to keep your ALM analysis reliable.

The Data Quality Challenge

Based on my experience, banks spend about 80% of their ALM implementation time wrestling with data issues. Yet surprisingly, many organizations still treat data quality as an afterthought. It is like showing up to a Michelin-star restaurant with ingredients from a gas station, you can't expect miracles.

But here's the good news: SAS ALM on Viya comes with a powerfull data quality framework that's like having a team of obsessive-compulsive data inspectors working for you 24/7.

The Data Quality Dashboard: Your Command Center

Remember playing "Where's Waldo?" as a kid? Finding data issues without a proper dashboard is just as frustrating. SAS ALM's data quality dashboard gives you a birds-eye view of your portfolio's health. It's like having X-ray vision for your data!

Key features of the Visual Analytics DQ dashboard include:

• Real-time data quality metrics
• Trend analysis
• Drill-down capabilities
• Automated alerts
• Multi dimensional slicing and dicing
• and much more

Out-of-Box Rules will get you started

The solution ships with an extended set of data quality rules that can catch common issues quite fast.

These rules cover:

• Missing or invalid values
• Date consistency checks
• Balance validations
• Currency verifications
• Relationship rules

Creating Your Own Rules: Because One Size Doesn't Fit All

When the standard rules are not enough, you can go ahead and create your very own, because every bank has unique data quirks. That's why SAS ALM lets you create custom DQ rules from basic to extremely advanced.

Here's a simple example:

if balance < 0 and product_type = 'ASSET' then

flag_error("Negative balance detected for asset product");

You can go as advanced as you like because the user interface allows you to be very creative and flexible, basically the sky is the limit, or the data in this case.

Here is an example of a more elaborate rule built in the UI:

Tips from the Field and Best Practices

I will share a story that shows these tips in action. A few years ago, I was working with a large European bank implementing SAS ALM. Their initial approach was to create hundreds of data quality rules right from the start. That didn't go well in the long run. Here's what we learned and how you can avoid the same pitfalls.

1. Start Small but Think Big

Think of building your data quality framework like cooking a complex meal. You don't start by juggling five pots on the stove while baking a souffle. Instead, you begin with the basics and build up.

Here's what worked for us: We started with just three critical rules for their mortgage portfolio:

• Validate that interest rates fall within historical ranges
• Ensure remaining term is less than original term
• Check that payment frequency matches product specifications

Once these were working smoothly, we expanded to more complex rules and additional portfolios. The key was having a clear roadmap of where we wanted to go while taking manageable steps to get there.

2. Document Your Rules (Your Future Self Will Thank You)

I once had to debug a rule that someone had helpfully named "Important_Check_V7_Final_FINAL_Use_This_One".  Does it sounds familiar?

Here's a better way to document a rule:

/* Rule ID: DQ_MORT_IR_001

Purpose: Validates mortgage interest rates against historical ranges

Created: 2024-01-15 by Sarah

Updated: 2024-02-01 - Added exception for promotional rates

Logic:

- Check if interest rate is between 0.1% and 15%

- Flag promotional rates (<1%) for review

- Exclude government-subsidized products

Dependencies:

- Requires PRODUCT_TYPE column

- Uses HISTORICAL_RATES reference table

*/

3. Test Thoroughly (Preferably Not in Production)

The bank discovered their data quality rules weren't catching negative prepayment rates... during a regulatory audit!

To avoid such excitement, we developed a testing approach I call the "Break it Before You Take it" method:

1. Create a small test portfolio with known issues
2. Run your rules against it
3. Verify all issues are caught
4. Then deliberately introduce new errors to ensure they're caught as well

In SAS ALM, you can use the test environment to run these checks without affecting your production cycles. For example:

# Create test cases

test_portfolio = {

'good_record': {'rate': 3.5, 'term': 360, 'remaining': 240},

'bad_rate': {'rate': -1, 'term': 360, 'remaining': 240},

'impossible_term': {'rate': 3.5, 'term': 360, 'remaining': 361}}

# Run through data quality rules

results = run_dq_rules(test_portfolio)

# Verify results match expectations assert results['bad_rate'].has_error('NEGATIVE_RATE') assert results['impossible_term'].has_error('TERM_MISMATCH')

4. Monitor and Adjust

Data quality isn't a set it and forget it process. It's more like taking care of your garden, it needs regular attention. In SAS ALM, you can set up monitoring dashboards to track:

• Number of issues detected over time
• Time to resolution
• Most frequent error types
• Impact on downstream processes
• And much more

The bank I worked with noticed a spike in data quality issues every quarter. After investigation, we discovered it coincided with their core banking system updates. This led to creating specific rules for post-update validation, preventing downstream issues in their ALM analysis.

Pro Tip: Set up automated notifications in SAS ALM to alert relevant teams when specific patterns emerge. For instance, if more than 5% of your portfolio fails validation, you might want to pause the cycle and investigate before proceeding further.

Conclusion is that Quality In = Confidence Out

Data quality doesn't have to be difficult. With SAS ALM on Viya, you have the tools to transform data quality from a necessary evil into a competitive advantage. Think of it as your portfolio immune system that is working silently to keep everything healthy.

Are you ready to take your ALM data quality to the next level? SAS ALM on Viya might be just what you need. And unlike my attempts at home baking sourdough bread, this actually works!

Find more articles from SAS Global Enablement and Learning here.