SpagoBI Lesson 2: OLAP with JPIVOT
Online Analytical Processing
Online Analytical Processing (OLAP) enables one to analyze different dimensions of multidimensional data. It enables one to analyze data from different perspectives. Consider sales data as an example. One might be interested in analyzing sales data in terms of the date when the sale occurred, the region the sales occurred, the store the sales occurred . The sales amount we are analyzing is called a measure. The way we analyze the measure (sales amount) is called a dimension. Therefore the sales date is one dimension of looking at the sales; the store where the sales occurred is another dimension of looking at the sales. We can therefore look at the sales data by date, by store e.t.c
We would like to demonstrate this in Spago BI using simple data with one dimension and several measures.
Below is the problem description:
Shemma Global is a Business intelligence company that specializes in data mining and analysis. They would like to view the memory usage of one of their servers by event time.
Olap 1: Server uptime data.
From the diagram olap 1 above, we are only interested in two columns, the event_date and used_memory. The event_date is our dimension (how we would like to view the data) and the used_memory is the data we would like to view (measure).
This kind of table is called a fact table. Normally dimension data like date, sales region e.tc are not stored in a fact table but in a dimension table. A foreign key is then included in the fact table to link the two .
Consider a telecommunication company as an example. We would make the fact table the central table in our schema surrounded by dimension tables. Below is an example.
Olap 2: Star Schema
In the above example, it is easy to answer questions like how many mobile phone subscribers were activated in the last quarter or how many subscribers are post paid or prepaid. The above schema is called a star schema. We would build a single fact table (Table 1) and link it to the dimension tables using foreign keys.
For the purpose of this demonstration, we will keep the measure and dimension in a single fact table.
Note: Email the author at xogutu at gmail dot com for the sample mysql database and other files used in this course for a small fee.
OLAP Cube.
An OLAP cube is a collection of measures (facts) and dimensions. In the telecommunication example above, we can create a cube which can answer questions like how many subscribers were activated on a certain year, certain quarter or certain month, or how much airtime was consumed by customers from Nairobi region or how many subscribers are in pre-paid.
Your first Jpivot document in Spago BI.
To create a cube in Spago BI, we will be using the SpagoBI JPivotEngine which comes embedded with your Spago BI server. The cube will be created using xml schema files. This will be a simple cube based on data from the diagram Olap 1 which shows the average amount of used memory by day. The average used memory here is the measure or fact while the day is the dimension. So our cube only has one dimension, the average amount of memory used on any given day. The resulting xml schema looks like the one below:
Olap 3: Mondrian Schema
Once you have created the schema, you need to tell SpagoBi server where the schema file exists. Navigate to the root directory of your SpagoBI installation, in my case C:BIAll-In-One-SpagoBI-3.0-apache-tomcat-6.0.18-06212011. We will call this the $SPAGO_ROOT directory. Inside $SPAGO_ROOT, navigate to resourcesOlap folder. Save your Mondrian schema here. Give it a name like AverageMemoryUsage.xml. Your folder will now appear as shown below.
Olap 4: Saving the Schema file.
Next, you have to add to the engine-config.xml file the schema you just created. Navigate to $SPAGO_ROOTapache-tomcat-6.0.18webappsSpagoBIJPivotEngineWEB-INFclasses folder. Inside there, you will find the file engine-config.xml. Open it using your favorite text editor and add the following lines.
<SCHEMA catalogUri=”/Olap/AverageMemoryUsage.xml” name=”AverageMemoryUsage” />
The line should be added between the <SCHEMAS>…. </SCHEMAS> tags. You have to restart your SpagoBI server for the changes to take effect. Once the server have been restarted, login to the url http://servername:8080/SpagoBI and login using the username biadmin and password biadmin.
Navigate to Resources-> Data source.
On the extreme right, click on insert button to create a new data source.
Once done, save the Data Source. We will be using this data source for the remainder of the book.
Now we will create a folder where all our OLAP documents will be stored. Click on Analytical Model -> Functionalities Management. Click on Functionalities then Insert.
We will create a folder called OLAP as shown below.
Under roles, select everything for /spagobi/admin (/spagobi/admin).
Finally we will create the OLAP document itself. Click on Analytical Model -> Documents Development. Click on Insert button.
Under Show document templates click on OLAP Documents. Click on Save. Click on Template build.
Select AverageMemoryUsage under Select schema. Under cube, select S MemoryUsage.
Now let us drill down and see the average memory usage for any day of the week. Click on the + next to All Types.
And there you have it, your very first OLAP document. You can see that the average memory usage for Saturday was 14,109.
Store Sales example.Problem definition: Shemma Global has offices in Nairobi, Kisumu, Mombasa and Kitale. The sales department would like to view the total sales for any store by year, quarter, month and day. Our measure is sales and we have a time dimension here with multiple hierarchies: year, quarter, month and day. Create a JPivot OLAP cube with SpagoBI to achive this.
1.
Build the Mondrian Schema
2. Save the file as ShemmaStoreSales.xml in the $SPAGO_ROOTapache-tomcat-6.0.18resourcesOlap folder.
3. Next, you have to add to the engine-config.xml file the schema you just created. Navigate to $SPAGO_ROOTapache-tomcat-6.0.18webappsSpagoBIJPivotEngineWEB-INFclasses folder. Inside there, you will find the file engine-config.xml. Open it using your favorite text editor and add the following lines.
<SCHEMA catalogUri=”/Olap/ShemmaStoreSales.xml” name=”ShemmaStoreSales” />
4. Restart the SpagoBI server.
5. Navigate to http://servername:8080/SpagoBI and login as biadmin.
6. Click on Analytical Model -> Documents Development.
7. Click on Insert and create a document similar to the one below.
8. Select OLAP Documents and click on Save.
9. Select “Generate New Template”.
10. Under Schema Select “ShemmaStoreSales”
11. Under Cube select “Store Sales”
12. Save the template and run the document.
13. Now let us compare Sales for Quarter 4 for Nairobi in 2011 against Sales for Quarter 4 for 2010.
14. Now let us compare sales between quarter 3 and 4 of 2011 for all stores.
15. Click on Measures, select all stores and click on Ok.
16. Click on Ok once again.
17. You will now be able to compare sales for any duration with any other duration for all the stores.
- image030
- image032
We will now add total sales to our example above. Modify the Mondrian schema above to include the following lines.
We have the total sales now, how can we move it to the last column?
References and further reading:
1. http://jpivot.sourceforge.net/
2. http://wiki.spagobi.org/xwiki/bin/view/spagobi_server/JPivot
This entry was posted in SpagoBI, SpagoBI Lessons. Bookmark the permalink.
Adding the line <SCHEMA catalogUri=… gives an error because the quotes are the wrong type
Once this is fixed I get the following error after selecting the schema and cube
org.apache.jasper.JasperException: An exception occurred processing JSP page /initialQueryCreator.jsp at line 324 321:
322: 323: 324: 325:
326: Resultset overflow occured 327:
Stacktrace:
org.apache.jasper.JasperException: An exception occurred processing JSP page /initialQueryCreator.jsp at line 324
321:
322: 323: 324: 325:
326: Resultset overflow occured 327:
Stacktrace: at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:505) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139) at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127) at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at it.eng.spagobi.utilities.filters.EngineCheckSessionFilter.doFilter(EngineCheckSessionFilter.java:80) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:722) Caused by: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS, {[Day of Week]} ON ROWS from [MemoryUsage] ]) at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:852) at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781) at org.apache.jsp.initialQueryCreator_jsp._jspService(initialQueryCreator_jsp.java:605) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374) … 23 more Caused by: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS, {[Day of Week]} ON ROWS from [MemoryUsage] ]) at org.apache.taglibs.standard.tag.el.core.IfTag.condition(IfTag.java:65) at javax.servlet.jsp.jstl.core.ConditionalTagSupport.doStartTag(ConditionalTagSupport.java:122) at org.apache.jsp.initialQueryCreator_jsp._jspx_meth_c_005fif_005f0(initialQueryCreator_jsp.java:883) at org.apache.jsp.initialQueryCreator_jsp._jspService(initialQueryCreator_jsp.java:576) … 26 more
javax.servlet.ServletException: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS, {[Day of Week]} ON ROWS from [MemoryUsage] ])
javax.servlet.ServletException: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS,
{[Day of Week]} ON ROWS
from [MemoryUsage]
])
at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:852)
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
at org.apache.jsp.initialQueryCreator_jsp._jspService(initialQueryCreator_jsp.java:605)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127)
at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at it.eng.spagobi.utilities.filters.EngineCheckSessionFilter.doFilter(EngineCheckSessionFilter.java:80)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:722)
Caused by: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS,
{[Day of Week]} ON ROWS
from [MemoryUsage]
])
at org.apache.taglibs.standard.tag.el.core.IfTag.condition(IfTag.java:65)
at javax.servlet.jsp.jstl.core.ConditionalTagSupport.doStartTag(ConditionalTagSupport.java:122)
at org.apache.jsp.initialQueryCreator_jsp._jspx_meth_c_005fif_005f0(initialQueryCreator_jsp.java:883)
at org.apache.jsp.initialQueryCreator_jsp._jspService(initialQueryCreator_jsp.java:576)
… 26 more
javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS, {[Day of Week]} ON ROWS from [MemoryUsage] ])
javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute “test” with value “${query01.result.overflowOccured}”: An error occurred while getting property “result” from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Memory Usage]} ON COLUMNS,
{[Day of Week]} ON ROWS
from [MemoryUsage]
])
at org.apache.taglibs.standard.tag.el.core.IfTag.condition(IfTag.java:65)
at javax.servlet.jsp.jstl.core.ConditionalTagSupport.doStartTag(ConditionalTagSupport.java:122)
at org.apache.jsp.initialQueryCreator_jsp._jspx_meth_c_005fif_005f0(initialQueryCreator_jsp.java:883)
at org.apache.jsp.initialQueryCreator_jsp._jspService(initialQueryCreator_jsp.java:576)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127)
at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at it.eng.spagobi.utilities.filters.EngineCheckSessionFilter.doFilter(EngineCheckSessionFilter.java:80)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:722)