Wednesday, 14 April 2010

What does the perfect ad-hoc reporting database look like?


So you have Tableau or another reporting tool which can read from a live database?  But your database has crazy field names, and loads of fields which are holding up your analysis.

What attributes does the perfect data source for Ad-hoc reporting have?

I came up with these – I’d love to know if you can think of more!

1. The data source should be well isolated in the database, so that users can find the correct data source immediately

2. There should be no requirement to create joins to other tables

3. The fields in the data set should be returned in an appropriate order – typically in order of importance, with some grouping determined by the purpose of the data

4. The field names should be semantically correct, using business terms and have a consistent naming convention

5. Fields should be of the correct type so they are interpreted correctly by the reporting tool (date, numbers strings etc)

6. The data source should be high performance (< 2 second response time)

7. Any geo spatial data should be at an appropriate level of zoom – particularly important for postcode data for example (outcode is most often appropriate, rather than the full postcode)

8. The data set should contain no ID fields, except for the primary entity in the data set (i.e. the list of properties SHOULD contain the PropertyID field, but no others)

9. The data set should have the minimum number of fields possible. It is better to handle requests for additional data items than to flood the data set with fields which are not used and confuse the end user)

10. Date fields should be complete dates, not part of dates like year or month.  A good ad-hoc reporting tool will handle this easily.

Enjoy!!  And please let mw know of any more!