External Database Views As Drupal Views

This is a followup post to a post where I showed how you can use VDC to display data from an external database table in a Drupal View. In this post I display an external database view as a Drupal View. This is another step towards showing how Drupal can be used as a Business Intelligence (BI) or data analysis platform.

Most reports usually have data from more than one database table. To cater for this need to use multiple tables, Drupal Views have the functionality to create relationships. At the time of writing you could not create relationships with VDC. This is not a problem because VDC allows you to use database views as a data source for your view. You can create database views that use data from different tables by using SQL joins. You can think of SQL joins as relationships in Drupal language.

Using the Chinook sample data set, I created a database view that showed total sales by genre per annum. The SQL code to create the database view is shown below.

CREATE VIEW vw_genre_total_year AS SELECT DATE_FORMAT(InvoiceDate, ‘%Y’) AS Calendar_Year ,c.Name AS Genre_Name ,SUM(a.Quantity) AS Total_Quantity ,SUM(a.Quantity*a.UnitPrice) AS Total_AmountFROM InvoiceLine a JOIN Track b ON a.TrackId = b.TrackId JOIN Genre c ON b.GenreId = c.GenreId JOIN Invoice d ON a.InvoiceId = d.InvoiceIdGROUP BY Calendar_Year ,c.Name

Having already created my data source, the next step was to import the database view as a Drupal View using VDC. As you can see from the screen shot below VDC gave me the option to select database views or database tables.

VDC Database View

Once I had imported the database view, I created some exposed filters to make interacting with the data easier for the end users. The screenshot below shows the database view data shown in a Drupal View along with some filters defined.

VDC Database View Data & Filters

You have to apply your mind to database performance when creating database views. Your database view cannot have millions of lines that need to be summarised every time the view is accessed via the Drupal View. If you have considerable data that needs to be summarised you should use materialised views or create aggregated tables for better performance.

Using views or preferably materialised views will give you a measure of control on the SQL queries that you do not have when you rely on Drupal to generate the SQL queries. You will be able to use SQL features such as SQL UNIONS, OUTER JOINS and sub selects which I have found hard to implement using Drupal Views. Database views also give you a layer of abstraction. You could change the underlying database tables in the database view definition and not affect the Drupal View.

Leave a Reply