Learn how to integrate BI tools like Tableau, Power BI, Looker, and Metabase with data warehouses. Master semantic layers, data modeling for BI, and building effective dashboards.
Learn how to integrate BI tools like Tableau, Power BI, Looker, and Metabase with data warehouses. Master semantic layers, data modeling for BI, and building effective dashboards.
Business Intelligence (BI) tools transform raw data from data warehouses into meaningful insights through visualizations, dashboards, and reports. Modern BI tools connect directly to data warehouses, enabling self-service analytics for business users without requiring deep technical knowledge.
This lesson covers the integration of popular BI tools with data warehouses, best practices for data modeling, semantic layer design, and building effective dashboards.
Learn about Modern BI Tool Landscape.
1
2┌─────────────────────────────────────────────────────────────────────┐
3│ BI Tools Ecosystem │
4├─────────────────────────────────────────────────────────────────────┤
5│ │
6│ ENTERPRISE BI TOOLS │
7│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
8│ │ Tableau │ │ Power BI │ │ Qlik │ │
9│ │ │ │ │ │ Sense │ │
10│ │ - Enterprise │ │ - Microsoft │ │ │ │
11│ │ - Powerful │ │ ecosystem │ │ - Associative│ │
12│ │ - Expensive │ │ - Affordable │ │ engine │ │
13│ └──────────────┘ └──────────────┘ └──────────────┘ │
14│ │
15│ CLOUD-NATIVE BI TOOLS │
16│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
17│ │ Looker │ │ Mode │ │ Sigma │ │
18│ │ │ │ │ │ │ │
19│ │ - LookML │ │ - SQL-first │ │ - Spreadsheet│ │
20│ │ - Google │ │ - Notebooks │ │ interface │ │
21│ │ - GitOps │ │ │ │ │ │
22│ └──────────────┘ └──────────────┘ └──────────────┘ │
23│ │
24│ OPEN-SOURCE BI TOOLS │
25│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
26│ │ Metabase │ │ Apache │ │ Redash │ │
27│ │ │ │ Superset │ │ │ │
28│ │ - Simple │ │ - Airbnb │ │ - Query-first│ │
29│ │ - Fast setup │ │ - Feature-rich│ │ - Alerts │ │
30│ └──────────────┘ └──────────────┘ └──────────────┘ │
31│ │
32│ EMBEDDED BI PLATFORMS │
33│ ┌──────────────┐ ┌──────────────┐ │
34│ │ Cube.js │ │ dbt │ │
35│ │ │ │ metrics │ │
36│ │ - API-first │ │ │ │
37│ │ - React │ │ - Semantic │ │
38│ └──────────────┘ └──────────────┘ │
39│ │
40└─────────────────────────────────────────────────────────────────────┘
41Learn about Connecting BI Tools to Data Warehouses.
Learn about Connection Architecture.
1
2┌─────────────────────────────────────────────────────────────────────┐
3│ BI Tool to Data Warehouse Architecture │
4├─────────────────────────────────────────────────────────────────────┤
5│ │
6│ ┌──────────────────────────────────────────────────────────────┐ │
7│ │ BI TOOL LAYER │ │
8│ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │
9│ │ │ Tableau │ │ Power BI │ │ Looker │ │ Metabase │ │ │
10│ │ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ │
11│ └───────┼─────────────┼─────────────┼─────────────┼───────────┘ │
12│ │ │ │ │ │
13│ └─────────────┴─────────────┴─────────────┘ │
14│ │ │
15│ ▼ │
16│ ┌──────────────────────────────────────────────────────────────┐ │
17│ │ SEMANTIC LAYER (Optional) │ │
18│ │ - Business-friendly field names │ │
19│ │ - Metrics definitions │ │
20│ │ - Relationships and joins │ │
21│ │ - Data access control │ │
22│ └───────────────────────┬──────────────────────────────────────┘ │
23│ │ │
24│ ▼ │
25│ ┌──────────────────────────────────────────────────────────────┐ │
26│ │ CONNECTION LAYER │ │
27│ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │
28│ │ │ JDBC │ │ ODBC │ │ Native API │ │ │
29│ │ └──────────────┘ └──────────────┘ └──────────────┘ │ │
30│ └───────────────────────┬──────────────────────────────────────┘ │
31│ │ │
32│ ▼ │
33│ ┌──────────────────────────────────────────────────────────────┐ │
34│ │ DATA WAREHOUSE LAYER │ │
35│ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │
36│ │ │PostgreSQL│ │ Snowflake│ │ Redshift │ │ BigQuery │ │ │
37│ │ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │ │
38│ └──────────────────────────────────────────────────────────────┘ │
39│ │
40└─────────────────────────────────────────────────────────────────────┘
41Learn about PostgreSQL Connection Example.
1# Tableau connection to PostgreSQL
2# Connection parameters typically stored in connection profile
3
4CONNECTION_PARAMS = {
5 'host': 'warehouse.example.com',
6 'port': 5432,
7 'database': 'analytics_warehouse',
8 'username': 'bi_readonly_user',
9 'password': '\${SECURE_PASSWORD}', # Use secrets manager
10
11 # Performance tuning
12 'sslmode': 'require',
13 'application_name': 'tableau_dashboard',
14 'connect_timeout': 30,
15
16 # Connection pooling (for tools that support it)
17 'max_connections': 10,
18 'min_connections': 2
19}
20
21# Sample Tableau Server data source configuration (XML)
22TABLEAU_DATASOURCE_XML = """
23<datasource caption='Sales Analytics' version='18.1'>
24 <connection class='postgres' dbname='analytics_warehouse'
25 server='warehouse.example.com' port='5432'
26 username='bi_readonly_user'>
27 <connection-customization>
28 <vendor name='postgres'/>
29 <driver name='postgres'/>
30 </connection-customization>
31 </connection>
32
33 <!-- Define relationships -->
34 <relation name='Sales Data' type='table'>
35 <columns>
36 <column name='order_id' datatype='integer'/>
37 <column name='customer_id' datatype='integer'/>
38 <column name='order_date' datatype='date'/>
39 <column name='total_amount' datatype='real'/>
40 </columns>
41 </relation>
42</datasource>
43"""
44Learn about Power BI Connection.
1# Power BI Direct Query vs Import Mode
2
3DIRECT QUERY MODE:
4 Pros:
5 - Always up-to-date data
6 - No data size limits
7 - Reduces Power BI storage
8
9 Cons:
10 - Slower performance
11 - Limited DAX functions
12 - Requires good warehouse performance
13
14 Use when:
15 - Real-time data needed
16 - Data volume > 10 GB
17 - Multiple users accessing same data
18
19IMPORT MODE:
20 Pros:
21 - Fast query performance
22 - All DAX functions available
23 - Works offline
24
25 Cons:
26 - Data refresh needed
27 - Storage limits (1 GB free, 10 GB Pro)
28 - Stale data risk
29
30 Use when:
31 - Performance is critical
32 - Data fits in memory
33 - Scheduled refreshes acceptable
341# Power BI M Query for PostgreSQL connection
2power_bi_m_query = """
3let
4 Source = PostgreSQL.Database(
5 "warehouse.example.com",
6 "analytics_warehouse",
7 [
8 Query="
9 SELECT
10 o.order_id,
11 o.order_date,
12 o.total_amount,
13 c.customer_name,
14 c.customer_segment,
15 p.product_name,
16 p.category
17 FROM fact_orders o
18 JOIN dim_customers c ON o.customer_key = c.customer_key
19 JOIN dim_products p ON o.product_key = p.product_key
20 WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
21 "
22 ]
23 ),
24
25 // Add custom columns
26 #"Added Month" = Table.AddColumn(Source, "Order Month",
27 each Date.StartOfMonth([order_date]), type date),
28
29 #"Added Quarter" = Table.AddColumn(#"Added Month", "Order Quarter",
30 each Date.QuarterOfYear([order_date]), type number),
31
32 // Set data types
33 #"Changed Type" = Table.TransformColumnTypes(#"Added Quarter", {
34 {"order_id", Int64.Type},
35 {"total_amount", type number},
36 {"order_date", type date}
37 })
38in
39 #"Changed Type"
40"""
41Learn about Looker LookML Configuration.
1# Looker connection and model definition
2
3# Connection configuration (connections.yml)
4connection: "analytics_warehouse" {
5 type: "postgres"
6 host: "warehouse.example.com"
7 port: 5432
8 database: "analytics_warehouse"
9 username: "looker_service_account"
10 password: "\${LOOKER_DB_PASSWORD}"
11
12 # Connection pooling
13 max_connections: 20
14 connection_timeout: 60
15
16 # Performance
17 pool_timeout: 30
18 sql_runner_precache_tables: yes
19}
20
21# Model definition (sales_analytics.model.lkml)
22connection: "analytics_warehouse"
23
24# Define explores (how users navigate data)
25explore: orders {
26 label: "Sales Orders"
27
28 join: customers {
29 type: left_outer
30 sql_on: \${orders.customer_key} = \${customers.customer_key} ;;
31 relationship: many_to_one
32 }
33
34 join: products {
35 type: left_outer
36 sql_on: \${orders.product_key} = \${products.product_key} ;;
37 relationship: many_to_one
38 }
39
40 # Access control
41 access_filter: {
42 field: customers.region
43 user_attribute: allowed_regions
44 }
45}
46
47# View definition (orders.view.lkml)
48view: orders {
49 sql_table_name: public.fact_orders ;;
50
51 # Dimensions
52 dimension: order_id {
53 type: number
54 primary_key: yes
55 sql: \${TABLE}.order_id ;;
56 }
57
58 dimension_group: order_date {
59 type: time
60 timeframes: [date, week, month, quarter, year]
61 sql: \${TABLE}.order_date ;;
62 }
63
64 dimension: total_amount {
65 type: number
66 sql: \${TABLE}.total_amount ;;
67 value_format_name: usd
68 }
69
70 # Measures (aggregations)
71 measure: count {
72 type: count
73 drill_fields: [order_id, order_date_date, total_amount]
74 }
75
76 measure: total_revenue {
77 type: sum
78 sql: \${total_amount} ;;
79 value_format_name: usd
80 drill_fields: [order_date_month, total_revenue]
81 }
82
83 measure: average_order_value {
84 type: average
85 sql: \${total_amount} ;;
86 value_format_name: usd
87 }
88}
89A semantic layer provides a business-friendly abstraction over the technical data warehouse schema. It translates complex SQL and table relationships into intuitive business concepts.
1
2┌─────────────────────────────────────────────────────────────────────┐
3│ Semantic Layer Architecture │
4├─────────────────────────────────────────────────────────────────────┤
5│ │
6│ BUSINESS USER INTERFACE │
7│ "Show me monthly revenue by product category" │
8│ │ │
9│ ▼ │
10│ ┌──────────────────────────────────────────────────────────────┐ │
11│ │ SEMANTIC LAYER │ │
12│ │ │ │
13│ │ Business Metrics: │ │
14│ │ - Revenue (friendly name for sum(total_amount)) │ │
15│ │ - Order Count (count distinct orders) │ │
16│ │ - Customer Lifetime Value (complex calculation) │ │
17│ │ │ │
18│ │ Business Dimensions: │ │
19│ │ - Product Category (mapped from dim_products.category) │ │
20│ │ - Customer Segment (mapped from dim_customers.segment) │ │
21│ │ - Order Month (derived from fact_orders.order_date) │ │
22│ │ │ │
23│ │ Relationships: │ │
24│ │ - Orders → Customers (many-to-one) │ │
25│ │ - Orders → Products (many-to-one) │ │
26│ │ - Orders → Time (many-to-one) │ │
27│ │ │ │
28│ └────────────────────────┬─────────────────────────────────────┘ │
29│ │ │
30│ "Translates to SQL" ▼ │
31│ ┌──────────────────────────────────────────────────────────────┐ │
32│ │ SELECT │ │
33│ │ DATE_TRUNC('month', o.order_date) as order_month, │ │
34│ │ p.category as product_category, │ │
35│ │ SUM(o.total_amount) as revenue │ │
36│ │ FROM fact_orders o │ │
37│ │ JOIN dim_products p ON o.product_key = p.product_key │ │
38│ │ GROUP BY 1, 2 │ │
39│ │ ORDER BY 1, 2 │ │
40│ └──────────────────────────────────────────────────────────────┘ │
41│ │ │
42│ ▼ │
43│ ┌──────────────────────────────────────────────────────────────┐ │
44│ │ DATA WAREHOUSE TABLES │ │
45│ │ fact_orders, dim_products, dim_customers, dim_time │ │
46│ └──────────────────────────────────────────────────────────────┘ │
47│ │
48└─────────────────────────────────────────────────────────────────────┘
49Learn about dbt Metrics for Semantic Layer.
1# dbt metrics configuration (schema.yml)
2# dbt metrics provide a centralized definition of business metrics
3
4version: 2
5
6models:
7 - name: fact_orders
8 description: "Order transactions fact table"
9
10metrics:
11 # Revenue metric
12 - name: total_revenue
13 label: "Total Revenue"
14 model: ref('fact_orders')
15 description: "Sum of all order amounts"
16
17 calculation_method: sum
18 expression: total_amount
19
20 timestamp: order_date
21 time_grains: [day, week, month, quarter, year]
22
23 dimensions:
24 - customer_segment
25 - product_category
26 - region
27
28 filters:
29 - field: order_status
30 operator: '='
31 value: "'completed'"
32
33 meta:
34 team: analytics
35 business_owner: sales_team
36
37 # Average Order Value
38 - name: average_order_value
39 label: "Average Order Value"
40 model: ref('fact_orders')
41 description: "Average value per order"
42
43 calculation_method: average
44 expression: total_amount
45
46 timestamp: order_date
47 time_grains: [day, week, month, quarter, year]
48
49 dimensions:
50 - customer_segment
51 - product_category
52
53 # Customer Count
54 - name: active_customers
55 label: "Active Customers"
56 model: ref('fact_orders')
57 description: "Count of unique customers with orders"
58
59 calculation_method: count_distinct
60 expression: customer_id
61
62 timestamp: order_date
63 time_grains: [day, week, month, quarter, year]
64
65 filters:
66 - field: order_status
67 operator: '='
68 value: "'completed'"
69
70 # Complex derived metric
71 - name: customer_lifetime_value
72 label: "Customer Lifetime Value (CLV)"
73 model: ref('customer_metrics')
74 description: "Predicted lifetime value of customer"
75
76 calculation_method: derived
77 expression: "
78 (total_revenue / NULLIF(number_of_orders, 0)) *
79 (365.0 / NULLIF(days_since_first_order, 0)) *
80 expected_lifetime_days
81 "
82
83 timestamp: calculation_date
84 time_grains: [month, quarter, year]
85
86 dimensions:
87 - customer_segment
88 - cohort_month
89Learn about Cube.js Semantic Layer.
1// Cube.js data schema (Orders.js)
2// Provides API-first semantic layer for BI tools
3
4cube(\`Orders\`, {
5 sql: \`SELECT * FROM fact_orders\`,
6
7 // Define joins
8 joins: {
9 Customers: {
10 sql: \`\${CUBE}.customer_key = \${Customers}.customer_key\`,
11 relationship: \`belongsTo\`
12 },
13
14 Products: {
15 sql: \`\${CUBE}.product_key = \${Products}.product_key\`,
16 relationship: \`belongsTo\`
17 }
18 },
19
20 // Dimensions (attributes for grouping)
21 dimensions: {
22 id: {
23 sql: \`order_id\`,
24 type: \`number\`,
25 primaryKey: true
26 },
27
28 orderDate: {
29 sql: \`order_date\`,
30 type: \`time\`
31 },
32
33 status: {
34 sql: \`order_status\`,
35 type: \`string\`
36 },
37
38 // Derived dimension
39 isLargeOrder: {
40 type: \`boolean\`,
41 case: {
42 when: [{ sql: \`\${CUBE}.total_amount > 1000\`, label: { sql: \`true\` } }],
43 else: { label: { sql: \`false\` } }
44 }
45 }
46 },
47
48 // Measures (aggregations)
49 measures: {
50 count: {
51 type: \`count\`,
52 drillMembers: [id, orderDate, Customers.name]
53 },
54
55 totalRevenue: {
56 sql: \`total_amount\`,
57 type: \`sum\`,
58 format: \`currency\`,
59 description: "Total revenue from all orders"
60 },
61
62 averageOrderValue: {
63 sql: \`total_amount\`,
64 type: \`avg\`,
65 format: \`currency\`,
66 description: "Average order value"
67 },
68
69 // Running total
70 runningRevenue: {
71 sql: \`total_amount\`,
72 type: \`runningTotal\`
73 },
74
75 // Complex calculated measure
76 revenueGrowth: {
77 sql: \`
78 (SUM(\${CUBE}.total_amount) -
79 SUM(\${CUBE}.total_amount) OVER (
80 ORDER BY \${CUBE}.order_date
81 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
82 )) /
83 NULLIF(SUM(\${CUBE}.total_amount) OVER (
84 ORDER BY \${CUBE}.order_date
85 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
86 ), 0) * 100
87 \`,
88 type: \`number\`,
89 format: \`percent\`
90 }
91 },
92
93 // Pre-aggregations for performance
94 preAggregations: {
95 main: {
96 type: \`originalSql\`,
97 refreshKey: {
98 every: \`1 hour\`
99 }
100 },
101
102 monthlyRevenue: {
103 type: \`rollup\`,
104 measureReferences: [totalRevenue, count],
105 dimensionReferences: [orderDate],
106 timeDimension: orderDate,
107 granularity: \`month\`,
108 partitionGranularity: \`month\`,
109 refreshKey: {
110 every: \`1 day\`
111 }
112 }
113 },
114
115 // Access control
116 security: {
117 public: false,
118 contextToAppId: (\`\${SECURITY_CONTEXT.email}\`),
119 contextToOrgId: (\`\${SECURITY_CONTEXT.organization_id}\`)
120 }
121});
122
123
124// Query the semantic layer via API
125// GET /cubejs-api/v1/load?query={...}
126const query = {
127 measures: ['Orders.totalRevenue', 'Orders.count'],
128 timeDimensions: [{
129 dimension: 'Orders.orderDate',
130 granularity: 'month',
131 dateRange: ['2023-01-01', '2023-12-31']
132 }],
133 dimensions: ['Products.category'],
134 order: {
135 'Orders.totalRevenue': 'desc'
136 },
137 limit: 10
138};
139Learn about BI Query Performance Optimization.
Materialized Views
: Pre-compute common aggregations
Aggregate Tables
: Create summary tables at different granularities
Partitioning
: Partition large tables by date or other dimensions
: Create indexes on frequently filtered columns
Query Caching
: Cache results of expensive queries
Incremental Refresh
: Only update changed data in BI tool caches
1-- Create materialized view for common dashboard query
2CREATE MATERIALIZED VIEW mv_monthly_sales_summary AS
3SELECT
4 DATE_TRUNC('month', o.order_date) AS order_month,
5 p.category AS product_category,
6 c.customer_segment,
7 COUNT(DISTINCT o.order_id) AS order_count,
8 COUNT(DISTINCT o.customer_id) AS customer_count,
9 SUM(o.total_amount) AS total_revenue,
10 AVG(o.total_amount) AS avg_order_value,
11 SUM(o.quantity) AS units_sold
12FROM fact_orders o
13JOIN dim_products p ON o.product_key = p.product_key
14JOIN dim_customers c ON o.customer_key = c.customer_key
15WHERE o.order_status = 'completed'
16GROUP BY 1, 2, 3;
17
18-- Create index for faster queries
19CREATE INDEX idx_mv_monthly_sales_month_category
20ON mv_monthly_sales_summary(order_month, product_category);
21
22-- Refresh strategy (can be scheduled)
23REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales_summary;
24
25
26-- Create aggregate tables at different granularities
27-- Daily aggregates
28CREATE TABLE agg_daily_sales AS
29SELECT
30 o.order_date,
31 p.product_key,
32 p.category,
33 c.customer_segment,
34 SUM(o.total_amount) AS daily_revenue,
35 COUNT(*) AS daily_orders
36FROM fact_orders o
37JOIN dim_products p ON o.product_key = p.product_key
38JOIN dim_customers c ON o.customer_key = c.customer_key
39GROUP BY 1, 2, 3, 4;
40
41-- Monthly aggregates (much smaller table)
42CREATE TABLE agg_monthly_sales AS
43SELECT
44 DATE_TRUNC('month', order_date) AS order_month,
45 product_key,
46 category,
47 customer_segment,
48 SUM(daily_revenue) AS monthly_revenue,
49 SUM(daily_orders) AS monthly_orders
50FROM agg_daily_sales
51GROUP BY 1, 2, 3, 4;
52
53
54-- Incremental refresh logic
55CREATE OR REPLACE FUNCTION refresh_daily_aggregates(
56 start_date DATE DEFAULT CURRENT_DATE - 1,
57 end_date DATE DEFAULT CURRENT_DATE
58)
59RETURNS void AS $$
60BEGIN
61 -- Delete existing data for the date range
62 DELETE FROM agg_daily_sales
63 WHERE order_date BETWEEN start_date AND end_date;
64
65 -- Insert updated data
66 INSERT INTO agg_daily_sales
67 SELECT
68 o.order_date,
69 p.product_key,
70 p.category,
71 c.customer_segment,
72 SUM(o.total_amount) AS daily_revenue,
73 COUNT(*) AS daily_orders
74 FROM fact_orders o
75 JOIN dim_products p ON o.product_key = p.product_key
76 JOIN dim_customers c ON o.customer_key = c.customer_key
77 WHERE o.order_date BETWEEN start_date AND end_date
78 GROUP BY 1, 2, 3, 4;
79
80 RAISE NOTICE 'Refreshed daily aggregates from % to %', start_date, end_date;
81END;
82$$ LANGUAGE plpgsql;
83Learn about Query Performance Monitoring.
1-- Monitor slow BI queries in PostgreSQL
2CREATE TABLE bi_query_log (
3 log_id SERIAL PRIMARY KEY,
4 query_start TIMESTAMP,
5 query_duration INTERVAL,
6 user_name TEXT,
7 application_name TEXT,
8 query_text TEXT,
9 rows_returned BIGINT
10);
11
12-- Log slow queries (via pg_stat_statements extension)
13SELECT
14 query,
15 calls,
16 total_exec_time / 1000 AS total_time_seconds,
17 mean_exec_time / 1000 AS avg_time_seconds,
18 stddev_exec_time / 1000 AS stddev_seconds,
19 rows
20FROM pg_stat_statements
21WHERE query LIKE '%fact_orders%'
22 OR query LIKE '%dim_%'
23ORDER BY total_exec_time DESC
24LIMIT 20;
25
26
27-- Identify queries that need optimization
28WITH slow_queries AS (
29 SELECT
30 substring(query from 1 for 100) AS short_query,
31 round(total_exec_time::numeric, 2) AS total_time,
32 round(mean_exec_time::numeric, 2) AS avg_time,
33 calls
34 FROM pg_stat_statements
35 WHERE mean_exec_time > 1000 -- Queries averaging > 1 second
36)
37SELECT * FROM slow_queries
38ORDER BY total_time DESC;
39Learn about Dashboard Design Best Practices.
Know Your Audience
: Design for executives vs analysts vs operations
Start with Questions
: What decisions will this dashboard inform?
Less is More
: Focus on key metrics, avoid clutter
Visual Hierarchy
: Most important metrics at top-left
Use Appropriate Charts
: Match visualization to data type
Consistent Color Scheme
: Use color purposefully
Interactivity
: Enable drill-downs and filters
Performance
: Dashboards should load in < 5 seconds
1
2┌─────────────────────────────────────────────────────────────────────┐
3│ Executive Dashboard Layout │
4├─────────────────────────────────────────────────────────────────────┤
5│ │
6│ ┌──────────────────────────────────────────────────────────────┐ │
7│ │ FILTERS & DATE RANGE SELECTOR │ │
8│ │ [Last 30 Days ▼] [All Regions ▼] [All Products ▼] │ │
9│ └──────────────────────────────────────────────────────────────┘ │
10│ │
11│ ┌────────────────┐ ┌────────────────┐ ┌────────────────┐ │
12│ │ KEY METRIC 1 │ │ KEY METRIC 2 │ │ KEY METRIC 3 │ │
13│ │ │ │ │ │ │ │
14│ │ $2.5M Revenue │ │ 15K Orders │ │ $167 AOV │ │
15│ │ ▲ +15% vs LM │ │ ▲ +8% vs LM │ │ ▼ -3% vs LM │ │
16│ └────────────────┘ └────────────────┘ └────────────────┘ │
17│ │
18│ ┌──────────────────────────────────────────────────────────────┐ │
19│ │ REVENUE TREND (Line Chart) │ │
20│ │ Shows: Monthly revenue for last 12 months │ │
21│ │ [═══════════════════════════════════════] │ │
22│ └──────────────────────────────────────────────────────────────┘ │
23│ │
24│ ┌─────────────────────────────┐ ┌──────────────────────────────┐ │
25│ │ TOP PRODUCTS (Bar Chart) │ │ CUSTOMER SEGMENTS (Pie) │ │
26│ │ Shows: Top 10 by revenue │ │ Shows: Revenue by segment │ │
27│ └─────────────────────────────┘ └──────────────────────────────┘ │
28│ │
29│ ┌──────────────────────────────────────────────────────────────┐ │
30│ │ GEOGRAPHIC PERFORMANCE (Map) │ │
31│ │ Shows: Revenue by region/country │ │
32│ └──────────────────────────────────────────────────────────────┘ │
33│ │
34└─────────────────────────────────────────────────────────────────────┘
35
36Key Design Elements:
37- Filters at top for easy access
38- Large KPI cards with trend indicators
39- Main trend chart prominently displayed
40- Supporting visualizations below
41- Appropriate chart types for data
42- Clean, uncluttered layout
43Learn about Chart Selection Guide.
1
2┌─────────────────────────────────────────────────────────────────────┐
3│ Chart Type Selection Guide │
4├─────────────────────────────────────────────────────────────────────┤
5│ │
6│ DATA TYPE CHART TYPE USE CASE │
7│ ═══════════════════════════════════════════════════════════════ │
8│ │
9│ Time Series Data: │
10│ - Line Chart Trend over time │
11│ - Area Chart Cumulative values over time │
12│ - Dual-Axis Line Compare two metrics │
13│ │
14│ Comparisons: │
15│ - Bar Chart Compare categories │
16│ - Horizontal Bar Long category names │
17│ - Grouped Bar Compare subcategories │
18│ - Stacked Bar Show composition │
19│ │
20│ Part-to-Whole: │
21│ - Pie Chart Simple composition (< 5 categories) │
22│ - Donut Chart Composition with center metric │
23│ - Treemap Hierarchical composition │
24│ - Stacked Area Composition over time │
25│ │
26│ Distribution: │
27│ - Histogram Frequency distribution │
28│ - Box Plot Statistical distribution │
29│ - Violin Plot Distribution density │
30│ │
31│ Correlation: │
32│ - Scatter Plot Relationship between variables │
33│ - Bubble Chart 3+ dimensional relationships │
34│ - Heat Map Correlation matrix │
35│ │
36│ Geographic: │
37│ - Choropleth Map Regional metrics │
38│ - Symbol Map Point locations with values │
39│ - Flow Map Movement between locations │
40│ │
41│ KPIs & Metrics: │
42│ - Scorecard Single key metric │
43│ - Gauge Progress toward goal │
44│ - Bullet Chart Performance vs target │
45│ │
46└─────────────────────────────────────────────────────────────────────┘
47
48Avoid:
49❌ 3D charts (distort perception)
50❌ Too many colors (confusing)
51❌ Pie charts with > 5 slices
52❌ Dual y-axes with different scales (misleading)
53❌ Tables when visualizations would be better
54Learn about Data Access Control and Security.
Learn about Row-Level Security (RLS).
1-- PostgreSQL Row-Level Security for BI tools
2-- Ensure users only see data they're authorized to access
3
4-- Enable RLS on the table
5ALTER TABLE fact_orders ENABLE ROW LEVEL SECURITY;
6
7-- Create policy for regional managers
8CREATE POLICY regional_manager_policy ON fact_orders
9FOR SELECT
10TO bi_user_role
11USING (
12 region IN (
13 SELECT allowed_region
14 FROM user_permissions
15 WHERE username = current_user
16 )
17);
18
19-- Create policy for sales reps (see only their customers)
20CREATE POLICY sales_rep_policy ON fact_orders
21FOR SELECT
22TO sales_rep_role
23USING (
24 sales_rep_id IN (
25 SELECT employee_id
26 FROM employees
27 WHERE username = current_user
28 )
29);
30
31-- Create policy for executives (see everything)
32CREATE POLICY executive_policy ON fact_orders
33FOR ALL
34TO executive_role
35USING (true);
36
37
38-- Implement RLS via application context
39-- Set session variable with user's permissions
40CREATE OR REPLACE FUNCTION set_user_context(
41 p_username TEXT,
42 p_regions TEXT[]
43)
44RETURNS void AS $$
45BEGIN
46 -- Store user's allowed regions in session
47 PERFORM set_config('app.user_regions', array_to_string(p_regions, ','), false);
48END;
49$$ LANGUAGE plpgsql;
50
51-- Use in RLS policy
52CREATE POLICY context_based_policy ON fact_orders
53FOR SELECT
54USING (
55 region = ANY(string_to_array(current_setting('app.user_regions', true), ','))
56);
57
58
59-- Tableau implementation: Use USERNAME() function
60-- Power BI implementation: Use USERNAME() in DAX
61-- Looker implementation: Use access_filter in explores
62Learn about Column-Level Security.
1-- Hide sensitive columns from certain users
2-- Create views that exclude sensitive data
3
4CREATE VIEW bi_orders_limited AS
5SELECT
6 order_id,
7 order_date,
8 product_id,
9 quantity,
10 -- Exclude: customer_email, customer_phone, payment_details
11 CASE
12 WHEN pg_has_role(current_user, 'sensitive_data_access', 'member')
13 THEN customer_email
14 ELSE '***@*****.com'
15 END AS customer_email,
16 total_amount
17FROM fact_orders;
18
19-- Grant access to view instead of base table
20GRANT SELECT ON bi_orders_limited TO bi_user_role;
21REVOKE SELECT ON fact_orders FROM bi_user_role;
22
23
24-- Dynamic column masking
25CREATE OR REPLACE FUNCTION mask_sensitive_data(
26 p_value TEXT,
27 p_user_role TEXT
28)
29RETURNS TEXT AS $$
30BEGIN
31 IF p_user_role IN ('admin', 'compliance_officer') THEN
32 RETURN p_value;
33 ELSE
34 RETURN REGEXP_REPLACE(p_value, '.', '*', 'g');
35 END IF;
36END;
37$$ LANGUAGE plpgsql;
38Your progress is saved in this browser, on this device only.