Tag: Data Modeling

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

What are the Factor Affecting the Selection of Data Warehouse Naming Convention?

The primary factors affecting the choices in the creation of Data Warehouse (DW) naming convention policy standards are the type of implementation, pattern of the implementation, and any preexisting conventions. Type of implementation The type of implementation will affect your naming convention choices. Basically, this boils down to, are you working with a Commercial-Off-The-Shelf (COTS) data warehouse or doing a custom build? Commercial-Off-The-Shelf (COTS) If it is a Commercial-Off-The-Shelf (COTS) warehouse, which you are modifying and or enhancing, then it is very strongly recommended that you conform to the naming conventions of the COTS product.  However, you may want to

Continue reading

The Value of Data Indicator Flags

I cannot count the times, which using a flag (also, called an indicator) is described as a nice to have in database table design, at least, until the code runs into complexity and/or performance challenges. When designing your data models, ETL’s, and reports it is useful to consider how indicator flags can help.  While indicator flags are, normally, binary in nature, such as True/False or Yes/No, but indicator flags don’t always need to be binary. How indicator flags can help your processes and reporting: Provide an equijoin for complex business rules, which can otherwise result ‘Not In List’, ‘In list’,

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