Query Types
This chapter provides an overview of query types.
See Also
Creating and Running Simple Queries
Query Types
This section provides an overview of query types and discusses:
Dimension queries.
Attribute Queries
Data source queries.
Understanding Query Types
You can create several types of queries to use with PeopleSoft Cube Manager, all of which you must define as user (ad hoc) queries, as opposed to role queries, or database agent queries.
See Dimension Queries.
See Attribute Queries.
See Data Source Queries.
Because you cannot have duplicate member names in a dimension, unless those members are shared (Essbase is the only platform that handles shared members), you cannot have duplicate query column names in your query metadata. PeopleSoft Cube Manager treats uppercase and lowercase characters as distinct, so the names ABC, Abc, and abc are all considered unique member names. However, Essbase offers an option to change all member names to uppercase. If you enable this option, you create problems with members that are identical except for their letter casing.
Note. PeopleSoft Cube Manager permits duplicate node names if you cannot avoid the duplication.
Dimension Queries
Dimension queries enable you to define the dimension structure using query results instead of, or in addition to, a tree. However, remember that you are using queries to create a tree-like structure. For narrow query definitions, each dimension query maps child members at a particular level to parent members at the next higher level. For wide query definitions, you need only one query to build the dimension. Optionally, these queries might contain extra members or level attributes, as well as the relationship information.
You can convey hierarchical information in one of two ways:
When you use multiple queries to define dimensional structure, the first query that you specify defines the first two levels of the dimension. To add lower levels, you must write one additional query for each additional level.
Suppose that you want to build a department dimension that contains the following departments within an organization:
Example of departments within an organization
The levels of the organization might be described in the following way:
Example of levels of the organization
To create the dimension, you must write two queries to provide the preceding information: one to define the child members at level 2 and one to define the child members at level 3.
Query 1:
Query 1
Note. During the build, PeopleSoft Cube Manager knows that child members with an unspecified parent become level 2 members, directly under the top of the dimension. You can create an empty column in a query by adding a blank ("") expression. Be sure to enter some meaningful text for the heading text and unique field name (such as Top of Dimension) so that you can easily identify the blank column when mapping query columns to dimension levels.
Query 2 must return one column for the second-level members (DEVELOPMENT and SALES) and one column for the third-level members (100, 200, 300, 400, and 500).
The query results look like this:
Query 2
To add additional levels, write one query for each additional level. Building on the previous example, assume that you want to create an employee dimension in addition to a department dimension. In this case, include a fourth level showing the employees in each department:
Example of employees within an organization
Now you might describe the levels of the organization in the following way:
Description of the levels of the organization
In addition to the two queries that you create, you must create a third query with the following results to add the fourth level to the dimension:
Results to add to the fourth level to the dimension
The following diagram illustrates how you supply three queries to create a dimension with four levels:
Four-level dimension created using three queries
Your dimension queries should always produce at least two columns in the result set: one for the parent and one for the child (or member). Except for the top-level query, a parent column for a given level must always correspond to the child column of the previous level.
Optionally, you can include attributes in the query. All attributes that you specify in a narrow query are associated with the field that is specified as the member. You must add attributes for the topmost member by using an attribute query.
When defining your dimension with a wide query, follow the same logic that is used with narrow queries.
Using the previous example for narrow queries, the following table illustrates the dimension that you want to build with a wide query:
Dimension that you want to build with a wide query
Instead of writing multiple queries to build this dimension, write one that contains only this information. The query result set might look like the data in this table:
Query results of writing one query to build dimension
As with narrow queries, the query can return attributes that you can associate with members. However, in the case of wide queries, you can assign the attributes to any member.
Attribute Queries
You can use attribute queries to set optional attributes on the members within a dimension. Set attributes on either node members or detail members of a dimension.
An attribute query should return at least two result columns: one that identifies the members whose attributes you want to define and one for each type of attribute to be set.
Example of an attribute query with at least two result columns
Most attribute types are platform-specific: they are used by one of the target platforms, but not all. Each attribute type uses its own set of conditions to determine whether to apply the attribute. In some cases, for the attribute to be applied, the attribute column must contain a specific value. In other cases, the attribute column simply must not be blank. An attribute query can return a subset of the members, in which case the specified attributes are set only on those members returned.
If an attribute query is on a table that uses a setID, you must select only the setID in which you’re interested. Thus, only one member ID field exists to tie the query results to the dimension.
Note. In the preceding example, one query sets three attributes. You can also create three separate queries in which you return the member and one attribute.
You can define numerous types of attributes by using queries and PeopleSoft Cube Manager. We discuss the following valid attribute types:
Flip sign attribute refers to the reversal of + (plus) and − (minus) signs for the member, which is sometimes necessary for accounting purposes. Valid values are blank (do not flip the sign) or non-blank (flip the sign). In a star schema, an attribute column is populated with a value of – 1 when a member has a sign to flip. In Cognos PowerPlay and Oracle Essbase, the data that is populated in a flip-signed cell has the opposite sign of the source query.
Label attribute provides a description for a member. In an Essbase database, this label equates to the default alias. In a Cognos PowerPlay PowerCube, it equates to the label. For star schemas, the label is a description of the member.
User-defined attribute applies to Oracle Essbase only. Oracle Essbase enables you to specify user-defined attributes for members. You can then use calculation (calc) scripts to search for and manipulate members that have particular user-defined attribute values.
PeopleSoft Cube Manager supports user-defined attributes. To design your own user-defined attributes, select PeopleTools, Cube Builder, Cube Manager, Attribute Definitions.
Oracle Essbase has the following valid property types:
Currency category applies to account-type dimensions. This attribute specifies a member that requires currency conversion to a specific category type. In the attribute query field, supply the type of conversion that is required (a value, normally in dollars).
Currency conversion type applies to account dimensions. This attribute equates to the Currency Conversion buttons on the Account tab of the Attributes dialog box for an account dimension in the Oracle Essbase Application Manager.
If the Currency Category attribute is set to a non-blank value on a member, that non-blank value is automatically applied to the Currency Conversion Type attribute. If the Currency Category attribute is set to blank, the Currency Conversion Type attribute is automatically set to inherit-use ancestor. If the Currency Category attribute is not applied at all, the Currency Conversion Type attribute is automatically applied with a value of no conversion ("").
If you set this attribute manually, valid values are blank (no conversion) or non-blank. If the query returns a non-blank value, that value is used as the currency category.
Currency name applies to country dimensions. The value of this attribute defines what type of currency the country or market region uses. This value identifies the type of currency in a currency cube.
Data storage enables Essbase to recognize what type of storage to allocate for the member. Valid values are 0 or blank (store data), 1 (never share), 2 (label only), 3 (shared member), 4 (dynamic calculation and store), and 5 (dynamic calculation, no store).
PeopleSoft Cube Manager sets the default value as store data for all members in the first rollup and the non-detail nodes of all other rollups. Detail nodes in secondary rollups are set to shared members.
Expense item applies to account dimensions only. Essbase has certain built-in formulas that can take advantage of the knowledge that an item is an expense. To pass this knowledge on to Essbase, you should use this property. Valid values are Blank (set) and non-Blank (do not set).
Time balance affects how the parent time value is calculated. Valid values are 0, 1, 2, and 3, which correspond to none, first, last, and average, respectively.
This property enables you to define the mathematical operator used for rolling up members. Most often, you expect that data is added (using the + operator) when rolled up. However, you might occasionally need to specify other operators, such as those listed in the following table:
|
Valid Value |
Action |
|
+ (plus sign) |
Add (default) |
|
– (minus sign) |
Subtract |
|
* (asterisk) |
Multiply |
|
/ (forward slash) |
Divide |
|
[Blank] |
Do not consolidate. |
|
~ (tilde) |
Do not consolidate. |
|
% (percent sign) |
Divide the total of previous member calculations by this member and multiply by 100. |
Cognos PowerPlay has the following valid attribute types:
Long description equates to any valid description in Cognos PowerPlay, meaning that it can contain unlimited text.
Short label equates to the short name. Valid values are any valid Cognos PowerPlay short name.
See Also
Oracle Essbase and Cognos PowerPlay Services documentation.
Data Source Queries
Data source queries define the data that you bring into the cube. Writing a data source query is straightforward; the query must return one column for each dimension and one column for each measure. Assume that you want to build a data source query for a cube containing amounts that are dimensioned by account, department, and period.
See Data Source Queries.