Microsoft® SQL Server™ OLAP Services supports various data and storage models to help you create and maintain an OLAP system that meets your organization’s needs.
OLAP Services offers three storage modes for your system:
The underlying data for a cube is stored along with aggregation data in a high-performance multidimensional structure. MOLAP storage provides excellent performance and data compression.
The underlying data for a cube is stored along with the aggregation data in a relational database. ROLAP storage enables you to take advantage of your investment in relational technology and enterprise data management tools.
The underlying data for a cube is stored in a relational database and the aggregation data is stored in a high-performance multidimensional structure. HOLAP storage offers the benefits of MOLAP for aggregations without necessitating duplication of the underlying detail data.
Virtual cubes and partitions are other forms of hybrid OLAP that enable you to tailor cube storage alternatives to meet your needs.
You can partition a cube into separate physical sections. You can store each partition in a different mode, in a different physical location, and with a level of aggregations appropriate to the data in the partition. The result is that you can fine tune the performance and data management characteristics of your system.
Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.
You can combine a cube’s multiple partitions back into a single physical partition. For example, you can use partition merging to consolidate portions of cube data such as data for a just completed quarter into a single partition for the year.
Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.
You can enable a cube for write access by multiple simultaneous users. User-initiated changes to the cube data are logged to a special, physically separated partition table associated with the cube and applied automatically as cube data is viewed. To the user it appears as if the data in the cube has changed. The changes can be discarded or made read-only at the discretion of the database administrator (DBA).
You can join cubes into virtual cubes, much like tables can be joined with views in a relational database. A virtual cube provides access to data in the combined cubes without necessitating the construction of a new cube, while it allows you to maintain the best design for each individual cube.
You can create calculated measures and calculated dimension members by combining multidimensional expressions (MDX), mathematical formulas, and user-defined functions (UDFs). This facility enables you to define new measures and dimension members based upon a rich yet easy-to-use expression syntax. You can register additional libraries of UDFs to use in calculated member definitions.
You can define properties for dimension members and use data for these properties within a cube. For example, if the members of a Product dimension are SKUs, there are likely to be several properties associated with SKUs such as size, color, fabric, and so forth. You can specify such properties as member properties and use them in analytical queries.
Virtual dimensions can be created from member properties associated with shared dimensions. A virtual dimension can be used to evaluate the properties of a dimension’s members against the members themselves. For example, measures can be evaluated for SKUs against size, color, fabric, and so forth. Virtual dimensions and member properties are evaluated as necessary for queries and require no physical cube storage.