Essbase Beginner’s Guide Chapter-VIII

Essbase Beginner’s Guide Chapter-VIII

Dimension Type

1) Standard dimension

2) Attribute dimension

3) Based on Storage

Dimensions

In Essbase, there are two types of dimensions

1) Standard dimension

a. Dense

b. Spares

2) Attribute dimension

a. Text

b. Numaric

c. Boolen

d. Date

3) Based on Storage

Standard Dimension

The distinction between Dense and Sparse dimensions allows Essbase to efficiently handle large amounts of data that is not evenly spread across the data blocks. It is this efficiency that allows Essbase to offer the slice and dice style of data access, while still maintaining high performance for fast data processing and retrievals.

Essbase automatically configure the Dense and Sparse dimension settings in your database. Essbase do this using the EAS Outline Properties tab. Essbase will do this fairly well initially, based on the data already existing in the database.

Auto configure option on the outline properties screen. If set to true, this option will let Essbase automatically set the Dense/Sparse settings of the dimensions in your database.

Notice in the preceding screenshot that there is an Auto configure option on the outline properties screen. If set to true, this option will let Essbase automatically set the Dense/Sparse settings of the dimensions in your database.

Sparse

A dimension which has low probability that data exists for every combination of dimension members.

Data is normally stored in sparse form. If no value exists for a given combination of dimension values, no row exists in the fact table. For example, if not every product is sold in every market. In this case, Market and Product are sparse dimensions. It's why in the reporting tool OBIEE for instance, by default, data are considered sparse.

Dense

A dimension which has the high probability that data exists for every combination of dimension members.

Most multidimensional databases may also contain dense dimensions. A fact table is considered to have dense data if it has (of a high probability to have) one row for every combination of its associated dimension levels.

A standard report can contain tens of thousands of data values, and it would be extremely inefficient for Essbase to deliver each data value into memory, one 8-byte cell at a time. Instead, for block storage databases, Essbase groups data cells into data blocks of dense dimensions, thus increasing the amount of information that can be delivered into memory at one time.Each data block is a multidimensional array that contains a fixed, ordered location for each possible combination of dense dimension members. Accessing a cell in the block does not involve sequential or index searches. The search is almost instantaneous, resulting in optimal retrieval and calculation speed.

Excel Example :-

Jan->Sales

NY->A->Jan->Sales

Number of Blocks 6
Each Block Size=12
Index Size=6
Cube Size=Number of BlocksXEach Block Size+Index Size
Size of Cube	78
Dense
Measure(Sales,COGS,Margin)
Time
Product
City
Sparse
Product
City

Jan->Sales->NY>A

Number of Blocks 12
Each Block Size=1	6
Index Size=12
Cube Size=Number of BlocksXEach Block Size+Index Size
Size of Cube	84

Attribute dimension

There is a dimension type known as an Attribute dimension type. The Attribute dimension is not a full blown standard dimension and in fact must be associated with a standard dimension. The Attribute dimension allows you to analyze your data with even finer granularity by adding data characteristics (for example, size) to your product.

Attribute dimensions are a special type of dimension and are associated with standards sparse dimension. Essbase does not store the data for attribute dimensions; Essbase dynamically calculates the data when a user retrieves it. These should be placed below the standards dimensions.

A standard report can contain tens of thousands of data values, and it would be extremely inefficient for Essbase to deliver each data value into memory, one 8-byte cell at a time. Instead, for block storage databases, Essbase groups data cells into data blocks of dense dimensions, thus increasing the amount of information that can be delivered into memory at one time. Each data block is a multidimensional array that contains a fixed, ordered location for each possible combination of dense dimension members. Accessing a cell in the block does not involve sequential or index searches. The search is almost instantaneous, resulting in optimal retrieval and calculation speed.

Every attribute dimension is associated with standered dimension.

Caffeinated is one attribut dimension it’s type is boolean so it has only two values either true/false or yes/no.

You can assign attribute to standered dimension.

Creating Attribute Dimensions

Attribute dimensions are powerful tools for reporting and calculating additional data. Typically, an attribute is a concrete characteristic of a member in a standard dimension; for example, size, inception date, or any other characteristic that does not change over time.

Attribute Dimension Rules

Attribute dimensions are not like standard dimensions. Standard dimensions can have multiple relationships across other dimensions; data can be stored and viewed across every intersection of all dimensions. For example, a market type dimension which relates to a market dimension results in the ability to store and view data such that any market can have multiple categories associated with it.

Attribute dimensions are not UDAs. Although there are many similarities, attribute dimensions are very different. Attribute dimensions provide much richer reporting capabilities.

Set of Instruction

Ø Base dimensions must be sparse. Base dimensions are the dimensions associated with the attribute dimension.

Ø Attribute dimensions do not have consolidation symbols or formulas. All calculations are done across the base dimension.

Ø Although attribute dimensions can have a multi tiered hierarchy, you must associate the level 0 members (bottom level members) of attribute dimensions with base dimension members.

Ø Base dimension members associated with attribute dimensions must be at the same level. This can be any level, but it must be the same across the base dimension.

Ø Do not tag shared members in the base dimension with attribute dimension members. Shared members automatically inherit their respective stored member attributes.

Attribute Calculations

By default, dynamic attribute calculations are available through the Attribute Calculations dimension. This dimension behaves like other attribute dimensions in that it is not automatically displayed in a report until you explicitly request it.

Boolean and Date

Default Boolean values are TRUE/FALSE and YES/NO.

Default Date formats are mm-dd-yyyy to dd-mm-yyyy.

Numeric Ranges for Numeric Attributes

A numeric attribute can represent a single value or a range of values. Ranges can be used for report filtering and calculations.

The default setting is Tops of Ranges.

Values between lower value and upper value are associated with attribute member.

If you change the option selection to Bottoms of Ranges:

Values between upper value and lower value are associated with attribute member.

User Defined Attributes (UDA)

Essbase UDA is a descriptive word or tag about an outline member. Similar to an alias, the main difference is that an alias may only be attached to one member. A UDA can be attached to many members.

What the UDA offers is a way to simplify and make the ongoing operations of your database more efficient.

Rules to create UDA

1. You can assign many UDAs to a single member, but you cannot assign the same UDA to a member more than once.

2. You can use the same UDA on many different members.

3. A UDA cannot be the same name or word as a member name or alias name.

4. UDAs cannot be used on Shared Members or members in an attribute dimension.

5. UDAs run with the dimension they were created in and can only be assigned to other members in that same dimension.

6. UDAs are member specific and only apply to the single member they are attached to. Parents, children, and so on, of a member assigned a UDA are not covered unless they also have the UDA assigned to them as well.

How to assign UDA to a dimension?

Right Click on dimension and go to member properties. Select UDA tab then assign that in to dimension.

Based on storage

A dimension type is a property that analytic services provide that adds special functionality to a dimension.

Those are:-

1. Time

2. Accounts

3. Currency

4. Country

5. Attribute

6. None

Time dimension type

The Time dimension type is the dimension where you define the periods used to calculate and report your data. This would typically be the dimension where you store calendar periods. The Time dimension also supports several of the Accounts dimension functions.

DTS (Dynamic Time Series)

Dynamic time series is means it changes dynamically. In order to calculate quarter -to -date values dynamically, you need to use a dynamic time series member for a quarter on the dimension tagged as time Analytic services provides height predestined dynamic time series members.

1. H-T-D (History to date)

2. Y-T-D (Year to date)

3. S-T-D (Season to date)

4. P-T-D (Period to date)

5. Q-T-D (Quarter to date)

6. M-T-D (Month to date)

7. W-T-D (Week to date)

8. D-T-D (Day to date)

9. QTD 10 feb (1st jan to 10th feb)

There are only two Dynamic Time Series Members in active mode.

For Example

Q-T-D (Quarter to date) you can use dynamic time series to navigate report.

H-T-D (History to date)

If you want to add one more Dynamic time series member in year dimension. You have to update outline till that level. You can add only those time series which are available in outline.

First update outline structure of year dimension.

Then you can use W-T-D time series set generation.

Account Dimension

Account dimension keeps all detailed information related to account. There is one imitation that you can’t keep tow dimension type as account in a cube.

Features a account type

1. Variance Reporting Expense

2. Time Balance

3. Skipping

Skip option works only if time balance is in active mode. Time balance works only if there is at least one time dimension defined. Here in below example skipping missing and zeros data.

If new custom dimension creates as then it will show error. GHR is custom dimension and tagged as account.

While verifying it shows below errors.

Error says that there are two dimension tagged as account.

Country dimension type

A dimension tagged as a Country dimension allows you to analyze your data across multiple countries, if you desire. You can also set the currency for each country to get true and current financial data analysis for each local market defined in your database.

Country Dimension

You can customize dimension type as country. When you can tag market dimension as country dimension it will show market as country.