"Business Intelligence (BI)" is a concept initially introduced by Gartner in the 1990s. It is considered as a solution that focuses on handling various data from multiple sources within an enterprise. The key aspect of BI involves extracting valuable data, cleaning it, and then undergoing the process of Extraction, Transformation, and Loading (ETL) to merge the data into an enterprise-level data warehouse. The data is organized according to specific modeling techniques and visualized using appropriate BI tools to create various analytical reports. This transformation of data into insights supports decision-making for managers.
For businesses, implementing a robust BI solution allows for a better alignment of business requirements with real data, resulting in more rational and intelligent business decisions. Decision-makers primarily receive the direct outcome in the form of visualized analytical reports. "Data Visualization" is an important characteristic of BI, enabling the presentation of enterprise stories through interactive dashboards, charts, graphs, and maps. This facilitates a better understanding and sharing of the displayed results, providing decision-makers with additional insights that traditional reporting and distribution tools cannot achieve. While frontend visual analysis is intuitive, it is just one aspect of BI, with the underlying database construction serving as the foundation. PieCloudDB Database, as a cloud-native database product, acts as a vital link between the underlying business system data source and visual analytical reports, playing a central role in the entire BI solution.
PieCloudDB Database is compatible with the PostgreSQL protocol and supports standard database interfaces such as ODBC and JDBC. Its comprehensive support for SQL enables seamless integration with common ETL and BI tools in the industry. Additionally, support for various procedural languages provides convenience for users to engage in database-based secondary development. This article will demonstrate how to use the Python programming language and the BI tool Tableau to connect with PieCloudDB Database, creating a comprehensive BI solution for a given business intelligence scenario.
- Determining Data Sources
- Data Collection
- Data Ingestion
- Data Cleansing
- Exploratory Data Analysis
- Data Modeling
- Data Insights
Introduction to Business Intelligence
With the significant adjustments made to COVID-19 prevention and control policies earlier this year, the tourism industry has gradually started to recover. In this trend, improving the quality of attractions and enhancing commercial competitiveness have become crucial challenges for scenic area planners. In this scenario, based on our solution architecture, we can analyze the content of tourist reviews on a tourism website, fully leveraging the power of textual data to provide decision support for the planning and upgrading of the tourism industry. This article will primarily focus on the external connection methods of PieCloudDB Database involved in the solution. Subsequent articles will delve into the data analysis process specific to this scenario.
Using Python to Access PieCloudDB Database
PieCloudDB Database fully supports the PostgreSQL client protocol, allowing us to use the PostgreSQL driver to access PieCloudDB Database. Additionally, psycopg is a Python API for executing SQL statements, serving as the interface for accessing PostgreSQL databases. Therefore, we can utilize the psycopg module to connect with PieCloudDB Database. You can follow the steps below for the operation:
# Install psycopg module
pip install psycopg2
# Import psycopg module
import psycopg2
# Connect to PieCloudDB Database
conn = psycopg2.connect(database='openpie', user='username', password='token', host='127.0.0.1', port='5432')
# Parameter information：
# database – database name
# user – database username
# password – database access token
# host – database ip address
# port
# Create a pointer object
cur = conn.cursor()
# Execute SQL statement
cursor.execute(sql)
# Submit the execution result
conn.commit()
# Close the pointer and database connection
cursor.close()
conn.close()
Using Tableau to connect to PieCloudDB Database
There are many popular BI tools in the market, and considering software maturity and market acceptance, we have chosen to use Tableau for exploratory data analysis. After conducting tests, we have confirmed that we can access the PieCloudDB Database using the PostgreSQL driver in Tableau Desktop. You can refer to the following steps for the operation:
- Choose a connection type：
Select the left-hand "Connect" pane > To a Server > More... > PostgreSQL
Before performing the connection operation, users are required to download and install the necessary PostgreSQL driver from https://jdbc.postgresql.org/download/. Then, copy the relevant .jar files to the following folder (you may need to manually create the folder): ~/Library/Tableau/Drivers. For detailed steps, please refer to the Tableau official website.
- Fill in the connection information:
According to the instructions, fill in the required information to connect to the database, including the database's IP address, port number, database name, username, and password.
In the entire business intelligence solution, we leverage the rich data science tools in Python for data collection, data cleansing, and data modeling. We also utilize Tableau's powerful visualization and analysis capabilities for exploratory data analysis and creating dynamic dashboards. If you're interested in the topic of "Natural Language Processing (NLP)" and want to learn more about the detailed data modeling process and the complete workflow of a business intelligence scenario, please stay tuned for our related technical blog posts.
