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

View all Portals

Scheduled Events
Archived Events

White Paper Library

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

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

    Ask The Experts published in DMReview.com
April 30, 2001
  By Douglas Hackney and Chuck Kelley


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.


For more information on related topics visit the following related portals...
Data Analysis.

Douglas Hackney is the president of Enterprise Group Ltd., a consulting and knowledge-transfer company specializing in designing and implementing data warehouses and associated information delivery systems. He can be reached at www.egltd.com.

Chuck Kelley is a senior advisory consultant for Navigator Systems (www.navigatorsystems.com), an independent consulting firm fully focused on corporate performance management (CPM) and BI solutions. Kelley is an internationally known expert in database and data warehousing technology. He has more than 25 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 45 data warehouses and data marts. He also teaches seminars, coauthored three 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 ckelley@navigatorsystems.com.

Solutions Marketplace
Provided by IndustryBrains

Business Intellignece Solutions
Unlock ERP systems and discover insight into your business. Pre built solutions from Jaros put real-time reporting and historical, trend, and analytic information at your fingertips.

GDS Solutions for Consumer Products
Get global data synchronization solutions for consumer products to help you meet customer demands quickly & accurately. Improve speed and flexibility, & better get collaboration with SAP.

Autotask: The IT Business Solution
Run your tech support, IT projects and more with our web-based business management. Optimizes resources and tracks billable project and service work. Get a demo via the web, then try it free with sample data. Click here for your FREE WHITE PAPER!

See Enterprise Business Intelligence in Action
See how business intelligence can be used to solve real business problems with this live demo from Information Builders

Free Business Intelligence articles & webcasts
BetterManagement helps decision makers connect issues with answers, problems with solutions, and ideas with perspectives from other business and government leaders, analysts, consultants and academics.

Click here to advertise in this space

E-mail This Ask The Experts E-Mail This Ask The Experts
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
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.