Data Visual Analysis 2.0: Analysis Principles and Tableau, SQL Practice

by DeepL Pro

Visit 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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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



喜乐君 | Tableau Visionary 20202-2023,《数据可视化分析》《业务可视化分析》作者


Fill in your details below or click an icon to log in: 徽标

您正在使用您的 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: