Tableau Business Intelligence Software

I wanted to visualize some data to showcase the business intelligence capabilities of Tableau. I'm using the Tableau Public version.

I used to generate a monthly report for a client in Excel - the original source data is held in a mySQL database and exported using Navicat.

The report has various attributes - I wanted to visualize the percentage of call type in a certain postcode area (i.e. a morning or afternoon call) by duration (by the second) as my level of detail.

Tableau matches the postcode but not to street level, only the UK postcode area. This is actually a sensible filter for this example; but deeper granularity would be required for in-depth analysis.

Tableau Business Intelligence Software

Tableau makes it easy to connect to the data source. I did have to extract the postcodes in Excel from the address field using the =Right function and clean the postcode further by using =Left, and I also had to set the map view up as UK as the default is USA.

The end result was quick and effective. Multiple sheets can be shown in a dashboard - very handy for showing different views of related data, with multiple chart formats supported.

- Allan

Navicat for mySQL

If your organization uses mySQL as the database, I recommend Navicat for SQL.

Here is the rationale:

Visual Query Building - if you don't have strong syntax of SQL, you can build up queries using boolean operators to get a preview of the result prior to the query being executed.

Import / Exporting - Navicat can automate exporting of tables to common file formats, and can import using ODBC.

Backup - Navicat can automatically schedule a backup of the database.

Reports - visually build reports for invoicing and statistical analysis.

Navicat for MySQL

The image depicts an instance of a legacy mySQL database in a VMware Windows 2000 Server image inside a Windows 2003 server, securely accessed via a hardware Sonicwall Virtual Private Network SSL connection. The image was created using the Acronis Enterprise package as an ISO file.

Due to a corrupted Java module in the front end of the database GUI, an alternative method of invoicing had to be developed. This is where the features of Navicat helped - especially the exporting function. 

- Allan

Project Dashboards

As a Systems Engineer, I’ve been asked in the past by Project Management Office to develop a dashboard – also known as a scorecard – to visualise project data.
From experience, the most valuable data is the “burn down” chart – depicting the delta (change) metric of engineering or performance artefacts against a predicted schedule.
There are notable Business Intelligence Suites available – such as IBM Cognos – however; the ROI on such suites has to be judged. It may be worth investing in some development of existing tools – such as Microsoft Office. Most organizations will use Microsoft Project to manage their schedule(s) and users will be familiar with Excel. OLE (Object Linking and Embedding) Project and Excel is achieved using the paste special function, allowing dynamic updating. If managers simply want key information to be presented – typically for evidence of progress to director level – this can be presented in Microsoft PowerPoint and published on the intranet SharePoint.
Here’s an example in Microsoft Excel:

Microsoft Excel Project Dashboard

I have adapted this template courtesy of http://chandoo.org/ with typical ISO 15288 processes and activities. The Project plan has yet to be fully customised or populated, but it goes to show how fit for purpose Microsoft Excel is.

- Allan

Microsoft Dynamics CRM Evaluation

As Microsoft offered a 30 day evaluation of their cloud based CRM solution, I signed up to see what capabilities it offered; primarily to see what enhancements it could bring.

The online product is extremely comprehensive, offering rich dashboards – it does make it very easy to produce good visual interpretations of data.

Microsoft Dynamics CRM

The resource centre is useful for setting goals.
Microsoft also offer the CRM connector for Outlook, so I downloaded and installed – to track emails and contacts. However, this affected the performance of Outlook – taking up to 3 minutes to send and receive email – and longer to show appointment reminders. This really upset my workflow. I don’t think I am unusual in not using a desktop PC, I am working on a Dell laptop with 1GB of RAM and an Intel Centrino processor; a common setup for a mobile worker. After uninstalling, the performance returned.

So, my recommendation is if your organization wants a comprehensive CRM system with a shallow learning curve and a familiar graphical user interface – it does have an Office 2007/2010 “look and feel” - then the cloud offering is a great product with powerful features for management. I can’t recommend the Outlook connector as the capability trade off against performance isn’t worthwhile – maybe it would need more modern hardware and RAM and that adds to the through life cost.

I’m also sceptical about the overhead with most CRM systems – I believe the majority of the tools could be developed in Excel, and the processes could be managed within Outlook/Exchange. I think that for a decent Return Of Investment in procuring a CRM system has to be balanced against development of existing tools and the scale of the organization.
- Allan

Database to analysis tool connections

IBM Data Studio successfully connected to the DB2 instance.

IBM Data Studio

Toad successfully connected using ODBC driver.

Toad for Data Analysts

Rapid Miner successfully connected using JDBC driver.

Rapid Miner

- Allan

IBM DB2 SQL and CRM

I've downloaded IBM DB2 Express-C; IBM Data Studio; Toad for Data Analysts; Microsoft Dynamics CRM for Outlook and Rapid Miner 5.

I’m setting up a sample DB2 database, to connect with TOAD and Rapid Miner 5 using ODBC, and connect the IBM Data Studio via JDBC, then I’ll be able to test complex SQL queries, both via the command line, and visually.

IBM DB2 Express - C

- Allan