Business Intelligence solution using SQL Server 2005

For computing, or more generally for business, one word recurs: information. This is essential in business today, as is knowledge of information that enables decision making. I am here to get more information on Business Intelligence solution using SQL Server 2005. I am sure that you members will provide detailed notes about it. I know that it is a key role of information system management, operation and protection thereof. At this level, information is data. The role of business intelligence is to transfer, modify, consolidate and analyze data to make them more accessible, to facilitate decision-making. Please provide some more notes. :notworthy

For starters, the concept of Business Intelligence will be detailed in order to highlight the importance of this process. In subsequent chapters, an example of deploying such a solution will be presented with Microsoft SQL Server 2005. Until the introduction of business intelligence solutions, databases were backup solutions information whose main asset was the saving of space: a database server can hold the equivalent volume of information from numerous files. Data access was possible, but restricted to computer experts. Indeed, it was relational databases, and it was essential to know the structure of the database to figure it out, what, where multiple databases, each containing over a hundred tables, can be really difficult. But the market moves faster and faster, it became necessary to find a solution to making quick decisions, ie a solution that allows simplified use of data, information.

This solution will be to divide the system's operational decisions. More concretely, the information system of the enterprise will consist of two poles:

• A first pole, operational, representing the production side of the business (sales, orders, inventory management, accounting data, production ,...) and whose main role will be in computer data entry.
• A second pole, decision, which is to repatriate the captured data at the operational level, to analyze and process them to make them accessible to policy makers. It is within this second cluster that Business Intelligence is involved. Data access is done through reports, indicators, graphs, ... which provides easy and near real-time situation of the company, as well as estimates of the future.

Datamining, Datawarehouse, Business Intelligence Datamart ... there are many terms used when working on a Business Intelligence project. Here are some definitions of terms to know before starting such a project.

• Business Intelligence: Intelligence: solution to consolidate data into a data warehouse and the retreat in order to facilitate decision-making.
• Data warehouse: data warehouse suitable for data processing in order to provide decision support. It is therefore a concept specific to intelligence and that includes all corporate data in the form of Datamarts is a set of Datamarts.
• Datamart: data store specializes in a business process. This is an excerpt from a data warehouse. Directly used by reporting services, it is between the data warehouse and reporting, data are ready to be used for decision making.
• Integration Services: SQL Server 2000 tool & 2005 for the repatriation of data. It involves using the BI functionality of Visual Studio to create a package which will run automatically retrieve the information in the data warehouse, which facilitates the updating of data.
• Analysis Services: tool for SQL Server 2000 & 2005 for data analysis and processing them in order to have a dimensional model, more suited to data processing that the relational model in the case of Intelligence.
• Reporting Services: tool for SQL Server 2000 & 2005 for the formatting of data in graphs and reports, accessible via an intranet, email, ...
• OLE DB: Microsoft APIs to use just the databases of different DBMS (Oracle, SQL Server ,...) to replace ODBC.
• ODBC API used for accessing databases. Providing limited opportunities, it is better to prefer OLE DB in the development of Business Intelligence with Visual Studio. Please note that access to Informix databases can be done through ODBC.

SQL Server relational database widely used as such, has since the 2000 version of Business Intelligence tools are: integration services, analysis services and reporting services. This list is not exhaustive, but these are the three main BI tools available from the standard version. The solution of the American publisher is based on Microsoft server technologies (Windows Server and Internet Application services) as well as the solution to Visual Studio. For deploying a business intelligence solution using SQL Server 2005, you must install Internet Information Services to the extent you want to allow access to reports from the Internet or an intranet. The example that illustrates this article takes on a Server in Windows Server 2003 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition and IIS 6.0.

In order to have daily monitoring of activity of enterprise: the Fire Brigade of Paris, a business intelligence solution should be established to facilitate access to operational statistics. For this, a relational database which currently serves history will be used. This basis, the former does not respect the traditional rules of a relational database (no primary key, ...), which is not without its problems. As can be seen, the base is far from optimized (few primary key redundant information). In fact, now that the structure of relational type of the project is presented, it is possible to realize the problems that may arise from such a basis. These problems prevent easy access to data in order to make a decision.

The problems that will arise are:

• No simple join possible between intervention and order to the extent that we are working on a several years old and that "numint" resets every year. It will be necessary to use the dates, but the same references are not available in these two tables ("datord" differs from "datapp).
• Road management is very complex, with duplication of information. It will be necessary to completely overhaul the structure of the address table by breaking into three cases: the case of known addresses, if institutions listed and reported the case of institutions (the latter two can be treated the same way.
• Finally, we must take into account possible developments: change of street name ... to create a history of such interventions.

Analysis Services is a tool that will be used for the optimization of data for decision making. It also allows the implementation of action and key performance indicator (KPI). Since a model resembling the dimensional model has been implemented in integration services, the use of Analysis Services is simple, it will be fairly easy to create a cube. Decision making requires indicators and measures. This information should be organized along axes of analysis. These axes correspond to the dimensions. The cube is the geographic organization of these data with a fact table (original) and three tables of dimensions (axes). It is therefore a special case, however, the term is commonly used when cube is an array (a fact table and two dimensions) or in the case of a hyper cube (a fact table and n dimensions with n> 3). The creation of the cube in Analysis Services will improve the performance of analysis (pre calculus) by implementing a multidimensional database from the database in a star (or snowflake) after integration services.

As for integration services, it must use Business Intelligence Development Studio and select "New Project Analysis services. Then, before setting up the cube, we must establish the source of the data and the data source view.

1. To set up a data source, you must right click on data source, then "New data source". A wizard starts.
2. Two cases arise, either the connection is already present and just select it, or it does not exist and we must create it. To do this, simply specify the server and used to select the database used.
3. After selecting the data source must specify the account to use for the connection.
4. As for the data source, you must right click on data source view to select "New Data Source View." A wizard starts.
5. Select the data source you want to use.
6. Specify, depending on your source database, how should be identified FKs. Select the tables needed for the project.
7. It only remains for you only to name the view. The latter is displayed. Check that all links between the fact table and dimension tables (primary key / foreign) exists. If this is not the case, you can complete the representation. You get a representation of the project.

In case the deployment server is a remote server, you must configure it. To do this, simply right click on the project and select properties. Then, choose deployment. You have to specify the server and the table (it must first be created on the SQL server). After selecting "New Cube" with a right click on cubes, one must choose: "Build the cube using a data source" and "automatic generation". Then you must select the source data. We must then verify that the fact table and dimension tables are properly selected. Then you can build the cube (do not change the other options). It remains only to treat the project (right click on the project, process). There are many other tools that may be the subject of another article, such as KPIs, actions, ... However, like Integration Services, we can not address all the options and other features.