BI & Warehousing

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.

I've been trying to build an image of OBIEE 11g recently, with not much success.

The process is as follows:
1. Fire up big-ass (to me anyway) Windows 2003/8 Server on Amazon.
2. Go through all the security hassle of Internet Explorer to download the software (I've since put it on it's own volume, and then attach that to my instance, so I don't have to do this 300 times)
3. Install 11gR2 database, software only.
4. DBCA to build database.
5. Net Manager to build the listener.
6. Create the weblogic metadata for OBIEE.
7. Install OBIEE.
8. Scream and yell when the configuration doesn't work.

Rinse and repeat 12 times.

I've gotten quite good at it.

Then I ran into this lovely error...TNS-03505: Failed to resolve name
Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.
Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.
I went through Net Manager and Net Configuration Manager about 1000 times. You know, instead of actually reading the error.

I thought I needed a Loopback Adapter. I didn't.

I started to go through the files by hand, sqlnet.ora, listener.ora, tnsnames.ora (which I'm much more confident with surprisingly)...it had to be something small.

# tnsnames.ora Network Configuration File:
c:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = TESTING)
     (SID = TESTING)
   )
 )

LISTENER=(Address=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))

I name all my sandbox databases "testing." Always have, probably always will. From the command line I issue:

SQLPLUS SYS/TESTING@TESTING AS SYSDBA

(Yes, I know I don't need to specify the user/password@service_name).

Spot it yet?

Could it be that my alias says: ORACLR_CONNECTION_DATA?

Yup, foiled again. ID10T in the house.

I have a dilemma. In the BI system that I’m working on I have the perfect application for the use of Expression Filters. It is almost as if someone specifically devised this problem in order to tempt me into using them, and I really am very tempted indeed. Let me explain. In the application which [...]

This post is almost a direct copy of my reply to the OBIEE EMG mailing list started by Mark Rittman about Initial Opinions on OBIEE 11g?.

I can't say I've had any success installing the software. So far I've tried on XP Pro (VM on laptop) and Server 2008 (AWS instance). I've chosen the Enterprise install each time.

The laptop I can understand, sort of, as it is severely underpowered. 3 GB RAM total for the machine. I understand the integration with WebLogic/Fusion, but it makes it that much more difficult for those starting out to get it up and running on their personal machines. I can still install the database on my laptop; in fact I have 3 or 4 versions (in VMs) on my machine right now. That's nice.

A couple of days ago, I fired up a cloud Windows Server 2008 server with 18 GB RAM. Installed the database. Created the BI metadata (rcu). Finally began the OBIEE install (Enterprise). It failed finally on one of the configuration steps (12 of 13), I believe it was one of the opmnctl processes.

That said, I haven't read the install documents from front to back yet. I have, however, installed a lot of Oracle software over the years, including EM, OAS (10g), etc. I can usually get the software up and running. This is a different beast though and is not for the weak.

I'll eventually read the docs, sooner rather than later.

On the bright side, installing the latest 11g forces people to learn a bit more about the interactions of the database, EM, WebLogic, etc. That's a good thing.

This is more for me since I seem to install it quite regularly.

I've been snagging the SQL from OBIEE query logs (nqquery.log), which doesn't come out too pretty.

So that I don't have to manually format 400 lines of SQL, I created a formatting template. Ctl - F7 and voila!

I do go back in and make small changes, which is why I am writing this. As I scroll down through the file and indent or change code, the GROUP BY clause is auto-generated, which is annoying to me.

To turn it off is easy (if you can remember, which I can't, which is why I write this).

Go to Tools - Preferences then look for Code Editor:



Expand that group and then go to Completion Insight:



Uncheck the box next to the arrow and you're done.

My hope is that in the next version of SQL Developer, we'll be able to call the SQL Beautifuler from the command line. That would be pure awesome.

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.

As you may know, OBIEE 11g was released not too long ago.

I downloaded it the day it was available, and have been trying every since to 1, find the time to install it and 2, not break the install process.

Environment
Guest: Windows XP Professional (an old image I had lying around)
VirtualBox 3.2.8
Host: Ubuntu Karmic Koala
Memory: 1.5 GB

In short, my laptop.

I started with the database, 11gR2 32 bit for Windows. No problems there.

As is my MO, I didn't read the installation instructions the first time I fired up the OBIEE installation. I was quickly met with the database connect string. Easy enough, machine:port:service_name.

What's this? You want a specific user? Bollocks.

I let it rest for a couple of days.

Meanwhile, a colleague asked a question about which database he could use. I asked if a version was specified and then quickly scanned the docs. No specific mention of a version...so I said Oracle XE, no 11gR2, no, XE, no, 11gR2. 11gR2, yeah, that's it.

That's when I finally decided to read the installation instructions...and found out I had missed one of the downloads.



Oh, you mean that one?

The Repository Creation Utility. Interesting.

I had heard that some of the new version would be database driven, I had just assumed (yes, I know about assuming) it was more RPD related (and it still may be).

Anyway, that took all of 15 seconds to install.

Back to the installer...and I don't learn any lessons, I tried to install it from my virtual folder, instead of from the "actual" disk. So, it took 4 times before I finally figured that out.

I copied the zip files to the disk, unzipped them and installed the sucker. By install I mean I am in step 12 of 13, Configuring BI Domain. That's the furthest I have gotten so I'm calling it a success.

Now to wipe the disk and start from scratch.

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.

I got an email today titled, "Your Favorite Movie."

It claims to successfully calculate your favorite movie by picking a number, from 1 to 9, then doing some calculations and applying the result to a list of movies.

Years ago...my first boss, got a similar email and began working through it via SQL. I was very impressed, both by the idea of using SQL and that he could do it in SQL. He was (is) a smart, creative guy.

I'm sure you've seen this one before:Your Favorite Movie

Try this test. Scroll down and do the quiz as it instructs and find out what movie is your favorite. This amazing math quiz can likely predict which of 18 films you would enjoy the most. Don't ask me how but it really works!

Pick a number from 1-9.

Multiply by 3.

Add 3.

Multiply by 3 again.

Now add the two digits together to find your predicted favorite movie in the list of 18 movies below.

Mine was "Gone with the Wind" - exactly right! So be honest, and do it before you scroll down to see the list below. It's easy and it works.How do you do this in SQL? It's not that hard really. First, get 1-9. SELECT from DUAL will accomplish that.

SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 9

Then you just need to apply the calculations. First go, I put the calcs in the outer query:

SELECT rn, ( ( ( rn * 3 ) + 3 ) * 3 ) calc_#
FROM
  (
    SELECT rownum rn
    FROM dual
      CONNECT BY LEVEL <= 9
  )
/

    RN     CALC_#
------ ----------
     1         18
     2         27
     3         36
     4         45
     5         54
     6         63
     7         72
     8         81
     9         90

A pattern jumps out at me immediately...

So I move it to the inner query and use SUBSTR to get each number and then add them together. I am sure there are better ways...this was quick and dirty.

SELECT
  rn,
  SUBSTR( t, 1, 1 ) + SUBSTR( t, 2, 1 ) total
FROM
  (
    SELECT
      rownum rn,
      ( ( ( rownum * 3 ) + 3 ) * 3 ) t
    FROM dual
      CONNECT BY LEVEL <= 9
  )
/

The results reveal the pattern rather quickly.

    RN      TOTAL
------ ----------
     1          9
     2          9
     3          9
     4          9
     5          9
     6          9
     7          9
     8          9
     9          9

Of course #9 is the movie that you would never pick. It's funny though:

1. Gone With The Wind
2. E.T.
3. Beverly Hills Cop
4. Star Wars
5. Forrest Gump
6. The Good, The Bad, and the Ugly
7. Jaws
8. Grease
9. The Joy of Anal Sex With A Goat
10. Casablanca
11. Jurassic Park
12. Shrek
13. Pirates of the Caribbean
14. Titanic
15. Raiders Of The Lost Ark
16. Home Alone
17. Mrs. Doubtfire
18. Toy Story

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)

Just a quick follow up to my post from the other day about using DBMS_Scheduler to generate lists of dates in SQL. I mentioned that you can use named schedules in this method, and that’s actually a handy way of testing your schedule definition. The syntax is along the lines of: begin dbms_scheduler.create_schedule( schedule_name => [...]

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.

A rather common question in the Oracle community goes along the lines of this: “How do I generate a list of dates?”, with variations such as needing only working days, excluding specific dates, etc.. If there is a standard method for this it tends to go along the lines of this: Generate a rather vanilla [...]

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.

Its finally here, Oracle BI Publisher 11g! Right now you need to download the full suite and then select to just install BIP. Not sure if there are plans to release an installer just for BIP? There is so... Tim Dexter