Dashboard Creation Mapping SQL queries to Google Chart

Dashboard Creation Mapping SQL queries to Google Chart Images

SQL – to – Visualization Mappings • Idea: – Automatic mapping of SQL queries to visualization scripts – Enable easy creation of dashboards based on SQL queries – Enable drill-down and other interactive features – Provide a pedagogical tool for teaching practical data visualization techniques

Data –to- Visualization Mappings Visualiza tion Object Data Source SQL Visualiza tion Object SQL Data Source Query results SQL User-generated SQL queries Data-to-Visualization Mapping Algorithms Visualiza tion Object

Google Charts • Images generated via parameterized URL strings (REST queries) • Google Charts Developer’s Guide: http: //code. google. com/apis/chart/image/docs/making_chart s. html • Parameters include chart type, chart properties, data, legends, chart sizes and colors, etc. • Google returns a PNG file with the graphic for the request

Example Google Charts REST String http: //chart. apis. google. com/chart Type = 3 D Pie ? cht=p 3 Data &chd=t: 100, 90, 80, 45, 42. 5, 15 Labels &chl=First|Second|Third|Fourth|Fifth|Sixth Size &chs=500 x 200 Title &chtt=My Chart &chco=FF 0000, 00 FF 00, 0000 FF, 000000, FFFFFF, FF 00 FF, 00 FF 00, 00 FFFF, DDD, AAAAAA, 555555 Colors (per slice) Resulting Image

Types of Google Charts

SQL Aggregate Queries • SQL Aggregate queries provide summary results (sums, averages, counts) with grouping • SELECT <grouping field>, <aggregate function> FROM <table or join> GROUP BY<grouping field> • Ideal for these visualizations: bar, line, and pie charts

Mapping Aggregate SQL Queries to Google Charts • Aggregate query • From query’s result set, generate the URL parameter values for: – chl (from grouping column) – chd (from aggregate column) – NOTE: for Google Charts, data range is 0 -100. So original data must be normalized before being sent. • Other parameters are set based on user preferences

Sample Aggregate Query From Adventure. Works database: SELECT FROM Production. Product. Category. Name, COUNT(*) Production. Product. Category FULL OUTER JOIN Production. Product. Subcategory ON Production. Product. Category. ID = Production. Product. Subcategory. Product. Category. ID FULL OUTER JOIN Production. Product ON Production. Product. Subcategory. ID = Production. Product. Subcategory. ID GROUP BY Production. Product. Category. Name order by count(*) desc

Sample Aggregate. Query From Adventure. Works database: Grouping column, aggregate column SELECT FROM Production. Product. Category. Name, COUNT(*) Production. Product. Category FULL OUTER JOIN Production. Product. Subcategory ON Production. Product. Category. ID = Production. Product. Subcategory. Product. Category. ID FULL OUTER JOIN Production. Product ON Production. Product. Subcategory. ID = Production. Product. Subcategory. ID GROUP BY Production. Product. Category. Name order by count(*) desc Grouping clause

Result Set (from Adventure. Works database) http: //chart. apis. google. com/chart? cht=bhs &chd=t: 100, 64. 1148325358852, 46. 4114832535885, 16. 7464114832536, 13. 8755980861244 &chxt=x, y&chxl=0: |0|209|1: |Accessories|Clothing|Bikes|Components|NULL &chs=310 x 150 &chtt=Product Category Counts &chma=30, 30, 30&chco=FF 0000, 00 FF 00, 0000 FF, 000000, FFFFFF, FF 00 FF, 00 Mapped to Google Chart URL String

Result Set (from Adventure. Works database) Aggregate data are normalized, then become the data values in the Google. Charts URL. http: //chart. apis. google. com/chart? cht=bhs &chd=t: 100, 64. 1148325358852, 46. 4114832535885, 16. 7464114832536, 13. 8755980861244 &chxt=x, y&chxl=0: |0|209|1: |Accessories|Clothing|Bikes|Components|NULL &chs=310 x 150 &chtt=Product Category Counts &chma=30, 30, 30&chco=FF 0000, 00 FF 00, 0000 FF, 000000, FFFFFF, FF 00 FF, 00 Mapped to Google Chart URL String

Result Set (from Adventure. Works database) Grouping field results become the label values in the Google. Charts URL. http: //chart. apis. google. com/chart? cht=bhs &chd=t: 100, 64. 1148325358852, 46. 4114832535885, 16. 7464114832536, 13. 8755980861244 &chxt=x, y&chxl=0: |0|209|1: |Accessories|Clothing|Bikes|Components|NULL &chs=310 x 150 &chtt=Product Category Counts &chma=30, 30, 30&chco=FF 0000, 00 FF 00, 0000 FF, 000000, FFFFFF, FF 00 FF, 00 Mapped to Google Chart URL String

Visualizing Geographic Data • The Google Charts API includes map charts in which you can adjust colors of a map’s regions using numeric data. • Good for use on aggregate queries where the group attributes are geographic units (e. g. states of the US, countries of Europe, etc. )

Result Set (from Adventure. Works database) Grouping field in result set (state abbrev) is associated with the states of the chart (cht=t indicates a map, chtm=usa indicates US map) http: //chart. apis. google. com/chart? cht=t&chtm=usa&chd=t: 100, 51. 4060742407199, 24. 139 C 4825646794, 0. 134983127109111, 0. 0899887514060742 &chld=CAWAORILOHTX&chs=250 x 150&chtt=Tot Customers by C State&chma=30, 30, 30&chco=FFFFFF, EEEEEE, 0000 FF, FF 0000&chf=bg, s, EAF 7 FE Mapped to Google Chart URL String

URL String Returns Image http: //chart. apis. google. com/chart? cht=t&chtm=usa&chd=t: 100, 51. 4060742407199, 24. 139 4825646794, 0. 134983127109111, 0. 0899887514060742&chld=CAWA ORILOHTX&chs=250 x 150&chtt=Tot Customers by State&chma=30, 30, 30&chco=FFFFFF, EEEEEE, 0000 FF, FF 0000&chf=bg, s, EAF 7 FE

Drill-Down • From aggregate, get sub-aggregates for each group • For a given query, you can specify a sequence of drill-down fields, which are used for each subsequent drill-down request

Example Drill-Down SELECT FROM Production. Product. Category. Name, COUNT(*) Production. Product. Category FULL OUTER JOIN Production. Product. Subcategory ON Production. Product. Category. ID = Production. Product. Subcategory. Product. Category. ID FULL OUTER JOIN Production. Product ON Production. Product. Subcategory. ID = Production. Product. Subcategory. ID Original GROUP BY Production. Product. Category. Name order by count(*) desc query (aggregate) Production. Product. Subcategory. Name Drill-down field SELECT Production. Product. Subcategory. Name, COUNT(*) FROM Production. Product. Category FULL OUTER JOIN Production. Product. Subcategory ON Production. Product. Category. ID = Production. Product. Subcategory. Product. Category. ID FULL OUTER JOIN Production. Product ON Production. Product. Subcategory. ID = Production. Product. Subcategory. ID WHERE Production. Product. Category. Name = 'Accessories' GROUP BY Production. Product. Subcategory. Name ORDER BY count(*) desc Modified query (sub-aggregate)

Example Drill-Down