...

1. Log in to Grouper using SSO

2. Search for the DS folder

3. When creating a group

   1. use the prefix grp_ds_denodo

   2. description should refer to a data steward

      1. members should all be run by the data steward before addition

   3. Add grp_ds_denodo-admin to the group with ADMIN privileges. This gives Denodo admins the ability to administer the group.

   4. When creating Create separate -dev and -read groups

      1. Add the dev group to the read group so all dev group members are synced with the read group automatically. This will ensure read access for devs will exist beyond dev, since only the read group should be promoted outside of dev.

   5. If developers need scheduler access, create a -scheduler group and associated associate that group with a scheduler project after importing into Denodo.

      Image Added

   6. If the developers need solution manager access, add the -dev group as a member of grp_ds_denodo-solution-manager-deploy.

   7. If a service account is going to be used, create -service group and give it the appropriate permissions after importing. Do not assign it the tts-facstaff role as this will create view clutter in applications like DBeaver and Tableau.

4. Add members to groups

   1. Only add members that have been approved by the data steward

5. Import groups into denodoDenodo using Design Studio. Note: You may need to wait up to 30 minutes for grouper changes to get synced to AD before importing.
   

   Image AddedImage Added

   Code Block
   Role base: DC=tufts,DC=ad,DC=tufts,DC=edu Attribute with role name: sAMAccountName Attribute with description: description Role search pattern: (&(cn=*denodo*)(objectcategory=group))

    

   Image RemovedImage Added

6. Assign roles and permissions as necessary. Add tts-facstaff role to give metadata access to all views.
   

...

1. Image AddedImage AddedImage Added

Denodo LDAP Configuration

...

Once a VDB is created, make sure you create and import a dev and read group by following the above AD group guide. Also, make sure you update the tts-facstaff role to include metadata Connect and Metadata access to the new VDB.

...

Auditing Privileges

In Denodo, you can run a query with privileges based on one or more roles by adding CONTEXT('impersonate_roles'='role1,role2,role3,...') to the end of any query.

...

Authentication

Server List

Denodo 89

denodo-solution-manager-prod-0110.it.tufts.edu
denodo-dev-0110.it.tufts.edu
denodo-stage-0110.it.tufts.edu
denodo-prod-0110.it.tufts.edu
denodo-prod-0211.it.tufts.edu

Denodo 9

denodo-solution-manager-prod-10.it.tufts.edu
denodo-dev-10.it.tufts.edu
denodo-stage-10.it.tufts.edu
denodo-prod-10.it.tufts.edu
denodo-prod-11.it.tufts.edu

Load Balancer

...

Load Balancer

The load balancer can be found here: https://tssdc-lb-float-vip.net.tufts.edu/
You need to create a ticket requesting access if you want read or write access.

...

In the window that appears, select Schedule time off and select the dates for your time-off.

...

...

Adding a user created function to Denodo via JAR file

https://github.com/Tufts-Technology-Services/denodo/blob/main/custom-functions/README.md

Changing Denodo User-Agent for HTTP requests

SET 'com.denodo.vdb.http.userAgent'='Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/116.0';

The VDP server needs to be restarted to take affect.

...

Those who are on-call can refer to the HSST doc for Denodo shared with ESCP to help with troubleshooting: /wiki/spaces/EnterpriseSystems/pages/1301184579

Automated OS Patching

The OS for the Denodo VM’s will be automatically patched each month on the following schedule:

Non-prod:  1^st Sunday 1AM

Prod: 3^rd Sunday 12AM

In the event that patching needs to happen manually, run the following command as root:

sudo yum update -y && sudo shutdown -r now

This will patch the OS and restart the VM.

How To’s

Adding a user created function to Denodo via JAR file

https://github.com/Tufts-Technology-Services/denodo/treeblob/main/scripts/denodo#updating-denodo

Get a list of all data source connections in Denodo

You can view a list of all data source connections on a Denodo instance with the following query:

select * from admin.connections

To get additional details on certain types of connection, run this query:

select * from admin.connections_detail

Add an index to a cache table

Denodo allows you to add indexes to cache tables for improved performance. However, there appears to be a bug in Denodo where index creation will succeed on the DB-side but Denodo will throw an error or warning. While this warning can be ignored when custom-functions/README.md

Changing Denodo User-Agent for HTTP requests

SET 'com.denodo.vdb.http.userAgent'='Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/116.0';

The VDP server needs to be restarted to take affect.

Updating Denodo

https://github.com/Tufts-Technology-Services/denodo/tree/main/scripts/denodo#updating-denodo

Get a list of all data source connections in Denodo

You can view a list of all data source connections on a Denodo instance with the following query:

select * from admin.connections

To get additional details on certain types of connection, run this query:

select * from admin.connections_detail

Add an index to a cache table

Denodo allows you to add indexes to cache tables for improved performance. However, there appears to be a bug in Denodo where index creation will succeed on the DB-side but Denodo will throw an error or warning. While this warning can be ignored when creating indexes in Design Studio, it can cause schedule cache jobs to fail if a PK index needed to be created as part of the job (invalidating rows based on PK enabled). To get around this, simply create the indexes in Design Studio before running the job.

...

1. Get a list of all dependents of the view dropped and their cache status

   Code Block
   select   distinct vd.view_database_name, vd.view_name, v.cache_status from view_dependencies() vd left join get_views() v on v.name = vd.view_name and v.database_name = vd.view_database_name where dependency_name = 'VIEW_NAME' and private_view = 'false';

2. Get the latest VQL backup for the environment where the drop occured. This could be a development backup in S3, or a deployment backup. See the sections above for their locations.

3. Assemble a VQL file containing the relevent CREATE, ALTER, CHOWN, and ALTER ROLE statements for each view. Be sure to include CONNECT DATABASE and CLOSE; statements for each database.

4. Create a revision in Solution Manager, validate and deploy.

See who has completed the Denodo training

Run the following query in the VQL shell, or access the view in the Data Catalog using the link below.

select * from training.abs_denodo_course_completion

...

1. select   distinct vd.view_database_name, vd.view_name, v.cache_status from view_dependencies() vd left join get_views() v on v.name = vd.view_name and v.database_name = vd.view_database_name where dependency_name = 'VIEW_NAME' and private_view = 'false';

2. Get the latest VQL backup for the environment where the drop occured. This could be a development backup in S3, or a deployment backup. See the sections above for their locations.

3. Assemble a VQL file containing the relevent CREATE, ALTER, CHOWN, and ALTER ROLE statements for each view. Be sure to include CONNECT DATABASE and CLOSE; statements for each database.

4. Create a revision in Solution Manager, validate and deploy.

See who has completed the Denodo training

Run the following query in the VQL shell, or access the view in the Data Catalog using the link below.

select * from training.abs_denodo_course_completion

https://denodo-prod.it.tufts.edu:9443/denodo-data-catalog/#/view/training/abs_denodo_course_completion

See who has access to views or columns (and their dependents)

The view admin.execute_permission_view has been created to help audit who has execute access to to a view and/or their dependents.

For example, to see who has execute access to views with columns containing visa or citizen that isn’t a Denodo admin, you can run the following query:

SELECT 
    epa.parent_database_name, 
    epa.parent_view_name AS view_name, 
    epa.parent_column_name AS column_name, 
    epa.parent_column_distinct_query AS distinct_query, 
    epa.parent_datasources AS datasources, 
    epa.child_database_name AS child_vdb, 
    epa.child_view_name AS child_view_name, 
    epa.child_column_name AS child_column_name, 
    epa.child_column_distinct_query AS child_distinct_query, 
    group_concat(DISTINCT epa.access_role) AS access_role_concat, 
    group_concat(DISTINCT epa.user_name) AS user_name_concat 
FROM admin.execute_permission_audit epa
WHERE
    (
        epa.parent_column_name like '%citizen%' 
        OR epa.parent_column_name like '%visa%'
    ) 
    AND epa.user_name is not null  
    AND epa.user_name not in (
        SELECT user_name FROM admin.f_ad_user_groups WHERE group_name in ('grp_ds_denodo-admin', 'denodo-admin')
    ) 
GROUP BY 1,2,3,4,5,6,7,8,9;

Example result:

...

This view has been deployed in all environments, and be access in either Design Studio or Data Catalog

https://denodo-dev.it.tufts.edu:9443/denodo-data-catalog/#/view/trainingadmin/absexecute_denodopermission_course_completionaudit

User Guide

Anchor
denodo-user-guide denodo-user-guide

...