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 : Client Architecture

By Exforsys | on March 25, 2005 |
MSAS

The PivotTable Service:The interfaces used by client applications to access OLAP data and data mining data on the server are provided by the PivotTable Service. The PivotTable service is a set of tools that enable the transfer of OLAP cubes to client applications from the OLAP server. Two programming interfaces for querying data are available to the developer from the PivotTable Services:–The OLE DB for OLAP and the ActiveX Data objects Multidimensional (ADO MD). Tools such as those available in VisualStudio.Net are harnessed to create applications that query multi dimensional data sources. The figure below illustrates the relationship scenario.

Using the PivotTable Service
The PivotTable Service architecture is designed to reduce the load on the OLAP server. It provides flexibility for the developer to query the OLAP data source without directly accessing the database every time.
The PivotTable Service has the capability of caching slices of OLAP cubes or whole cubes in the primary storage(RAM or memory) on the Client machine. The PivotTable service can also save the entire OLAP cubes on a secondary storage device on a client machine. The cube is saved as a cube(*.cub) file. In this case data will be available even when the system is disconnected from the network. This is extremely useful in a mobile computing environment. The cube saved on a client machine is known as Offline cube. The service also works on Microsoft Internet Information Server for importing data from an Internet source.
The disadvantage of the PivotTable service is that it is very expensive. It needs constant connection to the network server that provides the OLAP data. The Service also needs to be installed on every client machine wishful of using the service. It needs a minimum of 500KB memory and 5 MB disk space on each machine. Additionally each client machine must have Microsoft Data Access Components (MDAC) 2.6 or later. However, the growing hard disk space availability and memory availability will soon make this requirement seem minimal.
Using OLE DB for OLAP
This OLE DB for OLAP Provider interface helps the user harness the full power of the OLE DB standard API. This is part of the PivotTable service. This interface can be used for accessing multidimensional data. The OLE DB for OLAP provides the developer with an extended OLE DB API for accessing OLAP data sources, data mining and other related tasks. It enables the developer create multidimensional queries that return cubic data
In a COM environment, the core OLE DB standards enable the client application connect to an OLAP provider. The three main interfaces of the OLE DB for OLAP are: “IMDDataset, IMDRangeRowset and IMDFind. The design of the application is engineered to present an elegant structure that can manipulate multidimensional data more efficiently.
Using ActiveX Data Objects Multidimensional
Multidimensional Expressions(MDX) is the standard query language for querying multidimensional data. It is not unlike the T-SQL but is used for querying from multidimensional cubes and not from relational databases. The syntax can be used to query or alter OLAP structures.
The ActiveX Data Objects Multi-dimensional(ADO MD) is an extended ADO API on top of OLE DB for OLAP. The ADO MD object provides two views of a multi dimensional database: The structural view and the Dimensional View.
The Database Structural View
This view provides the user with a structural hierarchy of objects and collections of objects that represent the entire structure of the database. The following figure illustrates the structural view of the database.

The catalog object defines the identity of the database and the server to which the user is connected. Its functionality is to allow the user to connect to OLAP services.

The cubedef object defines the individual cube while the CubeDefs object defines all the cubes in the database. Dimension refers to any dimensions of the Cubedef object.

The Hierarchy defines each hierarchy of the dimension. The list of hierarchies can be found in the hierarchies collection.

The level object gives information about the level of the hierarchy. The list of levels is found in the Levels collection.

The member object details the member property while the list of members is found in the Members object.

The PivotTable View or the Dimensional View

The structural view cannot give the developer access to the data. With the PivotTable View the developer can connect to the OLAP data source and get at the data. This is illustrated in Part B of the figure above. The summary of the objects in this model are:

The CellSet allows the developer to express a query and provide a connection to one cube. Its functionality is analogous to the ADO’s connection object. A Cell object refers to a single cell in a cube. This is a multidimensional structure generated by the query. It can be used to review the data returned by the MDX query that generated the CellSet object. The Axis object is a collection of one or more members of a dimension in a cube and represents the axis of the cube. All available Axes for a cube are represented by a CellSet object and are stored inside the object’s collection property. The Position object refers to one position or coordinate along the axis represented by the Axis object. An Axis object can have several Position objects stored in the Positions collection object.

The PivotTable Service which is included as part of the Analysis Services, is also installed in Office 2000. The service functions as client liaison to the Analysis server. It can also be used to create and communicate with local cubes that don’t require Analysis server at all.

The local cubes created with PivotTable Service are different from cubes created using the Analysis services

  1. Aggregations cannot be created on the local cube. 
  2. Only MOLAP or ROLAP cubes can be created. While a .cub file is created for a MOLAP cube, the dimension members alone are stored for the ROLAP cube and the data remains at its source. 
  3. Each cube resides as a separate entity in the local machine and does not have a database structure. 
  4. Shared dimensions cannot be used 
  5. Cubes cannot have partitions 
  6. Virtual cubes cannot be created. 
  7. SQL query cannot be used on local cubes only MDX or ADO MD can be used to access local cubes. 
  8. User rights cannot be set for local cubes. 
  9. Explicit multiple hierarchies can be declared within one dimension in local cubes.

Eight MDX statements are used to create a local cube

  • CREATE CUBE defines the structure of the local cube.
  • INSERT INTO inserts data into a local cube from a relational data source or from an Analysis Services cube. 
  • CREATE MEMBER and DROP SET is used for creating a calculated member or dropping it. 
  • CREATE SET and DROP SET is used to create or drop a user defined set. 
  • REFRESH CUBE is used to rebuild the cube. 
  • CREATE CACHE populates the local cache with a portion of the cube data.

The Web Client

The internet and corporate intranets provide the network for the OLAP applications. The OLAP client connects to the server, database and cube and displays the cube metadata to the user. It uses HTML form components and variables to allow the user specify the parameters of an MDX query. The user does not have to directly create or edit an MDX query.

The client does not directly interface with the OLAP data provider. All interaction is managed through the web server. This architecture does not require an interface with an ADO connection.


« « MSAS : Cube Storage options
MSAS : Office 2000 OLAP Components » »

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
  • Data Warehouse database and OLTP database

    February 25, 2005 - 0 Comment
  • MSAS: Understanding Hierarchies

    April 3, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS : Building the Cube Part #1

    March 9, 2005 - 0 Comment
  • MSAS: Usage-Based Optimization

    April 13, 2005 - 0 Comment
  • MSAS : Meta data Repository

    March 23, 2005 - 0 Comment
  • MSAS – Introducing Solve Order

    April 21, 2005 - 0 Comment
  • Microsoft Analysis Services Training

    February 25, 2005 - 0 Comment
  • MSAS: Creating a Time Dimension

    April 7, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 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