Rittman mead blog

It’s almost that time again, and Rittman Mead will once again be attending and presenting at Oracle’s Open World conference and exhibition, running in San Francisco from September 19th-23rd 2010.

Our team are running thirteen sessions over the week, covering such topics as OBIEE 11g, Fusion Middleware 11g, Oracle Data Integrator, Oracle Golden Gate, Oracle Database 11gR2 and Oracle Exadata. Here’s the run-down on our sessions, and who’s presenting:

• Sunday 12.30pm, Moscone West 2002 : Mark Rittman, “Oracle Business Intelligence Enterprise Edition Dashboarding/Reporting and More”
• Sunday 12.30pm, Moscone West 2012 : Stewart Bryson, “Introduction to the ODTUG BI/DW Sunday Forum”
• Sunday 2pm, Moscone West 2020 : Jon Mead, “Oracle Exadata/Oracle Business Intelligence Enterprise Edition Retail Case Study”
• Sunday 4.30pm, Moscone West 2012 : Mark Rittman, Jon Mead, “Oracle Business Intelligence Enterprise Edition Panel”
• Tuesday, 3.30pm, Moscone West 3016 : Jon Mead, “An Oracle Exadata Case Study in the Retail Sector”
• Tuesday, 3.30pm, Moscone West 3020 : Borkur Steingrimsson, “Oracle GoldenGate In-Depth : What’s All the Fuss About?”
• Tuesday, 5pm, Moscone South 308 : Peter Scott, “Implementing a Retail Data Warehouse on the Oracle Exadata Platform : Lessons Learnt”
• Wednesday 10am, Moscone West 3010 : Mark Rittman, “Oracle Business Intelligence Enterprise Edition Architecture Best Practices”
• Thursday 9am, Moscone South 300 : Stewart Bryson, “Resuming, Restarting, Restoring : Three R’s of Data Warehouse Fault Tolerance”
• Thursday 11am, Hotel Nikko/Bay View : Stewart Bryson, “Real-Time Data Warehousing with Oracle BI and Oracle Database”
• Thursday, 12pm, Moscone West 3010 : Mark Rittman, “Oracle Business Intelligence Enterprise Edition Data Modeling Best Practices”
• Thursday 1.30pm, Moscone West 3010 : Venkat Janakiraman, “Oracle Business Intelligence Enterprise Edition / Oracle Fusion Middleware”
• Thursday 3pm, Moscone West 3020 : Venkat Janakiraman, “Oracle Data Integrator : ETL Loads on Oracle Essbase and Oracle Hyperion Planning”

We’ll also be at the various events, getting to as many sessions as possible and meeting up with our friends and customers within the industry. If you’re going to be there, stop by one of our sessions and say hello. We’ll also post the various presentations and papers on our website once the conference is over.

As mentioned in the blog entry here, BI Server 11g now supports more functions which were added to support the new UI features. Each of these functions can be used as part of the Logical SQL or can even be used directly in the Answers UI. In this blog entry we shall be looking at how some of these functions can be used.

1. SELECT_PHYSICAL: Though this is not a function, this is a new feature supported by BI Server that can bypass the BMM layer in the SQL queries. So, any SQL that uses SELECT_PHYSICAL will be more closer to the ANSI SQL standard. There are 2 main advantages of this

a. Any external reporting tools that generate SQL automatically can now leverage the functionality of BI Server. For example, in the blog entry here i had shown the difficulty in actually using Logical SQL with Cognos. But introduction of SELECT_PHYSICAL should alleviate this issue thereby making BI Server open for better integration externally.

b. Using SELECT_PHYSICAL one can now make direct joins across data sources without actually having the need to model them separately in the Business Model and Mapping layer.

With the addition of more in-memory operations in the BI Server, this should act as a point of integration for external tools that have the need to leverage the metadata framework of BI EE.

To illustrate how this works, lets look at the repository shown below,

As you see, the above repository contains all the relevant tables in the SH schema. Now, to use the SELECT_PHYSICAL command, lets go to the Issue SQL section of the BI EE and fire the SQL given below

SELECT_PHYSICAL 0 s_0, CHANNEL_CLASS s_1, SUM(AMOUNT_SOLD) s_2 FROM "ORCL - SH".."SH"."CHANNELS" A, "ORCL - SH".."SH"."SALES" B WHERE A.CHANNEL_ID = B.CHANNEL_ID GROUP BY CHANNEL_CLASS

Remember, though this is a SELECT_PHYSICAL SQL statement, BI Server will still do the parsing as the objects will have to reside inside the repository.

This option can be enabled at the ODBC/JDBC level as well. That way the DSN can be configured to just accept SELECT_PHYSICAL or SELECT commands.

I will cover more details of how this can be leveraged from external tools like QlikView, Cognos etc in a future blog entry.

2. AGGREGATE AT:

This function adds more multi-level analysis capabilities within the BI Server. This function provides the same functionality as the Level Based Measures. What this does is, it provides an ability to extract the values of a parent level in a query without having to SUM up the values in the lower level. To illustrate, lets look at the simple report below

In the above report, lets now apply a filter to display the just 2 quarters.

Now, what we need is an ability to display the value of yearly sales alongside the quarterly sales. To do that, we need to enter the formula as shown below

AGGREGATE("Sales"."AMOUNT_SOLD" AT "Times"."TimesDim - Calendar Year"."Calendar Year")

If we look at the report now, we should see that we can compare the Yearly sales along the Quarterly sales i.e. the AGGREGATE AT function will not respect the filter on that dimension. If you want the filters to be honored as well then the older SUM BY functions can still be used.

If you look at the Physical SQL generated, you would actually notice 2 separate WITH clauses (minimum). One for the Sales figure. And the other for the Yearly Sales figure. The Yearly Sales figure will not have the filter applied.

WITH SAWITH0 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from (select sum(T44515.AMOUNT_SOLD) as c1, T44523.CALENDAR_QUARTER_DESC as c2, T44523.CALENDAR_QUARTER_ID as c3, T44523.CALENDAR_YEAR as c4, T44523.CALENDAR_YEAR_ID as c5, ROW_NUMBER() OVER (PARTITION BY T44523.CALENDAR_QUARTER_ID ORDER BY T44523.CALENDAR_QUARTER_ID ASC) as c6 from TIMES T44523, SALES T44515 where ( T44515.TIME_ID = T44523.TIME_ID and (T44523.CALENDAR_QUARTER_NUMBER in (1, 2)) ) group by T44523.CALENDAR_QUARTER_DESC, T44523.CALENDAR_QUARTER_ID, T44523.CALENDAR_YEAR, T44523.CALENDAR_YEAR_ID ) D1 where ( D1.c6 = 1 ) ), SAWITH1 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from (select sum(T44515.AMOUNT_SOLD) as c1, T44523.CALENDAR_YEAR as c2, T44523.CALENDAR_YEAR_ID as c3, ROW_NUMBER() OVER (PARTITION BY T44523.CALENDAR_YEAR_ID ORDER BY T44523.CALENDAR_YEAR_ID ASC) as c4 from TIMES T44523, SALES T44515 where ( T44515.TIME_ID = T44523.TIME_ID ) group by T44523.CALENDAR_YEAR, T44523.CALENDAR_YEAR_ID ) D1 where ( D1.c4 = 1 ) ), SAWITH2 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6 from (select 0 as c1, D1.c2 as c2, case when D2.c2 is not null then D2.c2 when D1.c4 is not null then D1.c4 end as c3, D1.c1 as c4, D2.c1 as c5, D1.c3 as c6, ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, case when D2.c2 is not null then D2.c2 when D1.c4 is not null then D1.c4 end ORDER BY D1.c2 ASC, D1.c3 ASC, case when D2.c2 is not null then D2.c2 when D1.c4 is not null then D1.c4 end ASC) as c7 from SAWITH0 D1 left outer join SAWITH1 D2 On D1.c5 = D2.c3 ) D1 where ( D1.c7 = 1 ) ) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from SAWITH2 D1 order by c1, c3 NULLS FIRST, c2

3. CALCULATEDMEMBER:

This function is used to derive calculated members using members at any level of a hierarchy. In 10g, we can do this from a pivot table. In 11g, this function has been introduced at the BI Server level itself so that we can leverage these directly from the repository.

To illustrate this, consider the hierarchy shown below

In this hierarchy, we want to find out the difference between Steven King’s Salary and his 2 direct reportees (Neena Kochhar and Lex De Haan). To do this, we need to use a function given below

CALCULATEDMEMBER("HR"."Employee"."EmployeeDim",'Test', MEMBER(100) - MEMBER(101) -MEMBER(102))

This would push the function directly on the parent-child hierarchy and give us the correct values. This function cannot be used if there are other attributes of the same dimension in the report. This is generally used by external application tools. But within BI EE Answers, there is no need for using this as we can achieve the same using Custom Groups and Calculations.

4. EVALUATE_ANALYTIC:

This is another significant new feature that can be useful to push down the oracle database analytic functions. I have covered EVALUATE number of times before for function shipping database functions. EVALUATE_ANALYTIC extends that further and provides us an ability to function ship the analytic functions as well. The syntax of this function remains the same. I will cover this in detail in another blog post as there are quite a few use cases where EVALUATE_ANALYTIC can prove to be very useful.

5. HIERARCHICAL FUNCTIONS:

BI Server now supports hierarchical functions like ISCHILD, ISROOT, ISANCESTOR, ISDESCENDANT & ISLEAF that can be used to traverse a Parent-Child hierarchy. These functions generally return a boolean data type (true/false) and hence can be used in filters to restrict data. They can also be used in column formula case statements.

For example, in a hierarchy if we want to know whether a specific member is a leaf node or not, we can use the below function to display Yes/No as an attribute of the parent-child hierarchy

CASE WHEN ISLEAF(Employee.EmployeeDim) THEN 'Yes' ELSE 'No' END

Next up is some interesting posts on the BI EE integration with Fusion Middleware & Essbase. I will be doing these posts towards the end of next week.

As Mark had pointed out earlier here, one of the significant changes in the BI EE 11g release is the migration of security from the BI EE repository to Weblogic. Users and Groups will no more be maintained within the rpd. Even in an external authentication like LDAP, Groups will not be maintained within the repository. This is good in many ways as it can significantly reduce the size of the repository. The downside though is, in offline mode unless there is a change in any security object (like custom filters to a group etc), the users & groups will not become visible. The use of Web Catalog Groups is no more recommended. It still exists primarily for supporting upgrades. In this blog post, lets see how the new weblogic based security works.

There is a terminology change that we need to get used to while moving to BI EE 11g. Groups now refer to Weblogic groups. BI EE specific groups are called as Application Roles. The figure shown below explains the relationship between Users, Groups & Application Roles.

Weblogic security is much more comprehensive and it can support multiple external directories like LDAP, OID etc. It also supports table authentication. So, if you are planning on setting up external authentication, its preferred to set it up at the Weblogic layer. We can also setup external directories within BI EE (like in 10g).

To start with lets create a simple user bieeuser1 in the Weblogic Console Security Realm (http://localhost:7001/console).

While creating a user we can assign it to a provider. A provider is the authentication directory against which users will be authenticated. In our case, we have created the user in the default weblogic directory. In the same way lets create 2 more users bieerpdadmin & bieewebcatadmin.

The idea is to provide the following privileges to each of the users that we have created

bieeuser1 – Casual user with no access to the repository but with read only access to dashboards (no answers)
bieerpdadmin – Repository Administrator with full control of the Repository objects but with no control on the web catalog.
bieewebcatadmin – Web Catalog Administrator with full control on the web catalog but with no access to the repository

Remember, its not always necessary to assign users to weblogic groups. Users can be directly assigned to application roles. Once the users are created, lets login to the Weblogic enterprise manager and navigate to the coreapplication under the Business Intelligence folder.

Now, right click on the coreapplication and choose the Security->Application Roles option

This should list all the default application roles available within BI EE.

Let’s neglect the existing roles(ideally we will be reusing/reassigning the default roles as much as possible) and create 3 new roles.

1. BIEERoleUser
2. BIEERoleRPDAdmin
3. BIEERoleWebCatAdmin

For now lets not add any users/roles to these roles.

Lets now navigate to the Application Policies and start granting specific rights/grants to the roles that we created. Remember in any ADF application we need to define application policies so that they can be granted to individual users. We start with assigning the following privileges to the BIEERoleUser

1. resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.queryUserPopulation

This basically provides an ability to query the BI Server repository.

Lets assign this privilege to the BIEERoleUser application role.

In the same way lets assign the following privileges to the BIEERoleRPDAdmin and BIEERoleWebCatAdmin

For BIEERoleRPDAdmin

1. resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.ManageRepositories

For BIEERoleWebCatAdmin

1. resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.queryUserPopulation

Now that the roles have the right privileges, lets go back to the Application Role screen and start assigning the 3 users to each of these roles.

After assigning these, if you try to login to the repository in online mode, only the user bieerpdadmin can login. We have satisfied one requirement, that is we have created a user who is the repository admin. For presentation services, by default during install the privileges in presentation services get assigned to only 3 default groups BIAdministrator, BIAuthor and BIConsumer. So, to make bieewebcatadmin a true webcatalog administrator we have to go to the Application Roles and add the BIAuthor & BIAdministrator role to BIEERoleWebCatAdmin

This will automatically make the user bieewebcatadmin user to be the administrator of the webcatalog. Initially it is mandatory to assign a new role to the BIAdministrator role as that is pre-configured to be the only role having access to Privileges in Presentation Services. Granting BIAdministrator to a role also opens up the access to the repository. This can be disabled as well. It is not necessary to always create new roles and assign policies to them. Existing roles can be re-used and re-assigned. The entire security model is now more aligned with Oracle Platform Security framework so that all the fusion applications can integrate with each other seamlessly.

One of the important points to consider when moving from BI EE 10g to 11g is in understanding the BI Server behavior changes. Though there are quite a lot of changes in the underlying code for performance improvement etc, introduction of new features in 11g can potentially alter your generated queries in 11g. In this blog entry i will basically cover 2 main important changes that have been introduced in 11g. There are more actually, but that is something we will try to cover in our training days event.

1. Level Based Measures at Detail Level:

One significant change in 11g is the handling of level based measures at the detail level. For example, lets consider a measure AMOUNT_SOLD that is assigned to the detail day level of the date dimension.

In 10g, whenever we bring this column for reporting, we would basically see something as shown below i.e. the level will always be enforced.

In the case of 11g, there are 2 types of behavior

a. If the report contains normal attribute columns and when the query is at a grain higher than the measure grain (say the report is at the Year level), then normal aggregation will be applied and the level will not be enforced like in 10g. An example screenshot is given below

b. If the report contains hierarchical columns, then if the query is at higher grain than the measure, the measure values will become null. The values will appear at that grain or below. An example screenshot is given below

2. Priority Groups & Order of LTS:

In 11g, there is a new concept called Priority Groups. In 10g, the LTS that determines the final query is dependent on the ordering of the LTS in the dimension logical table(in most cases – but there are cases where we cannot be always be sure to guarantee this behavior). But in 11g, this is determined by the priority order set in each and every LTS.

For example, lets consider a case where there are 2 aliases for the same dimension CHANNELS as shown below

In the business model and mapping layer, lets assume that both the LTS are mapped to all the columns. Both the LTS are at the same detail level. Now, lets assign a priority group of 0 for the Channel Alias – B LTS and a priority group of 1 for the Channel Alias – A LTS. Also the SALES LTS of the Fact table will also be assigned to the priority group 0. In 11g, the LTS with the least number is considered as the one that has to be chosen for querying (highest priority)

Now, lets create a report containing columns from Channels dimension and the Sales fact.

If you look at the query of the report, you will notice that Channels Alias – B will be chosen as it is the one having the highest priority.

Lets now go back to the repository and reassign the highest the priority to Channels Alias – A as shown below

For the same report, if you look at the query now, you will notice that Channels Alias – A will be chosen instead of the B alias.

Mark has already explained the new Parent-Child hierarchy new feature in 11g here. Parent Child hierarchies are different from normal level based hierarchies not only in the nature of their structure but also in the way aggregations are done. For example, in a level based hierarchy, the most common type of aggregation that is done involves rolling up the numbers from the lower most level. But in the case of parent-child hierarchy, an aggregation by default enforces the roll-up across all descendant members. To understand this lets try to take this through an example.

Lets consider a sales department with the employee hierarchy shown below

Its a simple parent child employee hierarchy found in the oracle HR schema. Now the requirement for us is to model the Salary attribute of an employee in 3 ways

1. As an attribute – Show the salary of each employee as an attribute in the parent child hierarchy.
2. As a measure (All intermediary Roll-ups) – Roll-up the salary figures of all the employees who roll into the employee. For example, Scott King will have a Salary equal to sum of salaries of all the employees who report to him, including the ones who are his direct & in-direct reporting employees.
3. As a measure (Level-0 Roll-up) – Roll-up the salary figures from the lower most level till the top node For example, in the above figure, Scott King will have a Salary Roll-up figure of all the employees who are the lowermost level (not including the intermediary nodes – very similar to normal level based hierarchy roll-ups)

The above requirement is quite straightforward, but to achieve similar such aggregations in a normal level-based hierarchy, we will have introduce fragmentation & other techniques. But with parent-child hierarchy modeling now available in BI EE, all the above 3 requirements can quite easily be achieved.

Following is the employees parent child table that stores all the attributes of an employee

We start off with modeling this as a normal dimension table. As shown in Mark’s blog entry, we then create the Parent-Child table that stores all the relationships within the employee hierarchy (if you look at the script used in creating this, it will be containing a Connect By statement that will connect a root hierarchical node to every member that is part of its hierarchy).

Once this is created, we start with including the Salary as an attribute within the Employee dimension itself

This will serve our first requirement i.e show salary as an attribute of every employee. Now, to model the 2nd requirement i.e. to model salary as a measure and then make it to rollup from all the intermediary hierarchical members, we start with defining an alias on the main Employee table. Then join this table to the custom parent child employee table. The join structure of all the 3 tables is given below

We would then be using the salary column from the new employee alias table in our logical fact table as shown below

Lets now look at the report now and see what BI EE generates.

As you see, BI EE has generated the correct results with the roll-up of intermediary members as well. This is something that is quite difficult to model in BI EE 10g. If we look at the query generated (just the key one that generates the measure as BI EE now generates lots of intermediary sub-queries to support contextual drilling) you would notice that the default Parent-Child behavior is to achieve a roll-up across a hierarchy

To achieve the 3rd requirement i.e. to model a level based hierarchy kind of rollups from level-0, we create another alias in the physical layer and then model it the same way as the 2nd requirement. In the fact logical table, we then create a new measure that will map to the new physical alias as shown below

To ensure that we roll-up only from level-0 members, we then apply a filter in the fact logical table source as shown below

Lets now take a look at what BI EE generates

As you see, BI EE now generates the roll-ups only from level-0 members as against every descendant. We can have even more granular roll-ups based on custom defined attributes.

As well as the three Rittman Mead OBIEE 11g Training Days events we’re running in London, Atlanta and Bangalore later this year, I’ve also agreed to do a number of Enterprise Business Intelligence Masterclasses for Oracle University, including a visit down to Australia and Singapore. These events are seminar-style, cover OBIEE 11g as well as OWB11gR2/ODI 11gR1 and a bit of Essbase 11.1.2, and are an ideal high-level introduction to the technology behind the Oracle 11g BI&DW technology stack.

More dates will be announced over the next few months, but for now, here’s the current set of confirmed dates and venue:

• De Meern (Utrecht), Netherlands, 10-11 November 2010
• Bratislava, Slovakia, 25-26 November 2010
• Melbourne, Australia, 6-7 December 2010
• Sydney, Australia, 9-10 December 2010
• Singapore, Singapore, 13-14 December 2010
• Oslo, Norway, 14-15 February 2011

I’ll post details of any future events on our Events website page.

BI EE 11g is a significant feature filled release when compared with the previous 10g release. Most customers who are planning to upgrade to 11g have an automated way of upgrading their 10g Web Catalogs and repositories to the 11g version. When moving from 10g to 11g, we will have to go through the upgrade utility as there are significant changes to the repository structure and the web catalog structures. A direct copy & paste into the 11g version will not work.

The upgrade process involves the following steps

1. Install the new 11g version in either the same machine or a different machine.
2. Run the upgrade assistant utility to upgrade the repository & Web Catalogs.
3. Migrate other 10g specific customizations manually to the 11g instance. The different customizations are

a. Any Static files added to the 10g app server
b. Custom XML messages
c. Styles & Skins (The upgrades for this will have to be done carefully as some CSS & files have changed in this release)

4. Upgrade the Scheduler schema (Both BI Delivers & BI Publisher) through the upgrade utility
5. Upgrade BI Publisher

In this blog entry we shall see how to go about upgrading the RPD and Web Catalogs from the 10g to the 11g version. After installing 11g, start up the upgrade assistant utility from {ORACLE_HOME}/bin/ua (or ua.bat if you are on windows)

The upgrade assistant can be used to upgrade the scheduler schema, BI Publisher as well as the RPD & Web Catalogs. Lets start with the first option, i.e. upgrading RPD and Web Catalogs

In the upgrade screen, choose the 10g RPD along with the Web Catalog Path. In the Deliveries folder, choose the 10g deliveries path ({OracleBIData}/web/catalog/deliveries

In the next screen, provide the details of the weblogic server on which we will be deploying the Repository and Web Catalog to.

The upgrade utility does not seem to support remote deployments of the RPD and Web Catalog i.e. Upgrade Utility will only deploy the repository and web catalog on the machine containing the weblogic admin server.

The upgrade process will stop all the components first and then will do the individual RPD and Web Catalog Upgrade.

This is just a utility. We cannot expect all the reports to start working out of the box immediately after an upgrade. Good amount of testing is required to make sure all the older queries of the most commonly used reports are working without any major issues. Also, not all the reports would get migrated seamlessly due to the significant changes across the versions which the utility cannot recognize. Care has to be taken to make sure that all the warnings logged as part of the upgrade process are properly analyzed and corrected manually. A sample upgrade log is shown below (containing 4 warnings)

One important new feature of BI EE 11g is the ability to report on CLOBs and other large objects. The main difference between normal Varchar/Numeric columns and LOB datatype columns is, LOB columns do not support GROUP BY, DISTINCT & ORDER BY operations on them (from a reporting perspective). Any query that BI EE generates will always have a GROUP BY or a DISTINCT in it. So, in 10g reporting on LOB columns have always been an issue(will result in Ora-XXXX errors). One had to resort to different types of workarounds for reporting on LOB columns.

In BI EE 11g, its possible to isolate different columns from the GROUP BY/DISTINCT operations by using the SPARSE/DENSE lookup operators. I have already blogged about how Sparse & Dense lookups work in general here. Lets see how the same can be used for CLOB reporting. To demonstrate this i shall be using the v$sql view. This view basically contains a CLOB called SQL_FULLTEXT. This column contains the SQL queries stored in the form of CLOB, that are fired to the Oracle database by multiple users. Our aim is to report on this view and display the SQL Queries from the CLOB column.

We start with granting select on the v_$sql view to SH schema and then importing it in BI EE.

Then edit the column properties of the SQL_FULLTEXT column and increase the size to 100000 (or higher) so that BI EE can display the values from CLOB.

Start with modeling a dimension and a fact in the BMM layer as shown below

Then create another logical table (same as the dimension) containing SQL_ID as the primary key. The idea is to use this primary key and show the SQL_FULLTEXT using a DENSE lookup. Mark the new logical table as a lookup table.

In the dimension logical table create a new column and use the following formula to derive the SQL_FULLTEXT as a lookup column. This will always push a separate SQL without DISTINCT/GROUP BYs on the SQL_FULLTEXT column.

Lookup (DENSE "LOB Reporting"."Lookup - CLOB"."SQL Full Text" , "LOB Reporting"."Lookup - CLOB"."SQL ID" )

Lets now include this new column in the presentation layer and use it for reporting.

As you see, we should now be able to see the CLOB data without any restrictions. If we look at the query generated, you can basically see 2 separate SQL queries, one with the group-by and the other with just the lookup.

select sum(T43900.ROWS_PROCESSED) as c1, T43900.SQL_ID as c2 from SYS.V_$SQL T43900 group by T43900.SQL_ID select T43900.SQL_FULLTEXT as c1, T43900.SQL_ID as c2 from SYS.V_$SQL T43900

In BI EE 11g, usage tracking can now log logical sql’s greater than 4000 characters into a separate CLOB column.

One of the biggest new features in BI EE 11g is the ability to create native mapviewer maps and then overlay reporting components/visualizations on top of maps. For example if you are doing a country based sales analysis, its easy to color code each country across the world right within the maps. It is also possible to overlay pie charts, bar charts in the maps thereby providing an extensive visual analysis capability. In 10g, one had to go through some complex java scripting to achieve BI EE and mapviewer integration. I have blogged about it here and here. 11g makes this entire integration a lot easier.

Lets try to understand the integration through a simple example. Shown below is a report of Sales performance of 3 US states across 4 years.

We would like to depict this using a map as shown below.

As you see we have 2 visualizations applied on the same map. The first one is a different color coding for each of the 3 states depending on the sales performance. The other is a pivot representation of yearly performance for each state, all within a single map.

To do this we start with enabling the Mapviewer integration by updating the instanceconfig.xml to include the following tags (in each presentation server)

<SpatialMaps>
<ColocatedOracleMapViewerContextPath>/mapviewer</ColocatedOracleMapViewerContextPath>
<RemoteOracleMapViewerAbsoluteURL></RemoteOracleMapViewerAbsoluteURL> <LayerDataLayout>
<MaxRecords>600</MaxRecords>
</LayerDataLayout>
</SpatialMaps>

Then we need to restart the presentation service. This enables the connectivity between BI EE and Mapviewer. After enabling the integration, we then import the standard spatial MVDemo schema into the Oracle Database. This schema contains Layers, Themes and some Base maps for all the major US States, Cities & some inter-state highways. After importing and installing the MVDemo schema, we need to download the Mapbuilder application which will enable us to create a custom map. We will be using this map for rendering in BI EE. The Mapbuilder application can be downloaded from here.

After downloading the Mapbuilder app, we start the application and login to the MVDemo schema. By default this schema contains a lot of built-in themes, Base Maps and Tile Layers.

We start off with creating a simple Base Map called STATE_MAP which will be using 2 out of the box themes THEME_DEMO_STATES and THEME_DEMO_STATES_LINE

This step will enable us to render all the US States and its boundaries within a very simple map as shown below

Once the base map is created, lets create a new tile layer called STATE_TILE_MAP which will use this base map for rendering. We can define all the zoom levels, boundaries etc within the tile layer

Now we have a map that can show all the US states and its boundaries. We need to now expose this map to the mapviewer application (as an admin user through Mapviewer admin screen) by taking it online as shown below

Now the map is available for consumption through BI EE. Remember, the theme that we used above, THEME_DEMO_STATES has an identifier called STATE_ABBR which is basically the key that we need to pass from BI EE. Relationship between BI EE repository columns/subject areas and Mapviewer maps are done from the Manage Map Data screen in the presentation services admin console.

In this screen, we need to import the layers, maps and the images that we want to include as shown below

Edit the THEME_DEMO_STATES theme and map the State column from the repository. There need not be any relation between the column used in spatial and the column mapped in BI EE. All we need to ensure is the attributes should match. In my case, the State abbreviations come from the SH schema and the THEME_DEMO_STATES come from STATES table in the MVDemo schema.

Then navigate to the STATE_TILE_MAP in the background maps and add the THEME_DEMO_STATES again.

This establishes a relationship between the Subject Area and the Map thereby enabling us to add the mapview from answers. After doing this, lets create the report from the SH schema containing just the 3 states for 4 years.

Use the new Map View option to create the map.

This will render the map that we created in mapviewer. If you notice carefully, it will automatically apply some color coding to the 3 states that are part of the report.

The color coding is actually part of default visualization that Map Views apply on a map. Lets change this to something as shown below

Lets now create one more pie chart visualization (for obtaining the yearly breakup of sales in all the 3 states)

Lets use the Year as the column providing the necessary slices for the pie charts

This will give us the necessary visualization using Mapviewer.

Though there are a lot of steps involved, majority of the steps are for setting up the mapviewer. Once we have the maps set up the entire process of adding visualization to maps is quite straight forward.

I’m pleased to announce that, in line with the London and Atlanta Oracle BI 11g Training Days events, our event in Bangalore will now be hands-on.

As a reminder, the Rittman Mead Oracle BI 11g Training Days event is three-day of intense, hands-on technical training on the new 11g release of Oracle Business Intelligence. Over the three days, you’ll learn how to install and configure OBIEE 11g, create the metadata repository, create reports and dashboards, and use new features such as scorecards and the action framework. Aimed at developers with some experience with OBIEE 10g, and covering things at a fast pace, this is ideal training for BI developers looking to get up to speed quickly with the 11g release. Full details of the agenda can be found at http://www.rittmanmead.com/trainingdays 2010, and the event is running at the Fortune Select Trinity Hotel, Bengaluru (Bangalore), India, on November 15th, 16th, 17th 2010.

To take part in the hands-on labs, you will need to bring a laptop with the following minimum specification:

• 3GB of RAM (4GB+ if you intend to use VMWare for your environment, 6GB+ is preferable)
• 50GB of free disk space
• Windows XP SP2 Professional, Windows Server 2003 Enterprise Edition or higher. Note that we have not tested against Windows Vista or Windows 7, and our preferred OS version is Windows Server 2003 Enterprise Edition
• Either Firefox 3, or IE7 or higher (IE6 is not supported for OBIEE 11g)

You will also need to download OBIEE 11g prior to the event, and bring it along with you (either under your own company’s license, or the OTN trial license). We cannot distribute Oracle software at the event.

If you would like to reserve a place, contact us at trainingdays2010@rittmanmead.com. Places are limited to 30, and around 15 have already been reserved as of now.

The other big change in BI EE 11g as mentioned before here, is the ability to assign ID columns for descriptive columns more commonly known as Double Columns. This feature has 2 fold advantages

1. In BI EE 10g, there was no automated way of filtering on ID’s when end users chose the description values in the prompts. The Double column feature provides this ability in 11g.

2. In many implementations where data is captured in multiple languages, the descriptions might be stored in different languages. But the filtering of data will be on ID’s (which will be the same across languages). Double column feature provides that ability now.

Lets try to put this feature to use by using a simple example. We shall be using the Sales Warehouse schema (SH) that comes by default with an Oracle database installation. The screenshot below shows 3 columns from the CHANNELS dimension. One column is CHANNEL_DESC which contains the channel details in English. The second column in CHANNEL_DESC_FR which contains the channel details in French. And the third column is the CHANNEL_ID that acts as an id to both the french and english descriptions.

Our idea is to create 2 prompts, one in french and the other in English, and then using these 2 to filter on 2 separate reports. To do this we start with assigning the CHANNEL_ID column as the descriptor ID column in the Business Model and Mapping layer for both CHANNEL_DESC and CHANNEL_DESC_FR columns.

Once this is done, lets go to BI EE UI and create 2 dashboard prompts. Ideally its not necessary to use 2 prompts as we can use INDEXCOL function in the repository to switch between columns based on the user preference language, but for demonstration, i will create 2 prompts.

The first dashboard prompt will point to the French description field. When we include this column in the prompt, you will notice that the prompt will automatically show the Included ID column as well.

Now, if you look at the options section, we now have the ability to display the Descriptor ID as well.

Lets enable that option as well so that users who are more familiar with the ID’s will have the ability to toggle between the description and the ID.

Lets save this prompt and create another prompt that is similar to the above prompt but with CHANNEL_DESC as the source column.

Lets now create a simple report in Answers containing Year, Product Category and Sales columns. And lets apply the channel filter (on English) as well. When you create the prompt, you will notice that for static filters you can now enforce the filtering on IDs directly. But for this blog entry, lets use the is prompted filter.

If you now, bring the report and the prompt in a dashboard, end users will now have the ability to filter on the description as well as the ID.

If you enable the Select by ID check box, you will notice that the drop down will now have the ID and the description concatenated for easy selection.

Same will be the case for French descriptions as well.

Lets first choose the French Descriptions and then see, in terms of SQL how the query filter is generated

WITH SAWITH0 AS (select sum(T69590.AMOUNT_SOLD) as c1, T69588.PROD_CATEGORY as c2, T69591.CALENDAR_YEAR as c3, T69591.CALENDAR_YEAR_ID as c4 from SH.TIMES T69591, SH.PRODUCTS T69588, SH.CHANNELS T69584, SH.SALES T69590 where ( T69584.CHANNEL_ID = T69590.CHANNEL_ID and T69588.PROD_ID = T69590.PROD_ID and T69590.TIME_ID = T69591.TIME_ID and (T69584.CHANNEL_ID in (2, 4)) and (T69590.CHANNEL_ID in (2, 4)) ) group by T69588.PROD_CATEGORY, T69591.CALENDAR_YEAR, T69591.CALENDAR_YEAR_ID), SAWITH1 AS (select distinct 0 as c1, D1.c2 as c2, D1.c3 as c3, D1.c1 as c4, D1.c4 as c5 from SAWITH0 D1) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from SAWITH1 D1 order by c1, c3, c2

As you notice, though we have chosen the descriptions in the UI, the filters are automatically pushed to the IDs. Same will be the case for filtering on IDs as well.

A very important feature that has been introduced in 11g is the ability to model lookup tables in the repository. If you have worked with ETL tools before, lookup tables are quite common especially when we want to do a lot of lookup operations (id to description mappings). In 10g, to model lookup tables the only way was to make inner joins (equi join or outer joins) to the lookup tables through the Logical Table sources. But now in 11g, this ability has been added to reference both physical tables and logical tables.

There are 2 types of lookup tables.

1. Sparse Lookups – A sparse lookup basically means that the main driving table does not necessarily have corresponding lookup values in the lookup table for all the id values. This can be considered to be an equivalent of a Left Outer Join.

2. Dense Lookups – A dense lookup basically means that the main driving table will have matching lookup values in the lookup table for each of its unique id value. This can be considered to be an equivalent of an inner join.

There are 2 different ways of modeling lookup tables. Lets go through each one of them in this blog entry.

1. Physical Lookups – To understand physical lookups, lets take a very simple example given below

We have 2 tables, CUSTOMERS and CUSTOMER_LKP. CUSTOMERS table has all the details of a customer with CUST_ID being the unique primary key. CUSTOMER_LKP has 3 columns CUST_ID, CUST_INCOME_LEVEL and CUST_MARITAL_STATUS with CUST_ID being the primary key. The main difference between these 2 tables is, not all customers in the main CUSTOMERS table have a corresponding income level and marital status.

To model the CUSTOMER_LKP as a lookup table, we need to first define a primary key. If there is more than one column acting as a primary key, ensure that the key contains all the columns. In the physical layer, there is no join needed on the CUSTOMERS and the CUSTOMER_LKP table.

Now, in the Business Model and Mapping layer, lets create a new column called Customer Income Level. After that is created, lets go to the LTS mapping and apply the following the function. If you have more than one column as a primary key, the order of columns used in the key should match with the column order in the Lookup function.

Lookup( SPARSE "ORCL - SH".."SH"."CUSTOMER_LKP"."CUST_INCOME_LEVEL" , 'No Income Defined', "ORCL - SH".."SH"."CUSTOMERS"."CUST_ID")

What we have done here is we have directly referenced the lookup value column in our lookup function. Since not all customers have an income level set, SPARSE is used. The basic syntax of lookup functions is given below

LOOKUP ( SPARSE/DENSE #Lookup Value column from the Lookup Table#, #Default Value if there is no lookup value in the Lookup table# (only needed for SPARSE lookups), #Primary key columns from the main table )

Lets now create a report as shown below

Wherever the Customer Income Level is not defined those customers get defaulted to No Income Defined value. If we look at the underlying database query, there would be a left outer join that would be pushed automatically into the query due to this lookup function.

In the Physical Lookups option, the lookup operations are pushed to the database layer (wherever possible). Lets now look at the 2nd approach where the lookup operations will be pushed to the BI Server layer.

2. BMM Lookups:

11g now supports doing lookup operation in its own memory by modeling a logical table as a lookup table. For example, lets consider the below currency conversion exchange rates table.

This rates table has a composite primary key. To use the BMM lookups, lets create a new logical table in the BMM layer to hold the exchange rates table as shown below

To denote a table as a BMM lookup table, we need to enable the lookup option (if you notice, there is no more bridge table check box option like in 10g but just a lookup option)

When we enable a logical table as a lookup table, that means that this logical table does not require any BMM joins to either the fact or the dimension. So we can now have standalone tables in the BMM layer in 11g.

After enabling this, lets create a new logical column for extracting the rates. Since we will have a rate for every country and day, we will use DENSE lookups in this case. The function used for doing this is given below

Lookup ( DENSE "SH - Lookups"."Rates Lookup"."RATE" , "SH - Lookups"."Customers"."COUNTRY_ISO_CODE", "SH - Lookups"."Times"."TIME_ID" )

The syntax for the lookup functions remain the same as the one that we used in the physical lookups. But here we need to use logical table names instead of physical table names. To use this rate as part of a FX restatement, multiply the measures with the above rate lookup column. Lets now create a report and look at the query generated

The BMM lookup is now fired as a separate query. BI Server will do the in-memory joins between the rates and the measures & will do the aggregation as well.

You might have missed it late on Friday afternoon, but the long-awaited 11gR1 release of Oracle Business Intelligence is now available for download on OTN. Three years in the making, this new release of Oracle’s business intelligence platform provides an updated user interface, better integration with Essbase, new OLAP capabilities, the Action Framework for integration with applications and web services, and improved management through Enterprise Manager and WebLogic Server.

Rittman Mead have been one of the longest-running beta testers for this new release, and since the launch have made a wealth of information available at our OBIEE 11gR1 Resource Centre. Now that the product is generally available, keep an eye on this site for more in-depth articles on how to get the best out of this new release.

Rittman Mead also have two immediate offers for customers looking to implement OBIEE 11gR1. Our Oracle BI Training Days 2010 event, running in London, Atlanta and Bangalore, offers hands-on training on this new release led by our beta-testing team. Aimed at developers with a basic understanding of Oracle BI, this will be one of the first opportunities to get proper, in-depth training on this new release.

We also have a number of consulting packages for customers looking to upgrade, or implement OBIEE 11gR1 from scratch. Our OBIEE 11g Upgrade service takes you through the process of planning the new 11gR1 architecture, and then moving your existing metadata, reports and dashboards to the new platform, whilst our OBIEE 11g Implementation service is aimed at organizations implementing Oracle BI for the first time. We also have a number of free and trial services, details of which, together with our consulting offerings, are in this datasheet.

Finally, it’s Oracle Open World 2010 in San Francisco soon, and Rittman Mead are proud to be delivering ten sessions over the week, on OBIEE 11g together with supporting technologies such as Oracle Data Integrator, Oracle Golden Gate and Oracle data warehousing. Keep an eye on this blog for full details of these sessions.

One of the significant changes in the BI EE 11g is in the way clustering is done. Mark has covered horizontal clustering (using the scale-out option in the installer) in his previous post here. The entire process of clustering is much more automated in 11g and is much easier to manage & maintain. BI EE 11g supports 2 kinds of clustering

1. Vertical Clustering – Multiple instances of BI Server, Presentation Services etc on a single box
2. Horizontal Clustering – Multiple instances of BI Server, Presentation Services etc on multiple boxes

Vertical Clustering is new to 11g as this is something that 10g did not support(though there were unsupported work-arounds available). Vertical Clustering is preferred in cases where we want to make optimal use of the hardware & provide fault tolerance. This does not provide high availability when the server itself goes down. In this type of clustering generally 3 components are clustered

1. BI Server
2. BI Presentation Services
3. Java Host

That is, all the components that can act in active-active configuration can be clustered in Vertical Clustering. Other components like Scheduler, Cluster Controller which work in active-passive fashion are generally not clustered vertically.

In this blog entry we will be looking at how Vertical Clustering is done. One important point to note is, the concepts of clustering itself haven’t changed much.

We start with logging into the enterprise manager FMW control and navigating to the deployment section. Since we are doing vertical clustering, we do not need to have a shared drive for the repository & the web catalogs. But again its better to identify a drive that can potentially be shared in the future with other machines as Vertical Clustering does not provide high availability(for future migration to horizontal clustering).

Once the drive is identified (one for RPD and the other for Web Catalog), copy the web catalog to the shared drive. In the deployment section of the Enterprise Manager, enter the shared directory details of both the RPD and the Web Catalog. Upload the repository (RPD) into the shared drive using the enterprise manager.

After uploading the repository into the shared drive, restart the BI Server. After the restart you will notice that the repository that has been uploaded in the shared drive would automatically be synchronized to the BI Server.

After making this change navigate back to the Capacity-Scalability tab and increase the number of BI Servers and Presentation Services to 2 as shown below

This will automatically create new instances(within the main instance) in BI EE for both the BI Server and Presentation Server(after Activating the changes). We can validate this by looking at the number of directories under {ORACLEINSTANCE}/bifoundation/OracleBIServerComponent and {ORACLEINSTANCE}/bifoundation/OracleBIPresentationServerComponent

You can see 2 new directories created for the new BI Server and Presentation Server that we added through the EM. Lets now start these new components through the capacity management interface

In order to test the cluster, lets open up the DSN and configure the Admin Tool DSN to connect through the cluster controller as shown below

Lets now login to the Admin Tool and see the status of all the new servers in the Cluster Manager

As you see, the new components that we added for vertical clustering have been enabled automatically. This entire process now has become a lot easier. Also, Presentation Services plug-in and Presentation Services have been decoupled. So, essentially a single web server can communicate to multiple presentation services in a round-robin fashion without the need for load balancer. In horizontal clustering with multiple machines, load balancer will be required though in order to switch between different HTTP servers based on the incoming load. The screenshot below shows the sessions in multiple presentation services on a single box.

Next up is a blog post on the new Lookup feature followed by the Double Column feature available within the repository.

In a previous posting in this series, I looked at OBIEE 11gR1’s architecture at a high level, and yesterday following the official launch I took a look at the installation process. I briefly I touched on a few administration tasks such as starting and stopping the OBIEE components, but in this posting I want to look at this topic in more detail, looking at where all the various files have gone and how you perform basic administration on the system.

First up, as an OBIEE 10g administrator, your administration tasks were mostly performed either through the Administration tool, the web-based Presentation Server administration screen, or through editing files in the filesystem. There were something like 700 or so configuration options spread over multiple tools and configuration files, with some options (users and groups, for example) embedded in unrelated repositories (the RPD). OBIEE 11g addresses these by where possible moving administration and configuration into Fusion Middleware Control (also referred to as Enterprise Manager).

To start off with something familiar, the Administration tool that was present in OBIEE 10g is also present in 11g, is also Windows-based, and is used to maintain the semantic model used by the BI Server. Here’s a screenshot of the 11g version, showing the SampleApp and some of my own subject areas:

This tool is more or less the same, and has some enhancements in terms of dimension handling, new data sources and the like. A big change though is around security; now when you bring up the Security Manager dialog, it looks like this:

Users and Application Roles (roughly analogous to groups in 10g) are now defined in the WebLogic Server admin console, and you use the Security Manager to define additional links through to other LDAP servers, register custom authenticators, and set up filters and other constraints. In the above screenshot, the users shown in the Users list are those that are held in WebLogic Server’s JPS (Java Platform Security) service, and there are no longer any users and groups in the RPD itself. Notice also that there is no Administrator user – instead the standard administrator user is the account that you set up as the WebLogic Server administrator when you installed OBIEE, which typically has the username weblogic. There are also two additional default users; OracleSystemUser is used by the various OBIEE web services to communicate with the BI Server, and BISystemUser is used by BI Publisher to connect to the BI Server as a data source (both default to the same password as the weblogic admin user you set up during the install).

If you switch to the Application Roles tab, you’ll also see a list of new default application roles; BISystem, BIAdministrator, BIAuthor and BIConsumer, which are used to grant access to Presentation Server functionality and also encompass the old XMLP_* groups that you used to get in 10g that were used to manage access to BI Publisher. There’s also AuthenticatedUser which is the same as found in the previous release. So how do you create a new user in OBIEE 11g? For that you’ll need to start up the web-based WebLogic Server admin console.

To create a new user, log on to the WebLogic Server admin console (http://localhost:7001/console) and bifoundation_domain > Security Realms from the Fusion Middleware Control menu. Then from the list of security realms, select myrealm, and then from the Settings for myrealm dialog select Users and Groups, and then Users, from the tab menu, You are then presented with a list of existing users.

Pressing the New button brings up a dialog where you can enter the user’s details, and you can also use the Groups tab to define a group for the user, or assign the user to an existing group. Security is quite a bit of a big change in 11g and in addition, we have the Application Roles setting that you saw in the Security Manager screenshot, which you then map to the groups in WebLogic. I’ll cover security in a future posting, but for now, this is how to define basic users and groups.

Another area that’s changed significantly where configuration files and metadata files are stored. In OBIEE 10g, you had two top-level folders, $ORACLEBI and $ORACLEBIDATA. $ORACLEBI (typically installed, for example, in c:\oracle\oraclebi) would hold binaries and configuration files for the BI Server, plus other components such as BI Publisher and JavaHost. $ORACLEBIDATA (installed, typically at c:\oracle\oraclebidata) would hold binaries for the Presentation Server, config files for the Presentation Server, plus cache files and temporary files for the BI Server. In OBIEE 11gR1 the filesystem changes, with the diagram below showing the high-level filesystem layout for a Windows installation at c:\Middleware:

So where are the key files that we are used to working with? Taking my installation on Microsoft Windows 2003 Server, and with OBIEE 11gR1 installed at C:\Middleware, here’s where my key files are located:

• RPD Directory : C:\Middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
• NQSConfig.INI : C:\Middleware\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1\nqsconfig.INI
• NQClusterConfig.INI : C:\Middleware\instances\instance1\config\OracleBIApplication\coreapplication\NQClusterConfig.INI
• nqquery.log : C:\Middleware\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1\nqquery.log
• nqserver.log : C:\Middleware\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1\nqserver.log
• nqsserver.exe : C:\Middleware\Oracle_BI1\bifoundation\server\bin\nqsserver.exe
• Webcat Directory : C:\Middleware\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\
• instanceconfig.xml : C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml
• xdo.cfg : C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\xdo.cfg
• sawlog0.log : C:\Middleware\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1\sawlog0.log
• sawserver.exe : C:\Middleware\Oracle_BI1\bifoundation\web\bin\sawserver.exe

Taking a look at tthe NQSConfig.INI file, whilst the format is the same, notice how many of the parameters are now marked as being managed by Enterprise Manager (Fusion Middleware Control):

Now these are parameters that you’re supposed to change only through Fusion Middleware Control. You can change them manually, but they’ll get overwritten by the WebLogic Server admin server when you restart WebLogic. You can override this behaviour so that changes you do make to these particular parameters don’t get overwritten, but then you’ll have to remember to copy changes to all the nodes (in OBIEE 11g, clustering is automatically enabled). Not all parameters are managed in this way (in the screenshot above, DATA_STORAGE_PATHS, POPULATE_AGGREGATE_ROLLUP_HITS and USE_ADVANCED_HIT_DETECTION still have to be changed by manually updating this file, but over time the plan is to move more and more parameters to management through Fusion Middleware Control.

To change the managed parameters, go to Fusion Middleware Control, log in as an administrator user (weblogic/welcome1 in my case), and click on the coreapplication node under the Business Intelligence menu entry, so that an overview of the system components status is shown:

From this screen, you can stop, start and restart all of the system components (BI Server, Presentation Server etc) via OPMN. From this point, you can then click, on the Capacity Management, Diagnostics, Security or Deployment tabs to perform further maintenance.

• Capacity Management has four further sub-tabs, and can show Metrics gathered via DMS; the Availability of all the individual system components (allowing you to stop, start and restart them individually); Scalability lets you dynamically increase the number of BI Servers, Presentation Servers, Cluster Controllers and Schedulers in the cluster in conjunction with the “scale out” install option, and Performance lets you turn caching on or off and modify other parameters associated with response time.
• Diagnostics has two sub-tabs; Log Messages shows you a cluster-wide view of all server errors and warnings, and Log Configuration lets you limit the size of logs and what information gets included in them.

• Security is used for enabling SSO and selecting the SSO provider
• Deployment has five sub-tabs; Presentation lets you set dashboard defaults around page tabs, section headings etc; Scheduler sets the connection details for the scheduler schema; Marketing is for configuring the Siebel Marketing Content Server connection; Mail is for setting up the mail server that’s used by Delivers for email alerts. The most interesting tab is Repository though, as this is where you upload new RPDs for use by the BI Server.

When you first navigate to this tab, the option to upload a new RPD is grayed-out. This is because you have to press the Lock and Edit Configuration button, which stops anyone else from attempting the same operation at the same time. The default installation of OBIEE 11gR1 comes with an RPD called SampleAppLite, and I want to replace this with my own RPD, developed offline previously.

After pressing Lock and Edit Configuration, an “in progress” message comes up, and then you can start uploading your new RPD file. In the example below, I’ve used the Browse button to pick up a new RPD called OBIEE11g_Examples.rpd, and I’ve entered the RPD password into the text boxes below (remember in 11g, the RPD itself has a password, rather than you giving the password of an RPD user with admin privileges as you did with 10g).

Pressing Activate Changes will firstly bring up a message saying that the changes will be applied regardless of whether you close your browser window, and shortly afterwards, a second message is displayed saving that the action is completed successfully.

Then if you check the NQSConfig.INI file, you should see your change written to the file. (Technically, the Activate Changes process actually writes the changes to an intermediate file, which the Admin Server then polls regularly and once it sees the changes, writes them to the NQSConfig.INI file).

At this point though, as with OBIEE 10g, you still need to restart the BI Server for this change to take effect. To do this, click on the Restart to Apply Recent Changes link at the top of the web page, which takes you to the Overview page for the coreapplication system components in Fusion Middleware Control. From this point, you can either restart all components (which is a bit of overkill), or switch to the Capacity Management tab, then Availability sub-tab, and restart just the BI Server system component. Once you’ve done this, the new RPD will become active. Note also from the screenshot above that RPDs get automatically versioned, with each upload of a particular RPD being saved in the BI Server repository directory with a sequence number appended to it.

Many administration tasks in 11g are the same as 10g. For example, the log level for a particular user is still defined in the security manager, and you still view the query log (nqquery.log) either through the filesystem, or through the Manage Sessions link in the Presentation Server administration screen. Usage tracking is still manually set up through the NQSConfig.ini file, though the schema it uses is automatically created at installation time through the RCU (Fusion Middleware Repository Creation Utility). In 11gR1, only a subset of these administration tasks are performed through Fusion Middleware Control, but as the releases stack up, more of these functions will move to this environment, something that’s more important now that clustering is turned on by default.

Finally, the Administration screen in the Presentation Server web interface has had a visual overhaul with the 11g release. Some functions, such as the one to reload server metadata in 10g, have moved from Answers into this screen, and new functions have been added to manage, for example, the mapping feature.

Once you get beyond the main menu screen, the way the functions work hasn’t changed much in this release. Some of the dialogs have visually changed, but as you can see in the screenshot below, the functions work in much the same way as 10g, and you can see the Application Roles that were visible in the Security Manager at the start of this posting being used to grant access to Presentation Server functionality.

So that’s it for basic administration. Take a look at our OBIEE 11gR1 Resource Centre for a complete listing of our 11g postings, and we’re also running a special, three-day Oracle BI 11g Training Days event in Atlanta, London and Bangalore later in the year if you’re after in-depth, hands-on training on this new release. For now though, I’m going to hand-off to Venkat for a series of postings on the new features in the 11g BI Server.

Venkat broke the news earlier today that the full, production release of OBIEE 11gR1 is now available for download on OTN, and in this posting, I’m going to look at the process of installing and initially configuring OBIEE 11gR1. Now I’m not going to go through every step in the process as it’s fairly self-explanatory, but I wanted to pick out some of the changes since OBIEE 10g, highlight some of the interesting bits and talk about what it takes to set up a self-contained demo environment. In later postings I’ll be looking at setting up the RPD, managing the environment using Enterprise Manager, and taking a look at some of the new front-end features.

In my demo environment, I’ve got Oracle Database 11gR2 already installed on Windows Server 2003 Enterprise Edition 32-bit. I’m actually using VMWare Fusion (as I’m on a Mac; on Windows I’d use VMWare Workstation or Server instead) to which I’ve allocated 4GB of RAM. This is the first big change compared to OBIEE 10g, which you could generally get to run, along with a local Oracle database, within 1.5GB – 2.GB of RAM. We’ll see later on just what’s taking up the additional memory.

There are two main stages to the OBIEE 11gR1 installation:

1. Firstly, we use the RCU (Oracle Fusion Middleware 11g Repository Creation Utility) to set up the BIPLATFORM schema to support OBIEE 11gR1, and
2. Then, we install OBIEE 11gR1, including the Oracle WebLogic Server J2EE application server that takes the place of OC4J and Oracle Application Server 10g in this release.

If you’ve ever installed any Fusion Middleware 10g or 11g products (such as Oracle Application Server 10g, or some of the new 11g products such as Discoverer 11g), you’ll be familiar with the Repository Creation Utility. This is a Java application, like the Universal Installer, that sets up one or more schemas that need to be in place for the products you are installing. For this release, you can install the required schema into either Oracle Database 9i, 10g or 11g, Microsoft SQL Server or IBM DB/2. I start the RCU, select the option to create schemas (as opposed to dropping them), and then choose the Oracle Database option and enter the required credentials.

The RCU then carries out a quick check on the database you intend to use (my 11gR2 database didn’t need any changes, and I’d installed it fresh with the data warehousing option), and then prompts you to choose which schemas to install. This version of the RCU has an entry for Oracle Business Intelligence, which has a single entry for a component called Business Intelligence Platform. The MDS schema under AS Common Schemas is selected automatically, and is required by the underlying Fusion Middleware platform.

The prefix section is so that you can install multiple sets of schemas for different environments on the same database, but for now I leave this at the default value of DEV, for “development”. In OBIEE 11gR1, this DEV_BIPLATFORM schema contains all of the tables that in prior releases, were in schemas such as S_NQ_ACCT, S_NQ_SCHED and the BI Publisher scheduler schema, and it also contains tables that are used by OBIEE 11gR1 additional products such as the EPM Suite Calculation Manager, Oracle Real Time Decisions and other supporting products.

The database is then checked for compatibility, and then another screen is displayed, where you can fine-tune the names given to the tablespaces used for the DEV_BIPLATFORM schema plus other ones that are needed by Fusion Middleware 11g. I just leave these at the default values and allow a separate tablespace to be created for each schema. The RCU then creates the tablespaces and temporary tablespaces, and then creates the schemas and their tables. This process replaces all of the separate scripts that you have to run to set up the Delivers scheduler tables, the BI Publisher scheduler tables, the usage tracking accounting tables and so on that you had to do as post-install steps with OBIEE 10g. Once this is done, you’re ready to install OBIEE 11g proper.

Once the RCU process is complete, it’s time to install OBIEE 11g proper. After starting up the OBIEE 11gR1 installer, the first big change compared to 11g is that we’re now using the 11g version of the Oracle Installer (as used in Oracle Fusion Middleware 11g, and Oracle Database 11gR2) as opposed to the old Siebel-derived one. This new installer prompts you to use the RCU if you’ve not done so already, and lists out the tasks down the left hand side that you’ll need to complete.

The first page proper of the new installer asks you to select from one of three install types:

1. Simple Install, which is what most developers testing out the software will require, installs a basic OBIEE 11gR1 system along with WebLogic Server, using default values for things like domain names, port numbers and the like.
2. Enterprise Install gives you a few more options than a Simple Install, letting you customize the name of the WebLogic domain, the WebLogic Managed Server, the ports that are used and so on. If you need to vary away from the defaults, or “scale out” an existing installation, choose this option (but for most developers, Simple Install will be fine)
3. Software Only Install will install the OBIEE 11gR1 software (but not WebLogic Server, you need to have this already installed), but doesn’t set up any “instances” of OBIEE, a similar situation to when you install the Oracle Database binaries but don’t configure a database instance.

Selecting the Simple Install option will move you on to the next page for the installer, which then prompts you to select a “middleware home” for your installation. All of the OBIEE 11gR1 installation will go into this (there are no longer OracleBI, or OracleBIData directories), including the binaries and the “OBIEE instance” files, creating a separation in 11gR1 between those files that are static – the binaries and configuration file templates – and those that change over time, which include the configuration files, repositories, XML files and web catalog.

Pressing Next then brings you onto a screen that prompts you for a username and password for the WebLogic Server, and OBIEE, administrator. In 11gR1 there is no longer the concept of an Administrator user, and instead you create an administrator user here that becomes the default admin login into OBIEE (and the various WebLogic Server administration tools). I choose the username weblogic password welcome1, and press Next to proceed.

I’m now asked to confirm the products that I want to install. In later releases of OBIEE 11g, this list may include products such as Oracle Essbase and the other EPM Suite products, but for now you can select Business Intelligence Enterprise Edition, Business Intelligence Publisher, and Oracle Real Time Decisions. I select all three and press Next to proceed.

Next I’m prompted to enter connection details to the database in which I set up the DEV_BIPLATFORM schema. I look back to the connection details I used for the RCU and enter these details, pressing Next to continue.

After running a couple of checks and asking me for my Metalink login (optional, can be used for notification about available patches), the installer then summarises what it’s going to do next, and invites me to press the Install button to start the installation.

The installation process then begins, and starts by installing the supporting WebLogic Server files required for the infrastructure layer.

It then proceeds to install what in 11gR1 are called the “System Components”, which in this release are still C++ applications but are managed now by WebLogic.

Once installation has finished, the configuration process starts. Initially, this is concerned with instantiating the WebLogic domain and managed server, together with supporting technologies such as Enterprise Manager and Oracle Web Services Manager.

Then, the BI Server, BI Presentation Server and other system components are registered within the WebLogic Server managed server, and OPMN is configured so that it can stop, start and monitor them (more on this in future blog posts).

Other components that were included as part of the components I selected are then configured, and in the screenshot below we can see the parts of OBIEE that run as J2EE applications (referred to in 11gR1 as “Java Components” as opposed to “System Components”) being deployed to the WebLogic Server managed server. Both the Java components and system components will later on be managed by Enterprise Manager and the WebLogic Server admin console.

Assuming all of this completes without error, you are finally presented with a summary of what’s been installed on your system, like this:

So what does the Enterprise Install option do then, beyond and above this? Let’s go back to the start of the installation process and see what extra choices we get.

After selecting the Enterprise Install option, you are firstly asked whether you want to Create a new BI System, or Scale Out BI System. We’ll get onto the scale out option in a moment, but for now we select the option to install a new system, and enter the WebLogic / OBIEE administration user details, and also in this instance give a name to the WebLogic domain that’ll be set up.

I choose the same user name and password as before for this account, and leave the domain name at the default. Pressing Next then brings me to the next set of choices, where I supply the location of the middleware home, the domain home and the instance home and name, which is where the various configuration and repository files will be stored for the new OBIEE instance.

After this screen, you are then prompted to select from the same product list as in the Simple Install option. After selecting the same products as before, the next new screen is one where you can either choose to use the default port numbers, or you can point the installer to a file that contains your own custom port mappings (this is a standard process in the Oracle Universal Installer, but the Simple Install option just used the defaults).

Beyond this point, the installation process is the same as with the Simple Install option, and the installer will continue to install, and then configure, the options you have chosen.

Going back a moment to the start of the Enterprise Install option, you may remember that there was an option to “scale out” an existing OBIEE system. What this does is allows you to add more OBIEE and WebLogic components to an existing OBIEE 11gR1 domain, and we’ll look at this option in more detail in a later blog posting. In a similar vein, the third option, for a Software Only Install, lets you install just the OBIEE components (system components and Java components) into an existing WebLogic installation, like this:

This is useful if you’ve already got a WebLogic Server installation somewhere and you want to configure it to run OBIEE as well, and we’ll look at these last two areas in some future blog postings when we address the topic of scalability, clustering and high availability. For now though, we’ve got an OBIEE 11gR1 system installed, and once we’ve done a reboot (not mandatory, but can often free up a bit of memory after the install process) we can select the Run BI Services entry from the Oracle Business Intelligence Windows Start Menu entry to start all the processes.

If you take a look at the list of Windows services before this menu entry is selected, you can see that the list of servers – Oracle BI Server, Oracle BI Presentation Server and so on – that were listed as part of 10g, have now been replaced by two new ones in this release.

Oracle Process Manager (Instance1) is the service for OPMN, and controls the status of the system components that have replaced the individual services for components such as the BI Server and Presentation Server. Oracle WebLogic NodeManager (c_Middleware_wlserver_10.3) is a service that starts up and shuts down the individual WebLogic Server managed servers, not strictly necessary on a one-node system but essential for multi-node systems, so that an individual WebLogic Server admin server can start up remote managed servers.

Once you run the Windows Start Menu entry, a DOS box opens up which allows you to type in the WebLogic Server admin username and password, which you set earlier on in the install process.

Once this is entered, a WebLogic Server script then starts the admin server, then the managed server, the OPMN service any other components, and about five minutes later you should be able to log into OBIEE 11g, again using the WebLogic Server admin username and password.

So there we have it. Tomorrow, I’ll be writing about basic OBIEE 11gR1 administration processes including a guide to where all the key files have gone, which will then be followed next week by a series of postings by Venkat on new features in the BI Server 11g.

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.

Looks like BI EE 11g silently made it to OTN yesterday. The software is available for download now. It can be downloaded from here.  Currently its available only for Windows (32 bit & 64 bit) & Linux (32 bit & 64 bit).

Watch out this blog for detailed postings on new features and the changes in 11g.

I’m dogmatic about certain aspects of data warehousing. For instance, fact tables should be range partitioned by DATE. I tell my clients all the time: you will have a very difficult time persuading me otherwise. But they always try: they argue about all the attributes that are more pervasive than DATE: customer classes, transaction types, etc., etc. But I’m just not buying it. We are building data warehouses, and the third rail of the Soul of the Data Warehouse is how it handles time.

If you agree with me about this precept (and I really think you should), this is still not the end of the story. We must charge ahead into the lion’s den of a debate that has been raging in the Oracle data warehousing world for years: do we make the surrogate key of our date dimension a NUMBER, or do we make it a DATE? It’s funny… I remember this being the first question I ever posed to Mark years and years ago, and he did a blog entry that evolved out of our email communication. I don’t see the entry on the blog any more… it must have been lost in The Great Blog Disaster. Pity.

The choice between NUMBER and DATE bubbles up from the two streams at work in the Oracle Data Warehousing community: the data warehousing folks, and the Oracle folks. Ralph Kimball argues that the surrogate key of the date dimension should be numeric. In the Data Warehouse Lifecycle Toolkit book (or at least, in my edition of it), Kimball basically makes the argument that numbers require less space than dates. That one never did too much for me. However, in his Latest Thinking on Time Dimension Tables design tip, he makes a better argument: if our surrogate key is a DATE, then how do we handle “Not Applicable” type rows? This one has teeth, and I think that most designers who struggle with this decision point to this issue. If we use an actual DATE as our surrogate key, then what value can we use that actually means “no date at all”?

Oracle experts like Tom Kyte argue that “dates belong in DATES”. (If you look really hard at this post, you can see a younger and more naive version of myself weighing in on the debate… and also, apparently, not knowing how to gather histograms with DBMS_STATS. Oh well.) As Tom demonstrates on that post, the optimizer just plain works better when dates are stored in DATE datatypes.

I’ve typically been on Kyte’s side in this debate, both from a performance and a maintenance perspective. I’ve parted ways with Kimball on this point and urged my clients to build date dimensions with DATE surrogate keys, calling the column something like DATE_KEY. For the ‘NA’ types of dimension records, I use a wacky DATE value for DATE_KEY, such as ‘12/31/9999′ or ‘01/01/0001′. Think of this as the equivalent of -1 if the surrogate key were actually numeric. Being a surrogate key… it really doesn’t matter what value it contains: we just need to know the column name so we can construct the correct JOIN syntax. Then, I’ll build another DATE column in the table called SQL DATE, and this is the one that I expose to the reporting layer. Since SQL DATE does not have to serve as the primary key, it’s fine for it to be a NULL if desired.

In subsequents posts, I’ll examine new partitioning features in 11g, including interval partitioning (which Pete Scott recently blogged about), and also reference partitioning, and whether these enhancements provide more options to this historically binary choice.

If you had looked at my blog entry here, i would have shown a way of reporting on Oracle OLAP 11g using the newly introduced Essbase XOLAP. As mentioned there, one of the biggest advantages of using Essbase is its tight integration with Excel through Smart View. Unfortunately, in the case Oracle OLAP, the excel add-ins were based on the BI Beans technology which is more or less deprecated now. Also the excel add-ins of Oracle OLAP were not as powerful as the Smart View add-in. In my previous blog entry i had shown how XOLAP interpreted the MDX fired from Visual Explorer/Smart-View and then converted them back to the corresponding SQL calls to Oracle OLAP. The SQL’s generated by XOLAP were OLAP aware i.e multiple SQL’s were generated to hit the correct pre-aggregated intersections rather than doing aggregations through SQL. There are 2 biggest drawbacks with this approach. They are

1. It required an Essbase License
2. Any change to the OLAP metadata required an XOLAP cube rebuild within Essbase

Some time last year, Simba Technologies announced an MDX OLE-DB provider for Oracle OLAP. So far i did not get an opportunity to test this though it looked promising. Couple of weeks back we got an evaluation copy from Simba to test the driver (i will have to thank Simba and their Oracle OLAP – MDX provider team for providing us with an evaluation copy). This driver basically provides an ability for Excel users to leverage the power of Oracle OLAP using the Excel Pivot Tables/Charts etc. At a high level this driver does the following

1. End users can use the native Excel functionality to create charts/pivot tables etc
2. The charts/Pivot tables generate MDX (standard OLE-DB based microsoft MDX)
3. Simba driver then converts the MDX to one or more SQL calls to the Oracle OLAP

In this blog entry we will basically see how this driver works. The install process is quite straightforward where we are taken through a set of steps that will setup the OLE-DB driver. This driver will work only for Oracle OLAP versions 11.1.0.7 or above. Then we start off with setting up a DSN to connect to the Oracle OLAP database. Ensure that the client driver of Oracle used in the DSN is atleast of the 11.1.0.7 version.

Once this is setup, from Excel use the Data Connection Wizard to setup a OLE-DB connection through the Simba MDX driver.

This should automatically connect us to the Oracle OLAP schemas.

As you see, we can save a connection to a cube and then the same connection can be reused later for creating more reports. Lets start with creating a simple pivot table report (using native Microsoft Excel Pivot tables)

After this if you notice, we will now be having metadata of Oracle OLAP exposed within the Pivot Table member selection panels. This is very similar to Hyperion Visual Explorer where we are shown all the levels in a dimension and all the hierarchies as well. We can pick and choose either a specific level or we can choose members from multiple levels by applying proper filters. Lets create a very simple report as shown below

As you see, we now have the ability to drill using the native MS pivot table functionality. Lets look at the MDX fired to generate the above query.

SELECT {[MEASURES].[SALES],[MEASURES].[SALES_YTD]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[TIME].[CALENDAR].[ALL_YEARS].[ALL_YEARS]})}), Hierarchize({DrilldownLevel({[PRODUCT].[STANDARD].[ALL_PRODUCTS].[ALL_PRODUCTS]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, [TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_END_DATE], [TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_TIME_SPAN], [TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_LONG_DESCR], [TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_SHORT_DESC], [TIME].[CALENDAR].[CALENDAR_YEAR].[END_DATE], [TIME].[CALENDAR].[CALENDAR_YEAR].[TIME_SPAN], [TIME].[CALENDAR].[CALENDAR_YEAR].[LONG_DESCRIPTION], [PRODUCT].[STANDARD].[DEPARTMENT].[DEPARTMENT_LONG_DESCRIPT], [PRODUCT].[STANDARD].[DEPARTMENT].[DEPARTMENT_SHORT_DESCRIP], [PRODUCT].[STANDARD].[DEPARTMENT].[LONG_DESCRIPTION] ON ROWS FROM [SALES_CUBE] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS,2

As you see the MDX retrieves all the necessary Oracle OLAP level properties as MDX intrinsic properties. This is very interesting. The MDX driver basically does a metadata level mapping between MDX and Oracle OLAP. I am not sure how much of this is documented(in terms of MDX to SQL conversion calls) but again this looks very promising. Now lets look at the SQL that is fired back to Oracle OLAP. The driver can generate multiple SQL Queries for a single MDX call. This is very similar to BI EE 11g (which i shall be covering later once BI EE 11g is GA) where while doing a drill to multiple levels we will see multiple SQL calls being generated.

The first 2 SQL’s generated(for this report) will be for constructing the metadata or the member list for all the dimensions that are part of the query

SELECT 'OLAPTRAIN' AS CATALOG_NAME, 'SALES_CUBE' AS CUBE_NAME, members.DEPTH AS LEVEL_NUMBER, members.HIER_ORDER AS MEMBER_ORDINAL, members.DIM_KEY AS MEMBER_NAME, 1 AS MEMBER_TYPE, SHORT_DESCRIPTION AS MEMBER_CAPTION, 1 AS CHILDREN_CARDINALITY, CASE WHEN (members.PARENT IS NULL) THEN NULL ELSE members.DEPTH-1 END AS PARENT_LEVEL, CASE WHEN members."CALENDAR_QUARTER" IS NOT NULL AND members.LEVEL_NAME != 'CALENDAR_QUARTER' THEN '[TIME].[CALENDAR].[CALENDAR_QUARTER].[' || members.PARENT || ']' WHEN members."CALENDAR_YEAR" IS NOT NULL AND members.LEVEL_NAME != 'CALENDAR_YEAR' THEN '[TIME].[CALENDAR].[CALENDAR_YEAR].[' || members.PARENT || ']' WHEN members."ALL_YEARS" IS NOT NULL AND members.LEVEL_NAME != 'ALL_YEARS' THEN '[TIME].[CALENDAR].[ALL_YEARS].[' || members.PARENT || ']' ELSE (CAST (NULL AS VARCHAR2(1))) END AS PARENT_UNIQUE_NAME, (CAST (NULL AS VARCHAR2(1))) AS DESCRIPTION , members.CALENDAR_YEAR_END_DATE AS PROPERTY_4 , members.CALENDAR_YEAR_TIME_SPAN AS PROPERTY_5 , members.CALENDAR_YEAR_LONG_DESCR AS PROPERTY_6 , members.CALENDAR_YEAR_SHORT_DESC AS PROPERTY_7 , members.END_DATE AS PROPERTY_20 , members.TIME_SPAN AS PROPERTY_21 , members.LONG_DESCRIPTION AS PROPERTY_22 , 'TIME' AS DIMENSION_NAME , 'CALENDAR' AS HIERARCHY_NAME , members.LEVEL_NAME AS LEVEL_NAME FROM "OLAPTRAIN".TIME_CALENDAR_VIEW members WHERE members.LEVEL_NAME = 'CALENDAR_YEAR' ORDER BY MEMBER_ORDINAL, PARENT_UNIQUE_NAME, MEMBER_NAME SELECT 'OLAPTRAIN' AS CATALOG_NAME, 'SALES_CUBE' AS CUBE_NAME, members.DEPTH AS LEVEL_NUMBER, members.HIER_ORDER AS MEMBER_ORDINAL, members.DIM_KEY AS MEMBER_NAME, 1 AS MEMBER_TYPE, SHORT_DESCRIPTION AS MEMBER_CAPTION, 1 AS CHILDREN_CARDINALITY, CASE WHEN (members.PARENT IS NULL) THEN NULL ELSE members.DEPTH-1 END AS PARENT_LEVEL, CASE WHEN members."COUNTRY" IS NOT NULL AND members.LEVEL_NAME != 'COUNTRY' THEN '[GEOGRAPHY].[REGIONAL].[COUNTRY].[' || members.PARENT || ']' WHEN members."REGION" IS NOT NULL AND members.LEVEL_NAME != 'REGION' THEN '[GEOGRAPHY].[REGIONAL].[REGION].[' || members.PARENT || ']' WHEN members."ALL_REGIONS" IS NOT NULL AND members.LEVEL_NAME != 'ALL_REGIONS' THEN '[GEOGRAPHY].[REGIONAL].[ALL_REGIONS].[' || members.PARENT || ']' ELSE (CAST (NULL AS VARCHAR2(1))) END AS PARENT_UNIQUE_NAME, (CAST (NULL AS VARCHAR2(1))) AS DESCRIPTION , members.ALL_REGIONS_SHORT_DESCRI AS PROPERTY_9 , members.ALL_REGIONS_LONG_DESCRIP AS PROPERTY_10 , members.LONG_DESCRIPTION AS PROPERTY_12 , 'GEOGRAPHY' AS DIMENSION_NAME , 'REGIONAL' AS HIERARCHY_NAME , members.LEVEL_NAME AS LEVEL_NAME FROM "OLAPTRAIN".GEOGRAPHY_REGIONAL_VIEW members WHERE members.LEVEL_NAME = 'ALL_REGIONS' ORDER BY MEMBER_ORDINAL, PARENT_UNIQUE_NAME, MEMBER_NAME

Then the final query will be for generating the measure values.

SELECT SALES_CUBE_VIEW.SALES, SALES_CUBE_VIEW.TIME, SALES_CUBE_VIEW.PRODUCT FROM "OLAPTRAIN".SALES_CUBE_VIEW SALES_CUBE_VIEW WHERE SALES_CUBE_VIEW.TIME IN ('ALL_YEARS', 'CY2008', 'CY2010','CY2007','CY2009' ) AND SALES_CUBE_VIEW.PRODUCT IN ('ALL_PRODUCTS', '-518', '-519', '-520' ) AND SALES_CUBE_VIEW.CHANNEL = 'ALL_CHANNELS' AND SALES_CUBE_VIEW.GEOGRAPHY = 'ALL_REGIONS'

If you look at all the queries, they are all OLAP aware i.e. default member filters are applied properly and there is no additional aggregation that is pushed through SQL. This is very interesting and if there are customers using Oracle OLAP, this is one driver that can potentially be put to good use for Excel based reporting.

Currently looks like there is no way to fire custom MDX queries through the Excel 2007 that i have. So, i am not sure how the driver will behave/work when we push custom MDX aggregations like AGGREGATE, SUM etc. Also, i am not sure whether a mapping for all MDX functions(like intersect, union etc) to corresponding OLAP SQL calls exist. But I was told that custom MDX functions should also work well. It is just a case of Excel 2007 not supporting custom MDX queries for the native Pivot Tables.