《学习可配置连接.ppsx》由会员分享,可在线阅读,更多相关《学习可配置连接.ppsx(53页珍藏版)》请在三个皮匠报告上搜索。
1、Leaning into Configurable JoinsAdam GrossWashington and Lee UniversityAlisa LoughlinWashington and Lee UniversityElodie HardtUniversity of RichmondTom NicholasUniversity of RichmondLeaning into Configurable JoinsAdam Gross,director of admissions operationsAlisa Loughlin,assistant director of admissi
2、ons information systemsWashington and Lee UniversityFounded 1749Rural,Highly Selective Liberal ArtsCommon App,QuestBridge,Coalition for College member1,857 Undergraduate Enrollment49 States,41 CountriesTop producer of Fulbright students in the U.S.W&L prepares students to meet the worlds challenges
3、as ethical leaders,visionary thinkers and compassionate citizens.Washington and Lee UniversityLive in Slate in 2012Rural,Highly Selective Liberal ArtsCommon App,QuestBridge,Coalition for College member1,857 Undergraduate Enrollment49 States,41 CountriesTop producer of Fulbright students in the U.S.W
4、&L prepares students to meet the worlds challenges as ethical leaders,visionary thinkers and compassionate citizens.What is a join and why is it configurable now?Slate is a relational database,information is stored in many different tablesLocal bases did the work for us previouslyIsnt it all just Sl
5、ate?Imagine a Food Court,all the restaurants(Slate tables)are in the same place in the Food Court(Slate),but they are separate entities.You can get a Big Mac and a Whopper bring them back to your seat(your new CJ query),but it is going to take a couple of steps.In other words,you can get your Whoppe
6、r and your Big Mac at the Food Court,but it is going to take a couple of steps by going to both McDonalds and Burger King.Food CourtMcDonaldsBurger KingMcDonalds Big MacBurger King WhopperWhat do you mean different tables?Choosing your baseCategoryRecordsRelatedSystemMost common choiceAssociated thi
7、ngs,not dataset recordsStuff in the database tabBaseExamplesPersonSchoolPromptApplicationTestWorld-US Zip DataOrganizationsForm ResponsesRound“What do I want to see one row per?”“What type of record or value am I hoping to count or aggregate?”Admins determine Base access and Join accessInsert decade
8、-old Meghan Trainor reference here.Carte blanche access can be very overwhelming to end users.Person:one row per human Organizations:one row per institution School:one row per persons record of attending a school Application:one row per application Form Response:one row per submission of a particula
9、r formAfter choosing your base,you then join other tables to add the data from those other objects you need,using the joins to tell Slate how to prioritize selecting which row to use.Choosing your baseInsert decade-old Meghan Trainor reference here.Use the STL“Prospects and Applicants”base before?Pi
10、ck the“Person”base and add the“Application by Rank”join(rank=1)Where do I put my Joins?Top Level Join:When you need to get multiple filters/exports from one associated record.Export or Filter Subquery:When you need one filter/export from one or more than one associated record.Querying in Configurabl
11、e Joins bases initially will feel much slower than local base querying.Create TEMPLATES and LIBRARIES to do the repetitive work for you!Creating the same special join or subquery often?LIBRARY IT!Query Libraries“Tired of re-building the same exports,filters,and joins within your queries?These query
12、elements can now be created centrally within a database-specific Query Library.The Query Library packages institutionally-desired items for users to access throughout the database.This type of packaging allows for all of the appropriate joins,subqueries,calculations,ranks,and sorts to be applied at
13、once.”-Knowledge BaseBest Practices for Query LibrariesEstablish a Naming ConventionGrouping in a sensible wayMake a plan to update and validate regularlyShare them!Edit Permissions and add something otherwise not even Security Admins can see your hard work.W&Ls application of these practices:1.Star
14、t with a library with the same name as the base it uses2.Group with like purposes(similar subqueries,etc.)3.Check libraries for function annually during Cycle Prep.Notate this in the Notes section of each libraryTechnolutions probably did something new since the last time you checked it.This should
15、be your first libraryPerson-scoped tags:Fairly simple subquerySaves a lot of repetitionExists/Not Exists as a stand-in for In/Not In of oldTags are supposed to be easy to get to anywayW&L Library ExamplesSeparated Merit Scholarship Statuses from the Application library to keep the query screen sleek
16、.All exports in this library are subqueries of Application Activities with minor differences on the application activity subquery join or filter.Merit Scholarship StatusesSeparated from Person library for more granular permissions.Library joins give users permission to join to a base they may not ot
17、herwise have access to.Why join rather than subquery exports?Get more than one data point from each test(status,score,date for each)W&L Library ExamplesACT and SAT TestingThese library joins have the Optional:NoFilter to prevent users from seeing something they oughtntWhat libraries should I make wh
18、ile hanging out at my gate tomorrow?Addresses Mailing,then permanent Permanent,then mailing Join to addresses,use type,then rank,as sortsCritical Funnel Points Admitted DepositedComplex STL/Local filters/exports Has Mobile Phone with North American Format Application Period ActiveLegacy Use CasesOth
19、er W&L Query Libraries:Applicants in SchoolOrganizations-scoped,counts numbers of prospects&applicants with a school record linked to an orgGeodemographicFaster way to get multiple filters e.g.US Citizens AbroadWorkflows“Forget leaning into configurable joins,lets dive in head first”Upgrade your rea
20、ding experienceW&L shifted from the legacy application reader into Workflows for Application Year 2023Pros:All-in one editor environmentUtilize configurable joins(including libraries)in the readerLanding Page is a report,not widgets.Customizable for each workflowCJs provide consistency with other da
21、tabase functions that require them nowSearch bar when reading an app now searches within the PDFsControl over the reader lookup windowCons:No more bin flow view on the record pageBin history requires SQL to get toLost persistence of views/filters when navigating the Search/Browse/Queue tabs(have to
22、open and save the customize view screen each time)Lost ability to use multiple browser sessions to have different views concurrentlyApproaching the upgrade Use the knowledge base articles!Our recommendation:try to directly re-create bins,tabs,materials,and rules as they exist in your legacy reader.E
23、valuate which reader queries are needed.You will be re-creating these in Views.Just learning CJs yourself?This is a great exercise as you can see side-by-side if you have something correct in the moment.Finally!You can use the whole reader home screen,with all of the cool report tricksBest Practices
24、 for Views Consider different views for different users working in the workflow.Set permissions based off of the intended user(s)of a particular view.Avoid granting modify permissions.Add,then inactivate,common filters within views,even default view,to give views multiple uses with as few clicks as
25、possible.If using display widths,use percentages.W&Ls application of these practices:Add and inactivate round key,tags,interest score,queue filters to default view.Give the Default view the same permission as the access permission of the whole workflow.You need to grant permissions to every view reg
26、ardless of if everyone should have it.Final ThoughtsGet your camera ready,heres some cautionary talesTakeaways for TrainingDay 1:Uncheck the Extended Filters(or Extended Exports)box.This minimizes the risk of unintended poorly-constructed joinsAvoid instructing users to create their own unshared que
27、ry library.This minimizes bad query writing and helps everyone stay on the same pageLearn the difference between Not IN and Not EXISTSNot IN=has a different value,Not EXISTS=has no valueIf you are making something more than once,put it in a libraryIt really does help the slowing down problem of CJs“
28、Person”or“Person by Population”base?Person by Population only allows users to query on records in populations that are granted to them.Quick Takeaways Start small with Base and Join access permissions with end users.Easier to grant additional permissions than take them away Recents Tab in workflows
29、has a new behavior.Shows a larger amount of recent apps opened.You want to see some wild SQL-fu?Create a query from recents Really miss the bin flow view on the record page?Heres something to get you started on adding it to a dashboard query:This is just how I did it,theres probably better html-brai
30、ns in this room that can do it betterGUID of the particular BinGUID of the WorkflowBin History is found in auditUse these as div classes for each reader tab(left)HTML borrowed from the usual bin flow viewLeaning into Configurable JoinsAdam Gross,director of admissions operationsAlisa Loughlin,assist
31、ant director of admissions information systemsLeaning into Configurable JoinsElodie M.Hardt,Senior Assistant Director of Enrollment&Student CRM SystemsTom Nicholas,Director of Enrollment&Student CRM SystemsThe University of RichmondHow We Got HereSlate Implementation(Undergrad Admission)Configurable
32、 Joins announced at Summit;preview phase opens in OctoberGraduate/Professional Programs implemented(shared by Law and Graduate Business)Query Libraries introduced at Slate Spotlight with Alexander in JanuaryStudent Success implemented;Office of ESCRMS(Slate)created to manage three instances201820192
33、02020212022Base-specific CJ permissions and population-aware bases introducedConfigurable Join WinsLibraries are the Linchpin Allow you to curate exports/filters tailored to users needs by role,division,or ability Streamline experience for users Handle joins and subqueries so they dont have to Makes
34、 the query experience overall very similar to Local/STL basesMyth#1Users have to understand joins/relational databases in order to query using CJ bases.Myth#2My users will never be able to figure out subqueries.Myth#3CJs are just too complicated/overwhelming for end users.Local/STLCJMyth#3CJs are ju
35、st too complicated/overwhelming for end users.Extended Exports only appear for bases where a user has Join AccessOther Advantages to CJs Allow use of Person/Application by Population bases in a shared instance Field-level permissions are enforced!Law User Person by Population BaseBusiness User Perso
36、n by Population BaseOther Advantages to CJs Field-level permissions are enforced!Any user with STL permission on Local Prospect BaseUser with Administrator permission on CJ Person BaseUser without Administrator permission on CJ Person BaseCurating Libraries Dont worry about local filters/exports Inv
37、entory most commonly-used exports/filters that will require joins/subqueries(fewer than you think!)Consider how to group and permission common sets of exports/filtersEnd User Needs Crucial department-specific data points Needed format Results intended/required Nomenclature:Existing vs.SlateLibrary S
38、tructuresLibrary Structures By Population Population-based record access Access limitationsLibrary Structures Current User Aware Identify if the library is for all users or by role/team Reference for granting library access permissionsLibrary Structures Starting Small Avoid data overload Core set th
39、at the majority of users will need Build as users request more(service desk)Training Front End Training Ongoing Development Slate Summer Refreshers One-on-one/specialized offerings Shared Instances=Different approaches across different instances and populations To be join awareor NOT to be join awar
40、eThat is the question!Training Join Awareness Power Users Program Managers RegistrarJoin AwareNot Join Aware Standard Slate Users External Offices Tips&TricksTips&TricksRefresh the CJ library!Note:Does not automatically refresh in the test environment.Tips&TricksWithin Libraries Default to using joi
41、ns within subqueries Tips&TricksClearly name libraries and their filters/exportsTips&TricksIf you grant permission to a query that includes exports a user doesnt have access to,they will within that queryTips&TricksTrain your users to default to using Existing Joins Tips&TricksStart with the suitcas
42、es provided in Knowledge BaseQuestions?Tom NicholasDirectorEnrollment&Student CRM Systemstom.nicholasrichmond.eduSlate Community on Slack(slate-)Elodie HardtSenior Assistant DirectorEnrollment&Student CRM Systemsehardtrichmond.eduSlate Community on SlackSuper Slate Technolutions Friends Facebook GroupLeaning into Configurable JoinsElodie M.Hardt,Senior Assistant Director of Enrollment&Student CRM SystemsTom Nicholas,Director of Enrollment&Student CRM Systems#SlateSummit