Post on 15-Apr-2017
TIBCO Spotfire
Copyright © 2015 Varun Varghese 2
DB
DatabaseMS SQL ServerOracleMySQL
Spotfire 7.0
Spotfire Desktop
Spotfire Architecture
SpotfireServer
Spotfire Server(Has a web server)10.56.64.7
Cloud
Web player
Copyright © 2015 Varun Varghese 3
DW
DatabaseMS SQL ServerOracleMySQL
Spotfire 7.0
Spotfire Desktop
Spotfire Architecture
SpotfireServer
Spotfire Server(Has a web server)10.56.64.7
Copyright © 2015 Varun Varghese 4
DW
Teradata
Oracle
ETLTalend, Informatica, Data Stage, SSIS, Pentaho
Spotfire
SpotfireServer
Copyright © 2015 Varun Varghese 5
Spotfire 7.0
Spotfire Server
Spotfire Web
player
Copyright © 2015 Varun Varghese 6
Spotfire Server
Spotfire Server
Spotfire Server
Copyright © 2015 Varun Varghese 7
Copyright © 2015 Varun Varghese 8
Database joins
• Inner Join• Outer Join
• Left outer• Right outer• Full outer
Inner joinOver lapping between 2 tables
A B
10 columns100 rows
5 columns40 rows
15 columns
40 rows
Copyright © 2015 Varun Varghese 9
A
B C A B
OrdersOrderIdCustomerIdOrderDatePrice
ReturnsOrderIdReturnDateReasonForReturning
Inner Join
D D
EmployeeEmployeeIDNameShift
Copyright © 2015 Varun Varghese 10
Download TIBCO Spotfire Desktop 7.0 Trial version
http://spotfire.tibco.com/trydesktop
Copyright © 2015 Varun Varghese 11
Download Sample Data
https://drive.google.com/folderview?id=0B0HVcjpwTGxYb2tYOHh5MlQ4bzg&usp=sharing
Copyright © 2015 Varun Varghese 12
Adding Data Table to Spotfire (Excel)
Copyright © 2015 Varun Varghese 13
Choose File as the source
Copyright © 2015 Varun Varghese 14
Select the excel file from the files downloaded
Copyright © 2015 Varun Varghese 15
Data preview
Copyright © 2015 Varun Varghese 16
Rename the data table (optional)
Copyright © 2015 Varun Varghese 17
Table Visualization (non-aggregated data)
Copyright © 2015 Varun Varghese 18
Spotfire is an in-memory analytical tool
Datasource All data gets loaded onto Spotfire
Copyright © 2015 Varun Varghese 19
Linked to Source:Data is not saved in the Dxp file
Spotfire: Linked to Source Edit > Data Table Properties
Copyright © 2015 Varun Varghese 20
Embedding data in analysisLinked to Source
.dxp
data
data
This person won’t be able to open the file
Will be able to open the file.
(Smaller file size)
Bigger file size)
.dxp
Copyright © 2015 Varun Varghese 21
Click that icon to refresh the dashboard.Works only if you are using “Linked to Source” setting for the data table.
Refreshing the data tables used in the dashboard
Copyright © 2015 Varun Varghese 22
Spotfire Server
DB
Information Link Because the information link has the DB connection details, the dashboard will be able to access the DB.
Users
Spotfire: Linked to Source
Copyright © 2015 Varun Varghese 23
Spotfire Server
Users
Spotfire: Embedded in Analysis
Copyright © 2015 Varun Varghese 24
Linked to Source Embedding data in analysis
Copyright © 2015 Varun Varghese 25
Embedded in analysis:Data is saved in the Dxp file
Spotfire: Embedded in Analysis
Copyright © 2015 Varun Varghese 26
Close the “Recommended visualization” window
Copyright © 2015 Varun Varghese 27
Copyright © 2015 Varun Varghese 28
Copyright © 2015 Varun Varghese 29
Filter Panel
Copyright © 2015 Varun Varghese 30
Organizing filters
Copyright © 2015 Varun Varghese 31
Organizing filters
Copyright © 2015 Varun Varghese 32
Inserting Visualizations
Copyright © 2015 Varun Varghese 33
Use case for inserting a calculated column
This is what the filter should have looked likeWhat the filter currently looks like
TRIM([Order Priority])
Copyright © 2015 Varun Varghese 34
Trim([Order Priority])
Use case for inserting a calculated column
Filter based on Calculated columnFilter based on Original column
Expression
Copyright © 2015 Varun Varghese 35
Inserting Calculated Columns
Copyright © 2015 Varun Varghese 36
Table Visualization
Copyright © 2015 Varun Varghese 37
Adding/Removing columns from Table Visualization
Copyright © 2015 Varun Varghese 38
Hiding / customizing the legend
Copyright © 2015 Varun Varghese 39
Column formatting at global level
Copyright © 2015 Varun Varghese 40
Column formatting at visualization level
Copyright © 2015 Varun Varghese 41
Duplicating Visualization
Copyright © 2015 Varun Varghese 42
Mea
sure
axi
s
Dimension axis Measure axis
Dim
ensio
n ax
is
Vertical Bar Chart Horizontal Bar Chart
Copyright © 2015 Varun Varghese 43
Sorting the Bar Chart
Only in new versions of Spotfire Old versions of Spotfire
Copyright © 2015 Varun Varghese 44
Stacked Bar Chart
1. Add 2 measures on the Y axis2. Right click on the visualization and choose stacked bars
Copyright © 2015 Varun Varghese 45
Side-by-side Bar Chart
Copyright © 2015 Varun Varghese 46
100% Stacked Bar Chart
Copyright © 2015 Varun Varghese 47
Copyright © 2015 Varun Varghese 48
Showing Top 20 Customers in a bar chart
Copyright © 2015 Varun Varghese 49
Showing Top 20 Customers in a bar chart
Copyright © 2015 Varun Varghese 50
Showing Top 20 Customers in a bar chart (input driven by text box)
Copyright © 2015 Varun Varghese 51
Configuring the input field
Copyright © 2015 Varun Varghese 52
Adding lines and curves
Copyright © 2015 Varun Varghese 53
Copyright © 2015 Varun Varghese 54
Adding lines & curves
Copyright © 2015 Varun Varghese 55
Showing label for lines and curves
Copyright © 2015 Varun Varghese 56
Color-by axis: Categorical
Copyright © 2015 Varun Varghese 57
Copyright © 2015 Varun Varghese 58
Copyright © 2015 Varun Varghese 59
Copyright © 2015 Varun Varghese 60
Target Line adjustment changing the bar color
Copyright © 2015 Varun Varghese 61
Copyright © 2015 Varun Varghese 62
Copyright © 2015 Varun Varghese 63
Zoom slider
Copyright © 2015 Varun Varghese 64
Showing gridlines
Copyright © 2015 Varun Varghese 65
Sorting in Ascending order: Reversing the Category axis
Copyright © 2015 Varun Varghese 66
Reversing the Value axis
Copyright © 2015 Varun Varghese 67
Show Filtering Scheme on Page
Copyright © 2015 Varun Varghese 68
Choose a Filtering Scheme for the page
Copyright © 2015 Varun Varghese 69
Delinking filter panel from a visualization (no filtering would apply)
Copyright © 2015 Varun Varghese 70
Limiting data using expression
Copyright © 2015 Varun Varghese 71
Limiting data using expression: Editing the expression
Copyright © 2015 Varun Varghese 72
Limiting data using marking: Creating master-detail visualizations
Copyright © 2015 Varun Varghese 73
Copyright © 2015 Varun Varghese 74
Copyright © 2015 Varun Varghese 75
Copyright © 2015 Varun Varghese 76
Cross Table
Same as Pivot table in Excel
Copyright © 2015 Varun Varghese 77
Show Grand Total for Columns & Rows
Copyright © 2015 Varun Varghese 78
Color coding negative values in Cross Table
Copyright © 2015 Varun Varghese 79
Adding rule for color coding negative values in Cross Table
Copyright © 2015 Varun Varghese 80
Graphical Table
Copyright © 2015 Varun Varghese 81
Graphical Table: Axes
Copyright © 2015 Varun Varghese 82
Graphical Table: Calculated Value
Copyright © 2015 Varun Varghese 83
Graphical Table: Calculated Value
Copyright © 2015 Varun Varghese 84
Graphical Table: Sparkline
Copyright © 2015 Varun Varghese 85
Graphical Table: Icon
Copyright © 2015 Varun Varghese 86
Graphical Table: Bullet Chart
Copyright © 2015 Varun Varghese 87
Text Area: Dynamic Items
Copyright © 2015 Varun Varghese 88
Line Chart (Shows trend in a measure over a period of time)
Copyright © 2015 Varun Varghese 89
Line Chart (With multiple measures)
Copyright © 2015 Varun Varghese 90
Line Chart (Tweaking the appearance)
Copyright © 2015 Varun Varghese 91
Line Chart (Changing the date hierarchy)
Copyright © 2015 Varun Varghese 92
Combination Chart
Copyright © 2015 Varun Varghese 93
Combination Chart (Changing the appearance)
Copyright © 2015 Varun Varghese 94
Pie Chart (Do not use this chart)
Copyright © 2015 Varun Varghese 95
Pie Chart (Sorting the slices)
Copyright © 2015 Varun Varghese 96
Scatter Plot
Copyright © 2015 Varun Varghese 97
Scatter Plot (tweaking the color axis)
Copyright © 2015 Varun Varghese 98
Scatter Plot (Adding lines and curves – Straight line fit aka Linear Regression)
Copyright © 2015 Varun Varghese 99
Scatter Plot (Using the Shape & Size axes)
Copyright © 2015 Varun Varghese 100
Scatter Plot (Tweaking the size of the markers)
Copyright © 2015 Varun Varghese 101
Details-on-demand
Details-on-demand shows the marked data for every visualization in the dashboard
Copyright © 2015 Varun Varghese 102
Inserting Property Controls: 1) Input Field
Show top N customers in a bar chartControl the N parameter using an input field property control
Copyright © 2015 Varun Varghese 103
Inserting Text Area
Copyright © 2015 Varun Varghese 104
Copyright © 2015 Varun Varghese 105
Copyright © 2015 Varun Varghese 106
Copyright © 2015 Varun Varghese 107
Copyright © 2015 Varun Varghese 108
Copyright © 2015 Varun Varghese 109
Copyright © 2015 Varun Varghese 110
Copyright © 2015 Varun Varghese 111
Copyright © 2015 Varun Varghese 112
Inserting Slider Property Control
Copyright © 2015 Varun Varghese 113
Copyright © 2015 Varun Varghese 114
Copyright © 2015 Varun Varghese 115
Controlling the N parameter using slider property control
Copyright © 2015 Varun Varghese 116
Copyright © 2015 Varun Varghese 117
Copyright © 2015 Varun Varghese 118
Copyright © 2015 Varun Varghese 119
Copyright © 2015 Varun Varghese 120
Copyright © 2015 Varun Varghese 121
Copyright © 2015 Varun Varghese 122
Copyright © 2015 Varun Varghese 123
Copyright © 2015 Varun Varghese 124
Copyright © 2015 Varun Varghese 125
Copyright © 2015 Varun Varghese 126
Copyright © 2015 Varun Varghese 127
Copyright © 2015 Varun Varghese 128
Copyright © 2015 Varun Varghese 129
Copyright © 2015 Varun Varghese 130
Copyright © 2015 Varun Varghese 131
Difference in Sales from Quarter to Quarter
2010 Q2 – 2010 Q12010 Q3 – 2010 Q22011 Q1 – 2010 Q42011 Q2 – 2011 Q12011 Q3 – 2011 Q2
Sum([Sales]) - Sum([Sales]) OVER (Previous([Axis.X]))
Over – Node navigation function
Sales for each quarter – sales for the previous quarter
Copyright © 2015 Varun Varghese 132
Order Date (Quarter) Sum(Sales) Delta
2010 » Q1 415880.26
2010 » Q2 352786.14 -63094.1
2010 » Q3 456684.8 103898.7
2010 » Q4 698981.68 242296.9
2011 » Q1 272047.55 -426934
2011 » Q2 337345.29 65297.74
2011 » Q3 546381.05 209035.8
2011 » Q4 788733.54 242352.5
2012 » Q1 294067.11 -494666
2012 » Q2 428279.06 134212
2012 » Q3 508179.27 79900.21
2012 » Q4 1000205.74 492026.5
2013 » Q1 536149.16 -464057
2013 » Q2 518591.5 -17557.7
2013 » Q3 722667.23 204075.7
2013 » Q4 1074951.94 352284.7
Copyright © 2015 Varun Varghese 133
Difference in Sales from Quarter to Quarter
Copyright © 2015 Varun Varghese 134
Difference in Sales from previous month
Copyright © 2015 Varun Varghese 135
Sum of sales for each month divided by sales for that entire year
Hiearchy
Year
Month
Sum([Sales]) / Sum([Sales]) OVER (Parent([Axis.X]))
Sales for each month as a ratio of the sales for the corresponding year
Copyright © 2015 Varun Varghese 136
Formatting the value axis to show percentage
Copyright © 2015 Varun Varghese 137
Sales for each month as a % of the sales for the corresponding year
Copyright © 2015 Varun Varghese 138
Sales for each month as a % of entire timeframe on the X axis (each month’s sales divided by All)
Sum([Sales]) / Sum([Sales]) OVER (All([Axis.X]))
Copyright © 2015 Varun Varghese 139
Sales for each Sub-Category as a ratio of sales of all Sub-Categories
Copyright © 2015 Varun Varghese 140
Formatting it as %
Copyright © 2015 Varun Varghese 141
Sales for each Sub-Category as a % of sales of all Sub-Categories
Copyright © 2015 Varun Varghese 142
Money made so far: Running total of profit
Sum([Profit]) OVER (AllPrevious([Axis.X]))
Copyright © 2015 Varun Varghese 143
Moving Average: For a 3 month window
Copyright © 2015 Varun Varghese 144
Pick Moving Average from the aggregation drop down on the measure axis
Copyright © 2015 Varun Varghese 145
By default it shows 10 month moving average. Modify the expression to make the window 3 months
Copyright © 2015 Varun Varghese 146
Copyright © 2015 Varun Varghese 147
Copyright © 2015 Varun Varghese 148
Sum([Sales])THEN Avg([Value]) OVER (LastPeriods(3,[Axis.X]))THEN If(Count() OVER (LastPeriods(3,[Axis.X]))=3,[Value],null)
Here’s the final expression for moving average
Copyright © 2015 Varun Varghese 149
Copyright © 2015 Varun Varghese 150
Making the moving average window dynamic by controlling the window size using a property control
Copyright © 2015 Varun Varghese 151
Text Area: Property Controls
Copyright © 2015 Varun Varghese 152
Text Area: Property Controls - Label
Copyright © 2015 Varun Varghese 153
Copyright © 2015 Varun Varghese 154
Text Area: Property Controls – Input Field
Copyright © 2015 Varun Varghese 155
Copyright © 2015 Varun Varghese 156
Copyright © 2015 Varun Varghese 157
Copyright © 2015 Varun Varghese 158
Copyright © 2015 Varun Varghese 159
Copyright © 2015 Varun Varghese 160
Copyright © 2015 Varun Varghese 161
Copyright © 2015 Varun Varghese 162
Copyright © 2015 Varun Varghese 163
Text Area: Property Controls – Multi-line Input Field
Copyright © 2015 Varun Varghese 164
Copyright © 2015 Varun Varghese 165
Copyright © 2015 Varun Varghese 166
Text Area: Property Controls – Drop down list
Copyright © 2015 Varun Varghese 167
Text Area: Property Controls – Single Select List box
Copyright © 2015 Varun Varghese 168
Inserting List box control
Copyright © 2015 Varun Varghese 169
Text Area: Property Controls – Single Select List box
Copyright © 2015 Varun Varghese 170
[Region]=DocumentProperty("RegionListBox")
Setting the data limiting expression for the visualization
Copyright © 2015 Varun Varghese 171
Copyright © 2015 Varun Varghese 172
Copyright © 2015 Varun Varghese 173
Text Area: Property Controls – Multi-Select List box
Copyright © 2015 Varun Varghese 174
Copyright © 2015 Varun Varghese 175
[Region] in ($map("'${Regions}'", ","))
Resulting expression
Copyright © 2015 Varun Varghese 176
$map("sum([${Regions}])", ",")sum([Central]),sum([East]),sum([South]),sum([West])
$map("([${Regions}])", ",")([Central]),([East]),([South]),([West])
$map("(${Regions})", ",")(Central),(East),(South),(West)
$map("('${Regions}')", ",")('Central'),('East'),('South'),('West')
$map("'${Regions}'", ",")'Central','East','South','West‘
($map("'${Regions}'", ","))('Central','East','South','West')
[Region] in ($map("'${Regions}'", ","))[Region] in ('Central','East','South','West')
Custom expression evolution
Copyright © 2015 Varun Varghese 177
Text Area: Property Controls – Dropdown list (column selection) – Axis Selector
Copyright © 2015 Varun Varghese 178
Set from property
Copyright © 2015 Varun Varghese 179
Creating the Boolean property to store the selection status of the column list property control
Copyright © 2015 Varun Varghese 180
Cherry picking the columns that need to be shown in the dropdown list
Copyright © 2015 Varun Varghese 181
Copyright © 2015 Varun Varghese 182
Axis selector
Copyright © 2015 Varun Varghese 183
Bookmarks
Spotfire Server
The user found something interesting and captured a bookmark and included this bookmark as a link/button in the text area in the dashboard and saved it back to the server.
This is possible because webplayer 6.0 onwards allows editing the dashboard within the webpage
Copyright © 2015 Varun Varghese 184
Applying special bookmarks
Copyright © 2015 Varun Varghese 185
Copyright © 2015 Varun Varghese 186
Text Area: Property Controls – Iron Python Scripting
Copyright © 2015 Varun Varghese 187
Inserting action control
Copyright © 2015 Varun Varghese 188