Customer-Facing Reports Automation-Focusing on Adding Value

1 .Customer-Facing Reports Automation: FOCUSING ON ADDING VALUE May 28th, 2019 © The Pythian Group Inc., 2018 1 

2 .AGENDA ● Who are we? ● Customer facing reports and value added ● A typical technical assessment ● Automation to the rescue ● Technologies involved ● Demo ● Future work ● Conclusions and Q&A © The Pythian Group Inc., 2019 2018 2 

3 .Who are we? ● Gabriel Ciciliani ○ @gabocic ○ Internal Principal Consultant ○ Open Source Databases ○ Automation super fan ○ Based in Argentina © The Pythian Group Inc., 2018 3 

4 .Who are we? ● Ivan Groenewold ○ @igroenew ○ Lead Database Consultant ○ Managed Services MySQL ○ Also worked with Oracle, MongoDB ○ Based in Argentina © The Pythian Group Inc., 2018 4 

5 .Helping businesses transform and win using data and cloud © The Pythian Group Inc., 2018 5 

6 . 22 400+ 350+ Years in Experts in 35 Clients Business Countries Globally © The Pythian Group Inc., 2018 6 

7 . Pythian Services Professional Services Managed Services End-to-End Solutions From data and cloud strategy to Complete 24 x 7 operational Proven templated cloud-native roadmap to development, management services on premises data solutions customized to your implementation and beyond and in the cloud needs Two millennia of technology IT Infrastructure, databases, data From data warehouse migrations enabled knowledge and warehouses/platforms & analytics to enterprise data platforms and methodologies to help guide you workloads more Neutral trusted advisors—we put Break/fix with ongoing Fastest time to value with ongoing your interests first improvements to boost operational support and enhancements for performance and efficiency continuous transformation © The Pythian Group Inc., 2018 7 

8 .Diversified, blue chip client base Media/Information Financial Services Retail E-commerce SaaS Services © The Pythian Group Inc., 2018 8 

9 .Consulting and value ● Common types of engagements ○ Fix a specific issue ○ Take part (or drive) a project ○ Improve overall performance ○ Do a system review ● Time can be a limiting factor ● Perceived value © The Pythian Group Inc., 2018 9 

10 .Consulting and value ● Reports are a key part of any project ● Even with templates, tech reports take time ● Maximize time spent in tasks that make a difference ○ Benefits both big and small clients © The Pythian Group Inc., 2018 10 

11 . A typical technical assessment Collecting Populating Analyzing Generating Reviewing Final data files data recommendations report Only value added task! © The Pythian Group Inc., 2018 11 

12 .Technical assessments - Challenges ● Deliver consistent, high quality, assessments ● Streamline analysis ● Focus on adding value ● Concentrate collective knowledge © The Pythian Group Inc., 2018 12 

13 .Automation to the rescue! © The Pythian Group Inc., 2018 13 

14 .But.. before automating, one needs to standardize... © The Pythian Group Inc., 2018 14 

15 .Standardize first Determine: ● Assessment points ● Data to be collected and collection methods ● Report sections -> build a model report © The Pythian Group Inc., 2018 15 

16 . Then automate steps where possible... Collecting Populating Analyzing Generating Reviewing Final data files data recommendations report Collection Report Analysis Recommendations tools builder hints repository © The Pythian Group Inc., 2018 16 

17 .Collection tools ● Simple / easy to use ● Machine readable output ● Low footprint and requirements © The Pythian Group Inc., 2018 17 

18 .Report builder ● Builds standardized graphs ● Takes care of attaching tables, graphs and aux data ● Fed with collection tools output files © The Pythian Group Inc., 2018 18 

19 .Analysis hints ● Guides the consultant through the analysis process ● Concentrates up-to-date collective knowledge © The Pythian Group Inc., 2018 19 

20 .Recommendations ● Stores normalized, curated, reviewed versions of common recommendations ● Toolbar to easily include them into the appropriate section ● Avoids re-writing similar recommendations on every assessment and simplifies final review © The Pythian Group Inc., 2018 20 

21 .Technologies © The Pythian Group Inc., 2018 21 

22 .Technologies involved - Collectors Linux common tools MySQL conf and CSVs status variables Collectors JSONs (BASH) Flat files Percona toolkit ❏ Why BASH? To keep dependencies to a minimum ❏ One-time and In-time collection © The Pythian Group Inc., 2018 22 

23 . Technologies involved - Report builder Google Drive API PARBuilder.py GFolders Package with the GFiles files collected (Python) ❏ Why not keeping all code in Google App scripts? ● Google App scripts execution limit: 6 mins ● Need a script to coordinate the Google API calls ● It is easier to interact with a script that takes parameters than calling a function directly in Google App scripts © The Pythian Group Inc., 2018 23 

24 .Technologies involved - Report builder Google Google Google Drive Sheets Docs API API API GFolders Google Document ready GFiles App Script Processed CSVs for analysis (JavaScript) © The Pythian Group Inc., 2018 24 

25 .Technologies involved - Analysis hints ● Google Docs comments are copied with the template ● The consultant will respond and close each comment once that point is completed © The Pythian Group Inc., 2018 25 

26 .Technologies involved - Recommendations repo ● Recommendations are stored in a special Google Doc ● Using Google Docs embedded Google App scripts, we can add new menus and toolbars © The Pythian Group Inc., 2018 26 

27 .Demo © The Pythian Group Inc., 2018 27 

28 .Future work ● AI to discover patterns hidden in the data collected ● PMM for data collection, data visualization and images generation ● Extend it to other technologies © The Pythian Group Inc., 2018 28 

29 .Conclusions ● All reports analyze exactly the same points ● Recommendations are standardized and well presented ● It’s easy to incorporate additional checks or points to analyze © The Pythian Group Inc., 2018 29