Data Modeling – Column Data Classification

Data Modeling Column Data Classification When analyzing individual column data, at its most foundational level, column data can be classified by their fundamental use/characteristics.  Granted, when you start rolling up the structure into multiple columns, table structure and table relationship, then other classifications/behaviors, such as keys (primary and foreign), indexes, and distribution come into play.  … Continue reading Data Modeling – Column Data Classification

What is a Common Data Model (CDM)?

Data Model What is a Common Data Model (CDM)? A Common Data Model (CDM) is a share data structure designed to provide well-formed and standardized data structures within an industry (e.g. medical, Insurance, etc.) or business channel (e.g. Human resource management, Asset Management, etc.), which can be applied to provide organizations a consistent unified view … Continue reading What is a Common Data Model (CDM)?

Netezza / PureData – How to add a Foreign Key

DDL (Data Definition Language) Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics). Foreign Key (FK) Guidelines A primary key must be defined on the table … Continue reading Netezza / PureData – How to add a Foreign Key

Database – What is a foreign key?

Acronyms, Abbreviations, Terms, And Definitions Definition of a Foreign Key A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table. Facts About Foreign Keys Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which … Continue reading Database – What is a foreign key?

Database – What is a Primary Key?

Database Table What is a primary Key? What a primary key is depends, somewhat, on the database.  However, in its simplest form a primary key: Is a field (Column) or combination of Fields (columns) which uniquely identifies every row.Is an index in database systems which use indexes for optimizationIs a type of table constraintIs applied … Continue reading Database – What is a Primary Key?

Data Modeling – Fact Table Effective Practices

Database Table Here are a few guidelines for modeling and designing fact tables. Fact Table Effective Practices The table naming convention should identify it as a fact table. For example: Suffix Pattern: <<TableName>>_Fact <<TableName>>_F Prefix Pattern: FACT_<TableName>> F_<TableName>> Must contain a temporal dimension surrogate key (e.g. date dimension) Measures should be nullable – this has an … Continue reading Data Modeling – Fact Table Effective Practices

Data Modeling – Dimension Table Effective Practices

Database Table I've had these notes laying around for a while, so, I thought I consolidate them here.   So, here are few guidelines to ensure the quality of your dimension table structures. Dimension Table Effective Practices The table naming convention should identify it as a dimension table. For example: Suffix Pattern: <<TableName>>_Dim <<TableName>>_D Prefix … Continue reading Data Modeling – Dimension Table Effective Practices