Subscribe

RSS Feed (xml)

Powered By

Skin Design:
Free Blogger Skins

Powered by Blogger

oracle search

Monday, July 14, 2008

BI for the Masses - is here! (BI SE1)

Ever thought what you could get for $5,000? Five thousand dollars? Apart from a year's worth of mortgage payments on my apartment (and that would also change depending on the currency rate fluctuations - mind you my loan is rupee denominated, I am just getting a bit carried away with this analogy), I can think of Oracle Business Intelligence. Yes. Oracle Business Intelligence Suite Standard Edition One is now available for download from OTN. Let's just call it BI SE1 and forget for a minute all the branding and marketing people who may get very, very angry with me for messing up with the branding of the product (there are others also who I may piss off, but अब किसे याद रखूँ और किसे भूलूं, आख़िर दुनिया भर का ठेका तो नहीँ लिया है मैने|)।

OK, so BI SE1 is available for download from OTN। I already said that, didn't I? Right. Where can you get it? From the OTN download page, where else? Here.
And just what exactly does this BI SE1 package contain? The kitchen sink, almost.

  • Oracle Database Standard Edition One (version 10.2)
  • Oracle Warehouse Builder (core ETL only) (version 10.2)
  • Oracle Business Intelligence Answers (version 10.1.3.2.1)
  • Oracle Business Intelligence Dashboard
  • Oracle Business Intelligence Publisher
  • Oracle Business Intelligence Server
  • Oracle Business Intelligence Server Administrator

Each of these products is best-in-class, mind you. The Oracle Database, of course. Oracle Warehouse Builder is recognized as a leader among ETL tools. And BI Suite Enterprise Edition is a big reason why so many analysts, including Gartner, have recognized Oracle as a leader in BI Platforms. BI Publisher is a world class, performant, scalable, and mighty easy to use enterprise class reporting product. So there!

And what did I mean by the $5000 figure? I was lying, to be truthful. BI SE1 is licensed at $1000 per user, with a minimum of five users. So that means that the actual pricing for the entire kitchen sink is $1000!! But of course you would want to share the wonders of this product with more than just one user, right? Of course you would!

To download the product, go to this link


There are two tiny files that need to be downloaded. How tiny are we talking? Oh, just about 2.9GB (3,073,149,672 bytes to be precise). That shall take some time.


While you wait for these tiny files to download, you may want to check the doc library for this suite. That would be this link.


As you would see, apart from the regular doc on the Oracle Database, Oracle Warehouse Builder, and Oracle BI Publisher, there is one doc introducing BI SE1, and a bunch of tutorials.


Click the 'Getting Started' tab, and there are at least two other links of interest:
  • Business Intelligence Standard Edition One Install Guide (HTML, PDF)
  • Business Intelligence Standard Edition One Tutorial (HTML, PDF). The tutorial is a 226 page document that covers all topics you would expect, like how to setup the tutorial data mart, the BI repository, how to analyze data, publish reports, create Dashboards)
More tutorials are hosted on OTN here.
  • Creating Interactive Dashboards and Using Oracle Business Intelligence Answers
  • Creating a Repository Using the Oracle Business Intelligence Administration Tool
  • Installing the Oracle Business Intelligence Enterprise Edition on Windows
  • Using Oracle OLAP With Oracle Business Intelligence Enterprise Edition
  • Integrating Oracle BI Publisher with Oracle Business Intelligence Enterprise Edition
  • Sending Alerts Using Oracle Business Intelligence Delivers (Delivers is not part of BI SE1, but a little knowledge can't hurt, can it?)

Never enough space - dwitiya

I really, really need to do some cleaning up... (no, not personal hygiene, but that too).
See my earlier post for some context (There is never too much email space)

Similar sounding names can be confusing

As I remarked in my earlier post, I am no branding expert (gold medal from IIM Bangalore notwithstanding - heh heh... I had to say that), so I cannot comment on why the names for the three editions of Oracle Business Intelligence were chosen the way they are:

Oracle BI Standard Edition One
BI Standard Edition (SE)
BI Suite Enterprise Edition (EE)

Actually, to be fair, I can comment with some insight into these names... These naming conventions are also in use by the Oracle Database and the Oracle Application Server.
The Oracle Database 10g is available in these editions: Standard Edition, Standard Edition One, Edition, and also the free Express Edition.
The Oracle Application Server 10g is available in the following editions (link): Enterprise Edition, Standard Edition, Standard Edition One, and Java Edition.

Therefore, goes the thinking, BI should also follow the same convention. Which it does. The BI SE1 bundle is a subset of the BI EE bundle (it is missing Delivers for example), but with some additional products packaged (like the Oracle Database and Oracle Warehouse Builder), and with a lower price entry point. We don't have an 'Enterprise Edition One' bundle in either the Oracle Database or the Oracle Application Server, so to have a BI EE1 name may have confused people, though my **personal** preference would have been to name it so. Yeah, who cares about my opinion, personal or otherwise?

BI Standard Edition is unchanged: Discoverer is an integral part of BI SE, and will continue to do so.

Hope that clarifies...

And while on the subject of BI SE1, here is what the installer screen looks like: a native OUI screen, with some tweaks...

I am trying to find a machine where I can install all of this... I have BI SE 10.1.2 installed on one, BI 10.1.3.3.0 installed on the same machine and also on my work laptop, so I am pretty much looking at installing this on a VMWare image. If I get to it soon enough I shall do a post, else there are always the tutorials, OBEs, and the doc.

Updated Discoverer Statement of Direction

Mike Durran, product manager for Discoverer and BI SE, has posted an updated statement of direction for Discoverer on OTN. Get the PDF here.

Latest Discoverer Statement of Direction

The latest Discoverer Statement of Direction is now available from OTN:

http://www.oracle.com/technology/products/discoverer/index.html

or a direct link to the document:

http://www.oracle.com/technology/products/discoverer/pdf/discoverer_sod_2007.pdf

There will also be a session at the ODTUG conference next week in Daytona Beach covering the Discoverer roadmap and plans to integrate with Oracle BI Enterprise Edition.

Hope to see you there !

Interesting issue when downloading BI SE1

When downloading BISE1 I faced an interesting problem. After some initial puzzlement I figured out it has something to do with IE (Internet Explorer).

There are two files you have to download for BISE1 from OTN:

  1. http://download.oracle.com/otn/nt/ias/101321/biseone_windows_x86_101321_dvd.z01
    and
  2. http://download.oracle.com/otn/nt/ias/101321/biseone_windows_x86_101321_dvd.zip
These two files have the same name, except for the extension. The first one is "z01" and the second "zip".


In Firefox, the file extensions are preserved the way they are, so that the two files are distinct when downloaded. No problem here - see the screenshot below.


But when trying to download it using IE, the extension is changed to .zip

IE for some reason decides that z01 should really map to zip, so that downloading both files results in one being overwritten.
So the solution, workaround, is to download them using the "Save As" option, manually specify the extensions to avoid this problem...

Wonder why this is happening why the way it is - this happened to me on two separate machines, and at least one other person from the field faced the same issue.

CRN Article on BI SE1

CRN has this article on the Oracle Business Intelligence Suite Standard Edition One launched earlier this month (also see my earlier posts - this and this).

What no GUI? How to Build Analytic Workspaces using scripts

What no GUI? How to Build Analytic Workspaces using scripts

Recently on the OLAP OTN forum there have been a number of questions regarding the use of scripting to control the design, build and maintenance procedures for analytic workspace. There are many reasons for using scripting rather than using Analytic Workspace Manager to build and maintain an AW. Many DBAs prefer to use scripts because they can be scheduled and/or quickly and easily moved from one platform to another without having to install any GUI components. Most likely scenario is that for the production environment a direct connection from a GUI client tool is not allowed. Obviously if you are using Warehouse Builder this is not a problem as OWB will automatically generate deployment and maintenance scripts that do not require execution via the GUI. However, when you are just using AWM things can get a little complicated.

As there is so much information to cover I am going to break this into a series of postings. The purpose of this one is to provide a brief summary of some of the ways to create and define objects within an AW from the command line.

Part 1 : How to create a new AW

It is possible to create an AW from the command line by using the AW_ATTACH procedure. The AW_ATTACH procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session. AW_ATTACH can also be used to create a new analytic workspace, but the AW_CREATE procedure is provided specifically for that purpose.

DBMS_AW.AW_ATTACH (
awname IN VARCHAR2,
forwrite IN BOOLEAN DEFAULT FALSE,
createaw IN BOOLEAN DEFAULT FALSE,
attargs IN VARCHAR2 DEFAULT NULL,
tablespace IN VARCHAR2 DEFAULT NULL);



Notice using the scripting approach it is possible to set the default tablespace for the AW, a feature that is missing from the GUI. For example, the next SQL call creates the AW GLOBAL_PROGRAMS as the last user-owned analytic workspace in tablespace USERS:

SQL>EXECUTE DBMS_AW.AW_ATTACH('global_programs', true, true, 'last', ‘USERS’);

Alternatively you can go a step further and use the AW_CREATE command, which provides more control in some areas and less control in others. The AW_CREATE procedure creates a new, empty analytic workspace.

DBMS_AW.AW_CREATE (
awname IN VARCHAR2 ,
tablespace IN VARCHAR2 DEFAULT NULL ,
partnum IN NUMBER DEFAULT 8);


For example, the next command creates an analytic workspace named GLOBAL_AW. The resulting table AW$GLOBAL_AW will have two partitions and will be stored in the GLOBAL tablespace.

SQL>EXECUTE DBMS_AW.AW_CREATE('global_aw', 'global', 2);

With the AW_CREATE command it is possible to control the number of partitions, which is a very useful feature. But you do lose some control over where the AW is created in the attach list. This is only important if you have multiple AWs attached during the creation process. So why do we have two different procedures to create AWs? I have no idea but I am sure it seemed like a good idea at the time.

Now we have an empty AW, the next step is to start adding objects, possibly some data as well, to the AW. There are two options:

  • Import objects and data from an existing EIF file
  • Import objects based on existing XML documents
Part 2a - Importing an existing EIF file

If you already have an EIF file from an existing AW, the next step is import that file into your new AW. Ideally, the EIF file will contain all the required standard form metadata. If this is not the case and the EIF file is from an older version of Oracle OLAP, or Oracle Express, then you will need to have a different approach. Another blog entry will deal with the upgrade process.

To run the import process you need to place the EIF file in a directory on the file system then make a directory within the database to point to that filestore. We can use OLAP DML commands wrapped within the DBMS_AW.EXECUTE procedure to import the EIF file from the command line. Personally I prefer to make two passes through the EIF file, firstly to create the objects and then to load the data. This goes back to the good old days of Express and ensured efficient storage within the database files. Not sure if this still applies to 10g OLAP schemas, but as old habits die hard, here is the two pass process:

SQL>execute dbms_aw.execute ('aw attach name_of_analytic_workspace');

SQL>execute dbms_aw.execute ('cda scripts');

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' dfns');

SQL>execute dbms_aw.execute ('update');

SQL>execute dbms_aw.execute ('commit');

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data');

SQL>execute dbms_aw.execute ('update');

SQL>execute dbms_aw.execute ('commit');



The assumption here is the EIF file being used contains objects defined in standard form. If this is not the case then you will need to run a conversion process to generate standard form metadata.

Some warning signs need to set here : The import process above is going to load data into the analytic workspace with no commit point until the very end. Therefore, you need to make sure you have plenty of storage space within your TEMP tablespace, otherwise it is likely the import will fail with one of a number of error messages relating to memory and/or temp space issues. It is possible to control the update process by using the UPDATE keyword. For example:

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data UPDATE');


Will force an update after each object has been loaded. This is fine until you hit a very large cube when you may need slight more detailed control over the update process. The EIFUPDBYTES option lets you define the volume of data to read between updates. The following command will update after each block of 500,000 bytes has been loaded.

SQL>execute dbms_aw.execute ('EIFUPDBYTES = 500000’

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data UPDATE');



Obviously, by executing continual updates during the import the whole process will take much longer to complete than a single read with a final commit point at the end. But in the end “you pays your money and takes your choice” as they say.

One major problem with EIF imports is that most people create a single EIF file, which creates problems if you need to recover from a failed import process. Why? Because you have to start right at the beginning and import the whole thing all over again. Or you would think! The EIF file is in fact quite smart in terms of its contents. It is possible to get a list from the file of the actual contents so in reality it is possible to recover from a failed import process by checking what was imported and now resident in the AW and what is left to import from the EIF file. Requires a bit of OLAP DML skill…something for another blog posting perhaps?

Part 2b - Importing an existing XML definition

Each of the create procedures described above generates a completely blank AW. There is no standard form metadata contained within the resulting AW. Before you can import an XML template into the AW, standard form metadata needs to be added. The only way to do this seemed to be to use AWM (or right your own Java procedure to call the AWXML API). After a bit of digging I did discover the following an undocumented features called create_db_stdform. This is referenced in older versions of the OLAP documentation set and the syntax is as follows:
  • call create_db_stdform('my_aw_name','rw')


There is more information on Mark Rittman’s excellent blog website, where one of his posts covers migrating from previous versions of Express to 9i OLAP. The full article is on Mark’s website at:

http://www.rittmanmead.com/2003/10/30/creating-database-standard-form-analytic-workspaces/

To execute this command we can use the normal process as follows:

SQL>execute dbms_aw.execute ('call create_db_stdform(''global_aw'' ''rw'');


Now the AW will appear in AWM under the Model View and it is now possible to start creating dimensions and cubes. However, the purpose of this blog is to explain how not to use the GUI!
The next stage is to use existing XML template documents to define the objects within the AW. As far as I can tell there are no command line tools to create an AW XML definition for an existing object in an existing AW. The only way to do this is to use the Analytic Workspace Manager and the right mouse click menu options, or use the Generate feature in Warehouse Builder (Unless someone knows how to do this from the command line?).

So, assuming you have all the required XML files to create the dimensions and cubes for your new AW, the process for importing these files is very simple. There are to procedures you can use:

  • EXECUTEFILE
    • creates all or part of a standard form analytic workspace from an XML document stored in a text file.
  • EXECUTE
    • creates all or part of a standard form analytic workspace from an XML document stored in a CLOB.
The EXECUTEFILE function is the easier to use of the two functions. Assuming you have generated an XML definition of your AW you will also need to define a directory within the database that points to the location of the XML file(s). In the example below the directory SCRIPTS was created and the definition for AW is contained within the file GLOBAL.XML and the whole command is wrapped by the DBMS_OUTPUT.PUT_LINE function so that the "Success" message returned by EXECUTEFILE is displayed:

SQL>execute dbms_output.put_line(dbms_aw_xml.executefile('SCRIPTS', 'GLOBAL.XML'));Success


The EXECUTE approach is slight more complicated but achieves exactly the same result. So why do we have two ways of achieving the same thing? Who knows, as I said before I am sure there is a very good reason?

--Use DBMS_LOB package to create a clob

DECLARE

clb CLOB;

infile BFILE;

dname varchar2(500);

BEGIN


-- Create a temporary clob

DBMS_LOB.CREATETEMPORARY(clb, TRUE,10);


-- Create a BFILE use BFILENAME function

-- Use file GLOBAL.XML in the SCRIPTS directory object.

infile := BFILENAME('SCRIPTS', 'GLOBAL.XML');


-- Open the BFILE

DBMS_LOB.fileopen(infile, dbms_lob.file_readonly);


-- Load temporary clob from the BFILE

DBMS_LOB.LOADFROMFILE(clb,infile,DBMS_LOB.LOBMAXSIZE, 1, 1);


-- Close the BFILE

DBMS_LOB.fileclose(infile);


-- Create the GLOBAL analytic workspace

DBMS_OUTPUT.PUT_LINE(DBMS_AW_XML.execute(clb));

DBMS_AW.AW_UPDATE;

COMMIT;


-- Free the Temporary Clob

DBMS_LOB.FREETEMPORARY(clb);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/




and there it is. A new standard form analytic workspace created and populated with base objects (dimensions and cubes) without using a GUI. In the next blog posting I will explain how to maintain cubes and dimensions without using the GUI.

Hyperion products now available for download

Firstly, if you want to try out the Hyperion BI products, you can do so by going to http://edelivery.oracle.com/ and select "Hyperion Performance Management and BI" from the "Product Pack" dropdown. This then results in the following products that are available to download:

  1. Hyperion Performance Management and BI (9.3.x)
  2. Hyperion Performance Management and BI (9.2.x)
  3. Hyperion Enterprise Release (6.4.1)
  4. Hyperion Business Modeling (4.0.5)
  5. Hyperion Application Link (9.x) Media Pack





You can download individual product components where available, a license key, and also the product documentation - which is a good place to begin.

Secondly, to view a brief description of what these products are, at least at the 'platform' level, go to this page on OTN: Oracle Business Intelligence Platform Home. You shall find the following four Hyperion products listed as part of "Oracle Business Intelligence Enterprise Edition Plus"
  1. Interactive Reporting : Intuitive and highly interactive ad-hoc reporting
  2. SQR Production Reporting : High volume, presentation-quality formatted report generation
  3. Hyperion Financial Reporting : Formatted, book-quality financial and management reporting
  4. Hyperion Web Analysis : Web-based online analytical processing (OLAP) analysis, presentation, and reporting
Thirdly, the Hyperion products are now also listed on the Oracle price-list. The BI Suite EE has been renamed to BI Suite EE Plus (yeah... three cheers for branding).

Wow... three factoids in one post. I must be on a roll here...

How to limit the disk cache used by the BI Server

This is from a question asked on our internal mailing lists... (yes, I cannot seem to find the time to write new, original posts)

"Is there any way to limit the amount of disk space taken up by data stored on the BI Server for aggregations, calcuations, etc?"

The shor answer is "yes".
NQSConfig.ini (under C:\OracleBI\server\Config) has a section named [ CACHE ] for this purpose.

It reads something like this:

###########################################################
#
# Query Result Cache Section
#
###########################################################

[ CACHE ]

ENABLE = YES;
// A comma separated list of
pair(s)
// e.g. DATA_STORAGE_PATHS = "d:\OracleBIData\nQSCache" 500 MB;
DATA_STORAGE_PATHS = "C:\OracleBIData\cache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 100000; // 0 is unlimited size
MAX_CACHE_ENTRY_SIZE = 1 MB;
MAX_CACHE_ENTRIES = 1000;
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;


For example, this is how my cache folder looks like:

And to get more information on what these settings mean and do, you can see pages 229-240 of the "Oracle® Business Intelligence Server Administration Guide, Version 10.1.3.2, December 2006, (Part No B31770-01)" for a detailed explanation of what the different settings mean, and a brief exposition of the caching strategies.

Thanks to Krishnan Viswanathan from BI product management for providing the answer (yes... I am rendering credit unto others - wouldn't be nice on my part to steal and scoot -
चोरी और सीना जोरी "chori aur seena jori" is not something I subscribe to).

Petrol and OLAP

According to this NYT article (Big Rise Seen in Demand for Energy - New York Times), "It started with a simple question by Samuel W. Bodman, the energy secretary: What does the future hold for supplies of oil and natural gas?".

In order to answer this question, the National Petroleum Council developed an analytic data warehouse using Oracle relational and OLAP technology. Implemented by Vlamis Consulting, the NPC report states:
The data warehouse was designed to be the main analytical tool for the Task Groups, accepting all data collected from the survey questionnaire and other data sources. As the survey data were multi-dimensional, Oracle OLAP database technology was used and the collection was organized using 7 dimensions:
1. Time (year)
2. Geography (country or geographic region)
3. Energy type (e.g., Oil, Gas, Coal, Nuclear, Renewable)
4. Energy sector (e.g., Commercial, Residential)
5. Case type (e.g., Business as usual, Alternative energy policy)
6. Units (applicable unit of measure)
7. Source (e.g., Public, Proprietary)
The statistics contained in the report posed interesting technical challenges, including non-additive data, skip-level hierarchies, non-standard aggregation rules, and more - all of which Oracle OLAP is designed to manage. Discoverer OLAP was used to analyze the data.
You can find more on the Vlamis blog here (Vlamis Software » Blog Archive » Vlamis Used Oracle OLAP For National Petroleum Council Study)

Thanks to Marty Gubar from OLAP Product Management for passing this on.

Reply to a comment - Hyperion licenses

This is a post in response to a comment to a post (Hyperion products now available for download)
I have reproduced the entire comment here for convenience:

Anonymous said...

I noticed that on Oracle technology network, a developer can download the software with Development License.
******
Is there a place where a developer can download the Hyperion products with "Development License" to evaluate/learn the products or even prepare for certification?
*******
The reason I am asking is that the 30-day trial period on e-delivery website is barely enough for something as complex as Hyperion products. It takes a while to install/configure them!! Most existing Hyperion customers have only a few products licensed and in the past, Hyperion made it very difficult for developers of those customers to get a trial license (you had to go through sales team to get trial software who wouldn't help you unless they saw immediate money). If there was a development license for Hyperion products, I am sure many of the developers of existing customers would try out some of the remaining products that they don't currently have license for and recommend them to the business users. It would be fine if the development license terms restricted use by the individual developer for non-production use Only (there are paid Hyperion developer tools such as Dashboard Architect, studio, etc. that must still be paid for if used in production environment and any development done with Development License can not be installed in production unless a paid license has also been acquired).

Note: This individual developer "development license" would be different from the "paid development environment" license which is where the development team does the development before installing it to the "paid production environment".
(hopefully it is not too confusing)
The short answer to this question is that once software is available from OTN for download, there **should** not be any license requirement.

Oracle software - all of it - comes without any timed licenses, or trial licenses that lock you out of the product or restrict you to limited functionality after x number of days. If you take the case of Siebel Business Analytics, which became Oracle Business Intelligence Suite Enterprise Edition (with some products like Oracle BI Publisher joining this suite, which in turn came from the Oracle Applications side of the company and went by the very exciting name of XML Publisher), it also had a license key that was required to install and use the different products therein, like Answers, Dashboards, Disconnected, etc... It took some time to take out the license key requirements from the product, and when Oracle released Oracle BI EE 10.1.3.2 (aka code name of 'Maui'), it had no license key restrictions. BI EE 10.1.3.2.1 does not have a license key, nor will 10.1.3.3.0 (due very soon), or other releases.

I totally agree that a 30 day license is probably not enough to install, use, and learn the product family - I would prefer 90 days, and Hyperion does have many products that do not exactly fall in the 'Notepad' variety of easy-to-install-and-learn products (Planning, budgeting, financial performance management, etc...).

At this point I don't really have an answer - what you could try is to download and install the products with the 30 day license, and after the 30 day period try and obtain a new 30 day license file and see if that works.

Note that please do not take this as 'legal' advice from Oracle. This is my personal 2 cents (or चार आने char anne in my lingo).

11g OLAP – Hot Pluggable Performance Booster…

Over the last few weeks there have been a lot of headlines, articles and blog posts regarding the 11g release of the database. From the point of BI, this release does have a lot to offer. Most importantly (from my point of view) the multi-dimensional model will finally move beyond the current use cases and evolve into a hot a pluggable performance booster. Here is a direct quote from Charles Rozwat, Oracle executive VP for server technologies:

Oracle's new OLAP features take business intelligence 'from a specialized niche into a much broader market.'

"As data changes in the base data tables, those changes are automatically refreshed to the materialized views stored in the cubes. The bottom line is more automation and a better ability to handle large amounts of data and complex queries with less of an impact on performance."

Andy Mendelson has also been widely quoted regarding his comments on 11g OLAP:

"The big breakthrough here is that we're able to use the OLAP cubes as a transparent performance accelerator inside the database. The users are still happily using their SQL applications, and they won't even know they're using OLAP. We're moving OLAP from a specialized market to a much broader market among all our SQL users."

The end result is a hot pluggable peformance layer than will help improve response times for all SQL reporting products (Discoverer, BI EE, Business Objects, Cognos etc).

I am glad to see that Googling the term “11g OLAP” provides a number of links analysing 11g OLAP . The first link, at the moment anyway, is to Intelligent Enterprise and their posting provides a non-technical overview of the launch. From this site you jump directly to the second link: the 11g OLAP datasheet on OTN. The datasheet can be downloaded from the OLAP home page on OTN, or you can access it directly via this link here.

The real analysis starts with the next two links from Vlamis and Mark Rittman. Rather than repeat everything here I going to link everyone over to these two postings as together they provide the best analysis of what you can expect from 11g OLAP:

The Vlamis blog, provides a lot more detail on the launch and 11g Analytic Workspace Manager. Chris and Dan have been heavily involved with the beta program and their 11g OLAP posting contains links to the actual 11g launch webcast and links to a powerpoint presentation with screenshots of key 11g Analytic Workspace Manager features. You can get to their web site via this link which will take you directly to the posting on 11g OLAP: http://www.vlamis.com/blog.php/?p=27

Mark's posting looks more at the SQL access layer and the new SQL CUBE_TABLE function as well as reviewing more general features of 11g OLAP. You can access Mark's review at:

http://www.rittmanmead.com/2007/05/07/oracle-olap-11g-news-and-the-vlamis-blog/

For those of you wanting to try 11g OLAP the Linux version will be out in August so watch out for the announcement on OTN.

Go vote!

I have added a poll page element on the Oracle BI Blog template. This is a new page element that Blogger provided, and I wanted to sort of kick the tyres around. So, please go to the blog and vote.

Edits: corrected a typo. And added a screenshot - within minutes of posting this post and adding the poll page element my blog showed up with an error message for the poll! It's up and working now; anyway, signing off for the weekend.

Russian BI Blog

Doing some clickstream analysis on the BI Blog, I saw some hits coming this way from a site http://oraclebi.ru/

Check the blog out; and in case you don't know Russian, you can try the Google translated site here. It lists Mark Rittman's blog and mine among others.

Post Edit: I got an email from the blog author, Andrey Pivovarov, who is a sales consultant with Oracle, based out of Moscow.

Oracle Extends Its Lead in the DW Platform Tools Market

More excellent news for the database team, following hot on the heels of the extremely positive coverage of the 11g database, a new analyst report from IDC has identified Oracle as the leader, by a very big margin, in data warehouse platform tools market. The full story is on the Oracle.com website and can be found here.

This market segment comprises both DW generation and DW management tools. According to IDC, Oracle has over 40% of the market which translated into $1.8 billion in revenue for 2006. So which products were included as part of the platform in this survey? The same group of products that featured heavily in the 11g press releases via comments from Andy Mendelsohn and Charles Rozwat:

  • Warehouse Builder
  • Partitioning
  • OLAP
  • Data Mining
all of these products are key to growing Oracle's dominance of the data warehouse market, and from the information released as part of the 11g presentations (see previous posting) these products are adding significant new features that are going to make building and managing an Oracle data warehouse even easier.

Interestingly, IDC sees Oracle as being in an excellent position to exploit new areas such as spatial analytics. In fact, Warehouse Builder 10gR2 can already "spatially enable" an existing schema so there is nothing stopping customers from using spatial features today. The front page of OTN has a link to an article on integrating spatial with Google map:

http://www.oracle.com/technology/pub/articles/lokitz-spatial-geoserver.html

The only problem now is : where to send everyone to get more information? Personally, I always use the Yahoo websites for this type of information so here is the link :

http://biz.yahoo.com/prnews/070806/aqm088.html?.v=14

Alternatively try typing "Oracle Named Worldwide Market Share Leader in Data Warehouse Platform Tools" into Google and you should get over 150,00 hits.

The BI - EPM Blog

You may have seen the blog comment, if you haven't, here's the really good piece of news: there is a new BI - EPM blog available now at http://blogs.oracle.com/frankbuytendijk/. Here's an introduction from the blog:

Frank Buytendijk, Vice President of Corporate Strategy for Oracle|Hyperion and previously a Gartner Research Vice President, is one of world's most recognized thought leaders on business intelligence (BI) and enterprise performance management (EPM). As a researcher, presenter, and business strategy consultant for Oracle|Hyperion's customers, Buytendijk's style is best described as provocative, out-of-the-box, and future-oriented, while adding a human touch. Based in The Netherlands, Buytendijk brings 20 years of experience in BI & EPM.

You can subscribe to the blog feed here.

What no GUI? How to Load AW XML Files

This part of a series of postings prompted by many questions I received about how to manage an analytic workspace without using a GUI (AWM or OWB) . In this post I want to provide some more information on using XML templates. Most of this information came from Marty Gubar, OLAP Product Manager, who is an expert in using AW XML.

Personally, I am still not sure how you would create an XML file for an AW without using Analytic Workspace Manager, however, based on recent posts on the OLAP forum and from questions posted to our internal help mail list, it is obvious there are people actually designing and building AWs without ever using AWM. So I going to make the assumption that you have somehow managed to created your XML file that describes a dimension or a cube, or any other supported object, and question now is how do you load that file into the AW?

The first thing you need to do is to find this extremely useful jar file, called ImportXML.jar file. For the moment you will have to email me (keith.laker@oracle.com) to get a copy of this file, but hopefully the product management team will get it hosted on the OLAP OTN home page very shortly. Once you have this jar file you can create a script file as follows:

java.exe -classpath .\ImportXML.jar samples.awxml.ImportXML %1 %2 %3 %4 %5 %6 %7 %8 %9

The script takes the following arguments

  • id/pwd ID and Password
  • -con [host:port:sid] Connect string (e.g. localhost:1521:orcl)
  • -aw [TargetAW] AW that is the target for the XML input (required when XML does not include a complete AW)
  • - file [file] Name of XML file

Examples:
1. Importing an object (e.g. a calculation) into the GLOBAL analytic workspace:
  • import scott/tiger -con localhost:1521:orcl -aw global -file my_model.xml

2. Importing an entire analytic workspace:
  • import scott/tiger -con localhost:1521:orcl -file global_aw.xml

The above script can be used with the XML template files generated by Analytic Workspace Manager as well. Alternatively you can manually create your own (as some of you are quite clearly doing!). If you are manually creating these files, or just want to modify an existing file to make some changes hopefully the following overviews will help:

Structure of an XML File: Each XML file is broken down into four basic blocks, which relate to the type of work being performed.
  • Step 1 is to attach the required AW via an “Attach Id” tag.
  • Step 2 is to process the object identified within the file
  • Step 3 is to commit all changes
  • Step 4 is detach the AW
There is a quick an easy way to understand what is required and how these files are processed. If you use Warehouse Builder, it is possible to use Control Center to generate deployment scripts, which you can then review. For example I used OWB to generate a a CREATE dimension and a DROP dimension script so I could understand the steps involved and review the code it generated. There are a number basic tags that you need to use
  • Attach
  • Commit
  • Delete
  • Create
  • Delete
  • Alter
  • ActiveObject
  • Id
Hopefully, the following sections will help to explain how to use these various tags:

Attaching an AW
The process for attaching is very simple. There are two ways to do this, you either:
  • pass a parameter into your script file via the command line using -AW aw_name
  • code the AW and schema name into the XML file
I think the first is probably the best idea because it makes it easier to move XML and import files into different database instances. But if you want to hard code this information into your XML file then you need provide the following code:



(I am hoping you can read this code!). The process is quite straightforward. The first four lines are the normal XML header information, after that comes the following:
  1. Attach tag with an Id tag that contains an the word Action with a number, like this "Action1" . This controls the order of processing and in an normal XML file you will see this repeated with numbers increasing sequentially through the file
  2. ActiveObject tag starts the block to identify what the file is going to process which is determined by the next line
  3. In this case the attach needs to process an AW so an AW tag is used to identify the specific AW to be attached
  4. The last three rows contain the termination tags for rows 1,2 and 3.
It is possible to define an attach mode within this block as well, allowing the AW to only be attached in read-only mode which allows AWs to be shared across multiple projects and allows an AW to be attached by multiple processes at the same time.

Committing Changes within an AW
The process for committing is the same as the attach process except the tag Commit is used. Because during a session we could in theory have mulitple AWs attached the commit block still requires an ActiveObject block to identify the AW to be updated. This allows to not update other AWs as required.




Detaching an AW
The process for detaching is also the same as the attach process except the tag Detach is used. Because during a session we could in theory have mulitple AWs attached the detach block still requires an ActiveObject block to identify the AW to detach.



Building a CREATE Script
Create scripts are the output from Analytic Workspace Manager. After the main header block you will find a Create tag with an associated Id that defines the action reference for the create statement. If you are using the Attach tag as well, then the number associated with "Action" for the Create block must be greater than that used in the Attach block. It all sounds very complicated but when you see the code it is very simple.

The Create block identifies the create process in full. For a dimension that full process is:
  1. Dimension name
  2. Dimension labels
  3. Dimension attributes
  4. Dimension levels
  5. Dimension hierarchies
  6. Dimension mappings
A lot of information needs to be provided in the XML file, which is why I prefer to use Analytic Workspace Manager to generate these!

Building a DELETE Script
Performing a Delete operation in Analytic Workspace Manager is very simple - highlight the object and hit the delete key. Assuming there are no supporting objects that prevent the delete process from running the object will be deleted - for example you cannot delete a dimension while it is still associated with a cube. However, outside of Analytic Workspace Manager the XML file does provide a Delete tag to allow for command line deletion of objects. The amount of information you need to supply to delete an object is minimal. For a dimension it is simply the lines containing the Dimension tag and the end-Dimension tag. The delete process will remove all the objects with dimension automatically.








Building an ALTER Script
It is possible to update certain parts of an AW using this method. To be honest, I have not tried everything. According to the Java documentation this method allows you to update any of the text attributes, such as:
  • Long descriptive label
  • Short descriptive label
  • Plural descriptive label
So you could change a dimensions description from "Products" to "Company Products" for example. For more heavy duty alterations, such as modifying a hierarchy by changing the levels within the hierarchy I think you would need to delete the hierarchy first and then rebuild it. It may depend on the types of changes you want to make, however, the API documentation seems to indicate this is a heavily restricted tag. But enjoy!

Oracle Database 11g on Linux now available

For those keeping track, Oracle Database 11g Release 1 (11.1.0.6.0) - Standard Edition, Standard Edition One, and Enterprise Edition - became avialable for download from OTN. You can download it from this link - a 1.7GB single zip file. Also see Keith's previous post - 11g OLAP – Hot Pluggable Performance Booster… and Oracle Extends Its Lead in the DW Platform Tools M...

10.1.3.3 is now available

BI EE 10.1.3.3.0 is now available for download from OTN, and should also become available on eDelivery very soon. Also releasing with BI EE 10.1.3.3.0 is the standalone version of BI Publisher 10.1.3.3.0 as well as BI Applications version 7.9.3 ("Oracle Business Intelligence Applications").

See my earlier post - Go vote! - now one can quibble whether BI EE 10.1.3.3.0 did in fact release before Aug 14 or not, because it did go RTM on Aug 14, but it takes a few days for it become available on OTN for download. But anyway...

Oracle Business Intelligence Enterprise Edition 10.1.3.3.0 is the next (semi) major release for BI EE after the release of BI EE 10.1.3.2 that happened in January this year (see posts on 10.1.3.2). BI EE 10.1.3.2.1 released in April (see posts on 10.1.3.2.1), but it was a point release with support for some additional platforms , languages, and some targeted bug fixes. BI Standard Edition One was the other big news in the world of Oracle BI (see posts on BI SE1). While this - 10.1.3.3.0 - is not as big a new release as 10.1.3.2 was, which had more than 200 new features, 10.1.3.3 is still a big enough release to warrant a celebration (or not... depending on which side of the competitive fence you are on). In brief, some of the new features in 10.1.3.3.0 are (and I am reproducing verbatim from the new features doc):

  • "Oracle BI Answers-Based Metadata Dictionary Feature"
  • "Multi-Select Dashboard Prompts Feature"
  • "Office Integration with BI Dashboard Feature"
  • "Dashboard Link Types Feature"
  • "Import Oracle Catalog Feature"
  • "Embedded Database Functions Feature"
  • "Drag and Drop XMLA Metadata Objects Feature"
  • "Oracle BI Publisher Template Builder Enhancements"
  • "Flash Templates for Oracle BI Publisher"
  • "Oracle BI Publisher PowerPoint Output"
  • "Oracle BI Publisher Integration with Oracle BI Discoverer"
  • "Performance Improvements for Oracle BI Publisher"
  • "Converting Reports from Oracle Reports to Oracle BI Publisher"
  • "Support for Digital Signature in PDF Documents"
  • "Support for Postscript Printers"
  • "Installing Oracle Business Intelligence for Microsoft Office"
  • "Microsoft Office Support"
In particular, I would like to call out attention to these features:
  • Microsoft Office Integration: there are snazzy new Excel and PowerPoint Add-ins that access BI Presentation Server based requests, there is the ability to copy and paste BI views from Answers or Dashboards into Office documents, the ability to generate PowerPoint output from Answers views, and support for PowerPoint (.ppt) as an output format for BI Publisher.
  • Integration between BI Publisher and Discoverer: which I think every Discoverer customer shall be delighted with, as it brings to Discoverer customers the ability to create hi-fidelity layouts from your existing Discoverer worksheets (relational as well as OLAP), and lots more.
  • The ability to create interactive, nice looking, offline capable Flash dashboards using the Flex builder, and populate them with data supplied by BI Publisher. Flash therefore becomes an output format supported by BI Publisher, with all the attendant benefits.
  • For those more interested in the server side of things, one feature that you will like is the ability to drag and drop XMLA metadata objects in the Admin Tool.
Some useful links:
  • Download the software - home page (the page URL actually says 101320bi :-)
  • Download BI EE 10.1.3.3 for Windows, Linux (1.3 GB downloads, and you also get downloads for Solaris - SPARC, HP - Itanium and PA-RISC, and IBM - AIX5L)
  • Download BI Publisher 10.1.3.3 (Standalone version) for Windows, Linux (the download sizes vary from 700MB - 950 MB depending on the platform you choose to download for).
  • Oracle Business Intelligence Enterprise Edition Documentation - home page
  • Download the doc library for BI EE 10.1.3.3 (19 MB Zip file)
  • View the doc library for BI EE 10.1.3.3 (HTML Welcome page)
  • New Features Guide (HTML or download the PDF)
  • System Requirements and Supported Platforms (PDF)

BI EE Upgrade Survey

This is a quick survey to help us get actual feedback on Upgrade data and acceptable timeframes.

Data to Be Upgraded

1. What is the size of BI EE/BI Apps System to be upgraded

0 to 50GB
50 to 250GB
250 to 750GB
Greater than 750GB

Upgrade with Data

2. How much time is acceptable for a upgrade to take (including data)

0 to 2 hrs
2 to 4 hrs
4 to 8 hrs
Greater than 8 hrs

New poll and results from last poll

I have created a new poll on the BI blog - "What do you think is or are the most important features of BI EE 10.1.3.3?" It's going to be open till the end of next month. Now that BI EE 10.1.3.3 is now available, I wanted to get your feedback on what you think are the most important features to you in this release. You can go to the blog to vote, or you can also use this link to cast your vote.

And here are the results from the last poll - "When will BI EE 10.1.3.3 release?"

More than a third of you thought it would release in Sep, before Sep 15. Less than 1 in 8 thought it would release in the first half of August. Well

BI Publisher Discoverer integration

I shall talk a bit about the benefits of the integration between BI Publisher and Discoverer in the 10.1.3.3 release, and how that shall be of interest to people who want to create reports with more adhoc layouts than are possible with Discoverer, among other benefits.

The first: Discoverer
Discoverer is a BI tool used for adhoc querying, analysis, and reporting of data. It can report against relational as well as Oracle OLAP data sources. It has tens of thousands of customers, and it’s a fine product thank you. You can read more about Discoverer on this blog as well as on OTN.
To begin to cut to the chase, there are two primary enhancements that Discoverer customers have been asking for some time (there are more, but I am picking these two):
1. The limited flexibility when it comes to laying out your Discoverer worksheets. You get a title view, a text area view (basically a title view but at the bottom), a graph, and one table or one pivot table (crosstab). And you can position the graph above, below, to the left, or to the right of the table component.

or

or


or

Now, for adhoc analysis and for many other purposes these layouts are adequate. But sometimes people have felt the need to lay out these views in a more flexible manner.

2. You can schedule a Discoverer worksheet (or worksheets). You can view the results of these scheduled worksheets. You can export a scheduled worksheet. You can email a scheduled worksheet (using Discoverer Viewer). What you cannot do is do all of this in a non-interactive manner. i.e. you cannot tell Discoverer to run a worksheet at a specified date and time, export the results into PDF or XLS, etc..., and then email these results via email to designated people.


The Discoverer Scheduling Manager.


My scheduled Discoverer worksheet in Discoverer Viewer.


This is the scheduled Discoverer worksheet in Viewer.

And this is the email page that I can use to email the scheduled Discoverer worksheet to multiple users, as an attachment (XLS, PDF, HTML, etc...)

The second: BI Publisher (formerly known as XML Publisher) BI Publisher is the enterprise reporting and document creation product from Oracle, and now also part of BI EE. Apart from the millions of uses of BI Publisher, the ones that are pertinent to this post are the ability of BI Publisher to let users create highly formatted reports, schedule them, and have them delivered to a variety of destinations, including email, printer, FTP, WebDAV, etc...

The third: Better Together - what happens when you bring Discoverer and BI Publisher together? No... not a third product, but syncretism! In BI EE 10.1.3.2 BI Publisher was enhanced to let it access Answers based data sources to allow users to create pixel-perfect reports. In BI Publisher 10.1.3.3 this functionality has been logically extended to allow Discoverer data sources – specifically worksheets – to act as data sources to BI Publisher. For this purpose, there were enhancements in both Discoverer and BI Publisher. SOAP web services have been added to Discoverer, for the first time, to expose some functionality of Discoverer that can then be called by programs using these web services. BI Publisher was enhanced to let it recognize and handle Discoverer as a data source.

Therefore, now you can take Discoverer worksheets like this:


And build nice looking reports like this:





- all using the wonder of BI Publisher.
These reports can also be scheduled to run and have the content distributed to users via email (other supported destinations include FTP, WebDAV, Printer, etc...).

And you can use the BI Publisher Template Builder for Microsoft Word to format and layout these reports:


That's the real quick introduction to Discoverer and BI Publisher integration.

As an aside, I always find it a bit curious and sort-of-interesting when I see a press release or news story describing a new release as having ‘radically improved functionality’, ‘path-breaking’, and simply so great that you have to upgrade. Of course, every release represents improvement over the previous one, or at least it should (otherwise the engineers, product managers, and QA engineers failed to do their jobs and should be fired -sometimes they get promoted, but that’s another story), so in that sense it is reasonable to expect and reasonable to state that the new release is better than the previous one.
What you really don't want to do is end up in this sort of a situation:


This post attempts to articulate via textual and digital visual aids (aka screenshots) some of the benefits of the loosely coupled integration between BI Publisher and Discoverer that may accrue to users who have a requirement to develop reports with a degree of positional flexibility as it relates to the layout of components usually and typically used for rendering aggregated data in a manner so as to enable and facilitate distribution of the said data thus visualized such that it may be construed to confer functionality greater than that provided by Oracle Discoverer, albeit in a specific context.

Yes – I did that without even using the 'bullshit generator'!

Update: deleted a sentence, and moved the first paragraph from the original post to the bottom.

BI Office in 10.1.3.3

One of the two biggest features in the 10.1.3.3 release is the Office Add-in - properly known as 'Oracle Business Intelligence for Microsoft Office' (see other posts on 10.1.3.3)

You can go to OTN to download the software (see this post for download links).
This is the New Features doc, which has a chapter on the Office Add-in.
And once you install the client add-ins you can view the online help too - these are available as CHM files from the add-in's help menu.

So what's new in the new Add-in (is that a tautology)?
I can rattle off many, many features in BI Office, but for my money I would pick these four:

  • A revamped Excel Add-in and a new PowerPoint Add-in.
  • Ability to insert BI charts as refreshable images or even Flash objects.
  • A really, really, nice feature that we call the BI Clipboard.
  • Ability to drilling in Excel tables in offline mode.
While I shall get into some details on the add-in in future posts, I wanted to show some screenshots of how your presentations and spreadsheets can look like with data inserted from this BI Add-in. Other features such as the BI clipboard, support for levels, prompts, etc... I shall cover in subsequent posts.

Consider these screenshots of PowerPoint with charts inserted using the BI Office Add-in.
The first screenshot shows you six slides, all charts.

The second screenshot above, on the right, shows the same presentation secured using the 'Secure Oracle BI Data' button.

Here below is a similar screenshot of the BI Office Add-in with Microsoft Excel 2007. I have multiple worksheets in the workbook, each has a combination of tables, native Office charts, BI charts inserted as images. You can notice that some of the charts are the same as the ones I used in the presentation screenshots above. Of course - BI charts are available in both Excel and PowerPoint. Also visible is the BI Presentation catalog that you use to browse your requests and insert them into your spreadsheets.



The other nice feature is the Secure feature. One-click. Again. One click is what it takes to secure an Excel sheet. Tables, flat data views, charts, images, all. One click is what it takes to secure an entire PowerPoint presentation - tables, charts, images, even Flash objects. And one-click to refresh. Take a look:
This below is a worksheet from the same Excel file that I showed above.


With a single click (well... you could argue that buttons are meant to be clicked once, but I have had the misery of knowing and working for people who would double-click twice on buttons as well as hyperlinks, and they have done quite well in life too, so maybe that's what I have been doing wrong all these years: not double-clicking on links and buttons) I can secure all the data in the worksheet.

And with a single click again, I can choose to refresh the worksheet or the entire workbook.


And with that ends the first episode.
I shall be back (or does it sound more convincing were I to say "I'll be back"?)