Denodo Source Database Permissions For SQL Server

Denodo does not publish a concise list of what permissions denodo needs in a source database.  So, I thought I would document what is required for a SQL Server data source for denodo.

Source database Dendo Access Permissions

Assuming there is no need to write back to the source DB, the data source connection permissions in a SQL Server the denodo source database should be:

  • Read And View Definitions access
  • Create tables (for data movement optimization), and
  • Access to getSchemas()

Why Create Table, Not Just Temporary Table?

The reason by denodo needs create tables permissions, rather than just create temporary tables permissions, is that temporary tables are only valid for the same connection, and Data Movement makes use of multiple connections, so we can not rely on the temporary table mechanism.

What if you need to Write back capabilities to data sources connection?  

If you had a use case where users would need to write back to the SQL Server database via Denodo, the Insert/Update permissions would need to be added to the list above.

Denodo Reserved words – Virtual Query Language (VQL)

Here s another useful reference, which came from a customer question.  This customer was trying to create a denodo 7.0 VDP database, which denodo Virtual DataPort (VDP) would not except and was wondering why.  After a little research, it turned out to be VDP database name was a denodo reserved word.  While I did find the name finding the Denodo Reserved words for Virtual Query Language (VQL) were incorporated in the text of page which did not immediately return when performing Google Search for “denodo reserved words.”  Rather, the ‘List of Reserved Words’ for Denodo Virtual Query Language (VQL) was buried at the end of the Denodo VQL Syntax Conventions.

So, I thought I would pull the list of reserved words into a separate page and post the list of denodo VQL reserved words in this site for the next time some asks me for the list of Denodo Virtual Query Language (VQL) reserved words.

Denodo List Of Reserved VQL Words

  • ADD
  • ALL
  • ALTER
  • AND
  • ANY
  • ARN
  • AS
  • ASC
  • BASE
  • BOTH
  • CALL
  • CASE
  • CONNECT
  • CONTEXT
  • CREATE
  • CROSS
  • CURRENT_DATE | CURRENT_TIMESTAMP
  • CUSTOM
  • DATABASE
  • DEFAULT
  • DESC
  • DF
  • DISTINCT
  • DROP
  • EXISTS
  • FALSE
  • FETCH
  • FLATTEN
  • FROM
  • FULL
  • GRANT
  • GROUP BY
  • GS
  • HASH
  • HAVING
  • HTML
  • IF
  • INNER
  • INTERSECT,INTO
  • IS
  • JDBC
  • JOIN
  • LDAP
  • LEADING
  • LEFT
  • LIMIT
  • MERGE
  • MINUS
  • MY
  • NATURAL
  • NESTED
  • NOS
  • NOT
  • NULL
  • OBL
  • ODBC
  • OF
  • OFF
  • OFFSET
  • ON
  • ONE
  • OPT
  • OR
  • ORDER BY
  • ORDERED
  • PRIVILEGES
  • READ
  • REVERSEORDER
  • REVOKE
  • RIGHT
  • ROW
  • SELECT
  • SWAP
  • TABLE
  • TO
  • TRACE
  • TRAILING
  • TRUE
  • UNION
  • USER
  • USING
  • VIEW
  • WHEN
  • WHERE
  • WITH
  • WRITE
  • WS
  • ZERO

Denodo Related References

Denodo Virtual Query Language (VQL) Syntax Conventions > Denodo Reserved Words

ITPilot List of Reserved Words

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

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

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.