Rittman Mead Consulting

Just a quick remind for anyone who’ll be attending the UKOUG Technology and EBS Conference at the Birmingham ICC next week. We’ll be exhibiting at Stand 50, and will also be delivering the following sessions:

• “Oracle BI EE and Essbase Integration Step-by-Step”, Monday 14:45 – 15:45, presented by myself and Venkat Janakiraman
• “Oracle Warehouse Builder 11gR2 New Features”, Monday 16:15 – 17:00, presented by myself
• “Under the covers of ODI and the Oracle BI Applications”, Tuesday 14.30 – 15.30, presented by myself
• “Oracle BI Enterprise Edition Masterclass”, Wednesday 13.50 – 15.50, presented by myself, Venkat, Adrian Ward and Borkur

This year at the Masterclass, as well as tackling the basics of modeling and reporting, we’ll be tackling four of the questions that most often come up from our customers:

• How do we model non-dimensional (OLTP) sources using the BI Administration tool;
• How do we join together fact tables that do not have the same dimensionality;
• How do we model single-table sources, and
• How do we (safely) migrate OBIEE projects from one environment to the other.

Although OBIEE 10g is a fairly mature technology now, these questions always come up and it seems that no-one is too sure about how to approach them. We’ll deliver a session on this (together with a general update on the product) and try and tackle them for you.

We’re also running a BI “Fringe Event” on the Tuesday night at the Pitcher and Piano from 6pm – 9pm, where we’ll put some money behind the bar, lay on some nibbles and try and get all the BI speakers, delegates and users together for an informal get together. We’ll be inviting all those that came to the BI Forum in May and the Training Days in October as a kind of “reunion”, but we’d be happy to have anyone along with something interesting to say on Oracle BI. If you want to come along, please send me an email or come along to Stand 50 so we can get an idea on numbers. Other than that, see you all in Birmingham in a few weeks time.

If you had attended our training days event or one of our Open World sessions, you will have noticed that we had covered the various aspects of integration between Essbase and relational sources using BI EE. The presentations are available here. One of the scenarios that we had not covered was the ability to display relational attributes along with Essbase data. It is very similar to the Horizontal Fragmentation technique we had covered in the training. But there are significant differences which i thought made sense to cover in a separate blog entry.

For example, lets assume that we have the entire SH schema loaded into Essbase. In many cases, not all the attributes of a dimension are loaded into Essbase for a couple of reasons.

1. Load & Retrieval Performance
2. Cube size

If you look at the Essbase outline below, for the product dimension we have just loaded the primary hierarchy.

As you see this outline does not have all the Product attributes like Product Price, Product Pack Size etc. In many cases, such attributes might be maintained in relational sources but may never be loaded inside an Essbase outline. But from a reporting standpoint, we might still need to display these attributes in some cases as shown below

If you notice, Quantity is an Essbase measure and PROD_LIST_PRICE is a relational attribute of a product. The question is how do we model this in the repository in such a way that there is least impact on the queries generated. There are couple of approaches that we can take to model this. I will cover one such approach today.

This modeling technique leverages the concept of conforming dimensions. We start first by including the relational product source table in the Essbase Product dimension as shown below

Then we create another custom logical table called Product Attributes. In this logical table we we can include all those attributes that do not exist in Essbase. This logical table will be modeled as a separate dimension.

Ensure that the source of this new logical table has the same relational table source as the source that we added in the Product dimension. Now create a dummy Fact logical table called Fact-Products which will enforce the conforming dimension join.

The source for this fact logical table will be the same Products relational source. Now, create complex joins as shown below.

For each fact measure in the Essbase source map it to the total level of the Product – Attributes dimension as shown below. This will ensure that BI Server will combine both the sources together in its own memory.

Now while creating a report we need to ensure that we bring in measures from Essbase as well as our dummy fact. The dummy fact enforces BI Server level join at the product level.

If you look at the SQL queries, you will notice that BI Server will fire 2 separate queries. But the joins across the sources will be through the conforming product dimension.

   T2273.PROD_CATEGORY as c2,
     T2273.PROD_SUBCATEGORY as c3,
     T2273.PROD_NAME as c4,
     T2273.PROD_LIST_PRICE as c5,
     sum(1) as c6
from
     PRODUCTS T2273
where  ( T2273.PROD_SUBCATEGORY = 'Accessories' )
group by T2273.PROD_CATEGORY, T2273.PROD_LIST_PRICE, T2273.PROD_NAME, T2273.PROD_SUBCATEGORY, T2273.PROD_TOTAL
order by c1, c2, c3, c4

With
  set [Product4]  as '{[Product].[Accessories]}'
  set [Product5]  as 'Generate({[Product4]}, Descendants([Product].currentmember, [Product].Generations(5),SELF), ALL)'
select
  { [Measures].[Quantity]
  } on columns,
  NON EMPTY {{[Product5]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [SH.SH]

This shows the capability of BI Server in modeling multiple data source scenarios. If you require other dimensions to be part of the report then the dummy fact will have to assigned to the Total level of all the other dimensions (effectively Total levels need to be created in each dimension). For example, the Promotion dimension is shown below

We’re organizing a drinks reception at the Pitcher and Piano Bar on the Tuesday evening of the UKOUG Tech and EBS Conference, which is open to all BI & DW delegates, speakers and partners who’d like to come along. We’ll be there from 6pm to 9pm on Tuesday, 1st December, and we thought it’d be a good chance for anyone interested in BI and data warehousing to get together and share a few stories.

Attendance is free but it’d be good if you can let us know if you’re going to come along. The Pitcher and Piano is in Brindley Place, just across the road from the ICC, and we’ve reserved an area so just look out for the signs. Drop me a line if you plan to come along, or leave a comment on this blog post, and hopefully we’ll see you in Birmingham.

One intriguing or not-so obvious feature of BI EE is the way it does its Sub-Totals & Pivot Table calculations. As with any reporting tool, we expect almost every part of a report to be pushed back to the database in the form of native SQL. But there are certain cases wherein we cannot push an entire report in the form of SQL (for example Sub-Totals & Pivot table aggregations). In such cases, BI Server does these calculations in its own memory. Lets look at what this “in-memory calculation” means and how BI Server treats totals & Pivot table calculations. Since this is not documented anywhere, this behavior can be different across different releases (though i think it will work the same in all 10.1.3.* releases)

Sub-Totals:

To demonstrate how Sub-Totals work, lets start with a very simple report shown below

As you see, this table view report has a Grand Total. The question is how does BI Server create this Total. Does it bring the entire data into BI Server memory and then does the sub-total or does it push that back in SQL or does it write to any temporary file.

To understand this, lets start with looking at the logical SQL in the advanced tab of this report.

SELECT
Products.PROD_CATEGORY saw_0,
Channels.CHANNEL_DESC saw_1,
Sales.AMOUNT_SOLD saw_2
FROM
"SH - Training Days - Relational"
ORDER BY saw_0, saw_1

As you see this logical SQL does not actually represent the true logical SQL since the aggregation or the total part of the query is not anywhere in this SQL. So, to get the exact SQL we have to go to the Manage->Sessions & extract the logical SQL

SELECT
Products.PROD_CATEGORY saw_0,
Channels.CHANNEL_DESC saw_1,
Sales.AMOUNT_SOLD saw_2,
REPORT_SUM(saw_2 BY )
FROM "SH - Training Days - Relational"
ORDER BY saw_0, saw_1

If you notice, there will be a new function called REPORT_SUM which will be passed to the logical SQL. REPORT_SUM is an aggregation function that BI Server uses to calculate the total (since we had chosen Report Based Total whenever applicable in the table view). One good thing is we can take the exact SQL and fire it in the logical SQL window

As you see BI Server calculates the total as a column based total. There are quite a few functions like REPORT_SUM. I will list some of the common ones here

1. SUM

2. AGGREGATE

3. MIN

4. MAX

5. COUNT

6. COUNTDISTINCT

After calculating the column based total, BI Server will get the first record value in the Report Sum column and display it as Sub-Total. Now that we know what BI Server does for calculating the sub-totals in a table view, lets look at how the BI Server pushes the REPORT_SUM function. If you look at the physical query for the above logical SQL, you will notice that the report level total is not pushed back to the database. So, the question is how BI EE does this total. To determine this lets look at the {OracleBIData}/tmp folder. You will notice that for every query that has a sub-total (with database cache and presentation services cache disabled) BI EE will create temporary files in this tmp directory. The writing of temp files to this directory is governed by 4 settings in the NQSConfig.ini

WORK_DIRECTORY_PATHS	=	"C:\Oracle\OracleBIData\tmp";
SORT_MEMORY_SIZE = 4 MB ;
SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
VIRTUAL_TABLE_PAGE_SIZE = 128 KB ;

So, when we created the above report, BI Server had created a set of temp files in the above directory. These tmp files are actually similar to the cache files but are stored seperately. But how they are handled is unfortunately not documented (though we can arrive at certain conclusions by trying various scenarios). In some complex reports, when the sub-total calculation involves a lot of intermediate rows, you will notice a sizeable increase in the size of the tmp files

Pivot Tables:

Pivot table is probably the most widely used Answers component. Though widely used, many a times it can eventually lead to performance bottlenecks due to a lot of in-memory calculations. Most Pivot calculations are done in the BI Server memory and are not pushed back to the database(though we can control where it is executed to an extent). As a generic rule, in any implementation, if the repository is correctly designed, almost 90 to 95% of the reports can be achieved using normal table views(unless all the reports require a specific feature like Sub-Total at the top etc). The most common reason why pivot table is quite popular is the fact that it offers flexibility to end users in slicing/dicing the data. It is treated as an alternative to a multi-dimensional reporting solution.Pivot table can sometimes hog the temp space so much that even cache writing process might fail due to memory allocation errors. For example, lets take the above report and convert it into a pivot table as shown below

If you notice, all i have done is i have moved a couple of columns to the Excluded section of the Pivot table. If you look at the Physical query, the SQL will still contain Product Id and Channel Desc even though they are excluded.

select T24112.PROD_ID as c2,
     T24112.PROD_CATEGORY as c4,
     T26412.CHANNEL_ID as c6,
     sum(T24170.AMOUNT_SOLD) as c8,
     sum(T24170.QUANTITY_SOLD) as c9
from
     CHANNELS T26412,
     PRODUCTS T24112,
     SALES T24170
where  ( T24112.PROD_ID = T24170.PROD_ID and T24170.CHANNEL_ID = T26412.CHANNEL_ID )
group by T24112.PROD_CATEGORY, T24112.PROD_ID, T24170.CHANNEL_ID, T26412.CHANNEL_ID
order by c6

So what this means is BI Server will do all the aggregations in its own memory or by writing to tmp files. If we look at the tmp file directory

As you see, a sudden 59KB file has come into the tmp file directory due to the amount of custom calculations that the BI Server has to do in its memory. The size of the files directly are related to the amount of calculations & the number of cells retrieved. So, whenever you face an issue like a Write to Tmp directory error etc, then the first place to look at is how much of calculations are being done by the BI Server. Also, as much as possible try to reduce the amount of calculations to be done by the pivot table. As more users start accessing the system, more will the number of tmp files and hence more will be the I/O. This is something to take notice of while creating Pivot Table reports.

In the last blog entry i had shown a simple scenario of creating a BMM layer for one physical table. In today’s entry i will be covering a practical use case of creating a BMM layer from one physical table. This is a very interesting use case and can be used to demonstrate the reporting capabilities of BI EE.

Any parent body of a sporting event involving 2 teams/individuals like Tennis/Football etc generally collects a plethora of statistics. In fact stats are more important in a sporting event directly (for placing bets, buying a team, Potential Sponsors, Player analyzing weakness of opponent etc) than any BI implementation for a business(direct impact is more obvious in sports). The stats from a sporting event has immediate impact and actually makes more sense for any person since the end user looking at the stats has more understanding of the game (i always feel that we can relate more to a sporting event than the functioning of a business, but that’s just me ).

Stats for any game involving 2 teams/individuals are collected at the game level. For example, if you consider tennis, the stats like number of shots, number of unforced errors , number of forced errors, Winning Points, Losing Points, set scores etc made by both the individuals are recorded as attributes to the game in a single row. For example, if you consider the screenshot below

this contains actual data of all the games from 2007 in Men’s ATP tennis (i had to choose tennis since i thought that’s the game everyone can relate to). If you notice, every row corresponds to a game played by the winner & loser of the game. Typical stats are analyzed at a player level. For example, a major shoe manufacturer, before sponsoring a tennis player,  would like to know the number of games won & lost by the player in the most commonly played surface (hard courts) for example. But if you look at the way the data is maintained, the KPIs for 2 players are maintained in the same row (winner as well as the loser). Writing reports even in plain SQL can be tricky since either the query has to involve multiple Unions or sub-query joins. All we have is a single table but with a rich set of stats that can be analyzed in multiple ways. The even more trickier part is in modeling this for reporting in BI EE.

For modeling this, we need to determine how any person would be analyzing the stats. As with any game, we always have our favorite players and not so favorite ones. And typically we want to analyze how each one of them performed against each other over a period of time. Also there are stats that we need to look at by not comparing with any other individual like number of matches played in a year, win percentage, loss percentage etc. So there are 3 kinds of stats

1. Metrics when your favorite player is the winner – Example: Number of Matches Won
2. Metrics when your favorite player is the loser – Example: Number of Matches Lost
3. Metrics when your favorite player is the winner as well as the loser – Example: Number of Matches Played

As a first step we start with determining the logical tables that we will be needing in our model. They are

1. Player Dimension – This logical table will contain the following attributes(screenshot). The winner & loser have been transformed to Favorite & Opponent. They do not map 1:1 though. We will be seeing how we map the Favorite & Opponents from Winner & Loser attributes later.

2. Winner Facts
3. Loser Facts
4. All Facts

So, the idea is we enable a capability for any user to analyze the stats (Matches Won, Lost & Played) for their favorite players. But if you remember our data had 2 set of KPIs in a single row. That is our favorite player can be a winner as well as a loser. To accommodate this we create a couple of aliases in the Physical layer. One for Wins and the other for Losses.

There will not be any physical layer joins across these tables/aliases. The aliases that we have created above will act as 2 separate tables providing the 2 sets of KPIs in a single row. Since our perspective of analysis is FAVORITE & OPPONENT, we need to ensure that both the Winner as well as the loser are mapped to both FAVORITE & OPPONENT. So, we start with mapping the dimension to both the aliases. ATP-Winner alias will map Winner to Favorite & Loser to Opponent.

ATP-Loser alias will map Loser to Favorite & Winner to Opponent. Also the other attribute stats like Set Points, Winner points etc will get swapped

By doing this we make the Dimension logical table to have the universal information about a player & his opponent. It will take a while to understand this as this is the key in this modeling scenario. Next we map the individual logical table aliases to their corresponding Fact Tables.

Not only this, we can use the same approach to provide more than one perspective of the data. For example, if you are analyzing football then you can give a Home/Away perspective in addition to Winner/Loser perspective. Also, the same approach above can be used for games where more than 2 teams are involved in a single game. For example, Formula 1, Athletics etc.

To test this, we start with Analyzing Top 5 Players Win record in the last 3 years

And if you look at the query generated, it will contain a universal perspective of all the 5 players above as Winners. So, only one Alias will be used.

select T30942.WINNER as c1,
     T30942.YEAR as c2,
     sum(case  when not T30942.LOSER is null then 1 else 0 end ) as c3
from
     ATP T30942 /* ATP - Winner */
where  ( T30942.WINNER in ('Del Potro J.M.', 'Djokovic N.', 'Federer R.', 'Murray A.', 'Nadal R.') )
group by T30942.WINNER, T30942.YEAR
order by c1, c2

Lets include the Loss Statistics as well and see the resulting query.

WITH
SAWITH0 AS (select sum(case  when not T30942.LOSER is null then 1 else 0 end ) as c1,
     T30942.WINNER as c2,
     T30942.YEAR as c3
from
     ATP T30942 /* ATP - Winner */
where  ( T30942.WINNER in ('Del Potro J.M.', 'Djokovic N.', 'Federer R.', 'Murray A.', 'Nadal R.') )
group by T30942.WINNER, T30942.YEAR),
SAWITH1 AS (select sum(case  when not T30972.LOSER is null then 1 else 0 end ) as c1,
     T30972.LOSER as c2,
     T30972.YEAR as c3
from
     ATP T30972 /* ATP - Loser */
where  ( T30972.LOSER in ('Del Potro J.M.', 'Djokovic N.', 'Federer R.', 'Murray A.', 'Nadal R.') )
group by T30972.LOSER, T30972.YEAR)
select distinct case  when SAWITH1.c2 is not null then SAWITH1.c2 when SAWITH0.c2 is not null then SAWITH0.c2 end  as c1,
     case  when SAWITH1.c3 is not null then SAWITH1.c3 when SAWITH0.c3 is not null then SAWITH0.c3 end  as c2,
     SAWITH0.c1 as c3,
     SAWITH1.c1 as c4
from
     SAWITH0 full outer join SAWITH1 On nvl(SAWITH0.c2 , 'q') = nvl(SAWITH1.c2 , 'q') and nvl(SAWITH0.c2 , 'z') = nvl(SAWITH1.c2 , 'z') and nvl(SAWITH0.c3 , 'q') = nvl(SAWITH1.c3 , 'q') and nvl(SAWITH0.c3 , 'z') = nvl(SAWITH1.c3 , 'z')
order by c1, c2

As you see BI EE will automatically make a sub-query join across both the aliases since we have measures coming in from 2 different fact tables. The join is done as both the fact measures conform to the common dimension. There are quite a few other interesting stats that one can derive out of this. Like for example, we would like to know the list of matches that Federer has lost in the last year and also the opponents & the courts.

Interestingly he has lost to a total of 9 players. But Nadal & Murray have beaten him quite a few times more than other players. So, lets narrow this down further to see where Federer lost to Nadal & Murray

There are lots of other stats that we can accomplish out of this. I will stop here & will let FIFA, ATP etc notice this . Interestingly, in any major website there is not a lot of stats that are open for public. Only if we could have a live Web-Service giving us the results of each game & their stats, we can have even more interesting results for other sports (like what makes Usain Bolt faster than any other athlete!!! etc). But the point is BI EE can do these kind of statistical reporting pretty easily if modeled correctly in the repository.

It’s just a few weeks until the conference highlight of the year for Rittman Mead, the UKOUG Technology and EBS Conference and Exhibition in Birmingham. In previous years the UKOUG has tried to bring everyone together for this main event at the Birmingham ICC, but as Oracle has acquired so many companies it became impossible to run everything in one week, and so this year the event focuses on just technology (including BI and Data Warehousing) and Oracle E-Business Suite.

This year we will be at Stand 50, just next door to where we were last year. This year we’ll again have our famous Rittman Mead beer, we’ve got some exciting demos of OBIEE, OWB and the EPM Suite, and we’ll be joined over the three days by just about everyone from Rittman Mead in the UK. Last year we had the six of us, now we’re up to eleven consultants plus Chris Raby who handles our business development.

This year we will be delivering three conference sessions:

• “Oracle BI EE and Essbase Integration Step-by-Step”, Monday 14:45 – 15:45, presented by myself and Venkat Janakiraman
• “Under the covers of ODI and the Oracle BI Applications”, Tuesday 14.30 – 15.30, presented by myself
• “Oracle BI Enterprise Edition Masterclass”, Wednesday 13.50 – 15.50, presented by myself, Venkat, Adrian Ward and Borkur

This year at the Masterclass, as well as tackling the basics of modeling and reporting, we’ll be tackling three of the questions that most often come up from our customers: How do we model non-dimensional (OLTP) sources using the BI Administration tool; How do we join together fact tables that do not have the same dimensionality; and how do we (safely) migrate OBIEE projects from one environment to the other. Although OBIEE 10g is a fairly mature technology now, these questions always come up and it seems that no-one is too sure about how to approach them. We’ll deliver a session on this (together with a general update on the product) and try and tackle them for you.

We’re also running a BI “Fringe Event” on the Tuesday night at the Pitcher and Piano from 6pm – 9pm, where we’ll put some money behind the bar, lay on some nibbles and try and get all the BI speakers, delegates and users together for an informal get together. We’ll be inviting all those that came to the BI Forum in May and the Training Days in October as a kind of “reunion”, but we’d be happy to have anyone along with something interesting to say on Oracle BI. If you want to come along, please send me an email or add a comment so we can get an idea on numbers. Other than that, see you all in Birmingham in a few weeks time.

One of the best ways to understand the BI EE repository design is to start designing the Business Model Layer for a single Physical table. Any person who is new to BI EE will always start with testing the repository for a single physical table. This is where BI EE can be quite confusing for people who are crossing over from the realm of Business Objects, Cognos, Discoverer etc. Apart from providing a beginner’s perspective this type of design actually provides a very good understanding of the BI EE repository modeling. Advanced form of a single table BI EE design is used in reporting on Oracle OLAP 10g SQL views. I will be covering the latter in a follow up to this blog entry.

There are quite a few ways to approach the single table design. I will cover the most commonly used one (& its importance as i have seen some implementations where un-necessary joins are made). In the approach below, i shall be using a single table called as the ORDERS table from the OE schema.

BI EE mandates the presence of Dimensions and Measures in any Business Model. But a lot of people tend to argue that in a complete OLTP or a normalized data model, it is not always possible to identify measures & dimensions. A single column can act as a measure as well as a dimension metric. For example, Max Retail Price of a product can be a product attribute (dimension metric) while analyzed along with Products. But the same metric can be used as a measure while analyzing across all the other dimensions. So, in such cases, rather than grouping them as measures & dimensions, i always tend to group various columns into 3 broad classifications

1. Aggregating Attribute (any attribute which can be aggregated like Count, Sum etc)
2. Non-Aggregating Attribute
3. Both

In almost all the cases, you will for sure find attributes matching any of the 3 criteria above. In our case, the ORDERS table has the following columns.

ORDER_ID
ORDER_DATE
ORDER_MODE
ORDER_STATUS
ORDER_TOTAL
CUSTOMER_ID
PROMOTION_ID

In this table, all the attributes apart from ORDER_TOTAL are non-aggregating. To make it a bit interesting lets make ORDER_TOTAL to be considered also as an Order Attribute (non-aggregating). So, in effect we have 6 non-aggregating attributes and one attribute that is aggregating as well as non-aggregating depending on the report context.

So, we start with creating a Business Model layer containing 2 logical tables. Aggregating & Non-Aggregating (in BI EE terminology these are Facts & Dimensions).

Each logical table will contain the columns depending on the type of logical table. BI EE requires a minimum of 2 (both aggregating and non-aggregating attributes should be part of these 2) logical tables. So we start with including all the attributes (depending on their type) and in the corresponding logical table. The BMM layer offers the flexibility of having the same physical column to be treated as a dimension non-aggregating attribute and also as an aggregating measure/fact. In our case, ORDER_TOTAL physical column will be mapped in the dimension logical table as well as the fact logical table.

And every dimension logical table requires a business primary key so that the level of detail provided by each non-aggregating dimension table can be ascertained. The other important point to note is the logical join between the 2 logical tables. A logical join (complex as well as foreign key joins) denotes 2 important points

1. That the 2 logical tables are related to one another (possible to bring 2 columns from 2 tables in a single query)
2. That the 2 logical tables are related by either an inner join, outer join or a full outer join.

The first point above is straight forward. But the 2nd point will be the most confusing part since most tools out there provide a means of specifying outer joins in the physical relationship between the tables. BI EE is probably one unique tool that pushes this kind of join relationships to the Business Model layer (which is absolutely spot on though one quirk is we cannot control of order of outer joins in a physical query yet). For many this might look a bit odd but once you start understanding how the physical queries are structured, this will start making more & more sense.

For a single table model though, we just need to specify just the join between both the logical tables along with the cardinality. If we move on to the reports, we should be able to query both the ORDER_TOTAL’s in a single report

And if you look at the SQL, you will notice that one ORDER_TOTAL is pushed as part of the GROUP BY clause and the other will be pushed as a measure within the SUM clause

select T17214.ORDER_ID as c1,
     T17214.ORDER_TOTAL as c2,
     sum(T17214.ORDER_TOTAL) as c3
from
     ORDERS T17214
group by T17214.ORDER_ID, T17214.ORDER_TOTAL
order by c1, c2

This is pretty straight forward. There is another approach that i have seen people use in the past. For a single table, developers tend to create as many aliases as the number of dimensions & fact logical tables. Then a join is made on the primary key of the table (in our case ORDER_ID). All this is done in the physical layer as shown below

Then each alias is mapped on to the logical dimension & fact tables as shown below

This will work as well. But lets create the same report as shown above and look at the physical SQL

select T29596.ORDER_ID as c1,
     T29596.ORDER_TOTAL as c2,
     sum(T29605.ORDER_TOTAL) as c3
from 

          ORDERS T29596 /* Alias - Dim Orders */  inner join
          ORDERS T29605 /* Alias - Fact Orders */  On T29596.ORDER_ID = T29605.ORDER_ID
group by T29596.ORDER_ID, T29596.ORDER_TOTAL
order by c1, c2

As you see, though the report output will be the same, the physical sql does a self-join to itself through the aliases that we created. So, this is not an approach that i will recommend for a single table design as it does not perform well(once you have more & more isolated fact tables) at-least in this context. Aliases can be very useful sometimes but when used inappropriately can result in pretty bad queries thereby causing bad performance.

Single table design can get quite complicated when dealing with monstrous views like Oracle OLAP 10g views. I will cover some of the concepts like Dimension Binning (will be useful in general for any other SQL exposed OLAP tool like Hyperoll etc) used in BI EE while going against OLAP 10g in the next blog entry.

If you’ve been keeping an eye on our progress this year, you’ll probably be aware that 2009 has been a very exciting year for us. We’ve opened an office in the USA, and earlier in the year Adrian Ward and Daniel Bosman from Majendi, and Venkat J from India, joined us to strengthen our presence in the UK and Europe. We’re at the point now where we’re on the look out for more great OBIEE consultants, and we’d be interested in hearing from anyone with exceptional OBIEE skills who’d be interested in joining our team.

We have a number of ongoing projects in London and around the UK and Europe based around OBIEE, quite often with Essbase or the BI Applications in the background. Looking specifically at OBIEE, successful candidates will typically be responsible for:

1. Converting end user requirements into system design
2. Developing an appropriate solution
3. Testing the proposed solution against the individual requirements

Tool sets you will be required to use include:

1. The OBIEE adminstration tool
2. Dashboard development tools, including answers, delivers and catalogue manager
3. Database tools such as Toad, Warehouse Builder, ODI

Other useful skills are Essbase or Oracle OLAP, Cognos, PL/SQL, ApEx and ADF.

Whilst we would prefer permanent employees we are reasonably flexible about our relationship with our consultants, and can typically start with either an employed or contract arrangement which we can discuss at a later stage if appropriate. You will need to either be based in the UK or already have the right to work here, and as our work involves a high level of customer interaction, excellent English language and written skills are a must.

If you are interested in working with us, please provide details of where and how you have done the following

1. Repository development
2. Webcat development
3. Database modelling
4. Testing

and when you provide details, could you please outline how you went about:

1. Converting user requirements
2. Creating the design
3. Developing the solution
4. Testing
5. Implementation

For successful candidates at this stage, the next step will be a technical interview. In anticipation, could you please provide in your reply your availability for a telephone interview of approximately 30 – 45 mins for the week following your application.

Rittman Mead are an equal opportunities employer and does not discriminate in terms of gender, religion, disability or ethnic background.

If you are interested, drop us a line to careers@rittmanmead.com, and one of us will be in contact by return. If you know OBIEE inside-out and want to work with the best team in the industry, get in touch now and you could be working with us by the end of the year!

ODTUG Kaleidoscope, the Oracle Developer Tools User Group annual conference which next year is running in Washington DC, has a call for papers that closes on November 10th. This year, I’m the content lead for the BI, DW and Hyperion Reporting track and together with the rest of the committee, we’re trying to pull together an exceptional agenda based around OBIEE, OWB, ODI, Discoverer and the Hyperion reporting tools. By June next year when the conference runs, the 11g versions of OBIEE and ODI should be out, and OWB11gR2 is already with us, so this will be probably the first conference where we’ll be able to talk about our experiences with the tools with them actually out on general release.

If you saw details of our BI Forum in Brighton last May, but you were based outside of Europe and couldn’t make it, this would be an excellent event to put forward a paper for, and attend in June next year. If you’re Europe-based but fancy a trip over to DC in June (or indeed from anywhere outside the States), this is a great excuse to get over and meet other BI & DW developers from North America and around the world. ODTUG is a worldwide user group that focuses solely on development tools, and the sessions we run are practical, focused on tips and techniques and led by developers like yourselves. If you’re considering putting a paper forward, do so now and we’ll hope to see you in June 2010. Also, if you want to discuss potential topics with me, just drop me a line and I’d be happy to offer advice.

In other news, you might have seen a few weeks ago a posting where I mentioned that I was standing for a position on the ODTUG board. I’m so keen on what ODTUG are doing, particularly around creating a worldwide community for BI, DW and Hyperion developers, that I decided to stand for the ODTUG election and I’m very pleased to announce that I secured a place on the board. Thank you very much to everyone who voted for me, and I’m particularly looking forward to working with the rest of the ODTUG board, and the membership, to bring more great BI and DW content to the user group. With the imminent release of OBIEE 11g, together with updates to OWB and ODI, we’re at an inflection point with the various Oracle tools and I’m really keen that ODTUG remains the premier destination for help and support around their adoption.

I’m also looking forward to working with Shyam Nath from BIWA, and Faun De Henry from OAUG, so that all of the international user groups can work together to meet the needs of developers and customers using Oracle’s BI & DW tools and applications.

If you’ve been to any of the Oracle BI Applications roadmap presentations, you may well have seen a slide that sets out “Oracle’s Integrated Analytic Solution”, which looks like this:

(from Oracle Business Intelligence Applications Roadmap, Oracle Open World 2009)

Now we all know about the ERP Analytics (Oracle BI Applications running against Oracle EBS, Peoplesoft and SAP sources) and the CRM Analytics (BI Applications against Siebel), and the EPM applications are the ex-Hyperion application such as Planning, Financial Management and Profitability Management. But what about the Industry Analytic applications: how do they work, do they share data and an architecture with the Oracle BI Applications, and what do they look like?

Unlike the regular Oracle BI Applications, which are considered horizontal applications as they are applicable to all different industries, the Oracle Vertical Analytic Applications are specific to particular industries such as energy, life sciences, automotive and insurance. The vertical that Oracle have got the most emphasis on at the moment though is financial services, following the purchase of a majority share in i-flex (now renamed Oracle Financial Services Software) and Oracle’s position now as one of the major suppliers of banking, capital and risk management software. Oracle’s aim over the past year has been to bring together the Reveleus banking BI and data warehousing software from i-flex, plus software they recently acquired through the subsequent purchase of Mantas (fraud and money-laundering prevention) and combine this with the Oracle BI Enterprise Edition platform into something now called the Oracle Financial Services Analytical Applications.

The Oracle Financial Services Analytical Applications, like EPM Suite and the BI Apps, consists of a number of modules, some of which are based on i-flex / Reveleus technology (Oracle Financial Services Funds Transfer Pricing, Profitability Management etc) but with one, Oracle Financial Services Profitability Analytics, based on OBIEE technology with an i-flex derived underlying relational data model.

Reveleus is the business intelligence framework developed by i-flex, and is based on their own front-end technology and what appears to be a Microsoft SQL Server and Analysis Services back-end. Similarly, the Mantras technology pre-dates the Oracle aquisition and again uses a mix of custom and non-Oracle technology. Going forward though, Oracle’s strategy around financial services analytic applications is to create a suite of products, based around Oracle BI EE and the Oracle Financial Services Analytical Applications Infrastructure. This framework sits within the usual storage – data sources – middleware – foundation – EPM workspace architecture, but using its own data model, and sits in the area of the architecture usually occupied by the BI Applications and EPM Suite.

Now this is where it gets interesting, as there are clear links between these vertical applications and the rest of the Oracle BI and EPM stack. Unlike the horizontal BI Applications, these vertical applications use their own industry-specific data model, with the financial services one called the “Oracle Financial Services Analytics Applications Data Model”. This is a relational data model that is used by a number of frameworks and applications to provide banking-specific analytics using shared dimensions, metadata and metrics. The application that we are interested in that uses this overall data model, and is based around OBIEE, is Oracle Financial Services Profitability Analytics, the screenshot for which is at the start of this article. Placed in their own architectural technology stack, you can see the familiar dashboards, reporting and alerts provided by OBIEE, with more complex and industry-specific calculations provided by ex i-flex technology.

Profitabiltiy Analytics uses an OBIEE semantic model that reports against summary data taken from the Oracle Financial Services Applications (ex i-flex) data model, in a similar way to the E-Business Suite materialized views that Daily Business Intelligence uses. The summaries used for Profitability Analytics are stored in the same schema as the Oracle Financial Services Applications data model, with Oracle BI Enterprise Edition reporting against just these in order to provide good query performance. There’s no DAC with this application, with aggregation scripts used instead to create the summaries used by OBIEE, and all of these need to be installed on an Oracle 9.2 or higher database. A bit of configuration is needed to define start and end dates and the instrument configuration, but as it’s designed solely to work against Oracle’s own banking data model it’s not quite as much work as setting the horizontal BI applications up.

The Profitability Analytics tables in the current release are a bit more limited than the horizontal ones, in that it’s not really designed to allow changes to historical data (which may be an appropriate restriction in banking applications), there’s no SCD2 support and a few early limitations in how the LTSs in the repository have been set up. But it’s based on OBIEE, uses the OBIEE dashboard, requests and iBots framework and there is some scope for customization, in terms of bringing in additional data and metrics from the Oracle Financial Services Applications data model rather than from “universal” or ERP/CRM sources that you can do with the BI Apps. It also uses the Market Segmentation feature of OBIEE to do market and customer analysis, and security, strangely, isn’t set up by default with the configuration manual goes through a couple of examples of using the BI Server Security Manager and Party security dimension in the semantic model.

So, the Financial Services Profitability Analytics part of the Oracle Vertical Analytical Applications looks a bit like the short-lived Fusion Analytics module, based on the DBI summaries but using OBIEE technology, before Oracle changed emphasis to the BI Applications after the CRM and ERP data models were combined. Whilst it’s extendable via the OBIEE semantic model and the limited scope for customization against the Oracle Financial Services Applications data model, it’s something that is banking-specific and something that a bank might deploy alongside the horizontal BI applications, which would in turn cover their generic applications such as GL, HR and Purchasing.

The applications from Reveleus and Mantras look interesting and cover even more narrow banking and risk-focused areas, the technology under them is a little unclear from the documents on the Oracle website (possibly because they are considered “batteries included” and based on Microsoft database and OLAP technology), and you can imagine that within Oracle there’s a big push to get Hyperion technology into this stack, particularly by extending and verticalizing existing application such as Planning and Profitability Management.

Still, it’s an interesting look into the vertical applications that Siebel, and now Oracle have produced using the OBIEE technology that we’re all using, and certainly if you were a bank, pharmaceuticals company, insurance company or whatever, there’d be quite a compelling argument to get Oracle BI across the whole enterprise, delivering the generic content using the BI Applications, industry-specific content using the Vertical Analytic Apps, Siebel covering the CRM side and other, home-built systems covered using standalone OBIEE together with ODI or OWB11gR2. Coupled with what’s happened in banking and financial services over the past couple of years, if you’re an Oracle salesman in the financial services industry there’s quite a bit of interesting stuff to talk to your customers about now.

John Paredes recently sent over a review copy of a book he’s just had published, entited “The Multidimensional Modeling Toolkit: Making Your Business Intelligence Applications Smart with Oracle OLAP” (ISBN: 978-0-9817753-0-2, OLAP World Press). Interestingly, it’s a book written about Oracle OLAP (which is itself rare) but unlike the recent Oracle Press book on OLAP and Essbase, it looks at it from the OLAP DML perspective, based on Oracle OLAP 10gR2.

As such it’s not an introduction to basic Oracle OLAP via the Analytic Workspace Manager, it’s more something that could have been written in the days of Express and takes a more low-level, philosophical approach to OLAP with a particular emphasis on multi-dimensional concepts and the multi-dimensional building blocks that make up an Analytic Workspace. Whilst illustrated using recent front-end tools such as the Data Viewer within AWM, it eschews the approach taken by Oracle to make OLAP data behave as much like relational data as possible, an instead takes you straight into OLAP DML and the building blocks of a mult-dimensional database. The book does start off with instructions on how to build a cube using AWM, but even in this chapter its not long before we’re into the OLAP Worksheet (the OLAP DML front-end that’s hidden in AWM) where we’re using OLAP DML to add members to dimensions and query the metadata.

As such, it’s a brilliant book for anyone who’s used Analytic Workspace Manager and tools like Discoverer for OLAP and even OBIEE, and has wondered just how the engine within Oracle OLAP actually works. I’ve waited years for a book on OLAP DML and Jon Paredes has produced something that must surely be a labour of love, particularly as the market for Oracle OLAP books in total must be pretty small let alone one that focuses on OLAP DML, something that Oracle are in fact de-emphasizing with the move towards the OLAP API and cube organized materialized views in Oracle OLAP 11g.

One word of warning that I would give though is that with this move towards SQL and PL/SQL-based cube definitions in Oracle 11g, it’s now impossible to create OLAP objects using OLAP DML that are then registered in the OLAP metadata used increasingly by Oracle’s development and query tools, as this metadata has from 11g moved out of the analytic workspace itself and into the standard Oracle relational data dictionary tools. You can still use this manual approach though if working with OLAP data through the OLAP_TABLE function, and I know I’ll be working through John’s book over the next few months to fill out my knowledge of OLAP DML.

So, overall, a great book and it’s great to see something that’s been produced by someone for their love of the technology, and based on Oracle OLAP DML programming which is a bit of a specialist subject these days (I doubt most Oracle developers would even have heard of it). If you’re a developer using Oracle OLAP, particularly if you’ve migrated from another technology such as Holos or TM/1 and are wondering how to get under the controls hidden by Analytic Workspace Manager, or if you’re looking to use Oracle OLAP for high-end statistical or financial analysis, this is a very interesting read and I’d encourage you to get hold of a copy. If you’ve ever wondered just how to do an allocation, a forecast or create a calculation using more than two operators and been frustrated by the simplicity of Analytic Workspace Manager, this book may turn out to be an excellent investment.

One of the issues that people often face while using BI EE is the lack of control on Multi-Select prompts. One cannot set Multi-Select prompts to a presentation variable & similarly one cannot display the multi-selected values in a report (only filter view supports the display of all the selected values). The most common requirement is to do string operations on the multi-selected values by treating them all as a single string.

The other requirement that i have seen in the past is to do String Aggregation on certain dimensional attributes. Currently BI EE does not have any specific aggregations for strings. One can theoretically use FIRST, LAST etc kind of aggregation on string columns but those are not aggregations per se. A typical string aggregation would involve concatenation of all the strings in a specific format.

Both the above requirements can be solved by a simple technique. From a requirements perspective, they are different. But from an implementation standpoint both are similar as both the requirements require BI EE to do a string concatenation. To accomplish this we shall be using the COLLECT database function that was introduced in 10g. If you are in pre-10g release, you can use the STRAGG function created by Tom Kyte. And if you somehow have upgraded to 11gR2, then you can use LISTAGG. The idea is, we push down the aggregation part of the strings to the database and then use them directly in BI EE. The example that i shall be going through today will involve a simple Mult-Select Prompt on the CHANNELS_DESC column of the CHANNELS table in the commonly used SH schema. The requirement is to display the chosen multi-select values as a column in the report as shown below

There are basically 2 ways of achieving this. I will demonstrate both of them here. Both the approaches require the aggregation to be pushed into the database. So, we start with creating a Type and a database function to convert the output of the COLLECT function to a set of strings.

create or replace type MultiSelect as table of varchar2(1000);

CREATE OR REPLACE FUNCTION MultiSelect_Pipe (p_MSP IN MultiSelect)
RETURN VARCHAR2 IS
     var_msp VARCHAR2(4000);
     var_index NUMBER;
BEGIN
var_index := p_msp.FIRST;
IF var_index IS NOT NULL THEN
	var_msp := '(''';
	WHILE var_index IS NOT NULL LOOP
		IF var_index <> 0 THEN
			var_msp := var_msp || p_msp(var_index) || ''',''';
		END IF;
		var_index := p_msp.NEXT(var_index);
	END LOOP;
	var_msp := substr(var_msp,1,length(var_msp) - 2) || ')';
END IF;
RETURN var_msp;
END MultiSelect_Pipe;

If we test the above function in SQL, we should get the output in a string concatenated form.

select distinct
multiselect_pipe(cast((collect(channel_desc) Over ()) as Multiselect))
AS Channel_desc_agg from channels

Approach 1:

This approach will directly use the above function that we created above using EVALUATE from the repository. There are some drawbacks with this approach which shall be covering later. To implement this, we need to create a new column and basically use the same function within Evaluate function in the repository as shown below

 EVALUATE('MULTISELECT_PIPE(CAST((COLLECT(%1) OVER ()) AS MULTISELECT))' AS  CHARACTER ( 1000 ),
"ORCL".""."SH"."CHANNELS"."CHANNEL_DESC")

If this column is exposed in the presentation layer, one can now use this column in the report directly. It will capture the multi-select values from the prompt as well.

And if you look at the query, you will notice that the database function gets pushed in to the sql. The filter values applied on the multi-select prompt will be honored by the database function as well.

Select
     MULTISELECT_PIPE(CAST((COLLECT(T26412.CHANNEL_DESC) OVER ()) AS MULTISELECT)) as c1,
     T26412.CHANNEL_DESC as c2,
     T24112.PROD_CATEGORY as c3,
     sum(T24170.AMOUNT_SOLD) as c4
from
     PRODUCTS T24112,
     CHANNELS T26412,
     SALES T24170
where  ( T24112.PROD_ID = T24170.PROD_ID
and T24170.CHANNEL_ID = T26412.CHANNEL_ID
and (T26412.CHANNEL_DESC in ('Direct Sales', 'Tele Sales')) )
group by T24112.PROD_CATEGORY, T26412.CHANNEL_DESC
order by c1, c2, c3

There are a couple of drawbacks with this approach. Since we are not isolating the filter on the String aggregated column, if your fact table does not have a value for a chosen prompt value, that will not be included in the output. For example, lets assume that in the multi-select prompt we are choosing 3 channel values i.e Catalog, Direct Sales and Tele Sales. There is no transaction for Catalog in the Fact table. So, the string aggregated column will show only Direct Sales and Tele Sales. This is not a true reflection of the Multi-Select prompt values.

Also, the CHANNEL_DESC_AGG column requires CHANNEL_DESC column to be part of the query as well. If not, BI EE will try to push the analytic function into the group by clause there by resulting in an error.

Approach 2:

Both the issues above can be negated by using another approach. This approach will use the same database function. But what we will be doing here is, we will be modeling our repository in such a way that BI EE will split the above the report’s single query into 2 separate queries. So we start with creating a new database connection (this is needed) and copying the CHANNELS table over to the new connection.

Then we create a physical layer join between the CHANNELS table in the original SH schema database and the CHANNELS table in the new database

In the BMM layer, we need to snowflake the Channels dimension by adding a new logical table called Channels_Agg. This new logical table will contain the EVALUATE column(same as in Approach 1) and the CHANNEL_DESC column.

Create a logical snowflaked join between Channels and Channels Agg logical tables. In the presentation layer, expose CHANNEL_DESC and CHANNEL_DESC_AGG columns from the Channel Agg logical table. Lets now test the above Multi-Select report again with the same 3 values (one missing in fact table)

As you see, the above is a true reflection of the Multi-Select prompt. It always shows the values chosen in the prompt no matter what values are present in the fact table. If you look at the SQL, you would notice 2 separate queries being fired by BI EE.

Also, this does not depend on the selection of CHANNEL_DESC column in the report.

Currently we had to use the analytic function since BI EE cannot do analytic equivalent functions directly from the repository (though one can mimic them using level based measures but we do not have complete control on the queries). Hopefully as new releases come out, we should see such capabilities in the repository itself.

I’m currently over in Finland, waiting to travel back after the OUGF Autumn Conference in Helsinki. Together with Dan Morgan, Piet de Visser and Joze Senegacnik we were providing the English-Language track, and thanks again to Heli from the user group for inviting us all over.

The event started on the Wednesday evening for us, with a visit to a Laplandish restaurant where we were all given our own personal “Kuska” cup, something you have to initiate and then keep for life. Here’s a quick clip of the Kuska ceremony, Dan and Piet are sitting just opposite me.

There’s more of the ceremony here. I also got to see the first snow of the year (for me), on the way back it began to snow quite heavily and carried on during the conference yesterday, and there’s still some outside my hotel window now. Here’s Piet, and Tiina from OUGF who helped organize the event (more photos on Flickr.)

My session was on Oracle Warehouse Builder 11gR2 New Features, and I talked about the new code template, hybrid mappings, heterogeneous sources, OBIEE integration and SOA integration features in the product. There were quite a few OWB developers in the audience but none that had tried OWB11gR2, I guess this is mostly down to it only being available currently on Linux. Certainly for me the most interesting part of this new release is the code templates feature, and I’ll be interested to see how much this gets used on new projects – we do a lot of OWB development for clients and once the Windows version (so that you can use the client tools) is available, we’re expecting to see big uptake on this. We’re also in the middle of developing new course materials on OWB11gR2 and are looking to have these available early in the New Year, again to coincide with the Windows version of the tool.

If you’re interesting in seeing the slides, I’ve uploaded them here.

So, it’s goodbye to Helsinki soon, and back to the better weather in the UK (never though I’d get to say that). Next week I’m off to Ireland for the Irish BI SIG, to talk about Essbase and OBIEE, and Hyperion Planning, then it’s off to Kenya for an engagement with a local bank. Then it’ll be heads-down and planning for Birmingham, where most of the company will be converging for the upcoming Tech and EBS UKOUG Conference, our main event of the year.

[Mark Handford from the BI Survey dropped me an email the other day, to let me know that fieldwork for the BI Survey 9 is now underway. In my opinion I'd be great if as many Oracle and Hyperion customers could take part as possible, so that we get a good idea of how adoption of the various tools is taking place, and to that end here's the invite to take part in the survey - MR]

“The BI Survey 9: The Customer Verdict

We would very much welcome your participation in ‘The BI Survey 9: The Customer Verdict’, the world’s largest survey of business intelligence (BI) and performance management (PM) users (formerly known as The OLAP Survey).

As a participant, you will:

• Receive a summary of the results from the full survey
• Be entered into a draw to win one of ten $50 Amazon vouchers
• Ensure that your experiences are included in the final analyses

To take part in the survey on-line, visit: http://digiumenterprise.com/answer?link=270-5J9MMB9M

BARC’s annual survey obtains input from a large number of organizations in order to better understand their buying decisions, the implementation cycle and the business benefits achieved.

Both business and technical users, as well as vendors and consultants, are welcome to participate. If you are answering as a consultant, please answer the questions (including the demographic questions) from your client’s perspective; we will ask you separately about your own firm.

The BI Survey has always adopted a vendor-independent stance. While vendors assist by inviting users to participate in the Survey, Business Application Research Center (BARC) – the publisher – does not accept vendor sponsorship of the Survey, and the results are analyzed and published without any vendor involvement.

You will be able to answer questions on your usage of a BI product from any vendor. Your answers will only be used anonymously, and your personal details will never be passed on to vendors or other third parties.

The survey should take about 15-20 minutes to complete

* BARC (Business Application Research Center) is a leading independent software industry analyst specializing in Data Management and Business Intelligence. For more information on BARC please visit The BARC website, www.bi-survey.com and www.BI-Verdict.com”

In the last blog entry, i had shown a very simple work around to use direct database requests in BI EE. This capability has quite a few advantages and actually provides a means wherein one can actually create reports using this, wherever BI Server directly cannot(due to inherent limitations in the connectivity). There are quite a few use cases wherein we can put this to good use. Also one point to note is that, unlike Direct Database requests in relational databases (which has huge potential risks of opening up the database), the MDX direct database requests can accept only MDX queries. So, there is less chance of someone breaking/hacking into your Essbase applications. I will cover some of the use-cases here.

Filters on Evaluate Columns:

This is something that can quite be a deal breaker/showstopper while doing an BI EE implementation on Essbase. Even though one can function ship MDX functions using EVALUATE in the repository, the major drawback is one cannot apply any filtering on these columns even using Presentation Variables. The only way one can apply any sort of filtering on such columns is to use Go URL, Session Variables and updating the Session Variables through a presentation variable. But the complexity of such reports can quite get too difficult to manage. Direct Database requests can quite easily negate this. For example, lets consider a report shown below

Its a very simple report containing all the Scenario members against all Product members for the COGS, Sales and Profit measures. The MDX for the above report is given below

With
  set [Scenario2]  as '{[Scenario].members}'
select
  { [Measures].[COGS],
    [Measures].[Sales],
    [Measures].[Profit]
  } on axis(2),
{[Product].dimension.members} on axis(1),
  NON EMPTY {{[Scenario2]}} on axis(0)
 from [Sample.Basic]

Now, if you look at either the Product  or the Scenario members, we are bringing in the entire Dimension set instead of members from a specific Generation. So, if we had designed this in the repository, we would not have been in a position to filter either on the Scenario nor on the Product All member EVALUATE column. So, to enable that lets re-write the MDX as shown below to reference 2 presentation variables. And paste this MDX in the Direct Database requests page of BI EE.

With
  set [Scenario2]  as 'Intersect({[Scenario].members},{[@{ScenVar}{Actual}]})'
select
  { [Measures].[COGS],
    [Measures].[Sales],
    [Measures].[Profit]
  } on axis(2),
Intersect({[Product].dimension.members},{[@{ProdVar}{100-10}]}) on axis(1),
  NON EMPTY {{[Scenario2]}} on axis(0)
 from [Sample.Basic]

So basically we are referencing 2 presentation variables inside the MDX query. If you create a prompt containing both the presentation variables (and a custom SQL to display all members in both the dimensions), the filters can be applied on to the report.

Switching Measure Dimensions:

The above use case was pretty straight forward at least from a report creation standpoint. But what is good about this is the fact that the end user has complete control on Measure dimension. One can have 2 reports side by side wherein the Measure dimensions are completely swapped as shown below

All we need to do in the new report is to swap the AXIS numbers and we will get a new measure dimension. The MDX for this is given below as well.

With
  set [Scenario2]  as 'Intersect({[Scenario].members},{[@{ScenVar}{Actual}]})'
select
  { [Measures].[COGS],
    [Measures].[Sales],
    [Measures].[Profit]
  } on axis(2),
Intersect({[Product].dimension.members},{[@{ProdVar}{Cola}]}) on axis(0),
  NON EMPTY {{[Scenario2]}} on axis(1)
 from [Sample.Basic]

This is exactly what we expect out of a multi-dimensional reporting tool. Ability to change the Slice axis at will is such a feature of Essbase that end users will surely want them even in BI EE. If this is a necessity (which i believe will be in many cases), Direct Database requests will be the way to go atleast for now. 11g hopefully will provide more flexibility in this regard.

Varying Attributes:

This is another nice feature that was introduced in the EPM 11 release. If you are not sure what Varying attributes in Essbase are, i have covered it before in my blog entry here. This probably is very unique to a multi-dimensional tool. This is similar to Slowly Changing Dimensions in a relational data warehouse implementation. But the difference is, in Essbase the attributes can vary based on multiple dimensions(one being time). This offers multiple perspective view of the data. Unfortunately, BI EE currently does not have native support for Varying Attributes. The only way to create a multiple perspective view of the data is to use Direct Database requests. Lets take a couple of examples for illustration. I will be using the same example used in my Varying Attributes blog entry i.e Product dimension has an attribute dimension called Caffeinated (True or False) which in turn varies over time and Market. Time is the continuous varying dimension for the Caffienated attribute dimension. Any end user who generally uses Varying attributes would like to look at the data in 2 different perspectives.

1. Reality Perspective – Current Attribute values of a product driving the output of a query. For example, a report containing all the products & its sales, that are currently sold in US which are not Caffeinated

2. Historical Perspective – Historical Attribute values of a product driving the output a query. For example, a report containing all the products & its sales, that were sold in the US market as not caffeinated in June of this year(history).

The MDX queries to get both the perspectives vary in 2 ways. The WITH clause of MDX requires a perspective specification. And also the MDX attribute function varies. Since i have covered the details of these 2 already, i will just list the MDX queries below.

WITH PERSPECTIVE (@{Month}{Jul}) for Caffeinated
SELECT
 { Qtr1, Qtr2, Qtr3, Qtr4}
ON COLUMNS,
 {WithattrEx(Caffeinated, "=", "True", ANY,
 ([New York], Jan), ([New York], Dec))}
ON ROWS
 FROM Sample.Basic

WITH PERSPECTIVE REALITY for Caffeinated
SELECT
 { Qtr1, Qtr2, Qtr3, Qtr4}
ON COLUMNS,
 {AttributeEx(Caffeinated, ANY, ([New York], @{Month}{Jul}), ([New York], Dec))}
ON ROWS
 FROM Sample.Basic

Format Strings:

This is another feature like Varying Attributes that was introduced in the EPM 11 release of Essbase. Though the title of the blog entry would have given you a feeling that Format Strings work with BI EE using Direct Database requests, unfortunately that is not the case. Format Strings do not work with BI EE. For more details on Format Strings, refer my blog entry here. BI EE does some post processing on the MDX data obtained and hence does not display the custom MDX formatted measures. The 2 screenshots below show the MDX output in EAS and the BI EE output using the same MDX

Direct Database requests, due to the current nature of BI EE – Essbase integration, can form quite an integral part of the reports that you create using this integration. If some reports are not performing well then take the MDX query of those reports and run them through a couple of checks

1. Check whether the MDX query is an efficient query hitting only the correct & necessary Essbase cell intersections

2. Take the tuned MDX query and then fire the MDX from the Direct Database Requests page. If this is fast enough and generates the right data, then there is scope for further tuning in your BI EE repository. If not, then the issue is probably you are hitting an Essbase bug or an MDX bug or there is further scope for MDX query tuning(first 2 are remote and in most cases it will be the query tuning part).

One other thing i had noticed recently is the fact that though MDX as a reporting language in Essbase is comprehensive, it is not quite there yet when you compare it with the features offered by Microsoft SSAS. In SSAS, sub-queries in MDX are supported. Also, there are some more comprehensive Formatting, intrinsic member property support etc(probably because Microsoft developed this). Essbase outline itself supports around 25 odd intrinsic member properties which we can find in the JAPI member properties of Essbase. Unfortunately, those have not been exposed yet to MDX. Having said that, to an extent, BI EE and Essbase compliment each other pretty well and it will be interesting to see how BI EE 11g handles Essbase when it comes out sometime next year.

The vast majority of customers I come across that use OBIEE run the platform on Linux or Unix. For those that run it on Windows though, what tools do you have to monitor the performance of the platform? One of our customers is in just this situation as they are running Oracle BI Standard Edition One, which is limited to Windows as a host platform, and so a tool we’d use in this situation is “Perfmon”.

Perfmon (short for Performance Monitor) is a utility that’s shipped on Windows since the days of NT4.0 and is commonly used by Windows sysadmins to keep an eye on the characteristics of their system. You can use it to monitor hundreds of different metrics, such as processor usage, memory usage and disk usage, and you can connect it to the OBIEE binaries to find out more about your BI system.

I’m running Oracle BI EE on a Windows 2003 server, and to start taking a look at this feature I start Perfmon from the start menu (Start > Settings > Control Panel > Administrative Tools > Performance), or I can just type in perfmon from the Windows command line. The utility initially comes up like this:

Perfmon in this initial state is monitoring Pages/Sec (an indication as to memory swapping), Avg. Disk Queue Length (an indication of disk I/O waits) and % Processor Time (an indication of the load on the CPU).

To start with, I”m interested in some more general indicators of the load on my system. Do add more metrics, I click on the “+” button in the middle of the application toolbar to add a new counter. This brings up a dialog that lets me initially select a Performance Object, which is typically a part of an application or a general Windows area such as as System.

I select System and then select the Processor Queue Length counter, which represents the number of threads queued and waiting for time on the CPU. If the number is more than 10 times the number of CPUs (or virtual CPUs if using VMWare) then your system is running under a normal load.

I then press Add to add the counter to the Perfmon monitor, and check the output. Perfmon has scaled up the counter by ten to fit in with the others, and I can see that apart from a brief peak, this metric is looking acceptable.

Other metrics I might be interested in include:

• Memory > Pages Input/Sec (a good indicator of whether the system is memory-bound, shows the rate at which pages are read from disk to resolve hard page fault, and should generally settle at zero)
• Physical Disk > Current Disk Queue Length > drive letter (together with the above, a good indicator of excessive disk usage. Single disks should report a figure of 2-3 or lower, divide this by the number of disks if in a RAID array. A high figure for the above metric may indicate swapping due to lack of memory, these two together typically show whether systems are RAM and I/O bound)
• Network Interface > Output Queue Length > nic card name (shows whether the network is overloaded, look for more than 2 packets waiting for transmission to indicate the possibility of a network bottleneck)

So I now have Perfmon monitoring some useful overall system metrics to let me know if the system is overloaded. But what about OBIEE specifically?

If you go back to the point where you add new counters, you’ll notice that on a system with OBIEE installed, there are a bunch of performance objects specific to the server components of OBIEE.

These are the counters that tools such as the BI Management Pack uses to monitor OBIEE performance, and you can also access them from a browser using the URL http://<machine_name>:<port>/analytics/saw.dll?perfmon

This screen gives you all the counters in one place though, so there’s probably a bit too much data here to monitor on an ongoing basis. Plugging these counters into Perfmon gives you the ability to graph and monitor them individually, and you can also plug them into the underlying Windows alerting subsystem to let you know if something has gone wrong.

So let’s take a look at some OBIEE metrics. To keep things simple, I delete the existing counters from the Perfmon window and also switch to the Histogram view, so that I can see the metrics as a vertical bar chart. I then add the following two BI Server metrics to the window:

• Oracle BI Thread Pool > Avg. Request/Second > Server
• Oracle BI Thread Pool > Average Execution Time (milliseconds) > per BI Server connection pool
• Oracle BI Thread Pool > Peak Queued Requests
• Oracle BI PS Sessions > Sessions Logged On
• Oracle BI PS Sessions > Current Sessions
• Oracle BI PS Sessions > Active Sessions

All of which give me an idea of the general load on the system. I could add more detailed counters on cache usage and so on, but for now my Perfmon output looks like this:

One other neat feature of Perfmon is alerts. By right-clickin on the Performance Logs and Alerts > Alerts section within the Perfmon tree view, I can define a new Windows alert against these metrics.

I pick the Oracle BI PS Sessions > Maximum Sessions counter and set the alert to tell me if more than one session is running.

This process then runs automatically, and logs alerts in the Windows alert log, so that you can see them in the Event Viewer under Control Panel > Administration Tools. In this case, as I’ve set the alert threshold so low, running a couple of more browser sessions triggers the alert.

You can also do something similar if you’re running Essbase on Windows, or any of the other EPM components (Planning, Financial Reporting etc), though the counters aren’t quite so detailed and are more generic process counters such as amount of memory used, % of processor time, virtual bytes and so on. For something “free” though, Perfmon is neat utility and Oracle/Siebel have nicely instrumented the OBIEE server processes making it relatively easy to get a handle on the performance of your BI Foundation platform.

I got an email (almost 2 weeks back) from one of the BI EE engineers who works on the BI EE – Essbase connectivity. Looks like one of the bugs that i had pointed out in a very old blog entry here has been fixed (not exactly fixed but a workaround has been found). Also thanks to Roman K from Russia for reminding me about this yesterday. One of the major issues in the current BI EE – Essbase connectivity was the fact that Direct Database requests on Essbase did not work properly. For example, consider the below MDX.

With
  set [Accounts4]  as '[Accounts].Generations(4).members'
  set [Market3]  as '[Market].Generations(3).members'
  set [Product3]  as '[Product].Generations(3).members'
  set [Year3]  as '[Year].Generations(3).members'
  set [Axis1Set] as 'crossjoin ({[Accounts4]},crossjoin ({[Market3]},crossjoin ({[Product3]},{[Year3]})))'
select
  {[Scenario].[Actual]} on columns,
  NON EMPTY  {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

This is an MDX query generated by a very simple report given below.

If you try to fire the same query from the direct database requests page of BI EE, you will get an error as shown below

The fix is to just add one space to the From clause of the MDX. Who would have ever thought this?

Looks like the BI EE MDX parser does not understand the new line (before the from clause alone). Hence one would have to explicitly add a space to the from clause. Only if Oracle had exposed the source of the BI Server Essbase DLL’s, it would have been a lot easier to fix/identify these issues. But again this fix is very good especially if you want to test MDX without going to Essbase Admin Console or Smart-view.

Last week was our second Training Days event in London, and this year we had twenty attendees over the three days with the sessions being led by myself, Borkur and Venkat. The venue this year was a grand old room in the Bloomsbury Hotel in London, as you can see from the photos below it was aptly called “The Library”.

The focus this year was on OBIEE development and management; Essbase and Planning; and ETL using OWB11gR2 and ODI. Here’s Venkat on Day 1 talking about OBIEE and Delivers integration with Java and BI Publisher:

For me, highlights of the three days were meeting the various delegates from across the UK and Europe (and in particular, getting a chance to spend more time with Alex Hansal, one of the lead instructors on OBIEE at Oracle University); meeting some great guys from Logica and Atos Origin and of course our friends at Bicon; talking Essbase and OBIEE integration with a number of people with real project experience; and of course the social events we organized including a meal in the City of London at the end of Day 2.

Venkat and Borkur did sterling service as usual, with Venkat running most of Day 2 covering several aspects of Essbase development, and Borkur running two very technical sessions, one on Day 1 on the BI Management Pack and here, at the start of Day 3, where he set up a live cluster of Oracle BI Servers and BI Presentation Servers after being last out of the bar the previous night.

If you couldn’t make it to this year’s events, I’ve uploaded the complete three days’ slides to our website, which you’re welcome to download:

• Rittman Mead Training Days 2009 Slide Pack

More photos of the event are on Flickr.

If you’re coming to the UKOUG Conference in November in Birmingham, we are also running a couple of events you might be interested in. On the Tuesday night of the conference we are hosting an Oracle BI meetup for BI&DW customers, delegates and speakers at the Pitcher and Piano, Brindley Place, from 6pm – 9pm (more details on this nearer to the event), and I’m running one of the extended masterclasses on the Wednesday where I’ll be going through what’s new and important in OBIEE. If you’re going to Birmingham for the main UKOUG conference, keep an eye out on the blog for more details nearer to the event.

In the meantime, thanks to everyone who came along to the Training Days event, and we can’t wait for next year’s one where hopefully we’ll be taking an in-depth look at OBIEE 11g, ODI 11g and Essbase 11.1.2.

Its been almost a couple of weeks since i last blogged due to a flurry of events like Open-World, Training Days, Client Engagements etc. Getting the materials completed took most of my time and since then i haven’t had a chance to blog. But now that the busiest part of the year is behind me, i am looking forward to blog more frequently in the near future. One of the things that i have been planning on blogging recently is the High Availability features in the EPM 11 stack. I will split this into a series of entries over this month and the next. The first one that we shall look at today is Essbase clustering.

Clustering of Essbase servers can be very critical if you have a mission critical reporting system that requires to be online almost all the time. There are 2 kinds of clustering that Oracle supports for Essbase

1. Horizontal Clustering – This is a normal clustering scenario wherein we have an exact replica of the Essbase server spread across multiple machines and each machine is in active state (active-active).

2. Vertical Clustering – This is clustering scenario which is similar to Horizontal clustering wherein instead of having multiple instances, multiple instances of Essbase are created on the same machine (adding more hardware). If you compare this with BI EE, BI EE does not support this clustering architecture yet.

At a high level, the Essbase clustering architecture is given below

The idea is to have multiple Essbase servers being served in turns by the Provider Services. For complete high availability scenario, the Provider Services itself can be clustered so that there is no single point of failure. The Provider services clustering is very similar to Presentation Services clustering in BI EE. In a BI EE cluster, multiple presentation services write to a common shared web catalog. Similarly, in the case of Provider services, multiple web instances share a common cluster definition stored in a shared directory. This blog entry will focus only on the Essbase server clustering using a single provider services.

Scenario: We basically have 2 instances of Essbase running on 2 separate machines. One machine hosting one of the Essbase Servers and a corresponding provider services. Another machine hosting another Essbase server with no provider services. To cluster both the servers we shall start with logging into the Essbase Administration console and then adding both the servers under the Essbase Server node.

Once these Essbase Servers have been added, the next step is to add a Provider Services under the Provider Services node.

One of the advantages of Essbase clustering is the fact that it allows clustering at database level. That is, one has the ability to either include all the applications to be part of a cluster or only certain necessary ones. In our case, lets add both the Essbase Servers to the Essbase Cluster node.

While specifying the name for the cluster, ensure that you have a name without any spaces in them. Also, in our example, we shall just add the SampEast Application(from both servers) to the cluster

Whenever new applications/servers are added/removed from a cluster, the provider services need to be restarted (but they can be enabled/disabled after adding them without a restart)

Clustering capability can be leveraged only by those applications that can connect through Provider Services. So, if you have a smart-view client or an external application using JAPI, then clustering can be leveraged. To test our cluster, lets login to Smart-view using the provider services. While adding the Essbase Server, instead of the actual Essbase Server names use the cluster name that we gave above.

As you see we now have the application/database that we added under the cluster being displayed by Smart-View through the provider services. Now, lets create a report using this database and check where the session gets created.

As you see the first session gets created in the first Essbase Server. Now lets open up another instance of the Smart-View client and create the same report. You will notice that Smart-View pushes this new session to the other essbase server

Also, as another test, lets kill the first essbase server and then try running both the reports(both instances of Smart-View) again. You will notice that, both the sessions will now have moved to the valid & live Essbase Server.

Though it works out of the box and is quite good, it has certain drawbacks. Lets look at them one by one.

1. Write backs directly from Smart-view are not supported. If you try to write-back to a database cell, an error message would pop-up denoting the fact that write-backs are not supported in a clustered mode.

The only work around is to connect to the servers directly (without the provider services cluster) and then write-back on them. But the end user will have to ensure that both the servers are kept in sync manually. So, this cannot be used in Hyperion Planning apps.

2. Essbase Add-in cannot leverage the clusters.

3. Security across the nodes should exactly be the same. Both the nodes should have same security setup (native authentication or shared services authentication)

Apart from the above 3 limitations, every other individual essbase feature can be leveraged and hence all of them will be in a highly available mode. It is easy to add new nodes and remove them as and when necessary. In future, we shall see how to go about clustering Provider Services, OpenLDAP and Hyperion Workspace. All the clustering topics that i am planning to cover will all be based on active-active clustering mode. Active-Passive clustering is also supported though it is typically achieved through an external software solution (to keep machines in sync through some background process). Hence i will not be covering the latter.

I was recently going through the recorded Open World sessions using Oracle Open World on Demand, and came across an interesting session by Alaric Thomas and Mike Nader on the forthcoming Oracle BI Applications to Essbase Integrator. This is a technology project going on within Oracle that aims to deliver integration between the Oracle BI Applications technology stack and the Essbase stack, so that users can for example launch SmartView from a BI Apps dashboard and analyze their data using Essbase.

Listening to the presentation and reading the slides, it’s clear that this is a work in progress and there’s the usual disclaimers about not being able to make purchasing decisions based on what they say. As this session was a preview and it’s at such an early stage, I won’t be too specific about what they said, though you can listen to the session in full and download the slides on Open World on Demand, session S308151, if you’re interested in the full details. Anyway, here’s what was talked about:

The product is called the “Oracle BI Applications Essbase Integrator” and aims to deliver in three areas:

1. A seamless user experience between the OBIA dashboard and SmartView
2. Metadata sychronization between the BI Apps RPD and the Essbase Outline
3. An Extension to BI Apps Financial Analytics and Supply Chain/Order Analytics

Looking at the demo screenshots, the way that this would be delivered would be through additional narrative views on the BI Apps dashboard (the platform targeted is Oracle BI EE 10.1.3.4+, BI Apps 7.9.6 and Essbase 11.1.1) that would provide drop-down menus linking to SmartView analyses. When the menu item is selected, SmartView would be contacted (via the OracleSV protocol that Venkat blogged about here) with context being passed from OBI Apps to Essbase. Dimensions would be on the correct axes, security would be honoured and the user should not have to do anything to maintain their train of thought.

The way that data and metadata would work, is that the BI Apps RPD would be used as the initial data definition for the Essbase cube. The BI Apps RPD would be read and parsed, with data being extracted via UDML and then loaded into a Fusion Middleware MDS repository that stores an abstracted data model for the two systems. This would then be used to generate an Essbase outline along with the neccessary SQL scripts to extract data via the BI Apps RPD. Then, a similar process would work over time to keep the Essbase cube in sync with the BI Apps RPD, but preserving user changes to the Essbase cube so that models and other Essbase-specific analyses are preserved during updates. There is quite a sophisticated process at work here with bi-directional synchronization between the two products, from what I can see it doesn’t use Essbase Studio but instead relies on custom data and metadata loaders.

An area that appears to have had significant thought is how to apply OBI data filters, aggregations and calculations to the Essbase cube. OBI filters are essentially predicate based – filter products on “electrical”, for example – whereas Essbase ones are hierarchical, whilst OBI calculations are in SQL whilst Essbase ones are in MDX. OBI aggregations are also transferred 100% to Essbase, and the whole thing is aimed at the current set of BI Apps and Essbase products together with EBS rather than being reliant on the Fusion (11g) generation of products.

So, I won’t say any more as this session was the first public unveiling of the product, but as I said if you have Oracle Open World on Demand access you can listen to the presentation and see the slides for more details. I’ll be trying to get Alaric across to ODTUG Kaleidoscope later in 2010 to see if he can demo this later in the year, but if like us you’re a user of both the BI Apps and Essbase, this sounds like an interesting development.