Enterprise data application tool selection
First, why choose tools
The largest difference between enterprises and institutions is that the quality level of internal personnel is different, but because many reasons cannot require all people to have a high level. Remembering a very new business in a new company, it is a computer master in the company. This also shows the lag of enterprise applications from one aspect.
It is in this environment, in order to call our first-line personnel to complete the work under conditions of its original work habits. We also further understand the height integration and openness of office automation products themselves. And all the foundations originated from ODBC, which will be carefully described below.
Second, cute ODBC, slow ODBC
ODBC (Open Database Connectivity) is a unified interface standard proposed by Microsoft's company for accessing the database. With the client / server architecture, the interconnect access between multiple databases is a prominent Issues, while ODBC has become a powerful solution. ODBC can operate numerous databases because most databases are all or partially compliant with relational database concepts, and ODBC looks at these databases. Although it supports numerous databases, this does not mean that ODBC will become complicated. ODBC is based on the structured query language (SQL). Using SQL can greatly simplify its application design interface (API), due to the advanced nature of ODBC. And there is no competition with similar standards or products, and thus becomes more and more favored by many manufacturers and users. Currently, ODBC has become an important support technology in the client / server system.
At 1994, ODBC has the first version, which has been standardized and supported by various database vendors quickly by Open Data Base Connection (open database interconnect) ODBC solved two problems at the time, one was developed in the Windows platform, and the other is to build a unified standard, as long as the developer provided by the data manufacturer supports this standard, then developers can develop by ODBC can be different Free conversion between databases. This is indeed worthy of developers.
ODBC refers to the X / OpenData Management: SQL Call-Level Interface and ISO / ICE1995 Call-Level Interface standard, all of the two standards have been fully implemented in ODBC version 3.x. So all content of this book is based on ODBC 3.0 or higher.
At the beginning, I support ODBC database only SQL Server, Access, FoxPro, these are Microsoft products, they can support ODBC, but then the Windows graphics interface has become the most ideal vector for client software, so Migrating data vendors also released drivers for ODBC shortly.
When Windows 3.x and Windows 95, ODBC does not appear as a component of the system, and must be installed separately before use. But when you have Windows 98, when you install the operating system, ODBC does not need to be installed separately because it has become part of the operating system. This has a lot of excuses for many people who refuse ODBC.
In terms of remote data access, many programmers use ODBC's way, their convenience, and simpleness is very prominent. However, the low efficiency is also a defective shortcoming of ODBC. So slow ODBC is abandoned when the efficiency is high. And we just perform a limited front desk access to the remote SQLServer database, and the amount of data accessed is not very large, but it is only proposed after the server is handled. So ODBC can be said to be a lovely.
Third, open desktop data
Call your report, it seems to be a sentence. However, we have achieved this under the intensive basic network construction today. With the in-depth of information construction, data has become the most important resources of several departments of production, finance, management. At the same time, all business resources are integrated on one platform, and the data is from the quantity and species. A large leap, new technology and management requirements, have further requested further requirements for data analysis. In this context, how can we make further use of data more, how to further improve information through data, has become an important issue. The first step in these issues is how to ensure that the data is correct. The only way to this is to use less as possible to participate in data conversion, analysis, performance, statistics, and reported synthesis.
Microsoft's Office product system is widely used in our current enterprises and personal computers because of their advantages of their face-friendly, easy to apply and operate in our application and operation. And the Excel can be said to be a sword in data statistics. Here is an example of statistics and analysis of product key components through the data perspective in Excel
The requirements of the company's application:
The factory manager produced by the supervisor requires the production of key components in the product, and the amount of moon is entered in order to estimate the production capacity.
Software selection and program selection:
EXCEL is adopted because most people in the enterprise understand their usage. The front desk is used in the form of a data perspective and perspective. As shown in Figure 5, Figure 6.
Figure 5
Figure 6
The PivotTable is an interactive worksheet for summarizing and analyzing data in existing data lists, tables, and databases. In this way, the organization, summary and analysis of data can be easily performed. In particular, the use of data perspective is further favored by management.
And this perspective is based on non-OLAP data sources, just through the ODBC to put data in the remote database, and import it to the front desk after the data of Microsoft Query. This approach has a disadvantage that the data is based on a remote server, and data updates must be performed each time.
And in this way, there is a method for reform, and it can further implement it. The process is the following two processes.
1. Automatically export data from SQL Server, format Excel to design a package as shown in Figure 7.
Figure 7
At this point, the data is imported into the local Sheet1
2. Changing the data perspective table and perspective are such data dependencies based on external data, and are changed to data based on local SHEET1.
This will automatically call the packet through the remote database, which is the data of the front desk, and the data perspective and perspective are further automatically processed.
By using the function of the scheduling package in the scheduling package in the remote SQL Server database, you can implement the dynamics of the front desk data, and the report on the desktop is now moving. There is no resolute implementation of the dynamic and openness of desktop data tools to provide a solid foundation for the dynamics and integrity of data.
The above products are only Microsoft's Office2000, and the SYSTEM Project proposed in Microsoft's new office products further emphasizes the dynamic and interconnect of desktop tools. Further put forward the idea of department-level portals and enterprise portals, further strengthening the overall and integrity of internal transmission of enterprise data. With the development of information technology and computer technology, the more and more complete products will further provide better solutions for our company's information construction.
Fourth, the best backup power - SQLServer server
This title may lead a lot of non-discusses, especially from Oracle. There are still many large databases in China, but based on the quality of cost and technicians, Microsoft's SQL Server2000 still has a large market proportion, but with the further expansion of SAP companies, Oracle has begun to actively communicate with customers. Started the foundation literacy. Its company's website has begun to engage in free 21 days. It is mainly to teach you how to use Oracle, and how to make a qualified Oracle database manager. This also expresses the Chinese market to Oracle and Microsoft two companies. However, the company's background database is currently applied to SQLServer. So the following as an example of SQL Server, introduce several aspects of SQL Server practical. I hope I can provide help with your work.
4.1 Give you a reason to love it
SQL Server As a database developed on the Windows series platform, SQL Server has been favored by many users with its ease of use. It is believed that most users who have built their own business in the Windows platform will have considerable intimacy. . Different from FoxPro, Access Small Database, SQL Server is a fully functional database management system. It includes functions that support developed engines, standard SQL languages, extended features such as replication, OLAP, analysis. And the storage procedures, triggers, etc. are also owned by large databases.
SQL Server 2000 is often the reason for the preferred
Be easy to get
Divide two heads, if your company has not purchased a database, one of the main reasons may think that it is not good, then, starting from SQL Server. After all, most of the small and medium-sized business daily data applications are built on the Windows platform. Since SQL Server is completely consistent with the Windows interface, there are many "wizards" help, it is easy to install and learn. About SQL Server information, training is available, and the current domestic MCDBA certified engineers are not a small number.
From another perspective, learning SQL Server is to master other platforms and large data, such as Oracle, Sybase, DB / 2 foundation. Because these large databases are often higher for equipment, platform, and personnel knowledge, not everyone has such conditions, and has the opportunity to contact them. But with the foundation of SQL Server, you will easily learn and use them. The practice experience in the IT industry fully proves this.
Good compatibility
Since today's Windows operating system occupies the dominant bits, select SQL Server will definitely achieve some advantages in compatibility. In addition, SQL Server 2000 has functions that can quickly develop new Internet systems in addition to scalability, reliability. In particular, it can store XML data directly, and the search results can be characterized by XML format, which facilitates the construction of the interoperability of the heterogeneous system, laid the cornerstone of the Internet. These features play an important role in the .NET strategy.
Depending on 7.0
Microsoft SQL Server 2000 is an extension of performance, reliability, quality, and ease of use on SQL Server 7.0. SQL Server 2000 contains many new features that make it a superior log platform for e-commerce, data warehousing, and online business solutions. Its enhanced features include support, integrated analysis services, and convenient database management of rich expansion markings (XML).
E-commerce
In the case of using the Microsoft SQL Server 2000 relational database engine, XML data can be stored in a relational table, and the query can return the relevant results in an XML format. In addition, XML support also simplifies backend system integration and realizes seamless data transmission across firewalls. You can also use Hypertext Transfer Protocol to access SQL Server 2000 to implement secure web connectivity for SQL Server 2000 databases and online analysis processing (OLAP) cubes without additional programming. database
Microsoft SQL Server 2000 is very obvious to increase the OLAP (online analysis processing) function, which allows many SME users to analyze some of the characteristics of the data warehouse. OLAP can perform rapid and advanced analysis work through multi-dimensional storage technology to large and complex data sets. Data mining features can reveal the tendency to hide in large quantities of data, which allows organizations or organizations to maximize
Limited value from the data. This feature can predict future trends by effectively analyzing existing data.
Enhanced online business
Microsoft SQL Server 2000 simplifies management, optimization, and enhances quickly, successful deployment of the reliability and scalability required for online business applications. Among them, features to improve reliability include log transfer, online backup, and failover clusters. Improvements in scalability include support for up to 32 CPUs and 64 GB RAMs. By automatic optimization and improved management characteristics - automatic management, the online business application, automatic memory management, and simplified failover cluster installation and management, online business applications can be quickly deployed and managed.
Conservative "agility business"
The so-called "agility business" is able to break the internal and external commercial boundaries, and quickly react to the rapidly changing environment. . Microsoft has established strategic relationships with key partners, creates solutions that can integrate with many suppliers, so corporate users do not need to make "either complete acceptance or all do not" commitments. In the process of deploying solutions, business users do not have to remove the original devices from head. Agile business allows enterprise users to make full use of existing systems, independently decided to provide the hardware and software solutions, and who are provided, telesconsive, and more.
Now that SMEs apply databases that cannot exceed SQL Server's capability, and their powerful market share of the Supreme Microsoft's operating system does not request you to choose SQLServer, and its interface is friendly, and the simplicity of operation is also difficult. Reached.
4.2 Basics of Database Applications - Table
The table is a database application basis and one of the core parts of the information software design. I remember the domestic logistics software (not mentioned here). Its background database table design is quite bad. In the same field used in the front desk, he will use it in different tables to make the database maintenance work is very cumbersome. It can be seen that its product does not fully take into account the pertural issue of the database design when designing. There is also a good understanding of the relationship between the view and the table.
Talking about the table, everyone will easily generate a lot of tables, also have a lot of tables, and the requirements for the paradigm in the database, you may haven't payable, and in the first line of applications, the paradigm has only played effect. This provides a lot of convenience for the maintenance of the system after the system. Tables established in a database, especially the basic table (Note: Basic table: means a table for the only attributes used to express the elements of each aspect of the information system, such as our supplier table, distributor table, etc.) Establish relationships and table integrity to ensure the only data unique and data unity between various tables in the database. At this point, there is a problem in the design of many information systems, so that data maintenance for the later business personnel has caused a lot of trouble. At the same time, design triggers in the table is also a very critical aspect, which is especially important in designing system receptions. In particular, insert, update, delete operations, the server can be further invoked by the trigger to automatically do some associated operations on the server.
If you mention the table, you have to talk about the DTS package. DTS is an abbreviation of Data Transform System and provides a very important data conversion tool in SQL Server. By designing the DTS package, it is possible to more efficiently convert the data conversion and automation. Its interface design is shown in Figure 8
Figure eight
This is a DTS package that imports DBF data into SQL Server and introduces historical data in SQL Server first, first deleted a DTS package. This key is stored in the DBF data file. Especially when you operate the computer instead of a server, you must figure out the path to the data stored on the server. Because the design is operated over the server, the path to the server design is considered local, not the server. So this problem must be noted, this is the opposite of this problem with the database backup. Data storage paths that the data backup is that the path is the path on the server and is not local.
After a package is designed, if you go to execute the server locally, there are two data services that there will be two ways, and will be described below in the DTS package.
4.3 Best partners - View
About the table says so much, many people must think that if there is not designed table, how do I express data and data in different tables? ? View, a best partner, a assistant. Through my experience, the view is highlighted in the data application, and its relationship with the table is like the relationship between the foundation with the building. Table is those founders and views are buildings. Asked us to see, beautiful and majestic buildings.
Talking about the role of the view, it itself is not only linked through the fields through the fields, but also packet, hierarchical calculations, and a lot of good statistics, supervision and analysis. The relationship between this table and the view will be described below by an example.
Enterprise demand:
The modern manufacturing is fierce, and the raw material market is also high. So almost all enterprises use the order production, reduce or eliminate inventories. In such a context, companies need to understand their own ability. Four Class MRPII is based on the ability to react production by designing virtual library positions in the production process. The warehouse of key components to be tracked now. (Component: When it comes to Excel's role, it has been mentioned, which is a more practical technology found on five major pieces. The core and difference is that it has already begun in the server. This investigation program has a great promotion value.)
Design ideas:
First create a key part of the table key_item_cal to be evaluated, connect with the ware list H_MORVE through the part code. Baseline based on key_item_cal, at the same time, it is designed to generate detail and summary, and the table structure is shown in Figure Num.
Figure 9
The characteristics of the data in the table are shown in Figure 10.
Figure 10
Seeing this table, there will be two coded fields that will have two coded fields: key_item, Style is why all the summary key_item is all 1 is actually the characteristics of this table, because our final performance is To behave in a table, it will be in the table, and the summary is also in detail, which is the structure shown in Figure 11. Figure 11
By designing two coded fields, the first field can be used to join the warehouse table, pass the description to 1, and the second field is for packet summary, by a structure, key components can be performed well. Statistics and analysis.
The original code of its results is:
Select dbo.Key_Item.key_item_name as item_name,
DBO.H_MORVE.QTY_RECVD1 AS Quantity,
dbo.Key_Item.style as Style
From dbo.Key_Item Left Outer Join
DBO.H_MORVE ON DBO.KEY_ITEM.KEY_ITEM = dbo.h_morve.Item
WHERE (dbo.Key_Item.Key_Item <> 1)
Union
SELECT MAX (dbo.Key_Item.key_item_name) as item_name,
SUM (dbo.h_morve.qty_recvd1) as quantity,
dbo.Key_Item.style as Style
From dbo.Key_Item Left Outer Join
DBO.H_MORVE ON DBO.KEY_ITEM.KEY_ITEM = dbo.h_morve.Item
Group by dbo.Key_Item.style)
Order by dbo.Key_Item.Style ASC
This algorithm does not support UNITION in the View Wizard provided by SQL Server. When you use a command method to generate a view, the last order is not supported. Without STYLE order, the final data performance is not coming. So the final method is the stored procedure to generate the stored procedure of Key_iTM. The code is as follows:
Create Proc Key_ITM
AS
Select dbo.Key_Item.key_item_name as item_name,
DBO.H_MORVE.QTY_RECVD1 AS Quantity,
dbo.Key_Item.style as Style
From dbo.Key_Item Left Outer Join
DBO.H_MORVE ON DBO.KEY_ITEM.KEY_ITEM = dbo.h_morve.Item
WHERE (dbo.Key_Item.Key_Item <> 1)
Union
(
SELECT MAX (dbo.Key_Item.key_item_name) as item_name,
SUM (dbo.h_morve.qty_recvd1) as quantity,
dbo.Key_Item.style as Style
From dbo.Key_Item Left Outer Join
DBO.H_MORVE ON DBO.KEY_ITEM.KEY_ITEM = dbo.h_morve.Item
Group by dbo.Key_Item.style)
Order by dbo.Key_Item.Style ASC
Go
Generate two data final expressions in the design of the DTS package, one is an Excel form, the other is the SQL Server table of Key_Item_check. This automatically generates the table of key_item_check in Excel and SQLServer by automatically calling the DTS package every day. The use of data analysts and line production statistics. The package structure is shown in Figure 12
A good view design platform is available in SQLServer2000, which is simple and convenient. It is very easy to work for the inline and outer lines of the two tables. However, the guidewhere thereof is a restriction condition. If the UNION keyword cannot be used in the design platform of the view, there is also a condition such as CASE and cannot be used in the design platform. This greatly discovers the view of the view. Relatively, for those who are proficient in SQL statements, uses commands to generate a view more convenient, especially if the data perspective is more complex database applications, such as the following example:
SELECT
PARENT, MAX (PARNT_DESC) AS PARNT_DESC,
Max (Case Component When 'WC [R] 101' Ten Comp_Desc Else '' end) AS 'WC [R] 101',
Max (Case Component When 'WC [R] 102' Ten Comp_Desc Else '' End) AS 'WC [R] 102',
Max (Case Component When 'WC [R] 103' Then Comp_Desc Else '' End) AS 'WC [R] 103',
Max (Case Component When 'WC [R] 104' Ten Comp_Desc Else '' End) AS 'WC [R] 104',
Max (Case Component When 'WC [R] 105' Ten Comp_Desc Else '' End) AS 'WC [R] 105',
Max (Case Component When 'WC [R] 106' Ten Comp_Desc Else '' end) AS 'WC [R] 106',
Max (Case Component When 'WC [R] 107' Ten Comp_Desc Else '' end) AS 'WC [R] 107',
Max (Case Component When 'WC [R] 108' Ten Comp_Desc Else '' end) AS 'WC [R] 108',
Max (Case Component When 'WC [R] 109' Ten Comp_Desc Else '' End) AS 'WC [R] 109',
Max (Case Component when 'WC [R] 10B' Ten Comp_Desc else '' end) AS 'WC [R] 10b',
Max (Case Component When 'WC [R] 10C' Ten Comp_Desc else '' end) AS 'WC [R] 10c',
Max (Case Component When 'WC [R] 10d' Ten Comp_Desc Else '' end) AS 'WC [R] 10d',
Max (Case Component When 'WC [R] 10e' Ten Comp_Desc Else '' End) AS 'WC [R] 10E', Max (Case Component When 'WC [R] 201' TEN COMP_DESC ELSE '' End) AS 'WC [R] 201 ',
Max (Case Component When'WC [R] 202 'Ten Comp_Desc Else' 'End) AS' WC [R] 202 ',
Max (Case Component When 'When CoMP_DESC ELSE' 'End) AS' WC [R] 203 ',
Max (Case Component When'WC [R] 204 'Ten Comp_Desc Else' 'end) AS' WC [R] 204 ',
Max (Case Component When'WC [R] 205 'Ten Comp_Desc Else' 'end) AS' WC [R] 205 ',
Max (Case Component When 'When CoMP_DESC ELSE' 'End) AS' WC [R] 206 ',
Max (Case Component When'WC [R] 207 'Ten Comp_Desc Else' 'end) AS' WC [R] 207 ',
Max (Case Component When'WC [R] 208 'Ten Comp_Desc Else' 'end) AS' WC [R] 208 ',
Max (Case Component When 'WC [R] 301' Ten Comp_Desc Else '' end) AS 'WC [R] 301',
Max (Case Component When 'WC [R] 302' Ten Comp_Desc Else '' end) AS 'WC [R] 302',
Max (Case Component When 'WC [R] 303' Ten Comp_Desc Else '' End) AS 'WC [R] 303',
Max (Case Component When 'WC [R] 304' Ten Comp_Desc Else '' end) AS 'WC [R] 304',
Max (Case Component When 'WC [R] 305' Ten Comp_Desc Else '' end) AS 'WC [R] 305',
Max (Case Component When 'WC [R] 306' Ten Comp_Desc Else '' End) AS 'WC [R] 306',
Max (Case Component When 'WC [R] 307' Ten Comp_Desc Else '' end) AS 'WC [R] 307',
Max (Case Component When 'WC [R] 308' Ten Comp_Desc else '' end) AS 'WC [R] 308',
Max (Case Component When 'WC [R] 309' TEN COMP_DESC ELSE '' END) AS 'WC [R] 309', Max (Case Component When 'WC [R] 501' Ten Comp_Desc Else '' end) AS 'WC [R] 501 ',
Max (Case Component When 'WC [R] 601' Ten Comp_Desc Else '' End) AS 'WC [R] 601',
Max (Case Component When 'WC [R] 701' Ten Comp_Desc Else '' end) AS 'WC [R] 701',
Max (Case Component When 'WC [R] 801' Then Comp_Desc Else '' end) AS 'WC [R] 801'
From m_bom
GROUP BY PARENT
This algorithm has changed the structure of the data in the original table, and its change is as follows 13. Figure 14 shows that everyone can clearly understand.
Figure 13 is the original data in the database, and the data without the processing of the above algorithm
Figure 14 is the data generated after processing by the above algorithm. By comparing everyone will find it.
Figure 13
Figure 14
Through the above algorithm, the data format of Figure 13 is converted to a record shown in Figure 14. This use is also based on special requirements in the enterprise. The corporate process route is manifested through the father and son relationship. It is the way shown in Figure 13, and the application is required to have a complete process. The data needs to be processed. This example of Figure 14 is designed to generate a process card.
Through the following examples and quite pictures, we should have already demonstrated one aspect of modern enterprise data application tools. Our companies are informatization, and have begun to pay attention to our products. The other major resources after raw materials are production data, which is itself A leap in our business management. In terms of the choice of tools, this is just an example in the application, of course this is just a horns of the iceberg. The new technology has not better becomes a productive forces in our enterprises.
In the above example, we have viewed that our data is already a modern production management more need for the first hand. Only the most accurate data will have the most effective decision. Modern enterprises through the integration of modern information technology, more closely related to organizational organizations in traditional enterprises, not only in their functions, but also behave in their role. Because each functional organization is a service for other organizations. It is also this service relationship that further strengthens the integration of our ability to manage and resource in the integration of informationization.
