Frequently Asked Questions about OLAP and Microsoft Analysis Services 2000

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Carl Dubler and Frank C. Rice
Microsoft Corporation

April 2002

Applies to:
    Microsoft® SQL Server™ 2000 Analysis Services
    Microsoft Data Analyzer

Summary: Find answers to some of the most frequently asked questions concerning OLAP technology, its associated components, and Microsoft SQL Server 2000 Analysis Services. (14 printed pages)

Contents

Introduction
The Microsoft Solution
The OLAP Process
Conclusion
Glossary

Introduction

Businesses today are looking for ways to quickly make sense of the mountains of data that they create each day. Getting at the key pieces of information in this data so as to make timely decisions requires structures and processes different from the traditional Online Transactional Processing (OLTP) used in relational databases. These new processes include Online Analytical Processing (OLAP) and data mining. These new structures include OLAP databases and cubes. Together with Microsoft® SQL Server™ 2000, Analysis Services offers everything needed to build analysis applications, including integrated OLAP and data mining capabilities.

By using open industry standards, the integrated SQL Server-Analysis Services solution works in virtually any business environment and offers integration with a wide variety of third party products and client tools. For example, by using the graphical Analysis Manager in Analysis Services, businesses can create and deploy new analysis applications quickly and easily, with a solution that is scalable to support changing analysis environments and very large data volumes. One problem that frequently hampers the effective use of this data is that much of the data can be in a variety of formats and in a number of different locations. The integrated Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. With DTS tools, you can graphically or programmatically create DTS packages tailored to the specialized business needs of your organization. Microsoft's end-to-end solution eliminates the traditional barriers to data warehousing and puts sophisticated business intelligence capabilities within reach of virtually any business.

This article will answer some of the most frequently asked questions regarding Analysis Services and OLAP. In addition, a glossary containing some of the more common OLAP terms is provided at the end of the article.

The Microsoft Solution

Q. Which Microsoft SQL Server 2000 features can I use for building cubes and performing data analysis?

A. Cubes can be built and managed with Analysis Services. Analysis Services is included with the Enterprise, Standard, and Developer editions of SQL Server 2000. For more information on the capabilities of SQL Server and Analysis Services, see the SQL Server 2000 Web site.

Regarding data analysis, the Microsoft Data Analyzer allows you to perform detailed analysis of multidimensional data; publish and share data with other Microsoft Office products including Microsoft Excel, Microsoft PowerPoint, or to the Web; and query and filter your data to get just the information you need. Data Analyzer is also tightly integrated with Microsoft Analysis Services, which enables improved database analysis capabilities. See the Data Analyzer Web site for more information.

Q. Do I need to use SQL Server in order to build cubes?

A. No. You can build local cubes from Microsoft Excel, for example or by using MSQuery.

Q. Must I use DTS in order to build cubes?

A. No. In a data mart solution, however, you will need to use some sort of ETL utility. DTS provides a set of graphical tools and programmable objects that let you extract, transform, and consolidate data from disparate sources into single or multiple destinations. However, if you already have an ETL utility that you use, you can continue to use it instead.

Q. Where do cubes live—on a server or on the client?

A. Cubes are deployed on a server. It does not have to be a dedicated server, but that may be recommended if you will have a very large cube(s) or many users. However, users can take a subset of the cube and download it to a laptop so they can do ad-hoc analysis without having to be connected to the network.

Q. Can I use a cube in Excel? Can I access cubes over the Web?

A. Yes to both questions. In fact, a user could connect to a cube over the Web and still be in Excel. There are many solutions to delivering cubes and OLAP data over the Web. Among these, Microsoft ActiveX® controls, Active Server Pages (ASP) scripting, and ActiveX Data Objects (ADO) Application Programming Interfaces (APIs) provide a variety of solutions for querying OLAP data over the Web. Additionally, Data Analyzer can access cube data by using the Hypertext Transport Protocol (HTTP).

Q. What kind of licensing do I need for end-users to access cubes?

A. Cubes built with Analysis Services are licensed the same way (and using the same license) as SQL Server. For more information on licensing option, see the SQL Server 2000 Web site or refer to the End User licensing Agreement (EULA) which comes with SQL Server 2000.

Q. How are cubes secured?

A. Analysis Services has a robust security model that allows setting permissions for groups on dimensions all the way down to the cell level. In addition, you can limit the administrators that are permitted to access Analysis Services data through Analysis Manager and perform administrative functions. You can also restrict end users who access data on the Analysis server through client applications as well as specify which end users can access data and the types of operations they can perform. Administrator security is controlled using the Microsoft Windows NT® 4.0 or Microsoft Windows® 2000 group named OLAP Administrators. End-user security is controlled by a combination of:

  • Authentication during connection to the Analysis server.
  • Database, cube, and mining model roles defined in Analysis Manager.

Each role defines a set of users and the access they all share. A role is defined at the Analysis Services database level and then assigned to cubes that the users in the role are permitted to access. For more information, see the article Creating Security Roles.

Analysis Services supports Windows integrated security system. If you want to deliver cubes on the Web, Analysis Services also supports HTTP or secure HTTP (HTTPS) authentication in conjunction with Microsoft Internet Information Services (IIS) to establish connections to an Analysis server. For more information, see the article How to configure Analysis Services for the Web.

Q. Some competitors say that Microsoft cubes don't scale. Is that true?

A. Analysis Services provides the following options to improve scalability:

  • Customized Aggregation Options—By using the Storage Design Wizard, you can optimize the tradeoff between system performance and the disk space allocated to store aggregations. Analysis Services uses a sophisticated algorithm to determine the optimum set of aggregations from which other aggregations can be derived. This will allow you to optimize the efficiency of your queries while maintaining reasonable limits on the size of your databases.
  • Usage-Based Optimization—You can tune the performance of a cube to provide quick response to the queries most often executed by using the Usage-Based Optimization Wizard to design aggregations appropriate to those queries while maintaining reasonable storage requirements.
  • Data Compression and Storage Optimization—In multidimensional OLAP (MOLAP) and hybrid OLAP (HOLAP) storage modes, Analysis Services stores all or some of the cube information in multidimensional structures. In these structures, storage is not used for empty cells, and a data compression algorithm is applied to data that is stored. The can greatly speed up access to your data and reduce the size of your database.
  • Distributed Calculation—Microsoft PivotTable® Service incorporates functionality from the server so that calculations can often be performed on the client instead of the server. Because this distributes the computational load between the server and the client, it increases the capacity of the server, reduces network traffic, and improves performance for the clients.

These and other scalability options are discussed in detail in the SQL Server Books Online, which can be installed with the product.

Q. What is the difference between standard and enterprise editions?

A. The two important differences are: first, the enterprise edition allows users to access the cube over HTTP protocol; and second, the enterprise edition allows you to break a large cube into smaller pieces (called partitions) that may be distributed over multiple servers. This allows for very large performance gains.

The OLAP Process

Q. What are cubes and how will they benefit me?

A. A cube is a specialized database that is optimized to combine, process, and summarize large amounts of data in order to provide answers to questions about that data in the shortest amount of time. This allows users to analyze, compare, and report on data in order to spot business trends, opportunities, and problems. A cube uses pre-aggregated data instead of aggregating the data at the time the user submits a query.

Q. What are the general steps involved in creating an OLAP and building a cube?

A. Typically, the process of building a cube can be broken down into these general steps:

  1. Data is transformed and loaded into a data warehouse or a series of data marts—The operational data of the business is copied from the origin data sources into a data warehouse or data marts. During this process, the data is "cleansed" to remove erroneous data and formatted to be consistent. This process is usually accomplished by some ETL utility such as DTS in Analysis Services. The data warehouse typically consists of one or more fact tables joined by a number of dimension tables in a star schema. The fact tables contain contains the numerical data (that is, measures) and the dimension tables contain categories by which the measures can be separated for analysis such as customer information, product information, or time periods. The data in the dimension tables is sometimes further subdivided into additional tables that are joined to other dimension tables, resulting in a snowflake schema.
  2. Hierarchies and levels can be defined for the dimensions—Hierarchies typically display the same data in different formats such as time data can appear as months or quarters. Levels typically allow the data to be "rolled up" into increasing less detailed information such as in a Region dimension where cities roll-up into states which roll-up into regions which roll-up into counties and so forth. This allows the user to "drill-up" or "drill-down" to see the data in the desired detail. Levels and hierarchies for a star schema are derived from the columns in a dimension table. In a snowflake schema, they are typically derived from the data in related tables.
  3. The cube is created—Once the data has been loaded into a data warehouse or series of data marts, the cube can be built. A cube is essentially a graphical representation of the data defined in the data warehouse. As such, it is also defined as a set of dimensions and measures. In addition, because a cube is used for data analysis and decision support, the data in the cube can be further aggregated to provide a more summarized view of the data than that available from the data warehouse.
  4. The storage mode for the cube is selected—Physical storage options affect the performance, storage requirements, and storage locations of the data used by the cube. The three options available include MOLAP, ROLAP, and HOLAP. For further information on these three different storage mode options, see the question What is the difference between MOLAP, ROLAP, and HOLAP?.
  5. The cube is processed—When you process a cube, the aggregations designed for the cube are calculated and the cube is loaded with the calculated aggregations and data. Processing a cube involves reading the dimension tables to populate the levels with members from the actual data, reading the fact table, calculating specified aggregations, and storing the results in the cube. After a cube has been processed, users can query it.
  6. The cube is now ready to be used by users—Users can view the cube data by using the Cube Browser in the Analysis Manager, by using Microsoft Excel, or by using other specialty applications such as the Microsoft Data Analyzer. Cube Browser allows you to quickly browse multidimensional data in a flattened, two-dimensional grid format. The Data Analyzer provides a complete overview of your data on one screen so that you can quickly find hidden problems, opportunities, and trends.

Of course, depending on the complexities and structure of your data and the types of analysis your users will be doing, other, more complex steps may be necessary to complete the process.

Q. Which is better? A star schema or a snowflake schema?

A. It depends on your situation. In most cases, a star schema will give you better performance and is easier to maintain. Why? Because a star schema usually has fewer tables containing fewer links than a snowflake schema. That means that your cube has fewer tables to navigate in order to populate each dimension. Fewer tables equates to fewer links, which should result in less database maintenance.

A star schema consists of a fact table linked to one or more dimension tables. This linking in a multidimensional database is similar to linked tables in a relational database. However, the biggest difference in the two types of databases is the emphasis on the fact table in a star schema. The fact table contains a row for each transaction that will be analyzed in your cube. So for a Sales cube, the fact table will contain a row for each transaction. Thus the level of detail that you see in your data when you view a cube is determined by the granularity of the data in the fact table. Also in a star schema, the levels in a dimension are usually derived from the columns in the dimension table. For example, a Time dimension table might have a column of data for each quarter, week, and day of a year. These columns would translate to the Quarter, Week, and Day levels in the Time dimension in a cube.

A snowflake schema also has a fact table. However, the dimensions are spread across two or more related tables. Referring back to our example with the Time dimension, a snowflake might contain a table with quarterly data linked to a table of weekly data, and so forth. In this case, the levels in the dimension would be derived from the data in the different time-related tables. One reason why you might want to use a snowflake schema is if the volume of data makes storing it in one table too unwieldy. However, for most cases, you'll want to stay with a star schema.

Q. What is the difference between a data mart and a data warehouse?

A. Data marts are specialized databases designed to handle the reporting needs of a single department or single line-of-business application. A data warehouse is typically several data marts "rolled-up" into one giant database so reporting can be done enterprise-wide instead of on a departmental level. Warehouses are often expensive and time-consuming to build while marts can be built quickly and inexpensively. Typically, an organization will build a mart for their most important department first and later (if ever) build the warehouse.

Q. Do I need to have a data mart in order to build cubes?

A. No. However, Microsoft cube software is optimized for building on data marts or data warehouses and specifically, multidimensional databases with a star schema configuration. Without pulling the data from a multidimensional data mart or data warehouse, it can be more difficult to build cubes. For most solutions, the best long-term solution will be found by using a data mart or data warehouse consisting of an OLAP database configured as a star schema.

Q. A data mart (or star schema) seems redundant and a waste of hard disk space. Why can't I just use my operational data without also storing it in a data mart?

A. The fact is that the best reporting solutions require some amount of redundant data. In addition, OLTP systems have inherent problems that severely limit their effectiveness as for business intelligence:

  • OLTP data can be very inconsistent. For example, customer name fields may be formatted as last name, first name & middle initial in one table, as first name, middle initial & last name in another table, or contained all in one field in another table. Cleansing the data prior to loading it into a data warehouse can remove many of these inconsistencies.
  • OLTP data typically changes frequently. For example, the number of available units of a particular product can change very rapidly in the course of an hour. An analysis of the number of units sold could vary greatly from one analysis to the next. Refreshing the data in a data warehouse can be scheduled so that the data used for analysis is relatively constant.
  • The data might be located in multiple data sources. Data warehouses provide a way to consolidate data from various sources into a single data source.
  • Schemas for OLTP databases are usually optimized for entering groups of records (also known as transactions) and, therefore, tend to contain large numbers of individual records. Summarizing large numbers of records can take a long time. In contrast, data warehouses contain more summary data, which tends to better performance for reporting.
  • Servers hosting OLTP databases are usually busy with transactional processes. Summarizing large groups of records can rapidly tax a server hosting OLTP databases resulting in poor reporting performance or poor transactional processing. Data warehouse are optimized for reporting.

If you want to reduce or eliminate redundancy, then your reporting efforts will have to be based on your source OLTP systems. Not only will that increase complexity and decrease performance, it will reduce the performance for the OLTP systems used to run your business day-to-day. Spending money on additional hard disk systems and some time to create a data mart (or data warehouse) is well worth the benefit that a cube provides.

Q. Can Analysis Services accept other types of schemas in a data mart?

A. Analysis Services will allow variations on the star schema such as snowflake schemas and parent-child dimensions. Normally, these variations should only be used to accommodate unique relationships in your data and not as a first choice for data mart design.

Q. How long does it take, on average, to build a cube?

A. Building a cube is a relatively simple process that could literally take only a couple hours, maybe even minutes. Usually, the hardest part is building the data mart and populating it with data. By using the tools and wizards in the Analysis Manager, building cubes is easy and straightforward.

Q. It seems to me that cubes could be a real challenge to manage on the hard drive. I've heard of "cube explosion" where they get real big in a hurry. How am I going to manage them?

A. When you build a cube, Analysis Services has a utility called the Usage Analysis Wizard that helps you manage hard disk space. Remember that cubes are pre-aggregated data. Usually, it turns out that you don't need to build 100% of all possible aggregates when processing a cube because it is unlikely that your users will need all aggregates. The Usage Analysis Wizard can help you identify the aggregates you need and help you remove the ones you don't which helps you maximize your storage space. Analysis Services also employs other solutions such as data compression to help reduce "cube explosion".

Q. What is the difference between MOLAP, ROLAP, and HOLAP?

A. Remember that a cube is pre-calculated summaries (aggregates) of your data mart data. MOLAP, ROLAP, and HOLAP are different methods of storing these aggregates on disk. Here is a summary of their major features:

  • MOLAP (Multidimensional OLAP)—Stores the aggregates and the base-level data in your data mart into a number of proprietary files. While this requires a bit more hard disk space, it is the fastest type of cube accessibility and it greatly reduces the strain on your data mart.
  • ROLAP (Relational OLAP)—Stores the aggregates in your data mart as tables alongside your base-level data. While this minimizes the hard disk space needed, it is the slowest type of cube access.
  • HOLAP (Hybrid OLAP)—A compromise between MOLAP and ROLAP. The aggregates are stored in a MOLAP file, while the base-level detail is kept in the data mart. This provides excellent performance while browsing aggregates, but is slow when a user "drills down" to base-level detail.

Usually, MOLAP is the best choice for most cubes.

Conclusion

This article answered many of the most frequently asked questions concerning OLAP technology and its associated components. The article also discussed Microsoft SQL Server 2000 Analysis Services.

Glossary

  • ADO MD
    ActiveX Data Object (Multidimensional). A high-level, language-independent set of object-based data access interfaces optimized for multidimensional data applications. Visual Basic and other automation languages use ADO MD as the data access interface to multidimensional data storage. ADO MD is a part of ADO 2.0 and later.

  • Aggregation
    A table or structure that contains precalculated data for a cube.

  • Analysis Server
    The server component of Analysis Services that is specifically designed to create and maintain multidimensional data structures and provide multidimensional data in response to client queries.

  • Axis
    A set of tuples. Each tuple is a vector of members. A set of axes defines the coordinates of a multidimensional data set.

  • Business Intelligence
    The applications and processes of analyzing business data. This is generally synonymous with decision support systems.

  • Calculated Member
    A member of a dimension whose value is calculated at run time using an expression. Calculated member values may be derived from other members' values. A calculated member is any member that is not an input member. For example, a calculated member Profit can be determined by subtracting the value of the member Costs from the value of the member Sales.

  • Cell
    In a cube, the set of properties, including a value, specified by the intersection when one member is selected from each dimension.

  • Cellset
    In ADO MD, an object that contains a collection of cells selected from cubes or other cellsets by a multidimensional query.

  • Changing Dimension
    A dimension that has a flexible member structure. A changing dimension is designed to support frequent changes to structure and data.

  • Child
    A member in the next lower level in a hierarchy that is directly related to the current member. For example, in a Time dimension containing the levels Quarter, Month, and Day, January is a child of Qtr1.

  • Clustering
    A data mining technique that analyzes data to group records together according to their location within the multidimensional attribute space. Clustering is an unsupervised learning technique.

  • Cube
    A set of data that is organized and summarized into a multidimensional structure defined by a set of dimensions and measures.

  • Custom Rollup
    An aggregation calculation that is customized for a dimension level or member and overrides the aggregate functions of a cube's measures.

  • Data Mart
    A subset of the contents of a data warehouse. A data mart tends to contain data focused at the department level, or on a specific business area.

  • Data Member
    A child member generated for a nonleaf member in a parent-child dimension. A data member contains a value directly associated with a nonleaf member that is independent of the summary value calculated from the descendants of the member. For example, a data member can contain a manager's salary so that either individual salaries or summarized salaries can be displayed.

  • Data Pump
    An OLE DB service provider that provides the infrastructure to import, export, and transform data between heterogeneous data stores using DTS.

  • Data Scrubbing
    Part of the process of building a data warehouse out of data coming from multiple OLTP systems. The process must address errors such as incorrect spellings, conflicting spelling conventions between two systems, and conflicting data (such as having two part numbers for the same part).

  • Data Warehouse
    A database specifically structured for query and analysis. A data warehouse typically contains data representing the business history of an organization.

  • Dataset
    In OLE DB for OLAP, the set of multidimensional data that is the result of executing a MDX SELECT statement.

  • Decision Support
    Systems designed to support the complex analytic analysis required to discover business trends. The information retrieved from these systems allows manager to make business decisions based on timely and accurate analysis of business trends. See also Business Intelligence.

  • Dimension
    A structural attribute of a cube, which is an organized hierarchy of categories (levels) that describe data in the fact table. These categories typically describe a similar set of members upon which the user wants to base an analysis. For example, a geography dimension might include levels for Country, Region, State or Province, and City.

  • Dimension Table
    A table in a data warehouse whose entries describe data in a fact table. Dimension tables contain the data from which dimensions are created.

  • Drill Up/Drill Down
    A technique for navigating through levels of data ranging from the most summarized (up) to the most detailed (down). For example, when viewing the details of sales data by year, a user can drill down to display sales data by quarter, and further to display data by month.

  • Drill Through
    To retrieve the detailed data from which the data in a cube cell was summarized.

  • DTS
    Data Transformation Service. A component of Microsoft SQL Server 2000 and SQL Server 7.0 which performs ETL.

  • DTS Package
    An organized collection of connections, DTS tasks, DTS transformations, and workflow constraints defined by the DTS object model and assembled either with a DTS tool or programmatically.

  • ETL
    Extract, Transform, and Load. Tool and processes to extract, transform, and load operational data into a relational store and manage these processes.

  • Fact
    A row in a fact table in a data warehouse. A fact contains values that define a data event such as a sales transaction.

  • Fact Table
    A central table in a data warehouse schema that contains numerical measures and keys relating facts to dimension tables. Fact tables contain data that describes specific events within a business, such as bank transactions or product sales.

  • Flattened Rowset
    A multidimensional data set presented as a two-dimensional rowset in which unique combinations of elements of multiple dimensions are combined on an axis.

  • Hierarchy
    A logical tree structure that organizes the members of a dimension such that each member has one parent member and zero or more child members.

  • HOLAP
    Hybrid OLAP. A storage mode that uses a combination of multidimensional data structures and relational database tables to store multidimensional data. Analysis Services stores aggregations for a HOLAP partition in a multidimensional structure and stores facts in a relational database.

  • Leaf
    In a tree structure, an element that has no subordinate elements. For example, in Analysis Services, a leaf is a dimension member that has no descendants.

  • Level
    The name of a set of members in a dimension hierarchy such that all members of the set are at the same distance from the root of the hierarchy. For example, a time hierarchy may contain the levels Year, Month, and Day.

  • Linked Cube
    A cube based on a cube defined on another Analysis server.

  • Local Cube
    A cube created and stored with the extension .cub on a local computer using PivotTable Service.

  • MDX
    Multidimensional Expressions. A syntax used for defining multidimensional objects and querying and manipulating multidimensional data.

  • Measure
    In a cube, a set of values that are based on a column in the cube's fact table and are usually numeric. Measures are the central values that are aggregated and analyzed.

  • Member
    An item in a dimension representing one or more occurrences of data. A member can be either unique or nonunique. For example, 1997 and 1998 represent unique members in the year level of a time dimension, whereas January represents nonunique members in the month level because there can be more than one January in the time dimension if it contains data for more than one year.

  • MOLAP
    Multidimensional OLAP. A storage mode that uses a proprietary multidimensional structure to store a partition's facts and aggregations or a dimension. The data of a partition is completely contained within the multidimensional structure.

  • Multidimensional database
    A database paradigm that treats data not as relational tables and columns, but as information cubes that contain dimension and summary data in cells. Each cell is addressed by a set of coordinates that specify a position in the structure's dimensions. For example, the cell at coordinates {SALES, 1997, WASHINGTON, SOFTWARE} would contain the summary of software sales in Washington in 1997.

  • Named Set
    A set of dimension members or a set of expressions that is created for reuse, for example, in MDX queries.

  • Nonleaf
    In a tree structure, an element that has one or more subordinate elements. For example, in Analysis Services, a dimension member that has one or more descendants. In SQL Server indexes, an intermediate index node that points to other intermediate nodes or leaf nodes.

  • OLAP
    Online Analytical Processing. A technology that uses multidimensional structures to provide rapid access to data for analysis. The source data for OLAP is commonly stored in data warehouses in a relational database.

  • OLTP
    Online Transactional Processing. A data processing system designed to record all of the business transactions of an organization as they occur. An OLTP system is characterized by many concurrent users actively adding and modifying data.

  • Ordered Set
    A set of members returned in a specific order. The ORDER function in a Multidimensional Expressions (MDX) query returns an ordered set.

  • Partition
    In Analysis Services, one of the storage containers for data and aggregations of a cube. Every cube contains one or more partitions. For a cube with multiple partitions, each partition can be stored separately in a different physical location. Each partition can be based on a different data source. Partitions are not visible to users; the cube appears to be a single object.

  • Private Dimension
    A dimension created for and used by a specific cube. Unlike shared dimensions, private dimensions are available only to the cube in which they are created.

  • Process
    In a cube, the series of operations that rebuilds the cube's structure, loads data into a multidimensional structure, calculates summaries, and saves the precalculated aggregations. As a verb, to populate a cube with data and aggregations. It is one of three processing options for a cube.

    In a dimension, the operation that loads data from a dimension table in a data warehouse into the levels defined for a dimension and rebuilds the structure of the dimension. It is one of two processing options for a dimension.

    In a data mining model, the operation that retrieves training data from a relational or OLAP data source into the structure defined for a data mining model, statistically analyzes it with a data mining algorithm, and saves the statistical data as data mining content. As a verb, to populate a data mining model with data mining content.

  • Ragged Hierarchy
    A dimension hierarchy in which one or more levels do not contain members in one or more branches of the hierarchy. For example, the state or province level in a geography hierarchy contains no members for countries or regions that do not have states or provinces.

  • Relational database
    A collection of information organized in tables. Each table models a class of objects of interest to the organization (for example, Customers, Parts, Suppliers). Each column in a table models an attribute of the object (for example, LastName, Price, Color). Each row in a table represents one entity in the class of objects modeled by the table (for example, the customer name John Smith or the part number 1346). Queries can use data from one table to find related data in other tables.

  • Regular Cube
    A cube that is based on tables and has its own aggregations.

  • Regular Dimension
    A dimension that is neither a parent-child dimension nor a virtual dimension.

  • Repository
    The storage container for the meta data used by Analysis Services. Meta data is stored in tables in a relational database and is used to define the parameters and properties of Analysis server objects.

  • ROLAP
    Relational OLAP. A storage mode that uses tables in a relational database to store multidimensional structures.

  • Role
    In Analysis Services, a role uses Windows security accounts to limit scope of access and permissions when users access databases, cubes, dimensions, and data mining models.

  • Rowset
    The OLE DB object used to contain a result set. It also exhibits cursor behavior depending on the rowset properties set by an application.

  • Schema
    In Analysis Services, a schema is a description of multidimensional objects such as cubes and dimensions.

  • SELECT
    In Analysis Services, the MDX statement used to query cubes and return recordsets of multidimensional data.

  • Shared Dimension
    A dimension created within a database that can be used by any cube in the database.

  • Slice
    A subset of the data in a cube, specified by limiting one or more dimensions by members of the dimension. For example, facts for a particular year constitute a slice of multiyear data.

  • Slicer
    A dimension used to filter multidimensional data. This dimension appears in the WHERE clause of a MDX statement.

  • Snowflake Schema
    An extension of a star schema such that one or more dimensions are defined by multiple tables. In a snowflake schema, only primary dimension tables are joined to the fact table. Additional dimension tables are joined to primary dimension tables.

  • Source Cube
    The cube on which a linked cube is based.

  • Source Database
    In data warehousing, the database from which data is extracted for use in the data warehouse.

  • Sparsity
    The relative percentage of a multidimensional structure's cells that do not contain data. Analysis Services stores only cells that contain data. A sparse cube requires less storage than a dense cube of identical structure design.

  • Star Schema
    A relational database structure in which data is maintained in a single fact table at the center of the schema with additional dimension data stored in dimension tables. Each dimension table is directly related to and usually joined to the fact table by a key column. Star schemas are used in data warehouses.

  • Subscribing Server
    An Analysis server that stores a linked cube.

  • Time Dimension
    A dimension that breaks time down into levels such as Year, Quarter, Month, and Day. In Analysis Services, a special type of dimension created from a date/time column.

  • Tuples
    An ordered collection of members from different dimensions. For example, (Boston, [1995]) is a tuple formed by members of two dimensions: Geography and Time. A single member is a degenerated case of a tuple and can be used as an expression without the parentheses.

  • Virtual Cube
    A logical cube based on one or more regular cubes or linked cubes.

  • Virtual Dimension
    A logical dimension that is based on the values of properties of members of a physical dimension. For example, a virtual dimension that contains the colors red, green, and blue can be based on the Color member property of a product dimension.

  • Where Clause
    The clause element containing the filter dimension in a MDX SELECT statement.