A “View” into a new Query future

Attention Query Writers and Data Analysts:

Can you relate to the struggle of using Query to find every sub-account a membership might have? Or a list of members with only the basic share? The challenge lies with the fact that product records live across 6 different tables – This is a good data practice – But not exactly easy to combine for report queries. It can certainly be done, just not easily.

Which is why I am very very very excited to see that CU*Answers has recently released a helpful new database View with every single account any member has open today.

MBRNACCTSQ – Membership View Active Accounts

You’ll find this gem in your FILExx library (xx = your CU ID) of CU*BASE®. It’s not a comprehensive set of every data point found in the 6 active account tables. Naturally, not every data point applies to every product type, but everything consistent across the records was included plus a few extra.

Views themselves have already been in play behind the scenes for quite awhile here, but until this point only as a solution internally for various programs and APIs. Now, we are beginning to develop and launch Views for data analysts in hopes that these solutions will assist with creating faster and friendlier analytics with Query.

Fancy Tricks with MBRNACCTSQ

Sometimes a tool or resource is developed here with the intent of opening new doors to insights or function that you never had before. Other times a resource is intended to make something that exists faster, better, easier. This is a faster, better, easier solution. Here’s just two ways I recently used this View in a project:

Full Member Name

Normally membership names are split into 3 unique fields – First Name, Middle Initial, and Last Name. That is a good data practice. But combining them all for a final report can get messy with currently available options…like this:

Business Memberships

What You Get ANDSCAPING, LLC.      CALL ME FIRST
What You Want CALL ME FIRST LANDSCAPING, LLC.

Individual Memberships

What You Get ALICIA                 M  TESTMEMBER
What You Want ALICIA M TESTMEMBER

There are currently a few round-about ways to achieve a proper-looking combination of the name columns, but none as simple or convenient. Now, only join any table with account numbers to the MBRNACCTSQ View and you instantly have the full membership name for your report, spaced correctly, and in the right order!

Membership “Freshness Date”

Quickly find the last transactional activity across an entire membership account*. Here’s how to do this in Query with this View:

  1. SORT by Account Base
  2. BREAK by Account Base
  3. Set a MAX total on the Last Transaction Date column
    1. Via the “Create Totals and Sub-Totals” option
  4. Set output to SUMMARY
    1. If output is also set to a database file, the resulting file will include a brand-new queryable column of that value for the membership’s most recent transaction date across any suffix.

*This trick does not account for any offline trial balance (OTB) products since OTB products are not included in this View.

 

I look forward to watching our community of data analysts find new uses for the data resources available at your credit union. Keep an eye on CU*BASE® core release summaries to catch other Views as they go live (Read the Database Changes version)

Or watch the blog, I do plan to spotlight key launches on Views and other analytics or warehousing solutions.

Share your thoughts

3 Replies to “A “View” into a new Query future”

  1. I want to learn more about the ideas related to “report writers” and data base presentation and query tools. I know you are studying how to broaden the options for analyst to look at, present, and communicate what they find in data bases. I hope you will keep telling us what you find, and how to evaluate what tools most have in common and what they might earn their props from.

  2. Excellent stuff in this blog, Annalyn. It’s obvious that you’ve learned the ins and outs of our databases while doing the same real, practical things that our credit unions are doing (or should doing) every day.

Leave a Reply

Your email address will not be published. Required fields are marked *