Fluent Essays

After reading Chapter 5 (ATTACHED) of the textbook, think about the categories of information that can be collected within the healthcare industry, then design your database layout with a diagram, similar to the one in Exhibit 5.4, that provides the different categories of information and the decisions that information can affect. In addition to your diagram, write a 2 page paper that analyzes why your selected elements are important and what your decision-making process was in choosing and excluding specific elements. Chapter 5 Business Analytics at the Data Warehouse Level During the last couple of years, a lot of changes have happened at the data warehouse level, and we can expect many more changes in the future. One of the major changes was called by the phrase Big Data. The reports that created this term came from McKinsey Global Institute in June 2011. The report also addressed the concern about the future lag of skilled analysts, but this we will discuss in the next chapter. In this chapter we will only focus on the data warehousing aspects of the Big Data term. The Big Data phrase was coined to put focus on the fact that there is more data available for organizations to store and commercially benefit from than ever before. Just think of the huge amount of data provided by Facebook, Twitter, and Google. Often, this oversupply of data is summed up in 3 Vs, standing for high volumes of data, high variability of data types, and high velocity in the data generation. More cynical minds may add that this has always been the case. It is just more clear for us, now that we know what we can use the data for, due to the digitalization of the process landscape. The huge amount of data may lead to problems. One concrete example of data problems most companies are facing is multiple data systems, which leads to data‐driven optimization made per process and never across the full value chain. This means that large companies, which are the ones that relatively invest the most in data, cannot realize their scale advantages based on data. Additionally, many companies still suffer from low data quality, which makes the business reluctant to trust the data provided by its data warehouse section. In addition, the business typically does not realize that their data warehouse section only stores the data on behalf of the business, and that the data quality issue hence is a problem that they must be solved by themselves. The trend is, however, positive, and we see more and more cases where the ownership of each individual column in a data warehouse is assigned to an individual named responsible business unit, based on who will suffer the most if the data quality is low. Another trend we see is symbolized by the arrival of a little yellow toy elephant called Hadoop. This open‐source file distribution system is free and allows organizations to store and process huge amounts of raw data at a relatively low cost. Accessing the data stored via these file distribution systems is, however, not easy, which means that there are still additional costs associated with using the data for traditional BI reporting and operational systems. But at least organizations can now join the era of Big Data and store social media information, Web logs, reports, external data bases dumped locally, and the like, and analyze this data before investing more into it. Another newer area is the increased use of cloud computing. This term means that many systems are moved away from on‐premises installations (in the building) to external Web servers. However, data privacy, legislation and other operational processes, often still makes it necessary for the data to be stored on premises in the individual organizations. In Chapter 4, we looked at the processes that transform raw warehouse data into information and knowledge. Later, in Chapter 6, we will look at the typical data creating source systems that constitute the real input to a data warehouse. In this chapter, we discuss how to store data to best support business processes and thereby the request for value creation. Well look at the advantages of having a data warehouse and explain the architecture and processes in a data warehouse. We look briefly at the concept of master data management, too, and touch upon service‐oriented architecture (SOA). Finally, we discuss the https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c04.xhtml https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c06.xhtml approaches to be adapted by analysts and business users to different parts of a data warehouse, based on which information domain they wish to use. WHY A DATA WAREHOUSE? The point of having a data warehouse is to give the organization a common information platform, which ensures consistent, integrated, and valid data across source systems and business areas. This is essential if a company wants to obtain the most complete picture possible of its customers. To gather information about our customers from many different systems to generate a 360‐ degree profile based on the information we have about our customers already, we have to join information from a large number of independent systems, such as: • Billing systems (systems printing bills) • Reminder systems (systems sending out reminders, if customers do not pay on time, and credit scores) • Debt collection systems (status on cases that were outsourced for external collection) • Customer relationship management (CRM) systems (systems for storing history about customer meetings and calls) • Product and purchasing information (which products and services a customer has purchased over time) • Customer information (names, addresses, opening of accounts, cancellations, special contracts, segmentations, etc.) • Corporate information (industry codes, number of employees, accounts figures) • Campaign history (who received which campaigns and when) • Web logs (information about customer behavior on our portals) • Social network information (e.g., Facebook and Twitter) • Various questionnaire surveys carried out over time • Human resources (HR) information (information about employees, time sheets, their competencies, and history) • Production information (production processes, inventory management, procurement) • Generation of key performance indicators (KPIs; used for monitoring current processes, but can be used to optimize processes at a later stage) • Data mining results (segmentations, added sales models, loyalty segmentations, up‐sale models, and loyalty segmentations, all of which have their history added when they are placed in a data warehouse) As shown, the business analytics (BA) function receives input from different primary source systems and combines and uses these in a different context than initially intended. A billing system, for instance, was built to send out bills, and when they have been sent, its up to the reminder system to monitor whether reminders should be sent out. Consequently, we might as well delete the information about the bills that were sent to customers if we dont want to use it in other contexts. Other contexts might be: profit and loss, preparing accounts, monitoring sales, value‐based segmentation or activity‐based costing activities—contexts that require the combination of information about customers across our primary systems over time and that make this data available to the organizations analytical competencies. BA is not possible without access to a combined data foundation from the organizations data‐creating source systems. In fact, that is exactly what a data warehouse does. A data warehouse consists of a technical part and a business part. The technical part must ensure that the organizations data is collected from its source systems and that it is stored, combined, structured, and cleansed regardless of the source system platform. The business content of a data warehouse must ensure that the desired key figures and reports can be created. There are many good arguments for integrating data into an overall data warehouse, including: • To avoid information islands and manual processes in connection with the organizations primary systems • To avoid overloading of source systems with daily reporting and analysis • To integrate data from many different source systems • To create a historical data foundation that can be changed/ removed in source systems (e.g., saving the orders historically, even if the enterprise resource planning [ERP] system “deletes” open orders on invoicing) • To aggregate performance and data for business needs • To add new business terms, rules, and logic to data (e.g., rules that do not exist in source systems) • To establish central reporting and analysis environments • To hold documentation of metadata centrally upon collection of data • To secure scalability to ensure future handling of increased data volumes • To ensure consistency and valid data definitions across business areas and countries (this principle is called one version of the truth) Overall, a well‐planned data warehouse enables the organization to create a qualitative, well‐ documented, true set of figures with history across source systems and business areas—and as a scalable solution. ARCHITECTURE AND PROCESSES IN A DATA WAREHOUSE The architecture and processes in an enterprise data warehouse (EDW) will typically look as illustrated in Exhibit 5.1. The exhibit is the pivot for the rest of this chapter. https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0001 Exhibit 5.1 Architecture and Processes in a Data Warehouse As opposed to the approach weve used so far in this book, we will now discuss the data warehouse based on the direction in which data and information actually move (from the bottom up). Our point of departure in previous chapters has been the direction that is dictated by the requirements for information (from the top‐down). The bottom‐up approach here is chosen for pedagogical reasons and reflects the processes that take place in a data warehouse. This does not, however, change the fact that the purpose of a data warehouse is to collect information required by the organizations business side. As is shown by the arrows in Exhibit 5.1, the extract, transform, and load (ETL) processes create dynamics and transformation in a data warehouse. We must be able to extract source data into the data warehouse, transform it, merge it, and load it to different locations. These ETL processes are created by an ETL developer. ETL is a data warehouse process that always includes these actions: • Extract data from a source table. • Transform data for business use. • Load to target table in the data warehouse or different locations outside the data warehouse. The first part of the ETL process is an extraction from a source table, staging table, or from a table within the actual data warehouse. A series of business rules or functions are used on the extracted data in the transformation phase. In other words, it may be necessary to use one or more of the transformation types in the following section. Selection of Certain Columns To Be Loaded Its necessary to choose the columns that should be loaded. Here are the conditions under which columns need to be loaded: https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-anc-0001 https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0001 • Translating coded values. For example, the source system is storing “M” for man and “W” for woman, but the data warehouse wants to store the value 1 for man and 2 for woman. • Mapping of values. For example, mapping of the values “Man,” “M” and “Mr.” into the new value 1. • Calculating a new calculated value. For example, sales = number × unit price. • Joining from different sources. For example, to look‐up or merge. • Summing up of several rows of data. For example, total sales for all regions. • Generating a surrogate key. This is a unique value attributed to a row or an object in the database. The surrogate key is not in the source system; it is attributed by the ETL tool. • Transposing. Changing multiple columns to multiple rows or vice versa. In the load phase of the ETL process, data is entered in the data warehouse or moved from one area of the data warehouse to another. There is always a target table filled with the results of the transformation in the load procedure. Depending on the organizations requirements, this process can vary greatly. For example, in some data warehouses, old data is overwritten by new data. Systems of a certain complexity are able to create data history simply by making “notes” in the data warehouse if a change occurs in the source data (e.g., if a customer has moved to a new address). Exhibit 5.2 shows a simple ETL job, where data is extracted from the source table (Staging). Then the selected fields are transferred to the temporary table (Temp), which, through the load object, is sent on to the table (Staging) in the staging area. The transformation of the job is simple, since its simply a case of selecting a subset of the columns or fields of the source table. The load procedure of the ETL job may overwrite the old rows in the target table or insert new rows. Exhibit 5.2 Example of a Simple ETL Job A more complex part of an ETL job is shown in Exhibit 5.3. Here data is extracted from three staging tables. Note that only selected columns and rows are extracted with a filter function; an example of this could be rows that are valid for only a certain period. These three temporary tables in the center of Exhibit 5.3 are joined using Structured Query Language (SQL). SQL is a programming language used when manipulating data in a database or a data warehouse. The SQL join may link information about position (unemployed, employee, self‐employed, etc.) to information about property evaluations and lending information. There may also be conditions (business rules) that filter out all noncorporate customers. The procedure is a transformation and joining of data, which ends up in the temporary table (Temp Table 4). The table with the joined information about loan applicants (again, Temp Table 4) then flows on in the ETL job with further transformations based on business rules, until it is finally loaded to a target table in the staging area, the actual data warehouse, or for reporting and analytics in a data mart. https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0002 https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-anc-0002 https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0003 https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0003 Exhibit 5.3 Part of ETL Job with SQL Join When initiating ETL processes and choosing tools, there are certain things to bear in mind. ETL processes can be very complex, and significant operational problems may arise if the ETL tools are not in order. Further complexity may be a consequence of many source systems with many different updating cycles. Some are updated every minute, and others on a weekly basis. A good ETL tool must be able to withhold certain data until all sources are synchronized. The degree of scalability in the performance of the ETL tool in its lifetime and use should also be taken into consideration in the analysis phase. This includes an understanding of the volume of data to be processed. The ETL tool may need to be scalable in order to process terabytes of data, if such data volumes are included. Even though ETL processes can be performed in any programming language, its fairly complicated to do so from scratch. To an increasing extent, organizations buy ETL tools to create ETL processes. A good tool must be able to communicate with many different relational databases and read the different file formats that are used in the organization. Many vendors ETL tools also offer data profiling, data quality, and metadata handling (well describe these processes in the following section). That is, a broader spectrum than extracting, transforming, and loading data is now necessary in a good tool. The scope of data values or the data quality in a data source may be reduced compared to the expectations held by designers when the transformation rules were specified. Data profiling of a source system is recommended to identify the usability of the transformations on all imaginable future data values. Staging Area and Operational Data Stores ETL processes transfer business source data from the operational systems (e.g., the accounting system) to a staging area, usually either raw and unprocessed or transformed by means of simple business rules. The staging area is a temporary storing facility in the area before the data warehouse (see Exhibit 5.1). Source systems use different types of formats on databases (e.g., relational databases such as Oracle, DB2, SQL Server, MySQL, SAS, or flat text files). After extraction, data is converted to a format that the ETL tools can subsequently use to transform this data. In the staging area, data is typically arranged as flat files in a simple text format or in the preferred format of the data warehouse, which could be Oracle. Normally, new data extracts or https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-anc-0003 https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0001 rows will be added to tables in the staging area. The purpose is to accumulate the history of the base systems. In the staging area, many subsequent complex ETL processes may be performed which, upon completion, are scheduled for processing with an operations management tool. The tables may be transformed hundreds of times on several levels before data is ready to leave for the actual data warehouse. If the business needs to access data with only a few minutes delay—for example, because the contents are risks calculated on the portfolio values of the bank—it may make sense to implement an operational data store (ODS). This will enable business users to access this data instantly. Typically, it will not be a requirement that data in a data warehouse be accessible for business analyses until the following day, even though the trend of the future is real‐time information. Pervasive BA, as weve mentioned earlier, requires real‐time data from the data warehouse. The ETL jobs that update rows in a data warehouse and in data marts will usually run overnight, and be ready with fresh data the next morning, when business users arrive for work. In some situations, however, instant access is required, in which case an ODS is needed. In regard to digital processes like multichannel marketing systems and apps pulling operational data, the data will typically not be provided directly by the data warehouse, but from operational data platforms that manage the real time interaction with customers. Albeit with some delay, these interactions will be written to the data warehouse, just like these operational platforms, with some delay, will be populated by the data warehouse. Causes and Effects of Poor Data Quality Data quality is a result of how complete the data is, whether there are duplicates, and the level of accuracy and consistency across the overall organization. Most data quality projects have been linked to individual BA or CRM projects. Organizations know that correct data (e.g., complete and accurate customer contact data for CRM) is essential to achieve a positive return on these investments. Therefore, they are beginning to understand the significant advantage that is associated with focusing on data quality at a strategic level. Data quality is central in all data integration initiatives, too. Data from a data warehouse cant be used in an efficient way until it has been analyzed and cleansed. In terms of data warehouses, its becoming more and more common to install an actual storage facility or a firewall, which ensures quality when data is loaded from the staging area to the actual data warehouse. To ensure that poor data quality from external sources does not destroy or reduce the quality of internal processes and applications, organizations should establish this data quality firewall in their data warehouse. Analogous to a network firewall, whose objective is to keep hackers, viruses, and other undesirables out of the organizations network, the data quality firewall must keep data of poor quality out of internal processes and applications. The firewall can analyze incoming data as well as cleanse data by means of known patterns of problems, so that data will be of a certain quality, before it arrives in the data warehouse. Poor data that cannot be cleansed will be rejected by the firewall. The proactive way to improve the data quality is to subsequently identify poor data and add new patterns in the cleansing procedures of the firewall or track them back to the perpetrators and communicate the quality problems to the data source owners. Poor data quality is very costly and can cause breakdowns in the organizations value chains (e.g., no items in stock) and lead to impaired decision‐making at management and operational levels. Equally, it may lead to substandard customer service, which will cause dissatisfaction and cancellation of business. Lack of trust in reporting is another problem that will delay budgeting processes. In other words, poor data quality affects the organizations competiveness negatively. The first step toward improved data quality in the data warehouse will typically be the deployment of tools for data profiling. By means of advanced software, basic statistical analyses are performed to search for frequencies and column widths on the data in the tables. Based on the statistics, we can see, for example, frequencies on nonexistent or missing postal codes as well as the number of rows without a customer name. Incorrect values of sales figures in transaction tables can be identified by means of analyses of the numeric widths of the columns. Algorithms searching for different ways of spelling the same content are carried out with the purpose of finding customers who appear under several names. For example, “Mr. Thomas D. Marchand” could be the same customer as “Thomas D. Marchand.” Is it the same customer twice? Software packages can disclose whether data fits valid patterns and formats. Phone numbers, for instance, must have the format 311‐555‐1212 and not 3115551212 or 31 15 121 2. Data profiling can also identify superfluous data and whether business rules are observed (e.g., whether two fields contain the same data and whether sales and distributions are calculated correctly in the source system). Some programs offer functionality for calculating indicators or KPIs for data quality, which enable the business to follow the development in data quality over time. Poor data quality may also be a result of the BA function introducing new requirements. If a source system is registering only the date of a business transaction (e.g., 12 April 2010), the BA initiative cannot analyze the sales distribution over the hours of the working day. That initiative will not be possible unless the source system is reprogrammed to register business transactions with a timestamp such as “12APR2010:12:40:31.” Data will now show that the transaction took place 40 minutes and 31 seconds past 12, on 12 April 2010. The data quality is now secured, and the BA initiative can be carried out. Data profiling is thus an analysis of the problems we are facing. In the next phase, the improvement of data quality, the process starts with the development of better data. In other words, this means correcting errors, securing accuracy, and validating and standardizing data with a view to increase their reliability. Based on data profiling, tools introduce intelligent algorithms to cleanse and improve data. Fuzzy merge technology is frequently used here. Using this technology means that duplicate rows can often be removed, so that customers appear only once in the system. Rows without customer names can be removed. Data with incorrect postal codes can be corrected, or removed. Phone numbers are adjusted to the desired format, such as XXX‐XXX‐XXXX. Data cleansing is a process that identifies and corrects (or removes) ruined or incorrect rows in a table. After the cleansing, the data set will be consistent with other data sets elsewhere in the system. Ruined data can be a result of user entries or transmission errors. The actual data cleansing process may involve a comparison between entered values and a known list of possible values. The validation may be hard, so that all rows without valid postal codes are rejected or deleted, or it can be soft, which means that values are adjusted if they partly resemble the listed values. As mentioned previously, data quality tools are usually implemented when data is removed from the staging area to the data warehouse. Simply put, data moves through a kind of firewall of cleansing tools. Not all errors, however, can be corrected by the data quality tools. Entry error by users can be difficult to identify, and some of them will come through in the data profiling as very high or low values. Missing data caused by fields that have not been filled in should be corrected by means of validation procedures in the source system (for details, see Chapter 6). It should not be optional, for instance, whether the business user in sales selects one individual customer or not. The Data Warehouse: Functions, Components, and Examples In the actual data warehouse, the processed and merged figures from the source systems are presented (e.g., transactions, inventory, and master data). A modern data warehouse typically works as a storage area for the organizations dimensions as well as a metadata repository. First, well look at the dimensions of the business, and then well explain the concept of the metadata repository. From the staging area, the data sources are collected, joined, and transformed in the actual data warehouse. One of the most important processes is that the businesss transactions (facts) are then enriched with dimensions such as organizational relationship and placed in the product hierarchy before data is sent on to the data mart area. This will then enable analysts and business users to prepare interactive reports via “slice and dice” techniques (i.e., breaking down figures into their components). As a starting point, a business transaction has no dimensions when it arrives in the data warehouse from the staging area. That means that we cannot answer questions about when, where, who, what, or why. A business transaction is merely a fact or an event, which in itself is completely useless for reporting and analysis purposes. An example of a meaningless statement for an analyst is “Our sales were $25.5 million.” The business will typically want answers to questions about when, for what, where, by whom, for whom, in which currency? And dimensions are exactly what enable business users or the analyst to answer the following questions: • When did it happen? Which year, quarter, month, week, day, time? • Where and to whom did it happen? Which salesperson, which department, which business area, which country? • What happened? What did we make on which product and on which product group? All these questions are relevant to the analyst. Dimensional modeling is a popular way of organizing data in a data warehouse for analysis and reporting—and not without reason. The starting point is the previously listed transactions or facts. It may also be helpful to look at the organizations facts as events. These fact rows are enriched with dimensions in a data warehouse to provide perspective. The dimensions in Exhibit 5.4 surrounding the facts or transactions put the sales figures, revenue figures, and cost figures into a perspective. This type of illustration is also called a star schema. Among other things, it gives business users and analysts the opportunity to get answers from the data warehouse such as these: • Our sales in product group 1 in December in the United States, measured in the currency U.S. dollars, were 2 million. • Sales in department 2 of business area 1 in the first quarter in Europe, measured in the currency euros, were 800,000. Note that the dimensions answer questions about when, for what, where, for whom, and by whom. Business reality is viewed multidimensionally to create optimum insight. Generally https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c06.xhtml https://jigsaw.vitalsource.com/books/9781119302537/epub/OPS/c05.xhtml?favre=brett#c05-fig-0004 speaking, the multidimensional perspective enables the business to answer the question: “Why …