Microsoft Dynamics Ax (formerly known as Axapta) Programming: Using Database Views
There is nothing new about database views. Even in the days of punch cards. Yes, I am of the punch card era where I carried my box of punch cards through wind, rain and snow of Buffalo NY into the basement of Hayes Hall to complete my college assignment during the late 70’s. Even in those days, our college professors were teaching us about views, the power of views and what they meant to the database designer.
What is interesting is that in the world of real life programming, views do not hold the same level of esteem they do in academia. In nearly 30 years, I have leveraged views to help solve very complex problems and created sophisticated applications that have been driven by views. I have also noticed that many programmers have not embraced views with the same enthusiasm as I have. The objective of this discussion is to help encourage you to take a new view towards views and embrace views as part of your development.
Views become an important part of your development due to the normalized datasets. With normalization, the objective is to remove repeating field values so that an element is not represented multiple times in the database. The goal of this task is to design a reliable and efficient schema to support transaction processing. Users typically experience the benefits of a normalized database by simply changing a field value in one place and seeing the value change everywhere. Users also see the problems when they see a key value without the corresponding name/description.
When you create a view you are establishing a relationship between two or more tables to create a separate distinct view. This view behaves similarly to a table in that you can build forms, reports and queries with it. Then if it behaves similarly, then why create the view in the first place?
The first reason to create a database view is that it allows you compartmentalizing the task of data organization and retrieval from the application logic. You cannot underestimate the benefit this delivers to the developer and the user. On several occasions, I have been faced with complex requests by clients that have been efficiently addressed with defining the view first and the application second. The Microsoft Dynamics Ax (formerly known as Axapta) Query Class absorbs the view and provides you with unlimited retrieval options to access your data. Application logic then flows allowing you to quickly proof functionality and debug any problems.
The second reason that you create views to data is to mask complex data relationships. Today, you see this Microsoft Dynamics Ax2009 (formerly known as Axapta) with views created to display data from the Global Address Book. The highly normalized GAB data structures are masked in part by a view that bridges the display of the names to the corresponding master records.
Database views are real time and do not require supporting processes to build a view of the data for the users to access. In general, this simplifies the supporting application and creates a better user perception of the application. In my experience, users have unenthusiastically embraced batch processes. It is to the benefit of the developer to match the user’s perception than to battle the justification of an inefficient process.
Next time you are in the Microsoft Dynamics Ax (formerly known as Axapta) build a view, use a view and experiment with it. Try it in a report, form and a class. At first, keep it simple. But soon afterwards, if the bug catches you, you will be looking at the next complex task with whole new view!
Michael J. Conti
Vice President of Development – Clients First Business Solutions, Arlington, TX