Q: When you have multiple disparate applications with redundant dis-integrated data that very loosely applies business rules wouldn't you advocate a business rules enforced enterprise repository (ER) structure first and then a data mart for your source reports and then having the ER serve as a drill-down repository?

Chuck Kelley's Answer:

I think that there are two ways to accomplish this: 1) To use the ER structure as you define in you question, and 2) build a loosely historical layer of data and then build the data mart for drill down and reports. The reasoning behind number two is that as the business changes, you don't necessarily have to change the really tight ER structure. Either way works - you have to decide what is best for your company.

Evan Levy's Answer:

It's not apparent from your question if the data warehouse/mart is an organizational resource or an enterprise resource. Business rules are always a challenge, particular given how broad a space the term "business rules" can apply. Let me offer a starting point for you to address business rule implementation from a data relationship and representation perspective.

I recommend for EDW environments that the database model and physical structure represent enterprise based standards. These standards include data hierarchies, value representations, subject areas and their relationships, and even things like referential integrity. The depth of business rule support really reflects the amount of enterprise standardization for data and process that exists within your company.

Where rules are organizationally centric (such as client contact or relationship, financial metrics, etc.) we recommend implementing those business rules within the specific application or subject area data mart. This allows the organization to leverage enterprise data but support their organizationally centric activities (and need).

Clay Rehm's Answer:

Without agreed upon, validated and documented business rules, how are the data marts created in the first place? Your enterprise data model must reflect the business rules no matter how many multiple disparate applications there are. There must be a place that documents the business rules the organization intends to follow even if some of the applications do not enforce these rules.

Tom Haughey's Answer:

I agree with what you are saying although, as you will see, I choose to use different terminology and to describe this situation a little differently. I must confess that I find the expression of the question a little convoluted.

In essence, here is what I recommend. First put in place a central data warehouse (CDW) that contains the most basic business grains, both facts and dimensions. The CDW is the main database in the DW architecture and contains data in its most atomic form. It is a mostly normalized structure consisting of normalized analytical data. I say "mostly" normalized because most databases get partly denormalized anyway. It will not be pure star schema, that is for certain. It will contain several levels of data, including a level of data that is optimized for querying. This database has two purposes: to satisfy any query and to support any extract. Extracts are created and stored and fed to data marts to support reporting to end users, as will be some base data as well. Base data is needed to allow for drill-down, as you have described.

Your observations on business rules are important but I want to refine them. A business rule is a statement that defines or constrains some aspect of the business. Business rules are intended to assert business structure, or to control or influence the behavior of the business. OLTP systems support business operations and enforce business rules. Such business rules could be pricing rules, discounting rules, taxation rules, product configuration rules, and so forth. It may be that existing OLTP systems, as you suggest, loosely or differently enforce these rules. But by the time the data hits the DW, such business rules do not have to be re-enforced in the DW because they already have been enforced in the OLTP system. Instead, the results of this are brought across and stored in the DW. The business rules come across more as stored data, often as data derived from the OLTP system. Structurally they become the tables, columns, relationships and triggers or stored procedures of the resulting DW database. What the DW needs are reporting rules. These primarily take the form of reporting hierarchies and their supporting rules.

Nevertheless, I am in agreement with what you say, I am just refining it. There is one point that I strongly recommend you change in your thinking and it is the use of the term "ER" to refer to this main data warehouse database, which I have called the CDW. I believe this is a misuse of the term. There is an implied distinction between an ER model and a dimensional model. This distinction, I believe, is invalid and is the source of a lot of confusion in our industry. The distinction is not between a dimensional structure and an ER structure but between a dimensional structure and a normalized structure (what I called earlier a mostly normalized structure). A dimensional model actually is an ER model but it is one that has been optimized a particular way. The dimensional model is a quasi-physical ER model. Remember the term normalized in this case refers to normalized analytical data, not operational data. See my articles in DM Review, starting March 2004, "Is Dimensional Modeling One of the Great Con Jobs in Data Management History."

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access