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
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:
| Name | Description | Use Instead Of |
|---|---|---|
| MBRNACCTSQ | MEMBERx open accounts for active/open memberships | MEMBER1-6 MASTER |
| MBRNCLACQ | ACHISTx closed accounts for closed memberships | ACHIST1-6 MSHIST |
| MBRACLOSAQ | ACHISTx closed accounts for active/open memberships | ACHIST1-6 MASTER |
| ANRBALSETQ | ANR Balance Configurations | SYSCTL |
| ANRLVLSETQ | ANR Level Configurations | SYSCTL |
| DBTCRDRNDQ | Debit Card Roundup Configurations | SYSCTL |
| DVACTSETQ | DIV Account Control Configurations | SYSCTL |
| MBROBSDEMQ | Online Banking Demographics | PCMBRCFG OBMBRSECIN |
| MBROBSECQ | Online Banking Security | OBMBRSECIN OBMBRCHQST |
| MBRXOTBQ | OTB Accounts for active/open memberships | OTBMST2-4 MASTER |
| CCACTSETQ | Credit Card Control Configurations | SYSCTL |
| CCLOANSETQ | Credit Card Category Definition | SYSCTL |
| CDACTSETQ | CD Account Control Configurations | SYSCTL |
| CDRATERNGQ | CD Dividend Rate Configurations | SYSCTL |
| LOANSETQ | Loan Account Control Configurations | SYSCTL |
| MBRCUMBALQ | Member Cumulative (Aggregate) Balances | MBRBAL MBRBAL MASTER |
| COLCODESQ | Collateral Code Configurations | SYSCTL |
| COSIGNERSQ | Co-signers Name and Address | ADSL MEMBER5-6 |
| COLLNASGNQ | Collateral Loan Assignments | CLML MEMBER5-6 |
| TRANLNESCQ | Loan and Escrow Payments (current month) | TRANS1-2 MEMBER4-6 |
| HTRNLNESCQ | Loan and Escrow Payments (history only) | HTRANS1-2 MEMBER4-6 |
| TRANSEXTDQ | Extended Transaction Descriptions | TRDESC |
| MBRLNSUMQ | Combined Loan Summary of active/open loans | MEMBER5-6 |
| MBRALLACTQ | MEMBERx and OTBx combined open accounts for active/open memberships | MEMBER1-6 OTBMST2-4 |
| MBRXOTBQ | OTBx open offline accounts for active/open memberships | OTBMST2 OTBMST3 OTBMST4 |
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.
