Using Tableau with SAS A Case Study with Recommendations

1. Using Tableau with SASA Case Study with Recommendations 2009 Tableau Customer Conference July 20-23 – Seattle, WA Michael W Cristiani Market Intelligence Group, LLC

2. Should you be in this room? • Questionson the table: • Can output from SAS procedures (what’s that mean?) be used with Tableau? • Should SAS output be used with Tableau? • Can SAS output be used in concert with Tableau, SQL, web apps, without breaking the bank? • Can you contribute by crowdsourcing these questions with • Answers? • More questions?

3. Wisdom from the field “Remember, it’s not about the tools, it’s about the intelligence.” - Ted Cuzzillo Ted Cuzzillo is “a freelance writer who writes lead-generating journalism, PR and marketing for clients within the greater business intelligence community.” He also gifts the BI community with www.datadoodle.com. Ted graces friends and family with fabulous cuisine and delightful stories of Sicily. Ted is here at the 2009 Tableau Customer Conference.

4. Market Intelligence Group, LLC • Established 1997 • Database development • Database management • Decision support and comprehensive business analytics • Tableau application development • Hosted database marketing and business operations applications running on the DataWell® platform

5. Market Intelligence Group, LLC • Michael W Cristiani • Principal • Urban and Regional Planning • Telecommunications demand forecasting and analysis • Business analytics and decision support systems development

6. The Business Problem -- 2002

7. The Business Problem

8. Evaluation framework • Evaluators • Self, Bosses, Peers, Staff (reports) • Self • 3 bosses • 3 groups of 7 peers • 3 groups of 7 staff • Feedback requested for each evaluated behavior • Actual (% of time behavior is observed) • Preferred (% of time behavior is preferred) • Feedback Report • Report Gap, average Actual, Preferred, and (Preferred – Actual) scores for each behavior within each evaluator group

9. First Gen Solution • Gather business rules • Process data sets using PC-SAS (Base) • Summarize data into multiple “tables” to support multiple report formats with unique rules for calculating averages • Apply complex business and privacy rules • Develop Excel reports application • Graphic data presentation per client’s requirements • Lots of VBA • Deliver employee level results in Excel application • Lots of formulas • Huge (typically 100MB/file) • Monthly files • Point and click hard copy reports

10. Survey Administration – consider business rules Employee Assessment – consider business rules Raw Data Set (weekly, on demand) SASProcessing (as new data Arrives) Cubed Data Set (cumulative) PEAK™ Report Generator (Excel deliverable) Current process flow Evaluation Process

11. Some business rules • Protect privacy of evaluators • Minimum of 3 paired ratings per question within each rater group • Collapse remaining matched pairs into generic rater groups • Apply these rules for questions and core competency groups • Allow self and superior evaluations to flow through without matching ratings

12. Some raw and summarized data

13. SAS easily summarizes raw data while applying complex business rules

14. Deliverable description for First Gen Solution • Two MS-Excel huge workbooks (100MB and 30MB) • Reports [Focus of this presentation] • Verbatim Comments • Each tool features • Extensive VBA • In-workbook database (fact and lookup tables) • Point and click functionality • Report previews • Flexible printing • Batch • Individual employee

15. Example from First Gen Solution =+IF(OR($E17="",AND($E17<>"",$DE17="",$DF17="")),"",IF(AND($E17<>"",$DE17=G$7,$DF17=G$7),"S",IF(AND($E17<>"",$EA17=3,$EB17=G$7,$EC17=G$7),"B",IF(AND($E17<>"",$EA17<>0,$EA17<>2,OR($DE17=G$7,$EB17=G$7)),"A",IF(AND($E17<>"",$EA17<>0,$EA17<>1,OR($DF17=G$7,$EC17=G$7)),"P","")))))

16. Example from First Gen Solution

17. Successes of First Gen Solution • Significant improvements in productivity • Speedy data processing using PC-SAS • Batch report preparation takes minutes of SME’s time instead of days • HR organizational development specialists have time to counsel / write courses / teach • Why change this blissful situation?

18. Challenges with First Gen Solution • Frequent business rules changes require extensive SAS code changes • Limited access to PC-SAS on occasion • Data still not in a proper database • Data error tracking is cumbersome, usually occurring after employee has one-time hard copy report • Report format changes are a real challenge in Excel (100s K formulas) • Report distribution is offline / incurs significant mail costs • Administrative tasks handled manually by staff and a third party vendor • What to do?

19. Successes / Challenges of First Gen Solution NO YES NO YES YES YES NO

20. Next Gen Solution Requirements • Automate administrative tasks online • Secure self-serve report generation and delivery online • Store data in proper database • Improve data error detection and remediation • Eventual online data processing • Evaluate SAS processing environment for speed, cost, and reliability • Include restricted views of data for research and tracking

21. Next Gen Solution: Two Cycles • First Cycle: • Leverage investment in PC-SAS data processing code • Process data in PC-SAS • Store summarized data tables and lookup tables in a proper PostgreSQL database • Connect in Tableau to summarized data tables • Design reports for Tableau • Employee take away reports/views • Administrative and research views • Develop Tableau reports workbook • Apply business and privacy rules • Allow data exploration • PDF export • User filters • Publish Tableau reports workbook to Tableau Server • Secure login for up to 300 participants at a time (rolling) • Data exploration • PDF export

22. Next Gen Solution: Two Cycles • Second Cycle: • Start with First Cycle Tableau reports workbook • Test two approaches to data processing and make a decision • Connect in Tableau to summarized data tables • Incorporate SAS data processing logic into PostgreSQL code • Process data online • Requires minimal re-tooling of visualizations in Tableau • Connect in Tableau to unsummarized (raw) data tables • Incorporate SAS data processing logic into Tableau calculated fields or custom SQL • Requires some further re-tooling of Tableau report workbook • Publish Tableau reports workbook to Tableau Server • Secure login for up to 300 participants at a time (rolling) • Data exploration • PDF export

23. Evaluation of Next Gen Concepts Tableau demo comes here.

24. Successes/Challenges of Second Gen Solution YES1 YES YES ONLINE YES – but customizable YES YES 1 DataWell®

25. Why use summarized SAS output with Tableau? SAS is a ubiquitous data management and analysis tool set: Installed base of many millions Base SAS handles large data sets with ease Stable platform that runs in many operating environments Proven analytical and statistical techniques for data exploration and discovery Widely used in academia for research and teaching Training Very large body of official SAS published knowledge about SAS and its usage Endless resources online Accessibility Easy to export the results of SAS data management and analytics to flat files SQL interface is available

26. Why not use summarized SAS output with Tableau? Learning cycle: Becoming very proficient with SAS can take years of experience SAS specialists tend to concentrate on its specialized use within their specific discipline Learning materialscan be very expensive over a short time Expense: SAS licensing can be quirkily expensive Special modules are also expensive, and are often needed to accomplish specific tasks SQL access is not free Server based applications are well outside the budget of most potential users and consultants

27. Why use a SQL database with Tableau Tableau easily connects to many flavors of SQL: Many SQL implementations are open source PostgreSQL is MIG flavor of choice Online database management is a snap since Hosted database management solutions permit Automated inbound data feeds from any third party data provider (real time, online, or batch) Seamless interaction with PC-SAS if necessary Simple/complex querying of unified summarized database Security and accessibility A PHP / PostgreSQL front-end1 to this application will provide assistance in managing administrative functions Tableau is not designed to address. 1 DataWell®

28. Why not use a SQL database with Tableau Learning and evaluation: Becoming very proficient with the SAS/SQL/DataWell interaction may take some time The SAS processing necessary to prepare summarized data for either the First Gen or Second Gen solutions is complex. Will PostgreSQL choke on it? If no SAS processing of raw data is done, is Tableau up to the complex processing steps needed to summarize the data from a PostgreSQL database? For security reasons, clients may not want raw data exposed in hosted end-user facing environment

29. Evaluation of Next Gen Concepts From a business perspective, what makes sense is: Leverage SAS data processing investment Continue to process raw data feeds in PC-SAS Produce SAS output files in near real-time, stored on DB server, for access by DataWell® using PostgreSQL and PHP scripting Build unified results database from stored SAS data sets or output files provides for multiple levels of aggregation in one Tableau worksheet vs multiple tables (SAS/Excel)

30. Evaluation of Next Gen Concepts From a client perspective, what makes sense is: In Tableau, connect to unified results database Employee focused report views Basics research views Train client to go further with Tableau’s cycle of analysis Employee filter Publish workbook to Tableau Server In DataWell®, Integrate Tableau views Automatic view filtering on log-in Add administrative functions outside Tableau’s feature set.

31. Questions and Answers?

32. Resources http://www.migsite.com 119 East Court St Cincinnati, OH 45202 Robert Migliara robert@migsite.com Michael W Cristiani mike@migsite.com