Monday, August 20, 2007

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