View Tables

WHAT IS A VIEW

If you write queries, you will save time with View tables! If you don’t write queries but you’d like to, Views will make it so much easier to begin. More on this later, but essentially a View table will combine, organize, or reformat the raw data stored in CU*BASE into a much friendlier and simple structure for use in Query!

A View is actually an alternative way of representing data stored in the database tables. They aren’t actual tables (surprise!) and they don’t have any permanent storage. Data still lives and is maintained in the original “base table”. But even though a View is not technically a table, it will work just like one when you use it in Report Builder (Query) – just put in the View name and the location is still your FILExx library. (don’t know what FILExx is – click here)

Read more from Annalyn about suggestions and insights with Views:

A “View” into a new Query future

What is a View?

 

HOW ARE VIEWS HELPFUL?

Views are helpful for multiple reasons and we build them in an effort to assist with simplifying our database for analysts. Remember, CU*BASE and its data and programs are above-all designed to best support the primary function of the core – data processing. Analysts like you and I often need to re-interpret or re-format data to work well with our projects to extract the data for analysis.

Some of the ways Views can specifically help include their abilities to:

  • Combine data from different base tables – Especially those combinations that are done frequently by many CUs and for many reasons
    • Combining MEMBER5 and MEMBER6 for loan reports)
  • Summarize data, or hide sensitive data
  • “Clean up” a table by filtering out certain portions of the data that are generally irrelevant to analysis.
  • Apply optimal formatting to fields
    • Handling the older data values by turning them into an ISO-format equivalent)
  • Add additional indicators or labels for some codes that are otherwise obscure without a deeper knowledge of configurations
  • Reorganize or filter tables that are particularly confusing, or that store many unrelated data elements

Ultimately, all these functions of Views will make data easier to access and decipher for analysts.

 

VIEWS YOU CAN USE TODAY

Here’s a brief list of some Views you can find in CU*BASE today to assist your data mining and analytics projects. Check the Database Search Assistant tool in CU*BASE for the most current list of available Views:

NameDescriptionUse Instead Of
MBRNACCTSQMEMBERx open accounts for active/open membershipsMEMBER1-6
MASTER
MBRNCLOSQACHISTx closed accounts for closed memberships ACHIST1-6
MSHIST
MBRACLOSAQACHISTx closed accounts for active/open memberships ACHIST1-6
MASTER
ANRBALSETQANR Balance ConfigurationsSYSCTL
ANRLVLSETQANR Level ConfigurationsSYSCTL
DBTCRDRNDQDebit Card Roundup ConfigurationsSYSCTL
DVACTSETQDIV Account Control ConfigurationsSYSCTL
MBROBSDEMQOnline Banking DemographicsPCMBRCFG
OBMBRSECIN
MBROBSECQOnline Banking SecurityOBMBRSECIN
OBMBRCHQST
MBRXOTBQOTB Accounts for active/open membershipsOTBMST2-4
MASTER
CCACTSETQCredit Card Control ConfigurationsSYSCTL
CCLOANSETQCredit Card Category DefinitionSYSCTL
CDACTSETQCD Account Control ConfigurationsSYSCTL
CDRATERNGQCD Dividend Rate ConfigurationsSYSCTL
LOANSETQLoan Account Control ConfigurationsSYSCTL
MBRCUMBALQMember Cumulative (Aggregate) BalancesMBRBAL
MBRBAL
MASTER
COLCODESQCollateral Code ConfigurationsSYSCTL
COSIGNERSQCo-signers Name and AddressADSL
MEMBER5-6
COLLNASGNQCollateral Loan AssignmentsCLML
MEMBER5-6
TRANLNESCQLoan and Escrow Payments (current month)TRANS1-2
MEMBER4-6
HTRNLNESCQLoan and Escrow Payments (history only)HTRANS1-2
MEMBER4-6
TRANSEXTDQExtended Transaction DescriptionsTRDESC
MBRLNSUMQCombined Loan Summary of active/open loansMEMBER5-6

VIEWS HAVE A FEW RULES

A View must be located in the same location (library) as all the base tables from which it’s derived. This means we won’t making a View that contains all transaction history from current month (in FILExx) and all the historical months (in FILExxE).

Why? Crossing over libraries causes issues on the back-end that hinder disaster recover and data restore processes.

 

Views won’t automatically pick up new columns that might be added to its base tables in future CU*BASE software releases. They require maintenance to recognize the new columns. We do our best to do the maintenance proactively as we apply changes to base tables, but if you notice one we missed, please reach out!

 

View tables are consistently named a special way – The last letter of a View will be the letter ‘Q’.

Why? This is the way we chose to differentiate Views from other table names in the system. It helps you to identify them quickly and it helps us to identify them from our internal programming Views which end in a different letter.

Updated
September 17, 2019

Hit the Ground Running in 2020 with Analytics Booth!

Hit the Ground Running in 2020 with Analytics Booth!

Elevate your operations in 2020 and impress your board with enhanced reporting and trending metrics powered by Analytics Booth! Enjoy hundreds of operational trend lines and organizational insights accessible anywhere you have an internet connection.  Stop waiting for the end of the month to review your performance – enjoy daily insights at 24 hour intervals,… Read more »

Nov 13, 2019

Join Us for Asterisk Intelligence Week!

Join Us for Asterisk Intelligence Week!

Join the Asterisk Intelligence team next month for several educational events.  On Monday, December 2nd, we will be hosting on-site training for Dashboards Up Close and Personal at our Grand Rapids headquarters.  Then, on Thursday, December 5th, we will be hosting a series of webinars on Analytics Booth strategies. Our Report Builder courses are already… Read more »

Nov 13, 2019

Jump into a Dashboard Dive: Portfolio Analysis Dashboards

Jump into a Dashboard Dive: Portfolio Analysis Dashboards

Join the Asterisk Intelligence Team for a Dashboard Dive! Join the Asterisk Intelligence Team as they review tools #595 and #596, the Loan and Savings Portfolio Analysis Dashboards.  Reviewing balance changes and member participation in each of your configured share or loan products has never been easier!  Use these dashboards to review your portfolio positions… Read more »

Oct 28, 2019

Jump into a Dashboard Dive: Collections Dashboard

Jump into a Dashboard Dive: Collections Dashboard

Join the Asterisk Intelligence Team for a Dashboard Dive! Join the Asterisk Intelligence Team as they review tool #229, the Collections Summary Dashboard.  Accurate data is critical to managing your collections process.  Learn how to use this dashboard to generate insights into your delinquent loans, and how to leverage this information to improve your collections… Read more »

Oct 17, 2019

Don’t Miss This Week’s Dashboard Dive: Cross Sales Analysis Dashboard

Don’t Miss This Week’s Dashboard Dive: Cross Sales Analysis Dashboard

Join the Asterisk Intelligence Team for a Dashboard Dive! Join the Asterisk Intelligence Team as they review tool #320, the Cross Sales Analysis Dashboard.  Have you been considering incentivizing your staff for their successful cross sales?  Or looking for a way to run some ‘what if’ scenarios to estimate the costs of different incentive programs? … Read more »

Oct 14, 2019

Jump into a Dashboard Dive: Cross Sales Analysis Dashboard

Jump into a Dashboard Dive: Cross Sales Analysis Dashboard

Join the Asterisk Intelligence Team for a Dashboard Dive! Join the Asterisk Intelligence Team as they review tool #320, the Cross Sales Analysis Dashboard.  Have you been considering incentivizing your staff for their successful cross sales?  Or looking for a way to run some ‘what if’ scenarios to estimate the costs of different incentive programs? … Read more »

Oct 10, 2019

Asterisk Intelligence publishes book of simple data projects for analysts

Asterisk Intelligence, the data analytics and data warehousing team at CU*Answers, recently published a free book of their Data Fact projects executed in 2019. Data Fact projects are quick and simple data points researched from a credit union’s available data and delivered to them via email from Asterisk Intelligence (AI). The team selects a different […]

Sep 30, 2019

Asterisk Intelligence publishes book of simple data projects for analysts

Asterisk Intelligence, the data analytics and data warehousing team at CU*Answers, recently published a free book of their Data Fact projects executed in 2019. Data Fact projects are quick and simple data points researched from a credit union’s available data and delivered to them via email from Asterisk Intelligence (AI). The team selects a different […]

Sep 30, 2019

Don’t Miss This Week’s Dashboard Dive: Tiered Services Monthly Comparison

Don’t Miss This Week’s Dashboard Dive: Tiered Services Monthly Comparison

Join the Asterisk Intelligence Team for a Dashboard Dive! Join the Asterisk Intelligence Team as they review tool #856, the Tiered Services Monthly Comparison Dashboard.  A tiered services rewards program is an excellent way to incentivize your members and encourage increased product and service participation.  Regardless of whether or not you currently offer a tiered… Read more »

Sep 30, 2019

Asterisk Intelligence expands the audience for data strategies

Throughout 2019, Asterisk Intelligence, the data analytics and data warehousing team at CU*Answers, has worked diligently to expose operational data, insights, and data driven strategies to larger and more diverse audiences of credit union stakeholders. Recent analytical enhancements have integrated Analytics Booth operational Powerlines and Web Dashboards with key CU*BASE® features giving everyday users easy […]

Sep 26, 2019