What Is the OLAP DML?
The OLAP DML is a data manipulation language. You can use DML commands and functions to perform complex analysis of data. You can also write stored procedures that contain DML commands and functions.
Extensive Analytic Capabilities
The OLAP DML enables application developers to extend the analytical capabilities of querying languages such as SQL and the OLAP API. These are some situations in which you might use the OLAP DML:
- When you need to calculate data that cannot be calculated as part of your data warehouse extraction, transformation, and load (ETL) process or in SQL. Examples include forecasts, solving a model, some types of consolidations (aggregations), and allocations.
- When your application needs to perform various calculations, but you do not want to immediately commit the results in SQL tables. For example, you might have a forecasting application where you want to allow users to save personal forecasts and reuse them during a later session, but you do not want users to commit the forecast to the SQL tables. Instead, you can just commit the data to the analytic workspace without committing it to SQL tables.
- When you want to manipulate data that is stored in an analytic workspace. An analytic workspace can be an alternative to materialized views for storing aggregate data. It may also be the preferred storage location for data that is frequently used in business analyses such as models and forecasts.
Features of the Multidimensional Model
There are inherent features of the multidimensional model that make it an appropriate environment for business intelligence. The multidimensional model:
- Enforces referential integrity. Each dimension member is unique and cannot be NA. If a measure has three dimensions, then each data value of that measure must be qualified by a member of each dimension.
- Promotes consistency. Dimensions are maintained as separate workspace objects and are shared by measures.
- Preserves the order of data. Each dimension has a default status list, which contains all of its members in the order they are stored. The default status list is always the same unless it is purposefully altered by adding, deleting, or moving members. Within a session, the user can change the selection and order of the status list; this is called the current status list. The current status list remains the same until the user purposefully alters it by adding, removing, or changing the order of its members.
Because the order of dimension members is consistent and known, the selection of members can be relative. For example, the function call
lag(sales, 12, month)
compares the sales values of all months in the current status list against sales from a year ago (that is, 12 time periods earlier in the default status list for the month dimension).
- Presents data as fully solved. Applications do not need to define calculations. Because of the combination of power and ease-of-use of the OLAP DML, the analytic workspace can be prepared so that the data is presented as fully solved to the application.
- Manages calculated members and measures transparently. Users can define their own dimension members (often called custom aggregates), which function identically to the other dimension members and can be used transparently in any calculation. Similarly, users can define their own measures and assign values to them using any of the methods available in the OLAP DML. Throughout the session, these additions behave identically to the dimension members and objects originally provided in the workspace. Users can save their changes from one session to the next with a single DML command.
