This section describes functional behaviors to consider when you are using DB2 OLAP Server to create an OLAP application and database.
With DB2 OLAP Server:
When you use DB2 OLAP Server to create an OLAP database, it creates a relational cube in your relational database. The relational cube includes a fact table that contains the actual data values for the database. The members of the dimension you identify as the anchor dimension help define the structure of the fact table.
The fact table that DB2 OLAP Server creates has one column for each member of the anchor dimension that you specify, and one column for each additional dimension in your multidimensional database. The total number of members in the anchor dimension plus the total number of additional dimensions in your database, minus one, cannot exceed the maximum number of columns allowed in a table by your relational database. That number does not include shared or virtual members.
When you use DB2 OLAP Server, compression, data caching, and indexing are handled by your relational database.
Just as some information supplied by the Application Manager applies only when you are using the multidimensional storage manager, there is some information that applies only when you are using the relational storage manager.
When you design your multidimensional database, follow the steps in "Designing the Multidimensional Database" to optimize your design for best performance.
An anchor dimension is a dimension that you identify for DB2 OLAP Server to use to help define the structure of the fact table it creates in the relational cube for a multidimensional database.
The relational cube contains a set of data and metadata that together define the multidimensional database that you create using the Application Manager or ESSCMD commands.
The fact table holds the data values for a multidimensional database. It is the main table in the relational cube and contains the following columns:
Figure 3 shows the contents of a sample fact table.
|
The PROFIT, SALES, COGS, and INVENTORY columns are members in the Accounts dimension that was specified as the anchor dimension. The PID, TID, and MID columns represent non-anchor dimensions.
Each column that represents a member in the anchor dimension contains data values, and each column that represents a non-anchor dimension contains ID numbers for members in that dimension.
You can choose an anchor dimension or let DB2 OLAP Server choose one for you.
Most of the multidimensional databases that you create using DB2 OLAP Server include an Accounts dimension. Choosing the Accounts dimension results in the most easy-to-read and understand SQL queries. Because the Accounts dimension contains all the measures of your business, such as sales, expenses, and inventory, it is probably the dimension that you will choose most often for the anchor dimension. You can, however, choose a different dimension.
If you choose your anchor dimension, the dimension must be:
M = C-(N-1)
where:
M is the number of members in the anchor dimension that store data.
C is the maximum number of columns allowed in a table by your relational database.
N is the total number of dimensions in the outline of your database.
For example, if the column limit imposed by your relational database is 254, and your database has six dimensions, the dimension you specify as the anchor dimension can contain as many as 249 members. That number does not include shared or virtual members.
Additionally, your anchor dimension should have the following characteristics:
Choose your anchor dimension carefully rather than letting DB2 OLAP Server automatically choose one for you. After you load data into a multidimensional database, you cannot change or delete the anchor dimension without first clearing all the data in the database. After you change or delete the anchor dimension, you must reload all of your data. Your choice of an anchor dimension can affect the performance of queries, calculations, and data loads.
If DB2 OLAP Server chooses an anchor dimension for you, it first searches for a dense dimension with an accounts tag with a number of members such that M = C-(N-1). If it does not find a dense dimension using this first method, it chooses the first dense dimension in an outline with a number of member such that M = C-(N-1). You can view the anchor dimension chosen by DB2 OLAP Server. See "Viewing DB2 OLAP Server Run-time Parameters".
To set an anchor dimension, create a user-defined attribute named RELANCHOR for the top-level member of the dimension. The top-level member is the member that is the dimension name.
DB2 OLAP Server uses the member with the RELANCHOR attribute to determine which dimension to use as the anchor dimension when it creates the fact table.
You can assign the RELANCHOR attribute to only one member.
For detailed instructions on creating a user-defined attribute for a member in a dimension, see the Database Administrator's Guide.
You can view the setting of the anchor dimension and other run-time parameters using the Application Manager. See "Viewing DB2 OLAP Server Run-time Parameters".
When you create a multidimensional database with DB2 OLAP Server, the number of dimensions the database can contain is limited by the maximum number of columns allowed in a table by your relational database.
The fact table, which is the largest table created in your relational database by DB2 OLAP Server, contains one column for each member in the anchor dimension that you specify, and one column for each non-anchor dimension in your database outline. Therefore, the total number of members in your anchor dimension, plus the total number of non-anchor dimensions in your database outline, minus one, cannot exceed the column limit allowed by your relational database. That number does not include shared or virtual members.
To determine the maximum number of dimensions your relational cube can contain:
Remember to include the top level of the dimension. The highest level of a dimension is counted as a member because it can contain a value that is a consolidation of lower level members.
For example, if your anchor dimension will contain no more than 100 members, and your relational database allows as many as 254 columns in a table, you can have as many as 153 dimensions.
There is no limit for the number of members each non-anchor dimension can contain.
DB2 OLAP Server and the DB2 OLAP Starter Kit provide a dual storage manager function, which enables you to choose either a multidimensional storage manager or a relational storage manager when you create an application, or when a client program creates an application. The default storage manager is specified in the ESSBASE.CFG file, using the DATASTORAGETYPE statement. The multidimensional storage manager is the default. If there is no ESSBASE.CFG file, or if there is no DATASTORAGETYPE statement in the file, then the default is used.
If you upgrade from a previous version of DB2 OLAP Server, and you were using the relational storage manager as the default, then the installation program will add an entry to the ESSBASE.CFG file to specify the relational storage manager as the default. After you have finished installing the new version of DB2 OLAP Server, you must start and stop each of your existing applications before changing the DATASTORAGETYPE statement. DB2 OLAP Server uses the storage type specified in ESSBASE.CFG when it starts existing applications. Once applications have been started and stopped, you can change the DATASTORAGETYPE statement, and the change will apply to your applications the next time they are started.
If you use the Administration Manager, you can specify either the multidimensional storage manager or relational storage manager when you create a new application. The storage manager type that you choose will override whatever default is specified in the DATASTORAGETYPE statement.
To edit the ESSBASE.CFG file, follow these steps:
You can view the following run-time parameters using the Application Manager:
To view the run-time parameters:
The most important consideration when loading data is the ordering of the input data. For best performance, load the data in the reverse order of your outline, as long as your outline is ordered with dense dimensions first and sparse dimensions second, with the sparse dimensions in increasing size. Load the largest sparse dimension first, then the next largest, and so on, and load the dense dimensions last.
When you order your input data this way, the data loads much faster because the all the data for each block is loaded at the same time. Also, the blocks are loaded in the correct index sequence. If the input data is badly ordered, index management is more complex; blocks are written several times when the different data elements are loaded, and all the additional operations are logged.
There are additional steps you can take to optimize data load performance. Before you start the data load, read "Tuning Data Loads". Also, see the Database Administrator's Guide for more information about loading data.