Best practices for using Denodo at IU

• Get started with Denodo
• Flow of development
• Organizational/folder structures
• Naming conventions
• Associations
• Cache query results
• Publish APIs
• Move resources between VDBs
• Migrate from development to production
• VQL shell
• Admin tool preferences
• Role and user management
• Learn more

Get started with Denodo

See Get started with Denodo at IU.

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 Optimize 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:

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 (such as 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 (such as 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

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.

Cache query results

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, fill out the EDSD Support Form . For more on caching, see Optimize Denodo views.

Publish APIs

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

Move 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. This will export all dependencies, including data sources and base views.
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 import 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.

Migrate from development to production

To migrate resources between environments (DEV, STG, and PRD), see Migrate 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 Virtual DataPort 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, see Use Data Delivery Apply Security to assign security to Denodo views.

Learn 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, fill out the EDSD Support Form .