SSIS, SSAS, and SSRS are three key components for SQL Server to implement business intelligence (BI).
TencentDB for SQL Server has released the business intelligence server feature, which provides a complete set of BI solutions integrating data storage, ETL, and visual analysis and supports SSIS. SSIS can be used to sustain complex business scenarios, such as merging data from heterogeneous data stores, cleansing and standardizing data, populating data warehouses and datasets, transforming data for complex business logic, supporting management features, and automating data loading. It helps meet your diversified needs in various use cases, including BI analysis, high-value data mining, and primary data management system setup.
SSIS can extract and transform data from various data sources and load the data into one or multiple targets. SSIS capabilities in Tencent Cloud currently can be used for TencentDB for SQL Server and flat files (with .txt, .csv, .xlsx, and .xls extensions).
To use the SSIS capabilities in TencentDB for SQL Server, you need to use the SSIS engine in a business intelligence server to deploy a project first.
Data is usually stored in many different data storage systems, and you often need to extract data from these sources and merge it into a single consistent dataset. This process faces various problems, including complex and different traditional systems, data formats, and preprocessing flows.
SSIS can use .NET and OLE DB access APIs to connect to TencentDB and use an ODBC driver to connect to multiple legacy databases. It can also connect to flat files and Excel files. In addition, it has some source components to extract data from different data sources and provides the data transformation feature. After transforming data into compatible formats, it can further merge data physically into one target database. Then, it can load the data into flat files and SQL Server databases.
Various data sources use different conventions and standards, and different business processing jobs need to be executed during data loading. Therefore, whether data is loaded into an online transaction processing (OLTP) or online analytic processing (OLAP) database, an Excel spreadsheet, or a file, it needs to be cleaned and standardized before it is loaded.
SSIS includes built-in transformations that you can add to packages to clean and standardize data, change the case of data, convert data to a different type or format, or create new column values based on expressions. For example, a package can concatenate first and last name columns into a single full name column, and then change the characters to uppercase. A package can also clean data by replacing the values in columns with values from a reference table, using either an exact lookup or fuzzy lookup to locate values in the reference table.
A data transformation process requires built-in logic to respond dynamically to the data it accesses and processes. The data may need to be summarized, converted, and distributed based on data values. The process may even need to reject data based on an assessment of column values. SSIS offers many types of relevant jobs:
SSIS provides components to automate management, such as copying TencentDB for SQL Server databases and their contained objects, copying TencentDB for SQL Server objects, loading data, and setting the scheduling cycle and frequency of SSIS tasks.
SSIS aggregates discrete and partially structured data in an enterprise at different standards by cleansing and processing the data. This helps tap into the value of data and form an enterprise-level unified database, which serves as a high-quality data source for enterprise decision making.
Legacy business systems may have various problems such as complex and different data sources, formats, and preprocessing flows required for merging. SSIS can extract, transform, load, and merge data from multiple storage systems into one target database for unified primary business database setup. This not only facilitates internal data maintenance and management but also reduces the storage and maintenance costs otherwise incurred by many different databases.
SSIS provides a complete set of BI analysis solutions ranging from data storage and ETL to visual analysis. With SSIS, you can perform such operations on different data sources at one stop and then easily implement real-time self-service visual data analysis throughout the entire linkage. SSIS also helps you set up an enterprise data middleend to guide refined data-driven business operations.
SSIS greatly improves your efficiency, accuracy and system performance of data collection and cleansing while simplifying the entire data aggregation and analysis process. You can configure SSIS to automatically schedule data ETL jobs, which facilitates data integration with your internal business systems.
act1in the console, the account name displayed in the list will be
SSISDBdatabase; otherwise, SSIS may not run properly.
Prepare a built SSIS project file with the
1. Purchase a business intelligence server.
TencentDB for SQL Server uses SSIS capabilities, which require project deployment through the SSIS engine in a business intelligence server. If you are using SSIS for the first time, you need to purchase a business intelligence server. If the source and target TencentDB for SQL Server instances in your SSIS project already have a business intelligence server in the same region, skip this step and proceed to step 2.
2. Create a Windows authentication account.
You need to create a Windows authentication account for the business intelligence server for login and SSIS project deployment.
Accounts created on the business intelligence server all have Windows authentication permissions. Business intelligence servers can use only this type of accounts, and account permissions cannot be modified.
3. Add a flat file.
Before deploying an SSIS project, you need to check whether the project involves flat files, and if so, you need to add the flat files to the business intelligence server first. If the source and target instances in the project don't involve flat files, skip this step and proceed to step 4.
4. Interconnect the source and target instances and business intelligence server.
Before deploying an SSIS project, you need to interconnect the source and target database instances and business intelligence server involved in the project. The interworking group management feature is used to sustain interconnection between instances. Therefore, in the same account and region, all database instances and the business intelligence server in the SSIS project need to be added to the same interworking group, and the interworking IP for business intelligence services needs to be enabled for each of them before they can access each other.
5. Deploy an SSIS project.
Before deploying an SSIS project, you need to connect to the business intelligence server as follows:
5.1 Create a Windows authentication account with the same account name and password as that on the business intelligence server in a Windows CVM instance.
5.2 Use the Windows authentication account created in step 5.1 to log in to the Windows CVM instance.
5.3 Use the Windows authentication account to log in to the business intelligence server.
5.4 Deploy an SSIS project.
5.5 Configure the SSIS service, including connection configurations for flat files and the source and target TencentDB for SQL Server instances.
5.6 Run the SSIS service and execute the package.
5.7 Configure an agent job by creating job steps and schedule.
|Feature Page||Feature||Operation Guide and Directions|
|Instance List||Purchasing business intelligence server||For detailed directions on how to create/purchase a business intelligence server, see Purchasing Business Intelligence Server.|
|Renaming instance||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Renaming Instance.|
|Restarting instance||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Restarting Instance.|
|Terminating instance||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Terminating Instance.|
|Recycle bin||After a business intelligence server is terminated or deleted by mistake, the operations that can be performed in the recycle bin are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Recycle Bin.|
|Editing tag||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Setting Instance Tag.|
|Instance Management||Setting instance remarks||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Setting Instance Remarks.|
|Changing network||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Changing Network (from VPC to VPC).|
|Modifying project||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Setting Instance Project.|
|Setting instance maintenance information||The operation steps are the same as those for a TencentDB for SQL Server instance. For detailed directions, see Setting Instance Maintenance Information.|
|View monitoring chart||The monitoring metrics of a business intelligence server and a TencentDB for SQL Server instance are not completely the same. Specific monitoring metrics are as displayed on the actual monitoring page. For detailed directions, see Viewing Monitoring Charts.|
|Configuring security group||A business intelligence server also needs to be configured with a security group. For detailed directions, see Configuring Security Group.|
|Managing account||For detailed directions on how to create and delete accounts, see Creating Windows Authentication Account.|
|Managing SSIS||For detailed directions on how to manage SSIS and add flat files, see Adding Flat File.|
|Interworking Group Management||Interconnecting source/target instances and business intelligence server||For detailed directions on how to enable the interworking IP for business intelligence services and add instances to an interworking group, see Interconnecting Source/Target Instances and Business Intelligence Server.|