DWH News

Information Builders, the independent leader in operational business intelligence (BI) systems, today announced that the iWay Enterprise Information Management (EIM) suite was named a "Trend-Setting Product" of 2010 by KMWorld magazine. The award recognizes products that clearly demonstrate technology breakthroughs, which serve the vendors' full spectrum of constituencies, especially their customers. The iWay EIM Suite was selected for the second consecutive year.

Ya sabemos que no tenemos estrategia (al menos en Business Intelligence) y que tenemos ya alguna idea por dónde empezar. Esperemos que, al menos tengamos, una metodología clara de trabajo. En el contexto de la inteligencia de negocio existen diferentes enfoques en el momento de recopilar la información.

Uno de ellos es el uso del framework Zachman. Parémonos a pensar un momento el porqué. Cuando queremos diseñar una aplicación  de Business Intelligence, necesitamos saber qué datos coger, cómo recuperarlos, dónde se encuentran, cuando recuperarlos, quién es el responsable de los datos y quién los necesita y por qué. Y todo eso desde un punto de vista de contexto, concepto, lógico, físico y una representación detallada. Eso es en definitiva lo que permite analizar el framework de zachman.

Esta idea no es para nada nueva, pero a falta de una metodología clara ¿por qué no usar ésta?

You might have missed it late on Friday afternoon, but the long-awaited 11gR1 release of Oracle Business Intelligence is now available for download on OTN. Three years in the making, this new release of Oracle’s business intelligence platform provides an updated user interface, better integration with Essbase, new OLAP capabilities, the Action Framework for integration with applications and web services, and improved management through Enterprise Manager and WebLogic Server.

Rittman Mead have been one of the longest-running beta testers for this new release, and since the launch have made a wealth of information available at our OBIEE 11gR1 Resource Centre. Now that the product is generally available, keep an eye on this site for more in-depth articles on how to get the best out of this new release.

Rittman Mead also have two immediate offers for customers looking to implement OBIEE 11gR1. Our Oracle BI Training Days 2010 event, running in London, Atlanta and Bangalore, offers hands-on training on this new release led by our beta-testing team. Aimed at developers with a basic understanding of Oracle BI, this will be one of the first opportunities to get proper, in-depth training on this new release.

We also have a number of consulting packages for customers looking to upgrade, or implement OBIEE 11gR1 from scratch. Our OBIEE 11g Upgrade service takes you through the process of planning the new 11gR1 architecture, and then moving your existing metadata, reports and dashboards to the new platform, whilst our OBIEE 11g Implementation service is aimed at organizations implementing Oracle BI for the first time. We also have a number of free and trial services, details of which, together with our consulting offerings, are in this datasheet.

Finally, it’s Oracle Open World 2010 in San Francisco soon, and Rittman Mead are proud to be delivering ten sessions over the week, on OBIEE 11g together with supporting technologies such as Oracle Data Integrator, Oracle Golden Gate and Oracle data warehousing. Keep an eye on this blog for full details of these sessions.

One of the significant changes in the BI EE 11g is in the way clustering is done. Mark has covered horizontal clustering (using the scale-out option in the installer) in his previous post here. The entire process of clustering is much more automated in 11g and is much easier to manage & maintain. BI EE 11g supports 2 kinds of clustering

1. Vertical Clustering – Multiple instances of BI Server, Presentation Services etc on a single box
2. Horizontal Clustering – Multiple instances of BI Server, Presentation Services etc on multiple boxes

Vertical Clustering is new to 11g as this is something that 10g did not support(though there were unsupported work-arounds available). Vertical Clustering is preferred in cases where we want to make optimal use of the hardware & provide fault tolerance. This does not provide high availability when the server itself goes down. In this type of clustering generally 3 components are clustered

1. BI Server
2. BI Presentation Services
3. Java Host

That is, all the components that can act in active-active configuration can be clustered in Vertical Clustering. Other components like Scheduler, Cluster Controller which work in active-passive fashion are generally not clustered vertically.

In this blog entry we will be looking at how Vertical Clustering is done. One important point to note is, the concepts of clustering itself haven’t changed much.

We start with logging into the enterprise manager FMW control and navigating to the deployment section. Since we are doing vertical clustering, we do not need to have a shared drive for the repository & the web catalogs. But again its better to identify a drive that can potentially be shared in the future with other machines as Vertical Clustering does not provide high availability(for future migration to horizontal clustering).

Once the drive is identified (one for RPD and the other for Web Catalog), copy the web catalog to the shared drive. In the deployment section of the Enterprise Manager, enter the shared directory details of both the RPD and the Web Catalog. Upload the repository (RPD) into the shared drive using the enterprise manager.

After uploading the repository into the shared drive, restart the BI Server. After the restart you will notice that the repository that has been uploaded in the shared drive would automatically be synchronized to the BI Server.

After making this change navigate back to the Capacity-Scalability tab and increase the number of BI Servers and Presentation Services to 2 as shown below

This will automatically create new instances(within the main instance) in BI EE for both the BI Server and Presentation Server(after Activating the changes). We can validate this by looking at the number of directories under {ORACLEINSTANCE}/bifoundation/OracleBIServerComponent and {ORACLEINSTANCE}/bifoundation/OracleBIPresentationServerComponent

You can see 2 new directories created for the new BI Server and Presentation Server that we added through the EM. Lets now start these new components through the capacity management interface

In order to test the cluster, lets open up the DSN and configure the Admin Tool DSN to connect through the cluster controller as shown below

Lets now login to the Admin Tool and see the status of all the new servers in the Cluster Manager

As you see, the new components that we added for vertical clustering have been enabled automatically. This entire process now has become a lot easier. Also, Presentation Services plug-in and Presentation Services have been decoupled. So, essentially a single web server can communicate to multiple presentation services in a round-robin fashion without the need for load balancer. In horizontal clustering with multiple machines, load balancer will be required though in order to switch between different HTTP servers based on the incoming load. The screenshot below shows the sessions in multiple presentation services on a single box.

Next up is a blog post on the new Lookup feature followed by the Double Column feature available within the repository.

In a previous posting in this series, I looked at OBIEE 11gR1’s architecture at a high level, and yesterday following the official launch I took a look at the installation process. I briefly I touched on a few administration tasks such as starting and stopping the OBIEE components, but in this posting I want to look at this topic in more detail, looking at where all the various files have gone and how you perform basic administration on the system.

First up, as an OBIEE 10g administrator, your administration tasks were mostly performed either through the Administration tool, the web-based Presentation Server administration screen, or through editing files in the filesystem. There were something like 700 or so configuration options spread over multiple tools and configuration files, with some options (users and groups, for example) embedded in unrelated repositories (the RPD). OBIEE 11g addresses these by where possible moving administration and configuration into Fusion Middleware Control (also referred to as Enterprise Manager).

To start off with something familiar, the Administration tool that was present in OBIEE 10g is also present in 11g, is also Windows-based, and is used to maintain the semantic model used by the BI Server. Here’s a screenshot of the 11g version, showing the SampleApp and some of my own subject areas:

This tool is more or less the same, and has some enhancements in terms of dimension handling, new data sources and the like. A big change though is around security; now when you bring up the Security Manager dialog, it looks like this:

Users and Application Roles (roughly analogous to groups in 10g) are now defined in the WebLogic Server admin console, and you use the Security Manager to define additional links through to other LDAP servers, register custom authenticators, and set up filters and other constraints. In the above screenshot, the users shown in the Users list are those that are held in WebLogic Server’s JPS (Java Platform Security) service, and there are no longer any users and groups in the RPD itself. Notice also that there is no Administrator user – instead the standard administrator user is the account that you set up as the WebLogic Server administrator when you installed OBIEE, which typically has the username weblogic. There are also two additional default users; OracleSystemUser is used by the various OBIEE web services to communicate with the BI Server, and BISystemUser is used by BI Publisher to connect to the BI Server as a data source (both default to the same password as the weblogic admin user you set up during the install).

If you switch to the Application Roles tab, you’ll also see a list of new default application roles; BISystem, BIAdministrator, BIAuthor and BIConsumer, which are used to grant access to Presentation Server functionality and also encompass the old XMLP_* groups that you used to get in 10g that were used to manage access to BI Publisher. There’s also AuthenticatedUser which is the same as found in the previous release. So how do you create a new user in OBIEE 11g? For that you’ll need to start up the web-based WebLogic Server admin console.

To create a new user, log on to the WebLogic Server admin console (http://localhost:7001/console) and bifoundation_domain > Security Realms from the Fusion Middleware Control menu. Then from the list of security realms, select myrealm, and then from the Settings for myrealm dialog select Users and Groups, and then Users, from the tab menu, You are then presented with a list of existing users.

Pressing the New button brings up a dialog where you can enter the user’s details, and you can also use the Groups tab to define a group for the user, or assign the user to an existing group. Security is quite a bit of a big change in 11g and in addition, we have the Application Roles setting that you saw in the Security Manager screenshot, which you then map to the groups in WebLogic. I’ll cover security in a future posting, but for now, this is how to define basic users and groups.

Another area that’s changed significantly where configuration files and metadata files are stored. In OBIEE 10g, you had two top-level folders, $ORACLEBI and $ORACLEBIDATA. $ORACLEBI (typically installed, for example, in c:\oracle\oraclebi) would hold binaries and configuration files for the BI Server, plus other components such as BI Publisher and JavaHost. $ORACLEBIDATA (installed, typically at c:\oracle\oraclebidata) would hold binaries for the Presentation Server, config files for the Presentation Server, plus cache files and temporary files for the BI Server. In OBIEE 11gR1 the filesystem changes, with the diagram below showing the high-level filesystem layout for a Windows installation at c:\Middleware:

So where are the key files that we are used to working with? Taking my installation on Microsoft Windows 2003 Server, and with OBIEE 11gR1 installed at C:\Middleware, here’s where my key files are located:

• RPD Directory : C:\Middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
• NQSConfig.INI : C:\Middleware\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1\nqsconfig.INI
• NQClusterConfig.INI : C:\Middleware\instances\instance1\config\OracleBIApplication\coreapplication\NQClusterConfig.INI
• nqquery.log : C:\Middleware\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1\nqquery.log
• nqserver.log : C:\Middleware\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1\nqserver.log
• nqsserver.exe : C:\Middleware\Oracle_BI1\bifoundation\server\bin\nqsserver.exe
• Webcat Directory : C:\Middleware\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\
• instanceconfig.xml : C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml
• xdo.cfg : C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\xdo.cfg
• sawlog0.log : C:\Middleware\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1\sawlog0.log
• sawserver.exe : C:\Middleware\Oracle_BI1\bifoundation\web\bin\sawserver.exe

Taking a look at tthe NQSConfig.INI file, whilst the format is the same, notice how many of the parameters are now marked as being managed by Enterprise Manager (Fusion Middleware Control):

Now these are parameters that you’re supposed to change only through Fusion Middleware Control. You can change them manually, but they’ll get overwritten by the WebLogic Server admin server when you restart WebLogic. You can override this behaviour so that changes you do make to these particular parameters don’t get overwritten, but then you’ll have to remember to copy changes to all the nodes (in OBIEE 11g, clustering is automatically enabled). Not all parameters are managed in this way (in the screenshot above, DATA_STORAGE_PATHS, POPULATE_AGGREGATE_ROLLUP_HITS and USE_ADVANCED_HIT_DETECTION still have to be changed by manually updating this file, but over time the plan is to move more and more parameters to management through Fusion Middleware Control.

To change the managed parameters, go to Fusion Middleware Control, log in as an administrator user (weblogic/welcome1 in my case), and click on the coreapplication node under the Business Intelligence menu entry, so that an overview of the system components status is shown:

From this screen, you can stop, start and restart all of the system components (BI Server, Presentation Server etc) via OPMN. From this point, you can then click, on the Capacity Management, Diagnostics, Security or Deployment tabs to perform further maintenance.

• Capacity Management has four further sub-tabs, and can show Metrics gathered via DMS; the Availability of all the individual system components (allowing you to stop, start and restart them individually); Scalability lets you dynamically increase the number of BI Servers, Presentation Servers, Cluster Controllers and Schedulers in the cluster in conjunction with the “scale out” install option, and Performance lets you turn caching on or off and modify other parameters associated with response time.
• Diagnostics has two sub-tabs; Log Messages shows you a cluster-wide view of all server errors and warnings, and Log Configuration lets you limit the size of logs and what information gets included in them.

• Security is used for enabling SSO and selecting the SSO provider
• Deployment has five sub-tabs; Presentation lets you set dashboard defaults around page tabs, section headings etc; Scheduler sets the connection details for the scheduler schema; Marketing is for configuring the Siebel Marketing Content Server connection; Mail is for setting up the mail server that’s used by Delivers for email alerts. The most interesting tab is Repository though, as this is where you upload new RPDs for use by the BI Server.

When you first navigate to this tab, the option to upload a new RPD is grayed-out. This is because you have to press the Lock and Edit Configuration button, which stops anyone else from attempting the same operation at the same time. The default installation of OBIEE 11gR1 comes with an RPD called SampleAppLite, and I want to replace this with my own RPD, developed offline previously.

After pressing Lock and Edit Configuration, an “in progress” message comes up, and then you can start uploading your new RPD file. In the example below, I’ve used the Browse button to pick up a new RPD called OBIEE11g_Examples.rpd, and I’ve entered the RPD password into the text boxes below (remember in 11g, the RPD itself has a password, rather than you giving the password of an RPD user with admin privileges as you did with 10g).

Pressing Activate Changes will firstly bring up a message saying that the changes will be applied regardless of whether you close your browser window, and shortly afterwards, a second message is displayed saving that the action is completed successfully.

Then if you check the NQSConfig.INI file, you should see your change written to the file. (Technically, the Activate Changes process actually writes the changes to an intermediate file, which the Admin Server then polls regularly and once it sees the changes, writes them to the NQSConfig.INI file).

At this point though, as with OBIEE 10g, you still need to restart the BI Server for this change to take effect. To do this, click on the Restart to Apply Recent Changes link at the top of the web page, which takes you to the Overview page for the coreapplication system components in Fusion Middleware Control. From this point, you can either restart all components (which is a bit of overkill), or switch to the Capacity Management tab, then Availability sub-tab, and restart just the BI Server system component. Once you’ve done this, the new RPD will become active. Note also from the screenshot above that RPDs get automatically versioned, with each upload of a particular RPD being saved in the BI Server repository directory with a sequence number appended to it.

Many administration tasks in 11g are the same as 10g. For example, the log level for a particular user is still defined in the security manager, and you still view the query log (nqquery.log) either through the filesystem, or through the Manage Sessions link in the Presentation Server administration screen. Usage tracking is still manually set up through the NQSConfig.ini file, though the schema it uses is automatically created at installation time through the RCU (Fusion Middleware Repository Creation Utility). In 11gR1, only a subset of these administration tasks are performed through Fusion Middleware Control, but as the releases stack up, more of these functions will move to this environment, something that’s more important now that clustering is turned on by default.

Finally, the Administration screen in the Presentation Server web interface has had a visual overhaul with the 11g release. Some functions, such as the one to reload server metadata in 10g, have moved from Answers into this screen, and new functions have been added to manage, for example, the mapping feature.

Once you get beyond the main menu screen, the way the functions work hasn’t changed much in this release. Some of the dialogs have visually changed, but as you can see in the screenshot below, the functions work in much the same way as 10g, and you can see the Application Roles that were visible in the Security Manager at the start of this posting being used to grant access to Presentation Server functionality.

So that’s it for basic administration. Take a look at our OBIEE 11gR1 Resource Centre for a complete listing of our 11g postings, and we’re also running a special, three-day Oracle BI 11g Training Days event in Atlanta, London and Bangalore later in the year if you’re after in-depth, hands-on training on this new release. For now though, I’m going to hand-off to Venkat for a series of postings on the new features in the 11g BI Server.

Venkat broke the news earlier today that the full, production release of OBIEE 11gR1 is now available for download on OTN, and in this posting, I’m going to look at the process of installing and initially configuring OBIEE 11gR1. Now I’m not going to go through every step in the process as it’s fairly self-explanatory, but I wanted to pick out some of the changes since OBIEE 10g, highlight some of the interesting bits and talk about what it takes to set up a self-contained demo environment. In later postings I’ll be looking at setting up the RPD, managing the environment using Enterprise Manager, and taking a look at some of the new front-end features.

In my demo environment, I’ve got Oracle Database 11gR2 already installed on Windows Server 2003 Enterprise Edition 32-bit. I’m actually using VMWare Fusion (as I’m on a Mac; on Windows I’d use VMWare Workstation or Server instead) to which I’ve allocated 4GB of RAM. This is the first big change compared to OBIEE 10g, which you could generally get to run, along with a local Oracle database, within 1.5GB – 2.GB of RAM. We’ll see later on just what’s taking up the additional memory.

There are two main stages to the OBIEE 11gR1 installation:

1. Firstly, we use the RCU (Oracle Fusion Middleware 11g Repository Creation Utility) to set up the BIPLATFORM schema to support OBIEE 11gR1, and
2. Then, we install OBIEE 11gR1, including the Oracle WebLogic Server J2EE application server that takes the place of OC4J and Oracle Application Server 10g in this release.

If you’ve ever installed any Fusion Middleware 10g or 11g products (such as Oracle Application Server 10g, or some of the new 11g products such as Discoverer 11g), you’ll be familiar with the Repository Creation Utility. This is a Java application, like the Universal Installer, that sets up one or more schemas that need to be in place for the products you are installing. For this release, you can install the required schema into either Oracle Database 9i, 10g or 11g, Microsoft SQL Server or IBM DB/2. I start the RCU, select the option to create schemas (as opposed to dropping them), and then choose the Oracle Database option and enter the required credentials.

The RCU then carries out a quick check on the database you intend to use (my 11gR2 database didn’t need any changes, and I’d installed it fresh with the data warehousing option), and then prompts you to choose which schemas to install. This version of the RCU has an entry for Oracle Business Intelligence, which has a single entry for a component called Business Intelligence Platform. The MDS schema under AS Common Schemas is selected automatically, and is required by the underlying Fusion Middleware platform.

The prefix section is so that you can install multiple sets of schemas for different environments on the same database, but for now I leave this at the default value of DEV, for “development”. In OBIEE 11gR1, this DEV_BIPLATFORM schema contains all of the tables that in prior releases, were in schemas such as S_NQ_ACCT, S_NQ_SCHED and the BI Publisher scheduler schema, and it also contains tables that are used by OBIEE 11gR1 additional products such as the EPM Suite Calculation Manager, Oracle Real Time Decisions and other supporting products.

The database is then checked for compatibility, and then another screen is displayed, where you can fine-tune the names given to the tablespaces used for the DEV_BIPLATFORM schema plus other ones that are needed by Fusion Middleware 11g. I just leave these at the default values and allow a separate tablespace to be created for each schema. The RCU then creates the tablespaces and temporary tablespaces, and then creates the schemas and their tables. This process replaces all of the separate scripts that you have to run to set up the Delivers scheduler tables, the BI Publisher scheduler tables, the usage tracking accounting tables and so on that you had to do as post-install steps with OBIEE 10g. Once this is done, you’re ready to install OBIEE 11g proper.

Once the RCU process is complete, it’s time to install OBIEE 11g proper. After starting up the OBIEE 11gR1 installer, the first big change compared to 11g is that we’re now using the 11g version of the Oracle Installer (as used in Oracle Fusion Middleware 11g, and Oracle Database 11gR2) as opposed to the old Siebel-derived one. This new installer prompts you to use the RCU if you’ve not done so already, and lists out the tasks down the left hand side that you’ll need to complete.

The first page proper of the new installer asks you to select from one of three install types:

1. Simple Install, which is what most developers testing out the software will require, installs a basic OBIEE 11gR1 system along with WebLogic Server, using default values for things like domain names, port numbers and the like.
2. Enterprise Install gives you a few more options than a Simple Install, letting you customize the name of the WebLogic domain, the WebLogic Managed Server, the ports that are used and so on. If you need to vary away from the defaults, or “scale out” an existing installation, choose this option (but for most developers, Simple Install will be fine)
3. Software Only Install will install the OBIEE 11gR1 software (but not WebLogic Server, you need to have this already installed), but doesn’t set up any “instances” of OBIEE, a similar situation to when you install the Oracle Database binaries but don’t configure a database instance.

Selecting the Simple Install option will move you on to the next page for the installer, which then prompts you to select a “middleware home” for your installation. All of the OBIEE 11gR1 installation will go into this (there are no longer OracleBI, or OracleBIData directories), including the binaries and the “OBIEE instance” files, creating a separation in 11gR1 between those files that are static – the binaries and configuration file templates – and those that change over time, which include the configuration files, repositories, XML files and web catalog.

Pressing Next then brings you onto a screen that prompts you for a username and password for the WebLogic Server, and OBIEE, administrator. In 11gR1 there is no longer the concept of an Administrator user, and instead you create an administrator user here that becomes the default admin login into OBIEE (and the various WebLogic Server administration tools). I choose the username weblogic password welcome1, and press Next to proceed.

I’m now asked to confirm the products that I want to install. In later releases of OBIEE 11g, this list may include products such as Oracle Essbase and the other EPM Suite products, but for now you can select Business Intelligence Enterprise Edition, Business Intelligence Publisher, and Oracle Real Time Decisions. I select all three and press Next to proceed.

Next I’m prompted to enter connection details to the database in which I set up the DEV_BIPLATFORM schema. I look back to the connection details I used for the RCU and enter these details, pressing Next to continue.

After running a couple of checks and asking me for my Metalink login (optional, can be used for notification about available patches), the installer then summarises what it’s going to do next, and invites me to press the Install button to start the installation.

The installation process then begins, and starts by installing the supporting WebLogic Server files required for the infrastructure layer.

It then proceeds to install what in 11gR1 are called the “System Components”, which in this release are still C++ applications but are managed now by WebLogic.

Once installation has finished, the configuration process starts. Initially, this is concerned with instantiating the WebLogic domain and managed server, together with supporting technologies such as Enterprise Manager and Oracle Web Services Manager.

Then, the BI Server, BI Presentation Server and other system components are registered within the WebLogic Server managed server, and OPMN is configured so that it can stop, start and monitor them (more on this in future blog posts).

Other components that were included as part of the components I selected are then configured, and in the screenshot below we can see the parts of OBIEE that run as J2EE applications (referred to in 11gR1 as “Java Components” as opposed to “System Components”) being deployed to the WebLogic Server managed server. Both the Java components and system components will later on be managed by Enterprise Manager and the WebLogic Server admin console.

Assuming all of this completes without error, you are finally presented with a summary of what’s been installed on your system, like this:

So what does the Enterprise Install option do then, beyond and above this? Let’s go back to the start of the installation process and see what extra choices we get.

After selecting the Enterprise Install option, you are firstly asked whether you want to Create a new BI System, or Scale Out BI System. We’ll get onto the scale out option in a moment, but for now we select the option to install a new system, and enter the WebLogic / OBIEE administration user details, and also in this instance give a name to the WebLogic domain that’ll be set up.

I choose the same user name and password as before for this account, and leave the domain name at the default. Pressing Next then brings me to the next set of choices, where I supply the location of the middleware home, the domain home and the instance home and name, which is where the various configuration and repository files will be stored for the new OBIEE instance.

After this screen, you are then prompted to select from the same product list as in the Simple Install option. After selecting the same products as before, the next new screen is one where you can either choose to use the default port numbers, or you can point the installer to a file that contains your own custom port mappings (this is a standard process in the Oracle Universal Installer, but the Simple Install option just used the defaults).

Beyond this point, the installation process is the same as with the Simple Install option, and the installer will continue to install, and then configure, the options you have chosen.

Going back a moment to the start of the Enterprise Install option, you may remember that there was an option to “scale out” an existing OBIEE system. What this does is allows you to add more OBIEE and WebLogic components to an existing OBIEE 11gR1 domain, and we’ll look at this option in more detail in a later blog posting. In a similar vein, the third option, for a Software Only Install, lets you install just the OBIEE components (system components and Java components) into an existing WebLogic installation, like this:

This is useful if you’ve already got a WebLogic Server installation somewhere and you want to configure it to run OBIEE as well, and we’ll look at these last two areas in some future blog postings when we address the topic of scalability, clustering and high availability. For now though, we’ve got an OBIEE 11gR1 system installed, and once we’ve done a reboot (not mandatory, but can often free up a bit of memory after the install process) we can select the Run BI Services entry from the Oracle Business Intelligence Windows Start Menu entry to start all the processes.

If you take a look at the list of Windows services before this menu entry is selected, you can see that the list of servers – Oracle BI Server, Oracle BI Presentation Server and so on – that were listed as part of 10g, have now been replaced by two new ones in this release.

Oracle Process Manager (Instance1) is the service for OPMN, and controls the status of the system components that have replaced the individual services for components such as the BI Server and Presentation Server. Oracle WebLogic NodeManager (c_Middleware_wlserver_10.3) is a service that starts up and shuts down the individual WebLogic Server managed servers, not strictly necessary on a one-node system but essential for multi-node systems, so that an individual WebLogic Server admin server can start up remote managed servers.

Once you run the Windows Start Menu entry, a DOS box opens up which allows you to type in the WebLogic Server admin username and password, which you set earlier on in the install process.

Once this is entered, a WebLogic Server script then starts the admin server, then the managed server, the OPMN service any other components, and about five minutes later you should be able to log into OBIEE 11g, again using the WebLogic Server admin username and password.

So there we have it. Tomorrow, I’ll be writing about basic OBIEE 11gR1 administration processes including a guide to where all the key files have gone, which will then be followed next week by a series of postings by Venkat on new features in the BI Server 11g.

As Mark has shown in the previous 11g related blog entries, BI EE 11g introduces a lot of new features across the entire spectrum of BI EE. If you had got a chance to look at the list of new features, you can realize that significant amount of effort has been done in R&D as well as in the development. Some of the new features are quite path breaking innovations and some of them are outright customer requirements. I will start my 11g postings with the complete list (relevant) of new features that have been introduced in the BI EE, starting with the component that is closer to my heart i.e. the BI Server.

Majority of the new features introduced in the BI EE stack (in Answers, Scorecards) can be related to a new feature in the BI Server. So, if you are just now starting with 11g, I would recommend going through the complete feature set in BI Server before moving on to the other components. The list of new features (along with changes to existing features) are given below

1. Support for SELECT_PHYSICAL commands

This is one feature that truly opens up the entire BI Server to external tools/applications. Till 10g, the biggest drawback with any external tool interfacing with BI Server through ODBC had to fire Logical SQL. Logical SQL though is similar to ANSI SQL; there are quite a few operations that are completely different from ANSI SQL. For example, we can have a Logical SQL with SUM operation in the select but without a GROUP BY. So, any external tool had to know the Logical SQL constructs to interface with BI EE. But with 11g, that is not needed anymore. SELECT_PHYSICAL commands directly can bypass the BMM layer and do normal SQL (close to ANSI SQL) queries on the Physical layer objects.

The second biggest advantage is one can do a SELECT_PHYSICAL on a relational table and any other data source like Essbase & join them together. So, it’s not necessary anymore to go through the BMM layer for leveraging the true in-memory joining capabilities of BI Server. I will cover this in detail in another blog post.

2. Support for Lookups & Removal of Bridge Tables

One significant change in the BMM layer of BI EE is there is no more an option to treat a logical table as a Bridge Table. It is now recommended to model many to many joins in the LTS using physical bridge tables (which is what customers in 10g were doing anyway). So this is a welcome change.

The other significant change is the ability to mark any logical table as a Lookup table. A lookup table does not need to have relationship with any other table. BI EE now supports 2 kinds of Lookup operations

1. SPARSE Lookup – This is more like a Left Outer joins to the driving table. If you have used ETL tools like Informatica, the terminology of lookup is very similar. A Sparse lookup assumes that the lookup does not contain values for all the entries in the main table. Hence this would result in a left outer join
2. DENSE Lookup – This is more like an equi join between the driving table and the lookup table. This assumes that for every record in the main table there is a corresponding record in the lookup table

This lookup operation can be pushed completely to the database or can be completely done in the memory of the BI Server. The exact syntax etc will be covered in a separate blog post.

3. BI Server is now Truly in-memory

BI Server can now do operations in the RAM of a server. So, it’s becoming more like an in-memory database that can do joins on request across data sources.

4. Enhanced Caching

BI Server can now cache intermediary queries. For example, if a report requires 3 different queries to be fired separately, then BI Server can now cache each one of them separately. This becomes all the more relevant if you use lookup tables frequently.

5. Deferring Session Variables & BI Server based init blocks

One of the biggest problems in 10g was every session variable that was initialized using init blocks will be fired during the authentication process and hence can significantly increase login times. In 11g, there is now a new feature that allows the init blocks to be deferred for later execution i.e. init blocks will be fired only when variables are accessed in answers.

Also, now one can directly fire queries against BI Server to populate init blocks thereby benefitting from the advantages provided by the BI Server (like avoiding a round trip to the database just for populating a simple session variable)

6. Aggregate persistence wizard can automatically create indexes.

7. Support for Oracle RPAS, FMW View Objects, HFM as a data source

This is one significant addition in the BI EE 11g release. BI EE can now report directly on ADF 11g view objects. So, it’s quite easy to call any external application view objects like Ebusiness Suite etc. For this to work, there is a set of configuration that we need to do on the BI Server.

Also 11g now supports HFM and Oracle RPAS data sources.

8. Change in Behavior when RPD upgrades happen

There are quite a few modeling behavior changes that we have to consider while doing an upgrade. Some of the important ones are listed below

a. Any level based measure assigned to the detail level of a dimension will not result in repeating rows when the report is at a higher grain (when compared with the detail level). If the report contains normal attribute columns at upper levels, then the detail level aggregation will be ignored (normal aggregation will happed). If the report contains hierarchical columns, then such measures will produce null values at higher grain.

b. Ordering of LTS determined the query path in 10g. In 11g, this determined by the Priority Order set at the LTS level.

If you have a highly customized repository that depends on level assignments and LTS switching, I would recommend devoting more time in understanding the generated sql queries as there can be differences across releases.

9. Parent-Child Hierarchies, Skip level & Ragged Hierarchy handling

Mark has already covered this in detail here.

10. New Functions in the RPD – CALCULATEDMEMBER, AGGREGATE AT, ISCHILD, ISPARENT, ISROOT, ISANCESTOR & ISDESCENDANT, PERIODROLLING, EVALUATE_ANALYTIC

CALCULATEDMEMBER – This function provides an ability to generate custom calculated members within a hierarchy. For example, it is possible to create a calculated member from 2 members at 2 different levels in a hierarchy.

AGGREGATE AT – This function provides the same functionality as a level based measure i.e. the filters applied on the dimension to which the measure is aggregated at will not be applied to that measure.

Hierarchical Functions – BI EE 11g now supports hierarchical functions like ISROOT, ISCHILD etc that can be used to traverse a parent-child hierarchy.

PERIODROLLING – This is a new time-series function that can be used to do rolling time series based analysis. All time series functions are now supported directly from Answers.

EVALUATE_ANALYTIC – This is a new Evaluate function that can be used function ship Oracle database analytic functions.

11. Double Column support for members. Now it is possible to filter on the IDs when an end user chooses the description.

This is one feature that I am pretty sure everyone expects by default in a reporting tool i.e. the ability to pass IDs when a description is chosen in a prompt. But the way it has been implemented in BI EE actually makes it useful for a lot of applications than just description/id switch. This can be put to use for multi language applications where the descriptions can be any language but the id remains the same.

12. Equalize RPDs as part of the merge – One big issue in 10g while merging RPDs was the fact that the equalization process had to be done externally to make sure the ids were consistent. Now in 11g, Equalizing can be done as part of the merge itself.

13. Security has changed significantly. Users/Groups will not be stored within the repository anymore. To edit/assign users/groups to different objects in offline mode, atleast one property of such groups will have to be modified

14. RPD Compression – There will be a significant difference in the size of the RPDs in 11g release due to a new compression feature that is enabled by default. Also, now that users/groups are no longer stored in the repository that will further add to the reduction in size.

15. Import of metadata from the connection pool directly.

16. Ability to control writeback in the RPD and support for presentation layer hierarchies

17. Patching of Repositories – BI EE 11g now more variation of merge for doing incremental migration. This uses the concept of merge and generates incremental XML patch files which can then be applied on to the repository that needs to be patched.

18. Ability to hide Level Based Measures while browsing a hierarchy.

19. A new utility has been introduced to Prune the repository of unused objects. This can be very useful for large repositories.

20. Support for BLOB/CLOB columns in the repository

21. Ability to push measures within GROUP BY operation – Controlled from UI

22. Support for Standby databases in the Physical Layer

23. Multi-User Development has been enhanced significantly.

24. Support for Vertical & Horizontal Clustering.

25. Finally, the repository downgrade utility nqgenoldverrpd is back as part of the software binary. This utility was present in the 10.1.3.2.1 version of BI EE but was removed in the later releases. So, in the later releases, one had to apply workarounds like the one that i had covered here. This utility will be very handy when working across multiple releases. But this utility cannot downgrade a 11g repository to a 10g repository. It can only downgrade to intermediary releases.

Looks like BI EE 11g silently made it to OTN yesterday. The software is available for download now. It can be downloaded from here.  Currently its available only for Windows (32 bit & 64 bit) & Linux (32 bit & 64 bit).

Watch out this blog for detailed postings on new features and the changes in 11g.

You may not completely agree, but some people say that SharePoint 2007 was a disruptive collaboration technology. Was it the best solution on the market for enterprise collaboration? Probably not. But it did show us a different way of sharing. And it did bring forward a number of new solutions for the enterprise commonly called social software. 

Algo serio se mueve en España los últimos años en el área del BI. Tres empresas ya consolidadas (y referenciadas en diversos análisis de mercado), de renombre y reconocidas internacionalmente y tres de nueva factura. Buenas iniciativas todas ellas y muy interesante en enfoque de cada una de ellas en su aproximación al Business Intelligence . Veamos de qué empresas estamos hablando y damos cuatro pinceladas de ellas.

• ApeSoft: ofrecen una solución que incide en el problema de la gestión y del uso de spreadmarts para la toma de decisiones. Es decir, siendo Excel una de la principales herramientas de análisis, Apesoft busca hacer business intelligence basado en el mismo pero de forma que sea fácil de gestionar. Este enfoque es muy interesante dado que no busca el cambio disruptivo en el usuario final sino un cambio gradual basado en soluciones conocidas y además resolver la gestión de spreadmarts en el contexto de una organización. Esta empresa internacional fundada en 1994 y que hace poco ha recibido una importante inversión por parte de la Caixa, cuenta más de 300 clientes. Ofrecen cinco productos:
  • DataCycle Reporting – una solución de software business intelligence fácil de usar para automatización del Reporting e informes Excel.
  • DataCycle MyVision – un portal web de reporting fácil de usar para ejecutar y compartir informes Excel ricos gráficamente.
  • DataCycle Planning – una solución completa para la gestión integral del proceso presupuesto, desde su confección hasta la automatización del seguimiento.
  • DataCycle Scorecard – una solución integral para ejecutar y desplegar la estrategia de la organización a todos los niveles de la compañía.
  • DataCycle Smap – una aplicación gratuita y sencilla de usar para diseñar y crear mapas estratégicos.
• Denodo: ofrecen servicios de Enterprise Data Integration tanto en despliegues locales como SaaS.  Es decir, resuelven problemas en entornos de heterogéneos de datos (estructurados, semi-estructurados y no estructurados) como: recuperar, consolidar, validar, aplicar reglas,… en los datos y sobretodo consumirlos. Algunas de sus aplicaciones más importantes son: Product Data Management, Customer Data Management, Citizen Data Management,… Denodo es también una empresa internacional y reconocida mundialmente como un líder en Information-as-a-Service (por Forrester). Tienen clientes de renombre como Vodafone, Telefónica o Inditex entre otros.
• AparaSW: empresa tecnológica española, fundada en 1997, referenciada por Gartner gracias a su solución sobre gestión del fraude empresarial. Bajo la plataforma dVelox Enterprise (disponible también on-demand) se extiende una gama de productos que abordan problemáticas como la prevención del fraude, retención-fidelización de clientes y optimización de decisiones en el área de marketing. Es decir, ofrecen soluciones que buscan resolver determinadas problématicas comunes a la gran mayoría de las empresas.
• LiteBI: Ofrecen una solución completa de Business Intelligence basada en SaaS con un enfoque híbrido. Con ello quiero decir: un cliente ETL realmente potente y completo (basado en Kettle) que permite exportar los datos a la capa de metados de LiteBI y una solución SaaS que incluye informes, OLAP y cuadros de mando. Busca agilizar el despliegue de un proyecto de inteligencia de negocio, evitar al cliente la complejidad de gestión de un entorno de BI y reducir el tiempo de implantación de la solución. El usuario final sólo tiene que preocuparse de analizar la información conforme sus necesidades. Tienen ya diversos clientes y partners.
• Bingo Intelligence: solución que permite crear cuadros de mando e informes. Ofrecen un cliente de desarrollo ágil, rápido y sencillo (entorno windows) de estos objetos de análisis (los videos de presentación de producto inciden en este punto y lo confirman). Su objetivo es trasladar el análisis que se realiza en hojas Excel (con los problemas de información desestructurada, no integridad de datos,…) a un entorno controlado de una solución que no esté penalizado en términos de funcionalidad y sobretodo de usabilidad. Su lema: “Business Intelligence para seres humanos”.  No hay clientes conocidos (al menos desde su web).
• Tuent: Es un servicios SaaS (y también en cliente) para el análisis de información geolocalizada. Permite la creación de cuadros de mando y exportalos a PDF, Word o Excel. Busca agilizar la toma de decisiones sobre información territorial incidiendo en que no hay despliegue de infraestructura. Este enfoque sobre una solución de inteligencia de negocio aplicada sólo a información geolocalizable es muy interesante dado que la segmentación de datos (clientes, proveedores, comerciales, etc.) no sólo se segmenta geográficamente siguiendo por ejemplo, provincias y comarcas sino con criterios más complejos como bricks en el sector farma. Demo gratuito: Tuentfree. Tienen ya algunos clientes importantes en España.

¿Conocéis alguna más para añadirla a la lista? [Nueva Versión, gracias Begoña por la referencia de Apara]

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.