MSAS: Introduction and Working with Cubes

The main object in Online Analytical processing is the cube. A cube is defined as a subset of the data in the data warehouse, organized and summarized into multidimensional structure that consists of dimensions and measures.

Introduction to Cubes

The purpose of creating cubes is to provide an easy to use mechanism for data access and quick and uniform response time for queries. User interface controls are inbuilt into Analysis Services to enable end users to connect to the server for queries and manipulate the cube. Aggregations built into the cube at the time of creation of the cube, enable pre-calculated summary data to be returned to end users uniformly and quickly. The results of a query are then, retrieved from these aggregations, the cube’s source data, a copy of the data on the Analysis Server or on the Client cache or a combination of any of these.

Schemas are a set of joined tables in a data warehouse from which the cube draws its source data. The central table of the schema is the fact table, the source of the cube’s measures. The other tables joined to the fact table are the dimension tables—which are the source of the cube’s dimensions. The tables in a schema can be arranged and linked in the Snowflake schema or the Star schema.

Measures define the cube and the dimensions that are contained in the cube. A measure is a unit of the data that is being analyzed. For instance the sales cube in the FoodMart 2000 database, contain the measure Item_Sale_Price and Item_cost. These measures are used to evaluate data in the dimensions Store_Location, Product_Line and Fiscal_Year.

Cubes contain a hierarchy of Levels. A level is an element of a dimension hierarchy which describe data from the most summarized to the most detailed units the dimension. Levels are arranged in hierarchies which define the relative positions of members in a level to each other. For instance Store_Location dimension includes the level hierarchies: Continent, Country, Region, State_Province, City and Store_number. Each level in a dimension is of a finer granularity than its parent. For instance the level fiscal year contains Year, month, quarter, week and day which are members of the level.

Dimension levels are powerful tools as they help users drill down to granular levels or drillup to summarized levels of data in a cube.

The values within a cube represent the measures. These values exist for all cells in the cube. Packages: 190 and Last: Feb 20-21 may be measures contained in a cell. The Package may refer to the number of packages exported during the month February and on the days 20^th and 21^st. The cube may incorporate a route dimension which tells the end user the route by which the package was exported and the Max function may aggregate the packages and so on.

End users can determine the measures’ values for each and every member of the dimension because measure values are aggregated by the members. In addition measures can aggregate for all combinations of members from different dimensions. As a result it is possible to evaluate measures by members in multiple dimensions simultaneously.

A cube can contain up to 128 dimensions with large number of members and up to 1024 measures. Usually cubes are made with a very modest number of dimensions and members as against the scope and endless possibilities of the cube.

Cubes can be of various kinds. Regular cubes are cubes based on tables in the databases specified in the data source. They must contain at least one partition. The aggregations of data are stored in the Data folder of the Analysis server in which it is defined or another Analysis server depending on the storage modes and types of cubes partitions defined. Linked Cubes are cubes that are based on another regular cube that is defined and stored on another Analysis Server. Though these cubes appear and function like regular cubes, they can be spread over a number of Analysis servers and are linked together. The cube on which the linked cube is based is called the source cube and the server that stores the linked cube is called the publishing server. The linked cube is stored in the subscribing server. Distributed Partitioned Cubes are regular cubes that employ partitions on multiple Analysis servers. These partitions are stored in multiple Analysis servers other than the Analysis server which stores the meta data for the partition. Virtual cubes are a combination of several cubes into one logical cube. The measures and dimensions are selected from the consolidated set of measures and dimensions in the underlying component cubes. The virtual cube appears as a single cube to end users. Local cubes are cubes that are stored in the local system or the server and end users can browse the cube without connecting to the server if it is on the local machine. These cubes are based on tables and do not contain aggregations or partitions. Real time cubes are regular cubes that employ relational OLAP(ROLAP) partitions or dimensions that support real-time OLAP feature of Microsoft Server 2000 Analysis Services. Real-Time cubes do not require the cube to be processed every time the data changes and cube updates happen even as data changes in the table. Write Enabled Cubes are cubes that enable modification of the cubes data. These cubes enable users to explore scenario changes by changing cell values and analyzing the effects of the changes on the cube data.

Cubes are subordinate to the database in the object hierarchy and the database is the container for related cubes and the objects they share. Data sources, measures, dimensions, Partitions, Cube roles and commands are subordinate to cubes.

Creation of a cube involves three steps: Definition, Aggregation design and Processing. The cube is designed based on the analytical requirements of end users. A fact table is selected and measures within fact tables are identified. Dimensions are then created using one or more columns from another table. We saw how to build cubes using the Cube Wizard in “Introducing Analysis Manager Wizards”. Once the cube is designed the aggregations are to be defined using the Storage Design Wizard. Designing the aggregations specifies the summarization strategy. Finally the cube is processed with the full process option which creates the aggregations.

Once the cube is created, the Cube Editor is used to maintain it. Reprocess of a cube may be required if the source data changes. Cube security issues will have to be addressed using the security options available in Analysis services. We will be dealing with all these issues in greater detail in the sections that follow.