Sales Analysis
Slice & Dice the data set ..
Workshop - Sales Analysis
The true power of business analytics lies not in creating static reports, but in building dynamic analyses that allow users to explore data from multiple angles, drill into details, and uncover insights on demand. In this comprehensive workshop, you'll master Pentaho Analyzer, the powerful OLAP analysis tool that enables interactive data exploration through drag-and-drop simplicity. You'll learn to create sophisticated pivot table analyses that business users can manipulate in real-time to answer their own questions without returning to IT for new reports.
In this hands-on workshop, you'll experience the full spectrum of Analyzer capabilities, from basic data selection through advanced features like calculated measures, conditional formatting, and drill-through functionality. You'll work with the Steel Wheels sample data to build analyses that reveal sales patterns across territories, product lines, and time periods. You'll learn to enhance your analyses with visual indicators like color scales and data bars, create custom calculations that extend your data model, and implement drill-through links that let users navigate from summary to detail seamlessly. You'll also explore the various visualization options that transform tabular data into compelling charts and graphs, and master the export capabilities that distribute insights across your organization.
What You'll Accomplish:
Understand the three fundamental field types in Analyzer (measures, dimensions, and properties) and how they work together
Build a pivot table analysis by dragging dimensions to rows and columns and measures to the data area
Apply multiple filters to restrict analysis to specific territories, time periods, and top performers
Implement sorting and hierarchical drilling to navigate from years to quarters to months to details
Add subtotals at multiple levels and grand totals for comprehensive summarization
Create conditional formatting using color scales, data bars, and trend arrows to highlight patterns
Build user-defined measures including percentage calculations and custom formulas (Sales plus Tax)
Enable drill-through links that allow users to view supporting transaction details from summary cells
Customize column headers, numeric formats, and presentation elements for professional output
Switch seamlessly between table and chart views (column charts, pie charts, heat grids)
Export analyses to multiple formats (PDF, Excel, CSV) for distribution and further analysis
Explore administrative options including XML configuration, MDX query logging, and cache management
By the end of this workshop, you'll have mastered the essential techniques for creating interactive, self-service analyses in Pentaho Analyzer. You'll understand how to structure data dimensionally, how to create calculations that extend your analytical capabilities, and how to format analyses for both visual impact and usability.
Prerequisites: Pentaho Business Analytics Server with Steel Wheels sample data and configured OLAP schema Estimated Time: 30 minutes
Leading Product Lines
The analysis shows the Sales, Quantity, and Unit Sales for each year by Territory and Line, and includes subtotal lines for each Territory, as well as a grand total line. The Quantity column includes data bars to quickly visualize and compare values.
The background colour for the Unit Sales column is formatted using a colour scale range. The report values include hyperlinks allowing you to drill down to the supporting data for each value.
To open the Leading Product Lines Report, in the Folders pane, expand Public > Steel Wheels, and then in the Files pane, double-click Leading Product Lines (pivot table).
To view the available fields, on the interactive toolbar, click the Add more fields onto the report button.
To view the Layout panel, on the interactive toolbar, click the Rearrange fields on the report button.
To keep only the 2003 data, on the canvas, right-click the 2003 column header, and then click Keep Only 2003.
To drill down to show the Quarters, on the canvas, double-click the 2003 column header.
To view the analysis as a chart, on the interactive toolbar click the Choose chart type button, and then click Column.
To view the report in table format, on the interactive toolbar, click the Switch to table format button.
To open the Country Performance heat grid, on the main toolbar, click the Open button.
Navigate to the Public folder, click the Up One Level button twice, and then double-click the Public folder.
Double-click the Steel Wheels folder, then click Country Performance (heat grid), and then click Open.
View the Country Performance heat grid.
To view the underlying details for a specific square, hover the cursor over the red square for Belgium, 2003, QTR2.
Click the Spain label.
Drill into the data for Spain at the top of the heat grid, click the Keep Only button.
To view the Available Fields and Layout panels, on the interactive toolbar, click the Add more fields onto the report and Rearrange fields on the report buttons.
To see the sales data for specific product lines in Spain, from the Available Fields pane, select Line and drag it to the X Axis area in the Layout panel.
Drill down to the months, double-click the green square for Classic Cars, 2004, QTR4. December was the best month of the quarter.
Drill down to the Vendor, double-click the green square for December.
On the report title bar, click the View As Table button.
On the report toolbar, click More actions and options > Export > To PDF, and then in the Export to PDF window, click Export.
Close the PDF browser window.
On the report toolbar click More actions and options > Reset.
Sales Analysis
The report will display Sales Revenue grouped by Territory and Product Line over time.
From the User Console Home Perspective, click Create New > Analysis Report.
In the Select Data Source window, click Steel Wheels: SteelWheelsSales, and then click OK.
From the Available Fields panel, select Country and drag it to the Rows drop zone on the Layout panel.
To remove Country, from the canvas:
· Click the Country header.
· Drag it to the lower right corner of the canvas.
· Drop it in the trashcan.
Add Sales to the report by dragging it to the Measures drop zone on the Layout panel.
From the Available Fields panel, double-click Territory.
Select Years and drag it to the Columns drop zone on the Layout panel.
Select Quarters and drag it to the Columns drop zone on the Layout panel. Drop Quarters below Years.
Select Line and drag it to the Rows drop zone on the Layout panel. Drop Line above Territory.
To rearrange the fields, on the Layout panel, click Territory and drag it above Line.
On the Interactive Toolbar, click the Filter button.
In the Available Fields panel, select Territory and drag it to the Filter panel.
To display only EMEA, in the Filter on Territory dialog box:
• Select: Select from a list.
• From the list of values, click EMEA.
• Click the right arrow to move EMEA to the Currently Included list.
• Click OK.
In the Available Fields panel, right-click Years, and then select Filter.
To display only 2003, in the Filter on Years dialog box:
• Select: Select from a list.
• From the list of values, click 2003.
• Click the right arrow to move 2003 to the Currently Included list.
• Click OK.
In the analysis canvas, right-click Quarters, and then select Filter.
To display only the second quarter, in the Filter on Quarters dialog box, select: Select from a list.
From the list of values, click QTR2, then click the right arrow to move QTR2 to the Currently Included list, and then click OK.
On the analysis canvas, click Sales and drag it up to the Filter panel.
To display the Top 5 Product Lines with Sales Greater Than 10,000 in the Filter on Sales dialog box:
• In the text box for Sales Greater Than, type: 10000.
• Click the Top 10, etc. check box..
• In the numeric value box, type 5.
• Click OK.
On the analysis canvas, right-click the QTR2 header for 2003, and then select Show All Quarters.
Remove the Sales is greater than 10000 filter.
Right-click one of the Sales headers, and then select Conditional Formatting > Color Scale: Green-Yellow-Red.
Remove the conditional formatting, right-click one of the Sales headers, and then deselect Conditional Formatting > Green-Yellow-Red.
Remove all Filters.
In the analysis details, right-click one of the Sales headers, then select Subtotals (Sums, Averages, etc.).
Select Average, and then click OK.
In the analysis details, right-click the Territory header, and then select Show Subtotals.
To show grand totals for columns and rows, from the Layout panel: Click Report Options.
In the Report Options window, select Show Grand Totals for Rows and Show Grand Totals for Columns.
Click OK.
User Defined Measure
Remove all Totals, Subtotals & Averages for rows & columns.
Apply 2 filters:
• Territory: EMEA
• Year 2003
Right-click one of the Sales headers, then select User Defined Measure > % of, Rank, Running Sum….
Select: % of Sales.
Click Next.
Click the drop-down arrow for Decimal Places, select 0.
Click: Done.
Calculated Measure
In the analysis details right-click one of the Sales headers, then select User Defined Measure > Create Calculated Measure.
In the Name text box, type Sales + 6% Tax.
To specify the numeric format:
Click the drop-down arrow for Format.
• Select Currency.
• Click the drop-down arrow for Decimal Places.
• Select 0.
To multiply Sales by 1.06, in the formula pane:
• Click to the right of sales.
• Type: * 1.06.
• Click OK.
Drill-Through
Remove the following columns:
• Sales + Tax 6%
• % of Sales
Enable drill through links for Sales, from the Layout panel:
• Click Report Options.
• Select Show drill-through links on Measure cells.
• Click OK.
Drill through to the supporting data, in the analysis details, click the value for EMEA, Classic Cars, 2003, QTR1 ($96,678).
Right-click the column header for Line, then select Edit.
In the Name text box, type Product Line, and then click OK.
To modify the Sales data as currency, in the analysis details, right-click one of the Sales headers, then select Column Name and Format.
Change the name: Revenue
From the Format drop-down list, select Currency ($), and then click OK.
Chart Options
To switch the analysis format, click the View As Table or View As Chart button in the analysis title bar.
Export Options
Analyzer provides several options for exporting your analysis. You can export the analysis as a PDF file which launches the analysis in a new window. From there you can save or print the PDF file. You can export the analysis to Excel, which opens the analysis in a new Excel window.
You can download the analysis data in CSV format. When you download data in the CSV format you get numbers with the full precision available. This way you avoid any rounding errors when you continue to work with your data in Excel. The export options are available from the Interactive Toolbar by clicking the More actions and options button.
Chart Options
As an administrator, you can add default chart options that are applied whenever a new chart is created. Adding default chart options does not apply the changes to existing charts.
You can modify the options on charts without affecting the default option settings.
You can also set an existing chart back to the default settings by clicking the Reset to default link on the Other tab of the Chart Options dialog box.
Click on the 'cog-wheel'.
Select: Chart Options.
Adminstration
If been assigned the Administrator role, then you'll have access to some options that will help troubleshoot and optimize your PAZ reports.
Click on the 'cog-wheel'.
Select: Chart Options.
XML
Administrators can configure the default value of non-standard properties of the current visualization, at the report-level. This is useful for properties that are hidden from the user interface using a global configuration.
To perform this configuration, set the corresponding JSON text in the field Visualization state JSON, in the Report Definition dialog (Administration » XML).
For example, the following JSON configuration would change the colors used by many of the standard visualizations, by changing the value of the palette property:
[ { "palette": {"colors": ["red", "green", "blue"]} } ]
Log
Log option is useful for troubleshooting and optimizing your Analyzer reports. The first half of the log displays the xml of the report definition.
The second half display the MDX query and the times taken to execute the query.
MDX
From here you're able to open the log file, Clear the Cache, Check the Time dimension and Execute MDX queries.
Last updated
Was this helpful?