Pentaho Mondrian OLAP CUBES Installation Install Tomcat version
Pentaho Mondrian OLAP CUBES
Installation • Install Tomcat (version 5. 0. 25 or later). – Set JAVA_HOME Environment Variable pointed to SDK installation.
Installation – Add %JAVA_HOME%bin to general path • Test TOMCAT installation: – localhost: 8080
Installation • Explode mondrian. rar to TOMCAT_HOME/webapps – Test: localhost: 8080/mondrian/
Creating OLAP Cube • Explode psw-ce-3. 6. 1. rar – Put mysql-connector-java-5. 1. 29 -bin in drivers folder. – Execute workbench. bat – Options Connection • Use the sakila user to connect to the database sakila – Open File: TOMCAT_HOMEwebappsmondrianWEBINFqueriesSakila. xml – See the CUBE Rentals
Mondrian Cube Structure Fact Table Dimension Levels Dimension Table Measure
Creating a Query to Rentals OLAP Cube • File New MDX query: – select {[Measures]. [Rental Counts]} on columns, {[Customers]. [All Customers]} ON rows from Rental – Execute in JPIVOT • http: //localhost: 8080/mondrian/testpage. jsp? query=re ntalquery 2
JPIVOT • Queries and cubes are stored in the folder webapps mondrian WEB-INF queries. – See the query: rentalquery 2. jsp. – See the cube Sakila. xml
Create a cube for Sakila Data Warehouse • Create a New Schema Sakila. Olap. xml – Save in TOMCAT_HOMEwebappsmondrianWEB -INFqueries – Configure the connection to the database with user sakila_dwh.
Create a cube for Sakila Data Warehouse • Create a cube named Rentals. – Add a fact table: link it to the table fact_rental – Add Customer dimension
Customer dimension • Add the a hierarchy for Customer Dimension • Add the table: dim_customer • Add a level (Name) for hierarchy.
Customer dimension
Film dimension • Add the a hierarchy for Film Dimension • Add the table: dim_film • Add a level (Name. Film) for hierarchy.
Film dimension
Date dimension • • Add the a hierarchy for Date Dimension Add the table: dim_date Add a level (Year) for hierarchy. Add a level (Month) for hierarchy.
Date dimension
Date dimension
Add a Measure for the Cube
Add a Query • Edit rentalquery 2. jsp – Change: • jdbc. Url="jdbc: mysql: //localhost/sakila_dwh? user=sakil a_dwh&password=sakila_dwh" • catalog. Uri="/WEB-INF/queries/Name. xml“ – Add the query: – select {[Measures]. [Metrica 1]} on columns, {([Customer], [Date])} ON rows from Rentals – Reload Tomcat server.
Open JPIVOT • Open http: //localhost: 8080/mondrian/testpage. jsp? query=rentalquery • Answer the following questions: – Año y mes donde se realizaron mas ventas? – Cuantas rentas hizo el usuario en el 2005 (en que meses? ).
Tarea • Incluya todas las dimensiones del modelo estrella de sakila. • Estudie las consultas MDX y construya dos consultas que incluyan la sentencia where y permitan explotar todas las dimensiones del cubo en jpivot. (http: //msdn. microsoft. com/eses/library/ms 145514. aspx):
- Slides: 22