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

Denodo Data Virtualization Project Roles

A Denodo virtualization project typically classifies the project duties of the primary implementation team into four Primary roles.

Denodo Data Virtualization Project Roles

  • Data Virtualization Architect
  • Denodo Platform Administrator
  • Data Virtualization Developer
  • Denodo Platform Java Programmer
  • Data Virtualization Internal Support Team

Role To Project Team Member Alignment

While the denodo project is grouped into security permissions and a set of duties, it is import to note that the assignment of the roles can be very dynamic as to their assignment among project team members.  Which team member who performs a given role can change the lifecycle of a denodo project.  One team member may hold more than one role at any given time or acquire or lose roles based on the needs of the project.

Denodo virtualization Project Roles Duties

Data Virtualization Architect

The knowledge, responsibilities, and duties of a denodo data virtualization architect, include:

  • A Deep understanding of denodo security features and data governance
  • Define and document5 best practices for users, roles, and security permissions.
  • Have a strong understanding of enterprise data/information assets
  • Defines data virtualization architecture and deployments
  • Guides the definition and documentation of the virtual data model, including, delivery modes, data sources, data combination, and transformations

Denodo Platform Administrator

The knowledge, responsibilities, and duties of a Denodo Platform Administrator, Include:

  • Denodo Platform Installation and maintenance, such as,
    • Installs denodo platform servers
    • Defines denodo platform update and upgrade policies
    • Creates, edits, and removes environments, clusters, and servs
    • Manages denodo licenses
    • Defines denodo platform backup policies
    • Defines procedures for artifact promotion between environments
  • Denodo platform configuration and management, such as,
    • Configures denodo platform server ports
    • Platform memory configuration and Java Virtual Machine (VM) options
    • Set the maximum number of concurrent requests
    • Set up database configuration
      • Specific cache server
      • Authentication configuration for users connecting to denodo platform (e.g., LDAP)
      • Secures (SSL) communications connections of denodo components
      • Provides connectivity credentials details for clients tools/applications (JDBC, ODBC,,,etc.)
      • Configuration of resources.
    • Setup Version Control System (VCS) configuration for denodo
    • Creates new Virtual Databases
    • Create Users, roles, and assigns privileges/roles.
    • Execute diagnostics and monitoring operations, analyzes logs and identifies potentials issues
    • Manages load balances variables

Data Virtualization Developer

The Data Virtualization Developer role is divided into the following sub-roles:

  • Data Engineer
  • Business Developer
  • Application Developer

the knowledge, responsibilities, and duties of a Denodo Data Virtualization Developer, by sub-role, Include:

Data Engineer

The denodo data engineer’s duties include:

  • Implements the virtual data model construction view by
    • Importing data sources and creating base views, and
    • Creating derived views applying combinations and transformations to the datasets
  • Writes documentation, defines testing to eliminate development errors before code promotion to other environments

Business Developer

The denodo business developer’s duties include:

  • Creates business vies for a specific business area from derived and/or interface views
  • Implements data services delivery
  • Writes documentation

Application Developer

The denodo application developer’s duties include:

  • Creates reporting vies from business views for reports and or datasets frequently consumed by users
  • Writes documentation

Denodo Platform Java Programmer

The Denodo Platform Java Programmer role is an optional, specialized, role, which:

  • Creates custom denodo components, such as data sources, stored procedures, and VDP/iTPilot functions.
  • Implements custom filters in data routines
  • Tests and debugs any custom components using Denodo4e

Data Virtualization Internal Support Team

The denodo data virtualization internal support team’s duties include

  • Access to and knowledge of the use and trouble of developed solutions
  • Tools and procedures to manage and support project users and developers

denodo Virtualization – Useful Links

Here are some denodo Virtualization references, which may be useful.

Reference Name Link
denodo Home Page https://www.denodo.com/en/about-us/our-company
denodo Platform 7.0 Documentation https://community.denodo.com/docs/html/browse/7.0/
denodo Knowledge Base and Best Practices https://community.denodo.com/kb/
denodo Tutorials https://community.denodo.com/tutorials/
denodo Express 7.0 Download https://community.denodo.com/express/download
Denodo Virtual Data Port (VDP) https://community.denodo.com/kb/download/pdf/VDP%20Naming%20Conventions?category=Operation
JDBC / ODBC drivers for Denodo https://community.denodo.com/drivers/
Denodo Governance Bridge – User Manual https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual

Related References

Using Logical Data Lakes

Today, data-driven decision making is at the center of all things. The emergence of data science and machine learning has further reinforced the importance of data as the most critical commodity in today’s world. From FAAMG (the biggest five tech companies: Facebook, Amazon, Apple, Microsoft, and Google) to governments and non-profits, everyone is busy leveraging the power of data to achieve final goals. Unfortunately, this growing demand for data has exposed the inefficiency of the current systems to support the ever-growing data needs. This inefficiency is what led to the evolution of what we today know as Logical Data Lakes.

What Is a Logical Data Lake?

In simple words, a data lake is a data repository that is capable of storing any data in its original format. As opposed to traditional data sources that use the ETL (Extract, Transform, and Load) strategy, data lakes work on the ELT (Extract, Load, and Transform) strategy. This means data does not have to be first transformed and then loaded, which essentially translates into reduced time and efforts. Logical data lakes have captured the attention of millions as they do away with the need to integrate data from different data repositories. Thus, with this open access to data, companies can now begin to draw correlations between separate data entities and use this exercise to their advantage.

Primary Use Case Scenarios of Data Lakes

Logical data lakes are a relatively new concept, and thus, readers can benefit from some knowledge of how logical data lakes can be used in real-life scenarios.

To conduct Experimental Analysis of Data:

  • Logical data lakes can play an essential role in the experimental analysis of data to establish its value. Since data lakes work on the ELT strategy, they grant deftness and speed to processes during such experiments.

To store and analyze IoT Data:

  • Logical data lakes can efficiently store the Internet of Things type of data. Data lakes are capable of storing both relational as well as non-relational data. Under logical data lakes, it is not mandatory to define the structure or schema of the data stored. Moreover, logical data lakes can run analytics on IoT data and come up with ways to enhance quality and reduce operational cost.

To improve Customer Interaction:

  • Logical data lakes can methodically combine CRM data with social media analytics to give businesses an understanding of customer behavior as well as customer churn and its various causes.

To create a Data Warehouse:

  • Logical data lakes contain raw data. Data warehouses, on the other hand, store structured and filtered data. Creating a data lake is the first step in the process of data warehouse creation. A data lake may also be used to augment a data warehouse.

To support reporting and analytical function:

  • Data lakes can also be used to support the reporting and analytical function in organizations. By storing maximum data in a single repository, logical data lakes make it easier to analyze all data to come up with relevant and valuable findings.

A logical data lake is a comparatively new area of study. However, it can be said with certainty that logical data lakes will revolutionize the traditional data theories.

Related References

A 720-Degree View of the Customer

The 360-degree view of the consumer is a well-explored concept, but it is not adequate in the digital age. Every firm, whether it is Google or Amazon, is deploying tools to understand customers in a bid to serve them better. A 360-degree view demanded that a company consults its internal data to segment customers and create marketing strategies. It has become imperative for companies to look outside their channels, to platforms like social media and reviews to gain insight into the motivations of their customers. The 720-degree view of the customer is further discussed below.

What is the 720-degree view of the customer?

A 720-degree view of the customer refers to a three-dimensional understanding of customers, based on deep analytics. It includes information on every customer’s level of influence, buying behavior, needs, and patterns. A 720-degree view will enable retailers to offer relevant products and experiences and to predict future behavior. If done right, this concept should assist retailers leverage on emerging technologies, mobile commerce, social media, and cloud-based services, and analytics to sustain lifelong customer relationships

What Does a 720-Degree View of the Customer Entail?

Every business desires to cut costs, gain an edge over its competitors, and grow their customer base. So how exactly will a 720-degree view of the customer help a firm advance its cause?

Social Media

Social media channels help retailers interact more effectively and deeply with their customers. It offers reliable insights into what customers would appreciate in products, services, and marketing campaigns. Retailers can not only evaluate feedback, but they can also deliver real-time customer service. A business that integrates its services with social media will be able to assess customer behavior through tools like dislikes and likes. Some platforms also enable customers to buy products directly.

Customer Analytics


Customer analytics will construct more detailed customer profiles by integrating different data sources like demographics, transactional data, and location. When this internal data is added to information from external channels like social media, the result is a comprehensive view of the customer’s needs and wants. A firm will subsequently implement more-informed decisions on inventory, supply chain management, pricing, marketing, customer segmentation, and marketing. Analytics further come in handy when monitoring transactions, personalized services, waiting times, website performance.

Mobile Commerce

The modern customer demands convenience and device compatibility. Mobile commerce also accounts for a significant amount of retail sales, and retailers can explore multi-channel shopping experiences. By leveraging a 720-degree view of every customer, firms can provide consumers with the personalized experiences and flexibility they want. Marketing campaigns will also be very targeted as they will be based on the transactional behaviors of customers. Mobile commerce can take the form of mobile applications for secure payment systems, targeted messaging, and push notifications to inform consumers of special offers. The goal should be to provide differentiated shopper analytics.

Cloud

Cloud-based solutions provide real-time data across multiple channels, which illustrates an enhanced of the customer. Real-time analytics influence decision-making in retail and they also harmonize the physical and retail digital environments. The management will be empowered to detect sales trends as transactions take place.

The Importance of the 720-Degree Customer View

Traditional marketers were all about marketing to groups of similar individuals, which is often termed as segmentation. This technique is, however, giving way to the more effective concept of personalized marketing. Marketing is currently channeled through a host of platforms, including social media, affiliate marketing, pay-per-click, and mobile. The modern marketer has to integrate the information from all these sources and match them to a real name and address. Companies can no longer depend on a fragmented view of the customer, as there has to be an emphasis on personalization. A 720-degree customer view can offer benefits like:

Customer Acquisition

Firms can improve customer acquisition by depending on the segment differences revealed from a new database of customer intelligence. Consumer analytics will expose any opportunities to be taken advantage of while external data sources will reveal competitor tactics. There are always segment opportunities in any market, which are best revealed by real-time consumer data.

Cutting Costs

Marketers who rely on enhanced digital data can contribute to cost management in a firm. It takes less investment to serve loyal and satisfied consumers because a firm is directing addressing their needs. Technology can be used to set customized pricing goals and to segment customers effectively.

New Products and Pricing

Real-time data, in addition to third-party information, have a crucial impact on pricing. Only firms with a robust and relevant competitor and customer analytics and data can take advantage of this importance. Marketers with a 720-degree view of the consumer across many channels will be able to utilize opportunities for new products and personalized pricing to support business growth

Advance Customer Engagement

The first 360 degrees include an enterprise-wide and timely view of all consumer interactions with the firm. The other 360 degrees consists of the customer’s relevant online interactions, which supplements the internal data a company holds. The modern customer is making their buying decisions online, and it is where purchasing decisions are influenced. Can you predict a surge in demand before your competitors? A 720-degree view will help you anticipate trends while monitoring the current ones.

720-degree Customer View and Big Data

Firms are always trying to make decision making as accurate as possible, and this is being made more accessible by Big Data and analytics. To deliver customer-centric experiences, businesses require a 720-degree view of every customer collected with the help of in-depth analysis.

Big Data analytical capabilities enable monitoring of after-sales service-associated processes and the effective management of technology for customer satisfaction. A firm invested in being in front of the curve should maintain relevant databases of external and internal data with global smart meters. Designing specific products to various segments is made easier with the use of Big Data analytics. The analytics will also improve asset utilization and fault prediction. Big Data helps a company maintain a clearly-defined roadmap for growth

Conclusion

It is the dream of every enterprise to tap into customer behavior and create a rich profile for each customer. The importance of personalized customer experiences cannot be understated in the digital era. The objective remains to develop products that can be advertised and delivered to customers who want them, via their preferred platforms, and at a lower cost. 

10 Denodo Data Virtualization Use Cases

Data virtualization is a data management approach that allows retrieving and manipulation of data without requiring technical data details like where the data is physically located or how the data is formatted at the source.
Denodo is a data virtualization platform that offers more use cases than those supported by many data virtualization products available today. The platform supports a variety of operational, big data, web integration, and typical data management use cases helpful to technical and business teams.
By offering real-time access to comprehensive information, Denodo helps businesses across industries execute complex processes efficiently. Here are 10 Denodo data virtualization use cases.

1. Big data analytics

Denodo is a popular data virtualization tool for examining large data sets to uncover hidden patterns, market trends, and unknown correlations, among other analytical information that can help in making informed decisions. 

2. Mainstream business intelligence and data warehousing

Denodo can collect corporate data from external data sources and operational systems to allow data consolidation, analysis as well as reporting to present actionable information to executives for better decision making. In this use case, the tool can offer real-time reporting, logical data warehouse, hybrid data virtualization, data warehouse extension, among many other related applications. 

3. Data discovery 

Denodo can also be used for self-service business intelligence and reporting as well as “What If” analytics. 

4. Agile application development

Data services requiring software development where requirements and solutions keep evolving via the collaborative effort of different teams and end-users can also benefit from Denodo. Examples include Agile service-oriented architecture and BPM (business process management) development, Agile portal & collaboration development as well as Agile mobile & cloud application development. 

5. Data abstraction for modernization and migration

Denodo also comes in handy when reducing big data sets to allow for data migration and modernizations. Specific applications for this use case include, but aren’t limited to data consolidation processes in mergers and acquisitions, legacy application modernization and data migration to the cloud.

6. B2B data services & integration

Denodo also supports big data services for business partners. The platform can integrate data via web automation. 

7. Cloud, web and B2B integration

Denodo can also be used in social media integration, competitive BI, web extraction, cloud application integration, cloud data services, and B2B integration via web automation. 

8. Data management & data services infrastructure

Denodo can be used for unified data governance, providing a canonical view of data, enterprise data services, virtual MDM, and enterprise business data glossary. 

9. Single view application

The platform can also be used for call centers, product catalogs, and vertical-specific data applications. 

10. Agile business intelligence

Last but not least, Denodo can be used in business intelligence projects to improve inefficiencies of traditional business intelligence. The platform can develop methodologies that enhance outcomes of business intelligence initiatives. Denodo can help businesses adapt to ever-changing business needs. Agile business intelligence ensures business intelligence teams and managers make better decisions in shorter periods.

With over two decades of innovation, applications in 35+ industries and multiple use cases discussed above, it’s clear why Denodo a leading platform in data virtualization.

DataStage – Timestamp from Datetime

Timestamp from date is one of those data type conversions, which I occasionally have to do in DataStage but can never seem to remember. So, I thought I would write this quick post to document the data type conversion code, which is easy, once I finally remember how to do it again. 

The TimestampFromDateTime Function

I use the TimestampFromDateTime(%date%,%time%) function to do this data type conversion. I’m sure there are other ways to achieve the result, but I find this method clean and easy to perform. The TimestampFromDateTime(%date%,%time%) function is in the Functions > Date & Time menu.

To populate the function, you need only add your date field on use ’00:00:00’ as your time element

Time Element example

TimestampFromDateTime(<<Date Field Here>>, ’00:00:00′)

Example transformer code With Date Field

TimestampFromDateTime(Lnk_10.POSTED_DATE, ’00:00:00′)