Information and Technology Systems Overview PDF

Information and Technology Systems Overview of lectures António Manuel Valente de Andrade 23 September 2024 Version automatically translated from Portuguese. No human review yet! There are four fundamental technologies1: Materials o Heavy industry like chemicals, metallurgy, automobiles, textiles, and plastics. Life o Life and the well-being of human beings, such as biotechnology, health, sport, food and agriculture. Energy o Electricity, coal, oil, natural gas, renewables, and transport industries. Information o Resources are not finite and we're going to find out a bit more. 1 Helder Coelho, 1986 Content Content.......................................................................................................................................... 3 Table of Figures............................................................................................................................. 8 Table Index.................................................................................................................................. 10 Introduction................................................................................................................................ 12 [L|1] From Data to Information.................................................................................................. 16 The Company and the Environment....................................................................................... 16 Global Approach...................................................................................................................... 17 Origin and Nature of Data....................................................................................................... 18 Example of Data Transformation............................................................................................ 21 Information System................................................................................................................. 23 Information System Logical Architecture................................................................................ 25 Pedagogical Activity................................................................................................................ 27 Other Teaching Elements........................................................................................................ 28 Challenge................................................................................................................................. 28 Synthesis Lecture 1.................................................................................................................. 31 [L|2] Data Management.............................................................................................................. 32 The evolution of the spreadsheet........................................................................................... 32 Elementary use of Excel.......................................................................................................... 33 Elementary Statistical Functions............................................................................................. 36 Logical Functions..................................................................................................................... 38 Database functions.................................................................................................................. 42 SEQUENCE............................................................................................................................... 45 GROUPBY................................................................................................................................. 46 VISIO and Excel........................................................................................................................ 47 Excel and PDF.......................................................................................................................... 47 Charts...................................................................................................................................... 47 Read Sensor Data.................................................................................................................... 49 Other Didactic Elements......................................................................................................... 49 Synthesis Lecture 2.................................................................................................................. 51 [L|3] Data Management.............................................................................................................. 52 Introduction............................................................................................................................ 52 Filter........................................................................................................................................ 52 Large........................................................................................................................................ 53 Rank......................................................................................................................................... 53 Unique..................................................................................................................................... 54 Sort.......................................................................................................................................... 54 Rept......................................................................................................................................... 54 Forecast.Linear........................................................................................................................ 55 Randbetween.......................................................................................................................... 55 Round...................................................................................................................................... 55 Droplist.................................................................................................................................... 56 IFError...................................................................................................................................... 57 VLookup e XLookup................................................................................................................. 57 Pivot and Power Pivot Table................................................................................................... 59 Image....................................................................................................................................... 63 Check Box................................................................................................................................ 64 Text Functions......................................................................................................................... 66 TextSplit............................................................................................................................... 66 FIND and MID...................................................................................................................... 66 UNICHAR and ROW............................................................................................................. 68 Novas Funções......................................................................................................................... 68 REGEX new REGular EXpression (as of June 2014 not yet available in Portugal)..... 68 Other Didactic Elements......................................................................................................... 69 Synthesis Lecture 3.................................................................................................................. 70 [L|4] Data Management.............................................................................................................. 71 The context.............................................................................................................................. 71 Database Operations............................................................................................................... 71 Structure.................................................................................................................................. 71 Normalisation.......................................................................................................................... 72 The interface........................................................................................................................... 76 Indexing................................................................................................................................... 78 Exploring Contexts.................................................................................................................. 79 Challenge................................................................................................................................. 79 Other Teaching Elements........................................................................................................ 79 Synthesis Lecture 4.................................................................................................................. 80 [L|5] Data Management.............................................................................................................. 81 Introdution.............................................................................................................................. 81 Access Functionalities............................................................................................................. 81 Case Study............................................................................................................................... 82 Queries................................................................................................................................ 82 Forms................................................................................................................................... 85 Other Teaching Elements........................................................................................................ 86 Synthesis Lecture 5.................................................................................................................. 87 [L|6] Business Information Systems........................................................................................... 88 CEO, Synapxe........................................................................................................................... 88 Introdution.............................................................................................................................. 88 Business Information Systems................................................................................................ 88 ERP and Other IT Applications................................................................................................. 89 Customer Relationship Management..................................................................................... 90 Supply Chain Management..................................................................................................... 90 Business Intelligence............................................................................................................... 91 Project Management System.................................................................................................. 91 Quality Management System.................................................................................................. 92 Adopting an ERP...................................................................................................................... 92 ERP Business Solutions............................................................................................................ 94 External Communication......................................................................................................... 94 To Reflect................................................................................................................................ 96 Other Teaching Elements........................................................................................................ 96 Synthesis Lecture 6.................................................................................................................. 97 [L|7] Control and Decision Making............................................................................................. 98 Background to the concept..................................................................................................... 98 Introduction to Power BI......................................................................................................... 98 Reports and Dashboards......................................................................................................... 99 Information Sharing.............................................................................................................. 103 Getting Started...................................................................................................................... 105 Advanced Functionalities...................................................................................................... 105 Elementary Introduction to Calculus.................................................................................... 105 Other Teaching Elements...................................................................................................... 110 Synthesis Lecture 7................................................................................................................ 111 [L|8] Management of Information Systems............................................................................. 112 IS Management Activities...................................................................................................... 112 Modelling and Automatic Code Generation......................................................................... 115 Case Study............................................................................................................................. 115 Communication between management and the CIO........................................................... 116 Application Examples............................................................................................................ 117 Flowchart........................................................................................................................... 117 Structured Language......................................................................................................... 118 IS user role............................................................................................................................. 119 Essential Steps for Creating an APP...................................................................................... 124 Other features....................................................................................................................... 125 Evolution of the CIO's role.................................................................................................... 125 Other Teaching Elements...................................................................................................... 127 Synthesis Lecture 8................................................................................................................ 128 [L|9] Technological Systems..................................................................................................... 129 Sustainability......................................................................................................................... 129 Concept and Issues............................................................................................................ 129 Information Technology and Sustainability...................................................................... 130 Technological Systems.......................................................................................................... 131 Process Technologies............................................................................................................ 132 Context.............................................................................................................................. 132 The Manager's Focus......................................................................................................... 132 What technology can do for business?............................................................................. 133 Materials Processing Technology.......................................................................................... 133 Information Processing Technologies................................................................................... 134 Customer Interaction Technologies...................................................................................... 135 Process Technology Assessment........................................................................................... 135 Implementing Process Technology....................................................................................... 135 Technological Innovation...................................................................................................... 136 Innovation and Product..................................................................................................... 136 Innovation Concept........................................................................................................... 137 Engines of Innovation........................................................................................................ 137 Process Innovation............................................................................................................ 138 Organizational Innovation................................................................................................. 138 Marketing Innovation........................................................................................................ 138 Innovation for Social Change............................................................................................ 138 Risks of not innovating...................................................................................................... 138 Risk of Innovation.............................................................................................................. 139 Product or Service Design Phases......................................................................................... 139 New Product or Service Design Project................................................................................. 139 Activities................................................................................................................................ 140 Other Teaching Elements...................................................................................................... 140 Synthesis Lecture 9................................................................................................................ 142 [L|10] From the Information Society to Digital Transformation............................................... 143 What is the Information Society?......................................................................................... 143 Sharing Knowledge and Progress.......................................................................................... 144 Artificial Intelligence.............................................................................................................. 145 Challenges............................................................................................................................. 147 IS Infrastructure and Security................................................................................................ 148 Threats.................................................................................................................................. 150 Summary............................................................................................................................... 152 Other Teaching Elements...................................................................................................... 153 Synthesis Lecture 10.............................................................................................................. 154 List of Tutorials.......................................................................................................................... 155 Spreadsheet – Excel.............................................................................................................. 155 DataBase – Access................................................................................................................. 156 Power BI................................................................................................................................ 156 Power APPs........................................................................................................................... 156 Power Platform..................................................................................................................... 157 MindMaps............................................................................................................................. 157 Bibliografia | Bibliography........................................................................................................ 157 Table of Figures Figure 1 – Structure of Teaching Resources in ITS...................................................................... 12 Figure 2 – Support book in Portuguese....................................................................................... 13 Figure 3 – Resources for studying............................................................................................... 13 Figure 4 – The Company and its Context.................................................................................... 16 Figure 5 – From Data to Information.......................................................................................... 17 Figure 6 – From Data to Knowledge............................................................................................ 18 Figure 7 – Data Types, Storage and Processing........................................................................... 19 Figure 8 – Examples of Data Types.............................................................................................. 20 Figure 9 – Diversity in Data Sources............................................................................................ 20 Figure 10 – Data collection by Internet of Things (IoT)............................................................... 21 Figure 11 – Example of Transforming Data into Information..................................................... 22 Figure 12 – Information for Different Management Levels........................................................ 23 Figure 13 – Basic Architecture of an Information System........................................................... 25 Figure 14 – Updated IS Architecture........................................................................................... 26 Figure 15 – Type of Information.................................................................................................. 27 Figure 16 – Simulation of the Context of Exploitation of Management Tools............................ 29 Figura 17 – Copilot in Microsoft 365........................................................................................... 30 Figure 18 – Data connection and import.................................................................................... 33 Figure 19 – Structure of a Data Sheet......................................................................................... 34 Figure 20 – Introduction to Spreadsheets................................................................................... 35 Figure 21 – Using Sparkline......................................................................................................... 35 Figure 22 – Elementary Statistical............................................................................................... 36 Figure 23 – Application of Statistical Functions.......................................................................... 37 Figure 24 – Absolute Addresses.................................................................................................. 37 Figure 25 – Result of the various calculations proposed............................................................ 38 Figura 26 – Função IF.................................................................................................................. 39 Figura 27 – IF embedded............................................................................................................. 39 Figure 28 – Implementation of chained IF.................................................................................. 40 Figura 29 – Uso do IF AND........................................................................................................... 41 Figura 30 – Função IFS................................................................................................................. 41 Figure 31 – SUMIF Function........................................................................................................ 42 Figure 32 – CountIF Function...................................................................................................... 42 Figure 33 – From Data to Information with DataBase Functions............................................... 43 Figure 34 – Information Construction......................................................................................... 43 Figure 35 – DSUM function syntax.............................................................................................. 44 Figure 36 – DSUM Results and Interpretation............................................................................ 44 Figure 37 – DGET Implementation.............................................................................................. 45 Figure 38 – Inserting Objects in Excel......................................................................................... 47 Figure 39 – Sales Table................................................................................................................ 52 Figure 40 – LARGE Function........................................................................................................ 53 Figure 41 – RANK Function.......................................................................................................... 53 Figure 42 – Rank Function Implementation................................................................................ 54 Figure 43 – Unique and Sort Implementation............................................................................. 54 Figure 44 – Rept Function Implementation................................................................................ 55 Figure 45 – FORECAST Function.................................................................................................. 55 Figure 46 – ROUND Function...................................................................................................... 56 Figure 47 – Droplist..................................................................................................................... 56 Figure 48 – List chaining.............................................................................................................. 57 Figure 49 – IFERROR Function..................................................................................................... 57 Figure 50 – VLookup Implementation......................................................................................... 58 Figure 51 – VLOOKUP Function................................................................................................... 58 Figure 52 – XLOOKUP Function................................................................................................... 59 Figure 53 – Transaction Table..................................................................................................... 59 Figure 54 – Pivot Table feature................................................................................................... 60 Figure 55 – PivotTable with Slicers.............................................................................................. 61 Figure 56 – Relationships between tables.................................................................................. 61 Figure 57 – Power Pivot Menu.................................................................................................... 62 Figure 58 – Create Relationships................................................................................................. 62 Figure 59 – Power Pivot with two tables.................................................................................... 63 Figure 60 – IMAGE Function........................................................................................................ 64 Figure 61 – Using multiple Excel resources................................................................................. 64 Figure 62 – Drawing the Check Box............................................................................................. 65 Figure 63 – Conditional Formatting............................................................................................ 66 Figure 64 – TextSplit.................................................................................................................... 66 Figure 65 – FIND and MID........................................................................................................... 67 Figure 66 – Word Processing Functions...................................................................................... 67 Figure 67 – UNICHAR ad ROW..................................................................................................... 68 Figure 68 – Data Hierarchy.......................................................................................................... 72 Figure 69 – Structure at 1FN....................................................................................................... 73 Figure 70 – Structure at 2FN...................................................................................................... 73 Figure 71 – Structure at 3FN....................................................................................................... 74 Figure 72 – New Data Structure.................................................................................................. 75 Figure 73 – Book Data Base......................................................................................................... 76 Figure 74 – Rudimentary interface for accessing management functions................................. 77 Figure 75 – Software-mediated database reading and writing.................................................. 77 Figure 76 – Layered structure for exploring a database............................................................. 78 Figure 77 – Applications for Reading and Analyzing Relational Data......................................... 79 Figure 78 – DB structure............................................................................................................. 82 Figure 79 – Query structure........................................................................................................ 83 Figure 80 – Query with parameter passing................................................................................. 83 Figure 81 – Statistics queries....................................................................................................... 84 Figure 82 – Action Query............................................................................................................. 84 Figure 83 – Activating an Action Query....................................................................................... 84 Figure 84 – Calculation on Form................................................................................................. 85 Figure 85 – Information Production and Consumption.............................................................. 89 Figure 86 – Power BI architecture............................................................................................... 91 Figure 87 – Project Management................................................................................................ 92 Figure 88 – Summary of the presence of IT in Economic Units.................................................. 94 Figure 89 – Basic Data Analysis Architecture............................................................................ 100 Figure 90 – View of Table Relationships in Power BI................................................................ 101 Figure 91 – Power BI Operating Environment.......................................................................... 101 Figure 92 – Database with three tables.................................................................................... 102 Figure 93 – Reports and Dashboad........................................................................................... 103 Figure 94 – Data Storytelling..................................................................................................... 103 Figure 95 – Power Point (Menu Insert)..................................................................................... 104 Figure 96 – Visio associated with Spreadsheet......................................................................... 104 Figure 97 – App with Charts and a Dashboard.......................................................................... 105 Figure 98 – Three-table structure for analysis.......................................................................... 106 Figure 99 – Start environment in Power BI Desktop................................................................. 106 Figure 100 – Database Structure (Relational Model)................................................................ 107 Figure 101 – Environment for creating a calculation column................................................... 107 Figure 102 – Environment after pressing "New Column"......................................................... 107 Figure 103 – Calculation Expression.......................................................................................... 108 Figure 104 – Result of Calculation and Formatting of "Value" Data......................................... 108 Figure 105 – Reporting Environment........................................................................................ 109 Figure 106 – Quick Measure Creation....................................................................................... 109 Figure 107 – Elementary Report............................................................................................... 110 Figure 108 – IS Management Activities..................................................................................... 114 Figure 109 – CIO Supervision.................................................................................................... 114 Figure 110 – Flowchart examples............................................................................................. 118 Figure 111 – Microsoft Power Platform.................................................................................... 120 Figure 112 – App Designed in PowerApps................................................................................ 121 Figure 113 – Image folder management................................................................................... 122 Figure 114 – Folder with Excel Table Name.............................................................................. 122 Figure 115 – Creating Pages and Choosing the Order of Appearance...................................... 123 Figure 116 – Play Button........................................................................................................... 123 Figure 117 – Navigation between screens................................................................................ 124 Figure 118 – Structure of an App with access to two different files......................................... 125 Figure 119 – Customer and Technology Interaction................................................................. 135 Figure 120 – Creativity, Innovation and Invention.................................................................... 137 Figure 121 – Table of Activities and Project Network............................................................... 139 Figura 122 – Publicity Books in Portuguese Editions................................................................ 145 Figure 123 – Generative AI Operating Model........................................................................... 147 Figure 124 – The IT Infrastructure Ecosystem........................................................................... 149 Figure 125 – Security, Privacy and Ethics.................................................................................. 152 Table Index Table 1 – Structure of Chapters and Lectures............................................................................. 13 Table 2 – Structure of a Table in a Database.............................................................................. 71 Table 3 – Table of Orders............................................................................................................ 73 Table 4 – Book Table................................................................................................................... 74 Table 5 – Product records (Excel table in OneDrive)................................................................. 121 Table 6 – Engines of Innovation................................................................................................ 137 How you gather, manage, and use information determines whether you win or lose. Bill Gates Business at the Speed of Thought. 1999 With the advent of Generative Artificial Intelligence (AI), we realize that we are entering a new cycle of building a high-tech society with special challenges in terms of academic and scientific integrity so as not to commit plagiarism or other types of fraud. But also realizing that obtaining results via AI can be conditioned by the algorithm's prejudices or other forms of bias in the data, its analysis and the results suggested. On another, more personal level, we must also realize the threats posed by sophisticated disinformation, via the creation of false information based mainly on text, images, and videos through digital, often additive, processes. A student of this course must be aware of this reality to behave ethically and legally when using technological resources. Introduction The Information and Technology Systems (ITS) course has a structure of 10 Lectures lasting 1h30 and 14 Workshops lasting 3 hours. The operating model is shown in Figure 1. Figure 1 – Structure of Teaching Resources in ITS For the Lectures, the teaching support consists of: Book (Figure 2); Lecture slides; Lesson summary in video(s); Short thematic videos (e.g. ERP, CRP, etc.); Narrative and illustrated lesson summaries (this document); Formative tests on the Online Campus. Workshops: Class Guide setting out and explaining the practical activities; Exercise support files; Exercise resolution (published on Friday afternoons); Tutorials (listed at the end of this document); Some formative tests to prepare for the assessment moments. To manage personal performance, each student receives a weekly individual report card with their results compared to the rest of the class. Figure 2 – Support book in Portuguese The book in Portuguese (Figure 2) structures the topics in six chapters, which followed the three- hour Lectures and which, after the change to a 1.5-hour Lecture, have the distribution shown in Table 1. The components relating to the detail of some software applications are more up-to- date and developed in this document, in the slides, videos and tutorials. The more conceptual aspects are more developed in the book. Chapters Book Current Plan 1 Technologies and Business L1 2 Business Management Software L6 3 Data Management L2, L3, L4, L5 4 Design, Development and Management of Information Systems L8 5 Technological Systems L9 6 Society and Technology L7, L10 Table 1 – Structure of Chapters and Lectures To summaries, as shown in Figure 3, you can follow the Lectures with the support of the book (in Portuguese), this summary, the slides and the videos that accompany each lesson. Figure 3 – Resources for studying Depending on your study preference and learning style, you have different resources that complement each other. There are also formative tests to prepare for the lesson in advance. At the end of this document, you will find a list of available tutorials. Nowadays, Information Technologies and Systems are not a differentiating factor in business competitiveness because they are identical for companies in the same area and of a similar size, so the human factor prepared to take advantage of the technology as a Power or Super User is decisive: With new professional and personal skills for interacting with complex technological systems. Open to the relevance of learning for permanent updating. Sensitivity to a technologically rooted culture in the context of managing people in organizations. Focus on the quality of technology-based products, services, and organizational processes. It becomes clear WHY to learn with this stimulus and motivation to build the future, mastering new and useful skills. But it also identifies learning WHAT from a perspective of simulated and applied practice. And HOW to learn, benefiting from different learning styles and different forms of formative and summative assessment that are close to reality. The map on the next page shows a very brief overview of the relationship between Lectures (L) and Workshops (WS). In the Lectures, concepts and techniques are presented and in the WS, the software is explored. Lecture 1 | From Data to Information [L|1] From Data to Information “The goal is to turn data into information and information into understanding.” Carly Fiorina The Company and the Environment To understand the importance of the theme ‘From Data to Information’ in the context of organizations, let's reflect on it briefly, but in an individual context. We can therefore ask ourselves: How do data and information affect the decisions we make in our daily lives? How can data and information analysis help us better understand the world around us? What is the importance of visualizing data and information design to help make decisions? How are advances in technology changing the way we collect, store, and use data and information? An economic unit is asked these and other questions about its internal organization and the context in which it operates (Figure 4). Figure 4 – The Company and its Context In this sense, we must first realize the difference between data, information and knowledge and not forget the ethical dimension that must be involved in collecting data and sharing information. Digitalization is currently intense because it is possible to connect to technological systems from anywhere and on any device. Therefore, personal or professional information is mediated by technology and less on paper. Information and Technology Systems | 16 Lecture 1 | From Data to Information So, we have information internal to the company (corporate culture, standards, leadership,...) and information from the inside out that projects the organization’s image (reputation, social responsibility,...) and from the outside in (customers, suppliers, markets, government policies,...). In ‘digital emigrant’ companies, there is usually a difficult path to get them to adopt the use of technology in their management processes at an advanced stage, while in ‘digital native’ companies, technology is the reason for their business, so its adoption by top management is a natural process. The way in which a company adopts technology differentiates whether it is at a stage of digital transformation or just digitization (which in the 20th century was called computerization), i.e. doing the same thing as before, only with different means. There is no real TRANSFORMATION! Global Approach In a business context, the origin of data is very heterogeneous. With the growth of digitalization, which is the result of the evolution of technology, we are increasingly connected and able to store and consult various types of data. In a business environment and depending on the type of company, we have internal data from, for example, human resources management, finance, and accounting. But also, from project management and supply chain management. In addition to this data, many of the company's employees use email and social media resources for marketing activities. The processing of data, to convert it into information, can be human or developed by machines. Humanity developed computers to collect, store and process data, competing directly with human labour (Figure 5). Figure 5 – From Data to Information Once the data has been validated and processed, it becomes information. This information is needed to monitor what is happening in the company. It is also the basis for decision-making. In fact, information helps to reduce our degree of uncertainty about what we want to know or act on. Data >> [Processing] >> Information >> [Analysis] >> Knowledge >> [Synthesis] Wisdom Information and Technology Systems | 17 Lecture 1 | From Data to Information The Latin word informare, from the verb in-formo, means imposing a form, with the aim of instructing and improving. So, in-form-action is action that in-forms and is therefore transformative and capable of being the difference that makes a difference2. Data collection and storage is technically complex and very sophisticated. The central concerns are security, quality, accessibility, and storage. In colloquial language, however, the concepts of data, information and knowledge are often confused. As we've said, data is the raw material used to produce information, through human or machine intervention, and knowledge is the information perceived by individuals according to their culture, experience, and intelligence. In today's context we live with technological information, so called because of the strong mediation of technology in the production, storage and sharing of information. Figure 6 details some of the operations on data to transform it into information and operations on information to turn it into knowledge. Figure 6 – From Data to Knowledge Source: Gestão de Empresas na era do Conhecimento (página 181) Origin and Nature of Data To delve a little deeper into this topic, which gives us the means to control activity and support our decision making, let's briefly reflect on the origin and nature of data. We know that data comes from different sources. Historically, IT began by storing and processing structured data that was internal to the organization. Its storage architecture evolved from a type of file to stabilize it in relational databases. There are now variants to support new needs, such as NoSQL systems. In fact, there is now unstructured data that is stored and processed, as is clearly the case with messages on social platforms, typically based on text, sound and images, but also emails, for example, exchanged with customers. This data is particularly processed by an organization’s marketing department. Artificial Intelligence has an important role to play in the future of this treatment, which involves, for example, sentiment analysis. Companies are increasingly automating the data collection process, bringing the collection time down to zero. Depending on the size of the company, data may have to be stored in special warehouses called data warehouses. These are systems optimized for data analysis, with different access profiles and different ways of representing the information produced. They 2 As Gregory Bateson say in 1979 Information and Technology Systems | 18 Lecture 1 | From Data to Information have the capacity to manipulate and analyze large volumes of data, specifically using On-Line Analytical Processing (OLAP) techniques. They are also known as OLAP Cubes as a metaphor for the ability to analyze data from multiple perspectives, just as the faces of a cube are revealed. The information produced has different forms of representation depending on whether it is intended for control or decision-making. Detailed reports and graphs are the most common forms of information distribution. Figure 7 shows three examples of types of data, their storage and processing, which results in information for those who control and make decisions. Figure 7 – Data Types, Storage and Processing The organization currently collects data from the environment and internal data from its activity (remember Figure 4). But typically, to be well governed, a company needs to be aware of the laws and rules defined by the government. On another level, the company needs to know its competitors. But it also needs to be aware of financing systems in order to study their impact on the organization. Finally, and among other dimensions of data collection, the company must follow the evolution of the potential and threats of externally connected information technologies. Currently, only 20 per cent of the data stored on servers is structured. In other words, data resulting from transactions, products, raw materials, orders, purchases, sales, etc. (Figure 8). Unstructured data predominates and is typically more difficult to process. Information and Technology Systems | 19 Lecture 1 | From Data to Information Figure 8 – Examples of Data Types There are many sources of data. For big companies, they require sophisticated means of collection and processing to result in useful information for control and decision-making (Figure 9). Figure 9 – Diversity in Data Sources Nowadays it is possible to have the most varied sensors collecting data quickly for computer systems. This real-time collection, combined with algorithms, allows for the automation of many action rules. As such, the so-called Internet of Things (IoT) makes it possible to connect physical and digital environments (Figure 10). Information and Technology Systems | 20 Lecture 1 | From Data to Information Figure 10 – Data collection by Internet of Things (IoT) As a manager, see how useful these IoT resources can be, for example, to control the conditions of workplaces in accordance with the legislation in force by controlling ambient temperature, relative humidity, CO2 concentration and lighting. Example of Data Transformation Let's look at an elementary and purely academic example that illustrates the transition from transaction data (sales, purchases) to basic information. As we can see in Figure 11, the table on the left contains data resulting from the facts or events that produce these records. This is data on sales of products by certain salespeople. This processed data, adding up sales by product and identifying the highest and lowest sales, is information. With this information we can better govern the company's commercial activity. Information and Technology Systems | 21 Lecture 1 | From Data to Information Figure 11 – Example of Transforming Data into Information We can find out who sells the most, which type of product sells the most, or which is the most profitable, etc. This information can be used to draw up reports with tables and graphs that explain what happened in each period. We know, then, that Information is the result of processing quality data and serves, in both personal and business contexts, to control and support decision-making. Decision-making can be different at each of the three traditional management levels: operational, tactical, and strategic (Figure 12). At the operational level, the rules have to be clear, and their nature is also more elementary, so decisions are structured. At the operational level we have routine procedures such as issuing invoices, granting credit, or placing orders. At the tactical level, complexity increases and decisions are often semi-structured with a greater human component. At the tactical level there are actions such as selecting new markets and deciding on the content of a webpage or even a website or portal. At the top management level, and therefore at the strategic level for the organization, the complexity is enormous, and the human component is traditionally stronger than at lower management levels. It is necessary to define long-term objectives, enter or leave markets, which requires experience and the ability to evaluate. Information and Technology Systems | 22 Lecture 1 | From Data to Information Figure 12 – Information for Different Management Levels The evolution of Artificial Intelligence (AI) has opened new perspectives on the potential of information technology to support top management. That is, to be more useful in decision- making processes. Imagine a hospital environment with the advanced contribution of Artificial Intelligence in image analysis. The doctor almost acts as a natural and social interface for the patient, with AI being more competent at diagnosis. In business, the data collected, validated, and stored is processed by machines or people. Their processing according to predefined rules results in information. This information is typically presented in the form of lists and graphs. This helps us to understand what has happened or even what is happening. More powerful algorithms can check the data for patterns or trends and thus help make predictions. Technology companies and most academics in the field of computer science refer to this activity as obtaining knowledge from data. Strictly speaking, what we are obtaining is new information, or more sophisticated information with algorithms that apply more complex math’s and statistics. Knowledge is human and therefore richer because it is contextualized in culture, history, and experience. Today, employees in economic units have powerful resources for processing data and information. For example, they use invoicing, finance and accounting applications that are increasingly sophisticated and integrated with their statistical component. But employees have more flexible applications such as Excel and Power Business Intelligence (Power BI) to process data and share it in real time and interactively in Sway or PowerPoint. All this can be done as a team and with real-time, and therefore up-to-date, data! Data visualization and information design are very important factors. A good representation has strong persuasive potential. The ability to explore data in real time and interactively is relevant at the highest levels of management. Information System We reflect on the nature of data, its origins, quality, storage, and processing. We study how its processing results in useful information for controlling and justifying decisions. We need to think about the concept of a system and, as a result, the concept of information systems. Their purpose and logical architecture. Do you know what a system is? Do you know a definition? Perhaps start by identifying which systems you are familiar with and draw parallels with information systems. Information and Technology Systems | 23 Lecture 1 | From Data to Information As you know, there are natural systems and others created by humanity. In natural systems we have physical systems (solar, molecular, etc.) and living systems (reproduction, circulatory, respiratory, etc.). The systems created by humanity include economic, financial, legal, social and information systems, among others. We can therefore say that a system is ‘a group of interrelated elements, interacting with each other and forming a whole to achieve a purpose or objective’. We're going to study information systems in essence and we’re going to analyse Business Information Systems. An Information System is ‘a varied set of elements of different and diverse natures which, by means of productive combination models, are combined with each other with a view to collecting, processing, storing and making information available’, serving, as we have seen, different levels of management. Information systems have the mission of organizationally integrating different procedures that facilitate the control and governance of profit and non-profit organizations. The technologies provide very strong support, particularly in solving problems that are more algorithmic than heuristic in nature, so the control areas (at the bottom of the pyramid) are more developed than the decision support areas, which are now evolving more rapidly with the advent of AI. In other words, the algorithms for managing, for example, sales, stocks, purchases, salaries, have been mastered and are easy to develop and update compared to more complex decision- support algorithms. The technologies that support information systems are constantly evolving. We are thus mentioning the importance of IT systems, whose progress has enabled significant changes in the business. Firstly, the articulation of the information system with partners, employees, customers, suppliers, and the state. But also, transformations such as e-commerce. Everything is faster, more flexible, and inevitably dependent on technology. The scale may be global, but companies and people are also exposed to dangerous cyber-attacks (See Lecture 9). IT systems are easily copied by other companies, so the differentiating factors must come from the ability to do things differently through the information system that is built on this basis and under the inspiration of technically and ethically well-trained human beings. There is a historical approach that classifies information systems as summarized here: Transaction Processing Systems (TPS): Automate everyday tasks such as sales and payment processing. Management Information Systems (MIS): Transform transaction data into useful reports for management. Decision Support Systems (DSS): Help support decision-making by analyzing data and producing simulations. Executive Information Systems (EIS): Provide critical and summarized information for executives. Office Automation Systems (OAS): Facilitate communication and workflow in the office. Knowledge Management Systems (KMS): Capture and use explicit organizational knowledge. Information and Technology Systems | 24 Lecture 1 | From Data to Information Information System Logical Architecture To delve deeper into the concept of Business Information Systems, let's analyse the evolution of their logical architecture. The architecture of information systems must be integrated, holistic, harmonious, and economically sustainable. The constituent elements of the information system are data, physical components (hardware), logical components (software), procedures and people. It has different functions and incorporates the relationship between users and levels of integration. Architecture (Figure 13) is thus distinguished from engineering, whose central mission is to identify equipment, build and manage infrastructures. Figure 13 – Basic Architecture of an Information System Adapted de Putting the Enterprise into the Enterprise System, Thomas H. Davenport (1998) In fact, in 1998, Thomas H. Davenport published in the Harvard Business Review a fundamental study that remains valid and widely referred to in the specialist literature, to identify the role and logical architecture of an Information System, for an organization of any size. It is a model centered on the company's internal activity and draws attention to the centrality of the Database. The database must prevent data from being scattered across applications, services, and employees' computers. Davenport draws attention to the limitation of accepting the so-called ‘good practices’ of consultants without judgement and originality. In this way, the company loses the competitive and differentiating factors that technology can still provide. The evolution of technology and networks has made it possible to link the technology-based information system with the outside world. In this way, the information system connects with Information and Technology Systems | 25 Lecture 1 | From Data to Information consumers, partners, suppliers, and the state itself, by delivering automatic documentation, as Figure 14 attempts to represent. Figure 14 – Updated IS Architecture Business management is therefore based on what is known as ERP (Enterprise Resource Planning) at a technological level. This name was coined by the Gartner Group in 1990 to define a new name for global business management software. ERP works mainly with structured data and integrates a component called CRM, i.e. Customer Relationship Management, which is geared towards customer management. As can be seen in Figure 14, the architecture is now more complex, therefore more expensive and exposed to criminal attacks in cyberspace (see lecture 9). All these resources are intended to transform data into information that facilitates control and decision-making, with the manager having the task of identifying the type of information he needs for management that allows his company to be competitive, as we see in Figure 15. This orientation is crucial for Information System Planning. In other words, what we think the IS should be like, how we are going to produce and exploit it (a theme to be developed in L|8). Information and Technology Systems | 26 Lecture 1 | From Data to Information Figure 15 – Type of Information Source: Amaral, L., 1994, PRAXIS - A Framework for Information Systems Planning Pedagogical Activity 1. Hypothetical Context: Imagine a fictitious company called ‘Techno’. Techno sells electronic products and is considering a new marketing strategy to increase sales. 2. Teams and Departments: Imagine that you belong to one of these departments at Techno: o Sales: Responsible for generating revenue. o Marketing: In charge of promoting products. o Finance: Deals with budget and profit. 3. Fictitious data: The (fictitious) data we have is as follows: Product category Product Online Sales Offline Sales Total Sales Electronics Smartphone 120 80 200 Electronics Notebook 50 30 80 Electronics Smartwatch 30 20 50 Clothing Shirt 80 60 140 Clothing Jeans 50 40 90 Clothing Dress 40 30 70 Home & Decoration Kitchen Utensils 60 40 100 Home & Decoration Cushions 30 20 50 Home & Decoration Decorative pictures 20 10 30 Does the content of this table represent data or information? Justify. Is the content structured or unstructured ‘data’? Information and Technology Systems | 27 Lecture 1 | From Data to Information At what management level is this data/information typically collected, validated, and stored? Is there important ‘information’ missing from the table for your analysis? If so, what is it? 4. Decision-making: o Each team/department must make decisions based on the data: o Sales: ▪ Which products are most in demand? ▪ How can you optimise sales? o Marketing: ▪ Which marketing channel is most effective? ▪ How much to invest in online versus offline advertising? o Finance: ▪ What data to request about the environment? ▪ Create convincing graphics to persuade a favourable decision to invest in marketing? 5. Information Systems o The company decided to go online. What impacts do you identify for your current Information System designed to sell in physical shops? Other Teaching Elements Lecture I Support Slides: o 23-SIT-1-Lecture-IA-DosDadosaInformação – STD o 23-SIT-1-Lecture-IB-Sistema de Informação - STD Lecture I summarised in two videos o SIT-I-A-8.12-UK o SIT-I-B-5.09-UK Online Campus Formative Test Please note: you must download these items and view them on your computer. The option of accessing them in your browser and trying to open them to visualize them does not work. Challenge All 10 Lectures and 14 Workshops will enable you to understand the scope of management applications, identify the structure of the data being analyzed and choose the right software to process data for control and decision-making. These classes also give you the ability to share information in real time, online on the Internet, on the Intranet or in presentations to managers. Information and Technology Systems | 28 Lecture 1 | From Data to Information Figure 16 – Simulation of the Context of Exploitation of Management Tools Figure 16 tries to explain the technological ecosystem supporting the ITS Course. On the left, we simulate the existence of an ERP that supports the management of the company in its various departments and functions. We can stay connected to that data, or we can extract the data we're interested into the cloud and analyze it on different platforms (Excel, Power BI, Power Apps, Sway, Power Point, Planner, Visio), cross-referencing or not with external data (like Portuguese services Pordata, INE, etc.) and accessing it on different media. It will therefore be impactful to reach the end of the course and try to visualize 360.º what you have learned and retain the ability to understand, for each specific situation, which software is best to apply and how they can contribute to management! As a future knowledge worker, you will develop your activity in this field, which can incorporate what is known as Artificial Intelligence. Microsoft 365, with commercial or academic licenses, can incorporate Copilot which, in 2024, uses GPT 3.5 Copilot is incorporated into EDGE free of charge from a creation and analysis perspective via two different access buttons. In other words, through dialogue and the ‘engineering’ of the question, it allows you to create texts, images, etc. Or when the user reads a PDF or a website, Copilot analyses these documents and allows you to summarize them, etc. Security is essential here, for example, if you are analyzing your company's documents after logging in to EDGE, the system guarantees that the documents do not leave your organization’s domain, which is not the case in other environments. In 365 applications such as Word, Power Point, Excel, Outlook, Teams, Power BI and Power Apps, among others, it is also available for creation and analysis (Figure 17). As such, it can help you create a text or analyze one, two or three documents, summarize emails exchanged and draft replies (summarize, make drafts, and incorporate coaching), as well as help you build a PowerPoint presentation with notes included from a specific topic or document. Google is contrasting this with Vids, initially for video production based on Gemini, but which could eventually evolve into its own Office. Information and Technology Systems | 29 Lecture 1 | From Data to Information Figura 17 – Copilot in Microsoft 365 Information and Technology Systems | 30 Lecture 1 | From Data to Information Synthesis Lecture 1 Information and Technology Systems | 31 Lecture 2 | Data Management [L|2] Data Management “Data will speak to you if you are willing to listen to it.” Jim Bergeson Remember Figure 14 and the Reports component associated with Data Science to identify the importance of developing skills in processing and analyzing data. For this reason, in this L2, we are going to ‘let the data do the talking’. The traditional approach was to segment the data and then produce statistics. But what if the ‘data could talk’? With the speed and efficiency of today's data collection, we can always not only know what has happened, but also what is happening. It will be important to evaluate this ability to work in ‘real time’, in other words, the ability introduced by computer systems to reduce data collection time to zero. Note that the time taken to make decisions at a strategic level does not change in the same way. But for control it is of the utmost importance. So, it makes sense to learn how to use Excel spreadsheets and Power BI, among other applications. The evolution of the spreadsheet The concept of an electronic spreadsheet was devised by Dan Bricklin and implemented by Bob Frankston. VisiCalc was founded in 1979. Other applications followed, such as the famous Lotus 1,2, 3 (1983), which succeeded Microsoft Multiplan (1981). Microsoft Excel appeared in 1985 and Borland's Quattro Pro in 1989. Spreadsheet is one of the first personal computer applications that facilitated and motivated the mass use of the ‘PC’ and ‘Mac’ in the context of companies. In fact, the need to analyse data cuts across all professional areas, which saw Spreadsheet as the solution to studies that business management applications couldn't provide. When Excel was launched by Microsoft in 1985, it won over users even though, as we have seen, there were other well-established spreadsheets on the market. Artificial intelligence was first incorporated into Excel in the 365 Online version. Then the idea button moved to the desktop version. But in 2023, it was renamed ‘Analyse Data’. In 2023, Copilot was announced and became available for licensing in 2024. In a Microsoft 365 context, Copilot has both a creation and analysis component, while preserving the security of organizational data. Spreadsheets used to be limited in their data reading formats. Today, they allow access to the most common digital formats, both for import and as a connection to the data source. They can even read data included in documents in PDF format (Figure 18). In the field of analog-to-digital document retrieval, we can identify the APP called Office Lens, for Android, which has optical character recognition with the ability to scan an analog table and convert it to digital in spreadsheet format. In-depth and advanced data analysis brings together various skills such as statistics, math’s and computer science, allowing for a context as shown in Figure 18. Information and Technology Systems | 32 Lecture 2 | Data Management Figure 18 – Data connection and import. Elementary use of Excel We'll be using Excel in English. Both its interface and the use of functions. The normal procedure for putting Excel into English is as follows: 1. With Excel open, click on File in the main menu. 2. In the bottom left-hand corner, click Options. 3. In the window that opens, click on Language on the left. 4. In both tick boxes, tick the language you want (English) and click the Set as Preferred button. 5. In the alert messages that appear, simply click Yes. 6. Close Excel. 7. Open Excel again and check. The structure of a Spreadsheet is simple, and its basic operation is very elementary and very useful (Figure 19) replicating the idea of a sheet of squared paper that becomes ‘smart’. Information and Technology Systems | 33 Lecture 2 | Data Management Figure 19 – Structure of a Data Sheet In Figure 20 we see an elementary Spreadsheet with a collection of data in three lines containing data and a fourth, in the second position, identifying the six fields (Area, Q1 to Q4 and Total). Line seven shows a simple statistic with the total per field or column. Line seven activates the SUM function which makes it easy to invoke a range of rows or columns for calculation. We can therefore write, in B7, =SUM(B3:B5) 3. In other words, we sort the sum of the values we find in B3, B4 and B5, i.e. from B3 to B5. Alternatively, we could have inefficiently written the following expression =B3+B4+B5. If the range of values were huge, containing 100, 1000 or more rows, the impact on productivity and the accuracy of the result obtained would be enormous between doing SUM(B3, B4,...., B999, B1000) and Sum (B3:B1000). The equal sign (=) at the beginning means that we want to do a calculation or use a function and not write the word SUM or the expression B3+B4+B5. What we write in B7 can be copied to C7, D7, E7 and F7. Column F can be obtained by writing the following in F3: =SUM(B3:E3) and then copying it to the lines below. 3 As an alternative to the ‘=’ sign, you can use the ‘+’ sign Information and Technology Systems | 34 Lecture 2 | Data Management Figure 20 – Introduction to Spreadsheets To copy, you can select the source cell and COPY (CTRL + C) and PASTE (CTRL + V) into the destination cells. Alternatively, place the mouse in the bottom right-hand corner of the cell containing the formula (a thin cross appears) and drag (or double-click) to the destination cells. Figure 20 shows the conditional formatting for highlighting data. Of course, in this image, the wrong mix of styles appears - graphs, circles, and arrows - just to show various alternatives in columns B, D and E. The system automatically divides the data into three parts and marks those that fall into each of the intervals with three colors. This arrangement can be changed by the operator. Figure 21 uses Sparkline to show the evolution of the data. Here, too, styles are mixed up, just to show them off, and it doesn't make sense to represent them at the same time (columns F, G and H). Figure 21 – Using Sparkline You always must select the data and choose the formatting to apply. For example, select B3:B4 and Conditional Formatting and Icons Set (among other alternatives). In the case of the sparkline application, select B3:E3 and Insert / Sparkline / Line (Column or Win/Loss) and choose Location Range. Information and Technology Systems | 35 Lecture 2 | Data Management Elementary Statistical Functions Excel offers a wide range of functions grouped by technical area, such as finance, date and time, math’s, statistics, search, database, word processing, logic, etc. It offers two levels of help on function syntax. So, when you type in =SUM, for example, the system shows you the possible arguments for the function. If you choose FORMULAS / INSERT FUNCTION (or press fx) and choose, for example, SUM, you get a dialogue box explaining the purpose and operation of the function. This mechanism is very useful for learning Excel. As we've seen, a spreadsheet is a kind of electronic grid paper with referenced rows and columns, which we call addresses. For example, cell A4 is in column A and row 4. Let's take a brief look at the procedures for performing Sums, determining Maximums and Averages, and learn how to use relative and absolute addresses. As you can see on the sheet shown in Figure 22, there are areas shaded in blue for making elementary calculations. Figure 22 – Elementary Statistical So let's see how to apply the sum (SUM), maximum (MAX) and average (AVERAGE) to the blue shaded areas of the spreadsheet. We apply functions to ranges of values in column D. To determine the total, the maximum value and the average from D2 to D8. Calculating the average of each product in column F will be analyzed later. For a better understanding of what we've done, Figure 23 shows the use of the SUM, MAX and AVERAGE functions and the range of values on which they act. Information and Technology Systems | 36 Lecture 2 | Data Management Figure 23 – Application of Statistical Functions Applying the functions to rows 10, 11 and 12 and columns D and E shows that they act on a range of values from rows 2 to 8. Example =SUM(D2:D8). We calculate the sum, maximum and average in column D. Then we copy them to column E. The system that used to refer to D2:D8 now automatically refers to E2:E8. We say that it has taken on relative addresses. In other words, it realized that the columns had been moved in the copying process and took over the new column where the data originated. In Column F we're going to calculate the average value of each product in relation to the quantities sold (D). The result is obtained by dividing the values of each row in column F by the corresponding values in column D. So the value of E2 is divided by the value found in D2. In other words, E2/D2 to E8/D8. Naturally, from row 3 to row 8, copy the formula written in F2 (= E2/D2). After copying, place the cursor in one of the cells that received the copy to analyze how the relative addresses work. Figure 24 shows a new calculation situation for column G. Here we want to analyze the sales in value (column E) as a percentage of the total in E10. Figure 24 – Absolute Addresses In other words, we want to divide E2 by E10 and E3 by E10 and so on up to E8 by E10. We make the first line in F2 by writing =E2/E10 and then copy it to F3 up to F8. What happens? The system applies the relative addresses and, on line 3, puts E3/E11 and on line 4 E4/E12 and so on up to E8/E16. Information and Technology Systems | 37 Lecture 2 | Data Management In fact, E10 should be an absolute address. When copied, it should always be E10 to obtain not error information (#DIV/0!), but the result of the calculation, as shown in Figure 25.. Figure 25 – Result of the various calculations proposed To do this, we had to add a sign that turns E10 into an absolute address. The sign is the dollar sign ($). As we only want to copy into a column, we need a $ after the column identifier. So: =F2/F$10. To make things easier, many Excel users choose to press the F4 key to get the expression =F2/$F$10). In other words, it's always F10 whether you copy in a row or a column. If you wanted to copy to G or H, you wouldn't change the column (F) because you have the dollar before the column identifier (/$F10). Logical Functions Computer programming languages have some control instructions that are inspired by human thought. One of these is iteration or repetition. For example, I take steps (repeat - iterate) until I reach the door. Or I have a lesson until it's 10am. Or to determine the average, I must add up five values from I=1 to 5. Another control primitive is the conditional. So, I might think, if it's sunny then I won't take an umbrella else I will. If I get a 9.5, then I'll pass the course, else I'll fail. Looking at our example, we might think it would be a good idea to analyze the data according to the information in column C. So, for example, we want Total Sales (TOTAL SALES) to be increased by a further 30% if the product is of type "A+++", otherwise only by a further 10%, as shown in the "program" below. IF Energy = "A+++" THEN TOTAL SALES Total Sales + 30% ELSE Total Sales Total Sales + 10% EXCEL then has the IF Function with three arguments separated by ‘;’. = IF(logical_test;value_if_true;value_if_false) Some conditional functions can be found HERE (Microsoft Support). IFS, IF AND e IF OR, COUNTIF, COUNTIFS, SUMIF, SUMIFS, IFERROR Figure 26 shows a simple situation for applying the IF function as described at the beginning of this session. Calculate the sales forecast according to the energy type (column C) of each product. Information and Technology Systems | 38 Lecture 2 | Data Management Figura 26 – Função IF In this case, we're using the “FORMULAS” Menu and INSERT FUNCTION option (or, more simply, press fx) and we select IF and the dialogue box pops up as shown in Figure 26. IF (C2 = "A+++“; D2 *1,3 ; D2 *1,1) THEN ELSE Figure 27 shows a more complex situation. The idea is to find out IF the Manufacturer is SAMSUNG and IF the YEAR of Production is 2024, then mark with **. If it is SAMSUNG, but not from 2024, mark with + and, in the other cases, with -----. Figura 27 – IF embedded The THEN i.e. the true part can, for example, be another IF as in the following example: =IF(A2="Samsung";IF(B2=2024;"**";"+");"-----") A flowchart illustrates this procedure. Information and Technology Systems | 39 Lecture 2 | Data Management In column G we want to create alerts. So, IF the Manufacturer is SAMSUNG and IF it is also from 2024 THEN we mark it with **. If it's Samsung, but not from 2024, then +, and if it's neither, then -----. Figure 28 shows the application of the IF function in chained form. Figure 28 – Implementation of chained IF Information and Technology Systems | 40 Lecture 2 | Data Management IF AND can be an alternative to simplify things a little, as shown in Figure 29. Figura 29 – Uso do IF AND Let's look at examples of other functions in the same family: IFS, IF AND (IF OR), SUMIF and COUNTIF. IF AND The IF AND in Figure 29, =IF(AND(A2="Samsung";B2=2024);"**";IF(A2="Samsung";"+";"----")) asks whether the Manufacturer is SAMSUNG and whether it is from 2024. Then it tests other situations according to the purpose of the analysis already described. IFS In Figure 30 we see the application of the IFS function to classify sales (Column E) as follows: if sales are less than or equal to zero it is "-". If it's greater than 10 it's "+” if it's greater than 20 it's "++" and if it's greater than 30 it's "+++". Figura 30 – Função IFS =IFS(E210;"+";E2>20;"++";E2>30;"+++") Information and Technology Systems | 41 Lecture 2 | Data Management Imagine how to proceed if you want the following campaign clarification: if the manufacturer is Samsung, the campaign is in July. If it's Leica, the campaign is in July, if it's ASUS, the campaign is in September. Figure 31 shows the application of SUMIF to only add up Samsung's sales, i.e. the rows in column E when Samsung is labelled in column A. Figure 31 – SUMIF Function Figure 32 applies the COUNTIF function to find out the number of sales of each of the manufacturers, starting with Samsung. We indicate that we want to count from A$2 to A$16 if the content is B18, i.e. Samsung. The use of the dollar ($) allows us to then copy to C9 and C10 while maintaining the counting range, i.e. A2:A16. Figure 32 – CountIF Function Database functions The so-called database functions make it possible to move from tables with data collection to tables with information. Think back to Figure 11 and realize the elementary case in Figure 33 Information and Technology Systems | 42 Lecture 2 | Data Management where you can imagine thousands of sales lines in the table from columns A to D and, in columns F to I, an analysis of thousands of sales by Manufacturer (Brand). Figure 33 – From Data to Information with DataBase Functions The application of the above functions has been to complement the tables with the collection of transaction data (purchases, sales, production). Figure 34 shows the Database area. Please note that when defining the Database you must include the name of the Fields (i.e. in this case, line 1). We then build an analysis table as shown in columns F to I, in row 5, and place the Database functions for the Sum, Maximum and Minimum, i.e.: DSUM, DMAX and DMIN. Figure 34 – Information Construction Other common functions are DCOUNT and DGET. Figure 35 shows the application of the DSUM function in cell F5. Information and Technology Systems | 43 Lecture 2 | Data Management Figure 35 – DSUM function syntax Note that the DSUM function invokes the database from A1 to D8. It includes the Name of the Fields, i.e. row 1. This area can be given a name so that you don't have to type in the area, but rather the name of the database. It also invokes the field on which the calculations will be made (column D, hence the field in D1) and the Filter or restriction for the Manufacturer field (Brand) at F4 and F5. Note that when preparing the information table (columns F to I), the name of the filter field in F4 must be the same as the one in the database (copy/paste). As F5 is empty, it has no restriction and is therefore all Brands. Note that the result is 145 equals to the sum (SUM) of column D. In Figure 36, we see that in addition to G5, H5 and I5 are filled with DMAX and DMIN respectively. That is, DMAX(A1:A8;D1;F4:F5). Database A1 to A8, D1 the calculation field and the filter F4 and F5). As F5 is empty, the system acts on all the data (column D) and the result is the same as you would get with SUM, MAX and MIN. Try typing "Brand A" in F5. Do you see the result changing? What if you type "Brand C"? It gives zero, because it doesn't exist. Figure 36 shows the final chart with the information table constructed. Figure 36 – DSUM Results and Interpretation Information and Technology Systems | 44 Lecture 2 | Data Management To complete the information table, select F4:I8 and go to DATA / What-if-Analysis / Data Table and, in Column input cell, type or select F54. We are informing you that the input for the formulas in row 5 (DSUM, DMAX and DMIN) that we mentioned as being F5 becomes that column, i.e. Brand A and Brand B. Remember that if you type in F5 anything, the formulas react to that input. We'll look at PIVOT TABLE and POWER PIVOT TABLE as well as Power BI as an alternative to this concept. The DGET(database; field; criteria) function helps to retrieve values that fulfil a condition. Let's apply it to elementary situations such as the one documented in Figure 37 Figure 37 – DGET Implementation Note that in D11 we want to obtain the Unit Price (D1) from the database (A1:E9) that verifies the values recorded in A11:B12. That is, the quantity of the Bananas product with more than 20 units sold. SEQUENCE Look at the following figure where the SEQUENCE function is applied in three situations, as shown in row 2 and columns A, B and D. In column A to generate 20 numbered lines. In column B where the text "No." is joined (concatenated) with the number sequence resulting in No. 1, No. 2, etc. In column D where it numbers according to the lines occupied in column E. In effect, the COUNTA function counts the number of lines with content. Therefore, only two numbered lines appear in column D, but the function is prepared to number up to 20 lines 4 The Row input cell option will allow you to build double-entry tables Information and Technology Systems | 45 Lecture 2 | Data Management GROUPBY The GROUPBY function allows you to group, aggregate, sort and filter data based on the fields you specify, and its syntax is as follows: GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array]) Examples: GROUPBY(A2:A51:D2:D51;SUM) to generate a summary of total sales by year as shown in next Figure. We can use GROUPBY to generate a summary of total sales by product with descending ordering by sales as shown in next Figure with a formulation like the following GROUPBY(C2:C51:D2:D51;SUM;;;-2). Information and Technology Systems | 46 Lecture 2 | Data Management VISIO and Excel Microsoft VISIO is technical drawing software. Among its many features, it allows you to draw flowcharts, database structures, basic "architectural" plans, hierarchical diagrams and many others. Its special feature is that you can link spreadsheets to drawings. For example, with the name and position of human resources and reference to their photo to build a hierarchical diagram. In another case, if you have a floor plan, for example, of a warehouse with different sections drawn and identified by a name, you can associate a spreadsheet to create graphs with the stocks. Each row of the spreadsheet has the name of the section equal to the drawing and in another column the stock and VISIO combines the two and allows you to make interesting and original pictograms. We'll come back to this in Lecture 7. If you have these technical drawings, you can visualize them in Microsoft 365 online and there you can also make some very basic diagrams. In management, for example, you can make hierarchical diagrams. Excel allows you to add Microsoft VISIO (typically in Home / Add-In) and here you can also make some diagrams, such as hierarchical diagrams or organigrams. Excel and PDF Excel currently reads data from a file in PDF format if it exists, for example, in the form of a table. But a spreadsheet can also include files in PDF format. For example, in a list of candidates, access their CV. Or a list of products with PDF files associated with technical details or manuals for that product (Figure 38). Figure 38 – Inserting Objects in Excel You should do Insert / Object / Create From File / Browse / Choose PDF and OK. Charts The right graphics are not always produced for the right purpose. Table 1 provides a simple guide to help explain the criteria for choosing the most common situations. Information and Technology Systems | 47 Lecture 2 | Data Management Illustration Designation Purpose Bars Compare Quantities Lines Trends / Time Pie Percentages Dispersion Relationships between variables Histogram Data Distribution Radar Comparing Categories Bubbles Data in 3 Dimensions PIE / Doughnut Parts in relation to the Whole Table 1 - Some Types of Charts You can be creative and, for example, associate filters with data tables and their graphical representation. The following figure illustrates this. If you select one of the Product Types, you'll see the country or countries to which it was sold on the map. To summarise, the steps are as follows: 1.º Insert Table 2.º Slicers (Menu Insert) Information and Technology Systems | 48 Lecture 2 | Data Management 3.º Map: - Select Table - Insert Maps - Filled Map With a data structure like the one in the following figure, with four levels (Quater, week and Sales), you can create a Sunburst chart that represents the hierarchy of the data (Quater, Month and Week). Read Sensor Data You can connect IoT sensors to your computer and have Excel read this data to monitor it in real time and graph it. Temperature, wind, solar panel sensors, etc. To do this, you need to activate Microsoft Data Streamer for Excel. 1. Go to File > Options > ADD-Ins. 2. Check that the ADD-Ins option is of type COM. That is, Manage COM ADD-INS and press GO. 3. In the COM add-ins dialogue box, select the box next to Streamer. This is Microsoft Data Streamer for Excel and press Ok. A new horizontal menu option appears at the top of the Excel window. Alternatively, the sensors can transmit data to the wired or Wi-Fi network and store the data in the cloud (e.g. OneDrive). Other Didactic Elements Support slides for Lecture II: o 23-SIT-2-Gestão de Dados-FC-1 – PT - STD Video summary of Lecture II o L-2-Data Management-EN.MP4 of 10 minutes. Online Campus Training Test See the list of available tutorials and the supporting spreadsheets at the end of this document to try out the functions studied. Information and Technology Systems | 49 Lecture 2 | Data Management A spreadsheet with many of the functions we are going to study is available on the online campus and via Qr-Code to see how they work and to train (Excel-Global-Functions.xlsx) (Download). Please note: you must download these items and view them on your computer. The option of accessing them in your browser and trying to open them and visualize does not work. Information and Technology Systems | 50 Lecture 2 | Data Management Synthesis Lecture 2 Information and Technology Systems | 51 Lecture 3 | Data Management [L|3] Data Management “Statistics can be defined as a set of methods used to make wise decisions in the face of uncertainty.” Wilson Allen Wallis Introduction As we've seen, the Enterprise Management System provides reports for top management and other management levels, but these reports don't always have the necessary information or cross-reference with ot

Information and Technology Systems Overview PDF

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue