Selection of Certain Columns To Be Loaded

It's necessary to choose the columns that should be loaded. Here are the conditions under which columns need to be loaded:

• 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 organization's 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 it's 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.

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.

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, it's 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 (we'll 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 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 we've 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 can't be used in an efficient way until it has been analyzed and cleansed. In terms of data warehouses, it's 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 organization's 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 organization's 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 organization's 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 organization's dimensions as well as a metadata repository. First, we'll look at the dimensions of the business, and then we'll 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 business's 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 organization's 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 speaking, the multidimensional perspective enables the business to answer the question: “Why did things turn out as they did?”

Note the hierarchies in the dimensions in Exhibit 5.4. The organization consists, for instance, of a number of business areas. Under each of these areas, we've got a number of departments, and in each department, we've got a number of employees. These hierarchies provide us with the opportunity to slice and dice the information. A sales figure for the overall organization can be broken down into business areas. Each business area can then be broken down into departments, and the department figures can be broken down into individual employees. Note that these features are especially helpful when the business—on a daily basis—is analyzing information by itself and is therefore not drawing on quantitative analyst resources.

A modern data warehouse will normally contain a metadata repository. Here information is stored about business data. The simplest definition of metadata is data about data. For example: for a camera, data is a digital photo; metadata will typically contain information about the date the photo was taken, the settings of the camera, name of manufacturer, size, and resolution. Metadata facilitates the understanding of data with a view to using and managing data. Metadata has been given a central role as businesses are becoming increasingly demanding of documentation. Libraries have registered metadata about books to facilitate searches. This metadata includes title, genre, publishing year, author of the book, and so forth. Without metadata, it would be difficult or almost impossible to find the relevant data (books).

The documentation of data and tables is of equal importance, and the demands for metadata registration in the data warehouse have grown considerably in recent years. Previously, it was sufficient if tables and fields had meaningful names. The simplest way to create metadata about tables and fields is to give these meaningful names. For instance, consider a revenue table containing the two fields, Revenue and Time. That should make it obvious what the table contains! The problem is, however, that users other than the ones who made the table might interpret the contents of the revenue field differently. Is it revenue with or without value‐added tax (VAT)? Are discounts included in the figures? Is the revenue figure in U.S. dollars or euros? And those are just a few different interpretation possibilities.

Understandably, the previous metadata registration is no longer sufficient. A better metadata registration can be performed using labels on the fields of the tables. The revenue field could have a label explaining the contents precisely: revenue excluding VAT including discounts in U.S. dollars. That would increase the quality of the metadata, and the data could be used by other users. But we still have the problem that users must be able to search through tables for fields with, for instance, revenue contents (just as when we search for books at the library).

Many BA vendors have taken action on the consequence of customers' need for advanced metadata registration and search options for the average user. They have created one single metadata layer in text format (XML format, in fact), that points to physical tables, fields, users, servers, programs, and reports. This layer can be found in the metadata repository of the data warehouse (see Exhibit 5.5).

The metadata repository has become one of the BA vendors' most important upgrading and sales arguments—and the arguments are compelling. The metadata layer produces documentation about everything that goes on in the data warehouse and the front‐end portal. Some software developers are working along the lines that reports cannot be produced if they are not registered in the metadata repository of the data warehouse. Similarly, a physical table is not available to the reporting environment without metadata registration. The same situation occurs with users, servers, and so on. A metadata repository has become key, since all Web inquiries must go through the metadata layer via the metadata server. This results in visibility and documentation of everything that goes on, and this is considered increasingly important to the business. These days, it's almost unthinkable to build a data warehouse structure without a central metadata repository. In the top layer of the BA platform, where users access reports and data, this metadata repository also enables users to search on data definitions and reports from the Web interface as if they were books at the library.

The users of Apple's iTunes software know about metadata registration in XML format. An XML file in an iTunes library on a personal computer contains all the information about tracks, albums, and artists, and so forth. iTunes uses this vital file to navigate. If an iTunes user copies one MP3 music file with Microsoft Explorer to a physical music library outside iTunes, it won't appear in the user's iTunes music collection, and he or she won't be able to search for it or play it, because information about the existence of the file and other data is not metadata that is registered via his or her iTunes software.

Data marts for the support of business processes are the end products delivered by the data warehouse and thus contain information for business users. A data mart is a specialized version of a data warehouse. Like a data warehouse, a data mart is a snapshot of operational data to help business users make decisions or make strategic analyses (e.g., based on historical trends). The difference between a data mart and a data warehouse is that data marts are created based on the particular reporting needs of specific, well‐defined user groups, and data marts provide easy business access to relevant information. A data mart is thus designed to answer the users' specific questions. Relevant dimensions for the business area have been linked to data, and specific business rules apply to help users move about in the desired dimensions and hierarchies. An organization may have several data marts for different functions, such as marketing, sales, finance, human resources, and others. A data mart is normally structured as a dimensional model such as a star schema, made up of fact tables and dimension tables and using specific business rules. An online analytical processing (OLAP) cube or a pivot table is a way of arranging data in areas (arrays) to facilitate quick data analyses, and it is often used for data marts. The arrays are called cubes.

An organization may have many data marts, each of which might be relevant to one or more business units for which they have been designed. Many business units have assumed “ownership” of their data marts, and this includes hardware, software, and data. This ownership enables each business unit or department or business area to use, manipulate, and develop their data to suit their needs, without changing any information in other data marts or centrally in the data warehouse.

Another reason for gathering data in small marts is that time is saved in connection with queries, simply because there is less data to process. This means, too, that two different data marts may present exactly the same information, except that one presents it in much more detail, which can then be used if the user decides he or she needs detailed information.

When data has been joined and enriched with dimensions in the data warehouse, data can be extracted for business use to data marts. These ETL processes will use many different business rules according to individual user needs. A data mart may cover the accounting function's need for a consolidated set of figures with the specific business rules required. Another data mart may cover the need for performance monitoring of the organization's sales processes.

As stated previously, the database for data marts may be relational or OLAP cubes. The functional difference between these two types of data is of great significance to analysts and business users, among other reasons because the difference affects response times and analytical scope. A relational data model is a model where data is organized using common characteristics. The order of the rows doesn't matter; only the number of rows is important because the number affects how quickly extracts can be performed. The order of the columns is of no importance, either. Transaction‐based tables are always relational, as explained in the following section.

The sales table in Exhibit 5.6 has seven columns and three rows, and is a simple example of what a relational transaction table looks like. In this kind of table, we can quickly add a new transaction from the shop, when another item is sold.

For adding, processing, and extracting data from relational tables, we use the programming language SQL, which is a formalized way of talking to databases. If we want to know the book shop's revenue distributed on the types “paperback” and “hardback,” we can send the following SQL syntax off to the database:

Create Table REVENUE as
Select TYPE, SUM (NUMBER * PRICE) as REVENUE
From   Sales_Table 
Group by TYPE 
Order by TYPE

We will then receive a revenue dataset that looks like Exhibit 5.7.

Large enterprises, such as Walmart, have several hundred million transactions a year, and it doesn't take a lot of imagination to see that response times in reporting can be very long if a preceding summary of the relational tables is not carried out, or if a reporting mart with a database in the shape of an OLAP cube is not created.

Once the OLAP cube is created, we can't just add another row, as with relational tables. By using this method of arranging data in cubes, we avoid the limitation of relational databases, as these are not suitable for instant (real‐time) analysis of large data volumes. The relational database is more suited for creating rows in a table of a series of transactions. Even though many reporting tools are developed for relational data, these are slow when it comes to summing up large databases. In an OLAP cube, all summing up and calculating are carried out beforehand; we just pull out a value from the cube, so to speak, and we therefore do not need to sum up something like a million rows in the extract.

In the OLAP cube in Exhibit 5.8, each little subcube contains a sales figure that is calculated beforehand for the different sets of dimensional values. The sale of a certain product (coats) in a certain country (Denmark) within a certain period (the month of July) could be the little dark cube in Exhibit 5.8. The clever thing about the OLAP cube is that when the business user requests information about the sales of coats in Denmark in July, all the involved transactions do not need to be summed up. Instead the extract application runs straight into the cube via some index values, and extracts one single, precalculated, summed‐up figure, which is then returned to the user's client software.

The OLAP cube can be seen as an expansion of a two‐dimensional spreadsheet. A controller (financial analyst) will need to analyze financial data by product, by period of time, by town, type of revenue, or cost and then compare actuals with budget figures. Each of these dimensions may have in‐built hierarchies. The controller will begin at a summarized level (such as the total difference between actual revenue and budgeted revenue), and then perform a drill‐down or slice‐and‐dice in the cube to discover entities, products, sales people, or periods of time that are accountable for the difference in the total figures.

Note that the size of an OLAP cube increases exponentially when more dimensions are added to the cube or when the number of categories in individual dimensions grows; this naturally affects performance and response times.

Alternative Ways of Storing Data

As mentioned earlier, one of the newer trends in data warehousing is the emergence of open‐source systems like Hadoop. These systems can store large amounts of data on multiple servers (one large file might be spread over 50 servers). Also, these systems can replicate the data, so should one server fail, the data can be picked from another server, and similarly, should one server be busy, the calculations can be done somewhere else or parallel within the cluster of hardware that is managed by Hadoop's filing distribution system. Hadoop systems do not move large datasets to the calculating software (the traditional way); they move the calculating software to the large data sets—and this saves time.

This new technology means that the cost of software and hardware (which historically has been high when building a data warehouse) in the future might be reduced.

Another benefit of Hadoop's file distribution systems is that data can be stored quickly, as it will be in relatively raw format. The downside, however, is that data in this format will continue to be raw. When we have stored the data, we will not be able to change it. In other words, it is a “store once and read many times” system.

With the relatively low cost of storing data using filing distribution systems, organizations have a good way to store data that might be relevant in the future. Should the data, based on a closer examination of data analysts and new business requirements, be considered valuable in the future, it is likely to be moved into a more traditional data warehouse environment. The need for moving this data into an ordinary data warehouse is because the data quality might need to be improved before it can be used. Also, reporting from file distribution systems can be a complex affair, not suited for nonspecialists. In addition, a simple look up query of a single case name (e.g., the last produced Peter Pan movie) might take hours in the Hadoop system, as the whole file needs to read before an answer can be given. In the data warehouse environment the file/table can be indexed, and this will significantly reduce the search time to find specific data points such as the above case name example.

Business Analytics Portal: Functions and Examples

BA tools and portals aim to deliver information to operational decision makers. In most cases, this information comes from relational databases and OLAP cubes in the organization's data warehouse, and the information is presented to business users as scorecards, dashboards, conventional retrospective sales reports, business performance management (BPM) dashboards, and analytical applications. End users access the BA portal on top of the organization's data warehouse (see Exhibit 5.1). The BA portal constitutes a small part of the overall process to deliver BA decision support for the business. A rule of thumb is that the portal part constitutes only 15 percent of the work; 85 percent of the work lies in the data collection and processing in the data warehouse.

In the past, BA tools have been developed from individual applications to serve as critical plug‐ins in the organization's global portals. The requirements for vendors of BA portals are therefore now focused on their ability to completely integrate all kinds of BA tools into one global portal, which then contains all relevant information for decision makers, whether it be employees, partners, customers, or vendors.

Developments are moving quickly toward global BA portals, and the key to a successful implementation of such a portal that completely integrates BA tools is to acknowledge that the portal is not just the launching pad for applications. The portal must deliver true business value and allow users access to business critical information in a dynamic and secure way. Further, the information must be available via different online and mobile units, all of which must be easy for end users to operate.

In the pages that follow, we'll provide examples of user‐friendly BA front ends and dashboards, which can be accessed on a modern BA portal like the SAS Information Delivery Portal. In Exhibit 5.9, we see a dashboard with graphics for the monitoring of the performance of product groups.

This BPM dashboard generated by the SAS Enterprise BI Server enables business users to follow product performance (actual profits vs. budget profits) on a monthly or quarterly basis, totaled, distributed on different product groups, channels, and the geographical markets where they are sold. The information in the dashboard is lag information by nature, but users can, with simple projections based on trends and colors, form an opinion about needs for future performance improvements. In this way, the information can move business processes forward, too (lead information). We then get an answer to the question: “What do we need to do tomorrow?”

Exhibit 5.10 shows a chart produced with SAS/ETS software. The software provides forecasts of demand for services so that organizations can maximize staff resources. It can automatically account for seasonal fluctuations and trends, and can select the best method for generating the demand forecasts. Efficient staff allocations mean customers' needs will be met with no wasted resources.

The software package includes both prebuilt and customized reports, allowing us to gain the most from our data. Built‐in analysis allows us to understand how visitors navigate our Web sites and how they flow through a user‐defined set of pages. In addition to showing the drop‐off at each step, the reports track users as they flow into, out of, and through the funnel. The report captures where visitors enter the funnel and where they go once they exit. Expandable and collapsible lists of pages show the most popular customer paths.

With text‐mining analytics, it is possible to classify documents into predefined or data‐driven categories, find explicit relationships or associations between documents, and incorporate textual data with structured inputs (see Exhibit 5.11). The dynamic exploration component helps us discover patterns in large document collections and allows us to combine those insights with our predictive analytics to gain maximum value from all of our information. By using text‐mining analytics, world leaders such as Netanyahu and Obama can find out what's being said about them in forums and lists and magazine articles and newspapers, as well as on Twitter and other social media sites—in real time—and can have it all boiled down and analyzed.

To gain an edge in today's competitive market, powerful advanced analytic solutions are required to extract knowledge from vast stores of data and act on it. More and more organizations are turning to predictive analytics and data mining software to uncover patterns in data and discover hidden relationships. In Exhibit 5.12, the screenshot from SAS Enterprise Miner enables the identification of market basket profiles. We can also interactively subset the rules based on lift, confidence, and support chain length.

Master Data Management

Problems concerning data quality often lead to discussions about master data management (MDM). When the complexity of the business increases, and the data volumes explode, the business turns toward MDM as an intelligent way of consolidating and managing data. MDM provides a unified view of data, when data is integrated from different data sources. In organizations that have been growing for a long time, the different business areas will typically have developed different master data concerning customers, transactions, and products. The same applies to merging companies. The need for identical definitions will arise across business areas, national borders, and/or merging companies. For instance, the definition can include what precisely a customer is and which customer data is registered. Using MDM, the business can consolidate these sources to a master reference file, which then feeds information back to the applications. Accuracy and consistency are thus secured across the entire organization.

Service‐Oriented Architecture

SOA is a way of thinking about how to use the organization's resources based on a service approach and with the objective of providing a more efficient achievement of overall business targets. It is therefore not a product that can be purchased, but rather a design philosophy about how to structure a solution. SOA entails integration across systems. Each IT resource, whether it's an application, a system, or a database, can be reached via a service device. This service function is available via interfaces. Web services are an implementation form that uses specific standards and protocols, when they are executed as SOA solutions.

SOA makes systems more flexible in terms of business needs, simpler to develop further, and easier to maintain and manage. Implementing solutions with an SOA facilitates the organization's planning for the future—including when changes occur—and helps it to respond proactively rather than reactively.

What specifically constitutes a service? A service is a program with which the user can interact through well‐defined standards for the exchange of messages. Services must be designed for stability and accessibility. There is no point in “making SOA for the sake of SOA.” SOA is undertaken to support the needs of the business. It is especially suitable for organizations that make data and applications available to a large number of customers. An enterprise like Weather.com makes its services available to many customers via SOA. When customers draw on these enterprises' services, they do so via Web interfaces and stable programs that are always available. Communication between systems takes place via well‐defined standards/protocols for the exchange of data. Instead of each customer developing various extracts from the Weather.com databases, Weather.com places an application at the customer's disposal that serves the client with data when a well‐defined set of parameters is received in the right protocol format.

In relation to our data warehouse model in Exhibit 5.1, SOA will be represented as interfaces from source systems inward, pointing to the staging area, or interfaces from data marts toward users' applications via the BA platform. In addition to making data easily accessible, they must obviously be easily understandable, so users are given outlines of, say, temperature and maps, and not just meteorological terminology and map coordinates from the easily accessible applications.

How Should Data Be Accessed?

In this chapter, we have looked at the typical ingredients in a data warehouse solution, from source data to the front‐end solution. If we now look at a data warehouse solution in relation to the different information domains that were introduced in Chapter 4, there are some correlations worth mentioning. There are two types of direct users of a data warehouse: the business user and the BA analyst. Since the BA analyst will always make his or her analyses based on business needs, the business user will always be the end user.

When the business user approaches a BA analyst for assistance in accessing the data warehouse, he or she may have two reasons for this. The first reason is that the business user may be looking for more than mere information; he or she may also be looking for an analyst to enrich and interpret this information—that is, to deliver information to the business. This naturally means that the analyst must have business insight, as explained in Chapter 4.

The other reason for a business user to draw on analytical resources is that he or she does not have access to the desired data and is looking for information. As illustrated in Exhibit 5.13, the analyst often has many points of access to the data warehouse. The reason is not that the analyst needs to know more than the rest of the business, but rather that a data warehouse is a dynamic entity that continually adapts to the needs of the business. And since it may take some time for the business to acknowledge and formulate a new need for information, and for the required information to be delivered as standard reports, there will be an implementation period where the analyst will be delivering the required information as manual reports.

Access to Business Analytics Portals

Analysts don't actually need access to BA portals because, if they have direct access to the data they are retrieving, they can access the data via their analytical programs. When someone from the business side then requests a report, the analyst can refer to the data managers in the section of the data warehouse that is responsible for the development of reports. In smaller organizations, however, the roles of data managers and analysts will typically be performed by the same person, which means that already here we are starting to see that it doesn't make sense to separate the two roles. There are many other good reasons, however, why analysts, on an equal footing with business users, should have access to and be keen users of front‐end solutions.

Analysts will often make considerable use of BA portals because they provide fast and easy access to data. Obviously, analysts are not using this data in connection with the continual development of manual reports, because if there is a front‐end access to the required information, the end user can retrieve the data there themselves. An analyst will typically use a BA portal in connection with the development of new reports, score cards, or dashboards, in cases where any of these are deemed too complex for the end user to develop. After completed development, they are delivered to end users, who can then continually update the contents of the reports when new data is entered into the data mart or on request.

Analysts use front‐end solutions, too, in connection with the validation of data drawn directly from the data warehouse. If an analysis is based on a linkage of five different extracts collected by means of SQL directly from the data warehouse, there will typically be hundreds of lines of programming to validate. This will, of course, not be a case of a one‐to‐one validation, because then the analyst would have used the front‐end solutions only. But it may be in connection with segmentations based on information that is available only from the data warehouse and not from the data marts, where we, for instance, want to validate that we have included all customers in our analysis. We therefore want to compare the number of segmented customers with the official number of customers. If these two figures are not the same, we have an error in our segmentation. Since analysts also frequently have a high degree of contact with the people who make decisions based on data warehouse information, analysts must train their end users in the functionality offered by the BA portal.

Access to Data Mart Areas

It is tempting to argue that analysts do not need direct access to retrieve data from data marts, if they already have access to data via BA portals. The thinking here is that it is possible to transfer data to the front end and save it as a flat file, which can then be transferred to a statistics or data mining program. However, several arguments are against this: First, it's important to be able to automate processes, which is the opposite of manual processes where the analyst retrieves, saves, and imports data via physical routines. Second, in connection with data mining solutions, where the same data must be presented in the same way month after month, there are great savings to be had in automating such processes. It therefore becomes a question of what the analyst should be spending his or her time on: moving data around or analyzing it.

Some front ends pose problems in terms of being able to collect only a certain number of rows at a time or having the user be timed out after a short period, which is not the case with analytical programs. Their limitation is the hardware and the bandwidth. Direct access to data marts can therefore ensure that all rows are included. Another argument for analysts to have access to both front‐end solutions and data marts is that a front‐end can function as an SQL generator. If our analysts' expertise is not precisely the development of SQL, SQL requests can be generated in the front‐end solution and copied and pasted into a statistics program, which can also use the access of the front‐end solution to the data mart. In this way, we can automate data delivery via open database connectivity (ODBC) entries and ensure that our analysts stay focused on analytics, which is difficult enough. In connection with data mining projects, where data access can be a considerable consumer of time, this specifically means that the data access can be carried out faster, if we focus solely on easily accessible information from our data marts. Once our data mining processes are up and running, we can then start to collect more raw information directly from the data warehouse. Moreover, this approach supports what is generally recommended when establishing BA projects: Think big, start small, and deliver fast.

Access to Data Warehouse Areas

Access to these areas enables analysts, via front‐end solutions, to provide the business with answers that they may otherwise not get. The reason that only analysts can access some data is not a question about withholding information in data. It's a question of establishing front‐end solutions that present information in the most accessible way possible, based on users' needs and the skills of the business users in general.

A supermarket, for example, may be reporting on revenue from different products, summarizing products into categories such as milk and butter. But since it would be too difficult for the user to get an overview, or it would take up too much space in the data mart, we can choose to omit information about which particular milk or butter brand. If we want this information, we'll have to drill right down into the data warehouse.

There are several reasons why a business wants answers to questions that cannot be clarified via the BA portal. Needs may change over time, questions may arise in connection with ad hoc tasks that do not require regular reporting, or a business may want to implement complex analytical solutions. Data mining could be such a case, where the person driving the solution is called a data miner and not a typical business user. Data mining is generally associated with a business looking to use information as a strategic resource, not because the analytical method as such can justify this, but because it is a strategic decision that forms the basis of the investment in this competence area. As a result, data mining projects of a certain complexity depend on the data miner or analyst working with direct access to the data warehouse. Furthermore, the results of the data mining process (segmentations and recommended actions in relation to customers) will usually be distributed via the data warehouse to the CRM systems whose users are typically acting on the data mining information.

Access to Source Systems

Access to source systems is something analysts do not always have if a data warehouse has already been established. This access is usually not automated, which is the reason people often associate considerable time consumption with the use of information obtained directly from source systems. In addition to this, data quality can be quite variable, depending on what the information is used for in the source system. Furthermore, the source system itself might suffer in performance if accessed as a data warehouse. Developments in these cases will be that if information of significant value is identified in the source systems, a process will be started to ensure that this information becomes accessible in the data warehouse. If the business does not have a data warehouse, analysts always work directly with source systems, in spite of the weaknesses this entails. Over time, an analyst will usually want to carry out regular runs every month for reporting; the data set generated from this can therefore be seen as a data warehouse at the simplest level and as a quantity to be optimized.

In recent years, companies have come to realize the strengths and weaknesses of the Internet. There has been a growing understanding of the fact that, for instance, customer behavior on a company's Web site is relevant CRM information, too. It is not without relevance, for instance, for a telecom company to know whether a given customer has checked the company's Web site to find out how to cancel his or her subscription. If this information is then combined with information about the customer's value as well, it can be decided whether a “loyalty call” should be made to the customer in question. Long term, the company could structure its Internet portals so that the users' way of moving around these portals will affect the way this customer is treated. The Web site thereby becomes a questionnaire completed by the customer via his or her clicking, instead of merely providing information and automated services.

Generally speaking, though, Web log files are not yet providing the company with information about how customers and users use Web portals. In connection with commercials, however, there are exceptions, such as Amazon.com and a few search engines that are related to commercials that specifically and successfully use Web information as a strategic resource that can provide them with a competitive advantage. In these extreme cases, it seems possible to collect and use information to drive the company's strategy.