BI Blogs

Google Revealed: The IT Strategy That Makes It Work - Management News by InformationWeek: “To wring every ounce of performance from its hardware, Google writes custom software–lots of it. Major innovations include MapReduce, a programming model to simplify processing and create large data sets; BigTable, a system for storing and managing massive amounts of data; Sawzall, an interpreted programming language for analyzing large data sets in a distributed computing environment; Google File System, a distributed file system for data-intensive applications; and Google Workqueue, a system that groups queries and schedules them for distributed processing.

…

Google built its own CRM system to support its business of selling Internet ads billed by a mixture of bid price and click-throughs. But Google isn’t dogmatic about building its own tools. For accounting, it uses Oracle Financials.”

After we had the data warehouse built in our automated build, we needed a way to automatically deploy our SSIS packages and then call them.  You can use a tool called DTUtil to manipulate Integration Services on the server.  You can create and remove directores in msdb storage for SSIS packages, sign packages - and lots of other things.

I didn’t find many examples of using DTUtil so I’ll post an example.

Here is the command to deploy a package to the server.

dtutil /DestS [servername] /FILE [filename].dtsx /COPY SQL;[foldername]\[filename] /QUIET

If you will notice, you need to put .dtsx in the /FILE argument but not in the /COPY argument.  Also, it doesn’t matter what order the arguments are in so don’t worry about that.  In the /COPY argument the SQL means that I’m copying the package to the msdb on a SQL server.  The /QUIET means that it won’t complain if the package is already on the server - it will just overwrite it.

We were able to implement this right inside our NAnt script for deploying the SSIS packages.  Next we need to call the package right after deploy.  This might involve sp_cmdshell or calling a job out of a stored procedure.  We’ll see what works best.

Mark

Well that’s the first of the BI Seminars over and done with now, and I was
pleasantly surprised that, firstly we had around thirty attendees, and secondly,
it more or less went off without a hitch.

I must admit I was expecting about a maximum of ten attendees, so thirty was
pretty good and about the maximum you could do and still keep it fairly
interactive. The audience was a mixture of Oracle employees, partners and
customers, with the bias being towards Oracle and partners - no pressure there
then - and overall, with a pretty good level of existing experience with
Oracle’s BI tools. The timing went pretty well - I took my cue from an email
conversation I had with Jonathan Lewis who suggested four, one-and-a-half hour
sessions each day, and because I was there for two days you could afford to take
a bit longer with one and shorten another.

My trusty laptop held out ok; because it’s a fairly important session I took
a backup USB hard disk with a copy of my Oracle virtual machine, in fact the
only problem I had was with my Apple remote and Keynote - twice it completely
locked out and I had to hard reset and switch to Powerpoint, which was pretty
ironic on a Mac. I think next time around I’ll try and get a small laptop
bluetooth mouse and use that as a remote, having said that I can’t seem to find
one anywhere except Apple’s Mighty Mouse which is fifty quid and a bit too big -
I’ll have to have a hunt around the ‘net.

Having done the first seminar, things I’d change next time around are to add
a bit more content around BI Suite Enterprise Edition, maybe even make it all of
the second day - this is definately the area most people were interested in. I’d
probably add some more on new features in OWB 10gR2, again this was one of the
most popular topics although it might just reflect the particularl make-up of
the Dutch audience. Also, given the experience level of the attendees, I’ll
probably drop the introductory stuff, or at least have it in reserve should I
get a more beginner audience. Overall though, I was very pleased with how it
went, an excellent audience and hopefully we all got a bit of value out of it.

In this next series of postings, I’m going to go through some of the more
advanced features of OracleBI Answers and OracleBI Interactive Dashboards, and
look at:

• Adding a control to a dashboard page that passes a parameter value to the
  reports that are embedded within it;
• Creating a view selector, so that users can switch between different views
  of the data within a report;
• Creating a column selector so that users can choose which measure to view
  in a crosstab;
• Configuring a report within a dashboard to drill in place, rather than
  launching out to a new Web page;
• Drilling from one report to another, and
• Using the guided analytics feature to display links to reports and
  dashboards based on an OracleBI Delivers alert.

Thanks to Bas Roelands from Oracle Netherlands for passing on these tips at
the end of the Netherlands BI seminar.

For the first tip, I’m going to start off with a dashboard page with a number
of reports on it. Currently, these reports are for all years, but I’d like to
add a drop-down list to the top of the report that allows me to view the reports
for a particular year. Here’s what the dashboard looks like at the moment.

To add the drop-down list, the first thing I do is switch over
to Answers and then, in the Catalog view on the left-hand side of the page,
press the "New Dashboard Prompt" button to create a new dashboard
prompt (it’s the middle button in the screenshot), like this:

I then select Global Electronics as my subject area from the
pop-up dialog, and on the next page, define the scope of my prompt as just being
the dashboard page, not the whole dashboard.

Then, using the list of available tables and columns on the
left-hand side, I pick the column that I want to use with the prompt - in my
case, "Year".

I then save the dashboard prompt, and call it "Year
Prompt".

Now, I need to include the prompt on my dashboard. I go back
over to the dashboard application, make some space for the prompt and then drag
it, from the catalog, on to the dashboard page.

Now, when I save the dashboard and view the page again, the
control is added - but at this stage it won’t affect any of the reports.

To get the reports to respond to the Year prompt, I have to add
filters in to each of them. I’ll start off by amending the Top Customer report
just below it.

After I bring up the report in Answers, I add a column for Year,
and add a filter on it using the value "is prompted". This picks up on
the value in the dashboard prompt.

Now, when I go back to the dashboard and use the drop-down list,
the values in the report change to reflect the year I select.

Finally, now I know it’s working, I go back to the request and
hide the year column so that it’s not displayed in the dashboard.

Next, I want to change the Manager Revenue report that currently
displays a line graph, to one where the user can select from several
visualizations of the data. Currently, it looks like this:

which is revenue per year broken down by marketing manager. What
I’d like to do is add another drop-down box above the the graph, allowing the
user to switch to a crosstab view of the data, or a bar chart. To do this, I go
back into Answers and add another view to the request, a "View
Selector".

Then, when defining the view selector, I select the various
charts and the crosstab view that are associated with this request.

Now, I go back to the compound view and add this view selector
in.

Now, when I go back to the dashboard, the Manager Revenue chart
has a view selector above it, which allows me to switch between a line chart, a
bar chart and a crosstab.

Tomorrow, I’ll look at how guided analytics works with BI Suite
Enterprise Edition.

Summer is over and almost time to start work again - nothing technical here, perhaps next time.

Gartner has a three page news analysis of Oracle’s purchase of Sigma Dynamics’s assets (link to page on Oracle.com). This is the link to the page on Gartner.com. They have a PDF version of the document available for download. The document is sparse on details, but does give some useful background information to people not famililar with the company or how its product is used in Oracle’s Analytics

One more step of our automated build is solved.  I was able to automate PowerDesigner using Windows Script Host.  It isn’t the most elegant solution for sure, but it will do for now.

Here is a snippet of code:

set WshShell = CreateObject(”WScript.Shell”)
WshShell.Run “pdshell11.exe”
WScript.Sleep 2000
WshShell.SendKeys “^o”
WshShell.SendKeys MyModel.pdm

You can see that with this method, you could automate just about any piece of software - given that you can manipiulate it with keystrokes instead of mouse clicks.  I bet that eventually we will find a way to directly automate PD so othat we don’t have to use WSH.  Right now - while a build is in progress - you have to be very careful that you don’t touch the machine where the build is running.  If you change the active window while WSH is running, it will perform the .SendKeys on that window instead of PD.

This is something we would like to improve in the future, but for now it works.

The certain undeniable logic of Crystal Reports for Eclipse.

Are you open to open-source? It’s out there, but look twice.

WebFOCUS Resource Analyzer, a tool that’s designed to help organizations accelerate the performance of their WebFOCUS-based BI infrastructures.

It’s a milestone release, analysts concede, even if—in most other respects—it brings little new to the table in terms of either features or functionality.

Michael Armstrong-Smith, Discoverer expert par-excellence, an Oracle ACE, publisher of two hugely popular books on Discoverer, has published the first in a series of posts on how to use third party scheduling tools and products with Discoverer. Here is the link to the first post.

To add a few more sessions of interest (in addition to my previous post) in business intelligence:

Paul Rodwick, vice-president of business intelligence product management, has a session titled “Oracle’s Business Intelligence Road Map” (session id S282268). While Chris Leone (also a vice-president of product management, but with the applications strategy group) has a session titled “Oracle

I just blogged on the need for allowing dirty data to flow through to an auditable reporting area. There are a lot of questions about WHERE the dirty data should reside, and where the dirty data should be cleaned up. In this blog entry we’ll dive in to take a look at that in a short consolidated view. If you are fighting with compliance and auditability at a systems or data level, hopefully this will be helpful.