FOCUSBI: SSIS implementation of DW/BI project management

Public number:FOCUSBI
Focus on more business intelligence, data warehousing, database development, crawler knowledge, and Shanghai and Shenzhen stock market data push. Questions or suggestions, please pay attention to the public message to send messages;
If you feel that FOCUSBI is helpful to you, please forward your circle of friends or like [1] at the end of the article.

Business Intelligence Tutorial PDF

Link: https://pan.baidu.com/s/1f9VdZUXztwylkOdFLbcmWw Password: 2r4v

In the Focusbi:ssis architecture, the SSIS development case, the two articles about the end of SSIS development is to be executed, but what happens after being executed, how to monitor its implementation, this is also in the BI implementation of the challenges encountered, When there are hundreds of packages we should how to manage this SSIS ETL project, although the SSIS execution error is that there is an email notification error, but it can not tell you exactly where the error, when a project has hundreds of packages how accurate positioning errors and error reasons, how to clearly know what the SQL inside these packages Statement and the time that the package executes, and so on.

DW/BI Project Management: implementation of SSIS

Here I introduce the second module of "Dw/bi Project Management" Implementation of SSIS, which can solve how to manage hundreds of packages and the status of running within the package, and so on.

First log in to the DW/BI project management system, click the SSIS execution module, which will appear in this module: SSIS execution package time, SSIS execution SQL, configuration execution plan, configure mail Notification four sub-modules, where the configuration execution plan is to choose the right sequence of deployed packages will automatically generate jobs , this feature in the case of less than the size of the package is not much significance, only if there are hundreds of packages, and to be migrated to save you time, if you want to migrate also can be made into a stored procedure once the migration succeeds, so this submodule is not described. Configure mail notification: This submodule refers to the Mail module in SQL Server. I don't think it makes much sense, and the module will be canceled later. SSIS Performance module I'll focus on the execution of the package time and the SQL two sub-modules executed. In addition, the SSIS package performs error and warning information on the right side of the home page of the DW/BI project management This sub-module is the one you log in to appear, why I want to make a login to appear this content, even if there is an email notification SSIS error or warning but after all, the message may be missed.

DW/BI project management: SSIS execution package Time

Click Module SSIS execution sub-module SSIS execution package time will appear on this page, the page has the execution date of the query, where the default is the day the package execution, as long as you open the sub-module every day to see clearly the package and data flow component run start time, end time, length of execution, the number of rows affected. See the Type field in the table the content of this field is 1 for the data flow, 0 for the package. It is clear here that each package contains multiple data streams, which is very simple for the business to do, if the complex business requires that each package contains only one data flow, there is no incremental package, so in more complex business scenarios need to consider more factors, no matter how complex this module can be managed very well; The execution Time field is the time that the package or data flow is executed, which is a very important reference factor for performance optimization. The number of rows in the field is obviously the number of rows of data that is being processed by the data stream, which is done in full-volume and full-scale extraction, with no increment, so this field makes sense for incremental comparisons.

DW/BI project management: SQL executed by SSIS

Click Module SSIS execution sub-module in SSIS execution SQL will appear below this page, the package below a data flow executes what kind of SQL statement at a glance, no matter you are executing the stored procedure will also show the stored procedure, In the BI implementation process for the project delivery needs to party A's own use may be to party A's own maintenance, so in the process of implementation as far as possible is SQL to handle the simpler business, stored procedures to handle complex business, This convenient unified management also to the technical staff do not have too advanced understanding of SSIS High-level features can be understood and maintained. The SQL fields that are executed are followed by the data that is used by the DBA for tuning the SQL Reference.

Summary

Combining the database table structure of DW/BI project management with the implementation of SSIS two modules is very convenient for BI project development and maintenance, it can save labor cost, provide development speed, and make it easier to judge the problem. It can be said that my one months of work only 10 days of dream more and more close, I need more systematic, intelligent thinking bi project stability and early prevention of error methods.

FOCUSBI: SSIS implementation of DW/BI project management