Tag: data modeling 101

Technology - Why Business Intelligence (BI) needs a Semantic Data Model

Technology – Why Business Intelligence (BI) needs a Semantic Data Model

A semantic data model is a method of organizing and representing corporate data that reflects the meaning and relationships among data items. This method of organizing data helps end users access data autonomously using familiar business terms such as revenue, product, or customer via the BI (business intelligence) and other analytics tools. The use of a semantic model offers a consolidated, unified view of data across the business allowing end-users to obtain valuable insights quickly from large, complex, and diverse data sets. What is the purpose of semantic data modeling in BI and data virtualization? A semantic data model sits

Continue reading

Data Modeling – Fact Table Effective Practices

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 impact on aggregate functions (SUM, COUNT, MIN, MAX, and AVG, etc.) Dimension Surrogate keys (srky) should have a foreign key (FK) constraint Do not place the dimension processing in the fact jobs

Continue reading

Data Modeling – Dimension Table Effective Practices

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 Pattern: Dim_<TableName>> D_<TableName>> Have Primary Key (PK) assigned on table surrogate Key Audit fields – Type 1 dimensions should: Have a Created Date timestamp – When the record was initially created have a Last Update Timestamp – When was the record last updated Job Flow: Do not

Continue reading

Data Modeling – Database Table Field Ordering Effective Practices

Field order can help the performance on inserts and updates and, also, keeps developer and users from having to search entire table structure to be sure they have all the keys, etc. Table Field Ordering Distribution Field Or Fields, if no distribution field is set the first field will be used by default. Primary Key Columns (including Parent and Child key fields) Foreign Key Columns (Not Null) Not Null Columns Nullable Columns Created Date Timestamp Modified (or Last Updated) Date Timestamp Large text Fields Large binary Columns or Binary Field references Related References Data Modeling – What is Data Modeling?

Continue reading
%%footer%%