Rittman Mead Consulting

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

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

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

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

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

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

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

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

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

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

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

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

This should automatically connect us to the Oracle OLAP schemas.

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

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

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

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

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

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

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

SELECT
'OLAPTRAIN' AS CATALOG_NAME,
'SALES_CUBE' AS CUBE_NAME,
members.DEPTH AS LEVEL_NUMBER,
members.HIER_ORDER AS MEMBER_ORDINAL,
members.DIM_KEY AS MEMBER_NAME,
1 AS MEMBER_TYPE,
SHORT_DESCRIPTION AS MEMBER_CAPTION,
1 AS CHILDREN_CARDINALITY,
CASE
WHEN (members.PARENT IS NULL) THEN NULL
ELSE members.DEPTH-1
END AS PARENT_LEVEL,
CASE
WHEN members."COUNTRY" IS NOT NULL AND members.LEVEL_NAME != 'COUNTRY'
THEN '[GEOGRAPHY].[REGIONAL].[COUNTRY].[' || members.PARENT || ']'
WHEN members."REGION" IS NOT NULL AND members.LEVEL_NAME != 'REGION'
THEN '[GEOGRAPHY].[REGIONAL].[REGION].[' || members.PARENT || ']'
WHEN members."ALL_REGIONS" IS NOT NULL AND members.LEVEL_NAME != 'ALL_REGIONS'
THEN '[GEOGRAPHY].[REGIONAL].[ALL_REGIONS].[' || members.PARENT || ']'
ELSE (CAST (NULL AS VARCHAR2(1)))
END AS PARENT_UNIQUE_NAME,
(CAST (NULL AS VARCHAR2(1))) AS DESCRIPTION
, members.ALL_REGIONS_SHORT_DESCRI AS PROPERTY_9
, members.ALL_REGIONS_LONG_DESCRIP AS PROPERTY_10
, members.LONG_DESCRIPTION AS PROPERTY_12
, 'GEOGRAPHY' AS DIMENSION_NAME
, 'REGIONAL' AS HIERARCHY_NAME
, members.LEVEL_NAME AS LEVEL_NAME
FROM
"OLAPTRAIN".GEOGRAPHY_REGIONAL_VIEW members
WHERE
members.LEVEL_NAME = 'ALL_REGIONS'
ORDER BY MEMBER_ORDINAL, PARENT_UNIQUE_NAME, MEMBER_NAME

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

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

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

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

As I mentioned in the past, I am a great fan of Oracle bitmap indexes; they allow the database to do some really good optimizations and reduce the impact of typical data warehouse queries that need to filter and fetch large chunks of fact data. The flip side to their success in queries is that they can slow the ETL data load process as we update or create individual index entries that refer to many rows of data. The traditional way to deal with this is set the index to be unusable before loading and rebuild it after the ETL completes; for partitioned indexes (and if the indexed table is partitioned then any bitmap indexes must be locally partitioned) you can do this a partition level and just rebuild the unusable partitions.

Typically, I set indexes to be unusable by issuing an ALTER INDEX my_bitmap_index UNUSABLE and for partitioned tables ALTER INDEX my_bitmap_index PARTITION aug_10_part UNUSABLE, here I use a “smart partition name” that allows me to programatically determine the correct index partitions to manipulate – using the HIGH_VALUE of the partition from DBA_IND_PARTITIONS is problematic as it is a LONG data type and thus a tad convoluted to query (see Adrian Billington’s note on working with LONG columns).

However, when we use Oracle 11g interval partitioning the partition names are system generated; so how do we find the index’s partitions that need to be set unusable? There is no “PARTITION FOR” construct to alter an index’s partitions to be unusable, and the name of partition is not inherited from the table -it gets its own system generated name. There is still a way to do this and assuming that my_table is range partitioned by DATE interval; ALTER TABLE my_table MODIFY PARTITION FOR (TO_DATE(’1-Aug-2010′,’dd-mon-yyyy’)) UNUSABLE LOCAL INDEXES
Before interval partitions I have rebuilt unusable index partitions by writing a procedure to loop through all of the index partitions marked as unusable in the data dictionary and issue an ALTER INDEX …. REBUILD PARTITION command – here I can easily find the partition name to rebuild as the unusable marker is not held in a LONG, there is also an ORACLE package (DBMS_PCLXUTIL) that can do this; now, with Oracle 11g maybe the simplest thing though is to use ALTER TABLE my_table MODIFY PARTITION FOR (TO_DATE(’1-Aug-2010′,’dd-mon-yyyy’)) REBUILD UNUSABLE LOCAL INDEXES.

Rittman Mead is delighted to have been shortlisted for the UKOUG BI Partner of the Year 2010/11, to be awarded on the 7th October at a ceremony in London.

These awards are designed to provide recognition to Oracle Partners for the contribution that they make by offering their services to the Oracle user community within their particular specialist areas.

The winner will be decided by an online ballot voted for by members of the Oracle user community.  If you would like to register a vote for Rittman Mead, please do so at http://www.registrationline.org.uk/pya/voter.asp

Rittman Mead was honoured to be voted BI partner of the year at the inaugural awards in 2008/9 by our customers and community, and we are delighted at having been shortlisted for this years awards.

For more information on some of the excellent user community activities and events provided by the UKOUG, please visit www.ukoug.org

Here’s a quick one before the weekend comes along: one of the limitations in OBIEE 10g around software configuration management was that you couldn’t apply incremental patches to an RPD, applying for example a set of changes made in development to the production repository. The only thing you could do was take the whole development repository, test it and then copy it in it’s entirety into production, which wasn’t ideal.

In OBIEE 11gR1, you can create XML patch files based on the differences between two repositories, and then apply this to a third to incrementally update it (using the XUDML feature first introduced in 10g with the Content Accelerator Framework) To take an example, I have a development repository that contains a subject are with two tables:

I copy this RPD into production and make it my production version. Then, I carry on development and add a new logical table, and presentation table, to the development RPD.

What I’d like to do now, is generate a patch file that I can then apply to my production RPD, so that just these extra tables are added to it. I do this by selecting File > Compare from the BI Administration menu, and then I select the original RPD that I copied into production as the comparison RPD. The Compare Repository dialog tells me that I’ve got a bunch of new tables and columns in the modified RPD.

I then press the Create Patch… button to create the patch file. This creates an XML file that describes the new RPD objects that the patch file will create when applied.

To apply the patch, I open up the production RPD using the BI Administration tool, select File > Merge, and then select the original RPD and the patch file I want to apply. There’s also a command-line version of this utility, patchrpd, that you can use in a scripting environment to automate the process.

Once the patch merge completes, you’re left with a new copy of the production RPD, with the new columns and tables applied. Not bad.

The rest of the SCM process looks much the same in 11g as it was in 10g, though if you’re interested in scripted patching of the web catalog, Venkat’s posting the other day on the command-line interface to the 10g Catalog Manager looks an interesting option.

I’m very pleased to announce our third annual Rittman Mead Training Days event, this time focusing on OBIEE 11g and running in London, Atlanta and Bangalore in October and November 2010.

Our previous two Training Days events covered a range of Oracle BI topics, but with the launch of OBIEE 11g we want to focus just on OBIEE. We’ll be covering everything from installation and configuration through to report writing, creating KPIs and scorecards, right through to BI Publisher 11g and the Action Framework, and the event is aimed at developers who have a basic understanding of BI and OBIEE 10g.

Here’s the agenda for the three days:

Day 1

1. OBIEE 11g Installation, Configuration, Architecture and FMW11g Integration
2. Best Practices for OBIEE 11g RPD Design and Metadata Modeling
3. Analysis, Visualizations and Dashboards using OBIEE 11g

Day 2

4. Actionable Intelligence using OBIEE 11g
5. Essbase Integration with OBIEE 11g
6. Creating Scorecards and KPIs using OBIEE 11g

Day 3

7. Scaleout, Clustering and High Availability using OBIEE 11g
8. Upgrading OBIEE 10g Installations to OBIEE 11g
9. Diagnostics, Troubleshooting and Optimization of OBIEE 11g

For the events in London and Atlanta, we are putting together some hands-on labs that we’ll be using during the sessions. Delegates can bring along their own laptops and software and work with us to build a complete example system, which they’ll then be able to take back to their offices to carry on using. For the Bangalore event, we’re running this more as a seminar, but again we’ll give you the labs and data and you’ll be able to try them out back in the office.

This is a fantastic opportunity to learn OBIEE 11g from some of the longest-running beta testers of the product, giving you an opportunity to learn the new release quickly and gain lots of insights into its inner workings. Details on the dates, locations and how to book are on the event website, but hurry previous events of this type sold out in just a few weeks.

Due to rapid growth in secured and future projects across the US, Rittman Mead America is recruiting for experienced Oracle BI professionals with skills in Oracle BI, DW and EPM technologies.

What we’re ideally looking for is experienced, articulate developers and consultants with a passion for BI, data warehousing and performance management; a deep understanding of the Oracle BI & DW, or EPM technology stacks; excellent communication skills and the ability to both lead and work as part of a team on projects across the US and potentially globally.

We are primarily seeking to build our permanent consulting team, however we do have a wide associate network of trusted experts globally and we are always interested in extending this with high-quality consultants.

Consultants would be required to work in a variety of roles, from leading Oracle BI solutions design and development through to training and support at locations across the US and potentially beyond.

Current technologies we are using to provide BI solutions include:

• Oracle Business Intelligence Enterprise Edition
• Oracle BI Applications
• Oracle Essbase
• Oracle Data Integrator
• Oracle Warehouse Builder
• Oracle GoldenGate
• Oracle Real Time Decisions
• Oracle Exadata

In addition to a competitive package and a flexible and mature working environment, working with Rittman Mead offers consultants the opportunity to be a key member of a dynamic and progressive organization, working alongside some of the foremost Oracle BI experts in the world to deliver innovative solutions at leading global corporations.

Other benefits include training in the latest technologies (OBIEE 11g, ODIEE 11g, Oracle GoldenGate, Exadata); the opportunity to present at major global events such as OpenWorld and the platform to develop as a genuine leader in the field of Oracle Business Intelligence.

If you would be interested to have an initial conversation about how we could work together, please contact us on +1 888 631 1410 ext. 105, or morgan.mccarthy@rittmanmead.com

It’s the US launch of OBIEE 11g in New York today, and if you’re interested in the infrastructure behind OBIEE one of the highlights of the product demonstration that took place at the London launch was around scaleout and clustering. If you’re worked with the 10g release of OBIEE and tried to set up a cluster, you’ll know it’s a fairly involved task and one that requires a lot of manual setup (Borkur’s postings on 10g BI Server clustering, and 10g Presentation Server clustering, give a good overview of how this worked). Paul Rodwick’s presentation showed that in 11g, setting up OBIEE for high availability and clustering is a much more straightforward process, partly through improvements in manageability and partly through the move to WebLogic Server.

If you saw our previous posting on OBIEE 11g architecture, you’ll have seen how this initial release of OBIEE 11g is centred on WebLogic Server as the underlying application server architecture. Oracle Enterprise Manager is used to manage the OBIEE infrastructure, with a basic installation of OBIEE 11g consisting of a WebLogic domain made up of an admin server plus a managed server containing the OBIEE components. The OBIEE components consist of Java Components (the J2EE elements of OBIEE, such as BI Publisher, BI Office and so on) plus the System Components (BI Server, BI Presentation Server etc) which are managed by Enterprise Manager and brought up and down using OPMN. If you looked at the BI domain in WebLogic Server admin console, you’d see the admin server accompanied by the managed server, looking like this:

If you want to scale out this infrastructure, you would typically run the OBIEE 11g installer on a second server, and instead of choosing to do a new install, you’d choose to instead scale out the existing BI domain.

The installer will then create a new managed server on this server, and connect it to the existing domain on your original server. Then, when you start up the domain and access the WebLogic Server admin console, you can start up both the original managed server, and this one remotely, to give you two managed servers within the domain.

At this point, you’ve got an additional managed server, but you’ve not yet brought any additional system components into the cluster. You do this from Enterprise Manager, where you can now add additional BI Servers, Presentation Servers, Java Hosts, Cluster Controllers or Schedulers to the cluster, by selecting them from the Capacity Management > Scalability screen, where you’ll now see a new entry for your managed server.

As per previous releases, you’ll also need to specify a shared location for your RPD, and for the web catalog. You do this again from Enterprise Manager, this time selecting the Deployment > Repository screen and typing in the details of the shared locations.

All of this is made a lot easier as clustering is enabled by default with OBIEE 11g, which means that it’s relatively simple to add a new node to it. Under the covers, clustering is actually more or less the same in 11g as in 10g with the same active-active and active-passive arrangements, it’s just a lot simpler to set up and manage, something that’s also the case for thing such as SSO and SSL.

If you were at the launch of OBIEE 11g in London earlier in July, you may have heard Thomas Kurian refer to 11g as being the world’s first “closed loop” business intelligence solution. The idea behind this is to create links between the BI platform and the outside world, so that OBIEE can, for example, respond to business events, and users can trigger business processes based on information they see in a dashboard. This ability was present in the 10g version of OBIEE in a limited form, through iBots, request navigation and guided analytics, but in 11g this has been built-out into a feature in its own right, known as the “Action Framework”.

Using the OBIEE 11g Action Framework, I can define actions in the web catalog that can launch additional reports, display web pages or guide the user down a particular analysis path. What’s new in this release is that I can also link out to business applications, triggering workflow, invoking business processes and generally giving the user the ability to take actions within the BI tool based on what they are seeing. This interaction with business processes is something I looked at a couple of years ago in an article on OTN called “Integrating Oracle Business Intelligence Enterprise Edition with SOA”, but that was all hand-coded and hacked together whilst the Action Framework is a fully productized, supported feature of the 11g release. So how does this new feature work?

Let’s start with a simple analysis on regional performance, showing a list of regions and performance to target:

This analysis will be provided to managers through a dashboard, and I’d like them to be able to take some action based on its contents. Specifically:

• I’d like them to be able to drill to a detail-level report on a particular region, showing sales broken down by stores (and if appropriate, concessions), and by the staff working in them
• For those stores under-performing (defined as performance to target less than 100%), I’d like to provide a link to the store scorecard
• I’d also like to provide a link to the company website, passing across the region name as a parameter, to show more details on the region
• I’d also like to be able to trigger a workflow process to arrange a performance review, for those regional managers whose regions are underperforming
• Finally, I’d like to put a link under the report to point to the store scorecard if any regions are underperforming

(not much then…)

I start by creating the detail-level analysis that I want this initial analysis to link to. I include two hierarchical columns, one for the Stores dimension and one for the Staff dimension, and also put an Is Prompted filter on the Region Name column, so that it can be filtered on the Region Name that the user clicks on in the original analysis (as you would do when setting up request navigation in 10g).

Next is where the “action” comes in. Using the Home Page in the OBIEE 11g Unified Framework, I navigate to the Create … area, find the Actionable Intelligence section and then click on Action, and then select Navigate to BI Content as the action type.

I then use the Browse dialog that then comes up to navigate to the analysis I want to link to, and then save the action to the web catalog for use later on.

I then repeat this for the action that will navigate to the store performance scorecard, again selecting it from the web catalog. Once this is done, it’s time to create another action, this time to link to the company website.

I do this by creating another action, but this time selecting Navigate to a Web Page as the action type.

When creating this type of action, I can specify the parameters that the website expects, which will be mapped to columns in the analysis criteria when I add the action to it.

It’s similar process when I create the action that links to the application. In this case, I’m going to invoke a web service method that will trigger the human workflow (specifically, requesting that a regional manager goes into HQ for an interview.). I do this by in this case, selecting the Invoke a Web Service option from the Invoke menu in the new Action dialog, which lets me select between web services that have been registered by the OBIEE admin in one of the configuration files.

Finally, I want to set up an action that will sit under this analysis on the dashboard, and also link through to the scorecard if any regions are performing below target. I already have the action (I can reuse the one from before), but I need to define a condition that will determine whether it’s displayed. To do this, I select New > Condition from the common Header area of the Unified Framework, like this:

I then select the request that will test for this condition – basically, the original request with a filter on it to only return rows where performance to target is under 100% – and decide how many rows need to be returned for the condition to be met.

Now I’m good to go. Taking a look at the Catalog view in the Unified Framework, I can see a list of all the actions, and the condition, that I’ve created in this session.

Now I can go back to the original analysis, and start adding the actions. I do this by initially selecting the Criteria tab in Answers, and then select Column Properties > Interaction for the Region Name column. Then, I add the action to navigate to the scorecard, but set it up so that it only displays if that row’s performance to target measure is below 100%.

After this, I add the action that links out to the more detailed analysis, and Answers will take care of passing the Region Name parameter to the second report, as it did with request navigation in 10g. Once this is done, I add the action to bring up the company web page, and map the Region Name column in the analysis to the parameter in the action.

Then, I add the action that invokes the web service and again, make this conditional on performance to target being less than 100%. I’m now at the point where all of the actions are defined for the analysis, and I can view the list of them before saving the changes.

I can now test out the actions. I display a preview of the analysis and click on the first of the regions, one that is performing above target. I just get presented with two of the actions, as the other two only kick-in when performance is below target.

When I click on the second one though, the other two actions are displayed, as the condition for the action has been met.

Finally, I want to publish this analysis to my dashboard, and then use the condition I defined earlier along with the Navigate to Scorecard action to add a conditional message under it. Once I’ve added the analysis to the dashboard, see how on the left-hand side there are new dashboard elements for Action Link and Action Link Menu? These replace the Guided Analytics Link item that you used to get with 10g, and I drag the Action Link item onto the dashboard, into the same section as my analysis.

Then, when I display the analysis in the dashboard, I’ve got an action link (aka guided analytics link) under it, conditionally displayed if one or more regions are under-performing against target.

So there you have it. Tomorrow we’ll be taking a break from OBIEE 11g to look at the new 11g release of ODI, before we finish up our coverage of the OBIEE 11g launch announcements with looks at scaleout and HA, and the new release of BI Publisher.

Another new part of OBIEE 11gR1 that you may have seen at the launch in London, is a new product called Oracle Scorecard and Strategy Management. It’s delivered as part of the overall OBIEE 11g technology stack, but like Siebel Marketing before it it’s likely to be separately licensed and not an automatic part of an OBIEE 10g upgrade. Oracle Scorecard and Strategy Management had its own breakout sessions at the product launch, but for those of you who couldn’t attend, how does it work?

Taking a step back for a moment, another key new feature in OBIEE 11gR1 is support for key performance indicators (KPIs). KPIs are defined within the web catalog and are defined by taking a measure, a target measure and a set of thresholds, which can then be used either directly in dashboards (by use of another new feature called KPI Watchlists) or within one of the new scorecards. To take an example, say I have a measure for Store Sales, which I want to use along with Store Sales Target to define a KPI. I do this by first selecting these two measures from the BI Server semantic model:

I then define the dimensionality of the KPI, in this instance making it analyzable by two levels in the store hierarchy, and two in the time hierarchy.

The values used for these dimensions can either be “pinned”, which means that they stay static for this KPI, or they can be dynamic, which allows the user to change them to vary their point of view. In the example above, I’m using regular “attribute” columns from my semantic model, but I can also use hierarchical columns as well.

Once the dimensionality is defined, you then set the thresholds. In this example, I’m setting good performance as being within 90% of my threshold, acceptable as being 70% and below that, performance is unacceptable.

Once you’ve set this and a few other options, you then save the KPI to the web catalog. Then, you can either display the results of the KPI in a simple table, or you can add them to a KPI Watchlist, which can be added like any other object to a user’s dashboard. This KPI Watchlist can then be sliced and diced, using the dimension controls at the top of the watchlist, to allow the user to drill into whatever level of data they are authorized to view.

The Scorecard part of OBIEE 11g builds on this foundation of KPIs to allow you to define complex, multi-part strategies using the metrics in your semantic layer. In the scorecard below, I’ve taken the four KPIs in the KPI Watchlist above and organized them into a set of objectives; one which is financially-focused and aims to improve store monetary performance, and another which is more stakeholder-focused and uses the results of satisfaction surveys.

Using these objectives which are organized into a hierarchy of their own, I can create, for example, a strategy tree diagram that shows me how each objective feeds into the other. In this case, my overall objective of improving store performance is made up of my financial, and non-financial (stakeholder) objectives, each with their own set of KPIs.

You can set how much influence each objective has on its parent objective, so that, for example, the non-financial KPIs carry less weight than the financial ones. In the example above, while store sales and store margin are good and acceptable respectively, this is outweighed by the very poor staff satisfaction scores, which overall contribute to a poor overall store performance score. This is typical of balanced scorecards, where both financial and non-financial KPIs contribute to the overall, balanced score for the organization.

You can also define cause and effect maps, showing the relationship between KPIs in a “fishbone”-type of diagram.

If you’re analyzing the scorecard and want to add commentary, there’s an ability to add annotations to KPIs, and you can also drill-out from the scorecard into Answers to look at the data in more detail – this is actually quite a key feature, as it brings together the executive-style overview of the business with the ability to do more direct access, and if you combine it with the Action Framework as well (which we’ll cover in the last posting in this series), you’re getting what Oracle referred to in the launch as a “closed-loop BI system”.

There’s a lot more to Oracle Strategy and Scorecard, and we’ll have to wait until the GA release to go through the full details. But it’s certainly an interesting addition to the Oracle BI product portfolio, and the introduction of KPIs and other higher-level business metadata into the OBIEE repository is a welcome move.

As well as giving us hierarchical columns and a revamped user interface, OBIEE 11g comes with a number of improvements and enhancements to graphs and dashboards. In this posting, I’ll be taking a look at some of these new features, including:

• An updated graphing engine, new graph formatting tools and the introduction of the slider control
• Master-detail linking of visualizations
• New dashboard prompts

There’s also a new, built-in mapping capability that I’ll cover in a later posting, together with a full scorecarding application that I will be looking at tomorrow. For now though, let’s create a new request and add a simple bar chart view to it.

Notice a few changes in the UI. There’s still the same set of graphs (so new graphs types, at least that I can see), but we’ve now got Map on the list, and all the graphs have little icons to show you how they will look. Views, a feature of Answers that a lot of first-timers find confusing, now have their own panel with clear buttons for creating, editing and deleting them. There’s alos a consistent set of buttons at the top of the screen for exporting to Excel, searching, creating groups, previewing in a dashboard and so on.

I create my bar chart and take a look at the controls for fine-tuning the view. Looking at the graph itself first of all, there’s a new (SVG?) rendering engine replacing the old Flash-based engine used in 10g, which means that in theory it should work on iPads and iPhones as well. Best of all, the same graphing engine is shared with Oracle Fusion Middleware 11g (so you can create your own apps with the same look and feel as OBIEE 11g), and with BI Publisher 11g, so there’s consistency between reports created with these two applications.

Under the graph is a collapsable area for setting the graph layout. Something I was impressed with is the consistency in this layout editor between all the various views, so that you can for, example, exclude columns from the view, or add sections or pages, across all views including simple tabular ones.

Notice the Display as Slider? checkbox in the Sections area? This is common across most of the view types and allows you to replace section breaks with a slider, which you can move manually or play, like a video control.

I’m sure Stephen Few will be metaphorically turning in his grave, but seeing as most OBIEE projects I’ve worked on seem to get judged on the amount of eye-candy on the screen, this’ll be an interesting novelty to use for a while, and it could be useful for time-series reports.

Another new feature is Master-Detail linking. This allows you to set up views within an analysis that respond to values being clicked on in other views. To take an example, I want to create an analysis that lists out the product categories that we sell, and when you click on a category it shows a graph that displays profit margin for each product within the category.

I start by creating a new analysis, and select the Product Category and Product Name attribute columns, along with the Profit Margin measure. Then, in order to turn on master-detail linking, I select Column Properties for the Product Category column from the criteria view.

Then, using the Interactions tab in the Column Properties dialog, I set the Primary Interaction for this column to Send Master-Detail Events, and type in an arbitrary value for the Specify Channel setting. This is so that individual views can choose which master-detail events to respond to.

I then save this view, and create the bar chart graph. Then, I edit the properties for the graph and tell it to respond to these events.

Now, when I show both views in the compound layout, clicking on a product category filters the graph by this value.

Now there’s a lot of new features around visualization in this release that I’ll have to wait until GA to go into in more detail, but one other nice addition is an enhanced set of dashboard prompt controls. In the 10g release we could use drop-down menus, text boxes, date pickers and multi-select controls to pass parameters to dashboard requests, but in 11g this has been expanded to include radio buttons and list boxes (reminding me of QlikView):

together with drop-downs with checkboxes, and checkboxes on their own.

So there you have it. Tomorrow, to round the week off, we’ll finish by taking a look at KPIs and the new Oracle Scorecard and Strategy Management application.

At Rittman Mead, one of the major areas that we’ve been asked to help customers in the past is in the integration between OBIEE 10g and Essbase. OBIEE supports Essbase as a data source, but it’s had a few well-known limitations in the 10g release of OBIEE that have made it hard for certain types of Essbase customer to migrate to this new platform. In particular:

• Lack of proper support for ragged, unbalanced and value-based hierarchies in Essbase outlines
• Inflexibility around changes in the Essbase outline
• Very few OBIEE SQL functions were function-shipped to the equivalent Essbase MDX functions
• No out-of-the-box support for UDAs
• No way to access non-default member aliases
• The accounts hierarchy was flattened during import, losing the hierarchy of accounts (particularly important for financial applications)
• Limitations on how hierarchies could be drilled into, using Answers, and
• Limitations around how members could be browsed and selected

So it was not surprising then that most Essbase customers are looking forward to the 11gR1 release of OBIEE to address some of these shortcomings. So how does this new release stack up, does it address all of these issues, and does it go beyond traditional Essbase front-end tools such as Web Analysis and Interactive Reporting?

To test this out, I’m taking the Demo.Basic Essbase BSO database that comes with the 11.1.2 release of Essbase Server, to which I’ve added an additional alias table that describes members in the Products dimension in German, as well as the default English. Here’s how the outline looks, and note the ragged hierarchy in the Accounts dimension.

Note also the UDAs I’ve added to the Product dimension.

I now turn to the OBIEE 11g BI Administration tool, and select File > Import Metadata. I choose Local Machine from the Import Type drop-down, select Essbase as the Connection Type, and enter the connection details and login for the Essbase server.

The next page of the Import Metadata wizard lets me select the Essbase database outline to import.

Notice the option at the bottom to import UDAs? I then press Finish to complete the import.

Looking inside the physical layer of my semantic model, I can see the new Essbase database that I’ve imported. The most obvious change is that the Accounts dimension has now been brought in properly, and I now have a single measure in my database, that will be dimensioned by this accounts dimension. In the screenshot below, you can see the Product dimension hierarchy expanded, and there are a number of “columns” imported that are new to 11g.

As with OBIEE 10g, the generations in the Essbase outline are converted to columns in the physical model. A new feature however in the 11g version of OBIEE is the ability to create additional columns for any other alias tables that might be used in your outline. I right-click on the Product hierarchy, select Create Columns for Alias Table…

This brings up a dialog that lets me select the alias table to use. I select the German alias and press the Create button.

If I take a look at the Product hierarchy now, I can see the extra column that will expose the German aliases for these member names.

At this stage, if I take a look at the Accounts dimension, it has this same set of levels based on the generation names in my Essbase outline. But this isn’t what I really want, as the Accounts dimension is ragged and would really suit a parent-child, or value-based hierarchy instead. So, using a new feature in the 11g release, I right-click on the Accounts hierarchy and select Properties. Then, I change the Hierarchy Type from Unbalanced to Value, a new option with 11g.

If I then take a look at the hierarchy under my Accounts dimension, it’s now got just a single level, the Member Key column is still there but now there’s a Parent Key column as well.

So now the physical model is prepared, as with the previous 10g release of OBIEE I just need to drag and drop it into the business model and mapping layer, and then drag the business model into the presentation layer, to make it ready for analysis. Taking a look at the business model, I can see the parent-child hierarchy set up for the Accounts dimension, and the single measure under the fact table. To display particular accounts (equivalent to measures in regular relational OBIEE) I need to make sure I select an account name from the accounts dimension.

So, switching now to Oracle BI Answers 11g, I create a simple request showing the balances under each account.Notice the value-based ragged hierarchy used by the Accounts dimension.

And, as we’re working with a hierarchical column, we can use the Selection Steps feature to filter down the list of accounts used (regardless of their generation). I start by adding those accounts that are the children of the Total Expenses account, a feature that Essbase users are used to using in tools such as Hyperion WebAnalysis.

I then add another selection step, this time only keeping those accounts whose balance is over 100,000.

This leaves me with just the following selection, and the list of accounts shown in the request.

I can also bring in other dimensions and hierarchies, creating a pivot table that looks like this.

The aliases that I brought in earlier can be accessed by their own columns. In the example below, I’ve brought the German aliases in as an attribute column in the analysis (apologies Christian and @lex for the cod-German translations…).

Remember the UDA that I brought in earlier on? If I want to filter so that I only include those products with a UDA of Digital, I find the attribute column that references this particular UDA value and set the filter to ‘1′.

Now I could actually have set up the accounts hierarchy as a level-based, unbalanced hierarchy (which is the default when it comes in from the Essbase outline), but changing it to Value-based has a significant advantage if your outline is likely to change in the future. The 10g release of OBIEE always brought Essbase hierarchies in as level-based ones, creating columns for each of the generations in the hierarchy, which translated into columns in the OBIEE logical model. Apart from making it tricky to filter against hierarchies where members were held at arbitrary generations, the other problem this caused was when additional members were added to the outline that caused additional generation to be created.

As there is no “refresh” feature when importing outlines, the only way to add these new members was to try and create new columns in the physical model to represent the new outline generation, and somehow get these into the business model afterwards. In practical terms this was more or less impossible, meaning that any change in the outline of this nature meant you had to completely re-import the Essbase outline into the RPD, and rebuild the business model and presentation models afterwards, something that wasn’t really practical when the outline changed frequently.

OBIEE 11g changes this though by allowing you to define an Essbase outline hierarchy as value-based, something we did earlier on with the Accounts dimension. When you do this, there is only one “generation “in the physical layer and the hierarchy is instead defined by the Member key within it, and the Parent key that creates the link with the dimension member’s parent. By doing this, I can then go back into Essbase Administration Services and add two new members under an existing leaf level, effectively creating an additional generation in the outline. In this case, I’ve added Direct Marketing and Advertising members under the Marketing member, as shown below.

In OBIEE 10g, the only way to bring these new members into the repository (or more precisely, the generation that contained them) would be to re-import the outline into the RPD. Now though, these new members come through automatically with no need to recreate the RPD entries, as they are just part of this value-based hierarchy.

So there’s a brief intro into the new Essbase capabilities in OBIEE 11g. Keep an eye on this blog for more postings on this subject by Venkat nearer to the GA date, and also keep an eye on Metalink for details of compatibility between OBIEE 11g and the various Essbase releases. In the meantime though, we’ll move on from looking at RPD enhancements as part of this series, and tomorrow take a look at some of the new visualizations and controls that are available in the OBIEE 11g dashboard.

In our previous postings, we looked at the new user interface for OBIEE 11g, and how the metadata layer for the BI Server has been enhanced to better handle OLAP-style data. In this posting, we’ll move to the front-end and see how dimensional support has been enhanced in Oracle Answers 11g, in particular through a new feature called Hierarchical Columns.

If you followed the development process for OBIEE 11gR1 and in particular, the new features planned for Oracle Answers, you’ll probably be aware of enhancements to how Answers handles OLAP-style hierarchies, and how pivot table views in Answers have been updated to more suit the type of analysis performed by Essbase users. In a previous posting in this series I looked at how the underlying metadata in OBIEE 11gR1 has been extended to now encompass ragged, skip-level and parent-child hierarchies, and in this posting I’ll look at how the 11g release of Answers (now part of the 11g Unified Framework) can make use of these through hierarchical columns and general enhancements to pivot table views.

In this example, I have a logical model called Store Sales – Sales that has four logical dimensions. This model has then been used to create a corresponding presentation model, that exposes these dimensions as hierarchical columns.

The details of these dimensions, and hierarchical columns, are as follows:

• Products (Level-Based) dimension has a single regular, balanced, level-based hierarchy (as per the 10g release)
• Staff (Parent-Child) has a parent-child hierarchy, with an automatically generated closure table behind it
• Stores (Ragged & Skip-Level) has a single skip-level and ragged level-based hierarchy, and
• Times (Time Dimension) has two balanced, level-based time hierarchies (one for calendar, one for fiscal)

The data source behind this model is relational (Oracle 11gR2), and now I want to start reporting on this using the new hierarchical columns feature in OBIEE 11gR1.

I start by logging in to the OBIEE 11gR1 unified framework and create a new analysis. I select this presentation model, and then take a look at what’s available to me in the Criteria panel.

Starting with the Sales logical fact table and the Stores logical dimension table, I can see the one measure (Amount), the columns in the Stores table (now called Attribute Columns in 11gR1), and the dimension plus its levels just below the attribute columns. This dimension is referred to in 11gR1 as a Hierarchical Column, which gives us three types of column in this release (measure columns, attribute columns and hierarchical columns).

I start off by adding the Region Name attribute column to my criteria, along with the measure. I switch to the Results panel and see that the output looks very similar to what I’d have seen in OBIEE 10g. This is to preserve backwards compatibility for products such as the BI Applications, which will still be able to show reports and dashboards in the same way as if you’d been using OBIEE 10g.

When I select an attribute column in my criteria and then switch to the Results panel, the data is automatically shown in a table view, as it would have been in OBIEE 10g.

I now create a new analysis, but this time select the Stores (Ragged & Skip-Level) hierarchical column, along with the measure. Starting with the All Stores grand total level, I click on the + signs next to each member, and start drilling into the hierarchy. Notice how the SFO concession doesn’t have a store as a parent, and instead rolls up into the North CA region (this is the skip-level). Notice also how the Fisherman’s Wharf store has two concessions under it, whereas the other stores don’t (this is the ragged element of the hierarchy, where leaf members aren’t necessarily at the same hierarchy level). Finally, note how the All Stores grand total level is calculated based on the dimension members that are its descendants.

I can do a similar thing with the Staff (Parent-Child) hierarchy. As the name suggests, this is a parent-child, or value-based, hierarchy, and again I can display it in the Results panel and look at the numbers. Note however that with parent-child hierarchies, the values for each member aren’t calculated based on its descendants, and instead they just display the total associated with that member (i.e. there is no automatic roll-up of numbers).

Now this difference in behaviour between parent-child backed hierarchical columns, and level-based backed hierarchical columns, is interesting as there is no way to tell from the Answers side what type of hierarchy you are working with. I wonder if this will change in future releases?

You can include more than one hierarchical column in a pivot table, and indeed you can mix and match attribute columns and hierarchical columns in the same view. In the example below, I have the Staff (Parent-Child) hierarchical column down the left-hand side, which is then broken down by the Quarter attribute column from the Times (Time Dimension) logical table, with the Products (Level-Based) hierarchical column along the top.

You can also nest hierarchical columns within each other, such as in the analysis below where I’ve nested Times (Time Dimension) within the Staff (Parent-Child) hierarchical column.

So far, so good. As I mentioned before, pivot tables themselves have had a revamp in this release, with one of the key features being the ability to swap dimensions about when the pivot table is displayed in the dashboard (in 10g, you had to return to Answers to rearrange the layout). In the example below, I’m moving the Times (Time Dimension) hierarchical column so that it’s under the Product (Level-Based) hierarchical column, by grabbing the grab-bar above the Times (Time Dimension) hierarchical column and then dragging it to the new position.

You can also sort the pivot table by clicking the up and down arrows that appear over columns or along rows, or you can right-click anywhere in the pivot table and access a contextual menu from there.

Another feature in this new release is the ability to create dynamic groups (often referred to as custom aggregates); for example, to create a custom aggregate made up of Alison Chisel, Cassandra Barry and Pete sims (all of which are at different levels in the hierarchy), I press the New Group button in the Results panel menu, and then select these members of staff for the group.

After pressing OK to create the group, I can see them in the hierarchical column, and I can also drill into the group too (the old Calculated Item option is still there as well, for backward compatibility).

Hierarchical columns also bring another bonus, in the form of being able to access alternate hierarchies in a dimension. In the Times logical table I’ve expanded in the view below, I have two hierarchical columns, one for the calendar hierarchy and one for the fiscal hierarchy. In the past, I could only drill-down on one of these but now you can see both hierarchies listed in the Criteria view, and I can select which one I want to drill-down on (not quite as good as Discoverer, which let me display both on the report at the same time, but it’s getting there).

There’s one other major change with the introduction of hierarchical columns. For attribute columns, you can still filter in the same way, picking the column and then setting up the filter (Product Name = ‘Shoes’, or Amount < 100, for example). With hierarchical columns though you can set up step-by-step filters, which will seem familiar to anyone who used Oracle BI Beans, or Discoverer for OLAP, in the past. In this example, I’m displaying the Amount measure by the Stores (Ragged & Skip-Level) hierarchical column, and I’ve opened up the Selection Steps window at the bottom, which allows me to define step-by-step restrictions and additions to the set of stores that are displayed in the pivot table.

I want to change this to start with a list of stores, then restrict this list to just stores that have sold over 100, but then add back in any stores that are in the top 10% based on sales.

This clearly owes a iot to Oracle Express and Oracle OLAP, where you would progressively restrict, expand and redefine the “status” for a dimension, and the dialog owes a lot to a similar one in Discoverer for OLAP. From an initial look, it doesn’t look as easy to create complex, multi-dimensional conditions such as “give me the list of stores whose sales are in the top 5% of stores based on last year’s sales across just the food and beverage product lines”, but this is something I’ll need to play around with a lot in a future posting. For now though, we’ve got the traditional ability to apply sorts against columns (attribute columns in OBIEE 11gR1-speak), or we can use these selection steps for when we’re working with hierarchical columns. This feature will be especially useful for parent-child hierarchies (including ones with Essbase as a data source) that don’t have levels (aka columns) to filter against, instead having members that are arranged into an arbitrary ragged hierarchy.

In the next posting in this series, we’ll be taking this one step further by looking at how Essbase support has been improved in the 11g release of OBIEE. In the meantime, if you’re interested in seeing more of OBIEE 11g in action, BIWA SIG (the Business Intelligence, Warehousing and Analytics SIG) are running a webcast on Wednesday on Data Visualization Best Practices using OBIEE 11g, run by our friends Dan and Tim Vlamis. Attendance is free and it’ll be a good chance to see a demo of 11g.

If you were at the London launch of OBIEE 11g last week, or listened in on the webcast, you’ll have heard Paul Rodwick talk about support for ragged and skip-level hierarchies whilst he demonstrated the 11g version of the BI Administration tool. If like me you spend most of your development time in OBIEE working with the BI Server repository (the RPD), this was interesting news as hierarchies in the 10g release were fairly limited, and were always level-based and balanced. So how does this new feature work?

The 11gR1 release includes a number of enhancements to dimension handling, including:

• Support for parent-child (value-based) hierarchies
• Support for ragged level-based hierarchies
• Support for skip-level level-based hierarchies

The support for parent-child hierarchies has implications for Essbase and Oracle OLAP-based sources, but we’ll leave this to a later blog post. For now, I’m going to look at how these new features support more complex relational-based hierarchies.

To take an initial example, suppose we have a dimension that organizes stores into regions, stores and concessions:

If this was a simple, level-based balanced hierarchy, all would be straightforward. However imagine that this hierarchy was in fact ragged, with only certain stores having concessions. In this case, the leaf levels for the hierarchy wouldn’t be at the same level, something that would have caused problems for RPD modellers working with OBIEE 10g.

Now in 10g this would have been tricky, as OBIEE would have expected each leaf member to be at the same (in this case, concession) level, and if they weren’t, you’d need to fudge the data a bit, for example by adding dummy concession members so that each leaf was at the same level.

Going back to our hierarchy, another interesting thing you often get in real life is “skip levels”. Imagine that some of our concessions are so important that they report directly to the regional office, with no store in between. In the diagram below, the SFO concession reports directly to the North CA regional office, and again in 10g you’d need to create a dummy store parent for this concession to make it all work.

OBIEE 11g can handle this though with the new ragged and skip-level support for level-based hierarchies. It does this by detecting NULLs in either leaf levels (for ragged hierarchies) or other levels (for skip-level hierarchies) and use this to modify how the new hierarchical column type in Answers handles the missing levels. To create a ragged and/or skip-level hierarchy, you would therefore import your source schema into the OBIEE 11g physical layer as normal, and then create the logical model in the business model and mapping layer, like this:

Then, and this is a new feature in 11g, you choose whether to create your hierarchy as level-based, or parent-child. In this instance, the hierarchy is still level-based, and this option should then be selected.

You now get the opportunity to specify whether the hierarchy is ragged, skip-level and/or for a time dimension.

The hierarchy itself is then created as normal, with levels for All Stores, Region, Store and Concession, going down to a common primary key level so that each row in the source table has an ID that can be used to reference it.

This logical model is then copied across to the presentation layer along with the hierarchy, with the primary key level then being deleted so that users don’t drill down to it (it’s just there for internal purposes, not for displaying the user).

With the RPD then complete, I can then switch over to Answers and display the hierarchy. As you can see from below, it’s handled the ragged elements (some stores having concessions, some not) and the skip-level (for the SFO concession) correctly.

Another new feature in 11g is the ability to designate hierarchies as parent-child. Taking our example further, imagine that our stores had staff associated with them, and these staff had managers, with staff being stores in the underlying relational database as a table with a manager ID associated with them. In this case, when defining the hierarchy you can specify this to be the case.

This then brings up a dialog where I can name the dimension, and where I pick the logical column that provides the parent ID for the dimension member, in this case the Manager Name.

The way that 11g supports parent-child hierarchies is to create what’s referred to as a “closure table” behind your source data, that takes each of the individual members and explodes-out the hierarchy. This is done through a wizard that runs SQL against your data set, creates and populates the table and then maps it into your physical layer metadata, creating a simple link between the data in your fact table and this parent-child hierarchy. You’ll need to refresh this table every time the parent-child hierarchy changes, using a script provided by the wizard.

Taking a look at the final results in Answers, you can see the parent-child, ragged nature of the hierarchy at work, in the screenshot below showing sales for each of the salespeople arranged into the hierarchy that we just built.

So there you go. In the next posting, we’ll move away from the back-end and take a look at the hierarchical columns feature in 11g, and also see how this has led to enhancements in the pivot table views that we use in Answers.

Although the most obvious new thing about the recently launched 11gR1 release of OBIEE is the user interface, under the covers there’s been a lot of changes to the architecture, and how you perform systems management. There’s still the basic concept of a BI Server, Presentation Server, RPD and so on, but it’s now all managed and in some cases hosted, using WebLogic Server, the J2EE application server that Oracle have adopted following the acquisition of BEA.

For the 10g release, OC4J and in some cases, Oracle Application Server, was used in a limited form to provide a gateway between the users’ browser and the C++ OBIEE server applications, and to host the BI Office and BI Publisher J2EE applications, but you generally didn’t have to get too involved with it. In OBIEE 11g, WebLogic together with associated technologies such as OPMN, DMS and ODL are much more prominent and if you’re going to be administering an OBIEE 11g system (including running it on your own desktop), you’ll be getting to know WebLogic pretty well, together with the latest version of Oracle Enterprise Manager (or as it’s called in this release, Oracle Fusion Middleware Control).

When looking at the architecture of a new product, I often like to start at the top and work down. The diagram below shows the high-level architecture for OBIEE 11g, with the diagram showing a single OBIEE “BI Domain”, a container for a complete, single (and perhaps clustered) installation of OBIEE 11g.

At this high level, components in OBIEE 11g can be divided into two types:

• Java Components, the parts of OBIEE that are written in J2EE and run directly in the application server. These components include BI Publisher, the BI Office plugin, the BI Security Service, BI SOA Services, BI Action Services and the JMX MBeans that are used for configuring OBIEE
• System Components, which were implemented as Windows services in 10g, and include the BI Server, BI Presentation Server, BI Scheduler and BI Cluster Controller

This is similar to how it was in 10g, in that some (more recent) components of OBIEE 10g were implemented in J2EE and installed in either OC4J or Oracle Application Server, whilst the historical core of OBIEE ran as server components that you managed through either scripts, or Windows services. In 11g, these server processes still run outside of the J2EE application server, but now they are managed through the application server administration console and are stopped and started as if they were regular J2EE components (Oracle consider these Java components and system components as “peers”).

The Java Components and System Components are managed through a number of tools, including Fusion Middleware Control (aka Enterprise Manager), Weblogic Server Admin Console and OPMN. All of these components connect to a repository database, which can be any recent Oracle database and contains the schemas (scheduler, nqacct etc) that you had to create manually in 10g, plus a few others new for 11g.

If you’ve not used WebLogic Server before, each WebLogic installation has at least one “admin” server, and usually one or more “managed servers”.

• The Admin Server contains the admin console application, used to administer managed servers, and Fusion Middleware Control, used to administer OBIEE 11g
• One or more Managed Servers, which contain the J2EE OBIEE applications plus the “coreapplication” System Components

When you install OBIEE 11g, you get one admin server and one managed server. You can create another managed server, either on the same box if it’s big (as you used to do with multiple OC4J JVMs in 10g), or more usually on additional machines that are then managed by the original admin server. The admin server console is generally used to manage WebLogic-level tasks (starting up managed servers, communicating with the node manager etc), whilst the majority of the OBIEE administration is carried out using Fusion Middleware Control (which sometimes gets referred to as Enterprise Manager).

So whilst it looks initially a bit confusing and overwhelming, the basics are still the same. The BI Server is still a standalone C++ application, it’s just that it’s managed (stopped, started, monitored etc) through Fusion Middleware Control and OPMN. The various J2EE components such as BI Publisher are still J2EE, they are just installed in WebLogic now (apparently this will be extended to other J2EE application servers over time). Some key differences, especially if you’re running on Windows, is that there are no longer and BI Server, BI Presentation Server etc services, and you also can’t go around editing configuration files now as WebLogic manages them (actually this is only partially true, and I’ll get onto that in a separate posting). More importantly, all the WebLogic overhead means that you’ll need about 4GB of RAM if you’re installing OBIEE 11g on your laptop, along with a database for the repository schemas as source data, which is about twice what you need for the 10g version, so now’s the time to put in for a laptop upgrade.

There are some key Fusion Middleware technologies, terminology and utilities that you’ll need to get to know when starting to work with OBIEE 11g.

• WebLogic Server (often abbreviated to WLS) : this is the J2EE application server that Oracle uses across the board for all their 11g BI applications, such as Essbase 11.1.2, Discoverer 11g, BI Publisher 11g , Real Time Decisions 11g. It’s also used for the upcoming Fusion ERP applications, and is the default application server that you get with JDeveloper 11g. In the past it’s role in OBIEE was performed by OC4J and Oracle Application Server (which used OC4J under the covers), and both of these in 11g have been replaced by the core WebLogic Server, with JRockit (as opposed to the Sun JVM) generally used as the JVM (thanks Simon H for the clarification in the comments).

• OPMN (or Oracle Process Manager and Notification Server to give it its full name) : You may have first come across this in Oracle Application Server 10g, where it was used to start up the various Application Server components (OC4J_BI_Forms, OC4J_Portal etc). Its since been adopted across the technology stack to start, stop and monitor processes across distributed servers, and it’s used in OBIEE 11g to do the same for the system components (BI Server, BI Presentation Server, BI Scheduler and BI Cluster Controller). You can either access OPMN through the command line (opmnctl), or Oracle’s recommended approach is to use a graphical interface within Fusion Middleware Control. OPMN is also used in the 11g stack to control Essbase, Discoverer and other BI components, so it’s a tool that’s worth learning.
• DMS, or Dynamic Monitoring System, works alongside OPMN and provides statistics on the various components which can be persisted in a database. This will definitely be interesting when it comes to performance monitoring and optimization for 11g.
• ODL, Oracle Diagnostic Logging, is a centralized framework for logging. Again, interesting for performance, though not all logs (nqquery.log, for example) are covered by it.
• JPS (Java Platform Security) and CSF (Credential Store Framework) are just two parts of security in 11g. Security is a big change in 11g with users no longer stored in the RPD (including the Administrator user, which has now gone), and users, roles and groups now managed by WebLogic, linking out to LDAP servers as neccessary.

Two other changes are around patching, and upgrading. Patches are now handled by OPatch, the standard mechanism for other Oracle products. I’m in two minds about this as previous patches for 10g were dead simple – you just installed the new version over the old – but for 11g, it’s a lot more complicated now and I guess OPatch, with a patch repository and ability to roll back, was inevitable at some point. There’s also an Upgrade Assistant, again something common in the Oracle world, to migrate installations and metadata from previous releases.

So, one way to understand how all the components fit together is to look at how the components start up after a reboot. With OBIEE 11g, you use Oracle WebLogic Server admin console to start up the WebLogic Managed Server that contains the OBIEE components, and then you use Oracle Fusion Middleware Control to start these individual components up.

1. Once the WebLogic Server admin server is up and running, your first step is to log into the WebLogic admin console (typically, at http://localhost:7001/console) and navigate to the Server view. You then instruct the Node Manager to start WebLogic managed server, which typically takes a couple of minutes to complete.

2. Starting the managed server will also auto-start the java components (again via Node Manager), and the system components (via OPMN), so that you should now be able to log in. On a fresh installation, you’ll need to log in using the WebLogic Server administrator password that you specified during the install and configuration.

3. If you want to stop and start any of the OBIEE components, you do this via Fusion Middleware Control (or via the OPMN command line utility, which we’ll cover in a future posting). In the screenshot below, I’m showing the buttons used to stop and start all of the components together. Other screens let you stop and start individual components, something you’ll still need to do if you update a configuration file, switch to a new RPD etc.

A couple of points to notice on this. Firstly, on the left-hand side you can see the entry for the managed server (bi_server1) under bifoundation_domain, that contains the J2EE components for OBIEE. The coreapplication under Business Intelligence is the application that manages the server applications (BI Server etc) that are written in C++. Also note that one of the five components is down. Which one is it, and what is causing it? Let’s take a look.

4. Keeping with Fusion Middleware Control, I select Capacity Management > Availability from the tabs, and see that it’s the Scheduler that’s down.

Now this is the one component that depends on the database to be up and running before it’ll start, and although I know I’ve configured it (this is done automatically during the install now), it might need a restart to get it working. The recommended way to do this is to stop and start it from Fusion Middleware Control, so I highlight the BI Schedulers entry and press the Start Selected button above it.

It starts OK, so it was probably just a problem with the database not being up when the auto-start happened. I could also have started the component using opmnctl, and this is something I’ll look at more in a future posting.

5. Finally, I want to switch off caching, as OBIEE turns it on by default for new installs but this interferes with results when I’m doing development work. In OBIEE 11g, whether caching is turned on or not is still determined by an entry in the nqsconfig.ini file, but you’re meant to manage this setting via Fusion Middleware Control (if you edit it directly in the file, the admin server will overwrite your changes when you restart it, though you can disable this behaviour). To turn off caching, I navigate to the Capacity Management > Performance tab, where I can see the entry to enable the cache.

But if you notice, it’s all grayed-out. This is because you have to “lock and edit” the session first, which serialises access to these settings and stops another administrator changing things at the same time as you. I press the Lock and Edit Configuration button, and then uncheck the Cache Enabled checkbox, press Apply, then then press Activate Changes. This copies the new parameter value into the nqsconfig.ini file, but I still have to restart the BI Server system component before this becomes active, just like I had to do with OBIEE 10g.

So there you have it, a mix of old stuff (the BI Server and Presentation Server, config files etc) and new (WebLogic Server, system components, OPMN and so on). In future postings I’ll be looking at how you perform other OBIEE 11g administration and systems management tasks, and I’ll also take a look at how high availability and scaling out is achieved (clue – it’s much easier in 11g).

In the meantime, if you’d like to keep abreast on the news on OBIEE 11g as it becomes available, including details of our forthcoming 11g-focused training courses and services, be sure to bookmark our newly-launched OBIEE 11g Resource Centre. We’ll be posting links to all our OBIEE 11g articles, and there’ll be news in a few days time about a special, hands-on OBIEE 11g Training Days event we’ll be running in London and Atlanta in October 2010.

Now the launch of OBIEE 11g is behind us, we can start to take a look at some of the key new features in this release. One of the most obvious changes in OBIEE 11gR1 compared to the 10g release, is around the visual look and feel of the web-based components. OBIEE 11gR1 has a new, “Fusion” user interface that takes a task-orientated approach to working with the tool, making it easier for first time users to create a new report, access existing reports and dashboards, and take advantage of new functionality such as agents, KPIs or scorecards.

Oracle call this new interface the “Unified Framework”, and it combines the functionality of Answers, Interactive Dashboards and Delivers into a single interface. In addition, products such as BI Publisher (referred to as “Published Reporting”) and Marketing are integrated into the same screen, together with new Performance Management features such as KPIs and Scorecards. This unified framework can be broken down into three areas of functionality:

• The Home Page, shown in the screenshot above, that provides quick links to existing reports and to create new ones;
• The common Catalog page, which provides list of reports, dashboard and other objects, plus a search facility, and
• The common Header, the section at the top of each page (including the home page) with buttons for key function

The Home Page, shown in the screenshot above, is itself divided into several sections. On the left-hand side is the Create… section that provides links to create a new Analysis (reports, or the old “requests” in the 10g version), Dashboard, Published Reporting (BI Publisher reports), as well as agents and other objects. It also makes the process of creating dashboard prompts and shared filters more obvious (in 10g these were hidden away in the Answers interface), and also makes it possible to define “conditions”, a new web catalog object that formalises the process of defining boolean tests around report results.

If you select, for example, the Analysis option in the Create… list, a list of subject areas are displayed for you to choose from. Once you select the subject area, the interface opens up in a view similar to Answers in the previous release. We’ll look at this in a moment.

Under the Create… list is a set of shortcuts for browsing or managing your set of reports. The whole area within the home page is scrollable within a frame, and in the screenshot below I’ve scrolled down to reveal the Browse/Manage… area with Get Started… underneath it.

Over to the right of this panel is an area showing at the top, your Recent dashboards and other objects, and below that, the most Popular ones accessed by others on your system. Under each of these objects are links to open the object, edit it, print it and perform other tasks.

This area together makes up the homepage, which scrolls within a frame header up by the common Header area. The common Header area can be found on all of the various product pages, and provides a consistent set of buttons, options and other links that are used across all of the OBIEE 11gR1 application.

Starting with the bottom row first, there are buttons for Home (to take you back to the homepage), Catalog for opening the common Catalog page, buttons to create New reports and Open existing ones, and a link with your logged in name on it for opening the My Account page.

Clicking on New displays a list of the objects that you have permission to create. The most commonly used one will probably be Analysis, which opens Answers to create a new report, Dashboard, to create a new dashboard, Dashboard Prompt for a drop-down list for the dashboard, Report for a BI Publisher report and Agent, to create what in 10g was called an iBot.

Selecting Analysis from the New menu will firstly ask you to select a subject area, and then will open the new version of Answers (called Answers+ in the lead-up to 11g). Answers looks similar to the 10g release in that it has two panes, and in one of the panes you can switch between Criteria, Results, Prompts and Advanced tabs.

We’ll look at the new Answers in a future posting, but for now we’ll click on the Catalog link in the common Header area. This opens up the common Catalog view, which in 11g benefits from a search facility that indexes and crawls the web catalog in the background.

In the Catalog view, you can navigate through the web catalog on the top left-hand side, which in 11g also includes BI Publisher content.

On the right-hand side you can see the list of objects currently selected, and which also includes a preview facility that allows you to see the report before you open it (you can disable this by minimising the Preview area below the listing. Also on the bottom left-hand side are a list of Tasks, actions that you can perform on the object that is selected. All of the objects that you can see, and the tasks that you can perform on them, are secured based on your profile in the web catalog.

New to the Catalog link in the header area is a drop-down list of Dashboards. Clicking on this displays a hierarchical list of the dashboards you can open, organised into group folders if you’ve set these up.

Next to the New drop-down list is one for Open, which initially displays the same list of recent and popular objects that are shown on the homepage (in case you’ve got another page open when you click this list).

Clicking the Open link at the top of this list opens a dialog, again new in this release, where you can navigate through the web catalog.

On the line in the header above the New and Open links is a Search area, with an object-type drop-down list and text box for simple searches, and an Advanced search button that opens the Catalog view and allows you refine the search, sort the contents and so on.

Next to the Search area is a button for Administration, that when pressed shows (assuming you are have the BIAdministrator application role) the Presentation Server administration page. This is similar to the same page in OBIEE 10g, but now includes links to reload server metadata, manage maps and also manage BI Publisher (which in fact just takes you to the standard BI Publisher administration screen).

So that’s a quick guide through the new Unified Framework and homepage in OBIEE 11gR1. In the next couple of postings, I’ll be looking at hierarchical columns, enhancements to dimension handling in the RPD, and enhancements to pivot table views that can also be used dynamically in the dashboard.

Today was the launch of OBIEE 11g at Old Billingsgate, in London. I guess most readers of this blog, along with us, having been looking forward to this release for months, if not years, and so it was good to finally have the product announced and for customers to get a look at the new features in more detail.

The morning started off for us around 8.30am, as Rittman Mead had a partner booth in the main exhibition area. There were around ten or so partners exhibiting, and around six hundred or so attendees, including a lot of people from the States as their event isn’t until later in July. The main session itself started at 10am with an introduction by Oracle’s Andrew Sutherland and David Callaghan, and then Charles Phillips came on to set the scene.

The main detail of the launch came with Thomas Kurian’s session, where we were given a tour of the technology and thinking behind 11g, with the main themes being integration, enterprise, storage-to-scorecard and leveraging of technologies such as Essbase and Exadata.

To be honest, as long-term beta testers none of this was news to us, but it’s good to see the product finally launched and to hear the positive feedback from the attendees. Particularly impressive were the demos all performed by Paul Rodwick, as there was a lot of technology to show, a lot of demo steps to memorize, and (apart from a small glitch with BI Publisher) all going perfectly to plan. Well done to the Oracle team, and to Paul, for everything running smoothly.

After the main sessions were a number of breakout sessions around OLAP integration, systems management, scorecard and so on. I went to the OLAP one but then went back to the booth to speak to some people, but then went back at the end for the partner-only session where I was on the panel with Roger Cressey (Qubix, Hyperion) and Mike Pell (Oracle).

So, we’re now at the point where OBIEE 11g is launched, but it’ll be several weeks before the software is available for download on OTN. In the meantime, starting from tomorrow we’ll be posting a number of articles on the blog going into more detail on some of the key features, including enhancements to the RPD, the role of WebLogic, better support for Essbase and other features.

We’re also launching a number of new services, training courses and upgrade offers around the OBIEE 11g launch. We’ll be posting more details soon, but in the meantime be sure to bookmark our OBIEE 11g Resource Centre page, and also take a look at our two datasheets on OBIEE 11g services and our new updated OBIEE 11g course catalogue.

• Rittman Mead OBIEE 11g Services, Training and Support
• Rittman Mead Training Catalogue, Updated for OBIEE 11g

If you’re interested in more details from Oracle, check out the Oracle BIEE 11g launch microsite, where you’ll find additional collateral and white papers, a webcast of the event, demos, and a video featuring our own Borkur Steingrimsson talking about the systems management features in 11g.

Finally, keep an eye on this site for an announcement next week around our upcoming OBIEE 11g Training Days event in the UK and USA, where we’ll be taking you, fresh from the beta, through hands-on sessions on 11g led by myself and our team. This will be a highly-technical session for those already skilled in the 10g release of OBIEE, with limited availability (no more than 30 attendees at each session), and if you’re interested and want to register your interest in advance, drop me an email at mark.rittman@rittmanmead.com.

I am currently sat in the audience at the Official Oracle OBIEE 11G launch.

If you are on twitter you can follow the hash tag  #OBIEE11G

There probably 300 people here, all eager to see what has taken Oracle 3 years to produce.  I am pretty sure they will not be disappointed.

Over the coming weeks keep an eye on the Rittman Mead blog as we showcase the new features.

The launch is also being webcast at  http://www.oracle.com/businessintelligence11g/index.htm.

Every year, ODTUG shows why Kaleidoscope is the premier conference for the technical hacks in all of us. I can’t remember a time when I learned so much, had so many great conversations, and felt more at home than this year. The content was spectacular across the board, and even though Open World is looming large on the horizon, I already feel excited about Long Beach in (roughly) 365 days.

Mike Durran’s talk on Monday about the new OBIEE 11g features was enlightening. We’ve seen some of the features in the beta release, but having Mike demonstrate the OEM integration, security improvements, and general industrialization of OBIEE was eye-opening. As Rittman Mead has been instrumental in implementing the 10g release of OBIEE for clients over the years, and working with those clients (and the world at large via the blog) to try and circumvent some of the limitations in the current product, it’s good to see that Oracle is listening to the needs of their clients. Mark’s presentation on the internals of the BI Server was great as usual. I’ve seen this content before, but it was enjoyable to revisit. The academic approach Mark took in trying to define the sometimes undocumented behavior of the BI Server shows the right way to approach product-discovery.

Also good to see was David Allan’s presentation on “right-time” loading with Oracle OWB 11gR2. David knows this product as well as anyone, and he has a handle on what the product can do now as well as what clients want to see in the future. David demonstrated that OWB can make use of GoldenGate in a (reasonably) uncomplicated way. He also knows his way around a pint of Guinness (Scot’s drink Irish stout?), and though I got less sleep than I should have, I had more fun than I deserved. I also learned quite a bit from Holger Friedrich’s presentation on Pushing the Envelope with OWB. I always get questions from clients about how to properly do SCM with OWB, and I think now I just might start handing them a copy of Holger’s presentation. Really, really good stuff Holger.

My session on New Data Warehousing Features in 11gR2 was well-attended, and I think it delivered some good content around parallelism-enhancements, summary management improvements, and cool new tricks with SQL Analytics. I think the LISTAGG function was of particular interest… so that old workhouse STRAGG now has a shorter shelf-life. That’s OK… I’m sure Tom is tired of answering questions around it anyway. I’d like to thank Tim Tow of AppliedOLAP for giving me a great introduction.

I enjoyed the BI Panel moderated by Mark, and all the panelists and attendees had a good chance to discuss the future of BI within Oracle. Again we had Mike Durran, together with Bob Ertl, Joe Leva and Holger Friedrich. The contrasting perspectives of implementors and product managers rounded-out a great discussion, and I saw Bob take numerous notes about what customers are hoping for in the future direction of OBIEE.

Finally was the OWB Deep-Dive this morning with myself, Mark and Holger. It was a last-minute replacement for a cancelled session, but I think the content was very good and well-received. We rarely get a chance to go in-depth in our sessions, nor answer questions for half an hour either. The attendees were all familiar faces from our other sessions for the whole week, and we appreciate their dedication for seeking out the session even though it wasn’t in the session catalog, for getting started with us at 8:30 in the morning, and for sticking with us for three hours.

Thanks to Maria Colgan, Jean-Pierre Dijcks, Holger Fiedrich, Mike Durran, Mark Rittman and David Allan for the extra-curricular activities. I think I’ll sleep in late tomorrow.

Last time I mentioned some of the challenges of taking realtime feeds and publishing them into a data warehouse. This time I am going propose a way to meet those challenges.

But before that I will take a small detour around what Oracle refers to as their Reference Data Warehouse and Business Intelligence Architecture. Here we are dividing the data warehouse into three “sections”: “staging” which is a local copy of data from the source systems; “foundation” which is typically a “process neutral” 3NF representation of the business data such as “customer”, “product” and “orders”. It is likely to be different in structure from the staged tables in that it could well be the merging of data from multiple sources, for example customer attributes could come from both CRM and ERP systems. This foundation layer is likely to be versioned (that is, whenever a dimensional attribute changes a new, current version is created) and non-aggregated. The data in this layer are our BI jewels; we don’t know what future analysis and data mining needs will be and by aggregating things we lose the flexibility we might need; remember there is no UNGROUP BY clause in SQL. The third tier is performance and access layer where we typically present optimized table structures to the query tools, it’s here that we have the aggregated tables, bitmap indexes and all of those other ‘traditional’ data warehouse features. This not really a revolutionary (or even new) architecture – I have been doing similar things in my data warehouse design since the 1990s.

One of the key things to note is that the staging tables for the dimensional data should be complete replicas of the source tables and not just a set of extracted rows provided by the source data owner. Here is the ideal place to bring in replication technology and hence the beginnings of a real time data warehouse. Fact (or more accurately in this case “events”) in the staging area are not going to be full replicas of the fact source but rather all of the events that have occurred since the last load, agin this can be achieved by realtime replication. Remember we only need to replicate the tables of interest and not all of the structures of the source applications. At first glance it might seem extravagant to have effectively three copies of dimensional data (one in each layer) and two of facts – but these days disk is cheap and it is also (tongue firmly in cheek) a good way to use some more of the disk space you had to buy to get the required data throughput.

Acquiring data in realtime is not going to be our problem, and if we can use the stage tables directly for reporting then we can say “job done” and not worry more. Our problems arise if we need to do significant work on the staged data to report over it. We might see problems with data quality, surrogate key management for dimensions, particularly slowly changing dimensions, and the need to aggregate facts to improve performance of the query tool.

I am not going to get into the debate on what to do about data quality, I have blogged about that in the past. The only thing I will say though is that the resolution to data quality problems should be in the source system(s) – data warehouses should report the same data as used in the transactional systems and if that requires a master data management program then so be it.

I suspect that this next point might be considered heresy, but if you have immutable business keys for your dimensional data (perhaps from a master data management system) then consider using them in the data warehouse – this will reduce the complexity of the ETL processes needed to push data from stage to the data warehouse, it might also remove a time dependency of pushing dimensional data through to the foundation and access layers in realtime. The need to track slowly changing type 2 dimensions (where we keep a history of change) might force the use of surrogate keys, but other approaches are possible that might avoid the need for surrogate keys being used on the fact tables; one approach is to split the non-volatile and SCD-1 attributes from the versioned (SCD-2) attributes and store the dimension in two tables, with the first table joined to the FACT table (on the business key) and the versioned table of SCD2 attributes joined to first dimension table on business key; queries against the second table will need to also pass a date so the correct version is selected, but this is not hard to achieve with most query tools. By far the easiest thing to do, though, is avoid SCD-2 all together; many organizations think they need to implement SCD-2, but when they come to use the system they find that SCD-1 actually fits the reporting requirements of the vast majority of their users.