Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

How To Manage Current and Historical Information Within Your Data Warehouse

By Exforsys | on September 11, 2006 |
Data Warehousing

How To Manage Current and Historical Information Within Your Data Warehouse

In order for a company to use a data warehouse successfully, it must be designed so that users are able to analyze historical and current information. There are a number of things that will result from this technique, and they will have an effect on the data model design and ETL functions.

As an example, a finance company may need to analyze their profits for the last three years. Looking at the data from the last three years will allow the user to view the transformations the company has gone through. Unfortunately, only having a current view will not allow the user to get the information they need.

One of the biggest challenges that data warehouse managers face today is the issue of how to manage dimensional tables over a given period of time. Not only must they be able to do this, but they must also be able to manage this information with current data. There are a number of basic modeling techniques that are used, and one example of this is slowly changing dimensions, which can come in type 2 or 3. The SCD 2 technique can be utilized to display a dimensional table when a change within similar columns needs to be analyzed over a given period of time. The SCD 2 method will use keys within the table that will not change.

Whenever a change occurs, a new key will be added to the table. If meta data columns were added to the structure of the table, the new keys can be tagged as current while the records which were loaded in the past can be tagged as being historical. The SCD 2 is a technique which will solve part of the problem, because it will add historical information into the dimension. The queries that are run within the table will generate the correct historical views, and can use the keys from both tables. The second common element that is used to generate historical information is called SQL, or Structured query language.

The goal of using SQL is to produce facts which are grouped together over time. The information is grouped based on iterations and the keys that the dimensions have moved through. Using SQL may not be helpful in a situation where the goal is to overlook historical changes which have been made to a table. It may also not be useful when all the facts that must be displayed must be connected to current information. The meta data can find the rows in the table and ignore certain types of data. The fact table will be connected to the historical keys. If the current dimension is constrained, and the fact table data is retrieved, the reporting results may not be correct.

Intricate SQL techniques can be utilized in order to gain the data on the current row. As the same time, you will still be able gather the fact table rows which are connected to the production key. The success of SCD 2 is dependent on abilities of the tool. The use of the meta data will also play a role in its success. Another method that is used to balance historical and current data is SCD 3. Specifically, SCD 3 can model a dimension table to grab the current and historical changes which have been made on a key. It will use two columns within the table, and while one of them will be for current data, the other will be used for historical data.

The fact tables are connected to a single row, and this solves the problem of using multiple keys which is found when using SCD 2. A database management system can be utilized to identify either the historical or current columns.

It may also be possible for other tools to use alternative techniques. Another technique that is used to manage historical and current information is SCD 2+. With this technique, you will simply manage two sets of tables, and one will deal with current facts while the other will deal with historical facts. If you want to use SCD 2+, you will need a tool that can use the dimension tables that will be used to issue reports.

« « Object Oriented Programming Issues
Selecting Topics for Group Discussion » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Data Warehouse Tools

    August 18, 2006 - 0 Comment
  • Advantages and Disadvantages to Using a Data Warehouse

    September 15, 2006 - 0 Comment
  • Fundamental Themes For Your Data Warehouse

    February 8, 2007 - 0 Comment
  • Data Warehousing Methods

    August 22, 2006 - 0 Comment
  • The Difference Between Data Mart and Data Warehouse

    September 15, 2006 - 0 Comment
  • What You Should Know About Building a Data Warehouse

    February 10, 2007 - 0 Comment
  • Data Warehouse Design Strategies

    August 27, 2006 - 0 Comment
  • Historical Information About Data Warehouses

    January 31, 2007 - 0 Comment
  • How To Rate Your Data Warehouse

    February 10, 2007 - 0 Comment
  • How To Assess Your Data Warehouse

    August 30, 2006 - 0 Comment
  • Data Warehouses Non Technical Issues

    June 23, 2007 - 0 Comment
  • How To Evaluate The Software For your Data Warehouse

    February 15, 2007 - 0 Comment
  • Understanding The Challenges of Using Data Warehouses

    February 15, 2007 - 0 Comment
  • Understanding Quality Management For Data Warehouses

    February 13, 2007 - 0 Comment
  • Creating an Efficient Process for Data Warehouses

    February 13, 2007 - 0 Comment
  • How Does a Data Warehouse Differ From a Database

    February 11, 2007 - 0 Comment
  • How Data Is Stored Within a Data Warehouse

    February 11, 2007 - 0 Comment
  • How To Rate Your Data Warehouse

    February 10, 2007 - 0 Comment
  • What You Should Know About Building a Data Warehouse

    February 10, 2007 - 0 Comment
  • Fundamental Themes For Your Data Warehouse

    February 8, 2007 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Data Warehouses Non Technical Issues
  • How To Evaluate The Software For your Data Warehouse
  • Understanding The Challenges of Using Data Warehouses
  • Understanding Quality Management For Data Warehouses
  • Creating an Efficient Process for Data Warehouses

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT