Efficient ERD Modelling with MS Copilot: A Time-Saving Journey

In my role as senior Data and Analytics Consultant, I always wished I could sail through those ERD tasks that demand considerable time and energy. Let me tell you how MS Copilot turned my time-consuming ERD modeling into a time-saving journey!

In my role as senior Data and Analytics Consultant at NTT DATA Business Solutions, one of the common, but time-consuming tasks I have to undertake is the definition of ERDs (Enterprise Relationship Data Models).

Recently, I embarked on a project to define a comprehensive ERD model in the automotive sector, specifically a data model for a key business entity – the Vehicle. Designing a data model that integrates data from various sources can be a complex and time-consuming task. However, with the assistance of MS Copilot, this process became more streamlined and efficient. In this blog post, I will explore how MS Copilot’s assistance saved me time in researching and defining an ERD model for vehicles.

Focused Research

Our client utilises the SAP Vehicle Management System (VMS), an industry solution for the automotive sector, which centralises all vehicle-related data and supports the entire vehicle lifecycle.

To build an ERD for the Vehicle Model based on SAP VMS as the primary data source, I should have looked for the following types of information, among others:

SAP VMS overview: to understand the basic concepts and functionalities of SAP VMS. This includes how it integrates with other SAP modules like MM (Materials Management), SD (Sales and Distribution), and FICO (Financial Accounting and Controlling).
VMS tables and their relationships: to identify the key tables used in SAP VMS and their relationships and the main business entities within SAP VMS.

Instead of sifting through numerous documents, blogs and web pages, Copilot directed me to the most relevant resources and summarized the key points. Once I obtained the necessary information, I reviewed and verified it. If I had doubts, I asked follow-up questions to get immediate clarifications.

On this task, I estimate I saved 3.5 hours over a 2-week period.

Initial ERD Draft

One of the first steps in designing a Vehicle data model is creating an ERD. MS Copilot assisted me in visualizing the relationships between different entities such as Vehicles, Dealers, Customers, Sales and Purchase Orders and their mapping to VMS tables. For these tables, MS Copilot also suggested primary keys and foreign keys for each relationship and provided the purpose of every table in a summarized way.

An excerpt of the ERD generated by Microsoft Copilot in text format.

VLCVEHICLE is the central table.

VLCCUORDER, VLCDELIVERY, VLCADDATA, VLCINVOICE, and VLCHISTORY all reference VLCVEHICLE through the Internal Vehicle Number
1. VLCADDDATA: Stores additional qualifier data for vehicles, providing more detailed information.
o Primary Key: Qualifier ID
o Foreign Key: Internal Vehicle Number (references VLCVEHICLE)
2. VLCCUORDER: Manages purchase orders related to vehicles, linking each order to a specific vehicle.
o Primary Key: Purchase Order Number
o Foreign Key: Internal Vehicle Number (references VLCVEHICLE)

On this task, I estimate I saved 1 hour over a 2-week period.

Attribute Definition

Defining attributes for each entity is crucial. MS Copilot assisted me in identifying and defining these attributes. For example, the Vehicle entity included the Vehicle Identification Number, Manufacturer, Model, Paint, and Trim attributes. I then cross-checked the responses with trusted sources (e.g. current reporting and analytic systems, gathered requirements, workshops, etc.).
On this task, I estimate I saved 2 hours over a 2-week period.

Documentation

Comprehensive documentation is crucial for maintaining and understanding a data model. MS Copilot helped me generate detailed content for the Business Requirement document, making it easier for team members and SMEs to understand and validate the proposed new data model.

On this task, I estimate I saved 1 hour over a 2-week period.

Total Time Saved

By integrating Microsoft Copilot into my ERD design process, I saved a total of 7.5 hours over a 2-week period.

Here’s a summary of the time saved at each task. Please note that the “With Copilot” column includes the time spent on validating and refining MS Copilot’s responses:

Task	Traditional Approach	With Copilot	Time Saved
Focused Research	8 hours	3.5 hours	3.5 hours
Initial ERD Draft	3 hours	2 hours	1 hours
Attribute Definition	16 hours	14 hours	2 hours
Documentation	6 hours	5 hours	1 hour
Total	33 hours	25 hours	7.5 hour

Conclusion

MS Copilot provided valuable assistance at every step during the data model design phase. By reducing the time required for each task, it allowed me to focus on other critical aspects of the project.

I highly recommend giving Microsoft Copilot a try if you’re looking to streamline your data modeling design process.