Cubes are key elements in OLAP (online analytic processing), a technology that provides rapid access to data in a data warehouse. Cubes provide a mechanism to search for data with speed and consistent response time regardless of the amount of data in the cube or the complexity of the search procedure.
Cubes are subsets of data in a data warehouse, organized and summarized within a multidimensional structure. The data is summarized according to selected business factors, providing the mechanism for a fast and uniform response time of complex queries.
Defining the cube is the first of three steps in creating a cube. The other steps are, specifying the summarization strategy by designing the aggregations (precomputed data elements), and loading the cube to process it. To define a cube, select a target table and select the measures (numeric columns of interest to cube users) within this table. Then select the dimensions, each made up of one or more columns from another table. Dimensions provide the categorical description by which measures are separated for analysis by cube users.
Dimensions
Dimensions are descriptive categories by which numerical data (Measurements) in a cube are separated for analysis. For example, if a measurement of a cube is the production count, and the dimensions are Time, location of the factory and the product, the users of the cube will be able to separate the production count, within several time categories, location of the factory and products.
A dimension can be created to be used in a single cube or in multiple cubes. A dimension created for an individual cube is called a private dimension. On the contrary, if it can be used by multiple cubes, it is called a shared dimension. These can be used within any cube, in the database, thus optimizing time and avoiding duplicating private dimensions.
Shared dimensions also enable the standardization of business metrics between cubes. For example, standardizing the shared dimensions for time and geographic location, ensures that the analyzed data, from different cubes, is organized similarly.
Measurements
Measures are numerical data of primary interest to users of the cube. Some common measures are Sales in units, sales in pesos, cost of sales, expenses, production count, budget, etc. These are used by the OLAP service aggregation procedure and stored for its rapid response to user requests.
You can create a calculated measure and calculate dimension members, combining multidimensional expressions (MDX), mathematical formulas, and user-defined functions (UDFs). This facility enables you to define new measures and dimension members, based on a simple formula syntax. Additional libraries of UDFs can be registered, to be used in defining calculated members.
Member Properties
You can define properties for dimension members and use data for these properties within a cube. For example, if the members of the product dimension are its part number, it is the same to do various properties associated with this part number such as size, color, etc. You can specify such properties as a member property and use it in analytical searches.
Virtual Cubes
You can join cubes, within virtual cubes, much like the process of joining tables with views in relational databases. A virtual cube provides access to the data in the combined cubes, if the need to build a new cube, while allowing the best design to be maintained in each individual cube.
A cube can be updated, processing only the data that has been added, instead of the entire cube, incremental update can be used to update a cube while it is being used.
Aggregations
This is how the process of pre-calculating sums of data is called, to help reduce response times in information search processes.
Security
Using the security facilities managed by Microsoft SQL Server OLAP services, you can control who accesses the data and the types of operations that users can perform on the data. OLAP services supports the integrated security system offered by the Windows NT operating system and allows you to assign access permissions, to the database and the cube, including the virtual cubes.
Security is managed via access control rights that are managed by Roles, these determine the type of access to data. Roles define categories of users with the same access controls.
Storage Modes
For cubes, there are three ways to store your information:
1.- MOLAP - Multidimensional OLAP.
2.- ROLAP - Relational OLAP.
3.- HOLAP - Hybrid OLAP.
MOLAP
The cube's source data is stored together with its aggregations (summarizations) in a high-performance multidimensional structure. MOLAP storage provides excellent performance and data compression. As they say, everything goes in the bucket.
It has the best response time, depending only on the percentage and layout of the cube's aggregations. In general, this method is very appropriate for frequently used buckets due to its quick response.
ROLAP
All the information in the cube, its data, its aggregation, sums, etc., are stored in a relational database. ROLAP does not store a copy of the database, it accesses the original tables when it needs to answer questions, it is generally much slower than the other two storage strategies.
Typically ROLAP is used for large data sets that are not frequently searched, such as historical data from the most recent years.
HOLAP
HOLAP combines attributes of MOLAP and ROLAP, the data aggregation is stored in a multidimensional structure used by MOLAP, and the source database, in a relational database. For search procedures that access summary data, HOLAP is equivalent to MOLAP, on the contrary, if these processes will access source data such as drill down, they must search the data in the relational database and this is not as fast compared to whether the data were stored in a MOLAP structure.
The cubes stored in as HOLAP, are smaller than MOLAP and respond faster than ROLAP.
HOLAP is generally used for cubes that require quick response, for summarizations based on a large amount of data.
