1 / 20

Dashboard Creation

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

chogan
Download Presentation

Dashboard Creation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dashboard Creation Mapping SQL queries to Google Chart Images

  2. 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

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

  4. Google Charts • Images generated via parameterized URL strings (REST queries) • Google Charts Developer’s Guide: http://code.google.com/apis/chart/image/docs/making_charts.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

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

  6. Types of Google Charts

  7. 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

  8. 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

  9. Sample Aggregate Query From AdventureWorks database: SELECT Production.ProductCategory.Name, COUNT(*) FROM Production.ProductCategory FULL OUTER JOIN Production.ProductSubcategory ON Production.ProductCategory.ProductCategoryID = Production.ProductSubcategory.ProductCategoryID FULL OUTER JOIN Production.Product ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID GROUP BY Production.ProductCategory.Name order by count(*) desc

  10. Sample AggregateQuery From AdventureWorks database: SELECT Production.ProductCategory.Name, COUNT(*) FROM Production.ProductCategory FULL OUTER JOIN Production.ProductSubcategory ON Production.ProductCategory.ProductCategoryID = Production.ProductSubcategory.ProductCategoryID FULL OUTER JOIN Production.Product ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID GROUP BY Production.ProductCategory.Name order by count(*) desc Grouping column, aggregate column Grouping clause

  11. Result Set (from AdventureWorks 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=310x150 &chtt=Product Category Counts &chma=30,30,30,30&chco=FF0000,00FF00,0000FF,000000,FFFFFF,FF00FF,00 Mapped to Google Chart URL String

  12. Result Set (from AdventureWorks database) Aggregate data are normalized, then become the data values in the GoogleCharts 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=310x150 &chtt=Product Category Counts &chma=30,30,30,30&chco=FF0000,00FF00,0000FF,000000,FFFFFF,FF00FF,00 Mapped to Google Chart URL String

  13. Result Set (from AdventureWorks database) Grouping field results become the label values in the GoogleCharts 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=310x150 &chtt=Product Category Counts &chma=30,30,30,30&chco=FF0000,00FF00,0000FF,000000,FFFFFF,FF00FF,00 Mapped to Google Chart URL String

  14. URL String Returns Image 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=310x150 &chtt=Product Category Counts

  15. 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.)

  16. Result Set (from AdventureWorks 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.1394825646794,0.134983127109111,0.0899887514060742,0.0899887514060742 &chld=CAWAORILOHTX&chs=250x150&chtt=Tot Customers by State&chma=30,30,30,30&chco=FFFFFF,EEEEEE,0000FF,FF0000&chf=bg,s,EAF7FE C C Mapped to Google Chart URL String

  17. URL String Returns Image http://chart.apis.google.com/chart?cht=t&chtm=usa&chd=t:100,51.4060742407199,24.1394825646794,0.134983127109111,0.0899887514060742,0.0899887514060742&chld=CAWAORILOHTX&chs=250x150&chtt=Tot Customers by State&chma=30,30,30,30&chco=FFFFFF,EEEEEE,0000FF,FF0000&chf=bg,s,EAF7FE

  18. 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

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

  20. Example Drill-Down

More Related