by DeepL Pro
Visit www.DeepL.com/pro for more information.IV
Title：Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
Preface to the 2nd edition
Thanks to Tableau and readers for your support, you give the author the motivation to keep going. At the beginning of 2020, during the three months of the epidemic, I wrote down what I had thought and thought for years of learning Tableau without reservation, and published and distributed it with exquisite pictures. Data Visual Analysis: Tableau Principles and Practices” received a lot of praise from readers, accumulated 8 printings and over 10,000 copies, and met many Tableau fans, business users and visualization enthusiasts in the readership.
Over the past two years, Tableau products continue to update and iterate, the author also further supplemented the basic knowledge of SQL, database, data warehousing, and contrasted learning the application of PowerBI, FanSoft, Guangyuan and many other excellent BI products at home and abroad. “Practice is the best teacher”, the author in the analysis project consulting, Tableau enterprise training, visualization development project exercise, gradually realize the first version of the book many deficiencies and even errors, and give up the small repair plan, vowed to rewrite this book as a summary of the last two years of learning.
So, starting in April 2022, the author began to completely rewrite the contents of this book, redrawing and adjusting most of the illustrations. After the ups and downs of the epidemic and many delays, it finally came to the reader belatedly.
Here, the author summarizes the content improvements of the second version compared to the first version.
1. The second version of content improvements are summarized as follows:
— Thinking about digital transformation in practice
Combined with years of personal project practice, the book summarizes the application of data and its stages, the multiple paths of digital transformation and the step-by-step organizational scheme (Chapter1).Business analysis methods and systems are more mature.
Business is the “soil” for analysis. During the project consulting process, I proposed a framework system of “business-data-analysis”, which can be combined with enterprise business processes to create digital map (Chapter 2). At the same time, I tried to go beyond tools to build a universal business analysis approach around problem structure, aggregation, aggregation and level of detail, which is applicable to various analysis and BI tools and can even be used as a measure of analysis tools (Chapter3).
— In the three themes of data merging, data filtering, and calculation, the book introduces the application scenarios of Excel, Tableau, and SQL, and summarizes the “classification matrix of data merging”, “two types of filtering positions”, and other grounded summaries to help people without related backgrounds to quickly achieve beyond. The book summarizes “Classification matrix for data merging”, “Classification of calculation”, and other grounded summaries to help people without relevant backgrounds to achieve beyond faster, and also help “technicalists” from SQL to better understand the essence of agile BI. The high-level BI tools are not as simple as dragging and dropping, but behind the civilianization of technology, there is a more clever “business soul”.
— The importance of filtering in business analysis is further reinforced by separating “Data Filtering and Interaction” into Chapter 6. As the types of filtering are diverse and the priorities are complex, the abuse of SUM+IF type conditional aggregation should be avoided as much as possible, and treating filtering as a separate part of analysis is a key way to optimize analysis performance.
— Strengthen the concept of “level of detail” (instead of the previous concept of “hierarchy”), in addition to the data table level of detail (Table LOD), view level of detail (Viz LOD), using “reference level of
detail “(Reference LOD) to represent the pre-specified level of detail outside the view; in this way, the author integrates the three keys of data relationship, filtering and calculation, which will be the most important knowledge asset of this book and is the key to understand the commonality of analysis behind different tools beyond Tableau.
— Adjusts the intellectual framework of Title 3 computing, without which there is no endless business analysis, which is the most important feature of the book.
Chapter 8 reinforces the two major classifications of computation : row-level computation to complete data preparation and aggregate computation to complete business analysis. After introducing common functions by category, the differences and connections between the two types of calculations are introduced with the help of logical calculations.
Chapter 9 uses a new framework to introduce Tableau table calculations and SQL window functions, which represent the “secondary abstraction of abstraction” and are the first steps towards advanced analysis;
Chapter 10 combines “SQL aggregation subqueries” to explain LOD principles in depth, and combines classic cases such as product shopping baskets and customer RFMs to extend the concept of “pre-aggregation” in advanced analysis to more general business analysis.
— A new chapter on “From Data Management to Data Warehousing” (Chapter 11) has been added, which is the key to moving from visual analytics to professional data modeling and data methods. “See Tableau Server as a DW/BI platform”, giving more companies a new choice.
— Due to the limitation of space, most of the content related to Prep Builder data processing and Tableau Server has been removed.
2. Acknowledgements
Whenever I finish writing, I am always eager to share. Like “Data Visualization Analysis: Tableau Principles and Practices” and “Business Visualization Analysis: Tableau Methods from Problems to Graphics”, before this book went to press, I organized an offline activity of “HILO Jun Boutique Class” in Shanghai to introduce the core contents of this book in detail and teach each other. has further During the sharing process, the blind spots of knowledge system were further discovered. Thank you to the enthusiastic readers from Trina Solar, Hand Axle, Shanghai Electric, Hainan Airlines, SAIC, Infineon and other companies.
Thanks to my corporate clients who continue to support and trust me, I will reward you all with my professionalism and love.
Thank you, Tableau, for giving me the courage to walk through the fog, and I will support you indefinitely, worthy of the title “Tableau Evangelist” and the global honor of Tableau Zen Master/Visionary.
Thanks to my parents, my family, “great love has no words”, I should spend the rest of my life to repay with actions.
Yupeng Wu
Jan,20, 2023
Preface to the 1st edition ┃ VII
“Do Birds Fly Because They Have Feathers” – Tableau and the Author’s Analytical Journey
It has been exactly three years since I stumbled upon Tableau in 2017, and from yesterday’s hobby to this day’s work, it seems like an instant, but it seems like half a lifetime. Now, I have accomplished a task that I had not imagined before – to share what I think and know to more people in the way of publishing.
Idealists always have a habit of underestimating difficulties, and this is especially true of the matter of writing a book. With a total of 638 carefully crafted illustrations, different from the blog content and establishing a new system framework, you and I are looking at each other across the book, but I hope every reader can feel my unreserved writing attitude and efforts. 2021, I am honored to be listed in Tableau Zen
Master Global List together with Tableau artist Wendy, which is the best recognition from readers and Tableau to This is the best recognition from readers and Tableau to the author.
At the same time, I still want to talk about the origin of the author and Tableau, so as to explain how I started from scratch with a liberal arts degree and a business background to become the “Tableau Ambassador” today.
1. The origin of Tableau and Me
After graduation, I went through several exercises in state-owned enterprises, entrepreneurship and private enterprises, and returned to Baby Belle as the assistant to the president in 2017, taking time off from my busy schedule to study around and gain the company privilege of “reimbursement for any books I bought”.
Considering the inefficient “PPT data tradition” and the weakness of my own professional data analysis knowledge, I searched for various big data analysis tools privately and was finally convinced by Tableau’s
flexibility, ease of use and beauty. Then one after another for operations, procurement, human resources and other boards to do some not mature analysis.
I am a typical “writing type”, so from the first week of study, I have been taking notes and writing blogs one after another, purely to help myself enhance understanding, but unexpectedly, after three years, I have accumulated a considerable amount of ink. “All success is the triumph of long-termism”. Data and data analysis happens to be a good windfall, so I entered this “strange but new industry” by mistake.VIII ┃ Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
The Economist wrote: “The most important resource of the 21st century is data”, but data without analysis has no value, just as “life without reflection is not worth living” (Socrates), and this is exactly the growing trouble encountered by enterprises in transition. I decided to go with Tableau, integrating my years of work experience with my understanding of data, and seriously serve every customer while gaining self-improvement.
I chose Tableau and later passed the Tableau Desktop and Server QA certifications, and met many Tableau employees and enthusiasts when I attended the Tableau Summit, and then began a wonderful journey of developing and serving customers.
In the process of serving customers, I continue to accumulate my Tableau knowledge and business understanding, and continue to update my blog to enhance understanding and spread Tableau culture to more customers. The author never turns down any questions from customers and sees it as the best opportunity to collect problems and keep learning -there is nothing that learning can’t solve, and if there is, it’s a failure to learn the art. Pursuing delivery that exceeds expectations has not only helped the author increase the probability of renewals and incremental purchases from clients, but has also continued to accumulate material for the nextbook.
2. From what is known to what is understood
In the learning process, I continue to read all kinds of data analysis books, and carefully go through the official nearly 10,000 pages of documentation and white papers. Unfortunately, every Tableau topic book I could find at home and abroad could only satisfy my initial learning, but not my appetite for intermediate and advanced advancement. I always felt that the essentials were not refined and the framework was not clear, just like a martial arts manual lacking the last chapter, even though I was familiar with various moves, it was difficult to do what I wanted in front of the real world. This constraint in understanding hinders the delivery of the highest quality training, implementation and consulting to our clients. After studying with Mr. Siyue Wang of Shandong University for ten years, he taught the author an attitude of dealing with things: “interact with people to change yourself, and deal with things to change each other.” Therefore, the author wants to reconstruct Tableau’s knowledge system and hopes to help both beginners and advanced analysts to better use Tableau products.
At the beginning of Professor Christensen’s book “How should you measure your life”, the author asks a question that I will never forget: “Do birds fly because they have feathers?” I used to think so, but as the author says, humans have been trying to imitate light wings to fly for thousands of years, and in the end, it was tons of steel airplanes that did it. 100 years ago, humans accumulated enough knowledge in the fields of “fluid dynamics” and “aerodynamics” to achieve the goal. This is the importance of the principle. Many people think that the mastery of the principle is a matter for a few people, but they do not know that the theory is practical, and it gives the ability to “learn by example”.
Therefore, the author is eager to gain insight into the principles behind Tableau’s “drag and drop”,visualization, and especially advanced computing. Only by mastering the principles can the author use the simplest language to allow all customers to exchange the most efficient training and usage results with the lowest cost in time and money. And the road to great enlightenment is only one, and that is the road of continuous effort and deep thinking fusion.
In 2019, the author continued to launch a general attack on Tableau’s most difficult advanced calculations and advanced interactions, and continued to revise the blog as clear evidence of access; on the other hand, he organized monthly Tableau public courses to deepen his understanding in the process of sharing, and continued to summarize the macro framework of the book in the process of delivering training for customers such as Centaline Consumer Finance and Ealing Pharmaceuticals. In 2019, I got the key inspiration for Chapter 5 of this book during the training for Guolian Fisheries, and in 2021, I got the key inspiration for “Business Fields, Analysis Fields” during the internal communication in the information department of Changlong Group, and started to conceptualize the “Business Data Analysis Map” during the project of Ping An P&W. “in a project of Ping An P&W.
In this process, I continued to write blog articles for record and reflection, and now I have many Tableau blog articles, especially the series on “LOD Detailed Level Expressions” principles and case interpretations， which are almost on a par with the official introduction articles. In the beginning of 2020, due to the epidemic, I was able to rewrite every detail and its ideas, and merge basic and advanced calculations into a new explanation system, so that even beginners can quickly master the most difficult aspects of knowledge.
Finally, the author found the fundamental difference from Excel analysis to Tableau data analysis, namely the hierarchy (LOD level of detail). The objective table LOD describes the data structure and granularity, while the subjective viz LOD describes the business problem and its relevance, and integrates the two through multiple categories of calculations. The idea of “hierarchical analysis” is present throughout the book and is sublimated in the advanced computing section – the essence of advanced computing is multi-level problem analysis. As a result, the reader will see many new aspects in this book, especially understanding the core features of big data analysis in terms of hierarchy (level of detail), understanding data structures and identifying row-level uniqueness, understanding Tableau calculations and guiding how to choose them, etc.
And the carefully drawn illustrations are designed to enhance understanding with visuals, not just text. And through secondary processing, the knowledge density of each image is increased as much as possible.
3. Trends in the era of big data and business-driven data analysis
With the booming Internet economy, the era of big data has become an inescapable fact. In the face of the economic crisis, enterprises should pursue lean growth driven by lean analytics, and building an analytics-centric agile platform has become indispensable.
Because of this, agile BI has become an irresistible trend. Data analytics is the link between data assets and value decisions, and agile BI improves the efficiency of data utilization and enterprise decision making. “The future is here, and all enterprises will be data-driven organizations.
For enterprises, Tableau provides an agile “data warehouse, business analytics integration” DW/BI total solution. Tableau is an excellent enterprise-class big data visualization and analysis platform for both SMBs and large enterprises, and its business-oriented excellence is hard to be matched by peer products so far.
For business analysts, Tableau is easy to get started and flexible to use, so it is suitable for almost every
data user and business decision maker in the enterprise; at the same time, Tableau is profound and professional enough, and there is infinite space to explore in terms of visualization style, interactive exploration, and advanced computing, so Tableau analysts who keep studying can build a high enough Technical barriers, so as to defend their professional territory. This is also the author’s choice and path, as long as the effort, everyone can imitate, there is no so-called “learning power”, all that is needed is the intention and effort.
On this path full of light, the biggest obstacle is actually not the tools, but the people and culture. With the help of this book, I sincerely hope that more people will become proficient in Tableau and build their own professional barriers, saving time is saving personal lives and increasing efficiency is creating corporate profits.
4. Acknowledgement
From a blog to a book, something the author hadn’t anticipated a year ago; quarantined at home because of the epidemic, a spring, unexpectedly the dream became a reality.
Special thanks to Mr. Xiaoqiang Tang of , Mr. Yang Liu of Budweiser, Mr. Cong Fu of Hongta Group, Mr. Binxiang Huang of Jinfa Technology and many other readers for their contributions to the errata of this book.
Special thanks to Tableau for giving me the opportunity to learn and meet business customers, friends and readers from all walks of life.
I am grateful to my family, who have given meaning to the author’s life, and who have accompanied each other every day of the epidemic writing the book as they grow older.
Thank you for the time and for a life full of ups and downs and joys.
Yupeng Wu
Revised on January 20, 2023
Content and description
Content and description
Part one: Enterprise digital transformation and business analytics approach
This article breaks away from Tableau to help readers understand the system of data (the pyramid perspective of technology, the “map perspective” of business), and to understand the different paths of digital transformation of enterprises and different scenarios of data application. Most importantly, it introduces the core concepts and methodologies of analytics, away from all tools.
Chapter One Digital Transformation: Opportunities and Challenges in the 21st Century
Understand the layers of data, the stages of data application, and the digital transformation of the
enterprise.
Chapter 2 Business perspective analysis system and enterprise analysis map
The business-data-analysis hierarchical framework is built from a business perspective, and
enterprise-level data maps; visualization is an important expression of enterprise data analysis;
Tableau is a superior enterprise analysis tool and platform.
Chapter 3 Business Visualization Analysis: Key Concepts and Methodology
The core principles of this book are all in this chapter. The structural analysis of problems,
aggregation is the essence of analysis, and the construction of multiple problem metrics
(aggregation degrees) based on aggregation leads to advanced problem analysis. Metrics are the
business form of aggregation metrics.
Data tables are the starting and ending point of analysis, and visualization is another form of
presentation of aggregated tables.
Part Two: Data Modeling, Visualization and Interaction
This article introduces the three segments of business analysis: data preparation,
visualization graphics, and dashboard presentation. The key to data preparation is the logic
model, the key to visualization is field types and question types, and the key to dashboard
presentation is interaction.
Chapter 4 Data Merging and Relational Modeling (Tableau/SQL)
The data merging classification matrix contains row-level merging and aggregation table
matching. The row-level merging is divided into two types of merging Union and joining Join;
the aggregation table matching is divided into flexible mixed Blend and stable relationship
Relationship.
The key to understanding the data model is to understand the difference between physical layer
merging and logical layer matching.
Chapter 5 Visual Analysis and ExplorationXII ┃ Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
From problem classification to visual graphical styles, we combine markers, axes, and reference
lines to accomplish a variety of methods of enhanced analysis, of which the reference line is the
“modeling” of advanced computation and is the bridge between visualization and table
computation.
Chapter 6 Tableau/SQL Filtering and Set Interaction
Various filter types and complex system. Compared with the SQL filtering syntax, the Tableau
filtering classification system is reconstructed, and when multiple filters are included, the same
type takes the intersection and different types look at the priority. Set is an advanced filtering
tool, which is essentially a classification judgment. Parameters are used to control the scope of
the screening, set, is the most common variable.
Chapter 7 Dashboard design, advanced and advanced interaction
The dashboard is the most important representation, and the interaction is the flexibility of the
dashboard presentation. Basic interactions include quick filtering, highlighting, jumping, etc.
Advanced interactions are based on parameters, sets (usually variables), and mostly have to be
combined with calculations to be completed. This chapter also introduces Metric, Initial
Template, and Performance Optimization.
Part Three: Endless analysis with finite fields: Tableau, SQL functions and computational systems
The key to this article is the calculation, based on the level of detail to build a hierarchy from shallow to deep, is the key to the reader to understand Tableau, to understand the general analysis.
Chapter 8 The underlying framework of the calculation: row-level counting, aggregation calculation and its
functions
Row-level calculations complete data preparation and aggregate calculations complete business
analysis, both of which form the basis of the calculations. This chapter combines Excel, SQL and
Tableau to explain, and introduces the corresponding functions of Tableau. String functions and
date functions are row-level, while arithmetic calculations and logical functions are generic.
Chapter 9 Advanced Analysis Functions: Tableau Table Calculation/SQL Window Functions
Analysis is abstraction, abstract aggregation, “secondary aggregation of aggregation or inter-row calculation” is a typical example of advanced abstraction, typical cases are aggregate percentage, same ring difference. This chapter introduces typical calculation scenarios and functions such as sorting, moving average, and window aggregation, and introduces the application of nested table calculations, as well as cases such as “aggregate profitability”, benchmarking, and Pareto.
Chapter 10 Structured problem analysis: LOD expressions (SQL aggregation subqueries) and cases
The LOD table calculation is used to reference pre aggregated values in a view, which is similar to SQL aggregation subquery. This chapter introduces its principles, types and functions, and provides an in-depth introduction to typical cases such as member RFM and product shopping basket analysis.
Chapter 11 From Data Management to Data Warehouse: See Tableau as a DW/BI Platform
Tableau is not only a visual analytics tool, but also an enterprise-level big data analytics platform. This chapter introduces Tableau Server’s data management-related functions and describes the illustrative ETL process. It is recommended that enterprises consider Tableau as a DW/BI platform to build an agile analytics system.
Part 1
Foundation: Principles of Digital Transformation and Business Analytics
Chapter 1 Digital Transformation: Opportunities and Challenges for the 21st Century . 2
1.1 Understand the hierarchy of data and the value of .analysis 2
1.2 The 3 stages of data .application .4
1.2.1 Primary-Report Presentation: Collation and Fixed Presentation of Information 5
1.2.2 Intermediate-level-business analysis: analysis aids decision making, decision making
creates value . 7
1.2.3 The ultimate-“smart business”: big data reshapes business models .8
1.3 What exactly does “digital transformation” “turn” 10
1.3.1 Shaping a culture of factual data: “Everything speaks with data ” 10
1.3.2 Digital transformation stems from the digitization and continuous evolution of each
business scenario . 12
1.3.3 Centers of excellence and analytical talent with both business and technical skills
1.3.4 Unify and continuously optimize analysis methodology to improve analysis efficiency and accuracy
15
Ref. . 18
Chapter 2: “Business-Data-Analysis” Analysis System and Enterprise Data Map
2.1 “Business-Data-Analysis” system: BDA analysis . framework19
2.1.1 Analytical layer: indicator system construction and analytical dashboard21
2.1.2 Data Layer: Data Management and Data Warehouse22
2.1.3 Business layer: business processes and “business online ” 23
2.2 Building a global view: the enterprise data 24
2.3 Two paths to enterprise-level analytics: “top-dmapown” and “bottom-up ” 26
2.3.1 Bottom-up: The path to analysis from data 26
2.3.2 Top-down: the path of analysis from issues and indicators27XIV ┃ Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
2.4 Visualization as a bridge and medium for big data .analytics28
2.4.1 Visualization of numbers, text, and visualization elements28
2.4.2 From visualization to abstract analysis: towards dashboards and advanced analysis30
2.5 Tableau: The “Masterpiece” of Agile Business Analytics for Big Data32
Ref. . 34
Chapter 3 Business Visualization Analysis: Key Concepts and Methodology35
3.1 Dissecting problem structure, understanding aggregation processes and .metrics35
3.1.1 Structure of the problem and its interrelationships36
3.1.2 Aggregation is the essential process of problem . analysis38
3.1.3 Aggregation-based field classification: dimensional descriptions of questions, metrics to
answer answers40
3.1.4 Indicators are the business form of aggregated metrics41
3.2 Detail and Aggregate Tables: The Logical Process of . Aggregation42
3.2.1 Business schedules and issue aggregation tables: the starting and ending points of aggregation43
3.2.2 Physical and Logical Tables: Abstract Types of Data Tables45
3.2.3 Data types for fields: abstract types for data table .fields46
3.3 Visual charts: The “other side” of the aggregated crosstab .49
3.3.1 Problem Types and Visualization Enhancement Analysis50
3.3.2 Data types behind visualization: continuous and .discrete51
3.3.3 Field properties and their roles in .Tableau55
3 .4 Three-step approach to simple problems and Tableau examples
3.5 Aggregation and Level of Detail: Building a Hierarchy Theory of Complex .Problems58
3.5.1 Data Breakdown and Aggregation: Common Benchmarks and Metrics for Multiple
Issues59
3.5.2 Level of detail: abstraction basis for different “aggregation” problems60
3.5.3 Two Application Directions for Structured .Analysis62
3.5.4 Summary of key concepts: aggregation, degree of aggregation, level of detail, granularity
. 64
Reference . 66
Exercise topic . 67
Part 2 Data Preparation, Visualization, Interaction DesignCatalog ┃ XV
Chapter 4 Data Table Merging and Relational Models (Tableau/SQL) 69
4.1 Overview: Data merging and connecting data sources .70
4.1.1 Understanding the importance of data merging, data .modeling 70
4.1.2 Concepts related to data merging and data models 72
4.2 Classification Matrix and Data Model for Data Merging Case 73
4.2.1 “WYSIWYG” Row-Level Data Merging: Union and Join .74
4.2.2 The limitations of Excel: pivot table-based data merging 75
4.2.3 Tableau Data Mixing Primer, Completing JOIN Joins After Aggregation77
4.2.4 Data merging “classification matrix”: two merging methods, two merging
positions 79
4.3 Row-Level Concatenation, Joins, and Tableau/SQL Methods
4.3.1 Data Union (Data Union) 82
4.3.2 Data Join: connection conditions and connection methods 85
4.3.3 Advanced forms of connections: left-hand only, cross-connections and “self-connections”
. 90
4.3.3 The similarities, differences and limitations of the Union and Join
4.4 From Data Relationship Matching to Relational Models96
4.4.1 “Temporary” data relationships: Creating data relationship matches based on the
problem 97
4.4.2 Data model: pre-construct data relationships at the most detailed and business meaningful
level99
4.4.3 [Key] Hierarchical Analysis Method: From Data Merging to Data Relationship Model 101
4.4.4 [Difficulty] Relational Model Optimization (above): Matching Type (Base) .106
4.4.5 [Difficulty] Relational Model Optimization (Next): Matching Scope (Referential Integrity)
. 111
4.4.6 Shared dimension table: from snowflake model to mesh .model117
4.4.7 Access to best practices: visual representation of business relationship .models118
4.4.8 Case: Data Relationship Model for Books and Sales 119
4.5 Rephrasing Data Mixing: Editing Matching Relationships and Matching Detail Levels 121
4.5.1 Data Mixing Settings: Custom Mixing Conditions and Custom Matching Fields . 122
4.5.2 Advanced data mixing: data matching at a different level of detail than the main view .124
4.6 Interaction of different data merge types .128
4.7 Combining Tableau with .SQL/Python .129
4.7.1 Combining Tableau and .SQL 129
4.7.2 SQL’s JOIN connection 130
4.7.3 Tableau Table Extensions: Putting “Algorithmic Wings” on Data Sources (2022.3+) .133
Ref. . 134
Exercise topic 134
Chapter 5 Visualization Analysis and Exploration 135
5.1 Data Preparation: Understanding Business Processes and Organizing Data Fields 135
5.1.1 Data Tables: Understanding Business Processes and “Data Table Levels of Detail” 135
5.1.2 Fields: Understanding the objects of business processes and doing grouping and
classification . 137
5.2 From problem to visual graph: How to define the main view frame 139
5.2.1 From problem types to major visualizations 139
5.2.2 Primary Visualization: “Three Figures and a Table ” . 140
5.2.3 Intermediate visualization: distribution analysis, correlation analysis .144
5.2.4 Geospatial Visualization . 149
5.2.5 Data Image Role (Image Role) Visualization (2022.4+) . 156
5.3 Visualization Drawing Methods and Visualization Enhancements 157
5.3.1 Doing visualization like oil painting: the three steps of visualization and the use of
markers158
5.3.2 Metric biaxial and its integrated processing .159
5.3.3 The “common datum” for multiple axes: the metric . 161
5.4 Introduction to Advanced Analysis: Reference Lines and Reference Intervals .162
5.4.1 Creation of reference lines and their combinations .162
5.4.2 Gantt and target charts: two combinations of bars and reference . lines164
5.4.3 Reference intervals, box-and-whisker plots, and standard deviation . distributions167
5.4.4 Confidence interval model 168
5.4.5 Trend Lines and Forecast Lines . 169
5.4.6 Cluster 170
5.5 Formatting: adjust as necessary, but don’t overdo . it171
5.5.1 Common Settings Format Toolbar .171
5.5.2 Set the “label” format, custom text table .172
5.5.3 Tooltip formatting, interaction, and “picture-in-picture” . 173Catalog ┃ XVII
5.5.4 Other common tips174
Reference . 176
Exercise topic 176
Chapter 6 Tableau/SQL Filtering and Set Operations .177
6.1 Understand the screening methods and commonalities behind different .tools177
6.1.1 Two types of positions for filtering: independent filtering and “conditional calculation”
. 177
6.1.2 Use different tools to complete “independent screening” 178
6.2 Classification methods for screening: a level-of-detail-based perspective 183
6.2.1 [Getting Started] Data Table Row-Level Filtering: Dimension Filters 184
6.2.2 [Advanced] Specifying Detail Level Aggregate Filtering: Simple Conditions and Top
Filtering . 187
6.2.3 [Difficulty] Specifying filters for detail-level aggregation: building on custom
calculations193
6.3 Interactive methods for filtering ranges: fast filtering and parameter .control196
6.3.1 Quick Filter (Quick Filter) and its basic . configuration196
6.3.2 Special date filter: default filter to the latest date 197
6.3.3 Parameter Control: Full Independence and Dependent Reference .198
6.4 Handling of multiple filters: intersection calculation and priority . 200
6.4.1 Basics of multiple filtering: data sets and operations . 200
6.4.2 Computational principles of multiple filtering (above): the same type to take the
intersection (intersection) . 201
6.4.3 Principle of calculating multiple filters (below): intersection of different types by
Priority201
6.4.4 Adjusting Filter Priorities (above): Context Filters and Table Calculation Filters .203
6.4.5 Adjusting Filter Priorities (below): Data Source Filters and Data Extraction Filters 205
6.4.6 Priorities for filtering and calculation207
6.5 Set: The “magic container” that keeps the filtering down .208
6.5.1 Creating Custom Sets and the Nature of Sets 208
6.5.2 Customizing set membership: “Set control” (2020.2+) 210
6.5.3 Creating Dynamic Condition Sets 210
6.5.4 Set Actions: Updating Set Members with View Interaction212XVIII ┃ Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
6.6 Set Operations, Priorities, and Applications213
6.6.1 Merging multiple sets and the “merge set” operation213
6.6.2 Relationship and priority of set and filter 216
6.6.3 Advanced applications of the set: “user filters” to control user permissions .218
6.7 Intermediate Interaction: Quick Filter, Set Interaction in Dashboard 219
6.7.1 Basic classification of interaction design 219
6.7.2 “Sifting through graphs”: fast correlation filtering of multiple tables in the dashboard 219
6.7.3 Shared filters, sets, and parameters: typical dashboard interactions 221
6.7.4 Tooltip “picture-in-picture”: the simplest multi-table association 224
6.8 More utilities: grouping, data buckets, hierarchical structures, sorting .225
6.8.1 Groups as Data Preparation 225
6.8.2 Drill-down analysis of stratified structures with only correlation values displayed226
6.8.3 Sorting: Sorting Data Values for Discrete Fields 227
Reference . 229
Exercise Title 229
Chapter 7 Dashboard Design, Advanced and Advanced Interaction 231
7.1 DashboardDashboard: The most important form of theme . presentation231
7.1.1 Basic Dashboard Design Process and Common Functions 232
7.1.2 Dashboard size, layout, and objects234
7.1.3 Commonly used interactive objects: hidden buttons, navigation buttons . 238
7.1.4 Hierarchical structure in dashboard layout .240
7.1.4 Dashboard Adaptation Across Device Types .241
7.2 StoryStory: Narrating and exploring .242 with data stories
7.2.1 Stories and their basic settings .242
7.2.2 How the story is presented 243
7.3 Dashboard Advancement: “Metrics”, Initial Templates, Performance Optimization, and “Data
Guides” . 244
7.3.1 “Metrics” Metrics: Focus on Dashboard Key Metrics . 245
7.3.2 “Initial templates” (Accelerators): expert analysis templates to accelerate analysis . 247
7.3.3 Publish workbooks and “workbook optimizer” (Optimizer) .249
7.3.4 Data Guide (version 2022.3+) 252
7.4 Three basic interaction types: highlighting, filtering, and page . 253
7.4.1 Highlight: indirect filtering with focus254Catalog ┃ XIX
7.4.2 Page Rotation (Page): Continuous Overlay for Quick Screening 256
7.5 Two types of advanced interaction tools: parameter, set . interaction258
7.5.1 Key principles: commonalities and differences of parameters, sets .258
7.5.2 Combining parameters and logical judgments: switching view metrics . 261
7.5.3 Dynamic parameters: dynamic update of ranges and initial values . 263
7.5.4 Set Control: Manually Update Set Members as a Control . 265
7.6 Parametric Actions: Parameters, Calculations, and Interactions (Version 2019.2+) 265
7.6.1 Parameter Actions: Updating Metric Values Using Actions .266
7.6.2 Dynamic Screening: Parameter Actions and Calculations for Differentiated Screening
. 267
7.6.3 Dynamic Benchmark Analysis: Using Parametric Action Control Reference Lines and
Calculating Benchmarks . 269
7.6.4 Customizing the hierarchy: Expanding the specified categories using parameters .271
7.7 Advanced interactions: specifying dynamic visibility of region objects (2022.3+) 273
7.8 The pinnacle of advanced interaction: set action and set control276
7.8.1 Classic set actions: interactively updating custom sets (version 2018.3+) 277
7.8.2 Set Control and Update: Giving Sets a Powerful Soul279
7.8.3 Use set to complete comparative and benchmarking analysis 280
7.8.4 Suggestions for the use of advanced interactions 283
Exercise topic 284
Part 3 Doing Endless Analysis with Finite Fields: Tableau, SQL Functions,and Computational Systems
Chapter 8: The Underlying Framework for Computation: Row-Level and Aggregate Computation286
8.1 Evolution and classification of computing: from Excel, SQL to Tableau287
8.1.1 The nature of computing and its relationship to business . processes287
8.1.2 Understanding the two major classifications of detail levels and calculations with Excel 288
8.1.3 From Excel “access to one” to “database – SQL” access separation 292
8.1.4 Tableau: Integrating query, computation, and presentation294
8.2 Two major classifications of computation: analysis as an abstract process of aggregation 297
8.2.1 Differences and relationships between row-level calculations, aggregate . calculations298XX ┃ Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
8.2.2 Understanding the results of calculations from a business perspective: business fields vs.analysis fields300
8.3 Data preparation class functions (above): string functions, date functions 302
8.3.1 String functions: interception, find-replace, and other cleanup functions .302
8.3.2 date function: date uniqueness and conversion, calculation 307
8.3.3 Data Type Conversion Functions .315
8.4 Data preparation class functions (below): “Regular expressions” 316
8.5 Analysis functions: “direct aggregation” from details to problems 319
8.5.1 Describe the scale: sum, count, mean . 319
8.5.2 Describing the degree of volatility of data: variance and standard deviation 320
8.5.3 Focus on the individual, towards the distribution: percentile function and maximum,minimum, median 322
8.5.4 ATTR Attribute – Aggregate Judgment for Dimension Fields 324
8.6 General-purpose computing: arithmetic and logical functions 325
8.6.1 Arithmetic operations, precision control functions . 325
8.6.2 Logical Expressions and Logical Judges 329
8.7 Differences and Combinations of Row-Level and Aggregate .Computation332
8.7.1 Example: Analysis of profit and earnings structure by subcategory 333
8.7.2 Review: Differences between row-level and aggregate calculations334
8.7.3 SUMIF Conditional Aggregation: Combining row-level filtering and aggregation analysis
into one 336
8.8 Topic: Geospatial analysis of “spatial functions” . 338
Reference . 346
Exercise topic 346
Chapter 9 Advanced Analysis Functions: Tableau Table Calculation/SQL Window Functions
9.1 Two methods of aggregation and “generalized LOD expressions ” .347
9.1.1 Getting Started: Understanding the Hierarchy of “Total Percentage” Calculations from Excel348
9.1.2 Hierarchical framework for advanced analysis: data table level of detail and “aggregation”
. 349
9.1.3 Advanced: Two SQL Methods for “Total Percentage” 350
9.1.4 Tableau Agile BI for Business Users to Easily Navigate Secondary Aggregate AnalysisCatalog ┃ XXI
. 352
9.1.5 Classification of “generalized LOD expressions” and calculations355
9.2 “YoY/YoY” Offset Calculation and Table Calculation Setup Method .357
9.2.1 Dimensionality as a basis for offset calculation: year-over-year differences in a single
dimension .357
9.2.2 Year-over-year with multiple dimensions (above): distinguishing between scope and basis359
9.2.3 Year-over-year with multiple dimensions (below): Setting multiple bases . 361
9.2.4 SQL Window Functions: Offset Class Window Functions Case Introduction 362
9.3 Summary: The uniqueness of table calculations and two ways to set them up .366
9.3.1 Understanding the uniqueness of window calculations from two approaches to
difference calculations . 366
9.3.2 Two ways to set the range in Tableau: relative/absolute methods and applicable scenarios
. 369
9.4 Advanced analysis functions of the sorting calculation: INDEX and .RANK371
9.4.1 Tableau/SQL Sorting and Percentile Sorting371
9.4.2 Public benchmark comparison: comparison of movie box office at different times (TC2)
. 375
9.4.3 Convex plots: RANK function with date (TC4) . 378
9.5 The most important secondary aggregation function: WINDOW window function 381
9.5.1 Total: the simplest, commonly used WINDOW window functions .382
9.5.2 cumulative aggregation: RUNNING_SUM function – cumulative car sales 384
9.5.3 Moving aggregation: MOVING AVG moving window calculation function 386
9.5.4 “Grand unification”: a thousand different WINDOW window functions . 387
9.6 Most commonly used table calculations: Quick table calculations and their additional calculations
390
9.6.1 Quick Table Calculation: Preconfigured Common Table Calculation Applications391
9.6.2 Nested Fast Table Calculations: Combinations of Table Calculations (TC3) 392
9.7 Table Calculation Application (1): Custom Reference Lines, “Aggregate Margin” .394
9.7.1 Aggregate reference lines – a “visual form” for table calculations394
9.7.2 “Aggregate profitability”: understanding the table calculation corresponding to the
reference line 395
9.7.3 [Difficulty] Understanding the Difference Between TOTAL Totals and WINDOW_SUMXXII ┃ Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice
Summaries . 397
9.7.4 Custom reference lines and their calculation: box-and-whisker plot relaxation and scatter
plot color matrix . 399
9.8 Table Calculation Applications (2): Benchmarking Analysis – Combinations of Multiple Types of
Calculations . 403
9.9 Table Calculation Applications (3): Pareto Distribution – Cumulative, Aggregate and Nested 407
9.10 Table Calculation Applications(4): Financial ANR Calculation – Advanced Nested Table Calculation
410
9.10 Table calculation filters: the lowest priority filter type .414
9.10.1 Use RANK aggregation judgment to complete the filter . 414
9.10.2 Using the LOOKUP offset function to complete year-over-year and filtering 416
9.11 Extended Application of Table Computation: Predictive Modeling Functions 417
9.11.1 MODEL_QUANTILE prediction model . 418
9.11.2 MODEL_PERCENTILE prediction model . 420
Exercise topic 421
Chapter 10 Structured Problem Analysis: LOD Expressions and SQL Aggregate Subqueries
422
10.1 Business Analysis: Understanding the Logic and Nature of LOD Expressions 422
10.1.1 Simple level of detail: “Number of customers with different purchase frequencies” 422
10.1.2 Multidimensional level of detail: “Sales contribution by year, different matrix years”
. 425
10.2 “Level of detail” of LOD expressions and their relationship to .views428
10.2.1 Understanding what constitutes a high-level problem from the problem level of detail
. 429
10.2.2 Main view references “higher aggregation” level of detail aggregation: percentage
analysis 430
10.2.3 Main view references “lower aggregation” level of detail aggregation: purchasing power
analysis432
10.2.4 Master view referencing independent detail level aggregation: customer matrix analysis
. 434
10.3 Relatively Specified LOD Expressions and Operation Priorities .436
10.3.1 Absolutely specified and relatively specified LOD expressions 436
10.3.2 INCLUDE LOD references lower aggregation level aggregation and priority 438Catalog ┃ XXIII
10.3.3 EXCLUDE LOD references higher aggregation levels, and priority comparison 440
10.4 Beyond LOD: A detailed level system of computation and its priorities442
10.4.1 Application scenarios and roles of different computing . types443
10.4.2 Hierarchical analysis: understanding the arithmetic logic of computation and its
combinatorial form 444
10.4.3 Tableau Calculation, Filtering, and Prioritization of Data .Relationships446
10.5 Toward Practice: The Multi-times Convergence Problem and Structured Analysis
Methods447
10.5.1 Methodology: 4 Steps to Advanced Problem .Analysis447
10.5.2 LOD Multi-pass Aggregation: Nested LOD Calculation for Customer Buying Power
Analysis . 448
10.5.3 Syntax and SQL Representation of Nested LODs 453
10.6 Member analysis topic: Member RFM related case study 455
10.6.1 Introduction to Membership Analysis and Common Indicator Systems455
10.6.2 Using Tableau to Complete RFM Topic Analysis Metrics . 458
10.6.3 Single-dimensional distribution cases: membership frequency distribution and lifecycle
.distribution461
10.6.4 Multidimensional structural analysis: an example of analysis related to “customer
acquisition time” . 462
10.6.5 Repurchase Interval: A Combination of Row Level Calculation and LOD Calculation
. 465
10.6.6 Customer retention analysis: a combination of LOD expressions, table calculations .466
10.6.7 Customer matrix analysis: customer value classification (matrix) .469
10.7 Advanced Topics in Product Analysis: Multiple Perspectives on Shopping Cart .Analysis471
10.7.1 Shopping cart ratio: ratio of any subcategory relative to all .orders472
10.7.2 Support, confidence, and lift analysis: association recommendations between
categories .473
10.7.3 Association ratio for specified categories: filtering with “reference to the level of
detail” 476
10.8 Summary: Best Practices for Advanced .Computing481
10.8.1 The 3 major components of the problem and the 4 types of calculations481
10.8.2 How to choose a calculation . type483
Exercise topic 485
Chapter 11 From Data Management to Data Warehousing: The Building Blocks of Agile
Analytics
11.1 Data Management Data Management Functions: Data-Centric .487
11.1.1 Tableau Catalog Data Asset and Lineage Management .488
11.1.2 Tableau Prep Conductor Data ETL Process Management 492
11.1.3 Virtual Connections Virtual Connections: A Bridge Between Database and Analytics
. 493
11.1.4 Data Policy Data Policy: Adding Row Level Permissions to Data Access496
11.2 From Data Management DM, to Data Warehouse .DW500
11.2.1 Data warehouse is the product of a certain stage of development of data analysis .500
11.2.2 Logical Layering of Data Warehouse . 503
11.3 ETL: Data Processing in the Data Warehouse 505
11.3.1 Introduction to Prep Builder, an Agile ETL tool 505
11.3.2 The Impact of Agile ETL Tools on Data Analysis .506
11.4 Recommendation: Consider Tableau as DW/BI system . 507
Ref. . 509