A collection of information technology, consulting, and business knowledge
Category: Information Systems
Information Systems is an academic study of systems with a specific reference to information and the complementary networks of hardware and software that people and organizations use to collect, filter, process, create and also distribute data. … Information systems are also different from business processes.
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.
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
CURRENT_DATE | CURRENT_TIMESTAMP
Denodo Related References
Denodo Virtual Query Language (VQL) Syntax Conventions > Denodo Reserved Words
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 the “Execute” privilege in version 7.
According To The Denodo Documentation:
In Denodo 7.0, the privilege “Read” has been renamed to “Execute” to clarify its behavior. In VQL statements, the “READ” token can still be used to keep backward compatibility with existing VQL scripts.
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.
Import Denodo Security Notes
Consumer security authorization is imposed at the object level, then Data Level
Consumer security authorization is not imposed on Modeling Layers/VDP Folders
Using a virtual database to partition projects or subjects is a Best Practice
Basically, the ability to grant security is as follows:
Permissions grants include connection, creation, read, write and admin privileges over a VDP database.
Permissions grants include read, write, insert, update and delete privileges over a view.
VDP Columns Within a VDP View
Permissions grants include the denial of the projection specific columns /fields within a view.
Row Level Security
Row Level restrictions can be added to allow users to obtain only the rows that match a certain condition or to return all the rows masking the sensitive fields
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 are transformed in denodo Virtual Query Language (VQL) data types. the Business layer is what folks with a traditional data warehousing background would think of as Staging or landing. These base layer views should most closely mirror the technical structure and data characteristics of the input data source and will be the least business friend in their organization, naming, and metadata.
The Semantics layer
The semantics layer is where the major data reorganization, data transformation, and the application of business friend field names and metadata begins. The semantics layer is what folks with a traditional data warehousing background would think of as the Data Warehouse (DW) or Enterprise Data Warehouse (EDW). The semantics layer of the logical data warehouse (LDW) performs serval tasks:
Data from multiple input sources are consolidated
The model becomes multi-dimensional (Fact and Dimension oriented)
Field names and descriptive metadata are changed to meaningful, domain normalized, business-friendly names and descriptions.
Domain normalizing business rules and transformations are applied.
Serves as a data source for the business layer and reporting layer.
The Business Layer
The business layer, which is considered optional by denodo, is modeled along a more narrow business subject orientation and more specialized business rules are applied. This is what folks with a traditional data warehousing background would think of as a Datamart (DM).
The business layer of the logical data warehouse (LDW) performs serval tasks:
Limits and optimizes the data to facilitate business intelligence and report activities concerning a specific line of business or business topic (e.g. Financials, Human Resources, Inventory, Asset management, etc. )
Business-specific/customized rules and metadata are applied
Supplements the semantic layer and serves as a data source for the reporting layer.
Additional data consolidation and data structure denormalization (flattening) may occur in the business layer
The Reporting Layer
The reporting layer, which is considered optional by denodo, is the most customized layer and sees the most reporting topic specialization and specific need transformation. The reporting layer is where a traditional data warehousing may provide customized reporting, or system interface views, interface ETL’s to produce interface files, and reporting team do more of their own development.
The reporting layer of the logical data warehouse (LDW) performs serval tasks:
Provides consumer-specific customized rules and metadata
Provides consumer-specific data organization/layouts
Data is optimized for consumer purposes and may be highly or entirely denormalized to meet consumer needs.
The denodo “Base Layer” in the Logical Data Warehouse (LDW) can be thought of as the Data Staging local layer in a more traditional data warehouse (DW) development pattern. The Base layer is the level at which the source system data structures are transformed into denodo field types and the source data structures are rendered as created in base views (bv).
Base views (bv), the first step in virtualizing data, are the denodo structures reflecting the source system structure and the second step behind the data source connection and, therefore, are essential elements for the other layers of the Logical Data Warehouse (LDW). To provide some guidance to facilitate the usefulness and performance of base views here are some best practices:
Use consistent Object Naming conventions. It is strongly recommended that the denodo standard naming conventions be used.
Create indexes on Primary Keys (PK), Surrogate keys, and Foreign Keys (FK)
Import and or create the Primary Keys (PK), Foreign Keys (FK), and Associations.
Have Statistics Collection been set and include all critical fields?
Base views, as a rule, should not be cached unless absolutely necessary for reasons of performance.
Create performance Indexes to mirror sources system to improve performance.
Populate view Metadata properties describing the type and the nature of the data which the view contains.
Populate field Metadata properties describing Field. This is important for a few reasons:
The description can be inherited by view built from the base view.
This populates the Denodo Metadata Catalog within which data Stewards can maintain and improve the description.
Informs other developers and user of what the field is/means.
Field metadata should be annotated with “Not Used”, if the field is always null, blank, or empty. This saves time and labor when researching data issues
Retain the original table name (applying naming convention prefix and field names to facilitate data Lineage traceability.?
Use denodo tools against tables, where possible, rather than (Manual) SQL views, Database views, or Stored Procedure. Denodo cannot rewrite or optimize these objects.
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
Denodo Data Catalog Role Name
Assign categories, tags and custom properties groups to views and web services.
Edit views, web services, and databases. Create, edit and delete tags, categories, custom properties groups, and custom properties.
Can do the same as a user with the roles “data_catalog_editor” and “data_catalog_classifier”.
Configure personalization options and content search.
This role can perform any action of all the other data catalog roles.
The exporter role can export the results of a query from the Denodo Data Catalog.
denodo > User Manuals > Denodo Platform New Features Guide