STUDY GUIDE Introduction to data management for ac_241011_121620.PDF

Chapter 1 Introduction to data types and databases for Accounting Information Systems (AIS) 1.1 The importance and value of information The information explosion has caused ripple effects in all industries and affected our daily lives both at home and at the office. In the information economy, info...

Chapter 1 Introduction to data types and databases for Accounting Information Systems (AIS) 1.1 The importance and value of information The information explosion has caused ripple effects in all industries and affected our daily lives both at home and at the office. In the information economy, information has become the new definition of value. All forms of organisations and governments are experiencing challenges related to the characteristics of Big Data, the related processes and management thereof. Big Data can be defined as information assets that are characterised by such a high volume, velocity and variety that specific technology and analytical methods are needed for its transformation into value (De Mauro et al., 2018). Surdak (2014) identified six trends that are accelerating the radical increase in volume and impact of online data, namely: Mobility – The abundance of mobile devices such as smartphones and tablets that enable people to be connected 24-7; Virtual living – Online social media supports virtual living and the increasing interaction of people with family and friends online rather than face to face; Digital commerce – Buying goods and services online is now commonplace with a wide variety of alternatives; Online entertainment – people are frequently using the millions of online channels and entertainment sites for entertainment; Cloud computing – The cost and availability of cloud computing are now requiring it to be the minimum entry for organisations, large and small, to be competitive; and Big Data – Online activities are generating massive amounts of data. The wealth of information available can assist with decision-making; however, this requires good quality information. Good quality information is needed to understand the objectives of a decision, the related constraints that limit the possible alternatives and the actual alternatives themselves as well as to forecast the potential outcomes (Bélanger, Van Slyke and Crossler, 2019). The challenges of the information explosion and Big Data reduce the potential value that can be derived from the data. Effective data management tools and techniques are needed in order to obtain valuable information from Big Data, which can therefore provide value and insights to support decision-making in any organisation. Herbert Simon, the Nobel prize-winning psychologist and economist, stated way back in 1971 that “In an information-rich world, the wealth of information means a dearth of something else: a scarcity of whatever it is that information consumes. What information consumes is rather obvious: it consumes the attention of its recipients. Hence a wealth of information creates a poverty of attention and a need to allocate that attention efficiently among the overabundance of information sources that might consume it.” Although he did not use the words “information overload”, this is basically what he is talking about: being faced with more information than we can effectively process. The more information we have to sift through, the less attention we have to devote to other tasks. Information overload is a very real problem, in terms of both our business and personal lives. It reduces productivity, increases stress and can actually lead to physical health problems. 1 1.2 Introduction to Accounting Information Systems Data is one of the five key components/resources of an Information System (IS). The other components are the people, the software, the hardware and the networks, as shown in Figure 1. An IS is responsible for capturing all the data/information that is important to the business organisation. Information Systems are becoming increasingly ingrained in our everyday business, professional and personal lives and it is difficult to get through the day without interacting with such a system (Bélanger, Van Slyke & Crossler, 2019). An Accounting Information System (AIS) is one type of Information System that enables one to quickly store and retrieve all historic transactions that the company has performed and allows us to inspect each individual transaction that has been performed. It also allows the user to view summaries of the transactions in the form of financial statements and other reports. The majority of modern AISs are based on relational databases. This design means that the data is stored in the relational database and is more accurate, less redundant, and allows for data to be integrated. We will learn more about redundancy later. The Governing Body in the organisation (usually the Board of Directors) is responsible for governing technology and information in a way that supports the organisation setting and achieving its strategic objectives (KING IV Principle 12). Accountants, business managers, internal and external auditors (accounting specialists) all have an important role to play in understanding the AIS, assessing risks and evaluating the system for weaknesses, in order to respond to such risks appropriately. Remember, the majority of businesses today have migrated to an electronic AIS, which means that you would seldom have to look through physical accounting books and financial statements. As an accounting specialist, you will be responsible for identifying any security concerns of the system, and for ensuring that internal controls are implemented to reduce the possibility of fraud. An example of this, is that the tax manager/consultant would need to ensure that the AIS adheres to all the tax requirements. 2 1.3 Types of data and information Organisations make use of transactional data and information daily. External and internal sources of data are collected, which are then processed or stored in the database. An example of an internal source would be sales orders captured by staff. An external source could be data coming from a website, or a document (e.g. quote) from a supplier. The data is generally in a form which we refer to as “raw data” as the data itself does not really help us to make informative decisions. These can be individual transactions such as sales, purchases, returns, inventory breakages, etc. All data stored in the database can be transformed into information to help us make decisions. Here is where the real value lies as all the data can be used to generate timely financial reports on a daily, weekly, monthly, or yearly basis. Examples of these reports are statements of financial position, trial balances and inventory valuations. Other calculations and analysis can also be performed on the data such as Return on Investments, inventory turnover rates, etc. It is important to note that information can be generated for both internal users like managers and external users like suppliers, partners, trading partners, SARS or external auditors. Electronic data interchange (EDI) is sometimes used to communicate data/information from the organisation to their trading partners electronically and in most cases automatically. It is important that controls are in place to ensure that the right information is communicated (Schneider, 2014). 1.4 Designing database systems for Accounting Information Systems Data is input, stored, processed and converted to output in the IS to provide meaningful information to a user. If you had a business, you would want easy access to the information needed to run the business. Databases are one method used for storing data and have become a fundamental part of businesses today. Databases are not only used to store data about the business operations and the business environment, but also to transform data into valuable information to make decisions. Database systems may profoundly affect the fundamental nature of accounting. They have resulted, in many cases, in the perceived abandonment of the double-entry accounting system. This is partly due to the fact that the automation provided by the AIS requires that the user only enters one side of the entry (i.e. only the debit or only the credit) and the system automates the rest. There is no longer a need for an accountant to manually process the two entries. Because of the features of the AIS, the nature of external reporting has changed. The powerful querying capabilities that accompany database packages provide improved decision support, the ability to accommodate multiple views of the same underlying phenomenon and the ability to integrate financial and operational data. Accountants need to become knowledgeable about databases so they can participate in designing and developing the AIS of the future. This knowledge is important for designing systems that allow for accurate information that is easily accessible and therefore can be effectively reported on. The participation of accountants in the design and development process is also important to ensure that adequate controls are included to safeguard the data and assure its reliability. It is not in all cases where accountants need be involved in the development process as modern day AISs are developed using the relevant accounting standards. However, when implementing an AIS it is often necessary for the implementing organisation to have a certain level of customisation to suit the needs of the organisation. Having an accountant who not only understands the applicable accounting standards, but database knowledge as well will ensure that the organisation’s needs are met, and the fundamental characteristics of useful financial information are met. 3 1.5 Differences between database and file-based legacy systems For many years, companies created new files and programs each time an information need arose. Businesses used to store the information in systems known as “file systems”. You can think of file systems as data that are stored in folders on your PC, and each user in the business had their own PC with folders and files. Two types of files were typically stored, namely master and transaction data files. Transaction files relate to high volumes of data that are generated daily and may vary significantly. For example, transactions like sales could generate a thousand entries per day and can vary from different items purchased as well as quantities and prices. Master files relate to data that does not change often. For example, a customer’s details where their name, surname, cell number, and address will not change often. Other examples are inventory names, suppliers, or assets like vehicles where the data stays relatively constant. The problem with file systems was that they required a lot of manual work and did not integrate efficiently. Imagine the scenario where a salesperson records a new customer. For a different salesperson to serve that customer in future, he or she must have a copy of the customer’s details on their PC. This means the file had to be copied to the other salesperson. Imagine the customer’s file is now updated in the one file but is not updated in the other salesperson’s file. This means the salespersons would work with inconsistent data, which could cause a problem when trying to provide a service to the customer. A typical example is a change in the customer’s address on the one copy but not the other, which could lead to the customer’s purchases being delivered to the wrong address. This problem of duplicate information relates to unnecessary redundancy, which refers to the same data being stored in two or more separate places (Coronel, Morris, Rob, & Crockett, 2013). Having two versions of the same data will pose a problem as once one of these versions of data is edited, it will result in inconsistencies in the database as in the example of the customer in the previous paragraph. Another example is for employee data. Figure 2 illustrates this example, where two application programs are querying information from two separate files containing similar employee data. The problem is that both files store the same data such as Employee Numbers and Employee Names. The first file is storing salary related data and the second one is storing benefit detail such as medical aid. Storing the names in two different files is redundant as additional work is required to maintain the Employee names in both files. Information may be needed from two different applications to get all the information that is needed to run a query or report. In addition, inconsistencies arise when an Employee name is updated in the second file but not in the first file, resulting in two different names for the same employee. 4  Figure 2 – Example of a file system with redundancy From this example it should be evident that redundancy can lead to two other problems evident in file systems i.e. inconsistency and non-integration, since the specific data values stored in the different files are not always consistent. Non-integration of data is not a desirable situation as data should be integrated i.e. all data from different sources are combined and then users are provided with a unified view of these sources (Lenzerini, 2002). Data integration encourages collaboration between both internal and external users. Inconsistency of data should also be avoided. Inconsistency refers to the case of the same data kept at different places not matching. 1.6 Features and advantages of databases and a Database Management System (DBMS) The database approach enables a business to store all its data in one location. The database stores information in the form of tables, with each table storing information about a specific entity or relation. An entity or relation can be any object you want to store attributes about. For example, a supplier is an entity that has a name, address, phone number, account number and so on. You can think of the tables as an Excel file with columns and rows. Tables are also sometimes referred to as relations. Tables are made up of records (rows) and fields (columns). A record is a set of fields (or attributes) that all relate to the same thing (e.g., supplier), while each field represents a characteristic of the thing (e.g., supplier name, supplier address). The Database Management System (DBMS) is often referred to as middleware, which sits in the middle of the application program and the database. The DBMS is responsible for executing any request from the front-end application and retrieving the data from the physical database which could be on a server. An example of such would be when you want to view a trial balance in Sage AIS (the front- end application program), the DBMS will execute that request and retrieve the data from the necessary database tables. The DBMS is a program that manages and controls access to the database so that users can query the data without knowing exactly where the data is physically stored. Collectively the database, the DBMS and the application program are known as the database system. In the database approach, data is considered as an organisational resource or asset. The many advantages of the database approach include data integration, data sharing, reporting flexibility and reduced redundancy amongst others. Data integration is achieved since there is one centralised database consisting of different tables that can be accessed by many application programs. Data 5 sharing is achieved since data is stored centrally, and more users can access the same data without having to copy from one PC to another. However, data sharing should be done carefully, and DB administrators should ensure that internal controls are in place to ensure that only authorised users can view certain data. A very important feature of databases is program data independence. This feature is critical since when you change data in the database, the program does not crash as data is changed in the actual database and not in the program code. Reporting flexibility is achieved since a central database allows data to be merged from different tables. In addition, data in a database is time stamped, which means that data can easily be retrieved for a specific period. Minimal redundancy exists since many users can retrieve data from a central database, which reduces the redundancy of having multiple copies on various PCs. 1.7 Schemas, data models and program-data independence We need to ensure that our database tables are created correctly and that they record the correct data types. If the data that is recorded is incomplete or inaccurate, it can lead to bad decisions and can impact client service and user satisfaction (garbage in = garbage out). For example, if details such as the VAT rate, or discount percentage were recorded incorrectly in the Customer tables, this could lead to a) fines and penalties for incorrect VAT submissions and b) incorrect discounts, lost profit or disgruntled customers. It is often difficult to understand how exactly a database works. We can’t necessarily see how data is stored in the hardware, but we can draw a model of how we think it might be stored to help us understand. The way in which we perceive a database and its contents can be represented by a data model, which is an abstract representation of the contents of a database. Two models are typically used which are referred to as views and these views enforce program-data independence: The logical view - How the user or programmer conceptually organises and understands the data. The physical view - How and where the data are physically arranged and stored on disk, tape, or other media. The logical view is how we perceive how the data is stored and organised and how the user interprets that in his/her context. The most common way to understand data is through visualisation; in this case the DBMS will receive a request from the user to view the data in either a visualisation or table (logical views) and turn those requests into instructions for the operating system such as Windows, where the data will be retrieved from the database (the physical view). A data schema is a map of a logical structure of a database (hence it is how we understand how the data is stored in the database). There are three levels of schema: Conceptual-level schema; External-level schema; and Internal-level schema. The conceptual-level schema is the organisation-wide user view of the entire database and is mainly aimed at management. It is usually the first draft of a model of the organisation’s data and is typically not in a format that can be directly used for creating the database. This is how different data tables are related to one another and how data can be merged upon a query. The external-level schema is the individual user’s views of portions of the database (a subschema). For example, a lecturer may have a view of the details of their class list, but not all the data related to their students. 6 The internal - level schema provides a low-level, very detailed view of the database based on how data is stored. This will include data types, size of attribute and details about all the specific attributes in tables. 1.8 Data dictionaries and DBMS functions Consider all the different attributes stored in one table. Now imagine thousands of tables in one database. It will be very difficult to remember the details of each. This is where a data dictionary comes in handy. A data dictionary contains information about the structure of the database. For each data element stored in the database, e.g. customer number, there is a corresponding record in the data dictionary describing it. Usually only a person who has permissions as the database administrator is given access to the data dictionary. The data dictionary is also where many of the controls are added, updated or removed. For example, this is where the Primary Keys and Foreign Keys are added thus enforcing entity integrity and referential integrity (see Chapter 2 below). See an example in Figure 3. Other rules can also be added in the data dictionary and it is preferable to add them here, rather than in the computer program/application. Rules can be related to the formats of fields, for example ensuring a number is entered instead of a character or a date instead of a number. Validation rules can also be added that can for example display an error if a field is larger than a certain value. The DB Administrator can also ensure protection over the organisation’s data, forms and queries by setting permissions for access to Tables (even rows), Queries and Forms or programs. The rights allocated to a user or group of users can be set per object (i.e., Table, Query etc) can be either Read, Write, Update, Insert, Delete etc. Figure 3 – Example of a Data Dictionary Screenshot We know that data is input, stored, processed and converted to output with a database. The question is, “how exactly do we do this?” One of the ways is by using a fourth-generation language (4GL) such as Structured Query Language (SQL), which is a programming language that allows the user to create the database and then manipulate and extract useful information from the database through queries. This can be done with application programs like SQL server or Microsoft Access, which are user friendly and have many buttons and dropdown menus that are interactive. These application programs also allow a drag and drop facility that generates the programming (SQL) code for you. SQL programming consists of three main types: a) structural, b) maintenance, and c) querying. 7  Examples of SQL Code An example of SQL code that impacts structure is that which creates or amends structures in the data dictionary; one such example is shown in Figure 4, where a customer table is created with three fields (code, name and contact). In this scenario no data is added or updated, only the structure is created. In the second type of SQL code, maintenance code, SQL is used to change content in the DB (i.e. the actual data) and includes updating, inserting and deleting portions of the data in the database. An example of this type of SQL is shown in Figure 5 where two records are added/inserted into the customer table. In this example two customers, Smith and Johnson, are added. The third type of SQL allows for querying to interrogate the database, retrieve, sort, order, and present subsets of the database in response to user queries. An example of this type of SQL is shown in Figure 6, where all records in the customer table are displayed. In this type, no data is changed at all, neither is the structure changed. The data is only queried and displayed. Create TABLE CUSTOMER ( Cus_Code INTEGER NOT NULL UNIQUE, Cus_Name CHAR (25) NOT NULL, Cus_Contact VARCAHR (15) NOT NULL, PRIMARY KEY (Cus_Code)); Figure 4 – Simple SQL code to create a table in the data dictionary INSERT INTO CUSTOMER VALUES (023, ‘SMITH’, ‘0661234897’); INSERT INTO CUSTOMER VALUES (029, ‘JOHNSON’, 0732318526’); Figure 5 - Simple SQL code to add two new customers to the Customer table SELECT * FROM CUSTOMER; Figure 6 - Simple SQL code display all customers from the Customer table 1.9 Characteristics of a Relational Database Management System (RDBMS) A Relational Database Management System (RDBMS) is a DBMS with a relational data model which represents everything in the database being stored in the form of connected tables (Bélanger, Van Slyke and Crossler, 2019). Tables in a RDBMS are related to each other by means of primary and foreign keys. RDBMSs are the most popular type of database used by modern integrated AISs. A primary key is the attribute, or combination of attributes, that uniquely identifies a specific row in a table. A primary key must not change and cannot be empty (null). A concatenated primary key is a primary key that consists of more than one attribute or field. All the fields together uniquely identify that row in the table. A foreign key is the attribute appearing in one table and is a primary key in another table. 8  Examples of rows, tables, attributes and keys in an RDBMS Figure 7 provides an example of a row (or tuple) in a table (or entity) in an RDBMS storing supplier information. Figure 8 illustrates an example of an attribute in the Supplier table. One example of an attribute shown in this figure is the suppAddress, which stores the supplier address. In Figure 9, an example of a relation between two tables, Supplier and SuppOrder is shown. Figure 7 - Example of a row in Table/Entity (the Supplier table) Figure 8 - Example showing attributes e.g. supplier address Figure 9 - Example of a relation (between Supplier and SuppOrder) Figure 10 shows the example of Supplier Number (suppNum) in the Supplier table as a primary key. It is a primary key since it is not null and it uniquely identifies a supplier, meaning that there cannot be two suppliers with the same suppNum. 9  Figure 10 - Example of a Primary key (Supp Num) Figure 11 shows an example of a foreign key where suppNum is a foreign key in the SuppOrder table linking to the suppNum primary key in the Supplier table. Figure 11 - Example of a Primary key (Supp Num) and Foreign key relationship Review Questions 1. Identify six trends that are accelerating the radical increase in volume and impact of online data. 2. What is an Accounting Information System and what benefit does/can it have for your company? 3. What is the difference between database and file-based legacy systems? 4. What is the purpose of a data dictionary? 10 Chapter 2 Principles and processes of good database design 2.1 Requirements for a “good” relational data model Relational databases are the most widely used type of database in enterprises today. The rules that must be adhered to in these databases are as follows: Each column in a row must be single-valued. Primary keys must be unique and cannot be null (Entity integrity rule). Every foreign key must either be null or have a value corresponding to the value of a primary key in another relation (Referential Integrity Rule). Each non-key column in a table must describe a characteristic of the object identified by the primary key. The value in every row of a specific column must be of the same data type. Neither column order nor row order are significant. 2.2 Types of anomalies that can occur and how to avoid them There are three main types of anomalies that can occur in a badly designed RDBMS. An anomaly can be loosely defined as an abnormality or error in the data stored in the database. An anomaly will occur when certain changes that need to be made to a database cannot be successfully made (Coronel, Morris, Rob, & Crockett, 2013). These are insert, update or delete anomalies. An insert anomaly results when trying to add information about one entity in the database without it being associated with another entity, e.g. trying to add transactions for a customer that is not created on the database. An update anomaly results when changes (updates) to data values are not correctly and consistently recorded. Examples of Anomalies Figure 12 illustrates an update and insert anomaly. If there are redundancies and you change the value in one of the fields an update anomaly will occur. If you want to add a supplier with suppNum SMI06 but SMI06 has no orders yet, then an insert anomaly will occur. A delete anomaly results when deleting a row of data pertaining to one entity and data in another entity that relates to the first entity is not deleted as well. For example, if an Inventory item were discontinued and hence removed from the database table, we would lose information on the orders/invoices associated with that inventory item as well. A good database system will prevent that from happening. Figure 13 illustrates an example of how MS Access prevents a delete anomaly. Figure 12 - Update and Insert anomaly example 11  Figure 13 – Example of preventing a delete anomaly How do you avoid these anomalies? There are two approaches: Normalisation to avoid redundancy; or A semantic modelling approach. 2.3 Normalisation A normalised database design approach starts with the assumption that everything is initially stored in one large table. A set of rules is followed to decompose that initial table into a set of normalised tables. The objective is to produce a set of tables in third-normal form (3NF) because such tables are free of update, insert, and delete anomalies. A normalised database has no redundancy and no anomalies. 2.4 The semantic modelling approach to database design A second approach to designing a database is to use semantic modelling and model the data using modelling techniques. Semantic modelling refers to using models that in themselves have meaning. The advantages of this over simply following normalisation rules are that it: Uses the designer’s knowledge about business processes and practices; it therefore facilitates efficient design of transaction processing databases. Results in a graphical model that explicitly represents information about the organisation’s business processes and policies and facilitates communication with intended users. Data modelling is the process of defining a DB to faithfully represent all aspects of the organisation, including interactions with the external environment. Data models are used for: designing databases; graphically documenting data; understanding existing data in an organisation; and showing what information should be stored in a database and the relationships between those entities. Different semantic models can be used to design the database of an AIS. Well-known data models are Entity Relationship Diagrams (ERDs), class diagrams and Resource Events and Agents (REA) data models. It is important to clearly understand the steps involved in developing an REA diagram for a transaction cycle. Cardinality refers to the nature of the relationship between two entities, i.e. how many instances of one entity can be linked to a specific instance of another entity. 12 There are three types of relationships: one-to-one (1:1), one-to-many (1: M) and many-to-many (M: N). These data models and the relationships between entities can reveal important information about the business activities and policies of the organisation being modelled. In addition, the design of these models can significantly influence the success (or failure) of any attempt at analysing the data for operational or strategic decision-making purposes. An REA model is an extension of an ERD and has three types of Entities: Resources that the organisation acquires and uses - has economic value to the organisation. Events in which the organisation engages - business activities about which management wants to collect information. Agents participating in these events - people and organisations participating in events & about whom information is desired for planning, control, and evaluation purposes. When creating a REA model, there are various rules for connecting entities: Rule 1: Each event is linked to at least one resource that it affects. Rule 2: Each event is linked to at least one other event. Rule 3: Each event is linked to at least 2 participating agents. Each accounting cycle can be described in terms of give-to-get economic duality relationships. Developing an REA diagram for a specific transaction cycle consists of three steps: 1. Identify the events about which management wants to collect information. 2. Identify the resources affected by the events and the agents who participated. 3. Determine the cardinalities between the relationships. Review Questions 1. Identify the requirements for a “good” relational data model. 2. Describe the two approaches used to avoid anomalies and give an example of each. 3. Is there a relationship between an accounting cycle and entities when creating/ developing a REA model? 13 Chapter 3: Data reporting and analytics 3.1 Types of information for the transaction cycles Businesses globally are facing a data explosion as digital images, email inboxes, and broadband connections increase. The AIS and its RDBMs can ensure the effective capturing (input) and storage of data, for the purposes of processing the data so that information that is useful to the users can be retrieved (output). One of management’s objective is to produce financial information that is valid, accurate and complete, and that can lead to the Annual Financial Statements being fairly presented (valid accurate and complete historical information). However, management of a company also requires information for analysing the business and making decisions that affect the future of the company. Therefore, we need to differentiate between operational or transactional and analytical information. Transactional information – encompasses all of the information contained within a single business process or unit of work, and its primary purpose is to support the performing of daily operational tasks. Analytical information – encompasses all organisational information, and its primary purpose is to support the performing of managerial analysis tasks. Organisations capture and store transactional information in databases and use it when performing operational tasks and repetitive decisions such as analysing daily sales reports and production schedules. Transactional information examples include withdrawing cash from an ATM, making an airline reservation and purchasing stocks. Analytical information includes transactional information as well as external organisational information such as market, industry, and economic conditions. The primary purpose is to support the performing of managerial analysis tasks and for decision support. Analytical information includes information related to trends, sales, product statistics, and future growth projections. An AIS is a subset of Enterprise Resource Planning (ERP) systems (e.g. Sage 200 Evolution) and has an operational database that is normalised to facilitate data capturing. Querying an operational database could take a very long time (e.g. to get a summary of all the invoices for a vendor for 5 years). Some ERP systems (including AIS) incorporate a data warehouse or Business Intelligence (BI) tool as a separate add-on, since there may be limitations to the analytical capability of the AIS tool. 3.2 Data warehouses, Business Intelligence (BI) and Business Analytics (BA) A data warehouse (DW) extends the transformation of data into information used for analysis. DWs are designed for querying and reporting (and NOT operational data e.g. RDBMS). A data warehouse provides the ability to support decision-making without disrupting the day-to-day operations. Data in a DW is usually aggregated (summarised, historical) and not necessarily normalised. A DW allows faster access to historical and summarised data. A data warehouse is a logical collection of information gathered from many different external (e.g. competitor information) and internal (e.g. sales and inventory) operational databases that support business analysis activities and decision-making tasks (Figure 14). The primary purpose of a data warehouse is to aggregate the different types of information (e.g. marketing, inventory and competitor information) throughout an organisation into a single repository for decision-making purposes (Baltzan, 2014). A data mart contains a subset of data warehouse information e.g. for a department, branch or business area of an organisation. 14  Figure 14 - Data Warehouse Model (Baltzan, 2014) Business Intelligence (BI) tools enable business users to receive data for analysis that is reliable, consistent, understandable and easily manipulated. BI not only supports the decision-making process but also allows businesses to have a better insight into their operations by applying data analysis techniques to their information (Ishikiriyama et al. 2015). Business Analytics (BA) makes use of data and quantitative analysis to support reporting and decision- making within an organisation. BA is a sub-field of BI that provides a more analytical approach, which is better suited to strategic sustainability management. Examples of BI and BA are in banking to predict bad loans, fraudulent credit card use or to determine if a customer qualifies for a loan. Whilst operational databases contain information in a series of two-dimensional tables (mostly transactional), in a data warehouse or data mart, information is multidimensional, i.e. it contains layers of columns and rows. A dimension is an attribute of information, e.g. customer or branch or product. Data analytics is concerned with extraction of actionable knowledge and insights from Big Data (Rajaraman, 2016). Big Data Analytics (BDA) refers to a method that uses efficient analytic techniques to discover hidden patterns, correlations, and other insights from Big Data (Oussous et al., 2018). Online Analytical Processing (OLAP) tools allow users to explore and analyse multidimensional data with varying levels of aggregation. OLAP enables functionality such as slice-and-dice, filtering, drill- down, aggregation and pivoting. A cube is the common term for the representation of multidimensional information. In Figure 15, Cube A represents store information, product information, and promotion information. Cube B represents a slice of information displaying Promotion II for all products at all stores. Lastly, Cube C represents a slice of information displaying Promotion III for Product B at Store 2. 15  Figure 15 - OLAP Cube (Baltzan, 2014) Review Questions 1. What information is needed to monitor and analyse a company’s performance? 2. What benefit does having a Business Intelligence tool add to an AIS or ERP system? 16 Chapter 4: Introduction to the principles of automation in an accounting context 4.1 Introduction The purpose of this Chapter/study unit is to understand the basic principles of automation, including identifying use cases and its underlying benefits and risk, in the context of an accounting information system. Learning outcomes: 1. Introduce the principles of automation. 2. Outline the benefits and risks associated with the automation of processes. 3. Identify processes that lend themselves to automation. 4. Evaluate the need for cognitive computing systems in the automation process. 5. Build a command that automates a process. The study unit is designed to provide students with an understanding of principles related to automation, including Robotic Process Automation (RPA) and its relevance in the context of accounting, financial reporting and related disciplines. This will include a practical application of the principles learned through the automation of various tasks using the UiPath tool, equipping students with practical experience in using RPA technology to improve and streamline various tasks. Through this project, students will deepen their knowledge of RPA concepts, understand its benefits, and learn how to apply it to enhance relevant processes. The project aims to offer a hands-on learning experience that will enable students to gain knowledge and practical skills on RPA technology and its application in the accounting field. Overall, the project seeks to prepare students to leverage RPA technology effectively in their future careers. NB: Please refer to the glossary terms for Chapter 4. 4.2 Automation in Accounting Examples of Automation in Accounting (Gartner Inc., 2022):  Reporting automation – the automatic generation of timely reports, allowing users to spend time analysing the data and adding value rather than preparing the reports and finding the relevant information from various sources.  Robotic Process Automation (RPA – see below)  Intelligent Automation involving more complex human-like decisions An example of intelligent automation which is applied by certain cloud-based accounting service providers is as follows: Problem: Capturing supplier invoices into the accounting system. Supplier Invoices contain unstructured data (they have different designs, and the data fields are not in the same place for each invoice, the format of data is not uniform (e.g. dates, VAT). Therefore judgement is required when reading and capturing these invoices. They are also received from multiple sources. Solution: A unique email address is provided in the supplier invoice function. Invoices are emailed to this address and directly routed to the draft invoices folder. The software uses optical character recognition to identify details such as supplier name, amount, VAT etc. The accounts payable clerk checks (validates), adds any final detail, corrects any errors, and approves the invoice for capturing. This facilitates the “audit trail” of the transaction as the electronic invoice is automatically attached and stored, which allows for later retrieval for control/audit purposes. 17 4.3 Benefits and Limitations of automation and RPA Benefits of automation and RPA:  Accuracy of processing high volumes/monotonous tasks (does not get tired or make human errors).  Faster processing of repetitive tasks.  Can interact with other software applications.  Can work autonomously. However, automation and RPA have limitations, including: Automation is not suitable for in depth tasks which involve complexity and judgement. Cannot adapt to changes or uncertainty (such as changes made to external websites) (Kokina & Blanchette, 2019). Can only process information in digital form. Errors could become systematic. Fragmented processes are difficult to automate (for example, where invoice processing is performed differently for each country) (Kokina & Blanchette, 2019). Importance of Governance in automation: As in any environment, the governing body must ensure that a sound RPA control environment is maintained, including appropriate policies and procedures, risk assessment and internal controls. Principle 12 of the KING IV Report states that: The governing body should govern technology and information in a way that supports the organisation setting and achieving its strategic objectives. 4.4 Robotic Process Automation (RPA) What is Robotic Process Automation (RPA)? Robotic Process Automation is a software technology that creates, manages, and operates software robots. These robots imitate human actions while interacting with digital systems and software. The bots can easily: Recognise data. Understand information displayed on the screen. Navigate systems. Execute a variety of pre-determined actions. RPA bots are distinguished from humans as they can complete these tasks more quickly and consistently without the need for rest. RPA is widely used in various fields such as Finance, Healthcare, Education, and Production. Its primary aim is to streamline repetitive and tedious tasks that humans usually carry out, allowing them to concentrate on more critical and people-oriented activities. By taking advantage of software solutions like UiPath Studio, businesses can integrate RPA seamlessly into their operations, which can save time, money, and minimise the risk of mistakes. It is critical to adhere to specific standards to achieve efficient automation. The creation, development, and use of automated systems are guided by these concepts. 1. Simplicity: Automation systems are straightforward and easy to use, requiring minimal training and intervention. 2. Consistency: Automated systems produce consistent results with minimal variation, avoiding errors and defects. 3. Flexibility: Automation systems are adaptable to different situations, allowing for modifications and adjustments as needed. 4. Reliability: Automated systems are dependable and operate continuously with little or no downtime. 5. Scalability: Automation systems can expand to accommodate increased demand and growth. 18  6. Cost-effectiveness: Automated systems are cost-efficient, delivering value for money and justifying the investment made in them. Many businesses have adopted automation tools, including RPA to improve their business operations, making it crucial for individuals in the business industry to have knowledge of RPA. It is advantageous to understand RPA as it can help businesses increase efficiency, reduce costs, and improve the quality of work by automating repetitive, rule-based tasks. In ROV202, we will use the UiPath platform to practically demonstrate how a reporting task can be automated. 4.5 RPA vs Artificial Intelligence – what is the difference? According to CFB-Bots.com, “RPA is a software robot that mimics human actions, whereas AI is the simulation of human intelligence by machines.” 1 Follow the link to read further on this interesting topic (voluntary self- study). The ROV202 project will use RPA to automate a series of human actions rather than simulating human intelligence. Therefore, we will not require cognitive computing systems for the tasks that we will perform. 1 htps://www.c -bots.com/single-post/2018/04/09/the-difference-between-robo c-process-automa on-and-ar ficial- intelligence 19 Appendix A: We asked Chat GPT to create a 300-word summary (referenced) of the impact of Automa on on the Accoun ng Industry. This presented below unedited. Title: The Impact of Automa on on the Accoun ng Industry: A Paradigm Shi in Efficiency and Roles Introduc on: Automa on has emerged as a transforma ve force in various industries, including accoun ng. The advent of advanced technologies such as ar ficial intelligence (AI), machine learning, and robo c process automa on (RPA) has significantly impacted the accoun ng profession. This summary explores the key implica ons of automa on on the accoun ng industry, highligh ng the paradigm shi in efficiency and the evolving roles of accountants. Efficiency and Accuracy: Automa on has revolu onized repe ve and me-consuming tasks in accoun ng, such as data entry, reconcilia on, and report genera on. AI-powered so ware can swi ly process vast amounts of financial data, reducing human error and improving accuracy. By automa ng rou ne tasks, accountants can focus on value-added ac vi es such as data analysis, strategy formula on, and decision-making, thereby enhancing produc vity and efficiency. Streamlined Processes and Cost Reduc on: Automa on has enabled the streamlining of accoun ng processes, resul ng in improved workflow management. Advanced so ware can automate the en re financial repor ng cycle, from data collec on to financial statement prepara on. This automa on minimizes the need for manual interven on, accelerates processing mes, and reduces costs associated with labor-intensive tasks. Moreover, cloud-based accoun ng systems facilitate real- me collabora on and data accessibility, enhancing overall opera onal efficiency. Evolu on of Accountant Roles: Automa on has prompted a shi in the roles and responsibili es of accountants. As rou ne tasks are automated, accountants are required to develop new skills and exper se to adapt to the changing landscape. They are increasingly becoming strategic advisors, leveraging their analy cal abili es to provide insights and interpret complex financial informa on. Accountants now play a crucial role in leveraging automa on tools, ensuring data integrity, and implemen ng effec ve internal controls to mi gate risks. Challenges and Opportuni es: While automa on offers numerous benefits, it also presents challenges to the accoun ng industry. Concerns around job displacement have surfaced, with some fearing that automa on may render accountants obsolete. However, the integra on of technology provides opportuni es for upskilling and professional growth. Accountants can acquire proficiency in data analy cs, informa on technology, and business intelligence, allowing them to leverage automa on tools effec vely and enhance their value proposi on. Conclusion: Automa on has brought about a significant transforma on in the accoun ng industry, revolu onizing processes, enhancing efficiency, and redefining accountant roles. The integra on of advanced technologies has not only improved accuracy and produc vity but also enabled accountants to focus on strategic ini a ves. While challenges exist, the ongoing adapta on and upskilling of accountants will ensure they remain indispensable in the digital age. Embracing automa on as a partner, rather than a threat, will enable the accoun ng industry to leverage its full poten al and thrive in a rapidly evolving business environment. References: 1. Chae, Y., Koh, C. E., & Prybutok, V. (2020). Audit in the digital age: The effect of automa on on audit quality and audit fees. Journal of Accoun ng and Public Policy, 39(1), 106611. doi:10.1016/j.jaccpubpol.2019.106611 2. McCracken, M. J., McAllister, T. H., & Masli, A. (2019). The impact of robo c process automa on on the audit profession. Journal of Informa on Systems, 33(3), 59-72. doi:10.2308/isys-52460 20 3. Spathis, C., Zambon, S., & Yazdifar, H. (2020). Accoun ng informa on systems, automa on and the redefined roles of accountants. Accoun ng, Audi ng & Accountability Journal, 33(3), 673-699. doi:10.1108/AAAJ-06-2019-4026 Glossary Chapter 1-3: Glossary of Key Terms: Analytical information encompasses all organisational information, and its primary purpose is to support the performing of managerial analysis tasks. Anomaly can be loosely defined as an abnormality or error in the data stored in the database. Big Data can be defined as information assets that are characterised by such a high volume, velocity and variety that specific technology and analytical methods are needed for its transformation into value. Big Data Analytics (BDA) refers to a method that uses efficient analytical techniques to discover hidden patterns, correlations, and other insights from Big Data. Business Analytics (BA) makes use of data and quantitative analysis to support reporting and decision- making within an organisation. BA is a sub-field of BI that provides a more analytical approach, which is better suited to strategic sustainability management. Business Intelligence (BI) tools enable business users to receive data for analysis that is reliable, consistent, understandable, and easily manipulated. Cardinality refers to the nature of the relationship between two entities. Concatenated primary key is a primary key that consists of more than one attribute or field. Database Management System (DBMS) can be referred to as middleware that sits in the middle of the application program and the database. The DBMS is responsible for executing any request from the front-end application and retrieving the data from the physical database which could be on a server. Data analytics is concerned with extraction of actionable knowledge and insights from Big Data. Data dictionary contains information about the structure of the database. Data model is an abstract representation of the contents of a database. Data modelling is the process of defining a database to faithfully represent all aspects of the organisation, including interactions with the external environment. Data schema is a map of a logical structure of a database. Data warehouse (DW) is a logical collection of information gathered from many different external 21 (e.g., competitor information) and internal (e.g., sales and inventory) operational databases that support business analysis activities and decision-making tasks. Electronic data interchange (EDI) is sometimes used to communicate data/information from the organisation to their trading partners electronically and in most cases automatically. Field represents a characteristic of the thing (e.g. supplier name, supplier address) in a table (seen as a column). Foreign key is the attribute appearing in one table, which is the primary key in another table. Inconsistency refers to the case of the same data kept at different places but not matching. Information overload is the result of being faced with more information than we can effectively process. The more information we must sift through, the less attention we have to devote to other tasks. Non-integration is not a desirable situation as all data from different sources should be combined to give users a unified view of these sources. Online Analytical Processing (OLAP) tools allow users to explore and analyse multidimensional data with varying levels of aggregation. OLAP enables functionality such as slice-and-dice, filtering, drill- down, aggregation and pivoting. Primary key is the attribute, or combination of attributes, that uniquely identifies a specific row in a table. Record is a set of fields (or attributes) that all relate to the same thing (e.g. supplier) in a table (seen as a row). Redundancy refers to the same data being stored in two or more separate places without adding value (ie no new information). Relational Database Management System (RDBMS) is a DBMS with a relational data model that represents everything in the database being stored in the form of connected tables. Semantic modelling refers to using models that in themselves have meaning. Structured Query Language (SQL) is a programming language that allows the user to create the database and then manipulate and extract useful information from the database through queries. Entity/ Relation can be any object you want to store attributes about. For example, a supplier is an entity that has a name, address, phone number, account number and so on. You can think of the tables as an Excel file with columns and rows. Tables are also sometimes referred to as relations. Tables are made up of records (rows) and fields (columns). Transactional information encompasses all of the information contained within a single business process or unit of work, and its primary purpose is to support the performing of daily operational tasks. 22 Chapter 4: Glossary of Key Terms: Automation: Automation is defined as a technology concerned with performing a process by means of programmed commands combined with automation feedback control to ensure proper execution of instructions. Automation allows software or hardware components to carry out tasks without human intervention or as little human intervention as possible. For example, a self-driving car is considered to be in the field of automation. Artificial Intelligence: Refers to the simulation of human intelligence in machines that are programmed to think and act like humans. An example of artificial intelligence is ChatGPT which uses natural language processing to create humanlike conversational dialogue. To demonstrate this, we asked Chat GPT to create a 300-word summary (referenced) of the impact of Automation on the Accounting Industry. See Appendix A for the response. Use case: Is a written description of how users will perform a task on a system or website. It is a methodology/way of clearly describing the system/application requirements and all the actors involved. For example, a use case would describe through a series of written steps describing how a student would login into Moodle and access their learning material. Cognitive computing systems: Used to describe AI systems that simulate human thought. Human cognition involves real-time analysis of real-world environment, context, intent, and many other variables that inform a person’s ability to solve problems. For example, Siri is a cognitive computing system because it learns and makes decisions in humanlike manner. Command: A command is a specific instruction given to a computer application to perform some kind of task or function. It’s a way to communicate with your computer and tell it what you want. Automation in the accounting field Processes that lend themselves to automation include repetitive, rule-based tasks where consistent and error- free processing is required, especially tasks where humans may lose concentration or become fatigued. Automation also allows for improved evidence and documentation (Audit Trail). 23