OBIEE 11g - Prompts & Hierarchical Prompts

Hierarchical Prompts : 

• Hierarchical prompts generate selection steps. This impact the report layout as it includes the members that must be shown in the report.

Prompts : 

• Normal prompts generate filters. Filters do not impact the report layout but only the data retrieved from the database.

Hierarchical prompt:

Normal Prompt :

Note : Selection steps are not filters. Hierarchical prompts do not behave like normal prompts. Choose wisely.

Difference Between Filter & Selection Step

All,

Applying filters in reports may seem similar to selection steps.
see bellow image

Filters:
• Are always applied on all views.

Selection Steps:

• Are applied only if the corresponding column is included in the view.
• May generate additional logical and physical queries.

Look at bellow screen shots :

But see what happens when columns are removed from tables -

Next Time ..

Difference Between Group By And Calculated Item

                                                        Group by and Calculated Items

It is important to understand the differences between two types of selection steps: groups and calculated items.

Performance considerations - 

• Calculated items are computed on presentation server. They are executed on the (normally small) result set retrieved from BI Server. Usually they do not have any impact on performance.

• Groups are computed on the database. They generate additional logical and physical queries. They have a significant impact on resources required on the database, and therefore on global Performance.

Functionality perspective - 

• Calculated items formula are exactly applied on result set as they are. Aggregation rules used to compute the metrics on BI Server are not considered.
• Groups generate a query with a filter based on members selected. Aggregation rules are applied on BI Server as usual.

Next Time ..

Canonical Time Dimension in OBIEE

Canonical Time Dimension :

Each Business Model should include a main time dimension connected to almost all fact tables. This is necessary for reports that includes multiple facts. It is also much easier for end-users than having a time dimension per fact table.

OBIEE - Mini-Dimensions

Mini-Dimensions :

1. Mini-dimension tables include combinations of the most queried attributes of their parent dimensions.
2. They must be small compared to the parent dimension,so they can include only columns that have a relatively small number of distinct values.
   

Note : Mini-dimensions are joined both to main fact table and to

aggregate tables.

Used to : 

• They improve query performance because BI Server will often use this small table instead of the big parent dimension.

• They increase the usage of aggregate tables. Due to the level of aggregation, aggregate tables cannot be joined to the parent dimension. But they can be joined to the mini-dimension instead. It allows reports to use the aggregate table even if they use some columns from the corresponding dimension.

NeXt Time ..