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

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

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

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

Click on: Settings and enter the following details:

Enter the following details:

Click Ok.

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)

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

Define the CDA connection to: sampledata database.

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

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.

Was this helpful?