SQL Server BI Architecture

Concept Introduction:

1, Business Intelligence (INTELLIGENCE,BI), refers to the use of modern data warehousing Technology, on-Line Analytical Processing technology, data mining and data presentation technology for data analysis to achieve business value.

Data Warehouse,dw is a subject-oriented (Subject oriented), integrated (integrate), relatively stable (non-volatile), reflecting historical changes (time Variant) data collection to support management decisions.

on-Line Analytical processing (On-line analytical Processing,olap) supports complex analysis operations, focuses on decision support, and provides multidimensional conceptual views

data mining,dm refers to the non-trivial process of revealing hidden, previously unknown, potentially valuable information from a large amount of data in a database.

2. Business intelligence major vendors and products (Microsoft SQL Server only)

Microsoft's SQL Server, covering all areas of BI, is suitable for small to midsize businesses, and provides three services (Ssis:sql server integration Services[sql Server consolidation service], Ssas:sql Server Analysis Services[sql Server Analytics service], Ssrs:sql server Reporting services[sql Server Reporting Services]) and a tool (SQL Server Management Studio) to consolidate data warehousing systems, providing users with the features, tools, and capabilities they need to build typical and innovative analytic applications that enable modeling, ETL, query analysis or charting, custom KPIs (Key performance Indicator key performance indicators), set up reports and structure data mining applications and publishing functions.

Technical Introduction:

1. Basic BI Framework:

Reseller upload data, external data sources, business data-etl-> Data Warehouse (above for the data Service layer)->olap, ad hoc query (data Warehouse domain concept: refers to the user according to their own needs, flexible choice of query conditions, the system can be based on the user's choice to generate the corresponding statistical report. ), statistical reports (above for the application analysis layer), decision support, management support, business support (above for the information presentation layer).

2, ETL Technology concept: The core content of business intelligence is from many different business processing systems from the enterprise data (distributed, heterogeneous metadata), extract useful data, to clean up to ensure the accuracy of data, and then after extraction (Extraction), Conversion (transformation) and load (load), the ETL process (essentially the process of sample extraction), is integrated into an enterprise-level data warehouse, thus a global view of enterprise information, based on the use of appropriate query and analysis tools, Data mining tools, such as data Warehouse data analysis and processing, the formation of information, and even further refine the information to assist decision-making knowledge, and finally the knowledge presented to the manager, the decision-making process for managers to provide support.

3, the goal of the ETL process is to transform the data into business value, the method is to dump, monitor, analyze and display the massive data.

4. Multidimensional database (Multi dimensional DATABASE,MDD): Store data in an n-dimensional array, rather than as a relational database in the form of records. So it has a large number of sparse matrices, and people can look at the data through multidimensional views. Multidimensional database adds a time dimension, compared with relational database, its advantage is that it can provide data processing speed, accelerate reaction time and improve query efficiency.

Introduction to Microsoft BI Development:

1. Microsoft BI Installation: When installing SQL Server Select Install feature, note the "Analysis Services, Reporting services, Business Intelligence development Studio, Integration Services, where business Intelligence development Studio (bids BI development platform), has been updated in SQL Server2012 to SQL Server Data Tools. When the installation is complete, open SQL Server Data Tools and the new project will have the business intelligence template; in SQL Server Management Studio you will have the database engine, the SSIS engine, the SSAS engine, and the SSRS engine.

2. ETL Component-ssis (SSIS is the ETL tool for Ms Bi products): The new Integraton Services project can create ETL packages in the project.

SQL Server BI Architecture