Feed aggregator

Oracle Data Integrator 10.1.3.5 – Using OBI EE as a Data Source – Using BI EE JDBC Driver

Venkat Akrishnan - Sun, 2009-06-07 06:33

In a prior blog entry here, i had shown how to use BI Server as a data source for loading data from Essbase into custom tables. That approach basically used the Sun ODBC-JDBC bridge which in most cases has bad performance. Today we shall see how to use the native BI Server JDBC driver to connect to BI Server and in-turn use that for loading Essbase Data into a custom table.

One of the major advantages of ODI is the fact that it can be used to connect to any data source that has a valid jdbc driver. In order to make ODI to use the BI EE JDBC driver, we need to make the JDBC driver (jar file) of the BI Server to be accessible by ODI. The jar file(bijdbc14.jar) of BI Server can be obtained from the BI Publisher web server installation folder {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib. The main reason why we are going to the BIP folder structure is because BIP uses the JDBC driver of BI Server to do reporting on BI EE metadata.

image

The above screenshot basically provides the database driver class and the connection string of the JDBC driver of BI EE. Now copy this driver jar file to {OracleDI}\oracledi\drivers directory.

image

Now open up Topology Manager and create a new Universal Data Server. Use the above driver class and connection in the JDBC URLs.

image

Ensure that you are able to test the connection using Administrator username and password.

image

Now, in the physical schema choose any subject area in the Presentation Layer.

image

Now once this is done, go to the Designer and create a Project. And import the necessary Knowledge Modules that i had shown in the last blog entry. Since we are using JDBC, we can do a selective reverse of all the necessary tables.

image

But be aware that, BI EE supports multiple presentation tables with the same name across subject areas. But ODI currently does not support multiple table import with the same name (as there is no BI EE specific RKM. This can be resolved if you build a separate RKM. Also, though you might be able to reverse objects with special characters, they would not work within an Interface. So, its better to create aliases with proper ODI supported naming conventions. ). Lets choose just 4 such tables, Basic, Market, Scenario and Year.

image

image

image

Now lets build a very simple interface wherein we would be inserting Gen2, Market and the Measure value into a dummy table. Remember in BI EE, whenever you are bringing in multiple tables from a single presentation subject area, no joins are needed i.e. a cross join would have to be configured across multiple tables.

image

If you execute this interface you would notice that BI Server would generate the MDX accordingly and would then insert the output of the MDX to the database table.

image 

This should give you an idea of how to go about using multiple BI EE subject area tables in an ODI interface and then using that interface to load data into a database table. Also, this method should typically be as fast as your BI Server is since it uses the JDBC connectivity as opposed to the JDBC-ODBC bridge.


Categories: BI & Warehousing

Opening up SOA

Rakesh Saha - Fri, 2009-06-05 09:45

Oracle BI EE 10.1.3.4.1 – Handling Complex String Manipulations – Using Regular Expressions (RegEx) and Evaluate

Venkat Akrishnan - Thu, 2009-06-04 05:05

One of the requirements that i recently came across involved some complex string manipulations that cannot be solved easily by using BI EE specific functions. Just to illustrate the requirement, take a look at the screenshot below

image

As you see, the above is a simple BI EE report containing the customer related details. But the major problem with this report is that, it contains all the details of a customer in a comma separated  format. Basically the source application has inserted the data into this table in such a fashion. One key aspect to this is the fact that the comma delimited customer details follow a specific pattern. That is the details column is nothing but a concatenation of the below customer attributes in the same order

1. Customer First Name
2. Customer Last Name
3. Customer Gender
4. Customer Email Address
5. Customer Phone Number
6. Customer Street Address
7. Customer City
8. Customer State
9. Customer Country

The requirement is to get a report containing the Customer ID, Country ID, Customer Phone Number and the Customer Email address. Normally the best place for handling this would be in the ETL stage wherein individual attributes can be split into multiple columns and then used for reporting. Unfortunately in most cases we would have to work with what we have. To do this in the reporting layer requires some complex INSTR, SUBSTR logic. Today we shall another approach for handling such requirements. Instead of using normal string manipulation functions, we shall be using a new feature of Oracle 10g called as the Regular Expressions.

Regular Expressions or RegEx make string manipulations easy and in fact provides quite a few advanced formatting, filtering options. The regular expressions come as 4 different types of functions

1. REGEXP_SUBSTR – Advanced Sub-String function
2. REGEXP_INSTR – Advanced String position evaluation function
3. REGEXP_LIKE – For doing advanced filtering on a result set
4. REGEXP_REPLACE – For doing advanced multiple-iterative string replace

In order to achieve the above requirement, we need to use these database functions. And of course, we need to use EVALUATE to call these database functions. To split the above comma separate customer attributes, we need to use RegEx functions given below

1. Customer Phone – REGEXP_REPLACE(CUSTOMER_DETAILS,'(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*)’,’\4′)
2. Customer Email – REGEXP_REPLACE(CUSTOMER_DETAILS,'(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*)’,’\3′)

Now use EVALUATE and pass down the above RegEx functions back to the database.

image

If you look at the report now, you should basically have the individual attributes in a separate column as shown below.

image

The above was one requirement. Lets look at another requirement now. In some cases, when you are reporting against transactional applications, you would have columns which get entered as free flowing text. The requirement is to classify these columns into 3 types. One is numeric, the other is free text without numbers and the third is alphanumeric. The requirement is to basically give a provision to the end users to choose a specific type and then that column should only the corresponding text i.e. for example if numeric is chosen only the text where numbers were entered should be displayed. In normal sql terms, filtering for numerals and alpha-numerals require separate logic and is complicated as well. We shall see how we can achieve this using RegEx.

Lets first take a look at the report

image

Now, lets create a prompt which will produce just 3 values (Numeric, Alphabets, Alpha-Numeric).

image

 
SELECT 'Numeric' FROM RegEx WHERE CUSTOMERS_DETAILS.COMMENTS IS NOT NULL 
UNION ALL 
SELECT 'Alphabets' FROM RegEx WHERE CUSTOMERS_DETAILS.COMMENTS IS NOT NULL 
UNION ALL 
SELECT 'Alpha-Numeric' FROM RegEx WHERE CUSTOMERS_DETAILS.COMMENTS IS NOT NULL 

Make this prompt to set a presentation variable called AlphaNum.

image

Now go to the original report and create a filter as shown below

 
CASE WHEN '@{AlphaNum}{Numeric}' = 'Numeric' 
THEN EVALUATE('REGEXP_SUBSTR(%1,''^\d+$'')',CUSTOMERS_DETAILS.COMMENTS) 
WHEN '@{AlphaNum}{Numeric}' = 'Alphabets' 
THEN EVALUATE('REGEXP_SUBSTR(%1,''^[a-zA-Z]+$'')',CUSTOMERS_DETAILS.COMMENTS) 
ELSE EVALUATE('REGEXP_SUBSTR(%1,''^[a-zA-Z0-9]+$'')',CUSTOMERS_DETAILS.COMMENTS) END IS NOT NULL 

image

Now include this report in the main dashboard along with the prompt.

image

image

As you see, the RegEx functions are pretty powerful. And of course, with the advent of EVALUATE, we now have the capability to leverage them from BI EE itself. This saves time and effort in implementing complex string based logics.


Categories: BI & Warehousing

Oracle Enterprise Linux 4 Update 8 CD and DVD ISOs Now Available

Sergio's Blog - Wed, 2009-06-03 00:53

Download OEL 4 Update 8 for i386 and x86_64 architectures for free from edelivery.oracle.com/linux. ia64 ISOs will be published in the same location a bit later.

Categories: DBA Blogs

My JavaOne Presentation:

Debu Panda - Tue, 2009-06-02 13:24
I'm co-presenting with my co-author (EJB 3 In Action) Reza Rahman on JPA Application Tuning.

Here are details for the talk:

TS-3977 -Keeping a Relational Perspective for Optimizing the Java™ Persistence API

Thursday June 04 4:10 PM - 5:10 PM Hall E 134

If you are attending JavaOne this year, please attend the presentation. Also we have a book signing scheduled tomorrow (Wednesday June 3, 2pm at Java Book Store) so stop by if you want your book to be signed

-Debu

Oracle Data Integrator 10.1.3.5 – Using Oracle BI EE as a Data Source – Loading Essbase data into custom tables – Using JDBC-ODBC Bridge

Venkat Akrishnan - Tue, 2009-06-02 03:23

In the blog entry here, i had basically shown multiple ways of loading data into Essbase. Today we shall see an approach wherein one can extract data from Oracle BI Server using the JDBC-ODBC bridge. The idea is to use BI Server to import Essbase metadata. Then Oracle Data Integrator would be used to extract the Essbase metadata through MDX via the BI Server and then load into a custom table. Though there are no out of the box RKM’s to reverse engineer BI Server metadata, we can work around them using some other functionality. If you want to extend this, you can create your own custom RKM to reverse-engineer BI Server metadata tables.

To start with lets first import a sample Demo->Basic cube into our BI EE repository. While creating the subject area of this cube, ensure that you do not have spaces/special characters in the column names. If you need the special characters for Answers, then add aliases with new no space/special characters name. For example, if you have Gen1, Year as one of your column names, add an alias as Year.

image

image

The idea behind doing this is to ensure that we can treat these tables as normal tables using the AnalyticsWeb ODBC connection. Of course, we can make ODI to append double quotes if special characters are present in a column or a table name. But for now lets go with this approach.

Once the aliases have been added, go to the Topology Manager and create a universal Data Server using the SUN JDBC-ODBC bridge.

image

image

image

Create a physical schema. Ensure that you are entering the correct subject area name in the schema.

image

Ensure that you are able to test the connection. Once this is done, create a logical schema and assign it to the Physical Data Server through the Global Context.

image

After this go to the designer and create a project. Import the the following Knowledge Modules in to the Project.

1. LKM SQL to SQL
2. CKM SQL

Then create a model. Since we do not have a RKM to reverse engineer the data sources automatically (though you can actually extend other RKM’s for this purpose). So, for now lets manually enter the tables and columns in the Model as shown below

image

Use the Aliases as the column names.

image

image

Now ensure that you are able view the data directly from Essbase using this.

image

image

If you look at the screenshot above, BI Server would be showing the data against the actual names (Gen1, Year etc). So, you can be sure now that the aliases can be accessed even outside of BI EE using the ODBC connection. Now, to make this simple lets create a straight forward table called YEAR_DIM_ESS in any database schema (this will act as our target)

 
CREATE TABLE 
YEAR_DIM_ESS ( 
YEAR VARCHAR2(100), 
QUARTER VARCHAR2(20), 
MONTH VARCHAR2(20)); 

Import this table to another Model. And then create an interface mapping the columns from the source to the target. Ensure that you are using the SUNOPSIS_MEMORY_ENGINE as the staging area.

image

image

image

Now, if you execute the interface, you would have essbase data loaded inside the database table.

image

This is one other good option to consider if you have full set of end to end oracle tools like ODI, Essbase, BI EE etc. In this case today, i have used the JDBC-ODBC bridge. Later i would be covering how we can use the JDBC jar of BI Server directly within ODI.


Categories: BI & Warehousing

Deconstructing the iPod Shuffle UI

Tahiti Views - Mon, 2009-06-01 10:36
The new buttonless iPod Shuffle, which moves all the controls onto the headphone cord, is taken to task in this article:The new iPod shuffle: Button, button, who's got the button?Now, I'm a recent purchaser of the previous Shuffle model, and intuitively I prefer the Play/Pause/Forward/Back/Up/Down controls of that previous model. But I like to take contrarian positions sometimes too, so let me John Russellhttp://www.blogger.com/profile/17089970732272081637noreply@blogger.com0

size sometimes does matter...

Nuno Souto - Sun, 2009-05-31 18:37
Not really. Well... You know what I mean. ;)Anyways, sorry for the "catchy" title. This entry is prompted by some of the ongoing exercise in fine tuning our AIX db servers.AIX is a great OS but the information needed to eek out the last ounce of performance for Oracle dbs is sparse, or across many documents. This blog entry is for my future reference on how to make Oracle use large pages inNoonsnoreply@blogger.com13

Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Displaying Member Intrinsic & Custom Properties – Aliases and UDAs

Venkat Akrishnan - Sun, 2009-05-31 16:10

One of the common questions asked in the existing BI EE to Essbase connectivity is the ability to display member names in a report instead of the alias names. Currently BI EE does not support displaying the member names(if default alias exist already) in a direct way. So, lets take a look at an approach today which basically extends on the method described here in the modeling guide. Actually,. the method described there is correct but the explanation is partly wrong(in a different context it is right though). It is described there that Member Names are supported for only Level-0 members. It is actually supported for all the generations. But when a drill happens the alias names will return. Lets look at why this happens and of course understand the capability of BI EE in displaying other member intrinsic properties.

The most common question that generally comes up with regard to member names is, why not EVALUATE can be used to display them directly. The main reason why this is not possible is, Member Name is an intrinsic property of a member. There are no MDX functions which can actually be used to display a member name. An essbase member has many intrinsic properties. The intrinsic properties are listed below

1. MEMBER_NAME
2. MEMBER_ALIAS
3. LEVEL_NUMBER
4. GEN_NUMBER
5. IS_EXPENSE
6. COMMENTS
7. RELATIONAL_DESCENDANTS
8. MEMBER_UNIQUE_NAME
9. ANCESTOR_NAME

Apart from the above there are other properties like custom UDA properties etc. Now lets take the example of the Demo Basic cube. If you look at the outline of this cube, it has aliases at the Time dimension level and also for the quarters(not for the level-0 though). Now, our aim is that, whenever we include these levels in the report, the report should automatically display the member names instead of the alias names.

image

The intrinsic properties are typically referred directly in the repository as the External Name.

image

So, in order to refer the alias, lets create a custom physical cube column as shown below.

image

The name for the column can be anything. Basically we are creating an Member Name column for the level that has aliases. In our case it is the quarter level.

image

Assign this new column to the Gen2, Year of the year dimension.

image

Also, make this column to be part of the BMM and the presentation layer. Now, if you create a report on this column and a measure, you would get a report only with the member names for the Quarter Level.

image

But the problem with this approach is that, once you drill on this column, the drills would return. That can be manipulated by re-arranging the level keys accordingly. The main reason why i introduced this was to show a very good feature that can be derived using this approach. In many cases, you would have a report containing 2 or 3 dimension levels and a fact measure. In the same report, you might want to know which dimension member has a specific UDA assigned. For example, consider the outline below

image

If you look at the Market dimension, the 2nd generation has multiple UDAs assigned. Assume that we have a report like the one shown below

image

In the above report, we need one more column stating whether that member has a “Major Market” UDA assigned or not. This is not possible using MDX as the UDA based MDX functions will filter the members based on a UDA (which is not our requirement). So, to start with lets create a custom Physical cube column. In the External name of the column enter Major Market as the External Name. UDA is a custom property of a member. Hence it can be referenced directly as an External name as shown below

image

Now, include this UDA in the Gen2, Region level of the Market Dimension.

image

Once this extra column is included, just include this in the BMM and the presentation layer. Now in the above report, pull in this column. This would basically show all the members having the Major Market UDA as 1. Remaining members that do not have this UDA assigned will be shown as 0.

image

The above technique can be used for other solving other interesting reporting requirements as well. Its heartening to see every aspect of MDX being available in some form or other for customization.


Categories: BI & Warehousing

The Unicorn Factor @ The Twitter Conference

Ken Pulverman - Thu, 2009-05-28 01:38
140, The Twitter Conference just ended - a conference focused on the tools and trends of the real time web . We were there to see what we could leverage immediately in our marketing efforts. One of the presenters revealed the perfect quote originally served up as a tweet to sum up much of the content at events like this - 'All Unicorn, No Cupcake.' We got some Cupcake nonetheless, taking away a few gems.

Here's a fun fact from the conference. Did you know that there are now 10 thousand apps leveraging the Twitter API? Lots of these utilities and their founders made an appearance at the show. Very inspirational. Literally one guy not in a garage, but living above the garage in many cases. The guy next to me today had a very cool venture going that he was driving in his free time when he is not working at an mid-west farm equipment manufacturer. Seeing the entrepreneurial energy and the great utilities in progress is clearly not the source of my comment above, as I think many of these folks are delivering real Cupcake.

The Unicorn part of the reference relates more to some of the panelist consultants, conference groupies, and technology observers. No point calling him out here, but a well known social media observer told the audience that people are just going to need to get better at creating filters . Pure Unicorn. This from a guy who has 5 computers to geek out with wallstreet style while tracking the social web. Can you imagine joe six pack trying to promote his lawn fertilizer business with the same rig? Absolutely not.

There was also a ton navel gazing musings on the future from the panel jockies who aren't sure if they put the clothes in the dryer before boarding the flight to Mountain View. The exception to this was was Jason Calacanis who gave an excellent view of the likely future of Twitter and the real time web complete with possible screen mockups. He offered real justifications and potential business models for what he proposed. The rule, was unfortunately the folks from Twitter by providing just vague notions of where they are headed and really demonstrating for all that the business that they created has clearly gotten away from them.

In the battle of Cupcake vs. Unicorns though, Cupcake did win out in the passion and brilliant ideas of the developers that showed their wares. Search, analytics, collaboration, etc. The Unicorns occasionally offered value too when they pointed to the best of these utilities like Twitrratr.com, an elegant and simple way check the sentiment of posts for your brand.

Bottom line. The real time web is here. Time for all marketers to figure it out. Time spent waiting to figure out if Twitter is going to make it or come up with a business model is time wasted. The notion of needing to make sense of real time data from customers, employees, friends and enemies not to mention your car, house, and maybe even a pet or two is happening whether we like the notion or not. Learning to converse in this new world takes time, so it is best to start now.

I guarantee there will be a real time web solution without the need for a frequent Fail Whale in our near future. True, the utilities out there are numerous, sometimes shaky betas, and arguably all features vs. complete applications. Having said that, there are a lot of brilliant ideas to get your head around like location centric twittering, real time integrations with enterprise apps, and tweets that are automatically triggered and trigger other social interactions automatically. whew.

My advice for businesses......chances are your Twitter "phone" is already ringing. Answer it and begin unravelling how to leverage the Real Time web for your company.

First assignment - figure out what a # tag is and peruse the details from this Twitter conference using the tag #140tc. I'll let you apply your own Cupcake filter.

Oracle Enterprise Performance Management Architect (EPMA) – Administering Essbase, Planning and HFM – Setup

Venkat Akrishnan - Wed, 2009-05-27 12:37

One of the major advantages of Oracle acquiring so many companies is the fact that it integrates all the products in some fashion or the other, that too quickly. For example, if you look at the different possible ways of loading data into Essbase, the list of options available is sometimes absolutely daunting(to an extent flexibility as well) especially for users who are new to the product stack. The following list gives all the possible ways that i can think of for loading data into Essbase

1. Using Essbase Administration Services or EAS
2. Using Essbase Studio (from EPM 11 version)
3. Using Oracle Data Integrator (uses JAPI)
4. Using Hyperion DIM or Data Integration Management
5. Using custom Java, C & VB APIs
6. Using MaxL Scripts through an unix shell or batch script
7. Using Essbase Integration Services (Superceded now by Essbase Studio)
8. Using Oracle Hyperion Enterprise Performance Management Architect

I have covered 7 of the above 8 in prior blog entries. Today we shall see another important tool that is quite commonly used for managing the metadata of Oracle EPM products like Planning, Financial Management & Profitability and Cost Management. This tool called as Enterprise Performance Management Architect or EPMA provides a uniform platform for metadata management across most of the Hyperion components. Apart from its capabilities, one strange aspect to this product is the fact that it depends on IIS to web enable itself. So, you would need a mandatory windows server to host this. When i started using this a couple of months back, my initial impressions were far from impressive. I am not sure whether it was because of my environment or because of the product itself. Whenever EPMA was started, it seemed to be consuming the entire memory(more than the memory occupied by SGA of Oracle and the Essbase Kernel). Having said that, its features(and also the concept of common metadata management) are really good and of course with more usage i am starting to like this more and more.

Today we shall see what it takes to setup EPMA to manage Essbase Applications/Outlines. Just remember the fact that EPMA was never designed to do individual application(Essbase, Planning etc) level administration. That is meant for the respective product administration tools. EPMA is meant for managing the metadata like dimensions, hierarchies, data loads across product sets etc. So, in effect it is like ODI or Hyperion DIM but with more product specific multi-dimensional features thrown in. So, if you are managing Planning, Essbase and HFM (or any of these 2) in your organization EPMA could be a good fit when you want to use a single tool for managing the common dimensions, data loads etc.

To use EPMA for managing Essbase specific application outline members, ensure that you are using Essbase in external mode i.e shared services security should be used. By default when you install EPM in windows, Essbase will be installed with SSO configured against shared services. SSO is not sufficient to use EPMA against Essbase. Ensure that you convert Essbase from local authentication to External authentication. That is done by externalizing the users from Essbase Administration Services.

Once that is done, the next step is to create a data source for the interface tables using the EPM Configurator.

These interface tables are used for importing the pre-defined common dimension types that are available across EPMA. The idea is to have custom members defined in these interface tables and then import them into EPMA. This step is needed only if you have pre-defined members defined in the interface tables. If you are starting from scratch then this is not needed. Once the data source is created, log into EPMA and go to Application Library.

Then create a new Essbase (ASO) application.

Then edit this ASO application and create the dimensions and the corresponding members as shown below

After this step, go to shared services and ensure that the user with which you have logged in as has the necessary privileges to access Essbase. Basically one would need application and database administrator privileges.

Then you can deploy this application directly from EPMA to Essbase.

The major drawback with this is that this does not provide any restructuring options when data is loaded into a deployed cube. Also, one should not be changing the application directly within Essbase using EAS. Then the sync between EPMA and Essbase would be lost and all changes would be over-written when the sync is done from EPMA again. Typically EPMA is not recommended for Essbase applications. It is very good for managing Planning and HFM metadata. One can use shared dimensions wherein the same dimension can be reused across applications.This should give you an idea of how EPMA works. I would go into details of how one can do data synchronization across applications using EPMA in the coming blog entries.


Categories: BI & Warehousing

New job, lots of exciting stuff

Dan Norris - Tue, 2009-05-26 16:31

It’s been a week since I started my new job at Oracle Corporation. I’m a remote worker which means that the first day of work wasn’t the usual event since I just went to my home office and got on a concall with my new manager. After getting connectivity and accounts set up properly, I was able to pretty quickly work through the new hire checklist of forms and mandatory training.

My new Oracle-provided laptop arrived around mid-week and I realized that, at least for now, I’ll have to revert back to using the Windows-based laptop and (hopefully temporarily) put my MacBook Pro on the shelf. Actually, my wife is very excited since she’ll get the MBP to use now and we’ll do the usual “trickle down” to the kids so that the oldest computer in the “fleet” will get ditched.

I tried “upgrading” to a new DSL line (from cable modem) last week too, but that appears to have failed as the DSL modem drops my connection on a regular basis for a few seconds. That’s just long enough to break the VPN connection and make it appear that I’m bouncing off and on instant messaging every hour or so. Annoying.

I was excited to finally get on to some real technical work late last week and got to login to my first Exadata storage server. The chores were to re-image 4 servers and apply the latest patches to them. The real fun is starting now!

Of course, the most important part of any job (in my opinion) is people. I have had several virtual meetings with my peers and manager so far and, as expected, they’re all superb. I’m looking forward to working more with the team and others in our companion teams as well as the days roll ahead.

Scuba diving pre-ODTUG Kaleidoscope, Monterey, 21-June-2009

Dan Norris - Tue, 2009-05-26 14:28

I’m very pleased to report that I will be able to meet up with ODTUG Kaleidoscope attendees at both the ODTUG Community Service Day (2nd Annual!) and my own scuba dive outing as well. If you can, I’d love for you to attend both events. If you’re not a certified scuba diver, then you can at least participate in the Community Service Day festivities and help out the local area while enjoying some California weather too!

For those certified scuba divers that will (or can) be in the Monterey Bay area on 21-June, I invite you to come diving with me. I’ve arranged some reserved spots on the Beachhopper II dive charter that I’ve dove with before. Brian and Mary Jo (the captain, crew, and bottle washers) are top notch and we had a great time last fall at the first annual pre-OpenWorld scuba event (look for more details on the 2nd annual event later this summer). The boat isn’t huge, but 10 divers is enough for a lot of fun.

The pre-Kaleidoscope dive day is Sunday, 21-June (Father’s Day). The boat will depart the K dock at Monterey Bay harbor at 8am, so load-up is 7:30am. We’ll have a nice morning, drain 2 tanks at some of the best sites you’ll see in northern California (specific sites will be determined that morning by the captain and diver requests), and then motor back to the harbor probably shortly after noon or 1pm. Mary Jo said that she’d also entertain the option of an afternoon 2-tank trip as well, if there is interest (I know I’m interested). Oh, I almost forgot to mention that snacks are provided and they are amazing–made by Mary Jo herself!

The boat costs break down like this:

  • $70 for the boat trip (weights are not included)
  • plus $20 for two tanks of air ($90 total)
  • or $30 for 2 tanks of Nitrox ($100 total)

The charter doesn’t offer gear rental, so we’ll have to pick that up separately. I previously rented from Glenn’s Aquarius 2 which is located pretty close to the harbor and opens at 7am for morning pickup. Their pricing for rental are:

  • Weights only: $8
  • Wetsuit, hood, gloves: $21
  • Full gear (BCD, reg, exposure suit, etc.): $65

We’re less than 1 month away (I just found out I was going to be able to attend last week), so let me know ASAP if you’re interested in diving with us. Once you contact me, I’ll send you the signup instructions. I’m releasing the remaining open seats on 29-May, but there may still be open spots after that, so contact me (comment below, or email) if you’re interested.

As a special treat, Stanley will be joining us for his first scuba dive as well!

Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g

Oracle Optimizer Team - Tue, 2009-05-26 12:04

Recently we received several questions regarding a usual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when

  1. CURSOR_SHARING is set to similar

  2. Bind peeking is in use

  3. And a histogram is present on the column used in the where clause predicate of query

You must now be wondering why this is the expected behavior. In order to explain, let's step back and begin by explaining what CURSOR_SHARING actually does. CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache. However, there is also a caveat or additional requirement on CURSOR_SHARING, which is that the use of system generated bind should not negatively affect the performance of the application. CURSOR_SHARING has three possible values: EXACT, SIMILAR, and FORCE. The table below explains the impact of each setting with regards to the space used in the cursor cache and the query performance.

.nobrtable br { display: none }



CURSOR_SHARING VALUESPACE USED IN SHARED POOLQUERY PERFORMANCE
EXACT (No literal replacement)Worst possible case - each stmt issued has its own parent cursorBest possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt
FORCEBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR without histogram presentBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR with histogram presentNot quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space)Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt




In this case the statement with hundreds of children falls into the last category in the above table, having CURSOR_SHARING set to SIMILAR and a histogram on the columns used in the where clause predicate of the statement. The presence of the histogram tells the optimizer that there is a data skew in that column. The data skew means that there could potentially be multiple execution plans for this statement depending on the literal value used. In order to ensure we don't impact the performance of the application, we will peek at the bind variable values and create a new child cursor for each distinct value. Thus ensuring each bind variable value will get the most optimal execution plan. It's probably easier to understand this issue by looking at an example. Let's assume there is an employees table with a histogram on the job column and CURSOR_SHARING has been set to similar. The following query is issued

select * from employees where job = 'Clerk';

The literal value 'Clerk' will be replaced by a system generated bind variable B1 and a parent cursor will be created as

select * from employees where job = :B1;

The optimizer will peek the bind variable B1 and use the literal value 'Clerk' to determine the execution plan. 'Clerk' is a popular value in the job column and so a full table scan plan is selected and child cursor C1 is created for this plan. The next time the query is executed the where clause predicate is job='VP' so B1 will be set to 'VP', this is not a very popular value in the job column so an index range scan is selected and child cursor C2 is created. The third time the query is executed the where clause predicate is job ='Engineer' so the value for B1 is set to 'Engineer'. Again this is a popular value in the job column and so a full table scan plan is selected and a new child cursor C3 is created. And so on until we have seen all of the distinct values for job column. If B1 is set to a previously seen value, say 'Clerk', then we would reuse child cursor C1.
.nobrtable br { display: none }



Value for B1Plan UsedCursor Number
ClerkFull Table ScanC1
VPIndex Range ScanC2
EngineerFull Table ScanC3



As each of these cursors is actually a child cursor and not a new parent cursor you will still be better off than with CURSOR_SHARING set to EXACT as a child cursor takes up less space in the cursor cache. A child cursor doesn't contain all of the information stored in a parent cursor, for example, the SQL text is only stored in the parent cursor and not in each child.

Now that you know the explanation for all of the child cursors you are seeing you need to decide if it is a problem for you and if so which aspect affects you most, space used in the SHARED_POOL or query performance. If your goal is to guarantee the application performance is not affected by setting CURSOR_SHARING to SIMILAR then keep the system settings unchanged. If your goal is to reduce the space used in the shared pool then you can use one of the following solutions with different scopes:

  1. Individual SQL statements - drop the histograms on the columns for each of the affected SQL statements

  2. System-wide - set CURSOR_SHARING to FORCE this will ensure only one child cursor per SQL statement


Both of these solutions require testing to ensure you get the desired effect on your system. Oracle Database 11g provides a much better solution using the Adaptive Cursor Sharing feature. In Oracle Database 11g, all you need to do is set CURSOR_SHARING to FORCE and keep the histograms. With Adaptive Cursor Sharing, the optimizer will create a cursor only when its plan is different from any of the plans used by other child cursors. So in the above example, you will get two child cursors (C1 and C2) instead of 3.

Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g

Inside the Oracle Optimizer - Tue, 2009-05-26 12:04

Recently we received several questions regarding a usual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when

  1. CURSOR_SHARING is set to similar

  2. Bind peeking is in use

  3. And a histogram is present on the column used in the where clause predicate of query

You must now be wondering why this is the expected behavior. In order to explain, let's step back and begin by explaining what CURSOR_SHARING actually does. CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache. However, there is also a caveat or additional requirement on CURSOR_SHARING, which is that the use of system generated bind should not negatively affect the performance of the application. CURSOR_SHARING has three possible values: EXACT, SIMILAR, and FORCE. The table below explains the impact of each setting with regards to the space used in the cursor cache and the query performance.

.nobrtable br { display: none }



CURSOR_SHARING VALUESPACE USED IN SHARED POOLQUERY PERFORMANCE
EXACT (No literal replacement)Worst possible case - each stmt issued has its own parent cursorBest possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt
FORCEBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR without histogram presentBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR with histogram presentNot quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space)Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt




In this case the statement with hundreds of children falls into the last category in the above table, having CURSOR_SHARING set to SIMILAR and a histogram on the columns used in the where clause predicate of the statement. The presence of the histogram tells the optimizer that there is a data skew in that column. The data skew means that there could potentially be multiple execution plans for this statement depending on the literal value used. In order to ensure we don't impact the performance of the application, we will peek at the bind variable values and create a new child cursor for each distinct value. Thus ensuring each bind variable value will get the most optimal execution plan. It's probably easier to understand this issue by looking at an example. Let's assume there is an employees table with a histogram on the job column and CURSOR_SHARING has been set to similar. The following query is issued

select * from employees where job = 'Clerk';

The literal value 'Clerk' will be replaced by a system generated bind variable B1 and a parent cursor will be created as

select * from employees where job = :B1;

The optimizer will peek the bind variable B1 and use the literal value 'Clerk' to determine the execution plan. 'Clerk' is a popular value in the job column and so a full table scan plan is selected and child cursor C1 is created for this plan. The next time the query is executed the where clause predicate is job='VP' so B1 will be set to 'VP', this is not a very popular value in the job column so an index range scan is selected and child cursor C2 is created. The third time the query is executed the where clause predicate is job ='Engineer' so the value for B1 is set to 'Engineer'. Again this is a popular value in the job column and so a full table scan plan is selected and a new child cursor C3 is created. And so on until we have seen all of the distinct values for job column. If B1 is set to a previously seen value, say 'Clerk', then we would reuse child cursor C1.
.nobrtable br { display: none }



Value for B1Plan UsedCursor Number
ClerkFull Table ScanC1
VPIndex Range ScanC2
EngineerFull Table ScanC3



As each of these cursors is actually a child cursor and not a new parent cursor you will still be better off than with CURSOR_SHARING set to EXACT as a child cursor takes up less space in the cursor cache. A child cursor doesn't contain all of the information stored in a parent cursor, for example, the SQL text is only stored in the parent cursor and not in each child.

Now that you know the explanation for all of the child cursors you are seeing you need to decide if it is a problem for you and if so which aspect affects you most, space used in the SHARED_POOL or query performance. If your goal is to guarantee the application performance is not affected by setting CURSOR_SHARING to SIMILAR then keep the system settings unchanged. If your goal is to reduce the space used in the shared pool then you can use one of the following solutions with different scopes:

  1. Individual SQL statements - drop the histograms on the columns for each of the affected SQL statements

  2. System-wide - set CURSOR_SHARING to FORCE this will ensure only one child cursor per SQL statement


Both of these solutions require testing to ensure you get the desired effect on your system. Oracle Database 11g provides a much better solution using the Adaptive Cursor Sharing feature. In Oracle Database 11g, all you need to do is set CURSOR_SHARING to FORCE and keep the histograms. With Adaptive Cursor Sharing, the optimizer will create a cursor only when its plan is different from any of the plans used by other child cursors. So in the above example, you will get two child cursors (C1 and C2) instead of 3.

Categories: DBA Blogs, Development

Enterprise Linux 4 Update 8 Released on ULN, public-yum.oracle.com

Sergio's Blog - Tue, 2009-05-26 07:36

We've just released Oracle Enterprise Linux 4, Update 8 on ULN (linux.oracle.com) and on public-yum.oracle.com. DVD ISOs are available for Unbreakable Linux support customers by calling support. They'll be available soon on edelivery.oracle.com/linux

Categories: DBA Blogs

Oracle BI EE 10.1.3.4.1 – Writebacks to Essbase – Using JAPI and Custom HTML – Part 1

Venkat Akrishnan - Mon, 2009-05-25 14:10

Considering the amount of expectations surrounding the BI EE and Essbase connectivity, i thought it would make a lot of sense to blog about another interesting piece of the Essbase and BI EE integration i.e Writebacks to Essbase from BI EE. Actually i have seen this question being asked by customers/users in quite a few internal presentations that i have been involved recently. Writebacks to Essbase from BI EE is not supported by default. Having said that one can create a custom solution to enable a writeback to an Essbase cell. We shall see one approach of doing this today.

As you would probably know, Writebacks to a relational source from BI EE is supported through custom XML messages. Unfortunately, as of this release, this method cannot be reused for non-relational sources. Basically our requirement is pretty simple. In a BI EE report (reporting against an Essbase source), the end user should have the ability to enter custom values and update the corresponding intersections back in Essbase. The below screenshot explains the requirement pretty clearly.

The high level architecture diagram to enable these writebacks is given below

The high level flow is, for every cell update, the end user will enter the new value and then will click on the update button. That will pass on the parameters to a JSP page using the HTML Form GET method. The JSP will accept the parameters and will in turn pass on the values to the JAPI. The JAPI will then update the Essbase cell. To illustrate this, we shall use the default Demo->Basic cube. Remember the fact that one cannot use the write backs textboxes directly as currently BI EE does not provide a means of referencing the updated/entered value in a cell outside of the XML template. So, we would need to write our own custom HTML to generate textbox and the update buttons.

Import the Demo Basic cube into the repository and create the BMM and presentation layers by drag and drop. Change the physical and the BMM layer aggregations(of the all the measures for which you want to enable writebacks) to SUM instead of Aggr_External. The main reason for doing this is to ensure that we can use string manipulation functions like concatenation from Answers. For more details on each of these aggregations check my blog entries here, here, here and here.

Now, lets go to JDeveloper and create a simple JSP page. Use the below code in the JSP. You can customize this to your needs.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.Map.Entry" %>
<%@ page import="java.util.jar.Attributes" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="com.essbase.api.base.*" %>
<%@ page import="com.essbase.api.dataquery.*" %>
<%@ page import="com.essbase.api.session.*" %>
<%@ page import="com.essbase.api.datasource.*" %>
<%@ page import="com.essbase.api.domain.*" %>
<%@ page import="com.essbase.api.metadata.*" %>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
    <title>WriteBackEssbase</title>
  </head>
    <body>
		<font size="5"><%="WriteBack Started" %></font>

<%

        String s_userName = "admin";
        String s_password = "password";
        String s_olapSvrName = "localhost";
        String s_provider = "http://localhost:13080/aps/JAPI";

        try
        {
        IEssbase ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
        IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
        IEssOlapServer olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
        olapSvr.connect();
        IEssCubeView cv = dom.openCubeView("Data Update Example",s_olapSvrName, "Demo", "Basic");

        String v_Market = request.getParameter("p_Market");
        String v_Product = request.getParameter("p_Product");
        String v_Accounts = request.getParameter("p_Accounts");
        String v_Scenario = request.getParameter("p_Scenario");
        String v_Year = request.getParameter("p_Year");
        String v_Value = request.getParameter("p_Value");

        IEssGridView grid = cv.getGridView();
        grid.setSize(2, 5);
        grid.setValue(0, 1, v_Market);
        grid.setValue(0, 2, v_Product);
        grid.setValue(0, 3, v_Accounts); ;
        grid.setValue(0, 4, v_Scenario);
        grid.setValue(1, 0, v_Year);

        cv.performOperation(cv.createIEssOpRetrieve());
        System.out.println("\nData Cell at 2nd-row, 2nd-column: " + grid.getValue(1,1).toString());
        System.out.println ("Market: "+v_Market+" Product: "+v_Product+" Accounts: "+v_Accounts+" Scenario: "+v_Scenario+" Year: "+v_Year+" Value: "+v_Value);

        int row = 1, col = 1;
        if (grid.getCellContentType(row, col) ==
                IEssGridView.CELL_CONTENT_TYPE_DOUBLE) {
            IEssValueAny val = grid.getValue(row, col);
            double dblVal = val.getDouble();

            grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());
        } else if (grid.getCellContentType(row, col) ==
                IEssGridView.CELL_CONTENT_TYPE_MISSING) {
            grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());
        }

        IEssOpUpdate opUpd = cv.createIEssOpUpdate();
        cv.performOperation(opUpd);

        }catch (EssException x){
            System.out.println("ERROR: " + x.getMessage());
        }
%>
<font size="5"><%="WriteBack Ended" %></font>
		<font size="5"><%="WriteBack Ended" %></font>
        </body>
</html>
<%
    //response.sendRedirect("http://localhost:9704/analytics");
%>

I would not explain this in detail as the JSP is self-explanatory. But there is one aspect to writebacks using JAPI that one would have to be aware of. Whenever JAPI is used for writebacks ensure that you have a IEssGridView which basically visualizes your output as an Excel add-in grid. The rows and columns are numbered in an increasing order from zero.

The code snippet that actually does the writeback is given below

Once this is done, this JSP would have to be deployed on a web server that is accessible to BI EE. In order to accomplish that, create a custom WAR profile which would basically contain all the dependent jar files and also the manifest related information.

Then deploy this WAR file on the same application server as BI EE (or OC4J).

Once the deployment is done, test the jsp page by passing the url as shown below

http://localhost:9704/WriteBack/WriteBack.jsp?&p_Value=2000&p_Market=Market&p_Product=Product&p_Accounts=Accounts&p_Scenario=Actual&p_Year=Qtr1

Once this is done, lets create a BI EE report containing all the dimensions. Remember that for writebacks to work in Essbase, we would need a value/member from every dimension. Create a custom column and in the custom colum enter the formula as shown below

'<form name="input" action="http://localhost:9704/WriteBack/WriteBack.jsp">
<input type="text" name="p_Value" size="10"/>
<input type="submit" value="Update" />
<input type="hidden" name="p_Accounts" value="Sales" />
<input type="hidden" name="p_Year" value="'||"Year"."Gen2,Year"||'"/>
<input type="hidden" name="p_Market" value="'||Market."Gen2,Market"||'"/>
<input type="hidden" name="p_Product" value="'||Product."Gen1,Product"||'"/>
<input type="hidden" name="p_Scenario" value="'||Scenario."Gen2,Scenario"||'"/>
</form>'

And change the column format to HTML.

Basically what the formula above does is, it create an HTML input field in the report itself. One parameter p_Value would be obtained from the value of the textbox and the remaining hidden form parameters will be used for passing the dimension member attributes to the url.

If you go to the report now, you can alter the existing values and on submit, these values would be submitted back to Essbase.

The same methodology can be used for writing data back into Oracle OLAP as well. This is one method of doing writebacks. There is one more method as well wherein one can use the Writeback template itself. I will discuss that in future. But for now, the above should give an idea of how Writebacks can be enabled in BI EE against Essbase data sources. There are quite a few moving parts. But again these are required due to the nature of the connectivity as of today.


Categories: BI & Warehousing

VMWare ESXi Hypervisor

Duncan Mein - Fri, 2009-05-22 06:01
Server virtualisation is something I have been using for a few years now both at work and at home. I mainly use VMWare Fusion on my MacBook Pro and VMWare Workstation / Player on an old Windows Laptop. For everyday tasks these products are amazing as i can run an XP VM on my MacBook Pro for all those times I need to open MS Project or run TOAD.

On my server at home, I didn't want to install a host OS and then install VMWare Workstation / Server to host several Linux VM's. Instead I explored the option of a "Bare Metal" hypervisor from VMWare (ESXi 3.5 Update 3). A hypervisor is a very small linux kernel that runs natively against your servers hardware without the burden of having to install a host OS. From here you can create and manage all your VM's remotely using the VMWare Client tool.

For more information in ESXi, check out VMWare's website here

Reading the documentation suggested that ESXi was very particular about the hardware it supports and so began the quest to build a "White Box" ESXi server at home. Whilst this is not supported by VMWare, I wanted to share the process and components utilised in case anyone else was thinking of building there own ESXi Server.

Step 1. Download the ESXi 3.5 ISO from the VMWare Website (You will need to register for an account)

Step 3. Create a Bootable USB Stick (yes you can boot the Hypervisor from a USB stick if your Motherboard supports it) by following this concise guide

Step 3. Plug your USB stick into your server and configure the IP Address, DNS Server, Hostname and Gateway.

Step 4. Open a Browser window on a client machine and navigate to: http://ip address to download the VMWare Client tool.

Step 5. Enjoy using ESXi

My Server Configuration:

CPU: Inter Core i7 920
MOBO: Gigabyte GA-EX58-UD5
Memory: 12GB of Corsair DDR3 XMS3 INTEL I7 PC10666 1333MHZ (3X2GB)
SATA Controller: Sweex PU102
NIC: 3Com 3c90x

The following sites list loads of compatible hardware with notes and issues encountered:

http://ultimatewhitebox.com/index.php

http://www.vm-help.com/esx/esx3.5/Whiteboxes_SATA_Controllers_for_ESX_3.5_3i.htm

With the setup outlined above, I can run 5 Linux machines running Oracle Database, Application Server, APEX and OBIEE without any issue.

If this is something you are interested in evaluating, i can recommend spending a few hundred pounds on the components as its beats spending thousands on a supported Server from HP.

Oracle BI EE 10.1.3.4.1 – Scheduling Essbase/Planning Calculation Scripts – Action Framework & Custom Java Remote Procedure calls

Venkat Akrishnan - Thu, 2009-05-21 11:48

In the blog entry here, i had shown you how to run an essbase calculation script from the BI EE dashboards. To make it even more complete, lets look at a means of calling a calculation script through BI Scheduler. So, our idea is to use BI EE as a scheduler to schedule Essbase/Planning calculations. In order to do that there are certain pre-requisites. I have covered this in my blog entry before here. Once the setup is done, lets open the JDeveloper and create a new project. Include the schedulerrpccalls.jar, Ess_japi.jar and Ess_es_server.jar as part of the project properties.

The idea is to basically write a custom java program which in turn will run an Essbase calculation script through the Java API. The java program would then be bundled in the form of a jar file and called from an iBot. Use the below code to login to Essbase and then to execute the calculation.

package bischeduler;

import java.io.*;

import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;

import com.essbase.api.base.*;
import com.essbase.api.dataquery.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;

public class BIScheduler implements SchedulerJavaExtension{
    public BIScheduler() {   

        String s_userName = "admin";
        String s_password = "password";
        String s_olapSvrName = "localhost";
        String s_provider = "http://localhost:13080/aps/JAPI";

        try
        {
        IEssbase ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
        IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
        IEssOlapServer olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
        olapSvr.connect();

        IEssCube cube = olapSvr.getApplication("Global").getCube("Global");
        String maxLstat = "import database 'Global'.'Global' data connect as 'global' identified by 'global' using server rules_file 'UnitsLd' on error abort;";
        //cube.loadData(IEssOlapFileObject.TYPE_RULES,"UnitsLd",0,"SH",false,"global","global");
        cube.calculate(false,"CalcAll");
        //cube.beginDataload("UnitsLd",IEssOlapFileObject.TYPE_RULES,"global","global",0);
        //IEssMaxlSession maxLsess = (IEssMaxlSession)dom.getOlapServer(s_olapSvrName);
        //maxLsess.execute(maxLstat);
        }catch (EssException x){
            System.out.println("ERROR: " + x.getMessage());
        }

    }
    public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException{
        new BIScheduler();
        System.out.println("JobID is:" + jobInfo.jobID());
        System.out.println("Instance ID is:" + jobInfo.instanceID());
        System.out.println("JobInfo to string is:" + jobInfo.toString());
    }
    public void cancel(){};

        public static void main(String[] args) {
        new BIScheduler();

    }
}

Once this is done, create a deployment profile to bundle the code as a jar file. Ensure that the deployment also bundles the 3 jar files mentioned above. Then deploy the jar file.

Now copy the deployed jar file to the {OracleBI}\web\javahost\lib directory (or a directory that you have mentioned as the lib path in the Java host config.xml). Now create an ibot and then in the advanced tab call the deployed jar file as shown below.

You can create ibots in such a way that once the java class run is completed, it would trigger another ibot to deliver a report based on an Essbase cube. This will ensure that the administrator is sure of the fact that the calculation has run successfully.

Now you should be able to run essbase calculations from BI Scheduler directly.  One can even run ODI packages using BI EE. I would cover that as well in future. Keep watching this space next week for a method to do write backs into Essbase. I would present a couple of methods and both of them can be used effectively in a prod like environment.


Categories: BI & Warehousing

Oracle BI EE 10.1.3.4.1 and Essbase Connectivity – Report Based and Essbase based Grand Totals – Answers Based Aggregation

Venkat Akrishnan - Wed, 2009-05-20 05:13

This post here by Christian prodded me to write about another interesting feature in the BI EE and Essbase Connectivity. As you would probably know BI EE supports report based grand totals/sub-totals in a table view. There are 2 types of totals. One is BI Server based totals wherein BI Server would do the totalling on a result set. The other is data source specific totalling wherein the query is fired back to the underlying data source to obtain the totals. For example, lets just quickly import the Demo->Basic cube in to the repository and build a very simple report as shown below.

As you see, it is a very simple report containing a report based total and a sub-total at the market level. Before going further, lets look at the outline of the Basic cube first. As you see, every dimension top member is set as a stored only member.

If you look at the MDX of the above report, you would notice that 3 MDX queries would be fired. One for the base report , one for the grand total and the other for the market sub-total.

The aggregation for the Measure Sales is Aggr_External in both the physical and logical layer. In Answers, the aggregation is set as default. Now, lets go to the outline and convert the Year Dimension top member to be a label only member as shown below.

Now, try running the same report above. You would notice that the report level totals and sub-totals are totally wrong as shown below.

The main reason for this is the fact that since we have converted the topmost member of the outline to be label-only. So for the Year dimension, it will always pick the Qtr1 value instead of totalling all the quarters. For report developers, this would turn out to be an absolute nightmare considering the fact a report based total is created under the assumption that, the totalling is done on the report and not at the data source level. Now, from Answers lets change the aggregation os the sales measure to SUM.

And look at the report.

Basically, a report level SUM ensures that all the custom aggregations/totalling occuring in a pivot table/table are done at the report level instead of at the Essbase layer. So by default, ensure that you always have SUM at the report level to ensure that you do not get wrong answers especially for totals and sub-totals.

This should have given you an idea of how the aggregations at 3 layers (Physical, BMM and the Answers) can affect a report. I would cover the usage of Report Aggregations across different BMM and Physical Layer aggregations in the future.


Categories: BI & Warehousing

Pages

Subscribe to Oracle FAQ aggregator