NU and Kellogg PL/SQL definitions

Contains SQL and PL/SQL code for various Kellogg data definitions and best practices.

Data hierarchy

Concept: lower level tables should be defined based on SF objects, then joined/transformed into higher level definitions. Data packages should have clear hierarchical dependencies, and a single responsibility.

Naming conventions

id_type_description

• dw_pkg_: package/view accessing SF objects or DW tables; should be very fast
• ksm_pkg_: KSM specific package/view dependent on other packages/views
• mv_: refreshing materialized views, fast indexed versions of dw_pkg and ksm_pkg definitions
• tbl_: tables and non-refreshing materialized views; should be recompiled to update
• v_: general views
• tableau_: Tableau views

Examples

• ksm_pkg_utility wraps various utility functions, such as to_number_from_dollar()
• dw_pkg_base contains the cursor/logic to format base objects/tables including constituent, organization, etc.

Materialized view refresh

To facilitate pulling the important definitions contained in the ksm_pkg packages, materialized views are scheduled to refresh daily. See ksm_mv_scheduler.sql for the set of scheduled views.

If new materialized views are scheduled, the specs should be added to the above file, and last refresh and ETL times added to the view ksm_mv_refresh_stats.sql.

Important: respect dependencies! For example, mv_ksm_transactions is scheduled 10 minutes after mv_entity -- see their respective package descriptions.

Important views

Universal

• mv_entity = merged constituent and org tables, with consistent household definition (org ultimate parent is the hh primary). See c_entity
• mv_entity_ksm_degrees = KSM alumni definition, plus concatenated degrees. See c_entity_degrees_concat
• mv_entity_contact_info = assorted contact information in wide format (one row per entity), respecting special handling. See c_contact_info

Giving

• mv_ksm_designation = KSM gift designations, including cash and campaign categories. See c_ksm_designation
• mv_ksm_transactions = KSM hard and soft credit to all KSM designations. See c_ksm_transactions
• v_ksm_gifts_cash = use instead of mv_ksm_transactions to pre-filter out pledges and for calculated cash_countable_amount field
• v_ksm_gifts_ngc = use instead of mv_ksm_transactions to pre-filter out pledge payments

Prospect

• mv_assignments = all active staff assignments in wide format (one row per entity). See c_assignment_summary
• mv_contact_reports = consolidated contact report views in long format, disaggregating staff credit across multiple rows. See c_contact_reports
• mv_ksm_models = KSM model scores in wide format (one row per entity)