# Ralph Kimball - The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (Highlights)  ## Metadata **Review**:: [readwise.io](https://readwise.io/bookreview/61511627) **Source**:: #from/readwise #from/zotero **Zettel**:: #zettel/fleeting **Status**:: #x **Authors**:: [[Ralph Kimball]] **Full Title**:: The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling **Category**:: #books #readwise/books **Category Icon**:: 📚 **Highlighted**:: [[2026-06-21]] **Created**:: [[2026-07-04]] ## Highlights - Practitioners and pundits alike have recognized that the presentation of data must be grounded in simplicity if it is to stand any chance of success. ([Page 0](zotero://open-pdf/library/items/MS7EIR2Z?page=28&annotation=6SDMXQCD)) ^1027271011 - The chapters should be read in order by every reader. ([Page 0](zotero://open-pdf/library/items/MS7EIR2Z?page=30&annotation=R79N5TAA)) ^1027271012 - Hopefully, you won’t need to think very hard about the industry because we want you to focus on core dimensional modeling concepts instead. ([Page 0](zotero://open-pdf/library/items/MS7EIR2Z?page=31&annotation=PDRUCN4G)) ^1027271013 - The final case study reinforces many of the patterns we discussed earlier in the book in a single set of interrelated schemas. It can be viewed as a pulling-it-all-together chapter because the modeling techniques are layered on top of one another. ([Page 0](zotero://open-pdf/library/items/MS7EIR2Z?page=33&annotation=UVQFEGHQ)) ^1027271014 ### Data Warehousing, Business Intelligence, and Dimensional Modeling Primer - The DW/BI system must make information easily accessible. The contents of the DW/BI system must be understandable. The data must be intuitive and obvious to the business user, not merely the developer. ([Page 3](zotero://open-pdf/library/items/MS7EIR2Z?page=38&annotation=73TU4RSG)) ^1027271016 - We can summarize this requirement by simply saying simple and fast. ([Page 3](zotero://open-pdf/library/items/MS7EIR2Z?page=38&annotation=4UMKBXW2)) ^1027271017 - The DW/BI system must present information consistently. The data in the DW/BI system must be credible. Data must be carefully assembled from a variety of sources, cleansed, quality assured, and released only when it is fit for user consumption. ([Page 3](zotero://open-pdf/library/items/MS7EIR2Z?page=38&annotation=SUMULXIP)) ^1027271018 - The DW/BI system must adapt to change. ([Page 4](zotero://open-pdf/library/items/MS7EIR2Z?page=39&annotation=RC6A62P9)) ^1027271019 - The DW/BI system must present information in a timely way. ([Page 4](zotero://open-pdf/library/items/MS7EIR2Z?page=39&annotation=UWU28VFS)) ^1027271020 - The DW/BI system must be a secure bastion that protects the information assets. ([Page 4](zotero://open-pdf/library/items/MS7EIR2Z?page=39&annotation=XRG8ZW3C)) ^1027271021 - The DW/BI system must serve as the authoritative and trustworthy foundation for improved decision making. ([Page 4](zotero://open-pdf/library/items/MS7EIR2Z?page=39&annotation=2JCGLTDJ)) ^1027271022 - The business community must accept the DW/BI system to deem it successful. It doesn’t matter that you built an elegant solution using best-of-breed products and platforms. If the business community does not embrace the DW/BI environment and actively use it, you have failed the acceptance test. ([Page 4](zotero://open-pdf/library/items/MS7EIR2Z?page=39&annotation=2QBBYXIB)) ^1027271023 - There are strong parallels that can be drawn between being a conventional publisher and being a DW/BI manager. Driven by the needs of the business, DW/BI managers must publish data that has been collected from a variety of sources and edited for quality and consistency. The main responsibility is to serve the readers, otherwise known as business users. The publishing metaphor underscores the need to focus outward on your customers rather than merely focusing inward on products and processes. ([Page 6](zotero://open-pdf/library/items/MS7EIR2Z?page=41&annotation=D24IVRFQ)) ^1027271024 Focus on the data user. - Make the user interfaces and applications simple and template-driven, explicitly matched to the users’ cognitive processing profiles. ([Page 6](zotero://open-pdf/library/items/MS7EIR2Z?page=41&annotation=TWNJF9QP)) ^1027271025 - Continuously monitor the accuracy of the data and analyses. ([Page 6](zotero://open-pdf/library/items/MS7EIR2Z?page=41&annotation=IMFI35ZH)) ^1027271026 How? - Dimensional modeling is a longstanding technique for making databases simple. ([Page 7](zotero://open-pdf/library/items/MS7EIR2Z?page=42&annotation=IC7JVFXK)) ^1027271027 - data model that starts simple has a chance of remaining simple at the end of the design. A model that starts complicated surely will be overly complicated at the end, resulting in slow query performance and business user rejection. ([Page 7](zotero://open-pdf/library/items/MS7EIR2Z?page=42&annotation=2ITID5HT)) ^1027271028 - Normalized 3NF structures are immensely useful in operational processing because an update or insert transaction touches the database in only one place. Normalized models, however, are too complicated for BI queries. Users can’t understand, navigate, or remember normalized models that resemble a map of the Los Angeles freeway system. ([Page 8](zotero://open-pdf/library/items/MS7EIR2Z?page=43&annotation=L5P6S8BL)) ^1027271029 - A dimensional model contains the same information as a normalized model, but packages the data in a format that delivers user understandability, query performance, and resilience to change. ([Page 8](zotero://open-pdf/library/items/MS7EIR2Z?page=43&annotation=ZSWY9B3R)) ^1027271030 - Dimensional models implemented in relational database management systems are referred to as star schemas because of their resemblance to a star-like structure. ([Page 8](zotero://open-pdf/library/items/MS7EIR2Z?page=43&annotation=G3HDANQN)) ^1027271031 - The fact table in a dimensional model stores the performance measurements resulting from an organization’s business process events. You should strive to store the low-level measurement data resulting from a business process in a single dimensional model. ([Page 10](zotero://open-pdf/library/items/MS7EIR2Z?page=45&annotation=C25Y7LLB)) ^1027271032 #definition - The idea that a measurement event in the physical world has a one-to-one relationship to a single row in the corresponding fact table is a bedrock principle for dimensional modeling. Everything else builds from this foundation. ([Page 11](zotero://open-pdf/library/items/MS7EIR2Z?page=46&annotation=5ANGV9FA)) ^1027271033 - Additivity is crucial because BI applications rarely retrieve a single fact table row. Rather, they bring back hundreds, thousands, or even millions of fact rows at a time, and the most useful thing to do with so many rows is to add them up. ([Page 11](zotero://open-pdf/library/items/MS7EIR2Z?page=46&annotation=NYAB923G)) ^1027271034 - A true text fact is rare because the unpredictable content of a text fact, like a freeform text comment, makes it nearly impossible to analyze. ([Page 12](zotero://open-pdf/library/items/MS7EIR2Z?page=47&annotation=VAB89Z99)) ^1027271035 Rethink it in the AI era. - As examples are developed throughout this book, you will see that all fact table grains fall into one of three categories: transaction, periodic snapshot, and accumulating snapshot. ([Page 12](zotero://open-pdf/library/items/MS7EIR2Z?page=47&annotation=BEXZP5Z2)) ^1027271036 - Every table that has a composite key is a fact table. Fact tables express many-to-many relationships. All others are dimension tables. ([Page 12](zotero://open-pdf/library/items/MS7EIR2Z?page=47&annotation=SC3CBLEY)) ^1027271037 - The dimension tables contain the textual context associated with a business process measurement event. They describe the “who, what, where, when, how, and why” associated with the event. ([Page 13](zotero://open-pdf/library/items/MS7EIR2Z?page=48&annotation=66CQ47TC)) ^1027271038 - In a query or report request, attributes are identified as the by words. For example, when a user wants to see dollar sales by brand, brand must be available as a dimension attribute. ([Page 13](zotero://open-pdf/library/items/MS7EIR2Z?page=48&annotation=64MNR6CS)) ^1027271039 - Attributes should consist of real words rather than cryptic abbreviations. You should strive to minimize the use of codes in dimension tables by replacing them with more verbose textual attributes. ([Page 13](zotero://open-pdf/library/items/MS7EIR2Z?page=48&annotation=G96FWITX)) ^1027271040 - Operational codes sometimes have intelligence embedded in them. For example, the first two digits may identify the line of business, whereas the next two digits may identify the global region. Rather than forcing users to interrogate or filter on substrings within the operational codes, pull out the embedded meanings and present them to users as separate dimension attributes that can easily be filtered, grouped, or reported. ([Page 14](zotero://open-pdf/library/items/MS7EIR2Z?page=49&annotation=LT9G3US6)) ^1027271041 - You often make the decision by asking whether the column is a measurement that takes on lots of values and participates in calculations (making it a fact) or is a discretely valued description that is more or less constant and participates in constraints and row labels (making it a dimensional attribute). ([Page 14](zotero://open-pdf/library/items/MS7EIR2Z?page=49&annotation=ZW4Y76RZ)) ^1027271042 - Continuously valued numeric observations are almost always facts; discrete numeric observations drawn from a small list are almost always dimension attributes. ([Page 14](zotero://open-pdf/library/items/MS7EIR2Z?page=49&annotation=RRYYZMD4)) ^1027271043 - You should resist the perhaps habitual urge to normalize data by storing only the brand code in the product dimension and creating a separate brand lookup table, and likewise for the category description in a separate category lookup table. This normalization is called snowflaking. Instead of third normal form, dimension tables typically are highly denormalized with flattened many-to-one relationships within a single dimension table. ([Page 15](zotero://open-pdf/library/items/MS7EIR2Z?page=50&annotation=R3MRICBC)) ^1027271044 #takeaway - You should almost always trade off dimension table space for simplicity and accessibility. ([Page 15](zotero://open-pdf/library/items/MS7EIR2Z?page=50&annotation=MW3XCXDU)) ^1027271045 - Each business process is represented by a dimensional model that consists of a fact table containing the event’s numeric measurements surrounded by a halo of dimension tables that contain the textual context that was true at the moment the event occurred. ([Page 16](zotero://open-pdf/library/items/MS7EIR2Z?page=51&annotation=8JBHJL2Y)) ^1027271046 - Atomic data that has not been aggregated is the most expressive data; this atomic data should be the foundation for every fact table design to withstand business users’ ad hoc attacks in which they pose unexpected queries. ([Page 17](zotero://open-pdf/library/items/MS7EIR2Z?page=52&annotation=Z4TFV6BJ)) ^1027271047 - As illustrated in Figure 1-7, there are four separate and distinct components to consider in the DW/BI environment: operational source systems, ETL system, data presentation area, and business intelligence applications. ([Page 18](zotero://open-pdf/library/items/MS7EIR2Z?page=53&annotation=E96IHANH)) ^1027271048 - Unfortunately, some DW/BI initiatives have failed miserably because they focused all their energy and resources on constructing the normalized structures rather than allocating time to developing a dimensional presentation area that supports improved business decision making. ([Page 21](zotero://open-pdf/library/items/MS7EIR2Z?page=56&annotation=4M8M8JUC)) ^1027271049 #caveat - Our second stake in the ground about the presentation area is that it must contain detailed, atomic data. ([Page 21](zotero://open-pdf/library/items/MS7EIR2Z?page=56&annotation=YPW9WFPA)) ^1027271050 - If you have any hope of building a robust and integrated DW/BI environment, you must commit to the enterprise bus architecture. ([Page 22](zotero://open-pdf/library/items/MS7EIR2Z?page=57&annotation=7YFTT3XX)) ^1027271051 - Data in the queryable presentation area of the DW/BI system must be dimensional, atomic (complemented by performance-enhancing aggregates), business process-centric, and adhere to the enterprise data warehouse bus architecture. The data must not be structured according to individual departments’ interpretation of the data. ([Page 22](zotero://open-pdf/library/items/MS7EIR2Z?page=57&annotation=ZGNJMZ8N)) ^1027271052 - Remember, when you pre-suppose the business question, you’ll likely pre-summarize the data, which can be fatal in the long run. ([Page 32](zotero://open-pdf/library/items/MS7EIR2Z?page=67&annotation=SVV9SY9D)) ^1027271053 - When gathering requirements for a DW/BI initiative, you need to listen for and then synthesize the findings around business processes. ([Page 32](zotero://open-pdf/library/items/MS7EIR2Z?page=67&annotation=MKA2SAQ4)) ^1027271054 ### Kimball Dimensional Modeling Techniques Overview - Dimensional models should not be designed in isolation by folks who don’t fully understand the business and their needs; collaboration is critical! ([Page 38](zotero://open-pdf/library/items/MS7EIR2Z?page=73&annotation=YCXK8VJX)) ^1027271056 - The four key decisions made during the design of a dimensional model include: 1. Select the business process. 2. Declare the grain. 3. Identify the dimensions. 4. Identify the facts. ([Page 38](zotero://open-pdf/library/items/MS7EIR2Z?page=73&annotation=4TAD9FC3)) ^1027271057 - The grain establishes exactly what a single fact table row represents. ([Page 39](zotero://open-pdf/library/items/MS7EIR2Z?page=74&annotation=8KJ2H6Y6)) ^1027271058 #definition - Rather than a null foreign key, the associated dimension table must have a default row (and surrogate key) representing the unknown or not applicable condition. ([Page 42](zotero://open-pdf/library/items/MS7EIR2Z?page=77&annotation=MIT75TU8)) ^1027271059 - For example, an event of a student attending a class on a given day may not have a recorded numeric fact, but a fact row with foreign keys for calendar day, student, teacher, location, and class is well-defined. Likewise, customer communications are events, but there may be no associated metrics. ([Page 44](zotero://open-pdf/library/items/MS7EIR2Z?page=79&annotation=HALAFSI7)) ^1027271060 ### Retail Sales - Whenever possible, you should express the grain in business terms. ([Page 71](zotero://open-pdf/library/items/MS7EIR2Z?page=106&annotation=Y5RX85CS)) ^1027271062 - In debugging thousands of dimensional designs over the years, the most frequent error is not declaring the grain of the fact table at the beginning of the design process. ([Page 71](zotero://open-pdf/library/items/MS7EIR2Z?page=106&annotation=PPB4BTHZ)) ^1027271063 - Facts that clearly belong to a different grain must be in a separate fact table. ([Page 72](zotero://open-pdf/library/items/MS7EIR2Z?page=107&annotation=6TGMDMCY)) ^1027271064 - We strongly encourage you to resist the temptation to model the data by looking at source data alone. It may be less intimidating to dive into the data rather than interview a business person; however, the data is no substitute for business user input. ([Page 72](zotero://open-pdf/library/items/MS7EIR2Z?page=107&annotation=X93NHWIB)) ^1027271065 - The first DW/BI project should focus on the business process that is both the most critical to the business users, as well as the most feasible. Feasibility covers a range of considerations, including data availability and quality, as well as organizational readiness. ([Page 74](zotero://open-pdf/library/items/MS7EIR2Z?page=109&annotation=U3LSY77G)) ^1027271066 - The extended sales dollar amount equals the sales quantity multiplied by the net unit price. Likewise, the extended discount dollar amount is the sales quantity multiplied by the unit discount amount. ([Page 76](zotero://open-pdf/library/items/MS7EIR2Z?page=111&annotation=M8B29HC3)) ^1027271067 - Dimensional modelers sometimes question whether a calculated derived fact should be stored in the database. We generally recommend it be stored physically. In this case study, the gross profit calculation is straightforward, but storing it means it’s computed consistently in the ETL process, eliminating the possibility of user calculation errors. ([Page 77](zotero://open-pdf/library/items/MS7EIR2Z?page=112&annotation=HXCTAN7I)) ^1027271068 - Percentages and ratios, such as gross margin, are non-additive. The numerator and denominator should be stored in the fact table. ([Page 78](zotero://open-pdf/library/items/MS7EIR2Z?page=113&annotation=6GRAZY2T)) ^1027271069 - The metrics resulting from transactional events are typically additive as long as they have been extended by the quantity amount, rather than capturing per unit metrics. ([Page 79](zotero://open-pdf/library/items/MS7EIR2Z?page=114&annotation=G2WLCUXZ)) ^1027271070 - The date dimension is a special dimension because it is the one dimension nearly guaranteed to be in every dimensional model since virtually every business process captures a time series of performance metrics. ([Page 79](zotero://open-pdf/library/items/MS7EIR2Z?page=114&annotation=GX2ZRDNB)) ^1027271071 - Presuming the business needs to slice data by these nonstandard date attributes, then an explicit date dimension table is essential. Calendar logic belongs in a dimension table, not in the application code. ([Page 82](zotero://open-pdf/library/items/MS7EIR2Z?page=117&annotation=DSAM43FE)) ^1027271072 - Because dimension table attributes serve as report labels and values in pull-down query filter lists, this indicator should be populated with meaningful values such as Holiday or Non-holiday instead of the cryptic Y/N, 1/0, or True/False. ([Page 82](zotero://open-pdf/library/items/MS7EIR2Z?page=117&annotation=3EBM5Z93)) ^1027271073 - time-ofday is typically separated from the date dimension to avoid a row count explosion in the date dimension ([Page 83](zotero://open-pdf/library/items/MS7EIR2Z?page=118&annotation=9GW6PSJG)) ^1027271074 - You do not need to separate these repeated values into a second normalized table to save space. Remember dimension table space requirements pale in comparison with fact table space considerations. ([Page 84](zotero://open-pdf/library/items/MS7EIR2Z?page=119&annotation=RSVA6F93)) ^1027271075 - Keeping the repeated low cardinality values in the primary dimension table is a fundamental dimensional modeling technique. ([Page 84](zotero://open-pdf/library/items/MS7EIR2Z?page=119&annotation=PULUW6TI)) ^1027271076 - If the stable numeric value is used predominantly for filtering and grouping, it should be treated as a product dimension attribute. ([Page 86](zotero://open-pdf/library/items/MS7EIR2Z?page=121&annotation=D5NDBJH9)) ^1027271077 - Sometimes numeric values serve both calculation and filtering/grouping functions. In these cases, you should store the value in both the fact and dimension tables. ([Page 86](zotero://open-pdf/library/items/MS7EIR2Z?page=121&annotation=X8CZPV4C)) ^1027271078 - These distinct logical views on a single physical date dimension are an example of dimension role playing, which we’ll discuss more fully in Chapter 6: Order Management. ([Page 89](zotero://open-pdf/library/items/MS7EIR2Z?page=124&annotation=2SFZDZPV)) ^1027271079 - The various possible causal conditions are highly correlated. A temporary price reduction usually is associated with an ad and perhaps an end aisle display. For this reason, it makes sense to create one row in the promotion dimension for each combination of promotion conditions that occurs. ([Page 90](zotero://open-pdf/library/items/MS7EIR2Z?page=125&annotation=ST48XAMK)) ^1027271080 - If the four causal mechanisms are highly correlated, the combined single dimension is not much larger than any one of the separated dimensions would be. ([Page 91](zotero://open-pdf/library/items/MS7EIR2Z?page=126&annotation=MD5MB4CZ)) ^1027271081 - You must avoid null keys in the fact table. A proper design includes a row in the corresponding dimension table to identify that the dimension is not applicable to the measurement. ([Page 92](zotero://open-pdf/library/items/MS7EIR2Z?page=127&annotation=D5YML8MZ)) ^1027271082 - We sometimes encounter nulls as dimension attribute values. These usually result when a given dimension row has not been fully populated, or when there are attributes that are not applicable to all the dimension’s rows. In either case, we recommend substituting a descriptive string, such as Unknown or Not Applicable, in place of the null value. ([Page 92](zotero://open-pdf/library/items/MS7EIR2Z?page=127&annotation=9KSWTE3R)) ^1027271083 - Because the resulting dimension is empty, we refer to the POS transaction number as a degenerate dimension ([Page 93](zotero://open-pdf/library/items/MS7EIR2Z?page=128&annotation=Z8MA67KK)) ^1027271084 A dimension that has only one column–the key. - The degenerate dimension is a dimension key without a corresponding dimension table. ([Page 94](zotero://open-pdf/library/items/MS7EIR2Z?page=129&annotation=NY9R29NP)) ^1027271085 #definition - Because you can’t ask shoppers to bring in all their old cash register receipts to tag their historical sales transactions with their new frequent shopper number, you’d substitute a default shopper dimension surrogate key, corresponding to a Prior to Frequent Shopper Program dimension row, on the historical fact table rows. Likewise, not everyone who shops at the grocery store will have a frequent shopper card, so you’d also want to include a Frequent Shopper Not Identified row in the shopper dimension. ([Page 96](zotero://open-pdf/library/items/MS7EIR2Z?page=131&annotation=TA8SYQAW)) ^1027271086 - There is one important question that cannot be answered by the previous retail sales schema: What products were on promotion but did not sell? The sales fact table records only the SKUs actually sold. There are no fact table rows with zero facts for SKUs that didn’t sell because doing so would enlarge the fact table enormously. ([Page 97](zotero://open-pdf/library/items/MS7EIR2Z?page=132&annotation=HILQ529K)) ^1027271087 #caveat - In the case of the promotion coverage fact table, you’d load one row for each product on promotion in a store each day (or week, if retail promotions are a week in duration) regardless of whether the product sold. ([Page 97](zotero://open-pdf/library/items/MS7EIR2Z?page=132&annotation=RJY8XNKS)) ^1027271088 - The unique primary key of a dimension table should be a surrogate key rather than relying on the operational system identifier, known as the natural key. Surrogate keys go by many other aliases: meaningless keys, integer keys, non-natural keys, artificial keys, and synthetic keys. ([Page 98](zotero://open-pdf/library/items/MS7EIR2Z?page=133&annotation=G9YUY7C7)) ^1027271089 - Likewise, queries and data access applications should not have any built-in dependency on the keys because the logic also would be vulnerable to invalidation. Even if the natural keys appear to be stable and devoid of meaning, don’t be tempted to use them as the dimension table’s primary key. ([Page 99](zotero://open-pdf/library/items/MS7EIR2Z?page=134&annotation=GLHICKM7)) ^1027271090 - The multitude of snowflaked tables makes for a much more complex presentation. ([Page 105](zotero://open-pdf/library/items/MS7EIR2Z?page=140&annotation=6FSD2SIH)) ^1027271091 - We affectionately refer to these designs as centipede fact tables because they appear to have nearly 100 legs ([Page 108](zotero://open-pdf/library/items/MS7EIR2Z?page=143&annotation=R7887BZE)) ^1027271092 - Designing a fact table with too many dimensions leads to significantly increased fact table disk space requirements. ([Page 109](zotero://open-pdf/library/items/MS7EIR2Z?page=144&annotation=QGTY8LFH)) ^1027271093 - Most business processes can be represented with less than 20 dimensions in the fact table. If a design has 25 or more dimensions, you should look for ways to combine correlated dimensions into a single dimension. ([Page 109](zotero://open-pdf/library/items/MS7EIR2Z?page=144&annotation=V2YT47DA)) ^1027271094 ### Inventory - the 60 daily and 148 weekly snapshots should be stored in two separate fact tables given their unique periodicity. ([Page 114](zotero://open-pdf/library/items/MS7EIR2Z?page=149&annotation=33RG3DZW)) ^1027271096 - If performance measurements have different natural granularity or dimensionality, they likely result from separate processes that should be modeled as separate fact tables. ([Page 118](zotero://open-pdf/library/items/MS7EIR2Z?page=153&annotation=U38ZJJJ7)) ^1027271097 - In this inventory model, one row is placed in the fact table when a particular product is received at the warehouse. The disposition of the product is tracked on this single fact row until it leaves the warehouse. ([Page 118](zotero://open-pdf/library/items/MS7EIR2Z?page=153&annotation=JIJI9UG3)) ^1027271098 - Accumulating snapshots are most appropriate when business users want to perform workflow or pipeline analysis. ([Page 121](zotero://open-pdf/library/items/MS7EIR2Z?page=156&annotation=GWBHKSTJ)) ^1027271099 - By defining a standard bus interface for the DW/BI environment, separate dimensional models can be implemented by different groups at different times. The separate business process subject areas plug together and usefully coexist if they adhere to the standard. ([Page 124](zotero://open-pdf/library/items/MS7EIR2Z?page=159&annotation=KLPP2F5N)) ^1027271100 - At the most basic level, conformed dimensions mean the same thing with every possible fact table to which they are joined. ([Page 167](zotero://open-pdf/library/items/MS7EIR2Z?page=166&annotation=7PPCE6UZ)) ^1027271101 - This would be the case if you had a weekly inventory snapshot in addition to the daily snapshot. In other situations, facts are generated by another business process at a higher level of granularity. For example, the retail sales process captures data at the atomic product level, whereas forecasting generates data at the brand level. ([Page 168](zotero://open-pdf/library/items/MS7EIR2Z?page=167&annotation=3D5BDXJ9)) ^1027271102 - Another case of conformed dimension subsetting occurs when two dimensions are at the same level of detail, but one represents only a subset of rows. ([Page 168](zotero://open-pdf/library/items/MS7EIR2Z?page=167&annotation=RZZG6VIQ)) ^1027271103 - If each line of business has unique customers and unique products and there’s no interest in cross-selling across lines, it may not make sense to attempt an enterprise architecture because there likely isn’t much perceived business value. ([Page 171](zotero://open-pdf/library/items/MS7EIR2Z?page=170&annotation=28M5UXSU)) ^1027271104 - Thus far we have considered the central task of setting up conformed dimensions to tie dimensional models together. This is 95 percent or more of the data architecture eff ort. The remaining 5 percent of the eff ort goes into establishing conformed fact defi nitions ([Page 174](zotero://open-pdf/library/items/MS7EIR2Z?page=173&annotation=TJZTNNP2)) ^1027271105 - You must be disciplined in your data naming practices. If it is impossible to conform a fact exactly, you should give diff erent names to the diff erent interpretations so that business users do not combine these incompatible facts in calculations. ([Page 175](zotero://open-pdf/library/items/MS7EIR2Z?page=174&annotation=3AYNL6ZJ)) ^1027271106 ### Accounting - If this type of drill-across analysis is extremely common in the user community, it likely makes sense to create a single fact table that combines the metrics once rather than relying on business users or their BI reporting applications to stitch together result sets, especially given the inherent issues of complexity, accuracy, tool capabilities, and performance. ([Page 260](zotero://open-pdf/library/items/MS7EIR2Z?page=259&annotation=45RXH8ZM)) ^1027271108 - Fact tables that combine metrics from multiple business processes at a common granularity are referred to as consolidated fact tables. ([Page 261](zotero://open-pdf/library/items/MS7EIR2Z?page=260&annotation=MEDANIDX)) ^1027271109 #definition - One potential risk associated with consolidated fact tables is that project teams sometimes base designs solely on the granularity of the consolidated table, while failing to meet user requirements that demand the ability to dive into more granular data. ([Page 261](zotero://open-pdf/library/items/MS7EIR2Z?page=260&annotation=EIYBARRI)) ^1027271110 #caveat ### Financial Services - In general, most dimensional models end up with between fi ve and 20 dimensions. If you are at or below the low end of this range, you should be suspicious that dimensions may have been inadvertently left out of the design. ([Page 320](zotero://open-pdf/library/items/MS7EIR2Z?page=319&annotation=V837KBRU)) ^1027271112 - Any descriptive attribute that is single-valued in the presence of the measurements in the fact table is a good candidate to be added to an existing dimension or to be its own dimension. ([Page 320](zotero://open-pdf/library/items/MS7EIR2Z?page=319&annotation=F6JJECRH)) ^1027271113 - The keys of the subtype account dimensions are the same keys used in the supertype account dimension, which contains all possible account keys. ([Page 330](zotero://open-pdf/library/items/MS7EIR2Z?page=329&annotation=RMLN8J98)) ^1027271114 ### Healthcare - The design shown in Figure 14-3 handles the open-ended nature of multiple diagnoses. The diagnosis foreign key in the fact table is replaced with a diagnosis group key. This diagnosis group key is connected by a many-to-many join to a diagnosis group bridge table, which contains a separate row for each individual diagnosis in a particular group. ([Page 382](zotero://open-pdf/library/items/MS7EIR2Z?page=381&annotation=7JHXCD6U)) ^1027271116