DATA ACADEMY FEATURE SPOTLIGHT: VIEW-IT

Introduction

Data Academy® is a low-code/no-code software development environment that utilises software automation techniques and principles to enable the development of enterprise grade data management solutions in a fraction of the time using conventional means. This is achieved by leveraging the functionality of Microsoft SQL Server as well as functionality that extends way beyond SQL Server, accessed through Data Academy’s own development interface.

Feature Spotlight – VIEW-it

SQL Server Views provide an excellent method of presenting complex data structures in an easily understood and consumable form. Managing the content of views however is a major challenge, especially as the volume of data views grows.

Where it is necessary to present the same view to multiple audiences using different column names for example, requires duplication increasing the ongoing management overhead.

Global changes to views require laborious sequential processes to ensure all views have had change appropriately applied.

As a result of which many potential fixes for this problem has been presented as functionality within expensive third-party reporting applications which as well as the additional cost incurred also locks the organisation into the use of that tool for all reporting purposes.

Picture1

The Data Academy VIEW-it module provides an answer to this problem by delivering a capability that leverages the generic SQL Server View functionality in a managed environment. This allows the developer to interactively build custom views using the familiar query builder and publish them into SQL Server for onward use. However, the content of the views is centrally managed via Data Academy, providing a single point of maintenance.

Semantic Dictionaries

Having created views in the Data Academy Query builder, developers are provided with the ability to define semantic dictionaries that are applied to the views when they are generated.

Screen Shot 2019-04-20 at 16.08.44

Multiple dictionaries can be applied to views to present the same structure as views with different field column names but manged centrally. This allows changes to be rolled out across multiple views with a single change. This significantly:

  • simplifies the change management process
  • reduces errors to zero
  • reduces change management effort and hence time

The views created however are still generated as generic SQL Server Views. This means that there is no limit to the tools that can consume the output from them providing the best of all worlds:

  • Centralised view management
  • Semantic Dictionaries
  • SQL Server based Technology
  • No additional cost

Creating SQL Views

Views are created within Data Academy’s visual query builder which has full support for SQL Server’s T-SQL language including

  • Grouping
  • Aggregate Functions
  • Alias mapping
  • Sub queries
  • Visual Relationship builder
  • T-SQL Functions & Syntax

The visual query builder includes drag and drop functionality for adding tables and relationships. This is a very familiar interface for developers that have previously used query builders in SQL Server Management Studio or Microsoft Access.

The generation of views is integrated into the overnight orchestration process ensuring that views reflect the current data structures and therefore never go out of synchronisation.

Picture2

The Query builder also presents the developer with the SQL interpretation of the visual query. This can also be edited directly to make fine tuning changes. Subsequently, these changes are reflected into the visual query representation.

This means that it is possible to paste SQL directly into the SQL window from other query builders. This is then interpreted into the visual query representation and ultimately compiled into Data Academy.

This provides the developer with unrivalled flexibility, allowing them to work using whatever method is most familiar and productive.


Conclusion

Without investment in additional external tools and committing the organisation to single reporting output the presentation of complex data in simple format is not easily achievable. The Data Academy VIEW-it module on the other hand, delivers this capability through a centrally managed intuitive interface providing organisations with an agnostic solution to the problem of simplifying complex data whilst keeping change management overheads down and hence cost.

FEATURE SPOTLIGHT_