Modeling Data and Design

At Horizonte, a person can have several contracts, a contract can produce several bills and a consent can change with time. If the model ignores these differences, a report can double amounts and an assistant can assign an authorisation to the wrong purpose.

Key concepts

A data model He's a part of the business so we can get her and work with her. Select relevant entities, attributes, relationships and restrictions. It does not reproduce the whole reality: it makes clear the decisions that matter for a use. "Client" requires an agreement as to whether it means person, organisation, commercial account or other entity.

The cardinality indicates how many events can be related. A client can have many contracts. Each example contract belongs to a client. That rule should be validated with the business: if there are joint headlines, the model needs another relationship. Draw first an foreign key without agreeing to the rule can consolidate an incorrect assumption.

The granularity Answer what a row represents. A row for an invoice and a row for an invoice line are not interchangeable. By combining sets with different granularities, we have to preserve relations and amounts correctly. A valid SQL result can be semantically wrong.

The C7 client relates to C2 and C3 contracts. C2 has F42 and F43 bills while C3 has F44 bills. The 1 → N brands show that a client can have several contracts and each contract several bills. The keys preserve every relationship and avoid confusing a person, a contract and an invoice as if they were the same unit. ↗

From Meaning to Physical Model

The model conceptual allows to discuss entities and rules without relying on a database. The logical requires attributes, identifiers, relationships and restrictions. The physical concrete tables, appropriate types, indexes and structures for the chosen engine. To move from one view to another requires decisions. That's not just translating names.

A technical key identifies a registry a business key can identify an entity in a context. A client number of two different systems can match without referring to the same person. That's why the interidentifier mapping should maintain its origin. Reducibility and identity decisions are deeply focused on master data.

The time's modeling as well. Horizonte's record of purpose, state and valid consent. A currently boolean indicator does not explain what authorization existed at an earlier date. The model defines what history and how it's been interpreted. The applicable retention shall be agreed with the competent officials.

The rule "a client can have several contracts" is represented as a relationship 1 to N. In the logical model, CLIENTE.id_cliente is the primary key. CONTRATE has its own primary key, id_contrato, and uses id_cliente as foreign key that points to CLIENTE.id_cliente. The connection between the fields shows how each contract keeps its relationship with the client. Design the keys from the agreed business rule, before choosing the storage. ↗

Modeling and Semantics in Oracle

At Oracle AI Data Platform and Oracle Autonomous AI Lakehouse, schemes, tables and views can materialise models and data products. The catalogue describes the assets but to discover their columns does not automatically decide the granularity or definition of a metric. The team keeps that correspondence between a business model and published structures.

One semantic layer expressed terms, measures and relations for consistent consumption. One ontology makes explicit entities and relationships of a domain. can provide a context for analytics and agents. He's no substitute for his physical design. Nor does it guarantee that an inference of AI coincides with the definition approved by the organisation.

In Oracle Analytics Cloud, use Semantic Modeler to connect tables with terms and measures of the business. Define physical joints by their keys, represents Client and Contract by logical relations and cardinality and publish a model that analysts can re-use. Oracle AI Data Platform and Oracle Autonomous AI Lakehouse provide data prepared for this consumption.

To measure "active customers," select existing contracts at cut date and count different client identifiers. C7 contributes a client even if they have several contracts currently in place. The owner approves the definition. model sets up aggregation of the measure and contrasts with known cases. At Oracle Autonomous AI Database, Data Analysis allows to organize dimensions, hierarchies and measures at analytic views. That's how you apply DAMA modeling: you preserve the agreed meaning from the entities to the published indicator.

The approved definition lays down what "valid contract" means at a date of cut. In the example, C2 is valid and C3 is suspended but both belong to C7: the result measure counts an active client. The path links rule, logs and result between Oracle AI Data Platform and Oracle Autonomous AI Lakehouse. The corporate semantics connect concepts, relationships and measures with the meaning approved by the business. ↗

Explanated example

Horizonite has an invoice of 100 currency units with two lines: 60 per service and 40 per installation. If the equipment links the header with the lines and adds the total header, it gets 200. The consultation correctly implements the union. The error is to add a measure of another granularity.

The solution is to add line amounts or first add to invoice level before matching with other sets. The steward document the metric. The modeling confirms the relationship; The technical team implements a consistent view of Oracle Autonomous AI Lakehouse's product. The case shows why assistant's demand "calculates revenues" without defining level, period and rule is insufficient.

F42 has a total header of 100 and two lines: Service by 60 and 40 Installation. When united with header and detail, the total 100 is repeated in both rows. To add that field production 200 and counts twice the invoice. add line amounts conserves 100. Check the grain of each measure before adding the result of a union. ↗

Common

• To design only from available columns. Business rules are omitted that today's columns may not represent.
• Using name as an unequivocal identifier. Different people can share and a person can change them.
• To believe that more context removes all ambiguity. The terms, relations and inferences proposed require accountability and review.

Decision exercise

Horizon needs to answer if the client authorized commercial communications at the date of a campaign. Just keeps a column consentimiento_actual. That's enough to answer? What should model represent?