Phillip Khaiat Advanced Computer Systems Using Shared repositories and ODBC to manage multiple Enterprise Architect DBMS Project Repositories Hosted at KPMG HQ Toronto April 23& 24, 2014 2014 Enterprise Architect North American Conference Enterprise Architect Shared Repositories Mr. Phillip Khaiat, BASc, MSc, PMP Advanced Computer Systems April 2014 ©2014 Mr. Phillip Khaiat and ACS Inc, all rights reserved. Version 1.0 Apr., 2014 Copyright Notice © Copyright Phillip Khaiat and Advanced Computer Systems April 2014 All rights reserved. No part of this document may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of Advanced Computer Systems. Version 1.7 Oct., 2012 2 Welcome – Introductions Version 1.7 Oct., 2012 3 Agenda n n n Welcome Background: Multiple Repositories Repository Architecture: n n Requirements Options: n n n Shared Reference File Shared Repository War Stories and Lessons Learned: n n Managing Users and Groups Across Repositories Pitfalls and How to Avoid Them Version 1.7 Oct., 2012 4 Managing Multiple EA Repositories Version 1.7 Oct., 2012 5 Why Multiple Repositories? n n Multiple Independent Projects Security: n n n Cannot show/hide content in a single repository Multiple Concurrent Model Versions Testing and Learning: n “Sandbox” repository 6 Version 1.7 Oct., 2012 Repository Architecture deployment Repositories Name: Author: Version: Created: Updated: Repositories Phillip Khaiat 2.0 21-Apr-2014 10:52:15 AM 21-Apr-2014 10:58:16 AM Sandbox <Proj ect 1> <Proj ect 2> Reference 7 Version 1.7 Oct., 2012 The Architect Version 1.7 Oct., 2012 8 Requirements n n n n n Manage one set of Users and Groups. Multiple projects with a common and consistent set of system resources. Common standards and definitions used across all projects. Ease of access for all team members. Easy to update. 9 Version 1.7 Oct., 2012 Shared Reference File Option deployment Shared Reference File Name: Author: Version: Created: Updated: Shared Reference File Phillip Khaiat 1.0 21-Apr-2014 2:01:35 PM 21-Apr-2014 3:20:11 PM Sandbox <Proj ect 1> <Proj ect 2> Reference Data Reference Data Shared Reference File Reference Data Reference Data Reference 10 Version 1.7 Oct., 2012 Shared Reference File Option n n Can import reference data automatically from a Shared File Disadvantages: n n n n All users must have access to shared file Changes can only be made in Reference repository Must remember to export after every change! Repositories tend to get out of sync. 11 Version 1.7 Oct., 2012 Shared Repository Option deployment Shared Repository Name: Author: Version: Created: Updated: Shared Repository Phillip Khaiat 1.0 21-Apr-2014 3:46:29 PM 21-Apr-2014 3:49:27 PM Sandbox <Proj ect 1> Link Link <Proj ect 2> Link Reference Version 1.7 Oct., 2012 12 Shared Repository Feature n n Uses a central project containing common data as a Shared Repository. Common data includes System Resources such as: n n n n n Security Permissions, Users and Groups Stereotypes and Tagged Values Glossary terms or Data Types Image library (by extension) Shared repositories apply to DBMS based Enterprise Architect projects: n They can only link projects residing within one DBMS. 13 Version 1.7 Oct., 2012 Why Use a Shared Repository? n n n n Set up once Single copy of reference data to manage Can be updated from within any repository All changes are immediately available automatically to all other repositories 14 Version 1.7 Oct., 2012 Shared Repository Advantages n Allows models to be moved between repositories with losing properties: n n E.g. promoting from “Test” to “Production” Allows centralized management of Shared Repository contents: n n Promotes or enforces common usage and standards Users and access control 15 Version 1.7 Oct., 2012 Questions? Version 1.7 Oct., 2012 16 Creating a Shared Repository 1. 2. 3. 4. 5. Select Table Groups to Share Generate SQL Script Edit and Verify Script Run Script Be Careful! 17 Version 1.7 Oct., 2012 Shared Repository Option Version 1.7 Oct., 2012 18 Generates a SQL Script 1. Renames tables in current repository: 1. 2. 3. To t_<table name>_bak Creates synonyms to tables in shared repository. [Recently added to EA] If table does not exist in shared repository: 1. 2. Creates the table Copies data from current repository. 19 Version 1.7 Oct., 2012 Example SQL Script ----------------------------------------- SQL Server Shared Repository script. -- Main repository: [Sandbox]. -- Shared repository: [Reference]. ----------------------------------------- Summary. -- t_secusergroup: Creating new share. -- t_secuser: Creating new share. -- t_secgroup: Creating new share. -- t_image: Creating new share. ---------------------------------------USE [Sandbox] EXEC sp_rename '[dbo].[t_secusergroup]', 't_secusergroup_bak'; CREATE SYNONYM [dbo].[t_secusergroup] FOR [Reference].[dbo].[t_secusergroup]; ---------------------------------------EXEC sp_rename '[dbo].[t_secuser]', 't_secuser_bak'; CREATE SYNONYM [dbo].[t_secuser] FOR [Reference].[dbo].[t_secuser]; END GO ---------------------------------------[…] 20 Version 1.7 Oct., 2012 Questions? Version 1.7 Oct., 2012 21 War Stories and Lessons Learned Version 1.7 Oct., 2012 22 Why we need Tools Version 1.7 Oct., 2012 23 Working With Shared Repositories n n Once implemented, they work well. Two access control points: n n n Enterprise Architect Permissions Database Access rights Can be extended: n n Images Repository-specific permissions 24 Version 1.7 Oct., 2012 Images – Sharing Extension n n n Often have different IDs in different repositories Get scrambled when copying diagrams with elements displaying Alternate Images Can share images by: n n Renaming the t_image table Creating a synonym for it. 25 Version 1.7 Oct., 2012 Repository-specific permissions n Can manage a single set of users and groups, with repository-specific permissions, by: n n n n Sharing Security Users and Groups Not Sharing Security Permissions Assigning permissions in each repository Missing EA functionality: n What users belong to a group? 26 Version 1.7 Oct., 2012 ODBC Tool: Manage Users and Groups n n n User group membership from shared repository imported into Excel Uses the “OLE DB Query” Excel function Format the result as an Excel table Version 1.7 Oct., 2012 Query Definition Select GroupName, FirstName, Surname, Department, UserLogin from t_secusergroup, t_secgroup, t_secuser where t_secusergroup.UserID = t_secuser.UserID And t_secgroup.GroupID = t_secusergroup.GroupID Order by GroupName, Surname; Version 1.7 Oct., 2012 Excel Connection Version 1.7 Oct., 2012 Excel Table n GroupName Administrators Administrators Administrators Administrators Administrators Example: who’s an administrator? FirstName The Rajvir Phillip Phillip Jessica Surname Administrator Brar Khaiat Khaiat Li Department Administration Enterprise Architecture Enterprise Architecture Enterprise Architecture Version 1.7 Oct., 2012 Questions? Version 1.7 Oct., 2012 31 Pitfalls and How to Avoid Them n n SQL Errors Deleting Shared Repository Data (!) 32 Version 1.7 Oct., 2012 SQL Errors n SQL Script generation fails: n n Originally created script with invalid syntax Now gives an error: Problem is repository name with a “-” n Solution is to temporarily rename repository, then edit script. n 33 Version 1.7 Oct., 2012 Deleting Shared Repository Data! n My first attempt: 1. 2. 3. Create new, empty repository Link it to shared repository Do a DBMS to DBMS project transfer: n n n Copy data from shared into new repository Result: all shared data deleted! Why? 34 Version 1.7 Oct., 2012 DBMS to DBMS Project Transfer n Step 1: clears target project! Command doesn’t check for synonyms to shared repository. n This will clear all of the shared data in the source project! n n Lesson Learned (the hard way): n n Do the project transfer first! Then set up the shared repository. 35 Version 1.7 Oct., 2012 Wrap-up n Feedback n Thank You. Version 1.7 Oct., 2012 36 References n n n The Object Management Group: http:// www.omg.org/uml/ Sparx Systems (Enterprise Architect): http:// www.sparxsystems.com.au/resources/ World Wide Web Consortium: http://www.w3c.org/ Version 1.7 Oct., 2012 37
© Copyright 2024 ExpyDoc
