Data models define how data is connected, processed, and stored inside a system. Getting the model correct can enable a very powerful expression of the company’s metrics and enable meeting diverse business requirements. An effective data model also provides more flexibility and extensibility in the future.
Effective data models provide high speed retrieval, ease of data access, data integrity and data independence. Below are the 6 tips to create a more effective data model.
1. Create a derived column for Primary Key
When there are multiple columns involved in the primary key, create a derived column by combining columns into one column, separating with a special character such as ‘:’ or ‘-‘.
If there is no primary key column in the table, then combine the columns into one derived column by separating with a special character such as ‘:’ or ‘-‘.
2.Primary Key in parent table
Ensure that the parent table has a primary key, and that primary key is used in the relationship, otherwise data will be duplicated and dashboards contaminated with wrong data. For example, while joining a parent Products table and a child Product Transactions table, the Product table should have a primary key, and that primary key should be reflected in the SQL join statement.
3. Data validation by creating custom SQL
Each and every relationship created in an Elasticube should be validated by writing an inner join query of those 2 tables.
4. Create a bridge table to address a Many to Many Relationship
It is always better to create one bridge table to address the Many to Many relationships between two tables. Each and every relationship created in an Elasticube should be validated by writing an inner join query of those two tables. For example, in a relationship between Customers and Accounts, one Customer can have multiple accounts in a bank and an account could be associated with multiple Customers. In such a case, do not directly link the Customers table with the Transactions table.
5. Avoid cyclic relationships
Always avoid cyclic relationships in the Elasticube, as they may cause performance issues and can even break dashboards. Always ensure there is only one and only one route to get the data, by defining one or more relationships in the Elasticube.
6. Implement complex logic in the Elasticube
It is always best to perform complex calculations in the Elasticube rather than in a Dashboard. Keeping the logic in a single place provides ease of maintenance. Another advantage is better dashboard performance.
Not all the above tips are applicable in every situation, so keep the business context in mind before starting the modeling process. Aligning the model with the business requirements will always lead to superior results.