Data Transformation

Step 1. Capture and refine the requirements

• What decisions do you need to make?
• Which reports support those decisions?
• Which systems hold the data those reports depend on?

This creates a shortlist of the metrics and drivers that matter and the data we need to produce them.

Step 2. Build the conceptual model

We map real-world concepts, attributes and relationships.

Example discussion: engineers work multiple shifts on a job as part of a framework agreement for a client. Some engineers are employees and some are subcontractors.

We decide what is a concept, what is an attribute and what is a relationship based on reporting needs.

We sketch a simple diagram so everyone knows how the parts fit together.

Output: a conceptual data model that reflects how the business actually operates.

Step 3. Design the logical model

We review raw data from each source and reconcile it with the conceptual model.

If a system structures data differently, we define how to transform it so the reports answer the right questions.

We document the attributes we truly have and the rules we will use.

Output: a logical model that blends real-world meaning with what systems can provide.

Step 4. Create the physical model

We tailor the design to the technology on Microsoft Azure, choose data types, keys and relationships to keep queries fast and stable, and version definitions so everyone knows what each KPI means.

Output: a physical data model we can build on confidently.