Happy New Year! If your resolution is to improve your business intelligence capabilities and get about how you’re leveraging your data, you’ve come to the right place. Whether you’re a developer or end-user, SQL views can be a convenient and powerful utility.
For the Developer:
As a convenience, views may be used to combine data (via JOINs) from any number of related tables in order to perform repeatable data-gathering operations. This is particularly useful when joining transactional information between open and historical tables, a construct common to ERP software solutions like Microsoft Dynamics GP. Since SQL views are basically virtual snapshots of the permanent tables, they are automatically “updated” as the underlying data changes, but unlike the database tables, SQL views do not use any physical space.
SQL views are also used to query subsets of data from tables or even from other SQL views.
As an example, let’s say you often want to know the sum of all sales invoices for all customers. Because that information exists in two tables (open and historical invoices), this seemingly simple query would require an unnecessarily complex SQL statement.
However, by joining the open and historical invoice tables into a SQL view called VIEW_SALES, this can be obtained simply by:
1. CREATE VIEW VIEW_SALES AS
- SELECT * FROM OPEN_INVOICES
- UNION ALL
- SELECT * FROM HIST_INVOICES
2. CREATE VIEW VIEW_SUMMARY_SALES AS
- SELECT CCUSTNO [Customer Number]
- CBCOMPANY [Customer Name]
- SUM(NSALES) [Sales Amount]
- FROM VIEW_SALES
- GROUP BY CCUSTNO
The example above also illustrates the power that SQL views can provide to an end-user. The developer would be familiar with the field names in the database tables (CCUSTNO, NSALES, etc), but a customer would not want to view their queries in this way. The view’s results may be aliased in a more meaningful way for customer presentation, as illustrated by what’s inside the brackets.
Another advantage of the SQL view is that they are easily changeable in the event additional information is desired. This can be done without needing to change any of the underlying data tables.
Based on the example above, we also want to summarize customer sales by salesperson. A simple change is made to include the salesperson field (CSLPNNO):
1. ALTER VIEW VIEW_SUMMARY_SALES AS
- SELECT CSLPNNO [Salesperson Number]
- CCUSTNO [Customer Number]
- CBCOMPANY [Customer Name]
- SUM(NSALES) [Sales Amount]
- FROM VIEW_SALES
- GROUP BY CSLPNNO, CCUSTNO
A more complex manipulation of sales information can even be utilized to achieve the following results to simulate an aging report. The beauty of such a view is the data in the aging buckets will automatically roll to reflect the new aging schedule as time goes by.
For the End-User:
As users become more savvy with various reporting tools, developers may be asked less to provide custom reports and more to provide SQL views. For example, with a very basic knowledge of Microsoft Excel’s data import capabilities, the views above may be quickly represented in a simple spreadsheet.
By specifying the name of the SQL instance on the server and the name of the view, the data can be populated into an Excel document and manipulated into an appropriate presentation.
From the example above where customer sales were summarized by salesperson, the data may also be easily imported into Excel in the form of a Pivot Table, automatically providing customer sales and salesperson subtotals without the need for complicated Excel commands or formatting. This is real-time data from Dynamics GP being analyzed directly in Excel!
As you can see, SQL views are an invaluable tool to developers and, when defined properly, can also serve as a vital resource for a customer’s analysis and reporting needs.
If you’re looking for quick and easy ways to get smarter with your data this New Year, we’re here to help! Click here to schedule a free consultation with us or give us a call at (212) 869-9300.
Need to make the move from Dynamics GP? Learn more about your options migrating from Dynamics GP.
As a Dynamics GP user, it is important that you understand GP End of Life. It is not just a distant event; it carries substantial implications that every business must be aware of. To learn more, please watch our on-demand webinar, Microsoft Dynamics GP End of Life – What’s Next, to:
- Gain a firm grasp of the critical dates that will shape your decision-making process.
- Decode the terminology to understand how it will directly affect your day-to-day operations & future of your org.
- Delve into the security & business continuity risks that may jeopardize your organization.
Click here to watch now.
Additional SQL Resources
Why is my SQL Database Filing the Hard Drive of my Server?
How to Handle a NULL Value Loaded by SSIS From a CSV File
