Oracle BI EE 11g – BI Server new features

As Mark has shown in the previous 11g related blog entries, BI EE 11g introduces a lot of new features across the entire spectrum of BI EE. If you had got a chance to look at the list of new features, you can realize that significant amount of effort has been done in R&D as well as in the development. Some of the new features are quite path breaking innovations and some of them are outright customer requirements. I will start my 11g postings with the complete list (relevant) of new features that have been introduced in the BI EE, starting with the component that is closer to my heart i.e. the BI Server.

Majority of the new features introduced in the BI EE stack (in Answers, Scorecards) can be related to a new feature in the BI Server. So, if you are just now starting with 11g, I would recommend going through the complete feature set in BI Server before moving on to the other components. The list of new features (along with changes to existing features) are given below

1. Support for SELECT_PHYSICAL commands

This is one feature that truly opens up the entire BI Server to external tools/applications. Till 10g, the biggest drawback with any external tool interfacing with BI Server through ODBC had to fire Logical SQL. Logical SQL though is similar to ANSI SQL; there are quite a few operations that are completely different from ANSI SQL. For example, we can have a Logical SQL with SUM operation in the select but without a GROUP BY. So, any external tool had to know the Logical SQL constructs to interface with BI EE. But with 11g, that is not needed anymore. SELECT_PHYSICAL commands directly can bypass the BMM layer and do normal SQL (close to ANSI SQL) queries on the Physical layer objects.

The second biggest advantage is one can do a SELECT_PHYSICAL on a relational table and any other data source like Essbase & join them together. So, it’s not necessary anymore to go through the BMM layer for leveraging the true in-memory joining capabilities of BI Server. I will cover this in detail in another blog post.

2. Support for Lookups & Removal of Bridge Tables

One significant change in the BMM layer of BI EE is there is no more an option to treat a logical table as a Bridge Table. It is now recommended to model many to many joins in the LTS using physical bridge tables (which is what customers in 10g were doing anyway). So this is a welcome change.

The other significant change is the ability to mark any logical table as a Lookup table. A lookup table does not need to have relationship with any other table. BI EE now supports 2 kinds of Lookup operations

1. SPARSE Lookup – This is more like a Left Outer joins to the driving table. If you have used ETL tools like Informatica, the terminology of lookup is very similar. A Sparse lookup assumes that the lookup does not contain values for all the entries in the main table. Hence this would result in a left outer join
2. DENSE Lookup – This is more like an equi join between the driving table and the lookup table. This assumes that for every record in the main table there is a corresponding record in the lookup table

This lookup operation can be pushed completely to the database or can be completely done in the memory of the BI Server. The exact syntax etc will be covered in a separate blog post.

3. BI Server is now Truly in-memory

BI Server can now do operations in the RAM of a server. So, it’s becoming more like an in-memory database that can do joins on request across data sources.

4. Enhanced Caching

BI Server can now cache intermediary queries. For example, if a report requires 3 different queries to be fired separately, then BI Server can now cache each one of them separately. This becomes all the more relevant if you use lookup tables frequently.

5. Deferring Session Variables & BI Server based init blocks

One of the biggest problems in 10g was every session variable that was initialized using init blocks will be fired during the authentication process and hence can significantly increase login times. In 11g, there is now a new feature that allows the init blocks to be deferred for later execution i.e. init blocks will be fired only when variables are accessed in answers.

Also, now one can directly fire queries against BI Server to populate init blocks thereby benefitting from the advantages provided by the BI Server (like avoiding a round trip to the database just for populating a simple session variable)

6. Aggregate persistence wizard can automatically create indexes.

7. Support for Oracle RPAS, FMW View Objects, HFM as a data source

This is one significant addition in the BI EE 11g release. BI EE can now report directly on ADF 11g view objects. So, it’s quite easy to call any external application view objects like Ebusiness Suite etc. For this to work, there is a set of configuration that we need to do on the BI Server.

Also 11g now supports HFM and Oracle RPAS data sources.

8. Change in Behavior when RPD upgrades happen

There are quite a few modeling behavior changes that we have to consider while doing an upgrade. Some of the important ones are listed below

a. Any level based measure assigned to the detail level of a dimension will not result in repeating rows when the report is at a higher grain (when compared with the detail level). If the report contains normal attribute columns at upper levels, then the detail level aggregation will be ignored (normal aggregation will happed). If the report contains hierarchical columns, then such measures will produce null values at higher grain.

b. Ordering of LTS determined the query path in 10g. In 11g, this determined by the Priority Order set at the LTS level.

If you have a highly customized repository that depends on level assignments and LTS switching, I would recommend devoting more time in understanding the generated sql queries as there can be differences across releases.

9. Parent-Child Hierarchies, Skip level & Ragged Hierarchy handling

Mark has already covered this in detail here.

10. New Functions in the RPD – CALCULATEDMEMBER, AGGREGATE AT, ISCHILD, ISPARENT, ISROOT, ISANCESTOR & ISDESCENDANT, PERIODROLLING, EVALUATE_ANALYTIC

CALCULATEDMEMBER – This function provides an ability to generate custom calculated members within a hierarchy. For example, it is possible to create a calculated member from 2 members at 2 different levels in a hierarchy.

AGGREGATE AT – This function provides the same functionality as a level based measure i.e. the filters applied on the dimension to which the measure is aggregated at will not be applied to that measure.

Hierarchical Functions – BI EE 11g now supports hierarchical functions like ISROOT, ISCHILD etc that can be used to traverse a parent-child hierarchy.

PERIODROLLING – This is a new time-series function that can be used to do rolling time series based analysis. All time series functions are now supported directly from Answers.

EVALUATE_ANALYTIC – This is a new Evaluate function that can be used function ship Oracle database analytic functions.

11. Double Column support for members. Now it is possible to filter on the IDs when an end user chooses the description.

This is one feature that I am pretty sure everyone expects by default in a reporting tool i.e. the ability to pass IDs when a description is chosen in a prompt. But the way it has been implemented in BI EE actually makes it useful for a lot of applications than just description/id switch. This can be put to use for multi language applications where the descriptions can be any language but the id remains the same.

12. Equalize RPDs as part of the merge – One big issue in 10g while merging RPDs was the fact that the equalization process had to be done externally to make sure the ids were consistent. Now in 11g, Equalizing can be done as part of the merge itself.

13. Security has changed significantly. Users/Groups will not be stored within the repository anymore. To edit/assign users/groups to different objects in offline mode, atleast one property of such groups will have to be modified

14. RPD Compression – There will be a significant difference in the size of the RPDs in 11g release due to a new compression feature that is enabled by default. Also, now that users/groups are no longer stored in the repository that will further add to the reduction in size.

15. Import of metadata from the connection pool directly.

16. Ability to control writeback in the RPD and support for presentation layer hierarchies

17. Patching of Repositories - BI EE 11g now more variation of merge for doing incremental migration. This uses the concept of merge and generates incremental XML patch files which can then be applied on to the repository that needs to be patched.

18. Ability to hide Level Based Measures while browsing a hierarchy.

19. A new utility has been introduced to Prune the repository of unused objects. This can be very useful for large repositories.

20. Support for BLOB/CLOB columns in the repository

21. Ability to push measures within GROUP BY operation - Controlled from UI

22. Support for Standby databases in the Physical Layer

23. Multi-User Development has been enhanced significantly.

24. Support for Vertical & Horizontal Clustering.

25. Finally, the repository downgrade utility nqgenoldverrpd is back as part of the software binary. This utility was present in the 10.1.3.2.1 version of BI EE but was removed in the later releases. So, in the later releases, one had to apply workarounds like the one that i had covered here. This utility will be very handy when working across multiple releases. But this utility cannot downgrade a 11g repository to a 10g repository. It can only downgrade to intermediary releases.