Service Now Data Warehouse Giles Lewis June 11

Service. Now Data Warehouse Giles Lewis June 11, 2018 1 © 2018 Massachusetts Institute of Technology, IS&T

Agenda § Why replicate your Service. Now data to an RDBMS? § Tools § SNDML (Open Source) § Snow. Mirror § Perspectium § Load a table (Demo 1) § Load some more tables (Demo 2) § Problem Statement § Tables we need § SQL § Edits and Updates (Demo 3) § Database View (Demo 4) § Things to Consider § Recap: How to create a data mart 2 © 2018 Massachusetts Institute of Technology, IS&T

Reasons to Replicate Service. Now Data § Preferred reporting or analytics tool § Using a warehouse to combine data from multiple sources § Analytics or data mining 3 © 2018 Massachusetts Institute of Technology, IS&T

Create a Data Warehouse – Normal Process § Design Data Model ✓ § Develop Extract Process ✓ § Develop Load Process ✓ 4 © 2018 Massachusetts Institute of Technology, IS&T

Replication Tools § SNDML (Service. Now Data Mart Loader) § Open Source (free) § Command Line (no GUI) § Written in Java § Snow. Mirror § Graphical User Interface § Very easy to install and configure § Built in scheduler, plus a lot of other nice features § Perspectium § Both Application and Database replication § Both Pull and Push models § Bi-Directional 5 © 2018 Massachusetts Institute of Technology, IS&T

Demo Tools § Service. Now Personal Developer Instance (PDI) § https: //developer. servicenow. com § Postgre. SQL § “The worlds most advanced open source database” § Hosted on AWS RDS § https: //www. postgresql. org § SQL Workbench/J § A free, DBMS-independent, cross-platform SQL query tool § Runs on Windows, Mac, Linux § https: //www. sql-workbench. eu § SNDML – Service. Now Data Mart Loader § Run on a AWS EC 2 Linux server § https: //github. com/gflewis/sndml 3 6 © 2018 Massachusetts Institute of Technology, IS&T

Demo 1: demo. profile servicenow. instance=https: //xxxx. service-now. com servicenow. username=xxxx servicenow. password=xxxx datamart. url=jdbc: postgresql: //xxxxxx datamart. username=xxxx datamart. password=xxxx datamart. schema=demo 7 © 2018 Massachusetts Institute of Technology, IS&T

Demo 1 8 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: Problem Statement § List all the users who have ITIL role, but who have No Tasks (open or closed) assigned to them SELECT. . . FROM. . . WHERE. . . AND sys_id NOT IN (SELECT referencefield FROM. . . WHERE. . . ) § Bonus: On the same report, show what groups each of these users are in 9 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: Tables § § § sys_user_group sys_user_grmember sys_user_role sys_user_has_role task 10 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: load. yaml metrics: demo. metrics tables: - sys_user_group - sys_user_grmember - sys_user_role - sys_user_has_role - task 11 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: query. sql select u. name, array_agg(g. name) as group_names from sys_user u left join sys_user_grmember m on m. user = u. sys_id left join sys_user_group g on g. sys_id = m. group where u. sys_id in ( select distinct r. user from sys_user_has_role r where r. role in ( select sys_id from sys_user_role where name = 'itil' ) ) and u. sys_id not in ( select distinct t. assigned_to from task t where t. assigned_to is not null) group by u. name order by u. name; 12 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: query. sql select u. name, array_agg(g. name) as group_names from sys_user u left join sys_user_grmember m on m. user = u. sys_id left join sys_user_group g on g. sys_id = m. group where u. sys_id in ( select distinct r. user from sys_user_has_role r where r. role in ( select sys_id from sys_user_role where name = 'itil' ) ) and u. sys_id not in ( select distinct t. assigned_to from task t where t. assigned_to is not null) group by u. name order by u. name; 13 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: query. sql select u. name, array_agg(g. name) as group_names from sys_user u left join sys_user_grmember m on m. user = u. sys_id left join sys_user_group g on g. sys_id = m. group where u. sys_id in ( select distinct r. user from sys_user_has_role r where r. role in ( select sys_id from sys_user_role where name = 'itil' ) ) and u. sys_id not in ( select distinct t. assigned_to from task t where t. assigned_to is not null) group by u. name order by u. name; 14 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: query. sql select u. name, array_agg(g. name) as group_names from sys_user u left join sys_user_grmember m on m. user = u. sys_id left join sys_user_group g on g. sys_id = m. group where u. sys_id in ( select distinct r. user from sys_user_has_role r where r. role in ( select sys_id from sys_user_role where name = 'itil' ) ) and u. sys_id not in ( select distinct t. assigned_to from task t where t. assigned_to is not null) group by u. name order by u. name; 15 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: query. sql select u. name, array_agg(g. name) as group_names from sys_user u left join sys_user_grmember m on m. user = u. sys_id left join sys_user_group g on g. sys_id = m. group where u. sys_id in ( select distinct r. user from sys_user_has_role r where r. role in ( select sys_id from sys_user_role where name = 'itil' ) ) and u. sys_id not in ( select distinct t. assigned_to from task t where t. assigned_to is not null) group by u. name order by u. name; 16 © 2018 Massachusetts Institute of Technology, IS&T

Demo 2: Load and Review 17 © 2018 Massachusetts Institute of Technology, IS&T

Demo 3: update. yaml metrics: demo. metrics tables: - {source: sys_user, action: update, since: last} - {source: sys_user_group, action: update, since: last} - {source: sys_user_role, action: update, since: last} - {source: task, action: update, since: last} - {source: sys_user_grmember, action: sync} - {source: sys_user_has_role, action: sync} 18 © 2018 Massachusetts Institute of Technology, IS&T

Demo 3: Edit and Update 19 © 2018 Massachusetts Institute of Technology, IS&T

Demo 4: query 2. sql select user_name, group_names from itil_users where user_sys_id not in ( select distinct assigned_to from task where assigned_to is not null) order by user_name; 20 © 2018 Massachusetts Institute of Technology, IS&T

Warehouse Considerations § Usability § Denormalization § Views § Transformations § Functions § Performance § Indices § Materialized Views § Security § Users and Access § Roles and Permissions 21 © 2018 Massachusetts Institute of Technology, IS&T

How to create a Service. Now Data Mart 1. Design the data mart 1. 2. 3. 4. Get yourself a database Replicate some tables Build a Proof of Concept Design the data mart 22 © 2018 Massachusetts Institute of Technology, IS&T

Thank You § Survey Link http: //bit. ly/nercomp_servicenow 18 § SNDML Download https: //github. com/gflewis/sndml 3 23 © 2018 Massachusetts Institute of Technology, IS&T