FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Would you advocate a business rules enforced enterprise repository (ER) structure first, then a data mart for your source reports and then having the ER serve as a drill-down repository?

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."


Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

Clay Rehm, CCP, PMP, is president of Rehm Technology (www.rehmtech.com), a consulting firm specializing in data integration solutions. Rehm provides hands-on expertise in project management, assessments, methodologies, data modeling, database design, metadata and systems analysis, design and development. He has worked in multiple platforms and his experience spans operational and data warehouse environments. Rehm is a technical book editor and is a co-author of the book, Impossible Data Warehouse Situations with Solutions from the Experts. In addition, he is a Certified Computing Professional (CCP), a certified Project Management Professional (PMP), holds a Bachelors of Science degree in Computer Science and a Masters Degree in Software Engineering from Carroll College. He can be reached at clay.rehm@rehmtech.com.

Evan Levy is a partner and co-founder of Baseline Consulting Group, a multivendor systems integration and consulting firm. As the partner in charge of Baseline’s largest practice, Levy leads both executives and practitioners in delivering technology solutions that help business users make better decisions. He has led strategic technology implementations at commercial and public sector organizations and advises vendors on their product development and delivery strategies. Levy has been published in a wide array of industry magazines and has lectured on a range of technology delivery experiences at leading conferences and vendor events. He has been a featured speaker at the Marcus Evans Analytical CRM symposium, DCI’s Data Warehousing conference, the CRM Association, DAMA International, the AMA and the Data Warehousing Institute. His current work involves delivering and lecturing extensively on the topic of data integration. You can contact him at evanlevy@baseline-consulting.com.

For more information on related topics, visit the following channels:



Industry Vendors