Contexts: The Good, The Bad & The Ugly

1. Contexts: The Good, The Bad & The Ugly Steve Krandel & Jeffrey Laughlin

2. Agenda • Introduction • Overview Of Contexts • A Journey Into Context Hell • Starting Point – Life Before Contexts • First Step – A Tale of Two Contexts • Going Deeper – Two Sides of the Story • Close to the Edge – Having your cake and eating it too • Conclusion/Q&A

3. Introduction • Steve Krandel • Jeffrey Laughlin • Intuit

4. The Journey into Context Hell • Need a good quote from/about Dante’s Inferno or a long, arduous journey • Image of a haunted house, fire, pitchforks, etc. • Since it’s October and Halloween is almost here, let’s look at this presentation like it’s haunted house. You paid to get in, we’ll try to walk you down a path that may be scary, exciting or even fun at times, but hopefully you’ll have a couple of pieces of candy when it’s over

5. Disclaimer • Over-simplification used to present core concepts in a more understandable manner • Example do not represent any particular company, person, or universe , living or dead. • Others?

6. Steve’s Slides

7. Starting Point – Life Before Contexts Business Needs • Customer Care management wants reporting from two of their business systems • From the Case Management system • How many cases are created by day and by agent • From the Phone Switch • How many calls are handled by day and by agent • How many time is spent handled calls by day and by agent

8. Starting Point – Life Before Contexts Solution(s) • Cases universe • For reporting from the Case Management system • Calls universe • For reporting from the Phone Switch

9. Starting Point – Life Before Contexts Calls Universe

10. Starting Point – Life Before Contexts Cases Universe

11. Starting Point – Life Before Contexts

12. Starting Point – Life Before Contexts Outcome • Happy Users • Our solution is meeting the business needs • Leaders have information to support decision making • But… • Is there a better way we could meet the business needs? • Are there any possible efficiencies here?

13. Starting Point – Life Before Contexts • These universes have a lot in common • Both have two common dimensions and (coincidentally) use the same Table and Classes/Objects • Dates • Agent Information • Both are used to report a common business function • The business may even be looking at data from both universes in the same reports • Can we put both of these Subject Areas together into a single universe for the Contact Center? (of course we can!)

14. First Step – A Tale of Two Contexts Customer Service Universe

15. First Step – A Tale of Two Contexts Setting up multiple contexts • A separate context is created for each fact table • The join from the fact table to each dimension table is included • Notice that every join in the universe is included in at least one context

16. First Step – A Tale of Two Contexts Parameter Settings • Multiple SQL statements for each context • TBD – Separate statements • Multiple SQL statements for each measure • TBD - Separate statements per fact table – poor mans contexts • Allow selection of multiple contexts • We’ll get to this one later

17. First Step – A Tale of Two Contexts What happens when we run a report? Using only one context

18. First Step – A Tale of Two Contexts What happens when we run a report? Using both contexts

19. First Step – A Tale of Two Contexts Context Prompting • There is no context prompting, because the contexts have been built properly Steve’s Slide Here

20. First Step – A Tale of Two Contexts

21. First Step – A Tale of Two Contexts Outcome • Happy Users • Our solution is meeting the business needs • Leaders have information to support decision making • We are learning more • Until… • New answer lets users see more of the world… • Seeing more of the world make users ask more questions… • Those new questions send us back to work…

22. Going Deeper – Two Sides of the Story New Business Needs • Cases have two significant dates • Case Opened Date (default field used up to now) • Case Closed Date (“new” field) • The business want to be able to use both in reporting • Sometimes the Date needs to represent Case Open Date • Sometimes the Date needs to represent Case Close Date • (But rarely both at the same time)

23. Going Deeper – Two Sides of the Story Solution(s) • Possible ways to meet this new requirement • Separate Date Dimensions • Common Date with Context Prompting

24. Going Deeper – Two Sides of the Story Customer Service Universe Context Prompting now occurs by design

25. Going Deeper – Two Sides of the Story Which Contexts are available in the Context prompt? • When none of the objects in the query are related to the Cases subject area • No ambiguous relationships • No need for a context prompt • When ANY of the objects in the query are related to the Cases subject area • Multiple relationships exist • The user is prompted to select a context from the list of context that can resolve the conflict

26. Going Deeper – Two Sides of the Story Context Names and Descriptions • Make sure your context names and descriptions allow the user to recognize and select the context that meets their needs OR Which context prompt options would you prefer to see?

27. Going Deeper – Two Sides of the Story Setting the default context in a report • You can setup the report so that once you have selected a prompted context and saved it, the report won’t be prompted for a context again • Saves users from having to know which context they should select when they refresh the report • Works in View mode (but not in Edit) • Scheduled reports will fail if there is an undefined context prompt • Settings (for Web Intelligence reports) • Edit Query > Properties Tab • Uncheck “Reset contexts on refresh”

28. Going Deeper – Two Sides of the Story

29. Going Deeper – Two Sides of the Story Outcome • Transition • Success breeds success (or at least more opportunities for success)

30. Close to the Edge – Having your cake and eating it too Business Needs • Sales management needs to be able to report on opportunities and orders. • An opportunity can have an associated order, but it is not required • An order can have an associated opportunity, but it is not required

31. Close to the Edge – Having your cake and eating it too Solution(s) • A Universe that supports all 3 scenarios for the information they need: • For selected dimensional values • ALL of the Opportunities, and • ANY of the associated Orders OR • ALL of the Orders, and • ANY of the associated Opportunities OR • ALL of the Opportunities, and • ALL of the Orders

32. Close to the Edge – Having your cake and eating it too Sales Universe

33. Close to the Edge – Having your cake and eating it too Intermediary Aliases • At least two variations of the same join are necessary to meet all 3 Opty/Order scenarios for Sales reporting There can only be one join between OPTYS.OPTY_ID and ORDERS.OPTY_ID ALL records from OPTYS ANY related records from ORDERS OPTYS.OPTY_ID = ORDERS.OPTY_ID(+) ALL records from ORDERS ANY related records from OPTYS OPTYS.OPTY_ID(+) = ORDERS.OPTY_ID

34. Close to the Edge – Having your cake and eating it too Intermediary Aliases • An alias of one of the tables from join with multiple variations • An intermediary for the original table to create a separate, distinct path for the relationship between the tables • Used to link the two tables to mimic the join it is replacing • Replaces the original table in the join to the other table • Joined to the original table by primary key • Either table can be used as an intermediary • Create a separate Intermediary Alias for each variation of the join • By creating an alias for the additional variations on the join, joins can exist simultaneously in the universe

35. Close to the Edge – Having your cake and eating it too Intermediary Aliases

36. Close to the Edge – Having your cake and eating it too Parameter Settings • Allow selection of multiple contexts • Separate SQL is generated for each context selected • Results of each are merged together by a union • Provide a single output for the data provider

37. Close to the Edge – Having your cake and eating it too What Happens when a report runs? Only one of two contexts in prompt selected One SQL statement runs “normally”

38. Close to the Edge – Having your cake and eating it too What Happens when a report runs? Both contexts in prompt selected All Optys & Any Orders Context Union operator ties them together All Orders & Any Optys Context

39. Close to the Edge – Having your cake and eating it too

40. Key Learnings • You can organize tables, aliases, joins and objects to create complementary overlapping contexts for distinct data paths. • You can use context prompts to allow users to have different cuts at the data while sharing the same report. • It's even possible to allow users to select multiple contexts with in a single data provider to allow complex problems to be solved.

41. Conclusion / Q&A

42. Appendix

43. ] • Thank you for participating. Please remember to complete and return your evaluation form following this session. For ongoing education on this area of focus, visit the Year-Round Community page at www.asug.com/yrc [ SESSION CODE: INSERT SESSION CODE