Transcription

Guided Tutorial for Pentaho Pivot4JThis tutorial demonstrates usage of Pentaho BI Server with the Pivot4J plug-in to createpivot tables. You will use data sources and cubes that are already exist in Pentaho. Datawarehouse processing requires a multidimensional data representation that is convenient forbusiness analysts. Data cubes, a widely accepted representation of multidimensional data,support business intelligence processing by business analysts. In a data cube, cells containnumeric data called measures while rows and columns contain dimensions to organize the cells.In Pentaho, pivot tables and pivot charts provide a convenient and flexible interface formanipulating data cubes. Pivot tables display data in rows and columns and allow convenientrearrangement of the row and column headings. Pivot charts display numerical data graphicallyto provide insights to business analysts. Pivot tables support dynamic manipulation of the rowand column headings. Pivot tables in Pentaho are supported through a plug-in called Pivot4J.1. Starting Pentaho Pivot4J PluginThis tutorial assumes that you have followed the instructions given in the document toinstall the Java Runtime Environment and the community edition of Pivot4J. Here are the stepsto start the Pentaho BI server and use the plugin after installation. les\Pentaho\biserver-ce and right click the start-pentaho.bat file and select Run asadministrator. A command window will open showing some executing commands. Keepthe command window (Figure 1) open. You can make a shortcut to this batch file to makeit more convenient to launch the BI Server. For MAC users, you should use the terminal to navigate to the directory biserver-ce/and execute the set-pnetaho-env.sh file using the command “./set-pentaho-env.sh” Youhave to run this command only once. Then, execute the file start-pentaho.sh by using“./start-pentaho.sh”. You need to use only the last command to start pentaho for the nexttime.

29 January 2015 Guided Tutorial for Pentaho Pivot4J Plugin2To start the web interface for the Pentaho BI server, you should open your browser andnavigate to the address http://localhost:8080/. After you launch the browser interface, you should click on Login as an Evaluator.Use the Administrator account to login (Figure 2). The default page of the platform willappear as in Figure 3.Figure 1: Windows Command Window for the BI Server

29 January 2015Guided Tutorial for Pentaho Pivot4J PluginFigure 2: User Console Login Window3

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin4Figure 3: Home Page of the User Console2. Creating a Pivot TableThis tutorial uses a sample data source and cube that are already exist in Pentaho todemonstrate creation of pivot tables. The tutorial does not cover the process of importing orconnecting to other data sources or the steps to create cubes. To create a pivot table in Pentaho,follow the next steps: Select data catalog and cube: Click on File New Pivot4J View. A new tab openswith two options to select the catalog and the cube (Figure 4). Select SteelWheels as acatalog. The SteelWheelsSales cube will be then selected by default. When you finishclick OK. An empty Pivot table will open (Figure 5).

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin5Figure 4: Catalog and Cube SelectionFigure 5: Pivot4J Table View Customize the Pivot Table: This table allows users to customize the contents of the tableas well as the properties of the table (Figure 6). Customization will be discussed in thefollowing section. When you finish, save the table as a report by clicking on Save ()button. Write “SteelWheelsReport" as the file name and keep the default location/home/admin. Open saved reports: To open your saved reports, click on File Open. Navigate to therequired directory e.g. /home/admin. Select the report and click Open.

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin6Figure 6: Pivot Table Using Pivot4J View3. Customizing the Pivot TableYou will customize the pivot table by placing fields, modifying the axes, and addingaggregation fields as demonstrated in the following steps. Place Fields on the Diagram: To place the fields onto the diagram, you may drag them fromthe Cube structure at the top into the Pivot structure at the bottom. To remove the fieldsfrom the diagram, drag them from the Pivot structure to the Cube structure. Until you are familiar with the design window’s labeled areas, it is suggested that you use thearea choices as follows: place the Sales (from Measures) field in the columns, the Type(from Order Status) field in the columns area, and the Product (from Products) field in therow area (Figure 7). Modify the Pivot Table: You may add aggregation fields to the table. Common use ofaggregation fields is to calculate the total of rows or columns. Click on Agg. button. TheAggregation Configuration window opens. In the Columns section under Axis Aggregationtab, Click on Total from the Available field and hit the arrow to move it to the Selected field(Figure 8). Click Ok. The Pivot table now shows the total sales for each product (Figure 9).

29 January 2015Guided Tutorial for Pentaho Pivot4J PluginFigure 7: Pivot Table after Adding ValuesFigure 8: Aggregation Configuration7

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin8Figure 9: Pivot Table after Adding Total Field for Columns4. Extending the Pivot TableThe pivot table in the previous section is somewhat limited because it lacks a timedimension, an important element of most data cubes. In addition, the pivot table lacks a filterfield to restrict the data displayed. In this section, you will add these elements to the previouspivot table and then demonstrate some additional features of pivot tables. Add more fields: Place the Years (from Time) field in the Rows and Markets in the Filter(Figure 10). Change rows order: Placing Time before Products in figure 10 is not effective. Instead,dragging Product to Rows will change the order of the rows as in Figure 11. Expand and collapse views: You may now click on the plus sign next to the year andexpand the view to reveal the quarters of the year (Figure 12). Filter the Pivot Table: To see the effect of filtering, drag Markets form the cube structureto the Filter area. Expand the NA and then select USA (Figure 13). Click Ok. Figure 14shows the pivot table after applying the filter. Select specific Attributes: To select specific attributes to show in the table, click on themagnifier symbol next to the Order Status dimension name in the pivot structure.Remove all attributes from selected member’s field except On Hold and Shipped (Figure15). Click OK. Figure 16 shows the resulting pivot table.

29 January 2015Guided Tutorial for Pentaho Pivot4J PluginFigure 10: Pivot Table after Adding the Extra FieldsFigure 11: Pivot Table after Changing the Row Order9

29 January 2015Guided Tutorial for Pentaho Pivot4J PluginFigure 12: Pivot Table after Expanding Years into QuartersFigure 13: Pivot Table Filtering10

29 January 2015Guided Tutorial for Pentaho Pivot4J PluginFigure 14: Pivot Table after Filtering on USAFigure 15: Hierarchy Configuration of Order Status Dimension11

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin12Figure 16: Pivot Table after Attributes Selection5. Pivot4J with MDXMulti Dimensional Expression or MDX is a query language developed by Microsoft tomanipulate multidimensional information (data cubes). Pentaho with its Pivot4J plug in supportsMDX queries. To use MDX, simply start typing the query syntax in the MDX query field locatedin the bottom of the window. An example of MDX query is shown in Figure 17. Also, MDX inPentaho is dynamic. Every time you drag and drop dimensions or expand and collapse thehierarchy, Pivot4J provides the corresponding MDX statement.

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin13Figure 17: Query Example for the SteelWheelsSales CubeTo familiarize yourself with MDX, start a new pivot4J by clicking on File New Pivot4J View. Select SteelsWheels as the catalog and SteelWheelsSales as thecube and click OK. Try the followings and notice the changes to the syntax in the MDX query: Erase everything in the MDX field and type the following syntax:SELECT CrossJoin({[Order Status].[Shipped], [Order Status].[Resolved]},{[Measures].[Sales], [Measures].[Quantity]}) ON COLUMNS, {[Time].[2003],[Time].[2004]} ON ROWSFROM [SteelWheelsSales]WHERE ([Product].[Classic Cars])Click on Run. You should get the result similar to Figure 18. Start dragging or removing some of the dimensions from the cube structure to thepivot structure or vise versa. Expand and collapse some of the hierarchies by clicking on the plus sign next to thefiled name.

29 January 2015Guided Tutorial for Pentaho Pivot4J Plugin14Figure18: Result for the MDX queryThe MDX clauses that are used in the previous statement are: Select: This clause sets dimensions in the column or row axis CrossJoin: This clause combines two or more dimensions in single axis From: This clause determines the cube where the data come from Where: This clause slices the data for specific attributes in a dimension6. Stopping Pentaho Pivot4J PluginWhen you are finished, you should close the browser or logout and execute the rogramFiles\Pentaho\biserver-ce and double click the stop-pentaho.bat file. A commandwindow will open showing some executing commands. Then both command windowswill close. In Mac, you should run “./stop-pentaho.sh”.

install the Java Runtime Environment and the community edition of Pivot4J. Here are the steps to start the Pentaho BI server and use the plugin after installation. For WINDOWS users, you should go to the folder C:\Program Files\Pentaho\biserver-ce and right click the start-p