They do your data warehouse good

by Deborah J. Smith

In my hometown, we grew up being told that drinking lots of milk was the key to growing strong. The white mustaches seen on today’s billboards continue to extol the effects of calcium on our body’s ability to remain strong as we grow older. That’s because the skeletal system is the body’s framework. When that framework is kept strong, extended agility, strength and mobility are the rewards.

Much like the body’s skeletal system supports muscles and organs, a data warehouse supports a business. And much like proper nutrition grows strong bones, views help increase strength, improve performance and add dexterity to the underlying table structure in your data warehouse.

One of the greatest resources of every business is the information data provides. The strategic goal behind data warehousing is to centralize data and reuse it in multiple applications without duplication, maintaining a single view of the truth. The question then becomes how do you ensure a single view when some of the required business applications are diametrically opposed? How do you harness the strength of this centralized data, develop the synergism data warehousing offers and deliver data in all the various forms required by business users when their requirements are as different as A and Z?

Ahh ... views.

Views are the transparent layers that sit on top of the underlying data model or tables. They provide a way of looking at or regarding the data. This transparent presentation layer is what the end user sees and uses. In Teradata, views don’t require space, and they can transition the underlying data schema into nearly any presentation with such efficiency that the cost of using views is mitigated to simply the management of them. The benefit provided by views far exceeds the cost.

A strong foundation
Teradata technology is completely neutral when it comes to the underlying data model. A normalized data model (typically third normal form, or 3NF) is often recommended for Teradata implementations because it facilitates the storage and integration of data at the lowest level of granularity, and best practices have shown that a normalized model provides greater flexibility over other models. Other schemas might be chosen to accommodate the tools being used, or they might be necessitated by a specific application requirement whose priority becomes so high that the model is defined without thought for the future. This eventually requires changing the data schema and duplicating the data so that the next application requirements can be fulfilled, and the next and the next and so on. Either way, such practices compromise the data warehouse’s ability to meet the business requirements for which it was originally established.

Storing data in a normalized model allows data to address multiple business requirements rather than addressing one specific business need. Without a normalized model, the architecture quickly leads to a compromise of the integrity of the data warehouse, diminishing its strength and violating the first and most important rule of data warehousing—to provide a “single version of the truth.”

So, if you follow this data architecture and develop a 3NF data model, how do you employ tools requiring a different data schema? How is it possible to both easily and quickly address business requirements for new applications? Using a view strategy will resolve these issues and preserve the warehouse’s data integrity.

A clear view
A well-developed view strategy supports and influences database independence. When you adopt standards for view usage, the physical data model becomes independent of individual processing queries, whether these queries are generated by SQL generation tools, specific business applications or evolving business processes. A view strategy enables the use of different tools that often require data models that are not normalized and/or that require data duplication. Views that join tables, perform case logic, establish virtual columns or alter the data for presentation—either through a tool or an established processing script—ensure data independence while still enabling and optimizing tool performance and processing scripts.

Storing data at the lowest granular level in the data warehouse allows users to address multiple enterprise business concerns. However, some applications or ad hoc queries require data at a summary or rolled-up level. If the business response-time requirement is negotiable, or if the query volume doesn’t support the development of summary tables or use of other database features such as join indexes or value-ordered indexes, views that roll the data up to a summarized level might be the solution. This process allows applications to use the summary view while maintaining summary consistency of the detail data. The development of summary views is a compromise between providing a methodology for the detailed data to be summarized on the fly and instantiating the data in a summarized format. When making this decision, keep in mind all the issues that surround instantiating a second version of the detailed data, such as additional development effort and processing requirements, and the task of verifying consistency between the detail data and the additional versions of that data.

A great plan
Views provide a methodology for simplifying scripts. They influence database independence by hiding complex coding without compromising a friendly DBA database structure. Views are frequently used to provide security processing, and they can enforce data-locking standards. For example, short tactical queries or OLTP-type transactions might require read-access locking, while other ad hoc requests require access locking. Using views for these different types of transactions simplifies the request and ensures proper locking. Views sustain case logic, making it relatively easy to add virtual columns based on the content of single or multiple tables. Columns that have multiple meanings to different business users can be de-coded into multiple virtual columns that together make up the string of individual columns.

Think of how difficult it is to provide and verify moving time windows, with the risk of using multiple calculations to create different results that essentially answer the same question. Do ad hoc queries that require moving time windows take up additional resources, or do they simply not complete because of a lack of understanding how to code them or how to code them efficiently? Are multiple tables often joined inefficiently or incorrectly? Imagine being able to hide and/or standardize moving time windows or calculate last month, yesterday, year-to-date, or hide efficient multiple table joins. Providing code in a view allows a consistent re-usable method available for all end users and applications.

Using views to hide complex or frequently used table joins provides consistency for both application and ad hoc queries by simply re-using the same object or view. Views provide a way to consistently format columns, allowing data to be stored in the most optimized fashion and formatted through a view to provide a consistent format for the business users and applications. Views encapsulate best coding practices, best SQL coding, consistent enterprise metrics and user-friendly column names that might differ from the DBA-defined table columns.

 

A simple process
Typically, when you establish a data model and create the underlying tables, you generate one-to-one views that are reflective of those tables without joins, case logic or changes of any sort. Many third-party tools do not work or work inefficiently with the developed data model. The tools might require a data schema that is significantly different from the underlying data model.

By creating another layer or application-specific views, you provide third-party tools with the structure they require. Joining table structures, developing virtual columns or using case logic within the view can enhance tool performance and/or homegrown applications that require a different look at the data.

Using views to facilitate security issues provides a simplified means of performing access right management. Suppose you have data that contains enterprise financial information. While some data might be available to all business users, the actual financial information can only be accessed by upper management. Views protect secure information from the general business user while still providing access to the target business users. In addition, views can be used to protect privacy by ensuring that external business users only see information that is relevant to them.

A business with a view
Business enterprises that have adopted a view strategy have realized significant performance improvement, not only for specific applications but also for all other data warehouse workloads. Depending on the volume and use of just one application, changing or implementing a single view to improve performance of the application will also free up database cycles, thereby improving the overall performance of all data warehouse workloads. How much it actually helps the business users is ultimately dependent upon the environment and how extensively views are used to improve performance and efficiency.

One thing is certain: If the data model is the framework of your data warehouse, then views are the element that will provide the dexterity needed to enhance, strengthen and extend the value and use of that framework.

So, got views?

Deborah J. Smith is a senior data warehouse consultant with Teradata.

ILLUSTRATION BY NICHOLAS WILTON