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 : Office 2000 OLAP Components

By Exforsys | on March 25, 2005 |
MSAS
PivotTable component in Office 2000 is the PivotTable report feature. It allows the user select and cross tabulate numerical values in ways that are similar to the processes of a cube. The earlier versions of this report in Excel could only extract values from relational data sources. The version under reference can present data directly form an OLAP cube.

There is another PivotTable in Office 2000 which is a PivotTable list feature. This is a part of the Office Web Components. This component has the same functionalities as the PivotTable report, but includes capabilities that aren’t available in Excel component. It can function like a ActiveX control and be included in a web page or a Visual Basic application or Microsoft Word document.

PivotTable Report

The PivotTable Repot draws its data in two ways. In the first method, it imports values from an Excel List or a database table and stores the values in memory. When the user manipulates the PivotTable report, the necessary data is retrieved form the memory cache. In the second method, it uses an OLAP cube to derive its data. It communicates with the PivotTable Service of the Analysis Service and retrieves data from the cubes therein.

Let us examine how the PivotTable works

Open Microsoft Excel. Click on ‘PivotTable and PivotTableChart Report’ from the data menu.

This opens the PivotTable and PivotChart Wizard.

In the above screen the user has to specify the “External Data source” and “PivotTable” and click Next. This opens the Step 2 dialog box as below:
Click the “Get Data” button to specify the data source for retrieving data. Excel automates the Microsoft Query and pops up the Choose Data dialog box. Microsoft Query needs to be installed as part of Office Setup before it can be used with this option.

Select the OLAP Cubes tab in the above dialog box to select a cube from the list of cubes defined earlier. Select the cube that is to be used and click OK. The cube selected here is saved as a cube file (*.Cub) on the Client hard disk. However, we will define a new cube here and then use it to retrieve data. Click on <New Data Source>.
The Create New Data Source dialog box opens prompting the user select the source for the data and the OLE DB Provider for OLAP Services. Specify a name for your new cube definition. Select OLE DB Provider for OLAP Services 8.0 or OLAP Services.

For the purpose of this tutorial we will select Microsoft OLE DB Provider for OLAP Services. Click the Connect button. The Multi-dimensional connection dialog enables the user connect to either OLAP services server or the existing cube file on the hard disk.
Let us enter a name of the server and click next. The dialog box illustrated to the right appears prompting the user select the cube on the server. Select Sample and click Finish. The user is back to the Create New Data Source Dialog box and he is prompted to select the cube as below:
Click Next button. The Choose Data Source dialog box appears once more showing the user the newly added cube. Select the cube and Click OK to get back to the PivotTable and PivotChart Wizard.
Click Finish on the above dialog box and Excel embeds a new empty PivotTable object into the worksheet. The Pivot table toolbar is also displayed. Populating the table is achieved using the toolbar. There are four placeholders that are used to accomplish this task.
  1. PAGE FIELDS act like slicers or filters for data. Only members can be used as page fields when a level or dimension is added to the drop area. Members from the measure dimension, for instance, cannot be added to the page fields.
  2. COLUMN FIELDS AND ROW FIELDS determine the data displayed on the columns and row axes respectively. These axes may contain any type of data from a cube structure except measures.
  3. DATA ITEMS determine the numerical data portion of the spreadsheet. Only members from the measure dimension can be used as data items.

The toolbar gives the user a list of items from the cube to drop into the PivotTable. Inside the List item tree the user selects the data field required, specifies the row area option and clicks Add To button to add the field to the table. Similarly Column areas are also to be specified and the measures to be used are defined with reference to the items in the row group

Manipulating the PivotTable

The PivotTable is used for non technical people and hence has a simple drag and drop interface. It allows multiple dimensions on row and column axes and multiple member as page fields. Drilldown operations can be performed using the drop down list boxes and by double clicking the members listed in either of the axes. Sub totals and grand total are calculated automatically and displayed. The Format Report and Table Options in the PivotTable Toolbar can be used for customizing the look and feel of the PivotTable. Advanced functionalities of the PivotTable can be accessed by right clicking on the column or row heading of a PivotTable and select Field Settings from the pop up menu.

The PivotChart draws its values from the PivotTable. It provides all the functionalities common in charting programs. Users can select from a variety of chart types, sort data, perform TopCount operations and use formulas to manipulate the cube data.

In the next tutorials we drill down into greater details on how to build, manipulate and process cubes using the various services provided in the Analysis services.









« « MSAS : Client Architecture
ASP.NET Using the DataList and Repeater, Datagrid Controls » »

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 : Introducing Analysis Manager Wizards

    March 7, 2005 - 0 Comment
  • MSAS: Analysis Server Cube Storage

    April 12, 2005 - 0 Comment
  • MSAS : The Analysis Manager and Analysis Server

    March 18, 2005 - 0 Comment
  • MSAS – Merging Partitions

    April 19, 2005 - 0 Comment
  • MSAS: Working with Levels and Hierarchies

    April 3, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS : Preparing to Create the Cube

    March 7, 2005 - 0 Comment
  • MSAS: The Storage Design Wizard

    April 12, 2005 - 0 Comment
  • MSAS : The Wizards

    March 23, 2005 - 0 Comment
  • MSAS – Implementing Calculations Using MDX Part 1

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