Versions Compared

Version Old Version 76 New Version Current
Changes made by

Matthew Girard

Matthew Girard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Onboarding Users

  1. Determine access needshttps://tufts.service-now.com/now/nav/ui/classic/params/target/%24oc_workbench.do%3Fsysparm_redirect%3D%2524oc_groups.do%26sysparm_group_id%3D4576677c4f720340512ea3928110c72a

  2. If VDB exists, do one of the following:

    1. assign to existing AD groups in grouper

    2. create new AD group with custom permissions, assign, import into Denodo and deploy to production

  3. If new VDB needed, do the following:

    1. create and import AD groups following Managing AD Groups

    2. create VDB following VDB Creation

  4. Provide users with Denodo User Guide

...

  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.
      When creating

      image-20250212-201917.pngImage Added

    4. 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-20250203-203429.pngImage 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-20250212-162555.pngImage Addedimage-20250212-162849.pngImage 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-20250212-163006.pngImage Added

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

...

  1. image-20250212-163139.pngImage Addedimage-20250212-163329.pngImage Addedimage-20250212-163419.pngImage 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.

...

Server Architecture Diagram

...

Authentication

Server List

Denodo 8

...

denodo-prod-01.it.tufts.edu
denodo-prod-02.it.tufts.eduDenodo 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

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.

...

Backup script code
S3 bucket code

VM Backups

Handled by ESCP

All Systems: Nightly backup retained for 7 days

...

Health check script is available here.

How to’s

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

Code Block
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:

Code Block
select * from admin.connections

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

Code Block
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.

Note that adding indexes to views will cause an additional error when trying to save changes to the view via the Edit UI panel in the UI. This error prevents the view changes from persisting in Denodo, limiting you to making changes via VQL.

...

To add an index to a cache table, do the following:

  1. Enable caching for a view

  2. Add the index under Cache table indexes

    image-20240807-151253.pngImage Removedimage-20240807-153846.pngImage Removed

  3. Click Save (you’ll get a warning saying there was issue create the index)

    image-20240807-154152.pngImage Removed

ORA-00932: inconsistent datatypes: expected - got CLOB

Problem:

When running:

Code Block
SELECT * FROM student_lifecycle.p_ps_crse_catalog

Get error:

Code Block
P_PS_CRSE_CATALOG_ [VIRTUAL] [ERROR] 
P_PS_CRSE_CATALOG_ [JDBC WRAPPER] [ERROR]
P_PS_CRSE_CATALOG_#0 [JDBC ROUTE] [ERROR]  
Received exception with message 'ORA-00932: inconsistent datatypes: expected - got CLOB'

Cause:

Solution:

Possible Solution 1: Casting

https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast

https://community.denodo.com/answers/question/details?questionId=90670000000XcumAAC

Code Block
select CAST(bv_ps_crse_catalog.descrlong AS text) AS text_column
from bv_ps_crse_catalog

Leads to error:

Code Block
P_BV_PS_CRSE_CATALOG [VIRTUAL] [ERROR] 
P_BV_PS_CRSE_CATALOG [JDBC WRAPPER] [ERROR] 
P_BV_PS_CRSE_CATALOG#0 [JDBC ROUTE] [ERROR]  
Received exception with message 'ORA-22835: 
Buffer too small for CLOB to CHAR or BLOB to RAW conversion 
(actual: 4329, maximum: 4000)'

Possible Solution 2: Server Config

https://community.denodo.com/answers/question/details?questionId=906Du00000001Q7IAI&title=About+Oracle+Clob+columns+shown+properly+in+Denodo+when+group+by

...

Recover from a DROP CASCADE

DROP CASCADE is a very dangerous command that will not only drop the view listed, but all if it’s dependents as well. The dependents list can be very long, and recovery can be very cumbersome. Try to avoid the DROP command at all costs, especially DROP CASCADE.

In the event that you accidentally execute DROP CASCADE, follow these steps to recover.

...

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';

...

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.

...

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.

...

On-Call Schedule

The On-Call Schedule is maintained in ServiceNow here. This schedule allows members of the scheduler roster to be notified, in order, whenever a ticket with urgency of High is created within ServiceNow.

If it’s your first time being on an on-call schedule in ServiceNow, you’ll want to fill the contact form here.

When an urgent incident is created in ServiceNow, ServiceNow will cycle through the roster, starting with the Primary, and send an SMS message like the following below:

...

SMS messages come from +18446360241, so make sure that you create a contact for that number so that it doesn’t get blocked.

The On-Call Calendar can be viewed here.

If you need to schedule time off, go the On-Call Calendar, select the first shift where your time off is occurring and click Manage Shift

...

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

...

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:  1st Sunday 1AM

Prod: 3rd Sunday 12AM

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

Code Block
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/blob/main/custom-functions/README.md

Changing Denodo User-Agent for HTTP requests

Code Block
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:

Code Block
select * from admin.connections

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

Code Block
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.

Note that adding indexes to views will cause an additional error when trying to save changes to the view via the Edit UI panel in the UI. This error prevents the view changes from persisting in Denodo, limiting you to making changes via VQL.

...

To add an index to a cache table, do the following:

  1. Enable caching for a view

  2. Add the index under Cache table indexes

    image-20240807-151253.pngImage Addedimage-20240807-153846.pngImage Added

  3. Click Save (you’ll get a warning saying there was issue create the index)

    image-20240807-154152.pngImage Added

ORA-00932: inconsistent datatypes: expected - got CLOB

Problem:

When running:

Code Block
SELECT * FROM student_lifecycle.p_ps_crse_catalog

Get error:

Code Block
P_PS_CRSE_CATALOG_ [VIRTUAL] [ERROR] 
P_PS_CRSE_CATALOG_ [JDBC WRAPPER] [ERROR]
P_PS_CRSE_CATALOG_#0 [JDBC ROUTE] [ERROR]  
Received exception with message 'ORA-00932: inconsistent datatypes: expected - got CLOB'

Cause:

Solution:

Possible Solution 1: Casting

https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast

https://community.denodo.com/answers/question/details?questionId=90670000000XcumAAC

Code Block
select CAST(bv_ps_crse_catalog.descrlong AS text) AS text_column
from bv_ps_crse_catalog

Leads to error:

Code Block
P_BV_PS_CRSE_CATALOG [VIRTUAL] [ERROR] 
P_BV_PS_CRSE_CATALOG [JDBC WRAPPER] [ERROR] 
P_BV_PS_CRSE_CATALOG#0 [JDBC ROUTE] [ERROR]  
Received exception with message 'ORA-22835: 
Buffer too small for CLOB to CHAR or BLOB to RAW conversion 
(actual: 4329, maximum: 4000)'

Possible Solution 2: Server Config

https://community.denodo.com/answers/question/details?questionId=906Du00000001Q7IAI&title=About+Oracle+Clob+columns+shown+properly+in+Denodo+when+group+by

Open Data Source (only for VDP Administration Tool) > Configuration > Source Configuration > Delegate group by clause = No

This would slow down performance as well as create extra load on the denodo servers by not pushing the query to oracle.

Recover from a DROP CASCADE

DROP CASCADE is a very dangerous command that will not only drop the view listed, but all if it’s dependents as well. The dependents list can be very long, and recovery can be very cumbersome. Try to avoid the DROP command at all costs, especially DROP CASCADE.

In the event that you accidentally execute DROP CASCADE, follow these steps to recover.

  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.

Code Block
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:

Code Block
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/admin/execute_permission_audit

User Guide
Anchor
denodo-user-guide
denodo-user-guide

...