Chapter 4. Modeling dynamic cubes 75
4.3.4 Modeling level keys
The level key is the mechanism to define the members of a level. It is a set of attributes and
attribute expressions. The level key must uniquely identify the member within the level. The
level key can consist of one or more attributes. The combination of these attributes will be the
level unique key.
A member is uniquely identified when the value of the level key for the member does not
match the value of the level key for any other member in the level. This uniqueness is defined
partly by the data contained in the key and partly by the objects of the key. If the data in an
attribute is sufficiently detailed, it can uniquely identify a member. If not, you must add more
attributes to the level key.
These keys guide the SQL queries that are made to the databases to retrieve the data,
governing, among other things, GROUP BY.
A possibility is for a single column to uniquely identify a member, even if the column was not a
key in a table. The data in the column might be sufficient to identify the member. For example,
in the sample database GOSLDW, the column QUARTER_KEY in GO_TIME_DIM has values
in a format that uniquely identifies things.The values in QUARTER_KEY, such as 20131,
20132, 20133, and 20134 have sufficient information to identify a quarter within the context of a
calendar year. The modeler must be careful, otherwise additional data causes the key to no
longer be unique. It is also possible that the sample that the modeler examined contains
uniquely identifying data, but other records do not.
An example of a column that does not uniquely identify a member by its data is be the
MONTH_NUMBER column in GO_TIME_DIM. The values, such as 1, 2, and 3 do not identify
what the month is in the context of any particular year. GO_TIME_DIM has a column that
serves as a month level key, MONTH_KEY. In many cases you might not have that luxury. You
might need to create a key where the business key is insufficient to identify the member.
It is necessary to distinguish uniquely identifying data from unique data. Data is unique when
a datum in a column occurs only in one record in the table. Uniquely identifying data might
exist multiple times. In a time dimension table in a data warehouse, the datum for any
particular quarter could be duplicated for each record at the lowest grain of the dimension. If
the dimension is at the day grain, there could be over 90 records in which each quarter value
would exist. In GO_TIME_DIM you will see examples of such redundancy.
In the level key, the business key is the value that governs the generation of members. Each
distinct value in the business key will be used to create a member. In the level key editor, it is
identified by the key icon. The order of the keys in the level key is important. The first key
attribute is assigned the business key role. You must re-order the attributes to make the
attribute that you want to be business key. A business key does not need to uniquely identify
an entity. That is the purpose of the level key. What is necessary is that the combination of the
business key, and the business keys of its ancestors, uniquely identify an entity.
The additional keys provide context. Usually they can be the business keys of the higher
levels in the hierarchy. For example, assume that the values identifying the quarters in a time
dimension are not unique but are in the form of 1,2,3,4. You need to include additional keys to
make each quarter unique.
You might think that you need to include the business keys of higher levels in the level key but
that can have performance and other issues. It should not be necessary. The metadata
should contain sufficient information, either in the business key itself or in conjunction with the
other keys that you use in the level key. For a slowly changing dimension, however, the
inclusion of the higher-level business keys are necessary.
76 IBM Cognos Dynamic Cubes
Knowing the data and what it is being used for is important. Something that seems
conceptually sound can produce results that are incorrect. If the application is meaningless to
you, it can be difficult to know if you are implementing it correctly.
Some reporting requirements can specify alternate hierarchies which will organize the
members based on particular attributes that are of interest to users. You need to be aware
that the levels must identify the members as being unique within the context of the hierarchy.
It is possible that these hierarchies can produce non-unique members although the level key
can produce uniquely identified members in other contexts.
For example, assume that a dimension organizes stores by geography in one hierarchy. An
alternate hierarchy that organizes the stores by some attribute, store size class for instance,
can be problematic if you attempt to reuse some levels of the geography level in the alternate
hierarchy, but some levels can be reused without a problem. For example, the city level can be
uniquely identified in the geography hierarchy but, because stores of different sizes exist in a
city, the city can repeat in the city level of the store size hierarchy. The city level in the store
size hierarchy will need additional keys to identify each instance of that city in the level.
Composite key expressions are common in some applications but the nature of the level key
does not require their use. Having the attributes that uniquely identify the members in the
level key is sufficient.
In modeling for multilingual models, it is important to use keys that do not vary from one locale
to another.
Be aware of the potential number of members that are generated for any node in the member
tree. A large number can be quite difficult to navigate through and a very large number can
impede performance. Discuss with your consumers what sort of reports they will write. With
this information, you can create hierarchies that categorize members into smaller, more exact
sets.
You can use that discussion as an opportunity to refine the model to meet the needs of the
consumers. They might have requirements that they did not tell you about or did not request
because they might not believe that model it is possible. This information can assist your
consumers in generating reports and designing the functionality that might otherwise need to
be specified in the report. Additional functionality, because it would be built into the cube, can
speed up report processing time. It can have practical benefits of easing communications and
other aspects of office politics.
An expression can have a null result if one element of the expression returns a null. For
example, some records in EMP_EMPLOYEE_DIM have null values for the column Address2.
An expression that used Address2 will return nulls for each record where the value for
Address2 was null. If you have data with nulls, you must incorporate tests for nulls to handle
them appropriately:
Address1|| ' ' || Address2
An expression that tested for nulls might look like this:
Address1|| ' ' || if (Address2 is null) then ( ‘‘ ) else (Address2 )
If the null happens for an attribute that is being used in the level key, some members might not
be generated. If the null happens in a member caption, the member caption displays NULL as
its caption, even if other elements of the expression that is used to define the member caption
are not null. Expressions of this type usually need the data type to be the same.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.