Sales Analysis Dashboard

So we have the building blocks in place .. time to start bringing it all together with a Workshop that builds on the concepts learnt in 'My First Dashboard'.

• In Layout tab:

  • Create the row and column structure
  • Name your HTML objects
• In Data Sources tab:

  • Create your SQL queries
  • Test them to ensure they work
• In Components tab:

  • Add all components
  • Configure their properties
  • Link to HTML objects
  • Set up listeners
• Preview and test:

  • Use the preview button
  • Test all filters
  • Verify chart interactions

x

Mock-up

We're going to assume that the discovery phase has been completed and after iteration the initial dashboard design has been completed and signed off.

Key Features:

• Interactive date range selection
• Multi-select product line and country filters
• Trend analysis chart
• Product distribution pie chart
• Detailed data table with pagination
• Auto-updating components when filters change

Sales Analysis Dashboard - mock-up

1. Log into the Pentaho Server.
2. Select: Browse Files.
3. Create the following folders

Sales Analysis Dashboard - Folders

1. In the Pentaho User Console (PUC) and select from the menu: File -> New -> CDE Dashboard.
2. Click on the Template option.
3. Select: Empty Sample.

Empty sample

1. Click on: Settings and enter the following details:

1. Enter the following details:

1. Click Ok.

x

x

Layout

The mock-up now needs to be 'mapped' to CDE Layout.

We'll be using the 'Bootstrap' template.

Root Container
│
├── Filter Row (id: filterRow)
│   ├── Date Filters Column (span: 4)
│   ├── Product Filters Column (span: 4)
│   └── Additional Filters Column (span: 4)
│
├── Charts Row (id: chartsRow)
│   ├── Trend Chart Column (span: 8)
│   └── Pie Chart Column (span: 4)
│
└── Table Row (id: tableRow)
    └── Data Table Column (span: 12)

x

First Tab

Following the CDA structure for a datasource type = sql.jdbc

1. Define the CDA connection to: sampledata database.

<User>pentaho_admin</User>
<Pass>password</Pass>

1. Upload the sampledata.cda file to: /Public/CTools-Dashboards/5min-Dashboard directory on the Pentaho Server.

<?xml version="1.0" encoding="UTF-8"?>
<CDADescriptor>
    <DataSources>
        <Connection id="mariadb_connection" type="sql.jdbc">
            <Driver>org.mariadb.jdbc.Driver</Driver>
            <Url>jdbc:mariadb://localhost:3306/sampledata</Url>
            <User>your_username</User>
            <Pass>your_password</Pass>
        </Connection>
    </DataSources>

    <DataAccess id="allYearsQuery" connection="mariadb_connection" type="sql" access="public" cache="true" cacheDuration="3600">
        <Name>Sales Data All Years</Name>
        <Query>
            SELECT 
                YEAR(o.ORDERDATE) as YEAR,
                MONTH(o.ORDERDATE) as MONTH,
                o.ORDERNUMBER,
                o.ORDERDATE,
                o.STATUS,
                o.CUSTOMERNUMBER,
                c.CUSTOMERNAME,
                c.COUNTRY,
                p.PRODUCTCODE,
                p.PRODUCTNAME,
                p.PRODUCTLINE,
                od.QUANTITYORDERED,
                od.PRICEEACH,
                (od.QUANTITYORDERED * od.PRICEEACH) as TOTAL_AMOUNT
            FROM 
                ORDERS o
                JOIN ORDERDETAILS od ON o.ORDERNUMBER = od.ORDERNUMBER
                JOIN PRODUCTS p ON od.PRODUCTCODE = p.PRODUCTCODE
                JOIN CUSTOMERS c ON o.CUSTOMERNUMBER = c.CUSTOMERNUMBER
            ORDER BY 
                o.ORDERDATE, o.ORDERNUMBER
        </Query>
        <Parameters></Parameters>
        <Columns></Columns>
    </DataAccess>
</CDADescriptor>

Further details on the SQL Query.

SELECT 
    -- Time-based columns for analysis
    YEAR(o.ORDERDATE) as YEAR,          -- Extracts just the year from date (e.g., 2004)
    MONTH(o.ORDERDATE) as MONTH,        -- Extracts month number (1-12)
    
    -- Order information
    o.ORDERNUMBER,                      -- Unique order identifier
    o.ORDERDATE,                        -- Full date of the order
    o.STATUS,                           -- Order status (e.g., Shipped, Delivered)
    
    -- Customer information
    o.CUSTOMERNUMBER,                   -- Unique customer identifier
    c.CUSTOMERNAME,                     -- Name of the customer
    c.COUNTRY,                          -- Customer's country
    
    -- Product information
    p.PRODUCTCODE,                      -- Unique product identifier
    p.PRODUCTNAME,                      -- Name of the product
    p.PRODUCTLINE,                      -- Product category (e.g., Classic Cars)
    
    -- Order details and calculations
    od.QUANTITYORDERED,                 -- Number of items ordered
    od.PRICEEACH,                       -- Price per unit
    (od.QUANTITYORDERED * od.PRICEEACH) as TOTAL_AMOUNT  -- Calculated total for line item

FROM and JOIN Clauses:

FROM 
    ORDERS o                                    -- Main orders table with alias 'o'
    JOIN ORDERDETAILS od                        -- Table containing line items for each order
        ON o.ORDERNUMBER = od.ORDERNUMBER       -- Links orders to their details
    JOIN PRODUCTS p                             -- Product information table
        ON od.PRODUCTCODE = p.PRODUCTCODE       -- Links order details to specific products
    JOIN CUSTOMERS c                            -- Customer information table
        ON o.CUSTOMERNUMBER = c.CUSTOMERNUMBER  -- Links orders to customer info

Table Relationships:

• ORDERS (o): Main table containing order headers

  • Primary Key: ORDERNUMBER
  • Contains: ORDERDATE, STATUS, CUSTOMERNUMBER
• ORDERDETAILS (od): Contains individual line items

  • Composite Key: ORDERNUMBER, PRODUCTCODE
  • Contains: QUANTITYORDERED, PRICEEACH
• PRODUCTS (p): Product catalog

  • Primary Key: PRODUCTCODE
  • Contains: PRODUCTNAME, PRODUCTLINE
• CUSTOMERS (c): Customer information

  • Primary Key: CUSTOMERNUMBER
  • Contains: CUSTOMERNAME, COUNTRY

Ordering:

• Sorts results first by date
• Then by order number for orders on the same date

ORDER BY 
    o.ORDERDATE, o.ORDERNUMBER

---

And finally .. test the sampledata.cda connection.

x

x