As we indicated in earlier chapters, data warehouses should be built process by process, not department by department, on a foundation of conformed dimensions to support cross-process integration. We can imagine querying the sales or support service fact tables to better understand a customer’s purchase or service history.
Since the sales and support tables both contain a customer foreign key, we can further imagine joining both fact tables to a common customer dimension to simultaneously summarize sales facts along with support facts for a given cus-tomer, as in Figure 6.11. Unfortunately, the many-to-one-to-many join will return the wrong answer in a relational environment because of the differences in fact table cardinality.
Consider the case in which we have a fact table of customer solicitations and another fact table with the customer responses resulting both from the solici-tations and other independent sources. There is a one-to-many relationship between customer and solicitation and another one-to-many relationship between customer and response. The solicitation and response fact tables have different cardinalities; in other words, not every solicitation results in a response (unfortunately for the marketing department), and some responses are received for which there is no solicitation. Simultaneously joining the solic-itation fact table to the customer dimension, which is in turn joined to the response fact table, does not return the correct answer in a relational DBMS because of the cardinality differences. Fortunately, this problem is easily avoided. We simply issue multipass SQL code to query the solicitation and response tables in separate queries and then outer join the two answer sets.
The multipass approach has additional benefits in terms of better controlling performance parameters, in addition to supporting queries that combine data from fact tables in different physical locations.
Figure 6.11 Many-to-one-to-many joined tables should not be queried with a single SELECT statement.
Solicitation Date Key (FK) Customer Key (FK) More Foreign Keys ...
Solicitation Facts …
Customer Solicitation Facts
Customer Key (PK) Customer ID (Natural Key) Customer Attributes …
Customer Dimensions
Response Date Key (FK) Customer Key (FK) More Foreign Keys ...
Response Facts …
Customer Response Facts Customer Relationship Management 169
Be very careful when simultaneously joining a single dimension table to two fact tables of different cardinality. In many cases, relational systems will return the wrong answer. A similar problem arises when joining two fact tables of different granularity together directly.
If users are frequently combining data from multiple business processes, then an additional fact table can be constructed that combines the data once into a second-level, consolidated fact table rather than relying on users to combine the data consistently and accurately on their own. We’ll discuss consolidated fact tables further in Chapter 7. Merely using SQL code to drill across fact tables to combine the results makes more sense when the underlying processes are less closely correlated. Of course, when constructing the consolidated fact table, we’d still need to establish business rules to deal with the differing car-dinality (for example, does the combined fact table include all the solicitations and responses or only those where both a solicitation and response occurred?).
Summary
In this chapter we focused exclusively on the customer, beginning with an overview of CRM basics. We then delved into design issues surrounding the customer dimension table. We discussed name and address parsing where operational fields are decomposed to their basic elements so that they can be standardized and validated. We explored several other types of common cus-tomer dimension attributes, such as dates, segmentation attributes, and aggre-gated facts. Dimension outriggers that contain a large block of relatively low-cardinality attributes were described as permissible snowflakes in our dimensional designs.
In cases where our customer dimension has millions of rows, we recom-mended creating a minidimension of frequently analyzed or frequently chang-ing attributes. A minidimension is also appropriate for variable-width attribute sets. The fact table then has two customer-related foreign keys, one for the primary customer dimension and another for the minidimension. We discussed the implications of counting within a customer dimension where additional type 2 rows are created to handle change. We also explored the notion of creating behavioral study group dimensions, which merely consist of customer keys that share a common trait or experience. Finally, we tackled the handling of simple and complex commercial customer hierarchies. The unpre-dictable, variable-depth customer hierarchies commonly require the use of a bridge table to reflect the recursive hierarchy in a manner that can be queried by standard SQL code.
C H A P T E R 6 170
We briefly discussed the use of external software and service offerings to con-solidate customer information effectively while managing duplicate data.
Finally, we stepped back into the world of fact tables for a moment to discuss the potential downfalls of querying across two fact tables joined through a common customer dimension table.
Customer Relationship Management 171
Accounting
C H A P T E R
7
173
F
inancial analysis spans a variety of accounting applications, including the gen-eral ledger and detailed subledgers for purchasing and accounts payable, invoicing and accounts receivable, and fixed assets. Since we’ve already touched on purchase orders and invoices in this book, we’ll focus on the gen-eral ledger in this chapter. Gengen-eral ledgers were one of the first applications to be computerized decades ago, given the need for accurate handling of a com-pany’s financial records. Perhaps some of you are still running your business on a twenty-year-old ledger system. In this chapter we’ll discuss the data collected by the general ledger in terms of both journal entry transactions and snapshots at the close of an accounting period. We’ll also talk about the budgeting process.Chapter 7 discusses the following concepts:
■■ General ledger periodic snapshots and transactions
■■ Year-to-date facts
■■ Multiple fiscal accounting calendars
■■ Budgeting process and associated data, including net change granularity
■■ Consolidated fact tables that combine metrics from multiple business processes, such as actual and budget data
■■ Role of online analytic processing (OLAP) and packaged analytic financial solutions