Data Visualization & Analytics with Alibaba Cloud QuickBI: From Setup to Optimization

This article guides you through the implementation of big data visualization solutions using QuickBI in conjunction with other Alibaba Cloud services.

By Farah Abdou

Introduction

Alibaba Cloud QuickBI is one of the most powerful business intelligence platforms in the big data and cloud computing era, which can seamlessly integrate with the comprehensive suite of services provided by Alibaba Cloud. This manual will take you through hands-on implementation of big data visualization solutions using QuickBI along with other Alibaba Cloud services.

Prerequisites

Before you begin, ensure that you have the following:

An Alibaba Cloud account with appropriate privileges
QuickBI Enterprise Edition, recommended for big data scenarios
Access to the following Alibaba Cloud services:
- MaxCompute for data warehousing
- DataWorks, for data integration
- Object Storage Service, OSS for storing raw data
- AnalyticDB or ApsaraDB RDS for real-time analytics

Setting Up Your Alibaba Cloud Environment

1. Resource Group Creation

Resource Management First step to resource management is to create the resource groups:

Log in Alibaba Cloud Console
Go to Resource Management
Create a new resource group for your BI project:

   Resource Management → Resource Groups → Create Resource Group
   Name: quickbi-project
   Display Name: QuickBI Project Resources

2. Configure MaxCompute Project

MaxCompute will be used as your data warehouse:

1. Create a MaxCompute project:

   MaxCompute Console → Project List → Create Project
   Project Name: quickbi_data_warehouse
   Region: Choose your region
   Quota Group: Standard

2. Configure security settings:

   -- Create a role for QuickBI access
   CREATE ROLE quickbi_reader;
   
   -- Grant permissions
   GRANT READ ON PROJECT quickbi_data_warehouse TO ROLE quickbi_reader;

3. DataWorks Configuration for Data Integration

Configure the DataWorks workspace:

Log in to DataWorks Console
Create a workspace:

   DataWorks → Workspaces → Create Workspace
   Mode: Standard Mode (Development/Production)

Data Sources Connection

1. MaxCompute Connection

QuickBI Console → Data Sources → Add Source
Type: MaxCompute
Project Name: quickbi_data_warehouse
ID: Your AccessKey ID
Key: Your AccessKey Secret

2. AnalyticDB Connection (for real-time data)

QuickBI Console → Data Sources → Add Source
Type: AnalyticDB for MySQL
Host: your-analyticdb-endpoint.aliyuncs.com
Port: 3306
Database: bi_realtime_data
Username: your_username
Password: your_password

Creating Datasets

1. MaxCompute Dataset

Create a dataset from your MaxCompute table:

Data Sources → MaxCompute → quickbi_data_warehouse
Select Table → Create Dataset

Example MaxCompute SQL for data preparation:

-- Create an optimized table for QuickBI
CREATE TABLE IF NOT EXISTS sales_analysis
(
date_key     DATETIME,
    product_id   STRING,
    region_id    STRING,
    sales_amount DECIMAL(18,2),
    quantity     INT,
    profit       DECIMAL(18,2)
)
PARTITIONED BY (dt STRING);

-- Optimize for QuickBI querying
INSERT OVERWRITE TABLE sales_analysis PARTITION (dt='${bizdate}')
SELECT 
    transaction_date,
product_id,
    region_id,
    sales_amount,
    quantity,
    (sales_amount - cost_amount) as profit
FROM raw_sales_data
WHERE transaction_date = '${bizdate}';

Viz

1. General Dashboard Development

Create a sales performance analysis dashboard:

1. Enter:

   QuickBI → Workspace → Create → Dashboard

2. Add Alibaba Cloud region-specific map:

   {
"chartType": "AlibabaCloudRegionMap",
     "dataSource": "sales_analysis",
     "dimensions": ["region_id"],
     "measures": ["sales_amount", "profit"]
   }

2. Real-time Monitoring Dashboard

Configure in real time, sales monitoring:

1. Configure AnalyticDB connection

2. Create real-time dataset:

-- AnalyticDB table structure
CREATE TABLE realtime_sales (
    transaction_id VARCHAR(32),
timestamp DATETIME,
       amount DECIMAL(10,2),
       product_id VARCHAR(32),
       store_id VARCHAR(32)
   );

3. Configure auto refresh:

   Dashboard Settings → Auto Refresh → Interval: 30 seconds

3. Cross-Service Dashboard

Blend data from multiple Alibaba Cloud services:

MaxCompute: Historical Data
AnalyticDB: Real-time Data
OSS: Unstructured Data

Integration with Alibaba Cloud Services

1. DataV Integration

Link QuickBI to DataV for deeper visualizations:

//DataV configuration
{
  "dataSource": {
    "type": "QuickBI",
    "endpoint": "quickbi-api.aliyuncs.com",
    "dashboard": "dashboard_id",
    "refreshInterval": 300
  }
}

2. Message Service (MNS) Alerts

Configure sending alerts by Alibaba Cloud MNS:

from aliyunsdkcore.client import AcsClient
from aliyunsdkmns.request.v20150306 import PublishMessageRequest

def send_alert(threshold_breach):
client = AcsClient(access_key_id, access_key_secret, 'region_id')
    request = PublishMessageRequest.PublishMessageRequest()
    request.set_TopicName('quickbi_alerts')
    request.set_MessageBody(f'Alert: {threshold_breach}')
    client.do_action_with_exception(request)

3. RAM Security Integration

Configure RBAC:

{
  "Version": "1",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "quickbi:Describe*",
"quickbi:Get*",
        "quickbi:List*"
      ],
      "Resource": [
        "acs:quickbi:*:*:dashboard/*",
        "acs:quickbi:*:*:workspace/*"
      ]
    }
  ]
}

Performance Optimization

1. MaxCompute Optimization

Perform the optimization of your MaxCompute tables:

CREATE TABLE sales_summary_daily
(
date_key     DATE,
    region_id    STRING,
    total_sales  DECIMAL(18,2),
    total_profit DECIMAL(18,2)
)
PARTITIONED BY (region STRING)
CLUSTERED BY (date_key) SORTED BY (date_key) INTO 32 BUCKETS;

2. AnalyticDB Query Optimization

Enable the feature of materialized views:

CREATE MATERIALIZED VIEW mv_hourly_sales 
REFRESH COMPLETE START EVERY 1 HOUR
AS
SELECT 
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') as hour,
    store_id,
SUM(amount) as total_amount,
    COUNT(*) as transaction_count
FROM realtime_sales
GROUP BY 
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00'),
    store_id;

Advanced Features

1. User-defined Metric using PAI

Integrated usage with PAI (Platform for Artificial Intelligence):

from pai import predict

def custom_forecast(historical_data):
    model = pai.load_model('sales_forecast_model')
    prediction = model.predict(historical_data)
    return prediction

2. DataWorks Workflow Automation

Building an automated refresh workflow:

"type": "DataWorks",
  "schedule": {
    "cronExpr": "0 0 * * *",
    "timezone": "Asia/Shanghai"
  },
  "tasks": [
    {
      "name": "refresh_quickbi_dataset",
      "type": "QuickBI",
      "dashboard": "dashboard_id"
    }
  ]
}

Best Practices and Security

1. Security Best Practices

Use RAM roles for service access
Use MaxCompute to enable row-level security
SSL must be enabled on all connections
Regularly run security audits using ActionTrail

2. Monitoring and Maintenance

Configure Cloud Monitor for QuickBI:

{
  "monitoring": {
    "service": "QuickBI",
    "metrics": [
      "DashboardVisits",
      "APILatency",
      "DatasetRefreshStatus"
    ],

"period": 300
  }
}

Troubleshooting Guide

Common problems and solutions:

1. Slow Performing Dashboard

Check MaxCompute partition strategy
Verify usage of the index in AnalyticDB
Review network latency between services

2. Data Refresh Issues

Check DataWorks workflow logs to validate
OSS object permissions
Review MaxCompute tunnel logs

Conclusion

With proper integration with other Alibaba Cloud services, QuickBI provides a powerful platform for big data visualization. This guide will help you unlock the full Alibaba Cloud ecosystem for your business intelligence needs.

Resources

Disclaimer: The views expressed herein are for reference only and don't necessarily represent the official views of Alibaba Cloud.