Oracle BI EE 10.1.3.4.1 and Essbase Connectivity – Fragmentation on Essbase Sources
In our BI EE – Essbase session in OOW, we covered 2 important use cases in bringing BI EE and Essbase together. They are
- Horizontal Federation
- Vertical Federation
There is one more use case that only BI EE as a reporting tool can do on an Essbase source(not possible in Smartview or any other Hyperion reporting toolsets). For example, lets assume that we have 2 sources. One is an Essbase source and the other is a relational source. Assume that from a reporting perspective, the numbers from Essbase and the relational source would have to roll up together i.e current data from Essbase and the historical from relational source. In such cases wherein the dimensions, measures etc match vis-a-vis across sources, BI EE can combine the data from both the sources together in its own memory.
To demonstrate this, lets start with SH sales cube. Assume that SH Essbase database contains valid data from the Year 1999 and above, and the relational SH database schema has valid data for the years 1998 and 1999. Year 1999 has data from both relational as well as Essbase cube. In such cases, in-memory roll ups can be done in the BI Server memory by setting up fragmentation. So, we will start with creating the BMM layer directly from the Essbase cube first.
Then we need to map every logical table with the corresponding relational source including the Sales Fact Logical table.
Then in each of the relational logical table sources map each Essbase Generation to the corresponding database column
Then in the Sales logical fact table, for each source (both Essbase and Relational Sources) setup fragmentation as shown below.
Then to enforce the corresponding Year filters add filters in both the Essbase and relational sources of the Times dimension logical table as shown below
If you now go to Answers and create a report on Amount sold for every channel in all the Quarters you should now see data from both the relational as well as Essbase sources. For the year 1999, both Essbase and relational data will be summed up by the BI Server in its own memory. But for 1998 data will be picked up from the relational source and for 2000 data will be picked from the Essbase source
If you look at the actual queries that were fired by the BI Server, you would notice 2 separate queries (1 SQL and the other would be MDX)
And if you bump up the log-levels, you can notice the in-memory Union-all being performed by the BI Server
This is another very interesting use-case that combines the features of BI EE and Essbase. The same approach can be used for combining multiple Essbase sources, a single Essbase source with Multiple MDX queries etc.