At IU, what are best practices for using Denodo?

Denodo is a data virtualization and data integration platform in use at Indiana University. Denodo can connect to many different kinds of data sources and then combine, aggregate, or join and filter these data sources and present the results in multiple formats.

On this page:


Getting started with Denodo

See Get started with Denodo at IU.

Subscribing to the Denodo users mailing list

Denodo developers should subscribe to the denodo-users-l-subscribe@iu.edu mailing list for information regarding Denodo updates, maintenance, training, resources, and best practices.

Flow of development

Each Denodo development project is different, but the following series of actions is common:

  1. Create data sources.
  2. Create base views. If you anticipate changing data sources in the future, create interface views on top of the base views.
  3. Create derived views: Use joins and filters on the base views.
  4. Create an interface view on your final results. If you want to access the view via the web, publish the interface view as an API.
  5. If performance is an issue, optimize the views; see Optimizing Denodo views.

Organizational/folder structures

UITS recommends creating one folder for data sources and other folders for projects or categories. Following is an example virtual database (VDB) that uses this model:

An example of a folder structure in Denodo

Naming conventions

Data sources

To aid in future migration processes, each data source should have a generic name and should specify the instance. Use the following template for a data source:

  ds_<descriptor>_<db username or application>_<environment>

Base views

Base view standard

Standard base views should match the data source one-to-one for both the name of the object and the name of the attributes; these should both be technical names.

Base view from query

Base views from queries should match the data source for the attributes. However, the view name should be generated in a consistent, sensible manner based on the following considerations:

  • The view name should derive from data sources and underlying technical name(s).
  • If applicable, the view name can include an identifying characteristic (e.g., effdt).
  • The view name should end with the suffix _qry.

Derived views

Derived view names should be similar to base view from query names. Where possible, all attributes should be consistent with the technical name. However, where attributes are derived, the view name should be generated in a consistent, sensible manner based on the following considerations:

  • The view name should derive from data sources and underlying technical name(s).
  • If applicable, the view name can include an identifying characteristic (e.g., effdt).
  • The view name should end with the suffix _dv.

Interface views

Creating interface views with the end user in mind is the best practice. Customer-facing interface view names should be specified as part of a user story. If this isn't the case, consult the business analysts or architects. In general, these view names should reflect the subject area/segment and business entity.

The attribute names are currently undetermined. Until there's a more formalized attribute naming standard, conform to either to technical or DSS-derived names. Consumable attribute names should be consistent with the data dictionary or business glossary.

Name lengths

Technical names should follow the conventions of the underlying technical architecture. If the underlying data source is an Oracle database, the technical name is limited to 30 characters.

Business names should follow the conventions established for the data dictionary or business glossary. However, the conventions are still undetermined, and some of these names might occasionally exceed 30 characters. If they exceed 30 characters, and if any of these Denodo views need to be converted to an Oracle table, the conversion back to technical names will be part of the data modeling effort to generate the physical data model.

View naming summary

Type View Attribute Suffix
Base view standard Technical name Technical names n/a
Base view from query
Consistent, derived from technical name
Technical names _qry
Derived views
Consistent, derived from technical name
Technical names _dv
Interface views Business names Business/TBD n/a

Associations

You can add associations between tables; this helps when linking tables together and creates useful metadata for data consumers. For more on adding associations, see the Denodo tutorial on Linked Data.

Caching

Caching is one way to improve the performance of your views. Caching temporarily holds query results in a local Oracle database. Each VDB has its own database and space just for caching. To check your cache database usage, execute the view "cache_space_status_report". If you don't have a view by this name, contact EDSS. For more on caching, see Optimizing Denodo views.

Publishing APIs

You can easily publish views as SOAP or REST web services. Other applications can use these web services as Application Programming Interfaces (APIs).

Important:
When creating APIs, be sure to secure them. To do so, select the Settings tab, and then, from the "Authentication:" drop-down menu, select HTTP Basic with VDP.

Moving resources between VDBs

You can copy and paste objects between VDBs. This is useful when moving resources from your "_dev" VDB to your main VDB.

If you need to move many resources at once, use the Export and Import commands on your interface view or top-level derived view.

To export:

  1. Right-click a data source, folder, or view, and select Export.
  2. Under "Replace/Drop elements", select Replace existing elements.
  3. Check Export dependencies.
  4. Click Ok.

To import:

  1. From the File menu, select Import.
  2. On the "Import options" screen, select the correct target VDB as the Default database. Keep in mind that this will export dependencies, including the data sources and base views.
  3. Click Ok.

Because the export file contains encrypted passwords, it is best to treat this file as you would any file containing sensitive data: Delete the export or import file after you are finished with it, and only share it with others if absolutely necessary.

Migrating from development to production

To migrate resources between environments (DEV, STG, and PRD), see Migrating resources in Denodo at IU.

VQL shell

The VQL shell provides a programmatic interface into Denodo. To access the VQL shell, from the Tools menu, select VQL Shell.

SQL-like statements are available in the VQL shell. For a full list of commands, enter the VQL command HELP. For a reference of VQL commands, see the Denodo Advanced VQL Guide.

Admin tool preferences

It is not necessary to modify the admin tool preferences. If you modify these preferences, you may need to reinstall the Denodo client.

Role and user management

It is not necessary to view role and user management in Denodo. These components are managed by UITS Application Security and Quality Assurance (ASQA). If you wish to modify or add access in Denodo, contact EDSS.

Learning more

For more about Denodo, see the Virtual DataPort Administration Guide, videos, and tutorials on the Denodo Community website. If you have questions or need help with Denodo, contact EDSS.

This is document amti in the Knowledge Base.
Last modified on 2017-09-28 11:31:32.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.