Denodo Model Best Practices For Creation of Associations

What Are Denodo Associations?

In denodo associations follow the same concept as modeling tools, which can be described as an ‘on-demand join.’

Where Should Associations Be Created In the Denodo Model?

You don’t necessarily need to define an Association at every level; usually, the best practice is to apply associations at the following points:

  • On final views published for data consumers, indicating relationships between related views; Especially, on published web services.
  • On the component views below, any derived view that brings together disparate (dissimilar) data sources.  The associations should be defined as Referential Constraints whenever appropriate to aid the optimization engine.
  • On the component views below, any derived view that joins a “Base View from Query” with standard views, since Base Views from Query cannot be rewritten by the denodo optimization engine.  Often Base Views from Query create performance bottlenecks.

These best practices should cover the majority scenarios; beyond these guidelines, it is best to take an ad-hoc approach to create Associations when you see a specific performance/optimization.

Why Are Associations important in Denodo?

In a nutshell, associations performance and the efficiency of the denodo execution optimizer along with other model metadata, such as:  

  • The SQL of the view(s)
  • Table metadata (Table Keys {PK, FK), Virtual Partitions…etc.)
  • Data statistics, which are used by the Cost Based Optimizer (CBO)

Related References

Associations in Denodo

Personas Vs. Roles – What Is The Difference?

Personas and roles are user modeling approaches that are applied in the early stages of system development or redesign. They drive the design decision and allows programmers and designers to place everyday user needs at the forefront of their system development journey in a user-centered design approach.

Personas and user roles help improve the quality of user experience when working with products that require a significant amount of user interaction. But there is a distinct difference between technology personas vs. roles. What then exactly is a persona? What are user roles in system development? And, how does persona differ from user roles?

Let’s see how these two distinct, yet often confused, user models fit in a holistic user-centered design process and how you can leverage them to identify valuable product features.

Technology Personas Vs. Roles – The Most Relevant Way to Describe Users

In software development, a user role describes the relationship between a user type and a software tool. It is generally the user’s responsibility when using a system or the specific behavior of a user who is participating in a business process. Think of roles as the umbrella, homogeneous constructs of the users of a particular system. For instance, in an accounting system, you can have roles such as accountant, cashier, and so forth.

However, by merely using roles, system developers, designers, and testers do not have sufficient information to conclusively make critical UX decisions that would make the software more user-centric, and more appealing to its target users.

This lack of understanding of the user community has led to the need for teams to move beyond role-based requirements and focus more on subsets of the system users. User roles can be refined further by creating “user stand-ins,” known as personas. By using personas, developers and designers can move closer to the needs and preferences of the user in a more profound manner than they would by merely relying on user roles.

In product development, user personas are an archetype of a fictitious user that represents a specific group of your typical everyday users. First introduced by Alan Cooper, personas help the development team to clearly understand the context in which the ideal customer interacts with a software/system and helps guide the design decision process.

Ideally, personas provide team members with a name, a face, and a description for each user role. By using personas, you’re typically personalizing the user roles, and by so doing, you end up creating a lasting impression on the entire team. Through personas, team members can ask questions about the users.

The Benefits of Persona Development

Persona development has several benefits, including:

  • They help team members have a consistent understanding of the user group.
  • They provide stakeholders with an opportunity to discuss the critical features of a system redesign.
  • Personas help designers to develop user-centric products that have functions and features that the market already demands.
  • A persona helps to create more empathy and a better understanding of the person that will be using the end product. This way, the developers can design the product with the actual user needs in mind.
  • Personas can help predict the needs, behaviors, and possible reactions of the users to the product.

What Makes Up a Well-Defined Persona?

Once you’ve identified user roles that are relevant to your product, you’ll need to create personas for each. A well-defined persona should ideally take into consideration the needs, goals, and observed behaviors of your target audience. This will influence the features and design elements you choose for your system.

The user persona should encompass all the critical details about your ideal user and should be presented in a memorable way that everyone in the team can identify with and understand. It should contain four critical pieces of information.

1. The header

The header aid in improving memorability and creating a connection between the design team and the user. The header should include:

  • A fictional name
  • An image, avatar or a stock photo
  • A vivid description/quote that best describes the persona as it relates to the product.

2. Demographic Profile

Unlike the name and image, which might be fictitious, the demographic profile includes factual details about the ideal user. The demographic profile includes:

  • Personal background: Age, gender, education, ethnicity, persona group, and family status
  • Professional background: Occupation, work experience, and income level.
  • User environment. It represents the social, physical, and technological context of the user. It answers questions like: What devices do the user have? Do they interact with other people? How do they spend their time?
  • Psychographics: Attitudes, motivations, interests, and user pain points.

3. End Goal(s)

End goals help answer the questions: What problems or needs will the product solution to the user? What are the motivating factors that inspire the user’s actions?

4. Scenario

This is a narrative that describes how the ideal user would interact with your product in real-life to achieve their end goals. It should explain the when, the where, and the how.

Conclusion

For a truly successful user-centered design approach, system development teams should use personas to provide simple descriptions of key user roles. While a distinct difference exists in technology personas vs. roles, design teams should use the two user-centered design tools throughout the project to decide and evaluate the functionality of their end product. This way, they can deliver a useful and usable solution to their target market.

Database – What is a foreign key?

Acronyms, Abbreviations, Terms, And Definitions, DDL (Data Definition Language), 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 establishing a link/relationship between the table keys
  • Foreign keys are not enforced by all RDBMS
  • The concept of referential integrity is derived from foreign key theory
  • Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
  • A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
  • The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys

Cascade

  • The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.

No Action

  • Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records.   No Action does not mean to suppress the foreign-key constraint.

Set null

  • The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.

Set default

  • The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record

Related References

Database – What is a Composite Primary Key?

Database Table
Database Table

What is a Composite Primary Key?

A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it.  Like a Primary Key what a composite Primary Key is depends on the database.  Essentially a Composite Primary Key:

  • Is a combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And may define parent-Child relationship between tables

Related References

Database – What is a Primary Key?

Database Table
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 optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And, depending on the data model can, define parent-Child relationship between tables

Related References

Data Modeling – Dimension Table Effective Practices

Database Table
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 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 place the dimension processing in the fact jobs.
  • Every Dimension should have a Zero (0), Unknown, row
  • Fields should be ‘NOT NULL’ replacing nulls with a zero (0) numeric and integer type fields or space ( ‘ ‘ ) for Character type files.
  • Keep dimension processing outside of the fact jobs

Related References