Technology – Distinct Vs Group By in SQL Server

While it is tempting to select the fastest method, the truth is that DISTINCT is often the fastest option. There are advantages and disadvantages to each, but using both methods is not always better. Luckily, modern tools make this comparison easy. Tools like dbForge SQL Complete can calculate aggregate functions and DISTINCT values in a … More Technology – Distinct Vs Group By in SQL Server

How Long Can A Description In Denodo Be?

Denodo descriptions for base views, Derived views, and fields have a limit of 4,000 characters.  If you exceed this limit, you will not be able to save the view in denodo, and the offending description will turn red. Denodo Developer Note The limit is applied to each description; meaning 4,000 characters in the view description … More How Long Can A Description In Denodo Be?

Technology – Can A Hyperlink Be Added To A Denodo View Description?

In a recent meeting, we are running up against the maximum length of the view descriptions of 4,000 characters.  When someone asked the question “Can a hyperlink be added to a denodo view description?” So, I did some testing. Denodo Virtual Data Port (VDP)  in Denodo Virtual DataPort (VDP) and the short answer is yes.  … More Technology – Can A Hyperlink Be Added To A Denodo View Description?

Technology – What Are The SQL Minus and Except Clauses

Structured Query Language (SQL) is the de-facto query language used in most database management systems (DBMS) such as Oracle and Microsoft SQL Server. This domain-specific language is used in programming to query and return the desired data from a database. We use SQL to write queries that declare what data to expect from a dataset … More Technology – What Are The SQL Minus and Except Clauses

Technology – Denodo VQL To Get A List Of Cached View Names

Hello, this is a quick code snippet of a Denodo VQL (Denodo Virtual Query Language) to pull a list of cached view names which can be useful in pulling list of cached views. It’s not a complicated thing, but now that I’ve bothered to look it up on putting this note here mostly for me … More Technology – Denodo VQL To Get A List Of Cached View Names

Technology – Denodo View Performance Best Practice

Since I have been doing more training of beginning users of the Denodo, there have been a lot of questions around performance best practices and optimization. This article is a quick summary of some of the high points of the Donodo documentation, which are typically useful. However, I would like to point out that the … More Technology – Denodo View Performance Best Practice

Denodo – Script To Alter Association Metadata Description

Here is a Denodo Virtual Query Language (VQL) code snippet, which I use to populate the metadata description of Denodo Associations in Virtual DataPort (VDP).  This snippet identifies Denodo Association without a Metadata Description and fills Metadata Description with a simple explanation of what the Denodo Association’s purpose. VQL To Generate Association Alter Statements Description … More Denodo – Script To Alter Association Metadata Description

Denodo – Find Views With Missing Metadata Description

Here is a Denodo code snippet, which I use to identify views that do not have the metadata description populated.  Granted, this is a simple thing, but I have found it useful when validating that all Denodo views have been described in Virtual DataPort (VDP). SELECT *FROM GET_VIEWS()WHEREinput_database_name = ‘Database_Name‘And len(description) = 0;

SQL Server Length Function Equivalent

The purpose of the Length function in SQL The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS). The LENGTH Function Syntax LENGTH(string) LENGTH Function Notes If the input string is empty, the LENGTH returns 0. If the input string is NULL, … More SQL Server Length Function Equivalent

Obtaining The Difference Between Timestamps Or Microsecond Fields

The topic of how to obtain the difference or duration from timestamps having milliseconds has come up when working in Denodo a few times. So, after having clarified for different developers I thought I’d create this quick reference to clarify the subject little bit. To get the difference with the duration you must subtract the … More Obtaining The Difference Between Timestamps Or Microsecond Fields

How To connect DBeaver To Denodo Using JDBC

Here is a quick outline of how to configure DBeaver to connect to denodo using JDBC and a few references with other helpful information. Copy jdbc driver from denodo to dbeaver Copy from denodo engine server location: <DENODO_HOME>/tools/client-drivers/jdbc/denodo-vdp-jdbcdriver.jar to Dbeaver location: C:\Program Files\DBeaver\plugins Configure DBeaver Driver In DBeaver, navigate to Database > Driver Manager.  Choose … More How To connect DBeaver To Denodo Using JDBC

How to save Denodo Virtual Data Port (VDP) VQL Shell results

In a recent project, I was asked by a new user of Denodo Virtual Data Port (VDP) how to save denodo VDP VQL Shell results. So, here is a simple outline of the process of exporting the VQL Shell results: Execute the VQL Shell SQL Click on the ‘Save’ button in the ‘Results’ tab to … More How to save Denodo Virtual Data Port (VDP) VQL Shell results

Denodo Modeling Associations

Denodo associations, referential constraints are part art and part science. The importance of both primary keys and associations and their effect on the denotative optimizer is hard to overstate. Combining appropriately applying primary keys and associations based on actual view use is an essential element in tuning denodo and getting the denodo optimizer to provide … More Denodo Modeling Associations

How to save Denodo Virtual Data Port (VDP) view results

In a recent project, I was asked by a new user of Denodo Virtual Data Port (VDP) how to save denodo VDP view results. So, here is a simple outline of the process of exporting the view results: Execute the view Click on the ‘Save’ button in the Results tab to save results A dialog … More How to save Denodo Virtual Data Port (VDP) view results

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 … More Denodo Source Database Permissions For SQL Server

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 accept, 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 … More Denodo Reserved words – Virtual Query Language (VQL)

What Did The Denodo Read Privilege Change To In Denodo 7.0?

Well, the question was asked today by a customer, what happened to the “Read” denodo user privilege between version 6 and version 7.  So, I had to a little research and thought to write it down here, in case anyone else happens to ask or wants to know. The Denodo 6.0 “Read” privilege is now … More What Did The Denodo Read Privilege Change To In Denodo 7.0?

Technology – Denodo Security Enforcement

As the Virtual DataPort Administration Guide, explains in the section “Types of Access Rights” section, on VDP databases, views, rows, and columns. The denodo role-based access mechanism controls how and what a user or user role can use in the virtual layer, including the data catalog. Important Denodo Security Notes Consumer security authorization is imposed … More Technology – Denodo Security Enforcement

Denodo Data Consolidation & Denormalization

As the data modeling process in denodo moves through the conceptual layers of the data warehouse, there is an evolution of the data structure and their associated metadata. The Base Layer As the modeling process begins the base layer is the ingestion layer, where the source system data structures are recreated in denodo and field … More Denodo Data Consolidation & Denormalization

Technology – Denodo Data Catalog Roles

The denodo catalog provides the data governance and self-service capabilities to supplement the denodo Virtual DataPort (VDP) core capabilities. Six roles provide the ability to assign or deny capabilities with the denodo data catalog and supplement the database, row, and column security and permissions of denodo Virtual DataPort (VDP). The Tasks The Roles Can Perform … More Technology – Denodo Data Catalog Roles

Denodo Model Best Practices For Creation of Associations

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 … More Denodo Model Best Practices For Creation of Associations

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 … More Importing Associations And Joins From A Database Schema in Denodo

Technology – 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 … More Technology – Denodo SQL Type Mapping

Technology – Which Version Control Systems Are Supported By Denodo Virtualization?

Using Version Control is a denodo Virtual DataPort (VDP) recommended best practice. Version 8.0 of denodo virtualization supports three Version Control Systems (VCS): Microsoft Team Foundation Server (TFS) 2010 or later Apache Subversion (1.7), and Git and Git derivative version control systems (e.g., GitLab, GitHub, OpenBSD, ) User Manuals > Virtual DataPort Administration Guide > … More Technology – Which Version Control Systems Are Supported By Denodo Virtualization?

Technology – 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 … More Technology – Denodo Data Virtualization Project Roles

Technology – Denodo Virtual Dataport (VDP) Naming Convention Guidance

Denodo provides some general Virtual Dataport naming convention recommendations and guidance.  First, there is the general guidance for basic Virtual Dataport object types, and secondly, more detailed naming guidance recommends.       Denodo Basic Virtual Dataport (VDP) Object Prefix Recommendations Associations Prefix: a_{name} Base Views Prefix: bv_{SystemName}_{TableName} Data Sources Prefix: ds_{name} Integration View Prefix: iv_{name} JMS Listeners Prefix: … More Technology – Denodo Virtual Dataport (VDP) Naming Convention Guidance