If you're like most people who build using Drupal you want to build your site around nodes, that have titles, descriptions, tags, comments, etc. It's no surprise since those types of content oriented sites are the ones that make the world of the web go round. But what about another type of site that focuses not on nodes but on data contained in the Drupal database? Can you do it? Yes. Should you do it? Only if that sort of thing excites you or perhaps if a client requests it.
I'll give you a little background on where I'm coming from and what made me decide to play around with Drupal as a way to expose data sets. About a decade ago I was part of a group of people who built a first of its kind application to send and receive data from wholesale power markets in North America. The application was built on three tiers: a client, an application server, and a database server. The client and application server were written in Java and the database relied on Oracle technology. It was about as far away from open source as you could get. The product was successful though and we did hundreds of installs all across North America. What the product basically did for its owners was suck in data from a central server then expose that data to the user via tables and charts visible from the user interface. At some point last year I started wondering if I could do something similar with Drupal. So I started investigating, tried out different modules and realized that I could do even more with a Drupal install than we did with that product.
There are four tasks that need to be accomplished if you want to use Drupal as an effective data platform.
- Capture data to the Drupal database tables
- Setting the data types (text, numeric, etc.)
- Expose the data to the user using tables and charts
- Offer simplified download format options
Capturing Data
I have been capturing data using the Table Wizard module. Table Wizard allows you to create a database table by uploading a delimited (like tab or comma separated) file. This works pretty well. I recently uploaded a file with over 300,000 records and it was in the database within a few minutes. After you upload your file you click on the table name to "analyze" the table. Table Wizard will let you know what columns you have in the table, the data types and identify the primary key. When you first upload your data you will want to go switch over to your database to change data types of the appropriate ones and identify a primary key. Once you have done that you can come back and re-analyze the table.
Setting the Data Types
This is probably the step that will be the least welcome to those who are not familiar with databases. If you want to do extensive work with data in Drupal then you will want to become familiar enough with phpMyAdmin and MySQL data types to be able to navigate to your database and apply the appropriate settings for each type of data. You should also indicate which field contains the primary key for the table. The primary key is a unique value that identifies each record in a table. If you have a dataset that does not have a column with unique values then consider adding a column unique numeric code in each row. You can name the column something like record_id and then add values like 100001, 100002, 100003 and so on.
Expose the Data Using Tables and Charts
If you have been a good boy or girl and added a single field primary key then the Table Wizard module will let you check a box that is labeled, "Provide default view." This option automatically creates a view that you can expand upon as you see fit. Auto created table wizard views will have the tag "tw" added to them. You can also create a view by navigating to /build/views/add and looking for the radio button in the 'View Type' section that starts with Database table. At this point you need to a thing or two about the Views module to go further. There's not enough space to cover that here so I'm going to skip over the details and point you to the key modules and settings.
The standard views module will let you create a table from your data. You just have to choose a Page display and choose the Table style. You can then choose your fields which will appear as columns within the table. Give the page a Path and perhaps add an Exposed Filter or two to allow users to narrow down the data they are looking at. If you want to get a little fancy you can choose use Views Calc to create tables that include calculations like min, max or average on a set of numeric data.
Charts are a little more tricky but it can be done with the assistance of Charts and Graphs and Views Charts. Charts and Graphs allows you to integrate a number of different free charting solutions (including Google Charts) into your site. View Charts makes those features available as a Style (called "Charts") within the Views interface. The chart gets exposed on a page based on the Path that you define. You can also create a chart within the Block display type. There are other charting modules that exist for Drupal. All of them have some drawbacks but so far the Charts and Graphs/Views Charts combo has worked the best for me.
Offering A Download Option
The ability to download data probably won't be a must have feature for every site. After all someone could probably just copy the table and paste it into their favorite spreadsheet program. It's a nice feature to offer though and you can do it pretty easily with the help of the Views Bonus Pack module. With Views Bonus Pack installed you get the option to add a Feed display within the Views interface. With the Feed display selected you then get the option of setting a Style that includes the option for CSV and other popular formats like TXT, DOC, XLS and XML. You can then attach that display to the Table display and there will be an image that appears at the bottom of the table page that will generate a file download when clicked. Be sure to add the same filters to the CSV display that you have added to the Table display so the downloaded file reflects what the user is seeing with any filters selected.
The usual caveats apply to the ideas and tips that I've offered here. At any time a better module could come along or an existing module could change and alter the process a bit. So tread lightly and do some exploration before you commit to building a data driven site using Drupal. In fact, even though there was a very recent release of Table Wizard the development is being deprecated in favor of the Data module. I'll continue to upload using Table Wizard for now since I think it works well but also plan on giving the Data module a shot in the near future.
As always I'd love to hear your thoughts and tips on modules and workflows in the comments. If you have built or know of a good data access website built using Drupal feel free to share those links as well.
Bookmark or Share Post
