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

Importing Associations And Joins From A Database Schema in Denodo

A coworker recently asked a question as to whether denodo generated joins automatically from source RDBMS database schema.  After searching, a few snippets of information became obvious.  First, that the subject of inheriting join properties was broader than joins and needed to in modeling associations (joins on demand). Second, that there were some denodo design best practices to be considered to optimize associations.

Does Denodo Automatically Generate Joins From the Source System?

After some research, the short answer is no.

Can Denodo Inherit Accusations From A Logical Model?

The short answer is yes. 

Denodo bridges allow models to be passed to and from other modeling tools, it is possible to have the association build automatically, using the top-down approach design approach and importing a model, at the Interface View level, which is the topmost level of the top-down design process. 

However, below the Interface view level, associations and or joins are created manually by the developer.

Where Should Associations Be Created?

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

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.

Related References

Associations in Denodo

How to Check Linux Version?

While researching an old install for an upgrade system requirement compliance, I discovered that I b=need to validate which Linux version was installed.  So, here is a quick note on the command I used to validate which version of Linux was installed.

Command

  • cat /etc/os-release

Example Output of the command “os-release” file

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 between a reporting tool and the original database in order to assist end-users with reporting. It is the main entry point for accessing data for most organizations when they are running ad hoc queries or creating reports and dashboards. It facilitates reporting and improvements in various areas, such as:

  • No relationships or joins for end-users to worry about because they’ve already been handled in the semantic data model
  • Data such as invoice data, salesforce data, and inventory data have all been pre-integrated for end-users to consume.
  • Columns have been renamed into user-friendly names such as Invoice Amount as opposed to INVAMT.
  • The model includes powerful time-oriented calculations such as Percentage in sales since last quarter, sales year-to-date, and sales increase year over year.
  • Business logic and calculations are centralized in the semantic data model in order to reduce the risk of incorrect recalculations.
  • Data security can be incorporated. This might include exposing certain measurements to only authorized end-users and/or standard row-level security.

A well-designed semantic data model with agile tooling allows end-users to learn and understand how altering their queries results in different outcomes. It also gives them independence from IT while having confidence that their results are correct.

ETL Vs. EAI

Over recent years, business enterprises relying on accurate and consistent data to make informed decisions have been gravitating towards integration technologies. The subject of Enterprise Application Integration (EAI) and Extraction, Transformation & Loading (ETL) lately seems to pop up in most Enterprise Information Management conversations.

From an architectural perspective, both techniques share a striking similarity. However, they essentially serve different purposes when it comes to information management. We’ve decided to do a little bit of research and establish the differences between the two integration technologies.

Enterprise Application Integration

EAI is an integration framework that consists of technologies and services, allowing for seamless coordination of vital systems, processes, as well as databases across an enterprise.

Simply put, this integration technique simplifies and automates your business processes to a whole new level without necessarily having to make major changes to your existing data structures or applications.

With EAI, your business can integrate essential systems like supply chain management, customer relationship management, business intelligence, enterprise resource planning, and payroll. Well, the linking of these apps can be done at the back end via APIs or the front end GUI.

The systems in question might use different databases, computer languages, exist on different operating systems or older systems that might not be supported by the vendor anymore.

The objective of EAI is to develop a single, unified view of enterprise data and information, as well as ensure the information is correctly stored, transmitted, and reflected. It enables existing applications to communicate and share data in real-time.

Extraction, Transformation & Loading

The general purpose of an ETL system is to extract data out of one or more source databases and then transfer it to a target destination system for better user decision making. Data in the target system is usually presented differently from the sources.

The extracted data goes through the transformation phase, which involves checking for data integrity and converting the data into a proper storage format or structure. It is then moved into other systems for analysis or querying function.

With data loading, it typically involves writing data into the target database destination like data warehouse and operational data store.

ETL can integrate data from multiple systems. The systems we’re talking about in this case are often hosted on separate computer hardware or supported by different vendors.

Differences between ETL and EAI

EAI System

  • Retrieves small amounts of data in one operation and is characterized by a high number of transactions
  • EAI system is utilized for process optimization and workflow
  • The system does not require user involvement after it’s implemented
  • Ensures a bi-directional data flow between the source and target applications
  • Ideal for real-time business data needs
  • Limited data validation
  • Integrating operations is pull, push, and event-driven.

ETL System

  • It is a one-way process of creating a historical record from homogeneous or heterogeneous sources
  • Mainly designed to process large batches of data from source systems
  • Requires extensive user involvement
  • Meta-data driven complex transformations
  • Integrating operation is a pull, query-driven
  • Supports proper profiling and data cleaning
  • Limited messaging capabilities

Both integration technologies are an essential part of EIM, as they provide strong capabilities for business intelligence initiatives and reporting. They can be used differently and sometimes in mutual consolidation.

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.

denodo SQL Type Mapping

denodo 7.0 saves some manual coding when building the ‘Base Views’ by performing some initial data type conversions from ANSI SQL type to denodo Virtual DataPort data types. So, where is a quick reference mapping to show to what the denodo Virtual DataPort Data Type mappings are:

ANSI SQL types To Virtual DataPort Data types Mapping

ANSI SQL TypeVirtual DataPort Type
BIT (n)blob
BIT VARYING (n)blob
BOOLboolean
BYTEAblob
CHAR (n)text
CHARACTER (n)text
CHARACTER VARYING (n)text
DATElocaldate
DECIMALdouble
DECIMAL (n)double
DECIMAL (n, m)double
DOUBLE PRECISIONdouble
FLOATfloat
FLOAT4float
FLOAT8double
INT2int
INT4int
INT8long
INTEGERint
NCHAR (n)text
NUMERICdouble
NUMERIC (n)double
NUMERIC (n, m)double
NVARCHAR (n)text
REALfloat
SMALLINTint
TEXTtext
TIMESTAMPtimestamp
TIMESTAMP WITH TIME ZONEtimestamptz
TIMESTAMPTZtimestamptz
TIMEtime
TIMETZtime
VARBITblob
VARCHARtext
VARCHAR ( MAX )text
VARCHAR (n)text

ANSI SQL Type Conversion Notes

  • The function CAST truncates the output when converting a value to a text, when these two conditions are met:
  1. You specify a SQL type with a length for the target data type. E.g. VARCHAR(20).
  2. And, this length is lower than the length of the input value.
  • When casting a boolean to an integertrue is mapped to 1 and false to 0.

Related References

denodo 7.0 Type Conversion Functions