How to Build a What’s Trending Dashboard from a vBulletin Community Forum Database

These days it seems everyone wants to know what everyone else is talking about: What’s trending on Twitter or Facebook or Google Search, etc.

Well I wanted to know what our world-wide community of customers and developers were talking about in regards to our DataFlex products and their own development work. And I figured our company’s management and development team - along with our entire community - would too.

Data Access Worldwide’s community forums, which are powered by vBulletin, seemed like the perfect data source, and Dynamic AI the perfect tool to mine that data, and publish the information on-demand over the web. So I built this interactive dashboard: Trending on DAW Forums

It shows the top 10 subjects being discussed on our public community forums within four different slices of time: 24-hours, 7-days, 30-days, and 90-days. Our forums are utilized by many as an historical knowledgebase and the message threads are viewed repeatedly over a long period of time, so it’s not unusual to see a top-trending subject appear in more than one of these slices of time - a useful indicator of long-lived topics that developers are interested in, indeed!

At a glance you can see in the web reports what subjects (forum threads) are the most viewed over each of these time slices including how many replies were posted in the subject threads. Clicking on any subject shows who posted the message and when. From there you can click on the row icon to display all messages in the thread in the date-time order they were posted, and see the first couple lines of the message text. From there a link lets you jump into the actual complete thread in our vBulletin forum system.

So the dashboard consists of four report grids, one for each of the time slices mentioned. No programming was required to create the report presentations, or to combine the four into a single dashboard presentation, though I did write some formula expressions in the reports to define the time slice condition and generate the hyperlinks into our actual forums. Essentially I created one report and then cloned it three more times, changing only the title and the query condition applied in each report to the created date-time stamp on the thread. Each of those reports’ final drill-down path calls a fifth report that displays the actual message postings, automatically constraining that report to whatever specific subject thread was clicked on – all without any programming.

Tip: I used Dynamic AI to discover just where the data resided and how it was structured in the vBulletin database, for which I could find no documentation. In fact, I use Dynamic AI on virtually every development project in order to very quickly and efficiently learn about the database structure and data distribution in whatever database is used by a given application. It’s an invaluable tool and time-saver, but that’s a topic for a future blog!

For those interested in some of the under-the-hood technical details, the rest of this blog covers the following aspects:

1. vBulletin data sources
2. Report design: totals, drill-down path, etc.
3. Fixed condition that defines the time slice
4. Styling and combining 4 reports into 1 dashboard.

1) vBulletin Data Sources

The database is MySQL, and the main table needed is called vb_thread. Among its 32 columns are: ThreadID, Title, ForumID, ReplyCount, PostUserID, PostUserName, Views, and DateLine (the DateTime stamp the thread was created. These columns can be used to join to other tables such as vb_Forum, to get other descriptive information, etc. The ThreadID column value is used to allow making a drill-down report into the actual messages which live in the vb_Post table.

Note all of the datetime values in the database are in Unix format which stores date-times as integer values according to its own counting scheme. That integer needs to be converted to a recognizable date value using the built-in MySQL function: from_UnixTime(date-time_col_name). This can be done easily as a formula column in the report design, or in a view, which is simply a select statement saved in Dynamic AI’s repository and becomes a datasource available for any number of reports.

Due to some complications of DAW’s implementation of a hierarchy of forums, the need to enforce the same security as authenticated public access to forums enforces, and some cosmetic goals, I chose to create a Dynamic AI view that is a bit more complicated than a simple query on the vb_Thread table. For those interested, the SQL script and the sample report documentation are linked to at the end of this blog. (Don’t misconstrue the extensive technical details in that documentation as any indication it is technically demanding to make such reports. It was quite simple actually – and quick and easy to learn how to do!)

2) Report Design: Totals, Drill-down path, etc.

As examples of business intelligence reports, these are about as simple as it gets. Essentially the reports are simply reporting the top 10 detail rows from the data source based on the view count value in each row in descending order. A top 10 report in Dynamic AI requires a group total. The report is grouping on the ThreadID column and captioned with the Title column. The Views and Replies columns are defined as totals with the descending order applied to the Views total. So in this case each grouping consists of a single row and drilldown from that grouping is to the same detail row, but showing different columns.

The detail row provides two links for further drill-down. There is a column labeled Open DAW Thread and clicking on the data row thread below it that shows Open Thread will open the actual vBulletin thread inside the DAW forum application. Clicking on the row number cell, the unlabeled first column, will call a related report that will display all of the messages in the thread.

One of the nicest, most elegant and powerful tools in Dynamic AI is something called Dyn-Relations. These are simple metadata objects that allow defining that the data values in one or more columns from any data source in one database is related to the data in corresponding columns in any other data source. Those sources can even live in different databases and reside on different servers – anywhere. Those sources can be tables, back-end views, dyn-views, or dyn-joins.

In report design when defining what the final lowest level drill-down object will be, it can either stop at the detail row, proceed to a form, or proceed to a related report based on common data values in the single row used for drill-down, and the related report’s data source. In report design, Dynamic AI will present a drop down list of all of the reports that exist that were built using related data sources as defined in the dyn-relation object. Think of it as using the data values from a detail row in one report as providing dynamic runtime values to dynamically generated filters applied to a different report – automatically – with no programming involved.

The Open Thread hyperlink output on a detail row is an extra column defined in the report design. Dynamic AI allows defining a variety of formula columns that get added to the result set of the underlying query at runtime. These formula columns can also in turn be used as filters, groupers and totals just like the columns from the underlying data source. In this case the formula concatenates various data column values with string constants to form a valid URL. The formula looks like this and the most difficult thing about building the formula was getting the quote marks right! The command syntax in MySQL for concatenating strings is harder [for me] to write and read, so I broke it down into separate formulas and then referenced those formulas in my final formula:

• Open DAW Thread = Concat(d_T1_d,d_T2_d)
• T1 = ’<b><a style=’’color:#E54714’’; href = ’’http://support.dataaccess.com/Forums/showthread.php?’
• T2 = (Concat(cast(ThreadID as char(6)),’’’target=’’_DAW’’>Open Thread</a></b>’))

3) Fixed Condition to Define the Time Slice

Dynamic AI reports let you use any data source column as a run-time filter or a hidden fixed condition applied to the query behind the report. For date-time columns Dynamic AI offers a very long list of predefined date functions, ranging from a simple “yesterday’s date” to “first and last dates of the last quarter.”

You can also write any valid SQL expression using any number of data source columns and Boolean operators according to the SQL syntax supported by the back-end database, and this will get appended to the where clause before any runtime filters are applied to the report. Here are the fixed conditions defined for the four time slices / reports:

• Trending Last 24 Hours: thrd_DateStamp >=@yest()
• Trending Last 7 days: thrd_DateStamp >= CURDATE() - INTERVAL 7 DAY
• Trending Last 30 days: thrd_DateStamp >= CURDATE() - INTERVAL 30 DAY
• Trending Last 90 days: thrd_DateStamp >= CURDATE() - INTERVAL 90 DAY

4) Styling and Combining the Reports into a Dashboard

The final steps were to format the output to comport with DAW’s corporate brand and color scheme and combine into a single presentation. Lucky for me, this didn’t require any deep knowledge of HTML or CSS, and … ready, once again … no programming was required!

Dynamic AI defines various CSS classes and applies a style sheet to every report, form or dashboard. It comes with a library of CSS that can be modified and saved as a new style sheet. The class names are documented and it was simple trial and error that allowed me to tweak the right classes with color codes and fonts to achieve the results you see. Below the CSS style sheet used by this report is linked to. Within Dynamic AI’s repository you can enter additional parameters on how to use the style sheet for such things as a logo file name to use, adding rules to header and footer sections, etc.

A dashboard in Dynamic AI is simply a package of any combination of other existing report objects in its repository. You can click on any existing report in the repository to select it for inclusion in a new dashboard, and then click new/dashboard. So literally with 5 mouse clicks I created the final dashboard. Then I added some header comments to instruct users how to use the dashboard. I didn’t need to change any other available settings to force positioning such as wrap versus float. There are some powerful, creative behaviors available settings will activate for various kinds of reports or dashboards, but that’s for a future blog.

DOCUMENTATION:
DAW Forums Trending Last 7 Days Report Design.png
DAW Forums Trending Last 7 Days Report Documentation.html
DAW - No Selecter Frame.css
SQL script used to build the reports