Nightly Tables

These tables are created with SPROCs as part of the nightly scripts in the "db1" Local Report Server database "ReportALTALocal". SPROCs are baked into the database, but stored in BitBucket for backup/changes. Once done, they are copied to the "db1" database "DataWarehouse" which is where they are actually used for ALTA web sites. Nightly Steps.xlsx

• Advocacy Snapshot (ALTA_AdvocacySnapshot)

  • Essentially the "Advocacy Universe" of all inds and orgs with TAN and TIPAC.

• ATG & ITG Information (ALTA_ATGITG)

  • Records with ATG or ITG data.

• Board Roster (ALTA_BoardList)

  • BOD directory

• Committee Rosters (ALTA_CommitteeMembers)

  • Rosters for all current committee members.

• Committee Metrics (ALTA_CommitteeMemberMetrics)

  • Last two years of data on each member for metrics reporting in Thaddeus.

• Current Non Compliant Orgs (ALTA_CurrentNonComp)

  • In Universe, not compliant for M&L

• Current Year M&L (ALTA_CurrentYear)

  • Full details of all current

• Member Directory Area Served (ALTA_DirectoryAreaServed)

  • The details of orgs' choices of state/county served.

• Member Directory Orgs (ALTA_DirectoryMembers)

  • The organization details for those choosing to be in the directory

• Member Directory Inds (ALTA_DirectoryInd)

  • The individual details for those chosen to be listed with their org in the directory

• Elite Members (ALTA_EliteMembers)

  • Roster for Elite Providers directory.

• Engagement Snapshot (ALTA_EngagementSnapshot)

  • Monthly snapshot of all but "Never" level for metrics reporting.

• Engagement Level Counts (ALTA_EngagementLevelCounts)

  • Monthly count of individuals in each level.

• Events Calendar (ALTA_EventsCalendar)

  • For the re:Members (formerly Impexium) upcoming events that show on alta.org/events which is combined with the Intranet entries that haven't been put into re:Members (formerly Impexium) yet.

• Exhibit Booths (ALTA_ExhibitBooths)

  • All exhibit booth data, combod from actual purchases and sponsorship packages.
  • Used for reports and Meetings Web Site.

• Courses Export (ALTA_ExportCourses)

  • For Thaddeus reporting of course purchases

• Products Export (ALTA_ExportProds)

  • For Thaddeus reporting of merchandise purchases

• Subscriptions Export (ALTA_ExportSubs)

  • For Thaddeus reporting of subscription purchases

• HOP Leaders (ALTA_HOPLeaderList)

  • For the HOP directory and access on the web site.

• License Kits (ALTA_KitsLicense)

  • For Thaddeus exports for sending License Kits

• Member Kids (ALTA_KitsMember)

  • For Thaddeus exports for sending Member Kits

• Marketplace (ALTA_Marketplace)

  • For web site Marketplace directory

• Nightly Log (ALTA_NightlyLog)

  • Tracking steps of the nightly script process on the re:Members (formerly Impexium) side of the equation. (Another log in DW is ALTA's side)

• NTP List (ALTA_NTPList)

  • For web site NTP directory

• Organization Details (ALTA_OrgDetails)

  • "All" organizations and basic information, beyond what's in our Compliance and Member types of tables.

• Parsed Parents (ALTA_ParsedParents)

  • Specifically parsing out the re:Members (formerly Impexium) tree structure for org parent/grand/great/great great, helps get to Uber

• Partners (ALTA_Partners)

  • All years, for output on Meetings site, etc.

• Past Presidents (ALTA_PastPresidents)

  • For PP directory

• Primary Contacts (ALTA_PrimaryContacts)

  • Parsed out list of all orgs' primary contact for quick checking for web site access, etc.

• Registry Alt Mailing / Children / Members / PC / REA / RLC / Staff (ALTA_RegXXXX)

  • All information (often duplicated in other tables) but in the data structure that the RMS uses (GoMembers field names, etc.) so that the RMS recode would be simpler.

• Renewals BadBoys / FormerNonAgent / LapsedPFL / Members / NonComp / PFL / Welcome / WelcomeAbstractors / Welcome Industry (ALTA_RenewalsXXXX)

  • For Thaddeus exports for M&L for renewal emails/mailings

• Revenue (ALTA_Revenue)

  • Past 5 years of groups of products' invoice and paid revenue for Thaddeus reporting

• Secondary Contacts (ALTA_SecondaryContacts)

  • Parsed out list of all orgs' secondary contacts for quick checking for web site access, etc.

• Sponsors (ALTA_Sponsors)

  • All years, for output on Meetings site, etc.

• Staff Roster (ALTA_StaffList)

  • Staff directory

• TIAC (ALTA_TIAC)

  • All records with TIAC info

• TIPAC Metrics (ALTA_TIPACMetrics)

  • Past two years of Member/PFL/State data for each contributor for Thaddeus reporting

• TIRS Access (ALTA_TIRSAccess)

  • Parsed out access for TIRS since it applies to the entire tree below the purchase, so faster to check for members getting to TIRS.

• Uber Compliance (ALTA_UberCompliance)

  • CRITICAL table with all Uber parents and their PY/CY/NY information for M&L.

• Uber Parents (ALTA_UberParents)

  • All ind and org IDs with their Uber Parent for quick access for web site access

• Underwriters (ALTA_Underwriters)

  • All active UWs, mostly for Registry list.

• Universe (ALTA_Universe)

  • Current total and state by state counts.

• Upcoming Meeting Registrations (ALTA_UpcomingMeetingReg)

  • Attendees of past 4 years and future non-webinars for Thaddeus.

• Upcoming Webinar Registrations (ALTA_UpcomingWebinarReg)

  • Attendees of past 2 years and future non-webinars for Thaddeus.

• Webinars Archive (ALTA_WebinarsArchive)

  • Data for past webinars listing on web site.

• Webinars Upcoming (ALTA_WebinarsUpcoming)

  • Data for upcoming webinars listing on web site.