Some clients and students lament that while they want to deliver and share consistently-defined master conformed dimensions in their data warehouse/business intelligence (DW/BI) environments, it’s “just not feasible.” They explain that they would if they could, but with senior management focused on using agile development techniques to deliver DW/BI solutions, it’s “impossible” to take the […]
Design Tip #135 Conformed Dimensions as the Foundation for Agile Data Warehousing
Some clients and students lament that while they want to deliver and share consistently-defined master conformed dimensions in their data warehouse/business intelligence (DW/BI) environments, it’s “just not feasible.” They explain that they would if they could, but with senior management focused on using agile development techniques to deliver DW/BI solutions, it’s “impossible” to take the time to get organizational agreement on conformed dimensions. I want to turn this argument upside down by challenging that conformed dimensions enable agile DW/BI development, along with agile decision making.
Whether a Kimball specialist or not, many of you are conceptually familiar with conformed dimensions. A conformed dimension is descriptive master reference data that’s referenced in multiple dimensional models. Conformed dimensions are a fundamental element of the Kimball approach. Conformed dimensions allow DW/BI users to consistently slice-and-dice performance metrics from multiple business process data sources. Conformed dimensions allow data from different sources to be integrated based on common, unified dimension attributes. Finally, conformed dimensions allow a dimension table to be built and maintained once rather than recreating slightly different versions during each development cycle.
Reusing conformed dimensions across projects is where you get the leverage for more agile DW/BI development. As you flesh out your portfolio of master conformed dimensions, the development crank starts turning faster and faster. The time-to-market for a new business process data source shrinks as developers reuse existing conformed dimensions. Ultimately, new ETL development will focus almost exclusively on delivering more fact tables as the associated dimension tables are already sitting on the shelf ready to go.
Defining a conformed dimension requires organizational consensus and commitment to data stewardship. You don’t need to get everyone to agree on every attribute in every dimension table. At a minimum, you should identify a subset of attributes that have significance across the enterprise. These commonly-referenced descriptive characteristics become the starter set of conformed attributes, enabling drill across integration.
Over time, you can expand from this minimalistic starting point iteratively. Any special dimensional attributes unique to a single business process do not have to be discarded during the conforming process, so there are very few serious compromises. And finally, if your organization has already implemented a master data management (MDM) capability to support the transactional source systems, the work to create conformed dimensions for the enterprise data warehouse just got much easier.
If you fail to focus on conformed dimensions because you’re under pressure to deliver something yesterday, the various departmental analytic data silos will likely have inconsistent categorizations and labels. Even more troubling, data sets may look like they can be compared and integrated due to similar labels, but the underlying business rules may be slightly different. Business users waste inordinate amounts of time trying to reconcile and resolve these data inconsistencies which negatively impacts their ability to be agile decision makers.
The senior IT managers who are demanding agile systems development practices should be exerting even greater organizational pressure, in conjunction with their peers in the business, on the development of consistent conformed dimensions if they’re interested in both long-term development efficiencies and long-term decision making effectiveness across the enterprise.
Factless fact table are“fact tables that have no facts but captures the many-to-many relationship between dimension keys.” We’ve previously discussed factless fact tables to represent events or coverage information. An event-based factless fact table is student attendance information; the grain of the fact table is one row per student each day. A typical coverage factless fact […]
Accumulating snapshots are one of the three fundamental types of fact tables. We often state that accumulating snapshot fact tables are appropriate for predictable workflows with well-established milestones. They typically have five to ten key milestone dates representing the workflow/pipeline start, completion, and the key event dates in between. Our students and clients sometimes ask […]
Design Tip #43 Dealing with Nulls in the Dimensional Model describes two cases where null values should be avoided in a dimensional model; in these situations, we recommend using default values rather than nulls. This Design Tip provides guidance for selecting meaningful, verbose defaults. Handling Null Foreign Keys in Fact Tables The first scenario where […]
Industry-standard data models are an appealing concept at first blush, but they aren’t the time savers they are cracked up to be. What’s more, these prebuilt models may inhibit data warehouse project success. Vendors and proponents argue that standard, prebuilt models allow for more rapid, less risky implementations by reducing the scope of the data […]
If you’re a long time reader of the Kimball articles, Design Tips and books, you know we feel strongly about the importance of understanding the business’s data and analytic requirements. Suffice it to say that we expect more than merely inventorying the existing reports and data files; we encourage you to immerse yourself in the business community […]
The Kimball Group has frequently written about the importance of focusing on business requirements as the foundation for a successful DW/BI implementation. Design Tip #110 provides a crisp set of dos and don’ts for gathering requirements. However, some organizations find it difficult to land on the right level of detail when documenting the requirements, and […]
Whether you are developing a new dimensional data warehouse or replacing an existing environment, the ETL (extract, transform, load) implementation effort is inevitably on the critical path. Difficult data sources, unclear requirements, data quality problems, changing scope, and other unforeseen problems often conspire to put the squeeze on the ETL development team. It simply may […]
Over the years, we’ve described common dimensional modeling mistakes, such as our October ’03 “Fistful of Flaws” article in Intelligent Enterprise magazine. And we’ve recommended dimensional modeling best practices countless times; our May ’09 “Kimball’s Ten Rules of Dimensional Modeling” article has been widely read. While we’ve identified frequently-observed errors and suggested patterns, we haven’t […]
Certain industries need the ability to look at a backlog of work, and project that backlog into the future for planning purposes. The classic example is a large services organization with multi-month or multiyear contracts representing a large sum of future dollars to be earned and/or hours to be worked. Construction companies, law firms and other organizations with […]