Data Model and Stable IDs for BI

The core of the work was turning Arquimadera’s cost data into a dimensional model with stable IDs. Previously, categories, subcategories, suppliers, brands, product types, currencies, units, colors, and products were entered as repeated free text across different cells. Each entity received a unique, permanent identifier, such as CA01-CA09, PR001-PR036, and MA001-MA038, along with join tables for many-to-many relationships between suppliers, brands, subcategories, and product types. The consolidation was documented through 40 tracked revisions.

This foundation made it possible to build the forms without duplicate records or inconsistent references. The Supplier Registration Form supports four flows: adding a new supplier, adding a new brand, adding a new product type, and updating the list. The Product Form supports product registration, search, and updates, status and scope toggles, and bulk creation. In each flow, the system can check whether an entity already exists before inserting a new record because every entity has a reliable reference ID.

The same model also stabilized other Arquimadera automations. The quotation tool, the ACTIVE/DISCONTINUED status control, and price updates all relied on text matching. A small variation in a supplier or brand name could break a formula or connection. With ID-based references, those automations no longer depend on how a name is typed or where it appears in the data.

The result is a single structural source of truth for Arquimadera’s BI. A supplier, brand, or product type always refers to the same entity, wherever it is used. This reduces rework, removes an entire class of silent text-matching errors, and prepares the foundation for a future migration to a relational database without requiring the data to be restructured from scratch.