Every table has a primary key that consists of a unique value that identifies each distinct record (or row) in the table. There are two types of primary keys:
– Simple Key
– Compound Key
This type of key requires only one column to uniquely identify each record within a table.
This type of key requires two or more columns to uniquely identify each record within a table.
The type of key you use for a table depends on the nature of the data itself. The specific requirements of your business may also influence what type of key is used.
Simple keys are generally easier to work with in a data warehouse than compound keys because they require less storage space and they allow for simpler SQL. Compound keys tend to increase SQL complexity and query time and require more storage space. However, compound keys are often present in source system data. In such cases, retaining compound keys provides for a less complex and more efficient ETL process.
There are advantages and disadvantages to both types of table keys, so you have to consider which key structure best meets your needs when designing the data warehouse schema.
This is the major thing that you need to know before you start work on developing reports/identifying the root cause of the problems by looking at the report SQLs. Keep note on it — which helps you at any point of time..
Hope it helps for the day!!
Have a good Day!!