Carrier Dashboard

Wireless Carrier dashboard ..

A national Wireless Carrier requires a dashboard for tracking telecommunications traffic, with filters for Source Region, Destination Region, and Month (currently set to January), allowing for detailed analysis of calling patterns and platform usage.

Before we begin our dashboard journey, we need to understand our OLAP Mondrian datasource.

Log into the Postgres baseline_demo database.

The baseline_demo database has a number of key features:

• Centralized Fact Tables

• Dimension Tables (sometimes called lookup tables)

• Simple Join Paths

• Denormalized Structure

• Query Optimization Features

These tables have been 'mapped' using Pentaho Schema Workbench.

You will need to copy over the Postgres database driver to: /schema-workbench/lib

Start Schema Workbench.

cd
cd /Pentaho/design-tools/schema-workbench
./workbench.sh

Open: Workshop--Ctools/Carrier/schema/Wireless carrier.mondrian.xml
Expand: Retail Sales & Call Corridor cubes - our datasources.

You can test your MDX queries that will be used to populate the dashboard.

Open the MDX query panel: File -> New -> MDX Query
Copy & paste the following MDX query. Execute.

WITH 
 MEMBER [Measures].[Source Member Name] AS [Call Source].currentmember.uniquename
SELECT 
 [Measures].[Source Member Name]  ON 0,
 {[Call Source].[All], [Call Source].[Source Region].Members} ON 1
FROM 
 [Call Corridor]

The last step is to from the Pentaho Server - PUC

• define a connection to the baseline_demo databse

• upload the Wireless carrier Mondrian Schema

In the PUC select: Manage Data Sources.
Click on the cog wheel & select: New Connection.

Select Postgres and enter the following details:

The Manage Data Sources also has the option to: Import Analysis
Browse to: Workshop--CTools/schemas/Wireless Carrier.Mondian.xml and associate with baseline_demo datasource.

Click: Import & check the xml schema has been successfully imported.

The schema can now be referenced to create the dashboard mdx.mondrian.jndi queries.

We're going to make a few assumptions here ..

• connections tested

• the layout has been confirmed

• chart content & types agreed

• dashboard functions included - export graphs & tables

There are five “main” rows:

The first row contains four columns: one with the logo, and three with the selectors.

The second row contains two columns with the main KPIs: Number of Calls and Average Call Duration. Each column contains two rows: one for the header, and another for the data.

The third row has two columns, each with two rows.

The fourth and fifth rows have one column which contains two rows, but notice that the first row is split into two columns because there is a title and an Export button.

Highlight: /Public/CTools-Dashboard/Carrier-Dashboard-Layout/Layout.
Select: Edit from the Folder Actions.

Keep the Layout dashboard open in its own tab.

You will need to refer to each Layout entity to apply the Property Values.

Root Container
│
└── Layout Row (id: layoutRow)
    └── Layout Column (id: layoutColumn span:12)
        └── Header Row (id: headerRow)
            ├── CTools Logo Column (id: ctoolsLogo span:3)
            ├── sourceObj Column (id: sourceOBj span:3)
            ├── destinationObj Column (id: destinationObj span:3)
            └── monthObj Column (id: monthObj span:3)
    Space
        └── KPI Row (id: kpiRow)
            └── Total Calls Column (id: totalCallsCol span:6)
                └── Total Calls Title Row (id: totalCallsTitle)
                    └── Total Calls Title Column (id: totalCallsTitle span:12)
                        └── HTML Number of Calls
                    Total Calls Row (id: totalCallsRow)
                    └── Total Calls Obj Column (id: totalCallsObj span:12)
                    
             └── Average Calls Time Column (id: totalCallsCol span:6)
                 └── Average Calls Total Title Row (id: averageCallsTimeTitleRow)
                     └── Average Calls Time Title Column (id: averageCallsTimeTitle span:12)
                         └── HTML Call Duration
                     Average Calls Time Row (id: averageCallsTimeRow)
                     └── Total Calls Time Obj Column (id: averageCallsTimeObj span:12)
    Space
        └── Calls By Row (id: callsByRow)
            └── Horizontal Bar Chart Column (id: horizontalBarChartCol span:6)
                └── Horizontal Bar Chart Title Row (id: horizontalBarChartTitleRow)
                    └── Horizontal Bar Chart Title Column (id: horizontalBarChartTitle span:12)
                        └── HTML Calls by Platform
                    Horizontal Bar Chart Row (id: horizontalBarChartRow)
                    └── Horizontal Bar Chart Obj Column (id: horizontalBarChartObj span:12)

            └── Pie Chart Title Column (id: pieChartTitleCol span:6)
                └── Pie Chart Total Title Row (id: pieChartTitleRow)
                    └── Pie Chart Title Column (id: averageCallsTimeTitle span:12)
                        └── HTML Number of Calls
                    Pie Chart Row (id: pieChartRow)
                    └── Pie Chart Table Obj Column (id: pieChartTableObj span:8)
                    └── Pie Chart Obj Column (id: pieChartObj span:4)
    Space
        └── Calls Summary Row (id: callsSummaryRow)
            └── Calls Summary Column (id: callsSummaryCol span:12)
                └──Table Title Row (id: tableTitleRow)
                   └── Table Title Column (id: tableTitle span:10)
                       └── HTML Calls Summary
                   └── Export Table Button Obj (id: exportTableButtonObj span:2)
                   Main Table Row (id: mainTableRow)
                   └── Main Table Obj Column (id: mainTableObj span:12)

Create a Layout folder in the PUC .. give it a go ..!

Most modern day layout are controlled by Cascading Style Sheets (CSS)

Click on the Add Resource option in the Layout Structure toolbar.

Enter the following options:

Click on the ^ button and browse for the file.

Finally check the CSS has been applied.
Save & Preview the dashboard.

The completed workshop:

/Public/CTools-Dashboard/Carrier-Dashboard-Layout/Layout

Now that the Layout is complete, time to turn our attention to CDA data sources.

All the data in this dashboard comes from ten MDX queries.

The three selectors:

• Source Region

• Destination Region

• Month

are all populated via MDX queries, as are each of the tables and charts in the dashboard.

We're going to add two MDX queries to the dashboard.

• First query returns the values for the Source Selector.

• Second query returns the results for the main dashboard table that includes several defined parameters.

Let's start with the MDX query that retrieves the list of Regions.

Start with the Layout dashboard ..

Layout

Browse to: /Public/CTools Dashboard/Carrier-Dashboard-CDA/Layout
Click Edit under File Actions.

On the CDE Perspectives Toolbar, click the Data Sources Panel icon.

From the Data Source list, expand MDX Queries, and then click mdx over mondrianjndi.
To name this data source, in the Properties pane:

• Click in the Value for the Name property.

• Type sourceSelectorQuery.

• Press Tab or Enter.

Its BP to add the suffix “Query” to the data source name.

In the Properties pane:

• Click in the Value for the Jndi property.

• On the keyboard, press the down arrow.

• Select the BaselineDemo connection.

In the Properties pane:

• Click in the Value for the Mondrian schema property.

• On the keyboard, press the down arrow.

• Select the Wireless Carrier schema.

To enter the MDX query:

• In the Properties pane, click the ellipsis icon to the right of the Query property.

• In the MDX Editor window, enter the following MDX query, and then click OK:

WITH 
 MEMBER [Measures].[Source Member Name] AS [Call Source].currentmember.uniquename
SELECT 
 [Measures].[Source Member Name]  ON 0,
 {[Call Source].[All], [Call Source].[Source Region].Members} ON 1
FROM 
 [Call Corridor]

The query creates a calculated member (Source Member Name) and obtains the list of Source Regions from the Call Source dimension.

Save & Test the Layout.cda

OOOOps the columns are the wrong way around ..

To change the order of the columns:

• In the Properties pane, click in the Value for the Output Columns property.

• In the new window, click the Add button once to add another field.

• In the first Index field, type 1.

• In the second Index field, type 0.

• Click OK.

Save the dashboard and try again .. Refresh the layout.cda

Notice the Query has also returned an 'Unknown' Geography value .. This indicates that the Geography table in the baseline_demo database contains errors.

The completed workshop:

/Public/CTools-Dashboard/Carrier-Dashboard-Layout/Layout CDA

Next MDX query populates the main dashboard table. This query is a bit trickier as it includes parameters ..

Duplicate the sourceSelectorQuery and change the name to tableQuery.

Delete the Output columns.

Add the MDX Query.

WITH
 MEMBER [Measures].[usersCurrentMonth] AS '([Measures].[Users], ${monthParameter})'
 MEMBER [Measures].[usersLastMonth] AS '([Measures].[Users], ${monthParameter}.Lag(1))'
 MEMBER [Measures].[usersDifference] AS 'IIf(IsEmpty([Measures].[usersCurrentMonth]), NULL, (([Measures].[usersCurrentMonth] / [Measures].[usersLastMonth])-1)*100)'
 MEMBER [Measures].[callsCurrentMonth] AS '([Measures].[Calls], ${monthParameter})'
 MEMBER [Measures].[callsLastMonth] AS '([Measures].[Calls], ${monthParameter}.LAG(1))'
 MEMBER [Measures].[callsDifference] AS 'IIf(IsEmpty([Measures].[callsCurrentMonth]), NULL, (([Measures].[callsCurrentMonth] / [Measures].[callsLastMonth])-1)*100)'
 MEMBER [Measures].[timeCurrentMonth] AS '([Measures].[Duration in Minutes], ${monthParameter})'
 MEMBER [Measures].[timeLastMonth] AS '([Measures].[Duration in Minutes],${monthParameter}.LAG(1))'
 MEMBER [Measures].[timeDifference] AS 'IIf(IsEmpty([Measures].[timeCurrentMonth]), NULL, (([Measures].[timeCurrentMonth] / [Measures].[timeLastMonth])-1)*100)'

SELECT {[Measures].[usersCurrentMonth], [Measures].[usersDifference], [Measures].[callsCurrentMonth], [Measures].[callsDifference], [Measures].[timeCurrentMonth],[Measures].[timeDifference]} ON COLUMNS,
 CROSSJOIN(${sourceCallParameter}${sourceChildren}, ${destinationCallParameter}${destinationChildren}) ON ROWS

FROM 
 [Call Corridor]

It's out of scope to explain how MDX queries work , however, let's break this Query down into the various parts to hep explain whats happening behind the scenes:

Calculated Members Definition: The query starts by defining several calculated members in three groups (users, calls, and time), each following the same pattern:

Users:

- [usersCurrentMonth]: Gets the Users measure for the current month (specified by monthParameter)
- [usersLastMonth]: Gets the Users measure for the previous month using LAG(1)
- [usersDifference]: Calculates the percentage change between current and last month ((current/last - 1) * 100)

Calls:

- [callsCurrentMonth]: Gets the Calls measure for current month
- [callsLastMonth]: Gets the Calls measure for previous month
- [callsDifference]: Calculates percentage change in calls

Duration:

- [timeCurrentMonth]: Gets Duration in Minutes for current month
- [timeLastMonth]: Gets Duration for previous month
- [timeDifference]: Calculates percentage change in duration

The IIf Logic: Each "difference" measure uses IIf(IsEmpty()) to handle null cases.

This prevents division by zero errors and returns NULL if current month data is empty.

IIf(IsEmpty([Measures].[currentMonth]), NULL, (([currentMonth] / [lastMonth])-1)*100)

SELECT Statement:

On COLUMNS: Shows the current values and their percentage differences for users, calls, and duration

On ROWS: Uses CROSSJOIN to combine two dimensions:

${sourceCallParameter}${sourceChildren} (likely source locations/departments)

${destinationCallParameter}${destinationChildren} (likely destination locations/departments)

Template Parameters:

The query uses several parameters (denoted by ${...}):

${monthParameter}: Specifies the current month

${sourceCallParameter}: Source dimension

${sourceChildren}: Additional source hierarchy members

${destinationCallParameter}: Destination dimension

${destinationChildren}: Additional destination hierarchy members

From Clause: The data comes from a cube named [Call Corridor], which tracks call metrics between different locations.

The query is designed to display month-over-month comparisons of:

• Number of users

• Number of calls

• Total call duration

Each with their corresponding percentage changes, broken down by source and destination locations.

To specify default values for the parameters:

• In the Properties pane, click in the Value column for the Parameters property.

• In the new window, click the Add button four times.

• In the Name and Value columns, type the following:

Parameter

MDX

destinationCallParameter

[Call Destination.Destination Geography].[All]

sourceCallParameter

[Call Source.Source Geography].[All]

monthParameter

[Time.Standard Time].[2011].[Q1 2011].[January]

sourceChildren

.Children

destinationChildren

.Children

.Children returns all the direct child members of a given member in a hierarchy. When used with crossjoined tables, it returns the immediate descendants of the specified level or member.

To see the new results in CDA:

• From the Opened perspective, click the layout.cda tab.

• Right-click the layout.cda tab.

• From the menu, select Reload Tab.

• In the Confirm Reload dialog, click Yes.

The column names and data formats are not ideal. We will improve the appearance of the data when we create the table later.

For now let's modify the column names for the tableQuery.

To specify column names for the query output:

• In the Properties pane, click in the Value column for the Columns property.

• In the new window, click the Add button seven times.

• In the Index and Name columns type the following, and then press OK.

Index

Name

Source Call

Destination Call

Users

m/m-1

Calls

m/m-1

Duration

m/m-1

Save and again check in CDA dashboard.

The completed workshop:

/Public/CTools-Dashboard/Carrier-Dashboard-Layout/Layout CDA

The lineChartQuery

Duplicate the tableQuery and rename to: lineChartQuery.

Replace the existing MDX query:

WITH 
 SET DAYS AS DESCENDANTS(${monthParameter}, 1) 
SELECT
 DAYS ON 1,
 {[Measures].[Calls], [Measures].[Users]} ON 0
FROM 
 [Call Corridor]
WHERE 
 (${sourceCallParameter}, ${destinationCallParameter})