Data Modelling as a Service in a super major

1. Data Modelling as a Servicein a super major Our journeyER/Sig DAMA Boston, 7th March 2007Christopher Bradley I

2. Contents ben.braine@ipl.com • The challenge • Our solution • Self service user administration & provisioning for internal users • Detailed reporting of ER/Studio and Repository usage for user tracking (and chargeback) • Automated Model publishing • Issues / next steps Recap New stuff

3. The Challenge • Company is a diverse federated organisation – culture doesn’t lend itself towards big central control teams • Current position: • Data modelling undertaken to different degrees in different Segments & Functions • Variety of tools & techniques used to define DATA models • ARIS, ERWin, System Architect, KMDM, Enterprise Architect, Power Designer, Rational, PowerPoint, Visio, …… others? • Projects encounter common cross business data concepts, but create their own models & definitions • Massive benefit in reuse of common models & definitions (including standard industry models) • Plus benefits in interoperability, & efficiency through common approaches.

4. The Challenge • However ….. • Wanted to introduce data services including Data Modelling as a Service (DMaaS) • Environment & service not just a tool.DMaaS includes: Training & Education, Project support & mentoring, Community Of Interest, Documentation & “best practice” guidelines, Technology support (inc ER/Studio, macros, SharePoint etc). • Don't know how many / where users are • very volatile user population; • 100's of modellers, • 1000's of analysts • 1000’s business users wanting to read / view models • Standard common models (e.g. Master Data) need to be developed & then consumed by all projects. • Therefore common shared repository & standard DME

5. ben.braine@ipl.com 1. Self service user admin & provisioning

6. 1: Self Service • Self service user administration & provisioning for internal users to: • register for ER/Studio • gain & change repository permissions • repository password change • licence server access • view registered users / managers (& members) of teams can see who’s registered

7. Self Service • View registered users / managers (& members) of teams can see who’s registered

8. Self Service – Example: View users • Lets managers know who has registered (or who has not) on their team • Lets users verify they are registered correctly • Lets users see other members of the internal data modelling community

9. Self Service • Register new user

10. l l a w e r i F Active Directory Repository Repository Access Web Service Email Client Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint User Database Self Service – Example: Register New User New user request submitted from SharePoint Request received and validated against Active Directory User created in database User created in Repository User given default permissions Welcome email sent User only has to provide business area and MAC address - form is quick and simple to complete

12. l l a w e r i F Active Directory Repository Repository Access Web Service Email Client Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint User Database Self Service – Example: Register New User New user request submitted from SharePoint Request received and validated against Active Directory User created in database User created in Repository User given default permissions Welcome email sent User name, email address and contact information is retrieved from the BP Global Address List using Active Directory

13. l l a w e r i F Active Directory Repository Repository Access Web Service Email Client Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint User Database Self Service – Example: Register New User New user request submitted from SharePoint Request received and validated against Active Directory User created in database User created in Repository User given default permissions Welcome email sent The user is added to the local user database

14. l l a w e r i F Active Directory Repository Repository Access Web Service Email Client Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint User Database Self Service – Example: Register New User New user request submitted from SharePoint Request received and validated against Active Directory User created in database User created in Repository User given default permissions Welcome email sent The user is created in the Repository using ActiveX automation and assigned a randomly generated password

15. l l a w e r i F Active Directory Repository Repository Access Web Service Email Client Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint User Database Self Service – Example: Register New User New user request submitted from SharePoint Request received and validated against Active Directory User created in database User created in Repository User given default permissions Welcome email sent Permissions cannot be assigned using ActiveX automation so a bespoke web service is used to modify the database directly

16. l l a w e r i F Active Directory Repository Repository Access Web Service Email Client Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint User Database Self Service – Example: Register New User New user request submitted from SharePoint Request received and validated against Active Directory User created in database User created in Repository User given default permissions Welcome email sent An email is created, based on a template heavily customised based on the users personal information and licenses requested

17. Self Service New user request submitted from SharePoint Request received and validated against BP Active Directory User created in database User created in Repository User given default permissions Welcome email sent

18. 2. User & usage reporting

19. 2: User & usage reporting • Detailed reporting of ER/Studio and Repository usage for user tracking (and chargeback) • Custom solution • Database of users • User department & contact details • MAC address • Repository id • Licence server usage • Peak number of concurrent users (are we approaching licence limit?) • Number of unique users registered and using service (monitor take-up)

20. User & usage reporting • Log files are copied from the server and parsed • Usage graph shows peak concurrent license usage and number of unique users for a given day • Allows license purchasing decisions to be based on actual usage • Allows Data Modelling Environment take-up to be monitored

21. 3. Automated publishing of models to SharePoint

22. 3: Model Publishing - highlights • Publishing of models from repository to Data Modelling Environment SharePoint • Completely automatic generation of models in HTML (no need to produce ER/Studio report settings files) • Usual approach is to utilise report wizard • Approach would be unworkable for organisation’s large # of models • Automatically generate report settings files • Customise generated reports • Layouts, title etc • Automatic uploading to SharePoint • Uploading of 1000’s of files to SharePoint is very problematic • Restart built into our upload jobs • Report home page in SharePoint mimics repository structure • Highlights when repository models and SharePoint reports not synchronised • Publishing meta data to inform users of status

23. Issues faced • How to view models without client reader • How to upload models to SharePoint • Too many files created in HTML publication • Size of model images • How to directly link to sub-models

24. 1. How to view models without client reader Inspired by the Watkins Motors Publishing approach we looked at how we could build upon this The WM approach still had several manual steps The data models are constantly changing, so the admin overhead associated with manual publishing steps would be too high – we didn’t want to have ANY manual steps. SharePoint – chosen solution.

25. 2. How to upload models to SharePoint • Investigated 3 options: • Shell automation • Web DAV • Vermeer RPC protocol

26. How to upload to SharePoint Option 1: Shell automation • Message boxes were always popping up. • No “retry” method available • “Suppress errors” is not supported in Shell Automation. Option 2: Web DAV A protocol for sending files & versions to SharePoints. But, internal internet security appliance blocks this protocol & the red-tape to get this changed would be enormous. Option 3: Vermeer RPC protocol Web folders use this. Standard within MS FrontPage (VTI folders) We investigated an example / sample implementation & found it’s written in .NET We re-wrote this in VB6 for Excel

27. 3. Too many files created in HTML publication • For example, a single sub model had 550+ files generated; • All bar 24 were empty / dummy files. • We wrote routine to identify & then delete these before running the SharePoint publishing processes.

28. Model Publishing - Example Query for updated models Generate settings file Generate HTML version of model Upload HTML to SharePoint Generate and update repository page l l a w e r i F Repository Repository Access Web Service Repository Server Data Modelling Environment Support Application ER / Studio Data Modelling Application Environment SharePoint

29. Model publishing

31. 4. Size of model images Automatically generate report setting file We didn’t want any manual steps in creating a report settings file But by default zoom level of image is 100% - for lots of our large models this is too big & will result in image not being generated – why? Runs out of memory to render image ER/Studio 7.1.1 allows specification of zoom levels for sub-models We could now auto generate configuration settings files so that model images would be published ok

32. 5. How to directly link to sub-models For integrating with our process modelling tool (ARIS) we want to be able to click on a link (in ARIS) to go directly to the SharePoint having the sub model. Option 1: Parse full generated report & then re-generate HTML for the sub-model: Too slow - models too large Option 2: Generate each individual sub-model on their own: Option 3: Need to create tree of sub-models: But too many redundant sub models generated by ER/S reporting; So we added configuration parameters to specify the level of sub models we wanted publishing - business requirements only needed sub models at a particular level

33. Next steps & issues • Further workflow steps in SharePoint • Interactive training – certify internal users • Expand Community of Interest • Web portal to interrogate repository • How to restrict licence server use to only approved users • Can’t “restrict” unregistered users who know licence server name (unless we build a proxy server) • Laptop change = new MAC address. Are they a legitimate user? • Internet access to repo & licence server • Linking model artefacts to drive re-use (e.g. Entities from Master Data Models)

34. Weblog RSS Libraries Survey Intranet sites Discussions Lists Wiki editor Workflow A final thought • Did you miss the Web 2.0 & collaboration seminar? • Don’t worry – this has been Web 2.0 & collaboration in practice !

35. Thank you • Contact details: • Email: chris.bradley@ipl.com • Email: nic.paul@ipl.com • Tel: +44 (0)7973 184475 • MSN: chrisbradley@bigfoot.com • Web: www.ipl.com I

36. I Backup slides

37. Model Publishing - Example Query for updated models Generate settings file Generate HTML version of model Upload HTML to SharePoint Generate and update repository page SharePoint is queried to get publication dates and compared with the last modified dates in the Repository

38. Model Publishing - Example Query for updated models Generate settings file Generate HTML version of model Upload HTML to SharePoint Generate and update repository page A Report Settings File (XML) is generated from the model structure

39. Model Publishing - Example Query for updated models Generate settings file Generate HTML version of model Upload HTML to SharePoint Generate and update repository page ER/Studio’s built in facility is used to generate the HTML reports

40. Model Publishing - Example Query for updated models Generate settings file Generate HTML version of model Upload HTML to SharePoint Generate and update repository page HTML files are customised then uploaded to the SharePoint. Upload is unreliable so multiple retry loops had to be implemented

41. Model Publishing - Example Query for updated models Generate settings file Generate HTML version of model Upload HTML to SharePoint Generate and update repository page Repository page is generated from the Repository structure, shows when model changed and if model is out of date

42. In the spreadsheet we say PF_LDM_FINAL should publish sub-models at level 1 only (level 0 is the top level).  We actually have a config parameter called "Publish Submodels" and it is set to "PF_LDM_FINAL.dm1(1-1);GOIL_LDM(1-1).dm1".