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
 

MSAS – Applying OLAP Cubes

By | on March 3, 2005 |
MSAS
This tutorial covers the types of changes that impact on cubes and Synchronization OLAP and data warehouse data.


Multidimensional cubes are created out of the data stored in the data warehouse. Numerical measures are also summarized into pre-aggregated values when cubes are constructed. These cubes are then stored in multidimensional structures that are designed for rapid query. The pre-aggregated information is combined with raw fact data to respond to a wide variety of queries.

Types of changes that impact on cubes

Since cubes contain summarized data from the data warehouse, any changes to the data in the warehouse also affects the integrity and accuracy cubes which have been created from such data. Therefore, synchronization of data in the data warehouse and in the cubes becomes very important. OLAP data must be updated after the data in the data warehouse has been modified. Cubes, dimensions and partitions must be processed to incorporate the new or changed data. The method of processing the OLAP object depends on the kind of change that has been effected to the warehouse.

The most common type of changes to the data in the data warehouse is addition of current data to the data warehouse. Modifications of original data or change in the design of the warehouse are seldom attempted. Such additions of data will impact on cube definitions available to client applications. The impact of such additions is usually managed by carefully defining partition filters and by designing a strategy to synchronize OLAP and data warehouse data.

Changes to the data warehouse can also be made to correct errors in the data. This is usually minimized and taken care of during data transformation, scrubbing and validation operations. Changes may also occur due to changes in the structure of the organization or its products. Such changes will be incorporated into the OLTP database and then transferred to the data warehouse while migrating the data from the former to the latter. Cubes can accommodate and absorb the changes that correct value errors, but changes that move a fact table form one dimension member to another will adversely affect the integrity of the results derived from processing the cube. The data loaded into the cube will have to be refreshed by reprocessing the cube and recalculating the aggregations. The reprocessing of the cube can be done by selecting Full process or Refresh data processing options. If the aggregations remain the same the refresh data processing option will be faster.

Dimension hierarchies can also be affected by changes in the data in the data warehouse dimension tables even when the table schema remains the same. The dimension hierarchy depends on the relationships between the members of the dimension table. When the relationships change the dimension structure must be rebuilt.

Synchronization OLAP and Data Warehouse data

Valid cubes are made available to client applications. Since these cubes interact with the data in the data warehouse, a synchronization strategy must be put in place while designing the data warehouse. The strategy should provide for addition of data to the warehouse without causing data distortion and wrong reporting on queries by cubes.

Real time cubes are used to automatically update the data in the cubes, when the data in the warehouse has been updated. This is generally used when live data needs to be analyzed. They extend OLAP capabilities and do not replace the traditional cube designs and applications.

One strategy for managing additions to the data warehouse and OLAP data is the design of a batch update system. In this strategy, a batch number is assigned to all the data records in the fact table. When the cube is designed and a filter expression is added for each of the cubes partitions to specify the largest batch number. Any additions to the fact table subsequently will include a newer and higher batch number. Cubes then, become unaffected by the addition of new records as cubes are restricted to reading data only from the earlier batches. A batch number in a Dimension table will be useful in ensuring referential integrity.

Dimensions and cubes or partitions are to be processed to incorporate new data after a batch of data has been added to the fact table and the dimension tables. Shared dimensions are to be processed before cubes use them. The Incremental update option can be used to update the additions to a dimension if the structure of the dimension is not affected. However, the new members will be displayed but the cells associated with those members will remain empty till the cube is updated with new data form the fact table that relates to the new members. Use of the Rebuild the dimension structure option makes all the cubes that incorporate the dimension unavailable to the client applications.

The new data can be incorporated into the cube by updating the filter expression in each of the cube’s partitions to include the new batch number. The cube has to then be processed or incrementally updated. If the cube’s data is divided by multiple partitions, one of the partitions can be used to accumulate the data batches and that partition alone can be processed. Other partitions can be equipped with filters to prevent fresh data from being added to them.

When a cube is being deployed by a client application and the cube is being processed, the cube remains online till the processing is complete. Once the processing is complete the refreshed cube is displayed to the client application. This is true when the cube is processed with the Incremental update option or the Refresh data option. During full process the client application will be disconnected from the cube and must reconnect to the new cube after the process is complete.

The challenge is to manage changes to data warehouse data effectively and to ensure that such changes, are reflected, in the cubes in real time. The challenges are many and varied and it is important to make the right trade-offs along the way.

« « Understanding OLAP Models
Download example SQL Scripts used in Oracle 9i Tutorials » »

Author Description

Avatar

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • MSAS : Browsing the Cube

    March 10, 2005 - 0 Comment
  • MSAS – Introducing Dimension and Cube Processing

    April 17, 2005 - 0 Comment
  • Understanding Dimension Basics

    March 29, 2005 - 0 Comment
  • MSAS – Creating and Maintaining Calculated Members in Virtual Cubes

    April 24, 2005 - 0 Comment
  • Designing the Dimensional Model and Preparing the data for OLAP

    March 1, 2005 - 0 Comment
  • MSAS: Virtual Dimensions

    April 7, 2005 - 0 Comment
  • MSAS – Browsing the Dependency Network

    May 6, 2005 - 0 Comment
  • MSAS : Microsoft Data Warehousing Overview

    March 15, 2005 - 0 Comment
  • MSAS – Processing Dimensions and Cubes

    April 17, 2005 - 0 Comment
  • MSAS : Shared vs Private Dimensions

    March 31, 2005 - 0 Comment
  • MSAS – Browsing the Dependency Network

    May 6, 2005 - 0 Comment
  • MSAS – Building a Relational Decision Tree Model

    May 6, 2005 - 0 Comment
  • MSAS – Introduction to Data Mining

    May 6, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS – Introducing Analysis Services Security

    May 2, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • MSAS – Defining and Creating Drillthrough

    April 26, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • MSAS – Browsing the Dependency Network
  • MSAS – Building a Relational Decision Tree Model
  • MSAS – Introduction to Data Mining
  • MSAS – Applying security to a Dimension
  • Tutorial 65: MSAS – Managing Cube Roles

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