How to quickly raise the management accounting system in an advertising agency from scratch and without a budget
Management accounting system - serves to collect, systematize, attribute, store and analyze data for use in making management decisions . Simply put, it is needed first of all so that at any second a manager can open Power BI / Excel and see what is happening in the company right now: for money, for people, for KPI, for quality indicators, and so on. The system described below is a working model of what we would like to see “ideally”, and within the framework of only one department of Digital advertising. The solution for the entire agency is already being created by professional developers on a much more serious platform about which it will probably be written in the future.
Perhaps for someone written below will be obvious and trite. However, in our company this was practically an innovation, which helped to solve problems that seemed previously unresolvable without massive financial injections. Also, I want to immediately introduce myself not as a professional developer, which I am not. My main responsibility is project management, team coordination. There is an understanding of the business process in digital advertising, as previously he worked in the buying. Due to this, it was easier because I knew about what exactly should be the output. Together with the team, which included buyers and procurement control experts, we were finalizing the image of the result. I implemented the implementation mostly on my own, with the active support of the IT director and some other employees. The lack of knowledge of the technical nuances of programming was filled in by reading the forums / blogs / watching video guides. In our case, this was quite enough to create a working solution without additional investments. Yes, there were some more active projects besides this. That is, the work on this project took approximately 10% to 30% of my working time.
Historically, we did not have a unified management accounting system in our company. No, there were of course all kinds of 1C and Terrasoft, solving some separate tasks of accounting, financial accounting, document circulation. However, every time when the manager urgently needed a report on the activities of our department, we had to throw a cry in the mail to collect data on a bunch of accounts of advertising platforms. All of this was reduced to a certain large exelku, which was checked manually by the head and sent further. As you understand, every time such a “fire” took a lot of time and effort, and the accuracy of the numbers was, frankly, not the highest.
')
It was then that the first time the idea arose to make a certain base. In order to upload a report on current revolutions in the context of each site (format, device, and so on) would take seconds and not require the involvement of the whole team. So, we started with “pure” MS Access. Made a bunch of tables, some queries that summarize the data and display them in a digestible form.
The filling of the first version of the Access database was done as follows:
We used tabular data entry forms that were created by one click based on the tables themselves. The managers made the exelki in a certain format, and then I copied this sheet using a Copy-Paste method into a tabular form, consisting of ordinary text fields and combo boxes. With a special insert, the combo boxes immediately picked up the value, if it was in the table tied to the combo box. Reports, in the form of ordinary SQL queries, were formed in a fraction of seconds. The resulting data was copied back to Excel using the Copy-Paste method. This scheme suited all of them for about a year and a half.
But the department grew, as did the flow of incoming tasks. If the old Access database started with about 50 projects per month, then after 2 years it was already more than 100 projects or 1200 per year. The file base began to slow down. In addition, reporting requirements have become more complex. It was necessary to form them more often than before and with a much larger amount of data. At some point, management set the task of making a normal accounting system that other departments in our department could use, and the director himself could receive all the necessary reports.
So, we had the task to do something much more flexible than the Access file base with a bunch of table forms. At the same time, the solution should have been fairly simple to implement, since there was no excess budget in the company. Deadline was of course "just yesterday." The task needed to be implemented quickly and surely.
Nothing but MS Access and VBA immediately came to mind. But I really wanted to get away from the cumbersome files with the database, which need to be constantly backed up, restored, compressed. File bases quite often fall and just buggy. The speed of the formation of complex reports on large samples (more than 5000 records about 40 columns, say) did not suit at all: sometimes it takes 5-7 minutes to create such uploads. I wanted the flexibility of the Access interface and the speed of MSSQL. In addition, there were specific requirements associated with separate access to data and the undesirability of passwords mk. they would immediately become known to all. The database was intended only for internal use at work, without the possibility of remote access.
Having searched the forums, they nevertheless made the decision to make it based on Access, but with the addition of autologin functionality via ActiveDirectory, separate access to forms and data, and data storage in the MSSQL database. Fortunately, the MSSQL server has already been raised under another project, and the admins immediately agreed to provide the necessary resources. Architecturally, the application looked so that Access acted as interface and access sharing. All data manipulations were performed in MSSQL. The tables were tied up with the no-DSN method written in VBA by the same script.
Distribution of new versions was carried out using the following scheme:
On the Windows file server, a directory was created into which the binary versions of the base assemblies were downloaded. About security is written below. So, with the help of tools built into VBA, the database compiled itself and put it into the directory. At the same time, the old version of the binary was saved as a renamed file, in case of an urgent need to roll back the version. It was possible to implement all this with the help of Git, but decided to go easier. All automation was implemented on CMD scripts. Using the same scripts, the database was launched on the client machine. The user clicked the CMD file, the binary was downloaded from the server to a local temporary directory and started from there. The need for downloading was dictated by Access restrictions on simultaneous launch of the database by several users.
Below is the code of the CMD script that starts the database from the user:
To run the binary, there was no need to buy all MS Access. Only the solution developer needs the full version. Users installed a 32-bit version of Access 2013 Runtime , available for free. When installing all the necessary Access extensions (in the full version), this scheme worked fine on Windows 7.
The system of domain authorization and security of Access was initially built carefully and even a little paranoid. This was due to the requirement of guidance for data security and access control. Buying information is of great value in the market, since it can be a source of competitive advantages for the agency and improve the negotiating position during the dialogue with the sites. It was necessary to strictly restrict access to the forms for viewing information, depending on the position, department and the specific group to which the user belongs. Also, it was required to automatically limit the data uploaded in the form of reports. We took the excellent Tom Van Stiphout article “ Securing Access databases using Active Directory ” as a basis. In general, in the Russian-language segment of the Network there is very little in-depth information about the capabilities of MS Access and it rarely goes beyond the functionality of the Borey demo database . Therefore, the development of 90% used English-language articles and posts on various thematic forums. So, the domain authentication method was chosen. The user has been added to a specific group. Yes, I had to write an application every time, but the security of such a decision justified the costs. At the level of all forms, checkers were checked, checking which group the user belongs to. If acceptable groups were not found, then the database completed its work with an error. In case of unforeseen situations, a back-door was provided in the form of a text file with certain data that can be put in the same directory as the binary. If the database detected this file, then all forms were opened without checks. Only the system developer had information about such a possibility; therefore, the risk of unauthorized access was minimized.
A little later, in addition to “wired” in the form of checking the user's belonging to a certain group, a simpler and more flexible add-on appeared in the form of a standard check in the database: “user-role-right”. This is of course a much more vulnerable scheme. It is used only to clarify user rights when working with the Google Chrome extension, which will be briefly described below.
The experience of working with MSSQL was not particularly, therefore, the base was designed quite messy. A simple table hierarchy has been created:
Also, some procedures were created that output information from the database in various forms:
As it is not difficult to guess, the main way to restrict access to data was at the level of MSSQL. Access before displaying the list of projects made a request to the procedure “List of projects” with indication of the user id. The procedure compared the list of clients available to this user and displayed projects that were either created by this user or belong to his clients.
The standard report was also tied to the withdrawal of projects, so the user could not see the position of not "their" clients. The problem of double calling EXEC in MSSQL was circumvented by outputting positions through a function:
That is, to output the Standard Report, a procedure was requested (with parameters), which already made a request to the auxiliary function and filtered the available list of projects through INNER JOIN. Clumsy is simple, but works like a clock.
The connection between MS Access and MSSQL was, as mentioned above, based on the noDSN ODBC connection.
To join the table, the following code was called:
Two methods were used to work with the base. The first was to call the procedures and upload the result via a temporary request:
The second method makes more use of the capabilities of Access itself, namely the record sets. I will not describe in detail this powerful tool for working with data. You can read more about him on YouTube .
At some point, the number of database users has increased significantly. At the same time, a significant part of the database operators did not need anything other than information input tools. Tastes and habits are quite different for everyone. The need to maintain relatively complex Access infrastructure and constantly supplement it with the requirements of new users, forced us to look for an even simpler solution. Namely, it was required to make an easily supported terminal to fill the base, without the need for an Access installation, but powerful enough. There was another problem. In addition to the management accounting system described by me, the company already had a rather cumbersome document management system. Part of the data overlapped, so it was necessary to eliminate duplication of work. The most difficult introductory was that for various reasons it was impossible to change the mentioned workflow system: neither the front-end, nor the backend. Access to the base of this system for reasons of data integrity and security, it was decided not to provide.
To solve this problem, we used the features of Google Chrome Extensions. Fortunately, this is the browser that is installed by default on all employees of our company. The system is based on a bundle: Chrome Extensions + Angular + PHP + MSSQL (the same base as for Access). I will write more about this decision separately, since it is unlikely to be briefly described.
So, the infrastructure of information input was created. The conclusion, that is, management reports themselves, was built on two platforms: MS Excel and MS Power BI. With the first, everything is very simple. Access used a standard Excel file connection module, already made in a specific template. A copy of this file was created in the user's local temporary directory. Then a call was made in MSSQL to the Standard Report procedure that I already mentioned. The output went to a temporary Recordset (DAO), which was parsed according to the structure of the Excel file. At some point, users are faced with a long report generation time, since it includes 41 columns, and the number of records uploaded has already gone to tens of thousands. It is planned in the foreseeable future to rewrite the Excel report output module using a plug-in DLL in C ++.
Worked out a Power BI report much faster. At the beginning, only the desktop version was used. The developer made a model that was distributed to users (heads of key departments and the Director of the Digital Department) using about the same CMD file as described above for running Access binaries. The scheme was not very successful due to constant updates to the Power BI platform. At the same time, the model created on the new version lost compatibility with the old ones and it was necessary to constantly update the platform for 8-10 users. The solution was the cloud version of Power BI, which in the paid version supports corporate gateways to the MSSQL database and the update schedule. If earlier the user had to click on the “Update” button and wait for 2-3 minutes until the model loads the data and processes it, now he immediately sees the actual information at the moment of opening the dashboard. A separate article will be written on the intricacies of using Power BI to upload corporate reports.
The solution development methodology was and remains flexible. The forms built into Access allowed us to quickly create a prototype system and, within the first week, start discussing it with users. All decisions were made on the fly. It was not such that the developer "went to think" for 2-3 weeks. The result was issued immediately. If I didn't like something, they changed me right away, right in the process of the demonstration after the next one-day sprint. All comments were taken into account for a maximum of a couple of days, especially in the first weeks of development, since it was at this time that the most important part of the functionality of the system was laid. Directly technical development took about 40-50% of the time. The rest of the time was spent on numerous user inquiries on the nuances, coordination of the project working group, coordination with the IT department and the business process manager. Everything was done within the agency, without the involvement of professional programmers. The term for creating the system on Access was 4 months Google Chrome extensions - 3 months. Power BI reports took about 1 week. About a year went to fine-tune the system exactly to the requirements of the process and users.
Thus, with the help of MS Access and MSSQL, it was possible to create a self-written model of a management accounting system from scratch from scratch. With it, the time for uploading the most common reports and summary reports has been reduced to a few minutes or even seconds. Some executives had enough Power BI reports, which they analyze on their own. The whole development of the working model took about 8 months in total. The result was not only a solution to the tactical problem of speeding up the unloading of management reports, but also a much deeper understanding of how the management accounting system should be built at the agency level.
Perhaps for someone written below will be obvious and trite. However, in our company this was practically an innovation, which helped to solve problems that seemed previously unresolvable without massive financial injections. Also, I want to immediately introduce myself not as a professional developer, which I am not. My main responsibility is project management, team coordination. There is an understanding of the business process in digital advertising, as previously he worked in the buying. Due to this, it was easier because I knew about what exactly should be the output. Together with the team, which included buyers and procurement control experts, we were finalizing the image of the result. I implemented the implementation mostly on my own, with the active support of the IT director and some other employees. The lack of knowledge of the technical nuances of programming was filled in by reading the forums / blogs / watching video guides. In our case, this was quite enough to create a working solution without additional investments. Yes, there were some more active projects besides this. That is, the work on this project took approximately 10% to 30% of my working time.
Historically, we did not have a unified management accounting system in our company. No, there were of course all kinds of 1C and Terrasoft, solving some separate tasks of accounting, financial accounting, document circulation. However, every time when the manager urgently needed a report on the activities of our department, we had to throw a cry in the mail to collect data on a bunch of accounts of advertising platforms. All of this was reduced to a certain large exelku, which was checked manually by the head and sent further. As you understand, every time such a “fire” took a lot of time and effort, and the accuracy of the numbers was, frankly, not the highest.
')
It was then that the first time the idea arose to make a certain base. In order to upload a report on current revolutions in the context of each site (format, device, and so on) would take seconds and not require the involvement of the whole team. So, we started with “pure” MS Access. Made a bunch of tables, some queries that summarize the data and display them in a digestible form.
The filling of the first version of the Access database was done as follows:
We used tabular data entry forms that were created by one click based on the tables themselves. The managers made the exelki in a certain format, and then I copied this sheet using a Copy-Paste method into a tabular form, consisting of ordinary text fields and combo boxes. With a special insert, the combo boxes immediately picked up the value, if it was in the table tied to the combo box. Reports, in the form of ordinary SQL queries, were formed in a fraction of seconds. The resulting data was copied back to Excel using the Copy-Paste method. This scheme suited all of them for about a year and a half.
But the department grew, as did the flow of incoming tasks. If the old Access database started with about 50 projects per month, then after 2 years it was already more than 100 projects or 1200 per year. The file base began to slow down. In addition, reporting requirements have become more complex. It was necessary to form them more often than before and with a much larger amount of data. At some point, management set the task of making a normal accounting system that other departments in our department could use, and the director himself could receive all the necessary reports.
So, we had the task to do something much more flexible than the Access file base with a bunch of table forms. At the same time, the solution should have been fairly simple to implement, since there was no excess budget in the company. Deadline was of course "just yesterday." The task needed to be implemented quickly and surely.
Nothing but MS Access and VBA immediately came to mind. But I really wanted to get away from the cumbersome files with the database, which need to be constantly backed up, restored, compressed. File bases quite often fall and just buggy. The speed of the formation of complex reports on large samples (more than 5000 records about 40 columns, say) did not suit at all: sometimes it takes 5-7 minutes to create such uploads. I wanted the flexibility of the Access interface and the speed of MSSQL. In addition, there were specific requirements associated with separate access to data and the undesirability of passwords mk. they would immediately become known to all. The database was intended only for internal use at work, without the possibility of remote access.
Having searched the forums, they nevertheless made the decision to make it based on Access, but with the addition of autologin functionality via ActiveDirectory, separate access to forms and data, and data storage in the MSSQL database. Fortunately, the MSSQL server has already been raised under another project, and the admins immediately agreed to provide the necessary resources. Architecturally, the application looked so that Access acted as interface and access sharing. All data manipulations were performed in MSSQL. The tables were tied up with the no-DSN method written in VBA by the same script.
Distribution of new versions was carried out using the following scheme:
On the Windows file server, a directory was created into which the binary versions of the base assemblies were downloaded. About security is written below. So, with the help of tools built into VBA, the database compiled itself and put it into the directory. At the same time, the old version of the binary was saved as a renamed file, in case of an urgent need to roll back the version. It was possible to implement all this with the help of Git, but decided to go easier. All automation was implemented on CMD scripts. Using the same scripts, the database was launched on the client machine. The user clicked the CMD file, the binary was downloaded from the server to a local temporary directory and started from there. The need for downloading was dictated by Access restrictions on simultaneous launch of the database by several users.
Below is the code of the CMD script that starts the database from the user:
Set SERVER_PATH=\\storage\Dep\Share\Database xcopy "%SERVER_PATH%\DB_UU.accde" "%USERPROFILE%\" /Y %USERPROFILE%\DB_UU.accde To run the binary, there was no need to buy all MS Access. Only the solution developer needs the full version. Users installed a 32-bit version of Access 2013 Runtime , available for free. When installing all the necessary Access extensions (in the full version), this scheme worked fine on Windows 7.
The system of domain authorization and security of Access was initially built carefully and even a little paranoid. This was due to the requirement of guidance for data security and access control. Buying information is of great value in the market, since it can be a source of competitive advantages for the agency and improve the negotiating position during the dialogue with the sites. It was necessary to strictly restrict access to the forms for viewing information, depending on the position, department and the specific group to which the user belongs. Also, it was required to automatically limit the data uploaded in the form of reports. We took the excellent Tom Van Stiphout article “ Securing Access databases using Active Directory ” as a basis. In general, in the Russian-language segment of the Network there is very little in-depth information about the capabilities of MS Access and it rarely goes beyond the functionality of the Borey demo database . Therefore, the development of 90% used English-language articles and posts on various thematic forums. So, the domain authentication method was chosen. The user has been added to a specific group. Yes, I had to write an application every time, but the security of such a decision justified the costs. At the level of all forms, checkers were checked, checking which group the user belongs to. If acceptable groups were not found, then the database completed its work with an error. In case of unforeseen situations, a back-door was provided in the form of a text file with certain data that can be put in the same directory as the binary. If the database detected this file, then all forms were opened without checks. Only the system developer had information about such a possibility; therefore, the risk of unauthorized access was minimized.
A little later, in addition to “wired” in the form of checking the user's belonging to a certain group, a simpler and more flexible add-on appeared in the form of a standard check in the database: “user-role-right”. This is of course a much more vulnerable scheme. It is used only to clarify user rights when working with the Google Chrome extension, which will be briefly described below.
The experience of working with MSSQL was not particularly, therefore, the base was designed quite messy. A simple table hierarchy has been created:
- Customer
- Project
- The main table is 90% of the base: Purchased items by month, linked to the project through a unique number
- Auxiliary tables of entities, such as: brands, advertising formats, types of services
- Intermediate tables between the above
Also, some procedures were created that output information from the database in various forms:
- Standard report on positions for a certain period (with the possibility of limiting the list to only those projects that the user can see)
- List of projects (for flexible display of the list, depending on the user)
As it is not difficult to guess, the main way to restrict access to data was at the level of MSSQL. Access before displaying the list of projects made a request to the procedure “List of projects” with indication of the user id. The procedure compared the list of clients available to this user and displayed projects that were either created by this user or belong to his clients.
The standard report was also tied to the withdrawal of projects, so the user could not see the position of not "their" clients. The problem of double calling EXEC in MSSQL was circumvented by outputting positions through a function:
SELECT * FROM dbo.getPositionsFiltered(@month_start, @year_start, @month_end, @year_end, @position_id) n_1 INNER JOIN #all_projects_src ON #all_projects_src.project_id = n_1.project_id That is, to output the Standard Report, a procedure was requested (with parameters), which already made a request to the auxiliary function and filtered the available list of projects through INNER JOIN. Clumsy is simple, but works like a clock.
The connection between MS Access and MSSQL was, as mentioned above, based on the noDSN ODBC connection.
To join the table, the following code was called:
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & “Server” & ";DATABASE=" & “Database” & ";UID=" & “Username” & ";PWD=" & “Password” Set td = CurrentDb.CreateTableDef(«LocalTableName”, dbAttachSavePWD, “RemoteTableName”, stConnect) Two methods were used to work with the base. The first was to call the procedures and upload the result via a temporary request:
Sub runSQL(sqlStr As String, Optional return_values As Boolean = False, Optional rs As DAO.Recordset = Null) Dim dbs As DAO.DATABASE Dim qdf As DAO.QueryDef Set dbs = CurrentDb If (isQueryExists("Temp_query")) Then dbs.QueryDefs.Delete ("Temp_query") End If Set qdf = dbs.CreateQueryDef("Temp_query") qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & SQL_SERVER & ";DATABASE=" & DATABASE & ";UID=" & SQL_LOGIN & ";PWD=" & SQL_PASS qdf.sql = sqlStr qdf.ReturnsRecords = return_values If return_values Then ' rs Set rs = qdf.OpenRecordset() Dim count_rows As Integer count_rows = myRecordCount(rs) Else ' rs, qdf.Execute End If dbs.QueryDefs.Delete ("Temp_query") End Sub The second method makes more use of the capabilities of Access itself, namely the record sets. I will not describe in detail this powerful tool for working with data. You can read more about him on YouTube .
At some point, the number of database users has increased significantly. At the same time, a significant part of the database operators did not need anything other than information input tools. Tastes and habits are quite different for everyone. The need to maintain relatively complex Access infrastructure and constantly supplement it with the requirements of new users, forced us to look for an even simpler solution. Namely, it was required to make an easily supported terminal to fill the base, without the need for an Access installation, but powerful enough. There was another problem. In addition to the management accounting system described by me, the company already had a rather cumbersome document management system. Part of the data overlapped, so it was necessary to eliminate duplication of work. The most difficult introductory was that for various reasons it was impossible to change the mentioned workflow system: neither the front-end, nor the backend. Access to the base of this system for reasons of data integrity and security, it was decided not to provide.
To solve this problem, we used the features of Google Chrome Extensions. Fortunately, this is the browser that is installed by default on all employees of our company. The system is based on a bundle: Chrome Extensions + Angular + PHP + MSSQL (the same base as for Access). I will write more about this decision separately, since it is unlikely to be briefly described.
So, the infrastructure of information input was created. The conclusion, that is, management reports themselves, was built on two platforms: MS Excel and MS Power BI. With the first, everything is very simple. Access used a standard Excel file connection module, already made in a specific template. A copy of this file was created in the user's local temporary directory. Then a call was made in MSSQL to the Standard Report procedure that I already mentioned. The output went to a temporary Recordset (DAO), which was parsed according to the structure of the Excel file. At some point, users are faced with a long report generation time, since it includes 41 columns, and the number of records uploaded has already gone to tens of thousands. It is planned in the foreseeable future to rewrite the Excel report output module using a plug-in DLL in C ++.
Worked out a Power BI report much faster. At the beginning, only the desktop version was used. The developer made a model that was distributed to users (heads of key departments and the Director of the Digital Department) using about the same CMD file as described above for running Access binaries. The scheme was not very successful due to constant updates to the Power BI platform. At the same time, the model created on the new version lost compatibility with the old ones and it was necessary to constantly update the platform for 8-10 users. The solution was the cloud version of Power BI, which in the paid version supports corporate gateways to the MSSQL database and the update schedule. If earlier the user had to click on the “Update” button and wait for 2-3 minutes until the model loads the data and processes it, now he immediately sees the actual information at the moment of opening the dashboard. A separate article will be written on the intricacies of using Power BI to upload corporate reports.
The solution development methodology was and remains flexible. The forms built into Access allowed us to quickly create a prototype system and, within the first week, start discussing it with users. All decisions were made on the fly. It was not such that the developer "went to think" for 2-3 weeks. The result was issued immediately. If I didn't like something, they changed me right away, right in the process of the demonstration after the next one-day sprint. All comments were taken into account for a maximum of a couple of days, especially in the first weeks of development, since it was at this time that the most important part of the functionality of the system was laid. Directly technical development took about 40-50% of the time. The rest of the time was spent on numerous user inquiries on the nuances, coordination of the project working group, coordination with the IT department and the business process manager. Everything was done within the agency, without the involvement of professional programmers. The term for creating the system on Access was 4 months Google Chrome extensions - 3 months. Power BI reports took about 1 week. About a year went to fine-tune the system exactly to the requirements of the process and users.
Thus, with the help of MS Access and MSSQL, it was possible to create a self-written model of a management accounting system from scratch from scratch. With it, the time for uploading the most common reports and summary reports has been reduced to a few minutes or even seconds. Some executives had enough Power BI reports, which they analyze on their own. The whole development of the working model took about 8 months in total. The result was not only a solution to the tactical problem of speeding up the unloading of management reports, but also a much deeper understanding of how the management accounting system should be built at the agency level.
Source: https://habr.com/ru/post/307310/
All Articles