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.

Wireless Carrier Dashboard

Prerequisites

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

baseline_demo database

1. 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

1. Start Schema Workbench.

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

1. Open: Workshop--Ctools/Carrier/schema/Wireless carrier.mondrian.xml

2. Expand: Retail Sales & Call Corridor cubes - our datasources.

Wireless Carrier Mondrian Schema

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

1. Open the MDX query panel: File -> New -> MDX Query

2. 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]

MDX query - All Call Regions

---

The last step is to from the Pentaho Server - PUC

• define a connection to the baseline_demo databse

• upload the Wireless carrier Mondrian Schema

1. In the PUC select: Manage Data Sources.

2. Click on the cog wheel & select: New Connection.

New JDBC connection

1. Select Postgres and enter the following details:

baseline_demo connection details

1. The Manage Data Sources also has the option to: Import Analysis

2. Browse to: Workshop--CTools/schemas/Wireless Carrier.Mondian.xml and associate with baseline_demo datasource.

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

Wireless Carrier schema

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

Layout & Style

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

Dashboard layout

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.

1. Highlight: /Public/CTools-Dashboard/Carrier-Dashboard-Layout/Layout.

2. 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)
     

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

---

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

Add a Resource - CSS

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

Add Resource

1. Enter the following options:

CSS External File

1. Click on the ^ button and browse for the file.

Add dashstyle.css

dashstyle.css

1. Finally check the CSS has been applied.

2. Save & Preview the dashboard.

Layout

The completed workshop:

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

CDA

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.

MDX Data Sources

sourceSelectorQuery

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

Start with the Layout dashboard ..

Layout

1. Browse to: /Public/CTools Dashboard/Carrier-Dashboard-CDA/Layout

2. Click Edit under File Actions.

Layout Dashboard

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

mdx over mondrianjndi

1. From the Data Source list, expand MDX Queries, and then click mdx over mondrianjndi.

2. 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.

1. In the Properties pane:

• Click in the Value for the Jndi property.

• On the keyboard, press the down arrow.

• Select the BaselineDemo connection.

1. 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.

1. 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.

sourceSelectorQuery

1. Save & Test the Layout.cda

Layout.cda

OOOOps the columns are the wrong way around ..

1. 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.

Change the column order

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

Refresh CDA Dashboard

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

tableQuery

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

1. Duplicate the sourceSelectorQuery and change the name to tableQuery.

Duplicate Query

1. Delete the Output columns.

Delete Output Columns

1. 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.

1. 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.

1. 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.

tableQuery

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.

1. 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
0	Source Call
1	Destination Call
2	Users
3	m/m-1
4	Calls
5	m/m-1
6	Duration
7	m/m-1

Rename Columns

1. Save and again check in CDA dashboard.

Column Names

The completed workshop:

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

lineChartQuery

The lineChartQuery

1. Duplicate the tableQuery and rename to: lineChartQuery.

lineChartQuery

1. 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})

1. To edit/delete the column names:

• Click in the Value column for the Columns property.

• To the left of Index 0, click the delete icon.

• Click Remove.

• Change Index 1 to Number of Calls.

• Change Index 2 to Number of Users.

• Delete the remaining column names.

• Click OK.

Change column names

1. Save and view in CDA Dashboard.

lineChartQuery

The completed workshop:

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