Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

Resource Portals
Business Intelligence
Business Performance Management
Data Integration
Data Quality
Data Warehousing Basics
More Portals...


Information Center
DM Review Home
Web Seminars & Archives
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

General Resources
Industry Events Calendar
Vendor Listings
White Paper Library
Software Demo Lab
Monthly Product Guides
Buyer's Guide

General Resources
About Us
Press Releases
Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Ask the Experts Question and Answer

Ask the Expert

Meet the Experts
Ask a Question (Names of individuals and companies will not be used.)
Question Archive
Ask the Experts Home


We have a DSS system and use it to calculate profit at a slightly aggregated level.

Now our stores are increasingly being reassigned to different regions. We have a type-1 Region table. We're considering a hybrid type-1/type-2 redesign.

We've been asked by our analysts to not bother with type-2. They want us to once again calculate profit for current period, last period, and "December last year" when we see "a significant amount of store-to-region reassignments." They want us to rewrite the history of profit according to the latest store/region assignments.

Is there a technique we could use to deal with changing history for profit as well as store/region? The changing of history from a store/region point of view seems easy with a type-2 dimension. The profit part puzzles me.


Doug Hackney?s Answer: Never believe business people when they tell you they won't need the data in the future. Put the old data into a history table and keep it online or archived where you can get to it when they ask for it. They may not tell you now, but they will come back later and want to see performance data such as sales and profits in the old alignment projected through today's allocation and today's alignment projected back in time through the old performance metrics. It's the only possible way to do same-store comparisons, study the effects of the re-alignments and perform "what-ifs" on potential new alignments. Keep the store ID and the region ID at the time of the transaction in the transaction fact row and keep the current region ID in the store/region dimension. I think you should then write all the store/region alignment relationship histories (changes) into a store/region relationship history table. This will give you maximum flexibility in recreating actual history as well as forward-looking and backward-looking alignment projections.

Chuck Kelley?s Answer: Ask senior management what the business rule is for changing the regional assignments that often. Now on to your real question. Since profit has to be recalculated, is it possible to create a new dimension with different costs and factors and then do the calculations on the fly (i.e., during the query) while leaving the actuals in the fact table? If not, the only other way is to calculate the new way. However, I always hate losing real history, so why not consider a separate fact table. I would probably create what I call a sub- dimension for your region dimension and it will have a "today" view of the region. This way you can join the "today" view with the "real" view. You can use this sub-dimension to create the separate fact and give them "today?s" profit.

(Posted )


Site Map Terms of Use Privacy Policy
SourceMedia (c) 2005 DM Review and SourceMedia, Inc. All rights reserved.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.