The objective of this work is to show the possibilities of obtaining some tools that allow managing information at the user level with the Office package from SQL databases in general, also maintaining information security. Specifically applied in the Escambray Villa Clara Marketing Company that uses the EXACT Globe 3.7 as an integral system. Emphasizing the advantage that this type of solutions provides to achieve greater independence of users with respect to technology specialists, thus achieving highly personalized and easily adaptable reports, which also guarantees greater speed in obtaining a result.
Introduction
Most companies today have a lot of information stored but cannot use it because it is not processed, so they need to monitor all the relevant aspects that arise in their business environment and process and interpret them appropriately to finally make it available to those who do. need for which the use of information technology (IT) is essential today.
The results obtained thanks to technology are generally associated with their specialists. However, sometimes it is feasible at the user level with a little training, to obtain personalized information for specific analyzes, which allow for a more in-depth analysis of the reports that managers often ask their specialists to act on a specific problem.
It is clear that this not only depends on the preparation and will of the users, since sometimes companies or the computer systems with which the information is managed do not allow the development of external consultation tools for their data.
The underdeveloped countries have the most difficult path because they generally do not have all the necessary resources to achieve a high development of IT, even more so in the case of Cuba, which despite the economic blockade and in the midst of a global crisis, has proposed a computerization process with emphasis on economics. Many Cuban companies, thanks to the preparation and training of their specialists, have made great efforts to achieve IT development that allows them to remain competitive.
To update the Cuban economic model, a set of guidelines that characterize the state's economic and social policy were approved at the VI PCC Congress in 2012. These constitute a guide for the development of the short, medium and long-term objectives of the Cuban business sector, including the development of new information technologies.
Within the Cuban business community, the company Comercializadora Escambray Villa Clara, since its inception in 1999, has opted for the use of IT to identify itself in the state market for the sale of products for construction; it constitutes the commercialization channel for the production of the Industrial Business Group. Construction Materials (GEICONS) and MICONS in general, complementing the portfolio with imported products and materials.
It uses an Enterprise Resource Planning (ERP) system, called Exact Globe version 3.7 (EXACT). The company's management does not give up on its interest in improving the decision-making process based on all the information provided by the powerful ERP tool it has and defined among its short-term strategic objectives the development of improvement tools that do not it included an investment that the company was not in a position to assume.
The biggest benefit that an ERP system can provide to a company, of course taking into account that it has been effectively implemented is that an ERP system is the main source of information for other critical business systems. In other words, an ERP serves as a base or platform to implement other systems that will feed on the adequate information that an ERP manages. It also serves as a platform or basis for the implementation of other systems that allow the company to evolve into an electronic business.
EXACT is an integrated management system, mounted on SQL databases that aims to automate and support all the business processes of an organization. Each company can buy the modules they need, which provides a more feasible solution for small and medium-sized companies (SMEs), which cannot assume the total investment of an ERP. The information entered into the EXACT system is stored, linked and managed in a single database.
The version of the system that is used in the Escambray Villa Clara Marketer includes modules for stock management, purchasing, sales and accounting, covering all the processes of a marketer company. It also includes options for parameterizing company data that can be adapted to each individual entity.
This system offers the possibility of integration with office suites, for example Microsoft Office or Open Office and with programs such as Crystal Reports allowing to further personalize the professional report to workers.
The Microsoft Office, however, is among all the most used by users to manipulate the information they need, since it is the most widespread and the one that offers the most possibilities and therefore is the package in which they are most trained. Hence, a solution has been studied to access the EXACT SQL database and obtain the information that the specialists will use to carry out their particular reports and analyzes.
Better practices
The best practice for IT solutions is to achieve the implementation of tools that can be integrated, since what is desired is to have a comprehensive and unique information system that includes all the company's processes creating an information management environment. efficient where the information is delivered to the recipient how and when they need it to choose alternatives and not with islands of information or isolated systems that can lead to duplication and inconsistency of the data and a cumbersome process of obtaining the information based on the fact that all would not be linked in one place.
The ideal, then, would be to be able to add to the computer system that each company has new options or outputs that provide all the information needed and not create independent tools for the system. But not all ERP allow to provide this type of solutions, since although their fundamental characteristic is that they must be highly configurable and adaptable to each business itself, they generally do not admit major changes, except by hiring the company that provides a modification of the automated system, which would lead to a considerable increase in costs, for which SMEs are not prepared.
In the specific case of EXACT, some solutions can be achieved with integrated tools, but these can only be provided by IT specialists and, in their implementation and fine-tuning, require a reasonable amount of time. It is necessary to explain that for this it is necessary for the specialist who administers the EXACT system to carry out an in-depth study of the system, its databases and the modules that modify or use them.
The EXACT has a help for each module that is worked, where it specifies at the user level the terms that are used and the operations that are supported for each of the menu options. On the other hand, within its system maintenance module it has a data dictionary where it exposes for each table in the database, its fields with the data type and its indexes.
It is recommended for IT specialists who want to learn to fully exploit the capabilities of this system to create, as is feasible, a test company where, in addition to all the information that the system provides, review the way in which the data is modified. once an option is executed, this way they will be able to provide more complete and reliable solutions.
It is essential to evaluate in each case the potential that each specific software offers to achieve its modification and obtain new reports customized to the needs of each user in need. However, users should have the possibility of developing their own tools that allow them to become somewhat independent from IT specialists.
On many occasions, the specialists of each specific business activity must carry out analyzes beyond the pre-designed reports requested by the company's management, to find an explanation, for example, the deterioration of any indicator or to find a difference in an accounting entry with Regarding their items, this information varies according to what is sought, that is why they do not always need the same data to review and although they are all in the system they are in different options.
The initial idea and according to best practices is that departures are scheduled for each of the specific needs and are integrated into EXACT, but sometimes the system administrator cannot meet all the demands according to the time the response is needed, in addition that these were developed for spot analysis only and are generally not reused. So it was decided to find a solution that would make the specialists somewhat independent, as long as they were for specific analyzes.
It should be noted that this does not in any way contradict the achievement of a comprehensive system as best practice, since it is also considered as a good practice that any automated system allows users to feel pleased and really see solutions to their problems in automation. and not dependency.
SQL databases
SQL is a database access language that exploits the flexibility and power of relational systems and thus allows a wide variety of operations. It is a "high level" or "non-procedural" declarative language that, thanks to its strong theoretical base and its orientation to the management of record sets, allows high productivity in coding
Your Database Engine is the primary service for storing, processing, and protecting data. Provides controlled access and fast transaction processing to meet the requirements of your company's most demanding data consuming applications.
To easily manage permissions on databases, SQL Server provides several roles, which are the principals that group other principals. They are like the Microsoft Windows operating system groups. Database level roles apply to the entire database in terms of its scope of permissions.
Security principals are entities that can request resources from SQL Server. Like other components of the authorization model, principals can be organized into hierarchies. The scope of influence of a security entity depends on the scope of its definition: Windows, server or database; and whether the principal is indivisible or is a collection. A Windows Login is an example of an indivisible principal and a Windows Group is an example of a collection type.
SQL Server level security entities
- SQL Server Login: The SQL Server login sa is a server-level principal. It is created by default when an instance is installed. Starting with SQL Server 2005, the default database for sa is master. It is a change of behavior with respect to previous versions of SQL Server. Server role: These roles are security entities that group other security entities. Server-level roles apply to the entire server in terms of its scope of permissions. They correspond to the groups of the Windows operating system Database level security entities Database user: A user is a database security entity. Logins must be assigned to a database user in order to connect to a database.A login can be assigned to different databases as different users but can only be assigned as one user in each database. Database role: All users in a database belong to the database role publics. When a user has not been granted or denied specific permissions for a securable object, the user inherits the permissions granted to the public role for that object. Application role: This is a database principal that allows an application run with your own user permissions. You can use application roles to allow access to specific data only to those users who connect through a specific application. Unlike database roles,application roles do not contain members and are inactive by default. Application roles work with both modes of authentication.
BD client and server
Each database includes a guest user. The permissions granted to the guest user apply to all users who have access to the database, but do not have an account in the database. Cannot remove guest user, but can be disabled if CONNECT permission is revoked
As a security entity, users can be granted permissions. The scope of a user is the database. To connect to a specific database on the instance of SQL Server, a login must be assigned to a user in the database. Permissions within the database are granted and denied to the user of the database, not the login.
Connecting to database sources
Open DataBase Connectivity (ODBC) is a database access standard developed by SQL Access Group in 1992. ODBC's goal is to make it possible to access any data from any application, no matter what database management system (DBMS) store the data.
The software works in two ways, with driver software on the client, or a client-server philosophy. In the first mode, the driver interprets the SQL connections and calls and translates them from the ODBC to the DBMS. In the second way to connect to the database, a data source name (DSN) is created within the ODBC that defines the parameters, route and characteristics of the connection according to the data requested by the creator or manufacturer. The moment an SQL server is installed on a network, the driver to create a SQL DSN is activated on the ODBC.
This DSN actually allows us to define the database that will be interrogated without having to go through the application that we have used to build it, that is, with simple calls and commands from a program we will be able to obtain the data we are looking for without the need to run the the database as Microsoft Access which, obviously, will not have to be found on the server where we work.
Tooling modeling
In order to develop any computer tool, it is necessary to initially evaluate the needs of the specialists and the possibilities offered based on the way the data is stored. With this information, a design or modeling of what is to be automated is carried out first. to then guarantee that the implementation and fine-tuning is efficient and effective.
Thanks to the facilities provided by the SQL databases, it is totally feasible to obtain information from the database using the tools provided by the Office package and the ODBC connections, always guaranteeing the security of the information, in such a way that can modify.
For this you can create an SQL user who only has access to consult the data, on the other hand an ODBC connection is created within the system tools that connects with that user to the system's SQL database, this would allow some specialists with certain skills in using the Office, obtain and link relevant information without the assistance of IT specialists.
It is totally feasible from Excel and Access to create external data queries for which the predesigned connection could be used, accessing as a limited SQL user only to query. For this query, both Excel and Access provide a wizard that guides the steps to follow and which is very simple if the tables and their fields are known; it also allows conditioning the ranges and ordering them. The result of this query is returned in the case of Excel in a worksheet and from there it can be worked as one more document, incorporating formulas that provide more information than is available in the rest of the columns, and also easily customizable.
On the other hand, you can obtain, with said worksheet, dynamic charts and tables that link the information and make them more explanatory to make a specific report, which by simply updating the query are kept with the latest information captured in the system.
Something very similar occurs with Access, since it allows you to link the tables and obtain slightly more complex information, although this also requires the user to have more preparation. This is why most of these tools are developed on Excel.
With the use of any of these variants, each interested specialist could form their own reports, which could also be adapted to their needs. The problem related to the knowledge of the tables and their contents is solved by the EXACT system itself as one of its options, where it provides this information, as one more option within the Maintenance / Support / File List Module.
This problem can also be solved if IT specialists create general queries with all the information so that users can adapt it to their needs without requiring much training.
As is logical, this option is only informative and its modification is not possible, hence it is proposed to be used by the company's specialists for their training in order to achieve designs closer to their needs.
In the case of other companies that do not have the EXACT system and their database is SQL, IT specialists could develop a dictionary of data by which the advanced user can be guided.
With all this we can argue that it is possible to exploit the potentialities of access to query SQL databases from the Office package.
It is important to note that on many occasions different data or with another level of detail must be specified to delve further into a certain problem and that would generate the development of a new tool, however with this design that is proposed by the specialists themselves they can easily add all the information they need to perform different analyzes of the same problem. Hence its importance.
Steps to follow
In summary, the steps to follow for the development of these tools would be:
1. Create a read-only user in SQL: For this, the system administrator creates a new Logins from the SQL Server Enterprise Manager, specifying him among his Read Only rights.
2. Create a DNS connection in ODBC: In the administrative tools of the control panel of the operating system, select Data sources / Add and within the wizard choose the SQL Server as the controller and as the user and password the one created in the previous step. This process must be done on each of the machines where you are going to work.
3. Make the queries, which depends on the Office component to be used.
- From Excel: Select Data / Get external data / From Microsoft Query. At this point the connection created in step 2 is chosen and a wizard opens with the tables and fields of the entire database. It allows to select, filter and order by different criteria. At the end the wizard allows you to return the data to a worksheet in Excel or modify the SQL query from the Query. From Access: The tables that are needed are linked from external data / ODBC Databases / Link and like for Excel the source previously defined in step 2 is chosen. From this moment it can be used as a table created from Access and make all the queries and reports that are needed. Since the tables are linked and not imported they are kept up to date.
4. In any of the two specified cases the result of the queries can be worked as Office documents, adding formulas, filters, conditions, graphics, etc.
Implementation of some tools
Based on the modeling explained above and using the Office package, a simple group of solutions were implemented in the Escambray Marketing Company to help the work of some specialists to provide information to make specific or general interest analyzes. Such is the case of:
Product catalog: The company Comercializadora Escambray provides visual information of the products that it commercializes through an exhibition hall (showroom), in this way the clients who visit us can verify if the products correspond to their needs, but for this they are forced to attend our company. This is why commercial specialists also needed to have a printed catalog of product images to help customers.
They decided to photograph the products in inventory and store this information in a table with the product code, the description and a field where the photo is stored. This table was created using Microsoft Access, with the name of Images and was linked in the database itself created with the table of articles of EXACT and others of interest to provide more detailed information to the client. With this information, a query was created, linking all the information, creating a report that is sent to the clients with all the information, including the visual one. The commercial managers personalize the query by filtering or adding the specific information that each client requests. This catalog is also printed to be consulted in the company itself if desired.
Daily table: This tool was made using Microsoft Excel external queries. It provides all the information on the balance sheet, the warehouse inventory and the pending debit and credit items, on different worksheets. In the end, the economists create a worksheet where they link all the information consulted and use it to balance their accounts. This worksheet varies according to the differences that need to be found. Every day, each specialist consults this information and can determine if all the processes carried out on the day were correctly accounted for.
Sales behavior: The Exact provides a large number of reports on sales behavior, which can also be customized by the system administrator at the desired level, however the commercial manager reviews the status of this indicator daily to make decisions that guarantee compliance with plans. Sometimes you need to detail more about a specific group of articles or about a certain market or add new information about the suppliers or customers or the commercials that make a certain sale.
This is why it is requested to have greater independence in obtaining your own exit reports. For the development of this tool, Microsoft Excel was used, several worksheets were created with external queries to SQL that provided all the information in the tables related to sales. From this, the commercial manager makes his own worksheets that group the information in dynamic tables in the way he needs it, sometimes by months or quarters or with other levels of details, depending on the analysis he wants to do. In this way, it has also developed a sales emulation among the commercials, which it publishes on the company's intranet through obtained graphics.
Reconciliation with clients: A reconciliation is carried out monthly between the Escambray Marketing Company and the clients who purchased from it. This reconciliation does not have a predetermined format, it depends on the type of relationship you have had with the company and on the information you want to reconcile. Sometimes debts or advances or invoices made must be taken into account to determine a difference between what is paid by the client and what is pending. For this, Microsoft Excel was used, worksheets were created for invoices and pending items. The commercial manager filters the information related to a specific client with whom he wishes to reconcile, groups all the data he possesses using pivot tables and determines the aspects that he needs to reconcile.
These are just some of the solutions that have been proposed to specialists in the commercial and accounting areas, it is necessary to highlight that using this initial information the commercial and economic have developed their own reports adding to the results other formulas and data that have allowed them further explain the behavior of some indicators.
Main advantages:
- The user is more independent for his work, since he does not need the attention of an IT specialist for the solution. Custom solutions are developed, since the user can obtain reports according to his tastes and needs. Easily adaptable to the need for a Specific information, since it can be modified each time a new analysis is to be carried out or even be used for different analyzes.Faster solution, since the user knows exactly what he wants and therefore does not need to explain it in such a way that an IT specialist understands this and can develop the tool.
Its main disadvantage is that users must be trained in the way their system has designed their data, for which EXACT provides all the information. In the event that the system does not, the administrator must provide this information or create general queries for each type of user, which they can modify according to their needs.
Conclusions and recommendations
- ODBC connections to SQL databases allow external queries to be made to the systems that provide information in real time, and it is recommended to create users with restricted permissions, thus guaranteeing the security of the information. The EXACT Globe 3.7 SQL databases can be designed with user-level tools that provide personalized, fast and very useful information.
Bibliography
- , accessed December 2012 EXACT Globe 3.7 Manual SQL Server 7 Manual.
