BI & Warehousing

Oracle EPM 11.1.2.1 is Available

Look Smarter Than You Are - Thu, 2011-04-07 15:20
Gentlemen, begin your upgrades.  Oracle EPM 11.1.2.1 has finally shipped.  Those who've been waiting for the migration (and slightly more stable) releases of Essbase, Hyperion Planning, HFM, and the rest for around a year can finally get real lives.


I know: I'm shocked it's out too.  It's not on edelivery.oracle.com yet (at least as of 4:15PM Eastern on April 7, 2011) but it is available for download on download.oracle.com.  I made a bit.ly link to the exact page:
Oracle EPM 11.1.2.1


Tell everyone and impress your friends.


UPDATE: As of April 8, 2011, EPM 11.1.2.1 is now available in nice Media Packs on eDelivery.oracle.com for the following operating systems:

  • HP-UX (Itanium)
  • IBM AIX (64-bit)
  • Linux (32-bit and 64-bit)
  • Solaris (64-bit only)
  • Windows (32-bit and 64-bit)

As mentioned before, the EPM docs (including all the new features and yes, there are some new features) on 11.1.2.1 can also be found here:
http://download.oracle.com/docs/cd/E17236_01/index.htm
Categories: BI & Warehousing

Oracle EPM 11.1.2.1 - Releasing No Sooner Than April 10

Look Smarter Than You Are - Fri, 2011-04-01 11:25
In a posting back in February, I relayed that development was virtually promising that Oracle EPM 11.1.2.1 would be out sometime in March. I then padded that to be sometime by March 31. Here's what I was told from multiple sources (not one of which is Glenn Schwartzberg): apparently, development discovered two bugs during testing just this week. They are being immediately corrected and then the patch should release to manufacturing (meaning it can be bundled up and posted on edelivery.oracle.com).

This jibes with a public announcement that Oracle made during a Customer Advisory Board meeting in Copenhagen this week. A customer that was there told me that Oracle announced they were releasing 11.1.1.2 to edelivery on April 10. This is the first time I've ever heard of an exact date spoken in a semi-public forum.

No I think that the more likely date is April 11 (which is a Monday and as such, a lot more likely than Sunday). That said, the earliest I would expect it is April 11 since, well how do I put it gently, Oracle has been working this for around a year now and has lost pretty much all credibility with estimating patch release dates.

To add to the bad news, it looks like support for other application servers (like WebSphere) will NOT be in this patch. Looks like it will be delayed until to 11.1.2.2 (and who really believes that will really release by year-end?).

Keep watching here and I'll let you know when 11.1.2.1 gets posted for downloading.
Categories: BI & Warehousing

Why we do not use PowerConnect to access PeopleSoft Tree

Dylan Wan - Wed, 2011-03-16 12:51

1. It does not allow you to use parameters to the PeopleSoft connect. It may be changed later. However, it was a big issue when we try to address customer issues.

2. It requires EFFDT as an option.It expect that people change the EFFDT using Mapping Editor. How can a business user does that every month?

3. It asks for a Tree Name. Many PeopleSoft tree structure supports multiple trees. Tree is just a header of the hierarchy. Whenever you add a new Tree, you need to create a new mapping!!

It does not make sense to use PowerConnect due to the customer demands. All requirements are from customers.

We have no choice but stop using it.

Categories: BI & Warehousing

ISA Consulting Bought by E&Y

Look Smarter Than You Are - Wed, 2011-03-16 01:52
And so the consulting company acquisitions continue.  I haven't written about this in over a year mostly because these acquisition entries take so many hours to research (cry me a river, Edward), so let's bury the lead by first covering all the major acquisitions that have occurred since my last entry:


November 24, 2009: PWC acquires Paragon
Those in the Oracle EPM areas in Europe & Asia knew of Paragon.  With close to 100 employees, they were a significant player in the UK, Turkey, and Singapore markets.  It's not known how many of Paragon's employees made the transition to PWC, but press releases seem to reflect around 40.


March 29, 2010: Perficient acquires Kerdock
Kerdock was a major, long-standing Oracle BI/EPM vendor dating back to roughly 2002.  Based out of Houston, they had close to 65 employees at their peak.  When they were bought last year by Perficient (a public-traded company - NASDAQ: PRFT - with about 1,400 employees), they had roughly 45 employees and about $8MM in annual revenue.  They were bought for $6MM (of which $3.4MM was in cash and $2.6MM in PRFT stock).


May 4, 2010: Idhasoft acquires TLC Technologies
TLC is a long-time Oracle EPM partner based out of Pennsylvania.  Though they dated back to the late 90's, they were never that large.  Last year, a controlling interest in TLC was acquired by Idhasoft (through their Prism Informatica subsidiary) for an undisclosed sum.
If you hadn't heard of Meridian when Edgewater acquired them, you weren't alone.  They were only a few years old (and they were pretty small) but they had begun developing a reputation as a Hyperion Strategic Finance implementer that was able to compete with the focused expertise of BlueStone.  We'll never know if they would have fulfilled that promise of HSF experience, though, because they were acquired too early on by Edgewater.  They did have several former Alcar executives (the company that became HSF) on their leadership team (including Alcar's former head of services, Ricardo Rasche), so their acquisition was significant.


August 31, 2010: E&Y acquires Global Analytics
Global Analytics, as you may recall, bought Narratus (the former "Data into Action") a couple of years ago and in 2010, they were gobbled up themselves.  Largely through the strengths of Hyperion installation expert, Bill Beach, Global Analytics had developed a reputation in the Hyperion infrastructure world.  For a time, they were one of only 5 companies (interRel was one of the others) with a significant infrastructure practice around Hyperion which included them subcontracting to other larger global systems integrators.  They had several areas outside of Hyperion, and my guess is that's why  E&Y bought them in 2010.  The small size of their Hyperion practice doesn't seem like it would have warranted E&Y's attention.  Though maybe this should have been a predictor of the acquisition of ISA?


October 21, 2010: IBM acquires Clarity
In my opinion, this was the most significant acquisition in the Oracle EPM, Hyperion, and Essbase world in 2010.  Clarity Systems out of Canada (same place my high school girlfriend lived, by the way) was the first substantial partner to build a pre-packaged budgeting solution on top of Essbase that way pre-dated Hyperion Planning.  Originally a consulting partner at Arbor, Clarity turned their spreadsheet-based front-end to Essbase eventually into a full-featured financial planning, consolidation and reporting product.  What was once a fairly pleasant working relationship got contentious for a number of reasons including alleged licensing violations and what later turned into a compete between Clarity and Hyperion's own Planning and Financial Management products.  As Clarity began to score some competitive wins over Hyperion at companies like Southwest Airlines and Alcon Labs, the relationship took a turn for the downright hostile.


Eventually, Clarity started integrating with non-Hyperion products as they continued their expansion.  Interestingly, when IBM bought them last year, IBM made no secrets about their intentions to kill off most of the Clarity suite (including the planning and financial consolidation functionality).  This actually makes complete sense since they already have the Cognos and TM/1 products doing virtually the same functions.  So why did they acquire them?  Consultant bodies to implement BI/EPM at IBM's consulting clients? Clarity's client list? Just to eliminate a competitor.  None of the above.  Apparently, IBM noticed a weakness in their XBRL reporting and one component of Clarity handled this functionality.  Seems like overkill to me, but then I'm not a company the size of IBM.


Throughout 2010: Palladium founders leave to form other firms
As disastrous as the Hyperion/Arbor merger was back in 1998, there are many who feel that the merger of Balanced Scorecard Collaborative, Painted Word, and ThinkFast into Palladium was even worse.  While I'm not one to judge, it has definitely been true that  Palladium has been bleeding talent (in the Hyperion/EPM world, at least) since their founding.  The last 15 months have been particularly harsh with three major group personnel departures:
  • Painted Word executives including Scot MacGillivray, Jim Leavitt, Chris Boulanger, and Peter Graham all left to found Cervello.  All of these people were founders and/or executives at Painted Word when it became part of Palladium.  They stuck it out for a few years and then left as a group to create Cervello which seems to be doing Oracle BI and EPM consulting.  I can't vouch for that personally, because I haven't run into them at all, but their departure from Palladium was definitely a blow.
  • Tom Phelps left Palladium to start up ClearLine Group.  Tom Phelps was the original founder of the company that later became ThinkFast (one of the three components of Palladium).  Tom and his brother, Marty, founded a company that appears to be doing Oracle EPM consulting (but again, like Cervello, I haven't run into them yet).  With Tom Phelps departing and the Painted Words executives departing, the only founders of the component companies that are still part of Palladium are the Balanced Scorecard guys.
  • Palladium Pace team members including Dean Tarpley, Michael Wright, Carolyn Sieben, and a few others left to join Alvarez and Marsal in August, 2010.  The Pace product hadn't been selling anywhere near what its creators expected and this was the final nail in the coffin of the product.  While Pace is still mentioned on Palladium's website, it doesn't seem that there's anyone left at Palladium still working on the product.  Palladium had been shopping around for a buyer of their Pace business unit for a while, so it's unclear as to if Palladium sold the developers to Alvarez or if they simply were hired en masse.  Since there wasn't any sort of "predatory workpractices" lawsuit, I'm concluding that it was a purchase of the talent and Alvarez didn't want Pace at all.


March 15+ 2011: Ernst & Young acquires ISA
Well, I'd love to point to a press release on this, but there isn't one simply because it's not been announced yet. [Editor's Note: it is now public.  Scroll to the end of the story for more.]  Normally, I wouldn't do a blog entry on this until it was official, but this is the least stealthy acquisition in history.  I have heard about it from no fewer than three sources at three different companies, and since offers have already been extended to the employees that are going to get them at ISA Consulting, the affected people already know.  Keep watching Ernst & Young and ISA's news pages and I'm sure something will be up in the next week or two.


ISA is based out of Pennsylvania and is a very large player in the Oracle BI and EPM space.  Though they do other products, ISA is still considered by many to be a primarily Hyperion partner.  Based on what I've been told, E&Y is acquiring ISA primarily for their consulting expertise.  While they're letting almost all the sales and back office staff go (Mitch Rubin and Cliff Matthews being notable exceptions), most all of the consultants seem to be getting offers to join E&Y.  The partners at ISA do seem to be coming on as either partners or close to it at E&Y.


Even though E&Y is one of the 10 largest privately held companies in the USA, this is a significant acquisition because ISA does appear to have well over 100 people focused around BI, EPM, and data warehousing.  Whether they end up putting ISA in the BI & Data Warehousing group or into financial transformation (or split them between them), this acquisition will significantly increase the number of individuals in those areas. If E&Y does manage to hold on to the talent from ISA, they will now be able to much more directly compete with Deloitte on the BI & EPM front.


I haven't heard terms of the acquisition, but since E&Y doesn't need ISA's client list or sales expertise but rather just wants the consulting bodies, the dollars are presumably based on a multiple of EBITDA. Based on other similar deals in the last year, I expect the multiple is 6.5 times 12-month EBITDA (give or take a factor of 1.5).  If anyone knows any different, by all means, either shoot me an e-mail (I'll keep you anonymous) or post it in the comments to this entry.


Who's Next?
If you go way back to my posting from January 5, 2009, I offered up this list of potential targets for acquisition: 
One could speculate that it might be interRel, PII, Kerdock, Global Analytics, US-Analytics, Analytic Vision, HCG, TopDown, or even the Hyperion arm of Palladium, but it could just as likely be some other tiny Hyperion vendor that's not on anyone's radar screen right now. Heck, it might even expand beyond the consulting world to one of the Hyperion software partners like Applied OLAP or Star Analytics.I then went on to say that interRel could be removed from the list.  Well, I was right on Kerdock, Global Analytics, and the Hyperion arm of Palladium, so that leaves PII, US-Analytics, Analytic Vision, HCG, TopDown, Applied OLAP, and Star Analytics.  I guess I would add MarketSphere to that list too even though they're obviously in areas beyond Oracle EPM.  While many of these companies are too small to attract the attention of Deloitte, IBM, E&Y, and Oracle, don't be shocked if one or more of them is gobbled up in the next year by an off-shore consulting firm looking to fill in the EPM/BI gaps in their offerings.


It's now almost 2AM and I have to present to the HUG group in Minneapolis in a few hours, so I'm going to post and then sleep.  If I've stated anything incorrectly above, feel free to comment and please assume I wasn't trying to be malicious.  It's just been a long day and this entry (essay?) was almost 1,800 words.


UPDATE April 5, 2011: E&Y Officially Buys ISA Consulting
It took a week into April, but E&Y finalized the ISA deal and announced the deal publicly.  The press release states that ISA had 130 employees (I'd speculated 100+) and financial terms were not disclosed.  Read more about it here.
Categories: BI & Warehousing

I'm Totally Lost: Which Conference Do I Go To for Hyperion and Oracle EPM/BI Content?

Look Smarter Than You Are - Mon, 2011-02-28 17:02
The final Hyperion Solutions conference (the great big conference Hyperion used to put on with non-stop Hyperion content and over 4,000 attendees) was in the spring of 2007.  Back then, everyone knew which conference to attend, because there was only one national conference (Solutions) and then a whole lot of regional HUG (Hyperion User Group) meetings.  But then Oracle bought Hyperion and immediately disbanded the conference leaving the user community in disarray.


There are now several options depending on what you're looking for.  While I could attempt to make some sense out of the whole conference jumble in a blog post, I decided it would be better explained in a webcast.  To that end, I'm devoting two webcasts this week to the question “Now that the Hyperion Solutions conference is gone, which conference should I attend?”


I'm going to compare the benefits of the better known 2011 conferences:


I'm in a unique position to do this, because I don't work for Oracle and I have some ties to every one of these events (so you could say that while I'm biased, I realize the value each one can bring to the right audience).  Usually, our webcasts are only open to Oracle customers (not partners) but in this case, I want everyone to know why you'd want to go to each of the conferences so they don't find themselves wasting money at a conference that's totally not right for them.


Click on the links below to sign up for either Tuesday or Wednesday's webcast:



I will spend around 45 minutes covering all the conferences and then take questions from the audience.  Before you sign up for one of the conferences, devote 45 minutes of your life to making sure you won't find yourself trapped in the 7th circle of hell (otherwise known as stuck at a conference you hate).
Categories: BI & Warehousing

Oracle EPM 11.1.2.1 - Looks Like March

Look Smarter Than You Are - Mon, 2011-02-14 00:38
Disclaimer
I don't work for Oracle.  Everything you see below is what I've heard said in public forums followed by some speculation of my own.  Oracle not only does commit to release dates, they don't even commit to specific future functionality.  For that matter, they won't even go on the record that there will actually be any future releases of products.  So take this whole entry for the best guess that it is. 


Why Do I Care About a Little Dot Release?
Based on the disclaimer above, I have been really hesitant to go on the record with any information about Oracle EPM 11.1.2.1 (sometimes called "Talleyrand SP1" or "11.1.2sp1" or "The Patch" or "The Migration Patch" or "The Dot Release" although personally I like calling it "Waiting for EPM Godot").  The initial release of 11.1.2 dates back to April 2010 (yup, we're going on 10 months now) and there's still not an official service pack.  That doesn't mean there haven't been bug fixes.  They just slip them in as minor fix releases (like 11.1.2.0.02 and the rest).  What we're really waiting for are some of the things that should be in 11.1.2.1:

  • Bug Fixes.  Supposedly, all of the big show stopper bugs (like some of the Life Cycle Management issues and the almost complete unusability of the EAS fat client) are fixed in this release.
  • Migration.  11.1.2.0 was released with no migration tools from earlier releases.  It was what was called a "greenfield release."  In other words, you could install it, but you couldn't get there from any earlier version.  Now my company like many others have done several "upgrades" to 11.1.2 of as many parts as we can, but officially, there's no way to migrate to 11.1.2... until 11.1.2.1.  This version should have wizards and conversion utilities to migrate from multiple versions including 9.2, 9.3 and 11.1 (well, specific point releases of those).  In other words, you'll finally be able to do a supported migration and it should be easier than the manual methods we're doing now.
  • Wider Server Support.  11.1.2.0 was released with support only for Oracle WebLogic 10.  Unlike earlier versions, it did not support Apache Tomcat nor IBM Websphere.  Now the good news was that Oracle was kind enough to issue a limited use WebLogic license.  The downside obviously was if you were not already a WebLogic shop.  Taking on a new application server infrastructure isn't easy.  The patch should support Tomcat and Websphere as well as Windows 2008r2 from an OS standpoint.  While it's on the client not server side, also expect to see Office 2010 support in 11.1.2. 
  • Performance Improvements.  With all the new functionality in EPM 11.1.2 (especially in Hyperion Planning), some things just aren't very speedy.  Development has said publicly that they are working on improving the speed of many of these new features including workflow and the new drag-and-drop forms designer.
Any New Functionality?
The four items above are as close to 100% definite as you can get.  They might change the specific versions you're allowed to upgrade from (current rumor is 9.2.1, 9.3.3, 11.1.1.3, and 11.1.2) but there will be lots of bug fixes, a migration path, wider server support, and performance improvements in 11.1.2.1 most assuredly.  Where we get into a grayer area is, like the iPad 2, when we start speculating about any new features of functions in the release.  So here's what may or may not be in there (and don't base any buying decisions off my wild ass speculation):
  • Data Relationship Management.  This is straight from Oracle themselves, so it's very likely to be in there.  From the excellent though sporadically updated "Versioned Hierarchy of Reality" blog (ya, I'm one to talk), 11.1.2.1 should contain:
    • Performance Improvements
    • Upgrade Support
    • Console Improvements
    • Property Namespaces
    • Single Sign On
    • URL-based Navigation
    • Job Status and Cancellation
    • Action Script Options
  • Disclosure Management.  Both Disclosure Management and Financial Close Management saw their initial releases in 2010.  As such, they should both be seeing substantial functionality improvements as Oracle tends to do in dot releases after the initial major release.  For Disclosure Management, the only major improvement I've publicly heard multiple times is for the UK folks: the ability to do XBRL income tax returns for HMRC.
  • Financial Close Management. It looks like the main improvements to HFCM (Hyperion Financial Close Management) have to do with direct integration to PeopleSoft.  Expect to see direct PeopleSoft 9.0 and 9.1 integration with AP, AR, BI, AM, and GL into HFCM.  For details, scroll down to page 17 of a presentation Oracle delivered to the NorCal OAUG. Looks like Oracle eBS integration won't make it into the 11.1.2.1 release of HFCM but should be out by the end of 2011.
  • Smart View.  There will either be a bunch of improvements to Smart View in this release or they'll delay them to 11.1.2.2. A lot of the new Smart View features we've seen demoed in the last year have to do with a new look for Smart View that makes it look a whole lot like Essbase classic (such as the ability to hide the POV). I'm expecting that these User Interface improvements will actually make it into 11.1.2.1, but I wouldn't bet on it.
The ones above are what I would be willing to wager make it into 11.1.2.1.  There will doubtless be some minor improvements that I'll try to detail once the product makes it out the door.  As for some of those other big development things you've been hearing about (like custom dimensionality in HFM, account reconciliations in HFCM, project planning module for Hyperion Planning, micro-costing in HPCM, etc.) is probably going to end up waiting until 11.1.2.2.  The most recent public EPM roadmap presentation from Oracle (scroll down to page 12) seems to reflect that other big items are being held.

So When Does It Come Out?
Talk about burying the lead.  The post is almost over and I'm just now getting to what I mentioned in the subject line.  Every person who's talked about release dates to this point has been wrong.  It went from August to fall to October to 2010 year end to January to "when Dallas freezes over the same week they host the Super Bowl."  Well, we all thought that was never going to happen, but then last week leading into the Super Bowl festivities, Dallas spent 100+ hours below freezing and it snowed several inches too.  So recognizing an apocalyptic sign, I reached out to a very high up source in Oracle EPM development...

... and was told March.  Further, the person  was almost willing to guarantee it if I promised to not mention her/his name.  "March?" I asked incredulously, because I had frankly lost all belief in release dates for 11.1.2.1.  Development then explained to me why 11.1.2.1 has been delayed for so many months.  And here's the thing: it's actually not development's fault nor legal's fault nor sales nor marketing nor any of the other groups that would tend to cause holdups like this.

No, it seems to be because of Fusion.  Oracle EPM is part of the Fusion Middleware team, and it is built on several other Oracle technologies both Fusion and otherwise.  The delay is because one of the key technology "tentpole" components in Oracle EPM coming from the Fusion side keeps changing.  Every time that underlying technology changes how it's implemented, they have to go modify the code for Oracle EPM.  In other words, EPM 11.1.2.1 is just sitting there waiting for the tentpole technology to stop changing.

March, Seriously?
So how did development come up with March?  It seems that there's been a line drawn in the sand and that supporting Fusion tech will be finalized this month (February) which makes sense since it's the last month of Oracle's quarter.  Then it's just a quick change to Oracle EPM, some regression testing, and voila: Oracle EPM 11.1.2.1 will finally be released.  That said, I'd bet on March 31 to be a whole lot more likely than March 1.

I know a lot of you will be waiting until the patch comes out, but please don't blame me if the date slips again (but give me kudos if I turn out to be right).  My infrastructure team at interRel is gearing up for all our clients that have managed to be dissuaded from going to 11.1.2 until the patch comes out, so we're concluding that this delivery date is real.  We're expecting busy times in March and April with all the migrations.  Keep watching here, and I'll let you know when 11.1.2.1 finally shows up on edelivery.oracle.com.
Categories: BI & Warehousing

Another DBMS_Scheduler Chain Rule Issue

David Aldridge - Thu, 2011-01-20 05:23
Following on from a recent adventure with a problem validating DBMS_Scheduler chain rules, I hit another issue today. A rule was defined with a step name that does not exist. This happened because there is an odd limit of chain step name lengths (27 bytes I think), and the name of the step in the […]
Categories: BI & Warehousing

And Another Thing …

David Aldridge - Thu, 2011-01-20 04:02
Following on from my recent complaint about an all-time low on the Oracle Forums, does anyone else get the impression that the work there is not just answering questions, but seems increasingly to be correcting all of the incorrect answers? Obviously I have an example in mind. Or has it always been thus?
Categories: BI & Warehousing

Solving ORA-24172: rule set %s.%s has errors

David Aldridge - Tue, 2011-01-18 09:01
DBMS_Scheduler chains are a joy to use, until they stop being a joy and start being a real pain. I modified the logic of a process, dropping one stored procedure and replacing it with another (instead of writing out a list of files to a control file so that a cron job can scp them […]
Categories: BI & Warehousing

Script for Time Dimension Table

Keith Laker - Mon, 2011-01-17 09:06
Note - This blog post was updated on Nov. 14, 2012 with a new script.  This as been simplified a bit and includes half year.

One of the more common requests I get is a script for creating time dimension tables for Oracle OLAP. The following script will create a time dimension table for a standard calendar. It starts by creating a table with dimension members and labels. The second part of the script fills in end date and time span attributes. The section that creates end date and time span can be easily adapted for completing other calendars (e.g., fiscal) where the members have already been filled in.


--
-- Create time dimension table for a standard calendar year (day, month,
-- quarter, half year and year).
--
-- Drop table.
--
--DROP TABLE time_calendar_dim;
--
-- Create time dimension table for calendar year.
--
-- First day if the next day after TO_DATE('31/12/2010','DD/MM/YYYY').
-- Number of days is set in CONNECT BY level <= 365.
--
-- Values for end date and time span attributes are place holders. They need
-- to be filled in correctly later in this script.
--
CREATE TABLE time_calendar_dim AS
WITH base_calendar AS
  (SELECT CurrDate          AS Day_ID,
    1                       AS Day_Time_Span,
    CurrDate                AS Day_End_Date,
    TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
    TO_CHAR(CurrDate,'DY')  AS Week_Day_Short,
    TO_NUMBER(TRIM(leading '0'
  FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
    TO_NUMBER(TRIM(leading '0'
  FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
    TO_NUMBER(TRIM(leading '0'
  FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
    UPPER(TO_CHAR(CurrDate,'Mon')
    || '-'
    || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
    TO_CHAR(CurrDate,'Mon')
    || ' '
    || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
    RTRIM(TO_CHAR(CurrDate,'Month'))
    || ' '
    || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
    TO_CHAR(CurrDate,'Mon')     AS Month_Short,
    TO_CHAR(CurrDate,'Month')   AS Month_Long,
    TO_NUMBER(TRIM(leading '0'
  FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
    'Q'
    || UPPER(TO_CHAR(CurrDate,'Q')
    || '-'
    || TO_CHAR(CurrDate,'YYYY'))     AS Quarter_ID,
    TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
    CASE
      WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
      THEN 1
      ELSE 2
    END AS half_num_of_year,
    CASE
      WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
      THEN 'H'
        || 1
        || '-'
        || TO_CHAR(CurrDate,'YYYY')
      ELSE 'H'
        || 2
        || '-'
        || TO_CHAR(CurrDate,'YYYY')
    END                      AS half_of_year_id,
    TO_CHAR(CurrDate,'YYYY') AS Year_ID
  FROM
    (SELECT level n,
      -- Calendar starts at the day after this date.
      TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
    FROM dual
      -- Change for the number of days to be added to the table.
      CONNECT BY level <= 365
    )
  )
SELECT day_id,
  day_time_span,
  day_end_date,
  week_day_full,
  week_day_short,
  day_num_of_week,
  day_num_of_month,
  day_num_of_year,
  month_id,
  COUNT(*) OVER (PARTITION BY month_id)    AS Month_Time_Span,
  MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
  month_short_desc,
  month_long_desc,
  month_short,
  month_long,
  month_num_of_year,
  quarter_id,
  COUNT(*) OVER (PARTITION BY quarter_id)    AS Quarter_Time_Span,
  MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
  quarter_num_of_year,
  half_num_of_year,
  half_of_year_id,
  COUNT(*) OVER (PARTITION BY half_of_year_id)    AS Half_Year_Time_Span,
  MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
  year_id,
  COUNT(*) OVER (PARTITION BY year_id)    AS Year_Time_Span,
  MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id;
);
--
COMMIT;
Categories: BI & Warehousing

A New Low

David Aldridge - Thu, 2011-01-13 03:30
A new low on the Oracle Technet forums: a person with DBA access who can’t work out how to increase tablespace size. Hopefully not a production system.  
Categories: BI & Warehousing

Simba previews Cognos8 Analysis Studio accessing Oracle Database OLAP Option cubes

Keith Laker - Thu, 2010-12-02 06:26
Hot on the heels of support for BusinessObjects Voyager, and in addition to the native Excel 2003/2007/2010 pivot table access, Simba are previewing the same connectivity for Cognos8 Analysis Studio - the dimensionally aware UI in the Cognos BI suite.

Together with the unique SQL access to the same multidimensional data & calculations in Oracle Database OLAP cubes (meaning that *any* tool or application capable of connecting to Oracle and issuing simple SQL can leverage the power of Database OLAP - like Oracle Application Express for example), plus the existing support for Oracle's own BI tools including
together with the big functionality and performance improvements in 11g , there is now every reason to move to Oracle Database 11gR2 and to fully exploit the OLAP Option - whatever your choice of front end tool(s).

For Cognos fans: Here is the Video on YouTube:

&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/param&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;param name="allowFullScreen" value="true" frameborder="0"&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;
More information, see the Simba website : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm
Categories: BI & Warehousing

Simba previews Oracle OLAP MDX Provider connectivity to SAP BusinessObjects Voyager

Keith Laker - Tue, 2010-11-16 15:39
Simba technologies have released a short video to preview 'Using MDX Provider for Oracle OLAP to directly connect SAP BusinessObjects Voyager to Oracle Database OLAP Option'

This will be a great capability for users of both Oracle OLAP and BusinessObjects and will futher extend the reach of Oracle database embedded OLAP cubes.





You can get more details on the Simba website
Categories: BI & Warehousing

Microsoft Certifies Simba’s MDX Provider for Oracle OLAP as “Compatible with Windows 7”

Keith Laker - Thu, 2010-11-04 15:30
Simba announced today that Microsoft has certified its MDX Provider for Oracle Database OLAP Option for Windows 7.


This is great news for MS Office connectivity to your Database OLAP cubes. Already supported was Excel 2010 with its cool new BI features, as well as 2007 and 2003 versions. With Windows 7 support too, even the most up-to-date PCs are in good shape to exploit the OLAP Option.

Remember - via the native Excel pivot table connectivity, other features of MS-Office can also leverage the power, performance and calculation functionality of Oracle OLAP. So for example, your Powerpoint presentation to the senior management team, or the one you use when talking to suppliers or key customers can now contain live charts onto the latest information available in the Oracle Database cubes. Instead of spending time cutting and pasting static images into your slide shows each day, week or period-end you can be focusing on the news in the data and doing a better analysis of the results.

This is the perfect complement to your use of Oracle BI Enterprise Edition (10g or 11g) onto the exact same cubes for the broader BI use-case.

In addition to Microsoft Excel, Simba is gradually certifying other MDX front ends for the OLAP Option MDX Provider - with SAP-BusinessObjects Voyager already available and others due soon.

You can learn all about it on the Simba web site : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm

Connectivity into Oracle Database OLAP 11g is market leading: with the same cubes and calculations being accessible via MDX as well as a huge range of SQL based tools and applications, it has never been easier to deploy multidimensional analytics to the masses.

Categories: BI & Warehousing

Cell level write-back via PL/SQL

Keith Laker - Fri, 2010-10-22 10:03
A topic of conversation that regularly comes up when I talk to customers and developers about the OLAP Option is write-back to OLAP cubes. The most frustrating of these conversations usually involves someone saying 'but... the OLAP Option doesn't support write-back'. This is not the case and never has been.

Since the first OLAP Option release in 9i it has always been possible to write-back to cubes via the Java OLAP API and OLAP DML. But in recent releases, a new PL/SQL package based API has been developed. My thanks go to the ever-excellent David Greenfield of the Oracle OLAP product development group for bringing this to my attention.

At the most simple level, it is possible to write to a qualified cell:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61, SOLVE)')

In the example above, a cube solve is executed after the cell write. The objects are referenced by their logical (ie. AWM) names.

This approach is very flexible. For example you can qualify only some dimensions, in this case the assignment is for all products:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24''] = 711.61, SOLVE)')

You can also skip the aggregation:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61)')

or run multiple cell updates in one call:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61,
SET PRICE_COST_CUBE.PRICE["TIME" = ''27'', PRODUCT = ''27''] = 86.82,
SOLVE)');

You can also copy from one measure to another.

dbms_cube.build('UNITS_CUBE USING (SET LOCAL_CUBE.UNITS = UNITS_CUBE.UNITS'));

This will copy everything from the UNITS measure in UNITS_CUBE to the UNITS measure in the LOCAL_CUBE. You can put fairly arbitrary expressions on the right hand side and the code will attempt to loop the appropriate composite. You can also control status.


For more details, take a look at the PL/SQL reference documentation
Categories: BI & Warehousing

Incremental Refresh of Oracle Cubes

Keith Laker - Thu, 2010-10-07 10:11
One of the more common questions I get is about how cubes are processed, in particular how and when Oracle cubes are processed incrementally. Here is a short summary of how the cube refresh process works, a few scenarios and some suggestions on being smart about solutions.

- The cube will load all data from the source table.

- You can limit the data loaded into the cube by a) presenting new/changed data only via a staging table or filtered view or b) making the cube refreshable using the materialized view refresh system and using a materialized view log table.

- The cube will understand if data has been loaded into a partition. If data has not been loaded into a partition, it will not be processed (aggregated).

- If a partition has had data loaded into it, it will processed (aggregated).

- The cube will understand if a loaded value is new, changed or existing and unchanged. Only new or changed values are processed (the cube attempts to aggregate only new and changed cells and their ancestors).

- Changing parentage of a member in a non-partitioned dimension will trigger a full solve of the cube (across all partitions).

- If a member is added to a non-partitioned dimension, the cube will attempt an incremental aggregation of that dimension (that is, the new member and ancestors only).

Here are two scenarios that illustrate how this works.

1) How to trigger a lot of processing in the cube during a refresh:

- Load from the full fact table rather than a staging table, filtered view or MV log table. The full table will be loaded into the cube.

- Load data into many partitions. Each partition will need to be processed. For example, load data for the last 36 months when the cube is partitioned by time.

- Load data into large partitions. For example, partition by year or quarter rather than quarter or month. Smaller partitions will process more quickly.

- Make frequent hierarchy (parentage) changes in dimensions. For example, realign customers with new regions or reorganize the product dimension during the daily cube refresh. This will trigger a full cube refresh.

2) How to efficiently manage a daily cube refresh:

- Load from staging tables, filtered views or MV log tables where the tables/views contain only new or updated fact data. This will reduce the load step of the build. This becomes more important the larger the fact table is.

- Use as fine grained partitioning strategy as possible. This will result in smaller partitions, which process more efficiently (full or incremental refresh) and offer more opportunity for parallel processing. Also, it is likely that fewer partitions will be processed.

There can be a trade off with query performance. Typically, query performance is better when partitions are at a higher level (e.g., quarter rather than week) because there may be fewer partitions to access and less dynamic aggregation might be required. That said, the gain in refresh performance is typically much greater than the loss in query performance. Building a cube twice as fast is often more important than a small slowdown in query performance.

- Only add new data into the partitioned dimension. For example if the cube is partitioned by time, add data only for new time periods. Only the partitions with those time periods will be refreshed.

Clearly, there are many cases where data must be added to the non-partitioned dimensions. For example, new customers might be added daily. This is ok because a) new members are processed incrementally and b) new customers will likely affect only more recent partitions.

Schedule hierarchy realignments (e.g., changing parentage in product, geography and account type dimensions) weekly instead of daily. This will limit the number of times a full refresh is required. It might also allow you to scheduled the full refresh for a time where the availability of system resources is high and/or the query load is low.

The above scenarios help you understand how to most efficiently refresh a single cube. Also consider business requirements and how model the overall solution Two scenarios follow.

1) Data is available at the day, item and store levels in the fact table. The business requirements are such that all data must be available for query, but in practice most queries (e.g., 95% or more) are at the week, item and store levels.

Consider a solution where data is loaded in the cube at the week, item and city levels and more detailed data (day, item and store levels) are made available by drilling through to the table. This is very easy to do in a product such as Oracle Business Intelligence (OBIEE) or any other tool that has federated query capabilities and will be transparent to the business user.

In this case, the cube is simply smaller and will process more quickly. The compromise is that calculations defined in the cube are only available at the week, item and city levels. This is often a reasonable trade off for faster processing (and perhaps more frequent updates).

2) Data is available at the day, item and store levels in the fact table. The business requirements are such that all data must be available for query, but in practice:

- Longer term trending (e.g., year over year) is done at the week level or higher.

- Analysis of daily data (e.g., same day, week or month ago) is only done for the most recent 90 days.

In this scenario, consider a two cube solution:

- Cube A contains data at the week, item and store levels for all history (e.g., the past 36 months). This might be partitioned at the week level and aggregated to the month, quarter and year levels. Depending on reporting requirements, it might only be refreshed at the end of the week when the full week of data is available.

- Cube B contains data only at the day level for the most recent 90 days. It is not aggregated to the week, month, quarter and year levels (aggregates are serviced by Cube A). This cube is used for the daily sales analysis. This might be partitioned at the day level so that a) any full build of the cube can be highly parallelized and b) the daily update processes only a single and relatively small partition.

Using a tool such as Oracle Business Intelligence, which has federated query capabilities, a single business model can be created that accesses data from both cubes and the table transparently to the business user. When ever the user is querying at the week level or above, data OBIEE queries Cube A. If data is queried at the day level within the most recent 90 days, OBIEE queries Cube B. If data at the day level that is older than 90 days is access, OBIEE queries the table. Again, this can all be transparent to the user in a tool such as Oracle Business Intelligence.
Categories: BI & Warehousing

Generating A Gap-free Series Of Numbers — Not Always A Problem

David Aldridge - Fri, 2010-09-24 07:25
Well, it’s happened again. Someone has asked how to implement a requirement to generate a gap-free series of numbers and a swarm of nay-sayers have descended on them to say (and here I paraphrase slightly) that this will kill system performance, that’s it’s rarely a valid requirement, that whoever wrote the requirement is an idiot […]
Categories: BI & Warehousing

Working Around an Internal Error Based on Source Mnemonics

David Aldridge - Thu, 2010-09-23 05:16
My frenzied hacking through the unexplored territory of Expression Filters was derailed (to mix my metaphors) by an internal error when using the Evaluate() function in a rather complex query that uses an inline view, analytic functions etc.. The following was extracted from the alert log: ORA-07445: exception encountered: core dump [kkqsCkLegalEqvExpCB()+199] [SIGSEGV] [Address not […]
Categories: BI & Warehousing

Stupid Yahoo Mail

David Aldridge - Mon, 2010-09-20 07:52
I just browsed my Yahoo spam folder and found an email purporting to be from an ex-work colleague. It was sent to a bunch of other ex-colleagues as well and contained a link to what looked like a site for selling fake ED pills. I replied to the colleague to say, “Hey looks like you’ve […]
Categories: BI & Warehousing

A Cause of ORA-38435 in Evaluate()

David Aldridge - Mon, 2010-09-20 07:40
I’m part way through an implementation of some ELT code that uses expression filters to parse records and assign zero-to-many “inboxes” to each row, and as is often the case with the less well-explored parts of the system it often feels like I’m hacking through the undergrowth in unknown territory. Under such conditions it’s not […]
Categories: BI & Warehousing

Pages

Subscribe to Oracle FAQ aggregator - BI &amp; Warehousing