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).

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.

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 !

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.

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.

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)

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?)
  

How to Hide Measures for Discoverer OLAP and Excel Addin Users

There has been an on-going thread on the OLAP OTN forum regarding the process of hiding measures within both Discoverer for OLAP and the Excel Addin. As I have now managed to implement this feature, thanks to contributions from Mark O’Donnell and many others, I thought it would make for an interesting post.

Within an AW is possible to scope down the list of available dimension members using the PERMIT_READ command (another post on this subject later) and this process is quite well documented. However, removing measures has (at least for me) been a more difficult problem to resolve. When I developed the latest BI10g common schema sample analytic workspace I expanded the number of measures available to over a hundred measures. Compared to the original 9i schema the latest version has a lot of new features. However, the Query Builder measure panel now looks like this:




As the measures are split by functional area (customer analytics, sales analytics and brand analytics) it makes sense to have three different users and scope down the list of available measures accordingly.

To create the environment to manage the scoping process I added a new user called SH_SCOPE to my instance and created a table within this schema call SH_AW_SECURITY:

CREATE TABLE "SH_SCOPE"."SH_AW_SECURITY"
("USERID" VARCHAR2(50 BYTE)
,"MEASURE_NAME" VARCHAR2(250 BYTE));


The table contains the list of measures a user is able to access. To populate the table for the user SH_OLAP to only view he sales analytics cube I inserted the following rows into the SH_AW_SECURITY table viaSQL Plus.

insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.REVENUE.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.COSTS.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.QUANTITY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_PC_PY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_PC_YA.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PROFIT.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.MARGIN.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_PC_PY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSPC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSPP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GST.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSTC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSTP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSPC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSPP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PST.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSTC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSTP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SCTREND.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SPTREND.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SRTREND.MEASURE');

Each user will next SELECT priviledges on this table as an OLAP DML program will be used to query the contents of the table as will be shown later.

The next step is to add a procedure to the D4OSYS schema that will be called each time a tool connects to a schema via OLAPI connection. The procedure is called D4O_AUTOGO and can be relativelty simple:

create or replace PROCEDURE D4O_AUTOGO (user_id IN VARCHAR) authid current_user
is
BEGIN dbms_aw.aw_attach(‘SH_OLAP.SH_AW’, false, false, 'FIRST');
END;

In this case as I know the only AW this user will access is called SH_AW, I can hard code the reference in to the procedure. Alternatively, the procedure could be made more generic by using a cursor to return a list of all available AWs and then attach each one in turn, with each AW being scoped according to its own PERMIT_READ command (more on this later):

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.

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);

For more information on this procedure please refer to OLAP 10g Reference manual.

Please note I am not a PL/SQL expert so I am sure this code could be optimised further. It is important to test this procedure to make sure it works as expected before moving on to the next series of steps. This procedure will get called every time a tool makes a connection to a schema via the OLAPI. This forces each AW to be attached in read-only mode.

The next step is to ensure each user has execute rights to D4O_AUTOGO, so from the D4OSYS account simply GRANT EXECUTE rights to SH_OLAP.

The last step is to create an OLAP DML program called PERMIT_READ that will scope down the list of available measures when the AW is attached in read-only mode. This is an important point – the program should not execute when the AW is attached in read-write mode as this will affect the metadata used by Analytic Worksapce Manager and Warehouse Builder. If the code is executed with the AW is attached in read-write mode it is likely that both AWM and OWB will fail to work correctly with the AW.

Using Analytic Workspace Manager you need to attach to the required workspace in read-write mode. Then open the OLAP Worksheet and define a new program called PERMIT_READ:

DEFINE PERMIT_READ PROGRAM BOOLEAN

Permit read needs to return a boolean answer of ‘Yes’ to allow the attach process of the AW to complete successfully. Personally, I prefer to call a program from PERMIT_READ rather than coding directly within this program. Therefore, create another program called SET_MEASURES to manage the code to hide the measures. The code can either directly list the measures to be excluded within a SWITCH/CASE or IF THEN/ELSE block or alternatively, in this case I stored the list of available measures in the relational table created earlier. The program can query this table directly as follows:

DEFINE SET_MEASURES PROGRAM BOOLEAN
argument T_USER text

variable T_LIST text
variable T_MEASURE text

variable B_RETURN boolean

trap on error noprint
B_RETURN = NO

" Check to make sure AW is attached in read-only mode
if aw(RO 'SH_OLAP.SH_AW')
then do
" Make sure AW is the first attached in the list
AW ATTACH SH_OLAP.SH_AW RO FIRST

" Find the current user
SQL DECLARE USER_cur CURSOR FOR SELECT USER FROM DUAL
SQL OPEN USER_CUR
sql fetch user_cur into :T_USER

" Find all the measures for the current user
SQL DECLARE measure_cur CURSOR FOR SELECT measure_name FROM sh_scope.sh_aw_security WHERE userid=:T_USER
if sqlcode ne 0
then signal dclerror 'SQLERRM'

SQL OPEN measure_cur
if sqlcode ne 0
then signal dclerror 'SQLERRM'

while SQLCODE eq 0
do
sql fetch measure_cur into :T_MEASURE
T_LIST = joinlines(T_LIST, T_MEASURE)
doend

SQL CLOSE measure_cur
SQL CLEANUP

" Limit to the measures the user is allowed to see
limit ALL_MEASURES to charlist(T_LIST)
" Swap the limit to set the measures to all those the user is not allowed to see
limit ALL_MEASURES complement
for ALL_MEASURES
do
CNS &remchars(CHANGECHARS(ALL_MEASURES, '.', '_'), findchars(ALL_MEASURES, 'MEASURE')-1, NUMCHARS(ALL_MEASURES))

" Remove the properties for specified measures to remove them the Query Builder
" measure panel

PROPERTY DELETE 'AW$MEASUREDEF'
PROPERTY 'AW$ROLE' NA
doend
doend

B_RETURN = YES

ALLDONE:
return B_RETURN

ERROR:
B_RETURN = NO
goto ALLDONE

END

Make sure the program compiles correctly before proceeding. Within the PERMIT_READ simply call SET_MEASURES to execute the code. You can test the procedure by simply attaching your AW in read-only mode, the program should execute automatically and without errors.

Now when the user SH_OLAP logs into Discoverer Plus the Query Builder panel is scoped down to just the measures for sales analysis.





and the same is true for Excel:

Planned Paper for UKOUG : A DBAs Guide to OLAP

A Working Title: Everything you ever wanted to know about OLAP but were too scared to ask

On Friday I finished submitting three papers for this year's UK Oracle User Group conference. In the process of trying to think of some good subjects I came up with the idea of trying to help DBAs understand what is required to support an OLAP environment. There appears to be a huge amount of information aimed at developers and designers but very little information on how to manage analytic workspaces from a DBA's perspective. The aim is look at key topics that matter on a day to day basis and my plan is to try and cover the following topics:

• What is OLAP
• Components of an OLAP data model
  
• OLAP APIs
• Monitoring and Tuning, Tracing and Debugging
• General Maintenance Issues
  

Fortunately we have most of this information available internally, locked inside email threads, responses on the OTN Forum, and various consulting manuals. Problem is bringing it all together and keeping the focus on just this one key group of users - DBAs. I am trying to include as many scripts as possible within the presentation to allow people to quickly and easily just cut & paste into SQLPlus/SQLDev etc. I have a horrible feeling this could turn into a very large monster.

Ÿ

Fortunately, a customer in the UK has just asked me to spend a day with their DBA team covering this exact topic so I can at least road-test the presentation once before appearing at the UKOUG - assuming the paper is selected of course.

I have submitted two other papers covering OLAP/data mining (one of my pet subjects) and creating a data warehouse over the OWB repository for management reporting. These will be extensions to similar presentations I gave whilst working in the US last year. But I will be extending both topics by adding new ideas into the mix.

If you want to attend the UKOUG conference it will be held between 3-6 December at the International Convention Center, Birmingham. For more information follow this link:

http://conference.ukoug.org/

If anyone has any OLAP specific DBA scripts, or general hints and tips, they would like to send me I would be most grateful and obviously full recognition will be given in the presentation and the white paper.