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 !

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.

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 Publisher Discoverer integration 2

Let’s take a little closer but brief look at this entire integration (between BI Publisher and Discoverer of course!) in a little more detail.
Architecture, configuration, optimizations, all come a bit later - let's approach this from a more end user perspective.

This below is a Discoverer worksheet. A table actually. Shows me Profits, Sales, by Year, Region, and City. Plain vanilla report - took me less than five minutes to create it (probably closer to two, but then I was not timing myself).


This Discoverer worksheet also has two parameters (parameters are the equivalent of conditions whose values are specified and applied at run time) - one on Year and the other on Region. For those coming from the BI EE world (that would be Business Intelligence Enterprise Edition), parameters are the equivalent of 'prompts' in Answers. And these parameters have LOVs (that would be “List of Values”, and not some vulgarism or cute colloquialism).
The two parameters also have default values - 1998 for Year, and 'West' for Region. This is useful for reasons we shall see later (in other words - 'trust me, I know what I am doing').


What I want to do is create a report in BI Publisher that uses this Discoverer worksheet as the data source.
While I go about describing that process here, please do excuse my graphical loquacity (i.e. I shall use more screenshots than you may need or want, but this isn't about needs or wants).
Go to BI Publisher and create a new report under a folder of your choice.
But first you have to login.

Here is the folder I created ("Blog" under "Abhinav" under "Shared Folders").
And the report I created is "Simple_Discoverer_1" (I do have a gift with names).


Now, this report is empty. No data, no template.


So if you click the "View" link above, you would see that the report template dropdown is blank, and clicking "View" would return nothing.

This is good, because you do not want an empty report with no data and no layout to get possessed...
What we need to do first is specify where the data is coming from. We know where the data is coming from – a Discoverer worksheet – but we need to tell BI Publisher that, right? Prescient computing is still some ways off (hmm... there's an idea).
Create a new data model first. That sounds difficult? No it isn’t. Simply click 'Data Model' and then click the 'New' icon.


After you do that, select ‘Discoverer’ from the dropdown (here you see the first glimpse of the functionality that is new in BI Publisher and germane to this post on Discoverer integration), and then click the two searchlights/torchlights and pick your Discoverer connection and workbook/worksheet.


See all the arrows in the screenshot below? After your data set has been defined, click 'Save' (would it be a good idea to have an 'auto-save' type functionality here?)


Done that? Good. You are good to go – at least in terms of viewing your data via BI Publisher. Click the 'View' link and you can already see a couple of things.
Firstly, the Discoverer parameters are now available here in BI Publisher. And the default values have been pre-selected.
Secondly, since I had specified in the Discoverer workbook that these parameters could accept multiple values, the LOV in BI Publisher appears as a multi-select list box. If these had been single value parameters, these would have been dropdowns so you could select only one value.


Thirdly, you can view the data, but there is no formatting or layout, still. That again is expected. After all, we haven't associated a template with this report yet. But you can see the XML here. It's in a rowset form, and the Discoverer web services have done this. And this is perhaps the most critical part of the whole integration. It doesn't really matter how the data gets from Discoverer to BI Publisher, as long as it is in this format - rowset - that BI Publisher understands and can work with.


Scheduling and Distribution
Before we (I am using the royal pronoun here... there's only me writing this post, right?) get into the template building part where you can use the formatting capabilities of Microsoft Word to create nice looking reports with BI data in it, let’s see just what we can do with this bare bones report that has no template associated with it.
Specifically, I want to try out two things (things always have to be exposited in twos and threes when I speak - annoying habit).
Firstly, and this is what many Discoverer customers want to do; schedule and distribute Discoverer content to users.
So, click the ‘Schedule’ button, which takes you to the BI Publisher scheduler. There, change the parameters if you want to, or leave them as they are. Set up a schedule – for convenience I chose to run the job immediately. Furthermore, specify that you want to destination to be email. My email. But of course.



All done? Click the ‘History’ link, and there is my scheduled job, with a green status icon of ‘Success’ (I thought of another non-sequitur on 'success' and 'green', but let's leave that for another day).

Now all I have to do is wait for the email. And there it is. In my inbox – I see the mail, and there is the attachment – “attachment.xml”.


In the next post I shall talk about making pretty reports from your Discoverer worksheets in BI Publisher (you see, I can talk English, I can walk English, I can laugh English - a Hindi movie reference if you must know).

BI Publisher Discoverer Integration - 3

So far I have blabbed blogged two posts (BI Publisher Discoverer integration and BI Publisher Discoverer integration 2) on the BI Publisher and Discoverer integration in the BI EE 10.1.3.3 release.
In the second post I took a Discoverer worksheet and showed a barebones BI Publisher report created using that worksheet as the data source. No layout, no template, but showed glimpses of what is possible here.

This, the third post, goes a bit further, wherein I focus on the basics and show how you can take a single Discoverer worksheet and from it create multiple tables, charts, crosstabs, totals, etc... Basically to showcase one of the benefits of this integration and of the capabilities of hi fidelity layouts that BI Publisher enables. For specifics and details on BI Publisher I would suggest going to OTN and going over the excellent documentation (PDF User Guide, or HTML version), OBEs (Oracle By Example), the BI Publisher blog, or the other training material available from Oracle University.

Let's start from the beginning, a very good place to start.
Using a public connection ('Sales History' - as in based on the SH schema), I have created a Discoverer workbook, which has two worksheets in it. One worksheet is a table while the other is a crosstab (pivot table).


The table and pivot table are identical as far as the items go - both have the same items (fields if you will) on them, and both have the same parameters in use.


There is a parameter on 'Product Category', on 'Year', and one on 'Region'. The 'Year' parameter accepts only single values, while the other two parameters can accept multiple values - that is the way I have defined the parameters sir, not that 'Year' is any snootier than the other parameters.


And for reasons that may become clearer in a future post (or else it can become the plot for a blockbuster whodunit - 'The Curious Case of the Gratuitous Properties Screenshot') you can see the workbook properties, including the name, the identifier, and owner.


Using the steps I outlined in an earlier post, I create a BI Publisher report and point to this Discoverer worksheet as my data source. So, the first step is to create a new report as shown below, and give it a name.


If you remember, in the previous post I showed how you could use the web interface to specify that the data set for a BI Publisher report could come from a Discoverer worksheet. Well, there is more than one way to skin a cat (or is it 'bell a cat'? In any event I am a vegetarian, so these jokes are in extremely poor taste... the word 'taste' itself is a poor pun), and we shall look at a few skins bells ways here.
For that, let's go to Word and logon to BI Publisher from the 'Oracle BI Publisher' menu in Word. If you do not have the BI Publisher Template Builder for Word installed you can do that by downloading it from the BI Publisher Enterprise site.


After you have logged in the Template Builder displays a window for you to select either a template or define a new template.
So, if you remember the report we created in BI Publisher a few minutes back, you can now browse to the report (I saved it under 'Shared Folder' : 'Discoverer'). You can see from the right hand panel that my report is there: 'Sales History 1'). But if you look down at the panel at the bottom, the one that says 'Layout Templates', it is empty. Right? Correct? Yes. That is because we do not have a layout template defined for this report. And that is what this post is all about, right? So what I could do here is double-click the 'New' text and begin creating a layout template for this report.


More Than One Way To Create a Layout
But.... let's step back a minute (please do not try this when standing near a precipice, lest you bring matters to an abrupt end) and ask ourselves: "what if I wanted to start in Template Builder to begin with, and did not actually have a report created in BI Publisher Enterprise?" theek hai (ठीक है)- that's not a problem. You can begin in Template Builder itself.
Simply click the 'Workspace' dropdown and select 'Discoverer' from the dropdown list of values. You can see that 'Oracle BI' would actually take you to an Answers repository (or BI EE), whereas selecting 'Discoverer' takes you down the BI SE path.

Click 'Discoverer' and out pops a dialog asking you to select a connection - you see the Sales History connection: that's what you have to select. And the 'Select Connection' dialog couldn't be any more self-explanatory.

If you are familiar with Discoverer, and I dearly hope you are - else this whole series of posts is going to go down into the big aluminum can marked as non-bio-degradable, then you will know that I still have to select a workbook and worksheet. Once you have selected the connection the list of workbooks is displayed as a hierarchical list at the left, while the panel at the right shows the worksheets available. You will notice that because the Discoverer relational folder structure is basically one level deep (you do not have sub-foldering in Discoverer relational) the expand/collapse signs are a bit redundant and clicking them doesn't really do anything. They are useful when browsing workbooks from an OLAP connection. Anyway....

Say for this exercise I want to use the Discoverer table worksheet above, I double-click it. I now have to select a location in BI Publisher where this report should be saved under. So, another popup appears asking me to do that. In this case I choose to save it under the 'Shared Folder' folder directly (I already have one BI Publisher report based on the same Discoverer worksheet saved under the 'Discoverer' sub-folder).


Trust but verify - so let's make a quick detour into BI Publisher on the web and see whether the report does indeed exist where it should. Under the 'Shared Folders' folder you can see the report has been saved, and with a default name - 'For BI Publisher.Table' - which is a concatenation of the Discoverer workbook and worksheet name. And if you so desired you could click the 'Edit' link and you would see all the appropriate fields populated with values - just as if you had created the report from the web interface of BI Publisher. So you see.. that's what I meant that there is more than one way to bell the cat (CAT aspirants - sorry... but this is not what you are looking for).


Let us now return to Word and the Template Builder that is still waiting for us to return from this needless diversion and get down to the task of actually building a layout.

From the BI Publisher toolbar click 'Insert' and select 'Crosstab' as shown below.

The Cross Tab layout editor is quite simple and intuitive to use. It's after all meant to let you layout your fields on the two edges (column and row) and drop a measures item onto the body of the crosstab. You can click 'Preview' to see how the crosstab shall look like, or simply click 'OK'.


I also have added a couple of charts - the layout editor is again simple enough to use... It's probably a good idea to select a style and to uncheck the '3D' and 'Gradient' checkboxes. I don't like 3D effects or gradients in charts. Yes, yes, I am being rude in foisting my personal opinions, but regular readers of this blog would have been inured to that by now (I hope). A quick note, a sort of gotcha to be aware of. The image that gets displayed in the chart section of the editor is actually a sample image. There is one image for each combination of chart type and style and 3D effect and Gradient effect. There are more than 900 such images, one for each combination, that are available to the Template Builder.

So what does it have to do here? Simply this, that if you click the 'Preview' button, the image changes to show you an actual version of the chart based on your data. Then if you change any property in the chart the image reverts back to a standard, canned image from the stock images. So don't panic - this is expected behavior, and certainly simpler than generating a new image at design time everytime you change a graph property.


So, I now have one crosstab, two bar charts - one for Sales and one for Quantity, and I shall add one table, using the Table wizard. The wizard is, again, fairly straightforward to use.


Having selected all available fields to include in my table, this next screen is important as it lets me specify how I want my fields to be grouped. Here I want a grouping by Year ('Group above') and then by Quarter.


So now I have my four views defined.


Using these layouts, I want to preview my report. At this point more than the appearance I want to make sure that my data appears ok and that the views - the crosstab, the table, the charts - are all there in the report. If I click 'Preview' and select 'PDF' I shall be first prompted to save my report as a RTF file.

Why RTF? Because RTF is not a proprietary format like the binary DOC format that Word uses by default. As a diversion (yet another one!), take this very simple Word document with one line as shown below, and save it first as a .doc document and then as a RTF format document. Open the .RTF and .DOC files in a text editor and you shall see what I mean.






This is how my reports appears. You would notice that all the layouts are there as they should be. But not very pretty. This is because I have not started formatting them as I would like to. I am using Word, so that should be a fairly easy task to do. All I need to know is how to use the formatting capabilities of Word - headings, fonts, colors, page-breaks, table borders


Without getting into the specifics of what and how I format I report, this below is a preview of the same report, but after I have applied lots of formatting, layout changes - and yes one change - I added a Pie graph also to the report.



This below is the same report - what I have done is added an image to the header of the page so that it appears at the top of every page (as I have always maintained, an image is worth a thousand words, or sometimes even more)



See this screenshot below - this is the table I had defined above. What I have done is applied a 'Heading 1' level style to the 'Year' and 'Quarter' items, and also a blue background. That may not be the prettiest style, but then I am not an interface designer, and like a good man I know my limitations (or maybe not).
Another thing of note that I want to point out are the totals that I have inserted at the end of each country below. That is very easily done by usnig the Template Builder option of inserting a field and then selecting the 'sum' as the aggregation method.


And in the footer of the page I have set the page numbering. And oh yes, I also have a page break after every country, so that each country's data appears on a new page - again using Word's insert page break feature (or simply enter 'Ctrl+Enter' where you want the page break - but you already knew that).



Having created on layout template, I now want to upload this to the BI Publisher Server. That can be done in two ways. You can do that straight from Word itself. Simply select 'Upload Template As...' from the Template Builder menu.


Enter a name for the layout template and click 'OK'


And voila! It's done.


You would already have guessed that using the option I could make changes to the layout template in Word and upload it as a new layout, and so on. So by making incremental changes I could very quickly create new layouts for use.

Back at BI Publisher in the browser, I now want to run the report and view it in any of the supported formats: The parameters are displayed for me to select from. And, to repeat, you can either multi-select or single-select depending on how the parameters have been setup in Discoverer.




A simple post - the next one shall look at something different in this area.