When analyzing
stores, in particular their sales performance,
it is customary to include the partial sales of important departments
in the analysis. You can do this by splitting the daily store sales
amount into the sales amounts for individual departments. This is
a general technique for data analysis in many areas. If you use this
technique, the final aggregation level is lower than the focus level.
In the
properties view of an Aggregation Split, you can define
an aggregation split in detail.
The properties view provides
the following tabs:
General
- Name
- The name for an aggregation split must be unique.
- Description
- Optionally, you can add more details
about the aggregation split.
Split
Definition
You must define the aggregation
that you want to split.
- Aggregation
- From
the drop-down list, you can select the aggregation to be
used.
- Aggregation Values
- After
you selected the aggregation to be used, you must select
how the split values are represented. You can select one of the following
values:
- Absolute values
- Percentage of the total aggregation
value
- Percentage of the aggregation values over all slices
For example, you might have an aggregation that summarizes
the sales over the whole United States. You want to split this into
values for individual states, for example, New York, Maryland, and
Wyoming.
- The percentage of the total aggregation shows the
percentage with
respect to the national total.
- The percentage of the aggregation
values over all slices is based
only on the total of New York, Maryland, and Wyoming.
If
you calculate the split level values as percentages,
the split level value is set to SQL-<NULL> if
the total aggregation value or the aggregation values over all slices
are 0. This is done to prevent a potential runtime
error for a division by zero, when executing the generated data flow
or SQL script.
- Split Level
- You
can select one or more columns or hierarchy levels as split
level.
If the dimension is used by one of the focus components,
the split level must be lower in hierarchy than the split level of
the focus component. Otherwise you can select any level.
To
define the split level, select a column from one of the tables of
the input model or a level from one of the available hierarchies.
Slices
A slice
is the portion of the base
aggregation that belongs to one value of the split level or to a combination
of values of several split levels. A slice has a name and a corresponding
result column name. Within the profile, these names must be unique.
You
can use the Aggregation Split wizard or manually
define a slice:
- Aggregation Split wizard
- To
open the wizard, click Open Aggregation Split Wizard.
The
wizard fetches the values that are contained in the database and presents
a list of values or a list of value combinations. From this list,
you can select the values that you want to use.
In the Aggregation
Split wizard, you can also define slice names and result column names.
- Prefix or Suffix modification
- To modify
the prefixes or the suffixes of slice names or result
column names, click Edit Prefixes or Suffixes.
Select the prefix or the suffix to be replaced and the new prefix
or suffix. All slice names and result column names with the corresponding
prefix or suffix are modified.
- Manual slice
definition
- Click Add Slice repeatedly
to add a value
or a combination of values. When the new line appears in the table,
type the value or the value combination, a name for the resulting
slice, and a name for the result column in the output table.
You
can also click Edit to open the Edit Slice
dialog to manually define slices.
- Aggregate
Remaining Values into Default Column
- To aggregate all values
that occur in your data - even though
they might not be listed as values for the split level in your slices
- to a default column, select Aggregate remaining values
into default column.
For example, if your data contains
rows that include sales data from the states Texas or California,
the sales data of these states is aggregated into the default column.
Result Columns
You can
override the default data type of the result columns. The drop-down
list shows the available data types.
The data
type must be compatible with the result type of the defined SQL expression.
If you selected to aggregate the values as percentages, the data type
must be numeric.