OraNA :: Oracle News Aggregator

Note: I wrote this last Thursday but, due to vacation and travel, I didn't get a chance to post it until today..

Kaleidoscope 2009 is almost over and, unfortunately, I haven't had the time to keep those of you not lucky enough to be in Monterey up to date. The buzz is that this has been the best Hyperion related conference ever. Literally everyone I have talked to, and every one of the other ODTUG board members have talked to, have given Kaleidoscope a big thumbs up!

Some of the highlights for me:

• Hyperion Sunday Symposium. Oracle brought a bunch of development people to talk about what they are working on for the future and it looks very exciting for those of us from the Hyperion community. I plan to blog a summary of Sunday later.


• Hands-on labs. They were packed and I heard people were almost fighting to get in.. We are hoping to greatly expand the labs next year. We did learn that we were trying to fit too much into the time given for the lab and we will attempt to address that next year. Kudos to Monty Lateolais and our own Derek Hill for the many hours.


• Midnight Madness. This late-night, Hyperion 'stump-the-panel' discussion lasted the full 2 hours (thankfully starting at a more merciful 10 pm instead of midnight) and literally none of the 100 to 150 people there left early. As my expertise is really in the Essbase API, I served as the moderator for the panel and added color commentary where applicable. Perhaps the most amazing fact with Midnight Madness is that all of those people were drawn to the session without the offer of major quantities of free alcohol. There was, however, as much coffee, chocolate and Red Bull that your could consume.


• Oracle ACE Directors Sundown Session. The Sundown Session was another great panel discussion featuring all 4 Oracle ACE Directors in the Oracle BI Community including me, Mark Rittman from Rittman-Mead Consulting (and the leading expert on OBIEE-Essbase integration) and the team of Edward Roske and Tracy McMullen from interRel Consulting. I enjoyed this session a bit more as someone actually asked a Java API question (although it was really a Java IDE question).. The answer? I don't normally use Oracle JDeveloper but rather use IntelliJ. It is really a matter of WYLIWYL ("What You Learn Is What You Love").


• Wednesday night Special Event. The special event this year was dinner, followed by a comedian and finally dancing. The dinner was buffet style featuring more of the excellent food Kaleidoscope is known for; my favorite was the bay shrimp/avocado salad and the grilled salmon; the roast beef and roast turkey breast stations looked delicious). The comedy of Don Friesen was extremely funny and I was lucky enough to sit front-row center and not get picked on. You can see him on his website at http://www.donfriesen.com/. Finally, the music/dancing portion won me over when one of the first songs the DJ played was one of my favorites, "Pride and Joy" by Stevie Ray Vaughn.


• Working with the ODTUG Board and Oracle. I had *many* meetings this week between both the ODTUG Board and Oracle people and have continued to work on behalf of the Hyperion user community to provide great conference, great educational and great networking opportunities. Every Oracle employee we talked with was very enthusiastic about Kaleidoscope!

Kaleidoscope 2009 was also a coming out party for Dodeca. My Monday morning vendor session on Dodeca was so full I heard they had to turn people away. We also gave away a bunch of Dodeca T-Shirts at the session and at our booth.

I am writing this in a Thursday morning session that has a fairly large room that is nearly half full (which in itself is amazing for the last half-day of a conference). There are a quite a number of people wearing their Dodeca shirts today including me!

If you attended this Kaleidoscope, please give me the honest feedback on how we can make it better next year (and yes, we are painfully aware of the bandwidth issues here in Monterey).

Search Engine Land has a post, Michael Jackson’s Death: An Inside Look At How Google, Yahoo, & Bing Handled An Extraordinary Day In Search, on how web traffic spiked at some of the web's leading properties like Google, Facebook, and Wikipedia, as a result of Michael Jackson's death.

All good and fine, and a sad day for fans of Michael Jackson, the king of pop as he was known as, but a sad day

Go to http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html and accept the license.






Browse down to the section that says "Portal, Forms, Reports and Discoverer". 
Currently there are downloads available for Windows and Linux:

Windows x86: Disk1 (File 1) , and Disk1 (File 2)
Linux: Disk1 (File 1), and

Great to read about the Oracle Discoverer Web Services that have been released for public consumption as part of Discoverer 11G and Fusion Middleware 11G:

Here and Here

To quote from the User Guide:

The Oracle BI Discoverer Web Services are part of an Application Programming Interface (API) that enables a client to do the following:
- Obtain Discoverer connections, workbooks, and worksheets
- Execute worksheet queries
- Obtain worksheet content using the SOAP protocol (version 1.1 with JAX-WS/document wrapped format)

I've worked on a number of sites that have made a big investment in Discoverer reporting. It will be interesting to see how this new functionality will be used in the future.

If you see the Discoverer 11g Documentation library at http://download.oracle.com/docs/cd/E12839_01/pfrd.htm, you will notice the familiar set of docs, with one new addition. There is now a doc for the Discoverer Web Services. The "Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Web Services API", 11g Release 1 (11.1.1), Part Number E10412-01 can be viewed at

In the last 2 blog entries, i had covered 2 new features of EPM 11 Essbase. They were Format Strings and Varying Attributes. In today’s blog entry we shall see another good feature that was introduced in the EPM 11 release called as XOLAP. Though I have covered this before here, i  thought it would make sense if i introduce this again in the context of the BI EE – Essbase connectivity.

Prior to XOLAP, Essbase supported HOLAP (still does) wherein one can drill from an Essbase cube to a relational source(only on BSO cubes) thereby providing a drill-through. It also supported something called as LRO’s in BSO cubes wherein one can attach an artifact to a database cell. What was not possible though was visualizing relational and Essbase data together. For example, we might have Actuals loaded inside Essbase but Budget might be obtained directly from a relational source. In such cases HOLAP cannot be used directly(though some workarounds are possible). With the advent of BI EE – Essbase connectivity in the 10.1.3.3.2 release, such complex integration cases have been made possible within BI EE framework using conforming dimensions. For details on how this is done, check out the ODTUG white paper here that Mark and myself had created. But what if we want this kind of reporting in Excel-addin or smart view or any other downstream tools that use Essbase. This is where XOLAP can be very helpful.

For the sake of demonstration, i would use the Global schema here. Lets first start with building a XOLAP cube using the Essbase studio. Start with importing the data source and then creating the model.

Then build your hierarchies and deploy the cube as a XOLAP cube. Remember whenever a XOLAP cube is created, it is an ASO cube. Also, it gets created with “Duplicate Members” turned on.

Deploy this cube. Once the deployment is done, you can login to Excel-add in and view the data.

 

We now can report directly on a relational source through Essbase from Excel-add in. Our idea is to have a similar reporting structure but also have one more measure called Price which would be coming in directly from Essbase itself. In order to achieve this, create another ASO cube directly in EAS or in the Studio with a similar dimensional structure. It is not necessary that the ASO cube should have an exact dimensional structure as the XOLAP cube. But in our case for demonstration, we would create an exact similar structure. There would be another measure in the ASO cube called as Price.

Now load some data into Price measure alone and aggregate it.

Basically we have 2 cubes, one reporting on relational data using XOLAP and the other is a normal Essbase ASO cube. Now, in order to have a report with both Units and Price measure together, we need to create an additional ASO cube called GlobTarg which will be fed by the XOLAP and the ASO cube through transparent partition. So, lets first create the outline of GlobTarg first. Ensure that it has both Price and Units measures.

Now create 2 transparent Partitions,one with the XOLAP cube as the source and GlobTarg as the target and the other with the Price ASO cube as the source and GlobTarg as the target.

While creating the partition, map the corresponding source measures to the target measure. Once this is done, you can report directly on GlobTarg ASO cube. And you should be able to report both on the ASO as well as the relational source together.

Use this link - http://www.oracle.com/features/hp/oracle-fusion-middleware-11g-launch.html - to register for the event.

Apart from the very important reason that Fusion Middleware is the technology platform and foundation of Fusion Applications, Fusion Middleware 11g is also the vehicle for the release of Discoverer 11g.

I have obtained all three examples from Paul Kedrosky's blog, Infectious Greed.http://rebis.reidin.com/home.htmlA fake 3D bar chart. And a gradient effect. Two egregious errors in one chart.http://paul.kedrosky.com/archives/2009/06/crude_oil_price_1.htmlAn otherwise useful chart that is marred by the use of distracting gridlines, that overpower the data plotted. The obtrusive gridlines are

Another excellent feature that was introduced as part of the EPM 11 release was the support for varying attributes. Varying attributes help in providing different perspectives of multiple attributes of a dimension over time. One can visualize this as SCD 2 in a relational world. For example, lets take the Sample –> Basic cube and look at the various attributes that have been defined.

As you see, this cube has 5 different attributes defined on Product and Market dimensions. As of 9.3.1 release of Essbase one can have only static attributes defined. If varying attributes had to be defined, they had to be modeled as separate individual dimensions. Static attributes are those attributes that remain constant for a specific member. For example, lets assume that a product X has a static weight of Y ounces when it was introduced. If the manufacturing company decides to repackage the product X with a weight of Z ounces, then as of 9.3.1 release all the sales data mapped to Y ounces would switch over to Z ounces(similar to SCD-1 in a relational DW world). But in most cases, we want to see the sales data corresponding to their weights so that one can analyze the sales drop or increase due to the new product packaging. This is called as varying attributes over time. Sometimes, the same product X can be packaged with Y and Z ounces depending on Market. In this case, the weight varies over Time as well as Market.

With the advent of EPM 11.1.1.0, one can enable tracking of metrics over varying attributes as well. In the above outline, lets take the example of Pkg Type attribute. This has 2 values

1. Bottle
2. Can

This packaging can vary for products across multiple Markets. Also the packaging can vary for a product in a specific market over time. To enable varying attributes, one would have to first enable this feature while creating an outline or later.

In order enable the attribute Pkg Type on the product dimension, one would have to choose the attribute and the set of independent dimensions. Independent dimensions are those dimensions over which the attribute varies like Market and Time.

Continuous dimensions(in the screenshot above) are typically those dimensions like Time where there is a chronological order and where we can specify a range. After this has been enabled, while setting the attribute for each product, the Market and Time would have to be chosen as well. For continuous attributes, one can specify a range.

For each product set the Time range over which it is planned to be sold and also the market. In the above example, the product 100-10 is sold in New York Market from Jan till Dec in Bottle Type and in the Massachusetts Market from Jan to Dec in Can type. The same kind of association would have to be done for each and every Market for the corresponding Pkg Type.

The above basically provides a relational visualization in a multi-dimensional cube. This also provides analysis of data in multiple perspectives. For example, we might be needing a report wherein we would like to analyze the sales for the list of products that were sold as Bottles in New York from Jan-Dec with a perspective of the attribute setting that we had in July. These kind of queries can be answered pretty easily by Varying attributes. In a future blog entry we shall see how these varying attributes can be leveraged from BI EE. As of the current release in BI EE, one can only use the default query context/perspective. One cannot alter the perspectives due to the limitation of modifying certain parts of the MDX query in BI EE. Having said that, it is possible to leverage the varying attributes using the default perspective. I will cover this in the coming weeks.

Handy when you don't have a local photoshop at hand for customiztions:

http://www.smartsell.nl/index.php?option=com_wrapper&view=wrapper&Itemid=295

Till Next Time

Well we’re back now from two weeks in the States, the first week of which was vacation in San Francisco and Yosemite, the last was at ODTUG Kaleidoscope 2009 in Monterey. Well done to the ODTUG team and to the Oracle ACE program for an exceptionally-well organized conference, as I’ve said in the past ODTUG Kaleidoscope is big enough to draw in the best speakers but small enough to get to meet everyone, and Mike, Kathleen, Crystal and the rest of the team make us all feel very welcome. Thanks again for the invite over.

As I had the family with me I spent a bit less time at actual sessions this time, but still made it to the key events. Tim Tow and Edward Roske did an excellent job of organizing the Hyperion stream and gave me a few ideas for the BI stream for next year; on reflection, although we had some good BI speakers this year the BI stream itself was a bit disjointed and didn’t really have the coherence that the Hyperion stream did. Next year our plan is to take a much more proactive approach to the BI content and try and organize it in a similar way to the Hyperion sessions, with streams dedicated to ETL + DW, OBIEE and so on. If anyone reading this is likely to go next year and wants to help shape next year’s agenda, drop me a line and we’ll make a start soon.

Rittman Mead had a great turnout at the conference, with our names on the lanyards and Stewart Bryson kicking off the sessions with a deep look into the functonality of Oracle Warehouse Builder. Stewarts’ presentation is on our Articles page and here’s a quick video clip of him presenting:

In case you’re wondering, that’s Joe Leva (our US MD) and Lewis Cunningham (Oracle ACE Director) that I pan to towards the the middle of the video.

My two sessions were on Optimizing Oracle BI Enterprise Edition, and a joint paper I wrote with Venkat on Creating Hybrid Relational/Multi-Dimensional Data Models using OBIEE and Essbase. Both papers are on are articles page, including a white paper that accompanied the Essbase paper that goes into a number of different integration use-cases. Now that Venkat has joined us you can expect him and I to be authoring a bunch of other papers together, and to start things off we’ve had an updated version of the Essbase and OBIEE paper accepted for Open World 2009 in San Francisco.

Unfortunately as Venkat has only just left Oracle and joined us he couldn’t make it in person to Monterey, however I had a good stand-in as my six year-old son Scott was able to introduce me instead … we’d talked about this the evening before and he was up for doing a short introduction, and here he is introducing me at the start of the talk:

Well done to Scott! I’ll have him doing the demos next time…

Well for now it’s a quick turnaround and repack, as I’m off to Istanbul tomorrow to deliver a two-day Oracle 11g Data Warehousing seminar for Oracle University. After that it’s back up to London for a couple of days of client work, and starting to write the presentations and papers for Open World 2009.

I received a hint on OBIEE time series functions from my friend Peter S. Apparently the time series functions AGO and TO_DATE don't work when you're using it on tables with fragmented LTS.

A quick test yielded this:









State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22042] AGO function may not be used on 'Amount' because its source is horizontally partitioned. (HY000)

Checking metalink, I found document 739584.1 - AGO function with combined multiple sources. Enhancement request #7438154 has been raised and is targeted for 11g.

Cheers all!

Today I’m gonna be mean.

Before we start though, here is my definition of a BI consultant. In my eyes, a BI consultant is the guy at the frontend of a data warehouse/business intelligence project. He creates and designs reports, and is responsible for setting up the logical layer between the data source(s) and the ad hoc query tool. His main task is to retrieve a s**tload of data in an ultra fast way to keep the business folks happy. In this narrow definition a BI consultant is not a data integration specialist or a data warehouse designer, and certainly not a data miner.

In medias res: A while back I was working for a consulting firm. They had this principal BI consultant and one day I had the “luck” to work in a project with him. We ran an INSERT statement to load a couple of million rows into a table. After a minute or so my friend got nervous and started to query the table to check on the progress of our data load. He got very pale when his query did not return any records and seriously thought he had discovered a major bug in the database system. But this guy had a tool. A BI tool. He was the hero of charts, ad hoc, and drag and drop. The master of the Business Objects universe.  Was he able to string a simple query together? No. Did he have the skills to get him out of his performance mess. Nope.

Why is it not enough to just master the BI tool? And more importantly what skills do you need to become a true master?

Let me try and explain with an analogy. Airline pilots are highly paid individuals. Rightly so. Do you think this is because of their fabolous auto pilot skills? Or has it to do with the fact that they are trained to do the right things in extremely precarious situations? If you want to become a pilot you need years and years of training and experience. But once you have mastered the art of flying, it shouldn’t matter much whether you fly around in a Boeing or an Airbus. It’ll take a while to adapt but at the end of the day a plane is just a plane. It obeys the laws of gravity. The same is true for BI tools. Once you have mastered the core skills and concepts you can quickly and easily transfer them to any BI tool in the world and be a great success. For example with my knowledge of Business Objects, it took me a long weekend to learn the core functionality of OBIEE.

So what are these skills? As we have learned, the main task of a BI consultant is to query data fast, ultra fast that is. A report should not take longer than ten seconds to run. Having an excellent knowledge of SQL is imperative for this. At a minimum this means to be able to write complex sub-queries and multi table joins. In a data warehouse environment, however, this is usually not enough. Often a BI consultant needs to be able to query data recursively and do complex inter-row calculations. In the past we had to use expensive self-joins for this type of requirement. Not anymore though. Most if not all RDBMS today allow you to use analytic functions to perform inter row comparisons and aggregations. I am still perplexed that few consultants out there have mastered this essential skill. I myself first learned about the real power of analytic functions from my very good friend Maciej Kocoon (Maciej, pints next weekend?). A true magician with SQL. Another great source to learn more about analytic functions is the O’Reilly SQL Cookbook.

Of course, a BI consultant also needs to be able to troubleshoot performance problems. This normally means that he needs to have a good understanding of database concepts in general, and the particular  database system that is used in the project. At a minimum he needs to be able to trace queries and to read and interpret explain plans. Data modelling should be another core competency of a BI consultant. This includes both 3NF and dimensional modelling techniques. It also can’t do any harm to have business analysis skills to understand report requirements and visualisation techniques to create killer reports. Throw a good understanding of web technologies into the mix to customise the reporting environment, and you should be able to excel in any project. Ahh, I almost forgot OLAP, which should be easy once you have mastered analytic functions.

If you are an employer and have to choose between a guy who’s had years of experience in a particular tool but knows precious little about the inner workings of a database, data modelling, and SQL and someone who is a database expert with excellent knowledge of SQL, but has little knowledge of the BI tool at hand, always go for the latter. It will pay off. Guaranteed. And it will save you from creating embarassing support tickets..

If you had read my blog entry here, i would have shown how to go about creating textual measures in Essbase. Though that was a new feature introduced as part of the EPM 11 release, one related feature that i did not cover was the introduction of Format Strings. This probably is one of the very important features from an end user reporting standpoint. In a couple of implementations that i was involved in on 9.3.1, i faced a situation wherein we had to control the format of the data within Essbase itself. Unfortunately, since that was not possible in 9.3 release, custom formatting had to be applied in each and every downstream tools like Excel Add-in, HFR, BI EE etc.

Format strings help in controlling the output format of the measure values. For example, if we have an Expense account and a revenue account, the normal reporting requirement is to show the variance of the current quarter Actuals with the last quarter Actuals for both the type of accounts. For Revenue accounts, when current quarter is more than last quarter, then the variance would be positive else it would be negative. For Expense accounts, when the current quarter is less that last quarter, then the variance would be positive else it would be negative. Also, all negative values would have to be shown within braces and not as negative values.

Normally to implement the above requirement, we would have to write a custom formula to change the variance formula based on the Expense/Revenue UDA in an ASO cube(BSO cube has Expense/Revenue based reporting properties. But it is very limited in its usage). Also, adding braces to negative values would have to be done in the front end. Lets see how Format Strings can help us in achieving this requirement. Lets take the example of the Sample – Basic BSO cube. The strange aspect to the Format Strings is the fact that the implementation is done using MDX even in a BSO cube (should give a hint about the strategic direction :-)).

Lets first look at the outline. There are 2 measures Sales and COGS. Sales is a revenue measure and COGS is an expense measure.

Now lets create another member called Variance as shown below in the Scenario dimension.

This variance member is actually updated through a calculation script shown below (Format strings would not work on members having a member formula in BSO cubes)

SET CACHE HIGH;SET LOCKBLOCK HIGH;

CLEARDATA "Variance";

CALC DIM ("Year");

"Variance" = "Actual"->&CurrQtr - "Actual"->&LastQtr;

CALC DIM ("Market","Product");

Run the calc script after the data load. Following would be the variance values in Excel add-in.

As you see, since there was no formatting applied on the Variance values, both COGS and Sales are getting treated the same way i.e shown as positive numbers. Now, in order to apply format strings on this, lets first convert the outline into Text measure compatible outline.

After that, go to the member properties of the Variance member and add the MDX shown below

MdxFormat(IIF(Is([Measures].dimension.CurrentMember, [COGS]),

    CASE WHEN cellvalue() > 0 then Concat(Concat("(", numtostr(CellValue())), ")" ) else numtostr(-cellvalue()) end,

    CASE WHEN cellvalue() < 0 then Concat(Concat("(", numtostr(-CellValue())), ")" ) else numtostr(cellvalue()) end

))

Now if you go to Excel add-in, and retrieve the same data, you would notice that the accounting format would have got applied automatically.

This is an excellent feature. In fact using this alone, one can mimic textual measures that i explained before. In a future blog entry i would show how this can be leveraged from other downstream tools like HFR, BI EE etc.

Getting people to think is a good thing. However, getting them to think that your ad has a chart that just does not make sense is not a good thing.The more people see this chart, if you can call it a chart, the more they will have questions.Who are these so called 'Other Guys'? Is Google Gmail one of them? Is Hotmail there? What about the great local email provider from my country?These features

Whilst Mark, Stewart are Joe are enjoying Monterery, I am on the train going up to London to meet Borkur to do a joint presentation entitled High Availability in Oracle BI EE in the BI Enterprise stream of the UKOUG Conference Series Hyperion & BI 2009 event. The paper is a fairly technical one, going through the steps you need to perform to cluster OBIEE and discussing some of the pros, cons and pitfalls. If you are in the area please come along. I will post the slides after the event.

In a prior blog entry here, i had shown the advantages of using the MODEL clause. I also showed how the MODEL clause can be used to visualize the relational data in a multi-dimensional format. In most cases, using the MODEL clause alone can be sufficient. Just to recap, the MODEL clause divides your resultant data into 3 parts

1. Partition By set of Dimensions – Similar to Index entries in Essbase (sparse dimensions)
2. Dimension By set of Dimensions – Similar to Blocks of Essbase (dense dimensions)
3. Measure Dimension

So, whenever you use MODEL, Oracle constructs a multidimensional array for each partition by the dimension(s) combination value. The multi-dimensional array would be dimensioned by the dimensions specified in the DIMENSION BY clause. Though a multi-dimensional array is created, the created array contains only the valid intersections present in the fact table and not every intersections that are possible. For example, consider the sql query below

SELECT FISCAL_YEAR_DSC,CHANNEL_DSC,

UNITS

FROM

(

select sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC

from

CHANNEL_DIM T10881,

TIME_DIM T10939,

UNITS_FACT T10976

where

( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )

group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC)

MODEL

PARTITION BY (FISCAL_YEAR_DSC)

DIMENSION BY (CHANNEL_DSC)

MEASURES (UNITS) RULES (UNITS['Total'] = SUM(UNITS)[ANY])

ORDER BY 1,2

As you see, the above query produces Sub-Totals for every Fiscal Year. But if you notice in the output of the query, we would be missing the year FY-06 which has no transactions in the fact table. Similarly even the Channel Television is missing in the output since it does not have any fact level transactions. In some cases, though there are no transactions we need the capability to show the non-existent (in fact tables) dimension values in the output(data densification). This is where Partition Outer Joins come in very handy. One can visualize partition outer join as a Cartesian product of dimensions. The major difference between a Cartesian product and the partition outer join is, one can have a control on Cartesian product using Partition Outer Join. Stewart has already covered Partition Outer Joins in a prior blog entry here.

For example, lets say we have a requirement to show all the dimension values of Channel and only the transaction values of Fiscal year in our above report i.e. we need to bring in the Television Channel as well into our above report for Year.

Usually we would approach this using a normal outer join with the Channel Dimension as shown below.

SELECTA.FISCAL_YEAR_DSC FISCAL_YEAR_DSC,B.CHANNEL_DSC CHANNEL_DSC,NVL(A.UNITS,0) UNITS

FROM

( select sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC

from

CHANNEL_DIM T10881,

TIME_DIM T10939,

UNITS_FACT T10976

where ( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )

group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A

RIGHT OUTER JOIN

(SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

But again the above outer join only provides a single row for Television channel. But our requirement is to have the same Television Channel for every Fiscal Year that has transactions. This is where Partition Outer Join would be of great help. Lets modify the above query to the one shown below

SELECTA.FISCAL_YEAR_DSC FISCAL_YEAR_DSC,B.CHANNEL_DSC CHANNEL_DSC,NVL(A.UNITS,0) UNITS

FROM

(

select

sum(T10976.UNITS) as UNITS,

T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC,

T10881.CHANNEL_DSC as CHANNEL_DSC

from

CHANNEL_DIM T10881,

TIME_DIM T10939,

UNITS_FACT T10976 where

( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )

group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A

PARTITION BY (A.FISCAL_YEAR_DSC)

RIGHT OUTER JOIN

(SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B

ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

Now, if you notice we now have non-existent(in the fact table) all channel dimension values for every transactional Year. We can extend the same concept to show both non-existent year (FY-06) as well as channel dimension values using the query below.

SELECTA.FISCAL_YEAR_DSC,B.CHANNEL_DSC,NVL(A.UNITS,0)

FROM

(

SELECT B.FISCAL_YEAR_DSC FISCAL_YEAR_DSC, A.CHANNEL_DSC CHANNEL_DSC, NVL(A.UNITS,0) UNITS

FROM

(

select sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC

from

CHANNEL_DIM T10881,

TIME_DIM T10939,

UNITS_FACT T10976

where ( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )

group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A

PARTITION BY (A.CHANNEL_DSC)

RIGHT OUTER JOIN

(SELECT DISTINCT FISCAL_YEAR_DSC FROM TIME_DIM) B

ON (A.FISCAL_YEAR_DSC = B.FISCAL_YEAR_DSC)) A

PARTITION BY (A.FISCAL_YEAR_DSC)

RIGHT OUTER JOIN (SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B

ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

This can further be modified to include the sub-totals using the MODEL clause.

SELECTFISCAL_YEAR_DSC,CHANNELS,NVL(UNITS,0)

FROM

(SELECT B.FISCAL_YEAR_DSC FISCAL_YEAR_DSC, A.CHANNEL_DSC CHANNEL_DSC, NVL(A.UNITS,0) UNITS

FROM

(

select

sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC

from

CHANNEL_DIM T10881, TIME_DIM T10939, UNITS_FACT T10976

where

( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )

group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A

PARTITION BY (A.CHANNEL_DSC)

RIGHT OUTER JOIN

(SELECT DISTINCT FISCAL_YEAR_DSC FROM TIME_DIM) B

ON (A.FISCAL_YEAR_DSC = B.FISCAL_YEAR_DSC)) A

PARTITION BY (A.FISCAL_YEAR_DSC)

RIGHT OUTER JOIN (SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B

ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

MODEL

PARTITION BY (FISCAL_YEAR_DSC)

DIMENSION BY (B.CHANNEL_DSC CHANNELS)

MEASURES (UNITS) RULES (UNITS['Total'] = SUM(UNITS)[ANY])

ORDER BY 1,2

The primary advantage of using the Partition By Outer Join and MODEL clause together is the fact that one can now do custom member based allocations, calculations etc directly on a relational data source. If you do not have Essbase/Oracle OLAP and would still like to do the complex data manipulation on a relational source, MODEL and Partition By Outer Joins can come in very handy.

Stewart wrote and posted his last blog posting while on the plane to Monterey via gogo. Frankly, spending as much time on planes as we do, I am jealous and need to start flying with a better class of airline. Actual live internet at 35,000 feet? Now that is first class.

Speaking of first class, ODTUG Kaleidoscope never disappoints, the level of the sessions is consistently excellent. People tend to vote with their feet and the word is that attendance is up this year as well. This certainly fits with what we’ve been seeing at the presentations. This is the first conference I’ve gone to in the last 9 or 10 month that I haven’t presented at and it is nice to be able to attend more sessions. This is actually my first trip to Monterey and variation in landscapes in California continues to impress.

Stewart delivered an impressive presentation on considerations for selecting OWB. There were some particularly interesting points regarding the advantages and trade offs of the cube and dimension accelerators. The question and answer session afterward suggests that the new packaging of OWB with ODI has helped to reduce if not eliminate customer concern regarding which tool to use for new development.

Mark moderated the BI/DW panel with Mike Durran, Dan Vlamis, Jean-Pierre Dijcks and Michael Armstrong-Smith (Left to Right). There were some provocative opinions expressed regarding possible future paths for the essbase engine and how it might or might not integrate with the Oracle technology stack. Perhaps I’ll kick that bees nest in some future post. It is a topic that I think would generate a lot of discussion. Mark also presented our OBIEE optimization methodology. This approach focuses on specifically on measuring and managing the performance of the Oracle BI server. It joins our methodology for improving the performance of the Oracle BI applications. If you are reading this before today’s sessions, you will also want to stop by Mark’s presentation “Creating Hybrid Essbase/Relational OBIEE Logical Models”.

I’d like to welcome Venkat to the team. One of the reasons that Stewart and I were drawn to working with Mark and Jon and forming Rittman Mead America was their ability to identify and recruit world class talent. Venkat is a great example of such talent and we are very happy to have him on board.

A highlight of this conference for me was finally meeting Stanley. We may have been roommates at Collaborate, I really can’t say, I was pretty jet lagged at the time because I had flown there directly from Singapore. But this time we got to meet and he was good enough to pose for a picture with me, no autograph though. Stanley is Dan Norris’ Oracle Ace vest, he has his own twitter tag #stanleyace and his own blog (http://www.wtfistheacevest.com/). Apparently, he had gone diving with Dan in Monterey Bay over the weekend, and frankly he did not smell like he had showered since. This is why I only half have my arm over his sholder. Happy travels Stanley.

We had this strange error the other night, a report with a report prompt placed on a dashboard came back with the logon screen every time the prompt was used more than once.
After some hours of hard core hacking we found that is was caused by a backslash “\” in the dashboard page title “revenue \ week”. Seems that under certain conditions this screws up the internal search for an open db connection. It seems to be occurring only with the combination report prompt and “\” in the dashboard title and certain connection pool configurations.
The solution: don’t use backslashes in your dashboard page title when using a report with a report prompt.
It’s a good thing to implement as standard policy to, the backslash is often an escape character within OBI EE.

Till Next Time

Page 196 of the Presentation Services Administration Guide (the example to change kmsgAuthenticateRemembermyIDandpassword) triggered me to look for the option to switch on this feature:

Of course the answer can be found the manual on page 18….. It is controlled by the value of :”AllowRememberPassword” in the instanceconfig.xml. set it to “true” between the serverinstance tags to switch it on:

Till Next Time