SpagoBI Lesson 3: Highchart Dashboards
Business Intelligence dashboards
Every car comes with a dash board which has several gauges that alerts the driver when an important event have occurred. It might be that the car is running low on fuel or the engine oil is getting low. It might be also that the battery is nor charging or you are driving with handbrake on! An automobile dashboard need to be easy to understand and should not take time to read, remember you are driving. A good dashboard should be easy to understand and should portray relevant information. The business community copied this dashboard idea from the automobile industry. Business dashboards show at a glance the state of the business at any given time. As an example, a chart might show a comparison between sales between current quarter and the last quarter. If all sales for previous quarters are greater than current quarter, then something is definitely wrong.
Since a dashboard should be easy to read, normally only summaries are shown in dashboards. Dashboards also show trends and comparisons.
We will create our first dashboard to compare sales between current year against sales for previous year. The dashboard will be built using the highcharts library. First, we will write the Sql that will help us get the sales comparisons between current year and previous year for the Nairobi store.
Here is the result of the query:
- image0011
- image0031
And here is the query:
You can find the query under the dashboard folder in the CD that came with this book. It is called dash1.txt. In case you are reading this at spagolabs blog, then you can get the mysql dump and other files needed for this tutorial by writing the author at the email xogutu at gmail dot com for a small fee.
Now that we have the query, we will create the xml template that will be used by highcharts. We have included the xml below, it can be found under the dashboard folder in the CD that came with this book. The xml file is called sales_comparison.xml.
<HIGHCHART width=’80%’ height=’80%’>
<CHART zoomType=’xy’ />
<TITLE text=’Nairobi sales comparison, current year vs previous year.’ />
<SUBTITLE text=’Detail for 2011, 2010′ />
<X_AXIS alias=’current_month’ />
<Y_AXIS_LIST>
<Y_AXIS alias=’sales_previous_year’ opposite=’true’>
<LABELS>
<STYLE color=’#89A54E’ />
</LABELS>
<TITLE text=’Sales Previous Year (2010)’>
<STYLE color=’#89A54E’ />
</TITLE>
</Y_AXIS>
<Y_AXIS alias=’sales_current_year’ gridLineWidth=’0′>
<LABELS>
<STYLE color=’#4572A7′ />
</LABELS>
<TITLE text=’Sales Current Year (2011)’>
<STYLE color=’#4572A7′ />
</TITLE>
</Y_AXIS>
</Y_AXIS_LIST>
<LEGEND layout=’vertical’
align=’left’
verticalAlign=’top’
x=’120′
y=’40′
floating=’true’
borderWidth=’1′
backgroundColor=’#FFFFFF’
shadow=’true’/>
<SERIES_LIST allowPointSelect=’true’>
<SERIES name=’Sales Previous Year (2010)’ color=’#89A54E’ type=’spline’ alias=’sales_previous_year’ />
<SERIES name=’Sales Current Year (2011)’ color=’#4572A7′ type=’spline’ alias=’sales_current_year’ dashstyle=’shortdot’>
</SERIES>
</SERIES_LIST>
</HIGHCHART>
Now login to SpagoBI as the biadmin user and follow these steps to create the dash board.
1. Create a folder in SpagoBI, call it Dash Boards.
2. Click on Resources -> dataset.
3. Click on the add button.
4. On the Label, write “SalesComparisonNairobi”
5. On Name, write “SalesComparisonNairobi”
6. On Description, write “Sales comparison for Nairobi store between between current year and previous year.”
7. You should have the following once you are done.
8. Click on the Type TAB.
9. Under DataSet Type, select Query.
10. Under data source select “Sapgo BI Mysql Training”. Remember we had created this datasource previously.
11. Under Query, Paste the query in the file dash.txt on the folder dashboard.
12. You should have this once you are done.
13. Click on preview button. You should have the output shown below.
14. Save the data set.
15. Click on Analytical Model -> Documents Development.
16. Click on Insert. Add create the document as shown below.
17. Click on browse and select the sales_comparison.xml file we created previously. The file can be found on the CD that came with this book under dashboard folder.
18. Under documents template, select “Dash Boards” and click Save.
19. Click on “Home Page”
20. Navigate to “Dash Boards” folder. Click on “Sales Comparison” document to run it.
You should have your first chart!
Now let us look at sales_comparison.xml file to better understand it.
A. Chart Size.
The first element must be HIGHCHART. You can also specify the size of the chart in this element.
<HIGHCHART width=’80%’ height=‘80 %’>
B. TITLE and SUBTITLE.
The TITLEand SUBTITLE elements are the title and subtitle of the charts.
<TITLE text=’Nairobi sales comparison, current year vs previous year.’ />
<SUBTITLE text=’Detail for 2011, 2010′ />
C. The X_AXIS element.
The element <X_AXIS alias=’current_month’ /> is the label of the X axis. In Spago Bi, this can come from a column in your data set query.
In our chart, the labels come from the database column current_month from the dataset SalesComparisonNairobi we created previously.
The contents of current_ month column in the data set SalesComparisonNairobi through the xml element <X_AXIS alias=’current_month’ /> is used to label the x axis with January up to December on our chart.
D.
Y_AXIS_LIST Element.
The <Y_AXIS_LIST> element defines the items that appear on the Y axis. Normally they come from database tables or views through the SpagoBi data set. For example, in the block of xml code below:
1. Alias element.
alias=’sales_previous_year’ – This defines which column in the dataset query the data comes from.
opposite=’true’ – When opposite is true, the label will appear on the right. When false it appears on the left.
As an example, if we say opposite is false as shown below,
The Y axis elements will be on the left.
Change it to opposite=true and the Y axis items will appear on right.
gridLineWidth=’1′ – Use 0 if you do not need a grid line.
2. The <TITLE text=’Sales Current Year (2011)’> element.
This element is used to set the labels of the items on the Y axis.
E. The LEGEND element determines the position, color and other properties of the legend.
The above block of code produces the legend below.
F.
The SERIES_LIST element is the main contents of the chart. It can be line or column.
Since our chart has two lines, it therefore will have two series. The contents of the series come from a table column through the Spago BI dataset. For example, the dottet series (Sales for 2011) comes from the column sales_current_year and the element dashstyle=’shortdot’ makes it dotted. The element type=’spline’ means a line chart, if we change it to type=’column’ , we will end up with a bar graph as shown: