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 – Processing Dimensions and Cubes

By Exforsys | on April 17, 2005 |
MSAS
As stated earlier, maps are created when a dimension is processed. However, existing maps of a dimension are destroyed when it is processed and new maps are created. Consequently all cubes accessing the dimension will find it inaccessible and the cube will be invalid. The dimension will become accessible only when the cube is reprocessed.

When cubes contain a large number of dimensions and some of the dimensions undergo a change, reprocessing a cube can become a daunting task. However, Analysis server has a solution to this problem. It is possible to Incremental update a dimension. This process does not destroy existing maps and therefore does not invalidate cubes using the dimension.

Dimension Processing

As stated earlier, maps are created when a dimension is processed. However, existing maps of a dimension are destroyed when it is processed and new maps are created. Consequently all cubes accessing the dimension will find it inaccessible and the cube will be invalid. The dimension will become accessible only when the cube is reprocessed. When cubes contain a large number of dimensions and some of the dimensions undergo a change, reprocessing a cube can become a daunting task. However, Analysis server has a solution to this problem. It is possible to Incremental update a dimension. This process does not destroy existing maps and therefore does not invalidate cubes using the dimension.


Some of the changes that are made to a dimension during incremental update are the addition of new members, changing the property of existing members etc. Any other changes to a standard dimension will destroy the map and invalidate the cube. For example, you add new customer records to the customer dimension table. A cube that includes a shared dimension remains available to users while the dimension is incrementally updated, and the added dimension members are available in the cube after the update is complete. Because almost any property of a member can be changed by changing the appropriate field in the underlying dimension table, it is possible to have changed member names after the dimension is incrementally updated. The incremental update is also available only if a single shared dimension is updated. In other words the Incremental update processing option updates a dimension when changes have been made to the underlying tables of a dimension, but no structural changes have been made to the dimension itself.

Let us explore the screens that help the user incremental update a dimension. Let us say that we have added new sales_regions to the table and need to update the dimension to incorporate the incremented table.

Incrementally Updating a Dimension

1. In the console tree, Open Shared Dimensions folder and right click Department dimension and click Process


2. Select Incremental update option and click OK. Close the Process Log window.

3. When performing incremental update on a dimension, the Analysis server uses an SQL statement to extract information from the dimension. It is a repeat of the process followed when rebuilding the dimensions structure. However, new paths are created only for the new members. The new member is assigned the first unused number for the children under the region. For instance if Vera Cruz from Mexico is added to the dimension table, Vera Cruz will be assigned which is the first available number. However, when the dimension is rebuilt the member Vera Cruz will become the first state in the region and will be given the path.

4. Since the hierarchical view of data is important in Dimension processing, Analysis server will regenerate the Member Id for the entire dimension while incrementally updating the dimension. Changing the member Id does not invalidate the cube because the cube only looks at the path.

Changing Dimensions

If a dimension is flagged as a changing dimension, the existing members can be renamed or the parent of member can be changed. A changing dimension is optimized for frequent changes and it permits more types of changes than standard dimensions. Changing dimensions are useful as changes can be made without interrupting the end user and end users have to have access to changes immediately. Virtual dimensions, parent-child dimensions and regular dimensions using ROLAP storage are all changing dimensions. In changing dimensions levels below the top or above the bottom can be added, modified or moved and no processing is required. The only exception is the addition of a member group where the dimension will have to be processed.

Rebuilding the Structure of a Dimension

The Rebuild the dimension structure option re-creates and loads the dimension. This processing option is required after:

The structure of the dimension is changed. For example, after a level in the hierarchy is added or removed.

Relationships between members in the dimension hierarchy are changed. For example, after the sales regions are redefined so that the cities are now in different regions.

To rebuild the structure of a shared dimension

In the Analysis Manager tree pane, under the database that contains the shared dimension, expand the Shared Dimensions folder.

Right-click the shared dimension, and then click Process.

In the Process a Dimension dialog box, click Rebuild the dimension structure, and then click OK.

In the Process dialog box, wait for the rebuild to finish processing or click Stop to halt and cancel processing.

Cubes that use the Dimension must be processed after the dimension is processed.

Processing Cubes

While processing a cube the dimension tables are read to populate the levels with members from the actual data from the tables. The fact table is read and specified aggregations are calculated. The results are stored in the cube and the cube is ready to be queried.

Three kinds of options are available for processing cubes.

Full Process: is the processing option used to perform a complete load of the cube. All dimension and fact table data is read and all specified aggregations are calculated. The cube must be processed with the Full Process option when its structure is new or when the cube, its dimensions, or its measures have undergone structural changes. In addition, virtual and linked cubes also require complete processing after they are built, or there is a change in their structure, or a change in one of their shared dimensions. To process a virtual cube or a linked cube, use the Process dialog box.

Cubes with changing dimensions are exceptions to the rule. Where only changes have been made to the structure of changing dimensions of a cube the cube need not be processed with the Full Process option. However, processing with the other options may be required.

Processing a cube with the Full Process option can take a substantial amount of time if there is a large fact table and there are many dimensions with many levels and many items in each level..

If there are changes in the data warehouse schema that affect the structure of cubes, the structure of those cubes will have to be changed and then processed with the Full Process option. If there are changes in or additions to data in the data warehouse, completely process cubes will not be required. Such changes can be incorporated into existing cubes using the Incremental update or Refresh data processing options, depending on how the data changed.

The Full Process option can be used while users continue to query a previously processed cube; however, after processing has completed, users need to disconnect and reconnect to reestablish access to the cube.

Please note that if a shared dimension’s structure is updated and saved but not processed, it will be processed automatically when any cube that incorporates the dimension is processed using the Full Process option. Cubes that use this dimension will not be able to access the cube during this period.

Incremental update is used when new data is to be added to a cube, but existing data has not changed and the cube structure remains the same. The Incremental update option adds new data and updates aggregations.

An incremental update does not impact the existing data that has already been processed. It usually requires significantly less time than processing with the Full Process option. An incremental update can be performed while users continue to query the cube; after the update is complete, users have access to the additional data without having to disconnect and reconnect.

Incremental Updates and Partitions
When partitions are created and managed in multiple partition cubes, the user must take special precautions to ensure accurate cube data. Although these precautions do not usually apply to single-partition cubes, they do apply when they are incrementally updated.

On incrementally updating a cube, a new partition is created and merged with the existing partitions. The Incremental Update Wizard requires the specification of the data source and fact table of the temporary partition. It also requires the specification of a filter to limit the contents of the temporary partition. If the cube contains multiple partitions, specification of the partition into which the temporary partition is merged has to be indicated. If the cube contains only one partition, the temporary partition is merged into that partition.

To ensure accurate cube data, before performing an incremental update on any cube the user must understand the special precautions related to data integrity that apply to multiple-partition cubes. We will be discussing this in greater detail in the lesson on “Managing Partitions”

Refresh data option causes a cube’s data to be cleared and reloaded and its aggregations recalculated. This option is appropriate when the underlying data in the data warehouse has changed but the cube’s structure remains the same.

The Refresh data option can be performed while users continue to query the cube; after the refresh has completed, users have access to the updated data without having to disconnect and reconnect.

Additionally users can make use of a fourth option of “Incrementally update the Dimensions of a cube” in conjunction with any of the options listed above. This option incrementally updates the cubes dimensions as part of cube processing.

Process a cube

In the Analysis Manager tree pane, under the database that contains the cube, expand the Cubes folder.

Right-click the cube, and then click Process.

In the Process a Cube dialog box, click Full Process, and then click OK.

In the Process dialog box, wait for the cube to finish processing, or click Stop to halt and cancel processing.

After processing completes, the SQL statement used to process the cube can be viewed..

To view an SQL statement

In the Process dialog box, click a line beginning with the SQL icon.

Click View Details.

The time taken for the processing will depend on the size of the cube and the number of dimensions it has. Also, the storage required for temporary files during processing can be substantially larger than the final size of the cube. If during processing the free space of the disk containing the temporary file folder is exhausted, the user can specify a folder on another disk with more free space.

To change the temporary file folder used by Analysis Services

  1. In the Analysis Manager tree pane, right-click the Analysis server for which you want to change the temporary file folder, and then click Properties.
  2. Beside the Temporary file folder box, click Browse, select a new folder, and then click OK.

  1. In the Properties dialog box, click OK.
  2. The MSSQLServerOLAPService service must be stopped and restarted after this change..

Cube Properties for Processing

Some properties of a cube are used to control its processing. You can set these properties in the properties pane of Cube Editor or in the Cube Processing Settings dialog box, which is displayed when you click Settings in the Process a Cube dialog box. The following table describes these properties.


Processing Optimization Mode

Selection of ‘Regular’ in Cube Editor or ‘After all aggregations are calculated’ in the Cube Processing Settings dialog box, new cube data is not available until processing completes. These values are the defaults. If Lazy Aggregations is selected in Cube Editor or Immediately after data is loaded in the Cube Processing Settings dialog box, new cube data is available before processing completes; however, because the optimizations are not complete when the new data becomes available, query performance is reduced until the optimizations complete.

Stop Processing on Key Errors : Stop processing after encountering missing dimension key errors or Ignore all missing dimension key errors

If Yes is selected in Cube Editor or Stop processing after encountering missing dimension key errors in the Cube Processing Settings dialog box, processing is halted and canceled when the specified limit for the number of dimension key errors is exceeded. (See Key Error Limit.) A dimension key error occurs when a fact table row is encountered that contains a foreign key value not present in the joined primary key column of a dimension table. If No is selected in Cube Editor or Ignore all missing dimension key errors in the Cube Processing Settings dialog box, dimension key errors never halt and cancel cube processing regardless of the number of errors encountered. If one or more dimension key errors are encountered, the cube’s data does not reflect the entire fact table.



Key Error Limit : Processing will stop after

This is the limit for the number of dimension key errors. Cube processing is halted and canceled when the limit is exceeded. The default is 0. If a higher number is selected and processing completes, the cube’s data does not reflect the entire fact table. This property is ignored if No is selected for the Stop Processing on Key Errors property in Cube Editor, or Ignore all missing dimension key errors in the Cube Processing Settings dialog box is selected.

Key Error Log File : File path and name

Incrementally update a cube

This procedure updates a partition. Incorrect use of partitions can result in inaccurate cube data.

  1. In the Analysis Manager tree pane, under the database that contains the cube, expand the Cubes folder.
  2. Right-click the cube, and then click Process.
  3. In the Process a Cube dialog box, click Incremental update.
  4. (Optional.) To incrementally update shared dimensions contained in the cube during processing, select Incrementally update the shared dimensions used in this cube.
  5. Click OK to display the Incremental Update Wizard

In the Incremental Update Wizard:

In the Welcome step, click Next.

If the cube contains multiple partitions, the Select a partition to update step appears. In the Partition box, select the partition to update, and then click Next.

In the Data source box, select the data source that contains the data to add to the partition. The same data source used by the partition can be selected or a different one. By default the same data source used by the partition is initially displayed. To select a different data source, click Change, select the data source, and then click OK. If a different data source is selected, it must contain a fact table with the same structure and columns as the fact table for the partition, and it must contain dimension tables with the same structure and columns as the partition’s dimension tables.

In the Fact table box, select the table that contains the data to add to the partition. The partition’s fact table can be selected or a different table. By default the partition’s fact table is initially displayed. If this table is selected, the user must use a filter, as described in the next step, to ensure that only data not already in the partition is added. To select a different table, click Change, select the table, and then click OK. If a different table is selected, it must have the same structure and columns as the fact table for the partition. The table must also be manually merged with the fact table for the partition after the incremental update completes. Click Next.

Specify a filter (WHERE clause expression) to limit the data selected from the fact table and added to the partition. A filter is required if the fact table for the partition is selected as the fact table for the incremental update. Click Next

Click Finish.

In the Process dialog box, wait for the incremental update to finish processing, or click Stop to halt and cancel processing.

To refresh data in a cube

  1. In the Analysis Manager tree pane, under the database that contains the cube, expand the Cubes folder.
  2. Right-click the cube, and then click Process.
  3. In the Process a Cube dialog box, click Refresh data.
  4. (Optional.) To incrementally update shared dimensions contained in the cube during processing, select Incrementally update the shared dimensions used in this cube.
  5. Click OK.
  6. In the Process dialog box, wait for the data refresh to finish processing, or click Stop to halt and cancel processing.
  7. « « MSAS – Introducing Dimension and Cube Processing
    MSAS – Optimizing Cube Processing » »

Author Description

Avatar

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

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • MSAS – Applying OLAP Cubes

    March 3, 2005 - 0 Comment
  • MSAS: Introduction and Working with Cubes

    April 9, 2005 - 0 Comment
  • MSAS : The Data warehousing framework of SQL Server 2000 – Part 1

    March 15, 2005 - 0 Comment
  • MSAS – Troubleshooting Cube Processing

    April 18, 2005 - 0 Comment
  • MSAS : Working with Standard Dimensions

    March 31, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • Microsoft Analysis Services Installation

    March 5, 2005 - 0 Comment
  • MSAS: Introduction and Working with Measures

    April 9, 2005 - 0 Comment
  • MSAS : The Data warehousing framework of SQL Server 2000 – Part 2

    March 18, 2005 - 0 Comment
  • MSAS – Introduction and Managing Partitions

    April 19, 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