When you create an OLAP application and multidimensional database, DB2 OLAP Server catalogs the new application and database and creates a set of relational tables, known as a star schema. In addition, DB2 OLAP Server creates and manages a number of views that simplify SQL application access to the multidimensional data. You can use custom applications and standard query tools to access the multidimensional data using these views. Some applications are designed to take full advantage of data stored in the star schema created by DB2 OLAP Server.
The following list shows the complete set of views managed by DB2 OLAP Server:
DB2 OLAP Server stores all of its base tables and views in the username schema, where username is the user ID assigned to DB2 OLAP Server. For the SQL examples in this chapter, the schema name OLAPSERV is used.
All view names are in uppercase. Do not enclose view names in quotation marks. DB2 OLAP Server constructs view names and stores them in catalog views. Your SQL applications can query the view names from catalog views. Figure 9 shows the primary DB2 OLAP Server views.
Figure 9. DB2 OLAP Server schema
|
There is one cube catalog view that DB2 OLAP Server uses in its username schema. This view contains one row for each cube. Use this view to get details about all OLAP applications and cubes stored in a schema. The cube catalog view catalogs all the OLAP applications and databases managed by DB2 OLAP Server.
The cube catalog view name is CUBECATALOGVIEW. Like all other views, it is owned by the schema assigned to DB2 OLAP Server.
Table 14 shows the columns in the cube catalog view.
Table 14. Contents of cube catalog view
Use this SQL statement to get a list of OLAP applications:
SELECT DISTINCT APPNAME FROM OLAPSERV.CUBECATALOGVIEW
Use this SQL statement to get a list of multidimensional databases in application Sample:
SELECT CUBENAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample'
Use this SQL statement to get the view names for multidimensional database Basic in application Sample:
SELECT CUBEVIEWNAME,FACTVIEWNAME,STARVIEWNAME,ALIASIDVIEWNAME,LROVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample' AND CUBENAME='Basic'
The cube view and dimension views contain information about the dimensions and members in a relational cube. There is one cube view for each relational cube, and one dimension view for each dimension within a relational cube. These views can be used to query many of the attributes that are assigned to dimensions and members in the OLAP outline.
There is one cube view for each relational cube that DB2 OLAP Server manages. The cube view contains one row for each dimension in the relational cube. Use this view to get information about the dimensions of a cube.
The cube view name is obtained from the CubeViewName column of the cube catalog view.
Table 15 shows the columns in the cube view.
Table 15. Contents of Cube View
To access data in the cube view, your application must first determine the name of the cube view from the cube catalog view.
For example, to find the name of the cube view for the Basic database in the Sample application, you query the database use the following SQL statement:
SELECT CUBEVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample' AND CUBENAME='Basic'
The result of this query might be:
OLAPSERV.SAMPBASI_CUBEVIEW
To list the dimension names and the corresponding dimension view names for the Basic database:
SELECT DIMENSIONNAME.DIMENSIONVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW
To list the dimension names of the dense dimensions of the Basic database:
SELECT DIMENSIONNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONTYPE = 0
To determine the names of the non-anchor dimensions used to name columns in the star view:
SELECT RELDIMENSIONNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONTYPE <> 2
To return the name of the relational attribute view for the Product dimension:
SELECT RATVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONNAME='Product'
The dimension view name is obtained from the DimensionViewName column of the cube view.
Table 16 shows the columns in a dimension view.
Table 16. Contents of a dimension view
| Name | Type | Size | Contents |
|---|---|---|---|
| MemberName | VarChar | 80 | The name of the member. |
| RelMemberName | VarChar | 18 | Anchor dimension only. The DB2 OLAP Server member name.
This name is used to name columns in the Fact and Star views that correspond
to the members of the Anchor dimension. It is a unique name when
compared to all other Anchor dimension member names and the names of
non-anchor dimensions of this relational cube. It is a modified version
of MemberName. Changes that you might need to make to MemberName
are:
|
| RelMemberID | Integer | None | The DB2 OLAP Server ID for this member. This ID is used to join the dimension table to the fact table. |
| ParentRelId | Integer | None | The relational ID of the member's parent in the OLAP outline. The value is NULL for the top level member. |
| LeftSiblingRelId | Integer | None | The relational ID of the member's left sibling in the OLAP outline. This value is NULL for members that do not have a left sibling. |
| Status | Integer | None | The status of this member can contain a combination of:
|
| CalcEquation | Long VarChar (workstation); VarChar (OS/390) | 32700 (workstation); 250 (OS/390) | The default calculation equation for calculated members. Note that the default calculation equation might not be the equation used to calculate the member's value if a different calculation is specified in the calculation script used to calculate the relational cube. |
| UnarySymbol | Small Integer | None | The unary calculation symbol:
|
| AccountsType | Integer | None | This attribute is used only for the Accounts dimension. It can
contain a combination of the following values:
|
| NoCurrencyConv | Small Integer | None | Currency conversion setting:
|
| CurrencyMemberName | VarChar | 80 | A member name from the currency cube associated with this member. |
| GenerationNumber | Integer | None | The generation number for this member. |
| GenerationName | VarChar | 80 | The generation name for this member. |
| LevelNumber | Integer |
| The level number for this member. |
| LevelName | VarChar | 80 | The level name for this member. |
| alias table name There is one alias column for each OLAP alias table used in the outline. | VarChar | 80 | The alias for this member in an associated OLAP alias table. If an alias is not provided for a member, this value is null. See Using Alias ID Views. |
| relational attribute column name There is one relational attribute column for each RatCol user-defined attribute. | The data type specified when the relational attribute column was created. | The size specified when the relational attribute column was created. | The value of the relational attribute for this member. |
To access data in a dimension view, your application must first determine the name of the dimension view from the cube view.
For example, to find the name of the dimension view for the Time dimension in the Basic database, you query the database using the following SQL statement:
SELECT DIMENSIONVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONNAME='Time'
The result of the query might be: OLAPSERV.SAMPBASID_TIME
To list the member names for the Time dimension:
SELECT MEMBERNAME FROM OLAPSERV.SAMPBASID_TIME
DB2 OLAP Server creates and maintains two views of the fact table of the star schema:
Because a fact table contains values with different levels of aggregation, you must be sure that the set of members selected in each dimension has the same level of aggregation if you write an SQL application to aggregate. Otherwise, your aggregations will be incorrect. One way to meet this requirement is to include a constraint on either the generation number or level number field in the dimension table.
The fact table that DB2 OLAP Server creates has one column for each non-anchor dimension, and one column for each member of the anchor dimension that stores data. The fact table that corresponds to the outline used in Figure 9 has the following columns:
The dimension columns store member IDs that reference members of each non-anchor dimension. The member IDs can be mapped to member names using the dimension views. The anchor member columns store the actual data values. The anchor dimension members can be mapped to columns of the fact view using the dimension view for the anchor dimension.
DB2 OLAP Server uses internal names for the columns of the fact table, and internal IDs for the members. The fact view replaces the internal column names with dimension and member names, but it does not map the dimension column member IDs to member names. The star view replaces the internal column names with dimension and member names, and maps the dimension column member IDs to member names by joining the fact table to the dimension tables.
Although any dense dimension can be specified as the anchor dimension, if you are accessing the fact or star view from SQL applications and running ad-hoc queries, the most natural mapping is obtained if you specify the Accounts dimension as the anchor dimension.
The fact view name is obtained from the FactViewName column of the cube catalog view.
The fact view contains a variable number of columns of two types:
Table 17 shows details about the two types of columns in the fact
view.
Table 17. Contents of the Fact View
To access data in a fact view, your application must first determine the name of the fact view from the cube catalog view.
For example, to find the name of the fact view for the Basic database in the Sample application, you use the following SQL statement:
SELECT FACTVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample' AND CUBENAME='Basic'
The result of the query might be:
OLAPSERV.SAMPBASI_FACTVIEW
If your application keeps track of RelMemberID values for members, you can query the fact view directly. For example, to select the data values for product with RelMemberId 3 (100-20), in market with RelMemberId 2 (East), at time with RelMemberID 4 (Q3):
SELECT PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_FACTVIEW WHERE PRODUCT=3 AND MARKET=2 AND TIME=4
More commonly, queries against the fact view include joins with the dimension views. A query using joins that is equivalent to the previous query is:
SELECT PROFIT,SALES,COGS
FROM OLAPSERV.SAMPBASI_FACTVIEW,
OLAPSERV.SAMPBASID_TIME,
OLAPSERV.SAMPBASID_MARKET,
OLAPSERV.SAMPBASID_PRODUCT,
WHERE OLAPSERV.SAMPBASID_TIME.MEMBERNAME='Q3'
AND OLAPSERV.SAMPBASID_PRODUCT.MEMBERNAME='100-20'
AND OLAPSERV.SAMPBASID_MARKET.MEMBERNAME='East'
AND OLAPSERV.SAMPBASI_FACTVIEW.TIME=OLAPSERV.SAMPBASID_TIME.RELMEMBERID
AND OLAPSERV.SAMPBASI_FACTVIEW.PRODUCT=OLAPSERV.SAMPBASID_PRODUCT.RELMEMBERID
AND OLAPSERV.SAMPBASI_FACTVIEW.MARKET=OLAPSERV.SAMPBASID_MARKET.RELMEMBERID
The star view name is obtained from the StarViewName column of the cube catalog view.
The star view contains a variable number of columns of two types:
Table 18 shows details about the two types of columns in the star view.
Table 18. Contents of the Star View
To access data in the star view, your application must first determine the name of the star view from the cube catalog view.
For example, to find the name of the star view for the Basic database in the Sample application, you use the following SQL statement:
SELECT STARVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='SAMPLE' and CUBENAME='BASIC'
The result of this query might be: OLAPSERV.SAMPBASI_STARVIEW
To select the data values for product 100-10, in the central market, during the first quarter:
SELECT PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_STARVIEW WHERE PRODUCT='100-10' AND MARKET='Central' AND TIME='Q1'
To select all products that lost profits in the central region during the second quarter:
SELECT PRODUCT,PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_STARVIEW WHERE MARKET='Central' AND TIME='Q2' AND PROFIT < 0
Not all members of the star view are at the same hierarchical level, so construct your queries carefully when using SQL to perform aggregation operations. The selected members in a dimension should be at the same level to avoid double aggregations.
For example, the following SQL statement shows members selected at different levels in the star view. (Some sales will be counted twice because two levels of sums are aggregated.)
SELECT SUM(PROFIT) FROM OLAPSERV.SAMPBASI_STARVIEW
WHERE MARKET IN ('Central','Illinois') AND
PRODUCT='100' AND
TIME IN ('Q1','1996')
Assuming that Illinois is part of the Central region and that Q1 is part of 1996, this query results in PROFIT figures at both the state and region level and the quarter and year level being summed. Because the Central region already includes Illinois data, Illinois and Q1 data are counted twice in the sum. If you want to correct the SQL to sum the sales for two states in the Central region during two different quarters, you might use the following example:
SELECT SUM(PROFIT) FROM OLAPSERV.SAMPBASI_STARVIEW
WHERE MARKET IN ('Indiana','Illinois') AND
PRODUCT='100' AND
TIME IN ('Q1','Q2')