Build Your Own Enterprise Architecture System with Microsoft Access
There is a widely held belief that all Enterprise Architecture (EA) applications are expensive, bureaucratic and complex. Whilst many capable applications of such scale do exist, there is no requirement for this to be the case; indeed the TOGAF standard encourages practitioners to select tooling most appropriate for the environment to be managed.
By way of an example, through my consultancy activities I've recently helped a larger SME introduce EA capabilities by implementing a lightweight EA system based upon the TOGAF standard using only their existing Microsoft 365 tooling.
This system took just a couple of weeks to implement, required no costs for software, and is already driving a far deeper understanding of dependency, complexity and risk within their organisation.
In turn, this has now informed a far more strategically targeted programme of work for the coming year to deliver better value to the business whilst reducing risk. Without such a capability, organisations can resort to tactical, reactive and break-fix driven activities, missing opportunity to drive growth and value.
High Level Approach
I share the high level approach I followed should this approach be relevant for your organisation. I refer to the specific tooling I used to illustrate this example, however, the method should be broadly applicable to any comparable tooling of your choice:
1. Determine the Scope and Purpose of your EA Practice
As per the TOGAF guidance, the following should be carefully considered as they will have impact on your preferred tooling and its subsequent success:
- Budget - Consider if you have a project budget available, and / or operational budget to fund tooling and staff in subsequent years. For me, a lack of available budget for tools drove the decision to use the Microsoft 365 applications already in place.
- Staff - Who will perform the work described in the following sections? Who will own the EA data and maintain and operate the system after you go live?
- Scope - Think about who your system will serve; are you an IT Department looking to manage technology within a larger organisation (like my client)? Are you a dedicated Enterprise Architecture function with a broader remit? Your use case(s) will determine the functionality the system will require.
- Purpose - What are the intended benefits of your EA system? Are you looking to manage complexity, reduce cost, improve business fit, document business structure, etc? Be clear about the purpose and benefits you wish to realise as they too will influence the functionality of the system you will require.
- Opportunities - Are existing tools, technologies, skills and capacity available for you to use without additional investment?
- Constraint - Where you may lack funding, capability or capacity, think about how you might overcome challenge in order to deliver what is required. Alternatively, consider amending the scope of your intent to better reflect your ability to deliver.
Keep all of the above in mind as they will greatly influence the following sections.
2. Select A Baseline High Level Data Model
I recommend basing your EA data on an existing entity relationship model rather than trying to create a new one from scratch. Instead, an established baseline model will help you more rapidly build your system in a more valid and standards-compliant manner.
I started with the TOGAF meta model, however, I found it rather too abstract to be immediately useful. Instead, I used Orbis' articulation of the TOGAF meta model as I am familiar with it from previous projects, and I find it to be richer and more developed for practical application:
The intent is that each of the entities in the model will eventually become tables in your EA system.
3. Tailor the Model to Your Organisation
With reference to the scope and purpose of your practice determined in Section One, you may now tailor your the baseline model to fit your particular needs:
For example, should your intent be to only manage your IT Estate, then additional articulation of Technology specific entities may be relevant. If so, this would be the time to design such entities and add them to your model.
Conversely, should your scope reduce emphasis on the management of certain elements then now would be the time to consider collapsing them into simpler structures, or even remove them entirely.
For example, an IT Department might not have a desire to track business goals, motivation or function; and a business-focused Enterprise Architecture team may have less requirement to manage the specific details of technologies.
So, continue to refine your data model until it best reflects the scope and purpose identified in Section One. When complete, you will have a tailored data model to include the entities that you wish to track, and the relationships of those entities to other entities in your data model.
4. Elaborate Your Data Model with Relevant Attributes
You can now proceed to defining the attributes (fields) that you wish to track for each of the entities (tables) in your data model. Again, tailor the fields to your requirement by defining field types relevant for your scope and purpose.
Consider also where tables may link to themselves, and where "many to many" relationships exist, and design any linking tables required to enact such relationships. Clearly, some database design skills are likely to be useful at this stage.
When complete, you will have a schema suitable for creating a database. My approach is to develop the schema in spreadsheet such that the code required to create the database can be automatically generated, and subsequent changes to the data model can be rapidly re-generated with minimal effort. It is also self-documenting.
5. Evaluate and Select Tooling
Now you are ready to select the tooling with which you will build your system. Consider your budget, constraints and opportunities identified in Section One.
For my client, the Microsoft 365 suite was the obvious choice; they had already purchased this tooling, they had a mature deployment, and had great support skills within the team. This meant Microsoft Access as a front-end application and SharePoint lists as backend table storage. To inform this decision, I used the following options appraisal structure:
| Scenario | Technology Mix | Commentary |
| Individual User | A single Microsoft Access file containing application logic and tables. | Simple, rapid and easy. Will not scale beyond a single user. Data and application logic difficult to maintain separately. |
| Small Team | A front-end Microsoft Access file containing application logic and a separate back-end file for tables. | Simple, rapid and easy. Performance may not scale beyond a small team. Liable to file corruption. Relies upon underlying file permissions for security. |
| Large Team or Small Department | A front-end Microsoft Access file containing application logic and a separate back-end hosted in SharePoint Online lists. | Scales-up to more users. SharePoint Online can feel slow versus a RDBMS. Can consume native SharePoint Online security. Works consistently in distributed environments. |
| Large Department or Organisation | A front-end Microsoft Access file containing application logic and a separate back-end hosted in SQL Server (or another enterprise-class RDBMS). | Requires procurement of supporting RDBMS infrastructure. Requires DBA skills to develop and maintain back-end. Scalable, secure and performant. |
For your organisation, I recommend an equivalent options appraisal to evaluate your specific tooling, structure and purpose.
6. Build Your System
Now comes the fun part: building your system.
Initially, I used Microsoft Access to develop both the front end application and back end tables together at the same time. I rapidly built the tables using using the scripting created during Section Four; the automatically generated SQL statements can be pasted into a Microsoft Access SQL query window.
Then, I used the 'move data' facility within Microsoft Access to move the tables to SharePoint Online leaving links to the tables in their place. I found it preferable to move the tables to their enduring location before building the front end application as performance and function within Access can differ slightly between back end technologies, particularly so with SharePoint. It would be frustrating and wasteful to build an application that works locally, but then requires rework to operate using an alternative back end.
This completed the back end build. In your environment a similar processes might be followed to scale your tables out to SQL Server, or another backend RDBMS technology.
I then turned my attention to the front end development. I started by building repeating forms to list out a compact overview of the key attributes of the main entities in the data model. For example, I built forms for technology components, capabilities, vendors, etc.
I then built a 'detail' form for each of the above to permit 'drill down' from the overview for data amendment, entry and inspection. For entities like Technology Components that have multiple attributes (fields) and lots of links to other entities, I found availability of screen real-estate a challenge; I often had to use the 'tabbed form control' as a way of stacking fields. I also built subforms to demonstrate relationships, integrations, realisation of abstract capability, and contribution towards business goals and structure.
When the overview and details forms were built, I created a 'Main Menu' form that runs on startup to make them accessible in a branded and coherent manner. I added some VBA code to enact triggers and provide automation where appropriate to improve user experience and remove manual record maintenance tasks.
Next, I considered reporting requirements and built views (or 'queries' in Microsoft Access) to perform common reports, such as identifying technologies dependent upon expiring or end-of-life components.
Finally, I build a separate, standalone Microsoft Access application containing VBA code to replicate the SharePoint back end tables to local copies. I periodically run this code to perform an ad-hoc backup of table structure and EA data.
Recovery of data can be performed by copy and paste of data from the local tables back into the SharePoint Online linked tables. Recovery of SharePoint Online table structure (and data) can be performed by repeating the 'move data' feature.
7. Collect EA Data
Next it's time to populate your system with the EA data describing your environment. The location of this data might be spread around your organisation unless previous efforts have being made to centralise it.
In my instance I found it in several types of repository, including:
- Design and support documentation,
- ITSM Systems,
- Contracts and commercial documents, and most importantly,
- In the minds IT staff members.
Consequently, where data could not be derived from written or system sources I needed to engage with IT staff members to capture knowledge from them. It was also useful to validate information I had found from the other sources listed above.
8. Sell the Benefits
The system can only deliver benefits when people use it to its intended potential. So, to promote usage I embarked upon a charm offensive within the organisation, showing and telling the benefits of the system to relevant stakeholders. These included:
- Operational IT Staff, and how their use of system could make their daily activities easier,
- Senior Officers, and how the data could inform value-led investment and help manage risk,
- Other interested parties around the organisation, as an opportunity to share best practice, demonstrate competency and build the IT brand.
Thankfully, the system was well received and take-up was high; subsequently, the IT department in question now drives the EA function within their organisation.
9. Use the System
Embed use of the system into daily practice to keep the data up-to-date, relevant and accurate. For me this meant:
- Ensure organisational project and change processes feed data into EA system.
- Seek opportunity to build integrations with operational systems such IAM, ITSM, Inventory, HRM and financials to broaden the capability and richness of the data.
- Regularly review the data in the EA system with the stakeholders, systems owners, vendors, etc.
- Regularly review the function of the system and the data with IT Staff.
- Report upon the data regularly to identify risks and opportunities that can inform the project pipeline.
- Seek new use cases among disparate stakeholders, and continually develop feature enhancements in an agile manner to meet their needs.
- Seek opportunities to join and compare EA data in novel ways to create insight, find inconsistencies and build automation.
- Develop additional tooling to report upon, visualise and share your EA data with relevant audiences, such as SQL Server Reporting Services and PowerBI.
Conclusion
This has been a brief, high level overview of the steps I followed to rapidly build a successful, low cost Enterprise Architecture system using only Microsoft 365 tooling. I hope you find it useful should you (now) be considering a similar project.
Please contact me if you are interested in obtaining any of the artefacts I refer to above, such as the ER data model and Microsoft Access files. Furthermore, if this approach could be useful in your organisation then contact me and I'd be delighted to assist.