BI Blogs

This post is about Panorama because it is the UI tool I’m working with, but this can be made with every BI UI tool.

My customer wanted to get the effect shown by Analysis Services 2005 when browsing
a dimension (see the picture below). He wanted to see some properties of the members
shown in the rows, along with the usual measures. Unfortunately, Panorama (and I’m
sure that also other tools) does not have this option in the GIU. The solution is
this code:

Create Member CurrentCube.[Measures].[MyProperty] as
  iif(IsLeaf([MyDimension].[MyHierarchy].CurrentMember),
     [MyDimension].[MyHierarchy].CurrentMember.Properties(”MyProperty”),
     Null)

Note that declaring only the third row will cause that every member that is not a
leaf will cause an error, which is something we don’t want the viewer to see. If the
dimension has properties for members in other levels too, you can adjust this decleration.
This member can be declared either in the DataBase’s Script (after the CALCULATE expression)
or inside the session/query (not recommended in Panorama). Now, all you have to do
is to show the dimension’s members in the rows and this new measure in the columns
(after or before the regular measures), and you’ll get what you want.

In this next posting on building an Oracle Real-Time Decisions project (part 1, part2 and part 3 are here if you’ve not seen them already), I’m going to look at the process of creating and populating the variables that RTD will use when building my predictive model. To recap, I’m looking to build an RTD Inline Service that will decide on the most appropriate manufacturer for a customer order, with the inputs to this model being the details on the customer, details on the order and the previous history of four potential manufacturers. This step of the process is about defining variables (or “entities”) to hold details on the customer, order and manufacturers, and then creating a mechanism to populate them when the inline service is invoked.

Details on the customer and the order will come from the BPEL process that calls the RTD inline service, but details on the various manufacturers will actually be held in a database table that we’ll have to read from whenever the service is invoked. Starting off first with manufacturers then, I create a table called MANUF that holds details on the four manufacturerers and metrics on their manufacturing and delivery history. I’ve installed the Web element of Oracle Real-Time Decisions in to Oracle Application Server 10.1.3.3.1, and so I create a JDBC connection pool, and then a managed data source using the Enterprise Manager Control Web site that maps through to the schema containing this table. I then register this new data source with RTD by following the instructions on page 69 of the Oracle RTD Installation and Administration guide, it basically involves editing four XML files which whilst a bit tricky, does work and makes my new JDBC data source visibile in the RTD Studio application.

Once you’ve got the JDBC data source defined and registed with RTD, the next step is to create a Data Source object within RTD Studio that points through to the JDBC data source, and then use it to import the table metadata into the application. Once you’ve done this, you can select the table columns that you want to make available to the application, and pick one of the columns that will be used as the lookup column.

Now I’ve brought in the database data source, I can now create the Manufacturer entity, which I’ll map to this data source in a moment so that it auto-populates when the service is invoked. To start this process, I select “New Entity” from the RTD Studio service metadata menu, like this:

and then auto-create the entity columns (entities are like tables, they exist at the table-level in RTD applications and have one or more columns associated with them) using the “Import” option. This allows me to pick a data source, suck across the columns associated with one of the tables available through the data source, and then provide a set of default values for the entity, something that RTD requires.

The columns in this new entity are then mapped to the data source columns using the Mapping tab, and finally I press the Key button to define an entity key which is defined as an integer. The final entity looks like this:

with the mapping defined as this:

The next step is to define a session variable that will hold the values of the various manufacturers during the time the service is running. To do this, I double-click on the Session entity in the Inline Service Explorer and then use the press the “Add Attribute” button to add an attribute called Manufacturer, which is then defined as a complex attribute based on the manufacturer entity I just defined earlier.

Finally, I need to go back to the Manufacturer entity settings, click on the Mapping tab and map and use the Data Source Input Values section to map the manufacturer session entity key (which will get it’s value from the BPEL process) to the entity key, so RTD will do an auto-lookup of the manufacturers details from the database when required.

So the situation I’m in now is that I’ve defined a data source that maps to a table containing the details and metrics on manufacturers, and then I’ve used this data source to build an entity which is keyed on the manufacturer ID, with the entity mapping back to the data source so it knows where to get it’s data from when invoked. Then, I’ve defined a session variable of a type “manufacturer”, which is used to hold values of manufacturer entities in memory when the service is run. One thing that I’m not sure of is how we hold details on more than one manufacturer - do we read them into the session variable serially, then feed them into the model, or do we have multiple instances of this session variable, one for each manufacturer under consideration. We’ll have to wait and see.

As well as requiring details on manufacturers, the inline service I’m building also need to hold details on the customer (their name, their status, their credit limit) and details of the order (the order value, the order date and so on). These values will actually come from the BPEL process calling the inline service, and so I won’t need to go through the process of defining additional data sources and using these to create the entities. Instead, I just use the inline service explorer to create the new entities, with customer being created first, and I use the “Add Attribute” and “Add Key” buttons to create the required columns, like this:

I then do the same for Orders, creating an Order entity, adding just a single key (Order ID) and two attributes, Order Value and Order Date.

Next I create two more session variables, one for Customer and one for Order, creating them using the Customer and Order entity types, and defining session keys to map to the keys used by the two entities.

When everything’s set up, the session variables used in the RTD inline process look like this:

So as a process the steps are:

1. Define relational data sources if required.
2. Define the entity, either manually or by reference to an associated data source table.
3. Define the key for each entity.
4. Define a session variable attribute for each entity, with the variable type being based on the entity table definition.
5. Define a session key for each session variable attribute, based on the key for each entity.
6. If the associated entity is auto-mapped to a database table, provide a link between the session variable key and then entity key so an auto-lookup can take place.

OK, so we’re now in a position to test everything out. To do this, I need to create an Informant, an input into the RTD process that we can then use to debug and diagnose the variable initialization process.

I first of all locate the Integration Points, then Informants node in the Inline Service Explorer, and select “New Informant”.

I call the new informant “Testing” and press the “Advanced” button when naming the informant to check the “Show in Decision Center” button (the Web interface for RTD that’s used for monitoring the process of RTD inline services).

On the Testing informant properties page, I press the “Select” button to pick the Manufacturer session key, which is passed as in input into the informant.

Now, that’s got me thinking. In a second, I’m going to add some logic that will display some of the manufacturer attributes for the manufacturer that is referenced by the manufacturer ID input value passed to the informant. To do this, it’ll do a database lookup, using the key and entity binding I created earlier. I wonder, therefore, if I should do all the customer and order details lookups in this way as well, where the inputs to the Inline Service are just the customer ID, order ID and manufacturer ID, and RTD gets all the rest of the information from the database, i.e. not from the calling BPEL process? I’ll carry on for the moment, but I may have to come back and revisit this later on.

Anyway, the next step is to add some logic to this informant, logic that is going to output the diagnostic data we’re looking for. To do this, I click on the Logic tab and type in the following Java code:

logInfo("Manufacturer name = " + session().getManufacturer().getName());
logInfo("Manufacturer accepted orders = " + session().getManufacturer().getNumAcceptedOrders());

like this:

Be careful with the variable names etc, they’re case sensitive - in my case, RTD took the column name NUM_ACCEPTED_ORDERS and turned it into the java method getNumAcceptedOrders, which is referenced through the class session().getManufacturer().getNumAcceptedOrders - it helps with RTD if you’re got a bit of Java knowledge, mine consists of going on a couple of courses a few years ago, but at least I know how to hunt through a bit of Java code, which I had to do in this instance to see how RTD had named this variable/method in the auto-generated Java code.

Now it’s just a case of deploying the Inline service, like this:

Once it’s deployed OK and with no errors, you can use the Test facility in RTD Studio to test it out. I input a manufacturer_id of 1, and get back the correct results.

OK, so where does this leave us?

1. Firstly, we know we can look up details of a manufacturer given a manufacturer ID. HOWEVER, I’m not sure whether this is in fact what we should be doing - we need some metrics on manufacturers to feed into the predictive model, but then again it’s the manufacturer we’re choosing at the end, I’m not sure whether we get the manufacturers attributes from a table like this, or whether they come from the predictive model - we’ll have to wait and see.
2. I’ve defined entities and session variables for customers and orders, but as yet haven’t populated them, as I’m planning to get the BPEL process calling the RTD Inline Service to provide these values as input parameters. I’m not discounting the option, though, of just having the BPEL process pass across the customer ID and order ID, and have RTD get the rest o of the customer and order details from database tables, like we did with manufacturer. This would fit the OTN Oracle-by-example better, but in the scenario I’m trying to built, it’d be more logical to get the BPEL process to provide these values, as it holds them as part of the order it’s processing.

So, some good progress but a few questions that I still need to answer. Tomorrow, I’m going to create some informants for the external systems - the BPEL process, the feedback at the end of the customer order and so on, which should well answer these outstanding questions. Check back tomorrow for some more progress.

The PIVOT operation is one frequently discussed when talking about more advanced SQL Queries. Pivoting is the process of switching rows and columns, and is for example an advanced feature in Excel. Before Oracle 11g, there were several approaches to pivoting, that were not work-arounds for the lack of the PIVOT operator in the Oracle […]

One of the interesting new pieces of functionality in Oracle 11g is offered by the DBMS_COMPARISON package. This package allows us to compare two tables - or two views on tables - and inform us on the differences in data. More specifically: the package can summarize if there any changes between the data sets and […]

Over the past two days, we’ve taken an initial look at Oracle Real-Time Decisions (RTD), come up with a scenario with which we can build an RTD demo, and yesterday taken a look at the various elements of an RTD project. Today, we’re going to take the Order Bookings manufacturer choice scenario and match it up to the various RTD project elements.

As a recap, we have a current BPEL process that takes a customer order, validates it, retrieves some customer information and then gets to a point where it has to decide on a manufacturer. At the moment, the choice is based purely on which manufacturer provides the lowest quote; in the future, we’re going to use RTD to choose a manufacturer based on which one helps us achieve our current business goals, be they to minimize cost, improve customer service, get most orders delivered on time and so on. RTD is going to build up a predictive model that will take information on the customer and their order, take into account contextual information such as the time of year, and come up with the choice of manufacturer that is most likely to help us deliver on our business goals.

RTD projects have five main elements:

1. Entities, the actors in the process
2. Choices, the things that the decision can pick between, in our cases the various manufacturers
3. Performance Goals, the business priorities that can be in effect at any one time - reduce costs, improve customer service, get most orders accepted and delivered within X days
4. The models and rules that are used to make a decision, and
5. The actual decisions that take place

In addition, we need to define Informants, interfaces into the Inline Service that provide data on the customer, whether the decision was accepted, what the customer’s feedback on the order process was and so forth.

Going through this list then, the entities that we will need are:

1. The customer - the person placing the order. Customers will have several data items associated with them including their age, status and so forth. Data on the customer will come from an Oracle database, which will be accessed via the customer ID passed to the inline service from the Order Bookings BPEL process.
2. The order - principally the value of the order, passed to the Inline Service from the BPEL process, and also the number of items, name of the most expensive item in the order and so forth.
3. The manufacturers, their name, number of staff, whether they will take orders electronically and so on.

I mentioned in one of my initial postings that I’ve actually got a very similar RTD project, built by the RTD product management team, that does a similar job - it selects the most suitable satellite TV installer based on a similar scenario and set of inputs.

Taking a look at the project in Oracle RTD Studio, I can see that I might well need some additional entities, to hold manufacturer metrics, real-time data on manufacturers, such us the number of order rejections they’d made in the last hour, day and so on. I’ll have to decide on whether these additional entities are needed when putting my example together.

In terms of choices, these are going to be the various different manufacturers that the decision-making process can decide from. At the moment, the Order Bookings BPEL process uses two manufacturers, “Select Manufacturers” and “Rapid Manufacturers”. To this we’ll add “Bargain Manufacturers” and “Expert Manufacturers”, and we’ll set up the details for each of them to reflect the following characteristics:

• Select Manufacturers will be expensive, provide excellent customer service, and deliver within an average timeframe.
• Rapid Manufacturers will be expensive, typically delivery faster, have average to just under average customer service, but sometimes reject orders because they’re too busy.
• Bargain Manufacturers are typically the cheapest, always take on orders but sometimes delivery them late, and don’t have particularly good customer service.
• Expert manufacturers are expensive, always take on orders, have below-average response times but good customer service.

Within the BPEL process, I’ll introduce a random element such that some manufacturers reject orders and some deliver late. These results will feed back into the RTD model, meaning that over time, a decision might start to change as certain suppliers’ delivery time starts to suffer, their number of rejected jobs goes up and so on. We’ll store these scores and metrics in a database table, and make them available in the RTD project through the supplier entity.

In terms of performance goals, we’ll have a number that we’ll emphasize and de-emphasize at various times. These goals will be:

• Minimize Service Cost
• Maximize chance of delivery on-time
• Minimize rejection rate of work orders

Over time, if we want to maximize customer service at any one time, we’ll emphasize delivering on time and having all work orders accepted, whilst at others, if we’re more concerned with maximizing profits, we’ll emphasize cost reduction instead. If I can work out how to do it, I’ll segment the customers (based on their status, which can be either gold, silver or bronze), emphasizing customer service for the gold customers and cost reduction for the others.

The informants, or events or interfaces into the RTD inline service, will come at three points:

1. When we get all four quotes through an need to pick a manufacturer
2. When we get the work acceptance, or not, from the manufacturer
3. When we get data back from the customer on when the order actually arrived.

The three models we will initially build up will be used to predict the following metrics:

1. Customer Satisfaction
2. Order rejection, and
3. Order completion on time

Finally, we will have one decision that the RTD project will generate, which will be a choice of manufacturers based on our current business priorities, which will be expressed by weighting the various performance goals to emphasize customer service, keeping costs down and so on.

As shown in the screenshot above, taken from the existing RTD demo I’ve got on my machine, it’s also possible to segment customers and apply different performance goal weightings for each segment.

So, that’s the background to the project. Tomorrow, we’ll set up the entities and choices.

Como en bastantes ocasiones nos habéis estado preguntando por una buena recopilación de libros sobre los temas de interés de este Portal: Business Intelligence, Open Source, Data Warehouse, etc… ahí va la lista (todos disponibles a través de Amazon).

Si nos tenemos que quedar con alguno como esencial… sin duda ‘The Data Warehouse Toolkit’ de Ralph Kimball. Si queréis recomendar libros que consideréis interesante para añadir, no os cortéis.

Succeeding with Open Source
by Bernard Golden

Essential Open Source Toolset
by Andreas Zeller, Jens Krinke

Open Source Software: Implementation and Management
by Paul Kavanagh

Open Source Enterprise Solutions: Developing an E-Business Strategy
by Gunnison Carbone, Alex Lesniak, Duane Stoddard

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
by Ralph Kimball, Margy Ross

Building the Data Warehouse (3rd Edition)
by W. H. Inmon

Business Intelligence Roadmap: The Complete Project Lifecycle for Decision-Support Applications
by Larissa T. Moss, Shaku Atre

Open Source Solutions For Small Business Problems (Networking Series)
by John Locke

Open Source GIS: A GRASS GIS Approach (The International Series in Engineering and Computer Science)
by Markus Neteler, Helena Mitasova

Beginning MapServer: Open Source GIS Development (Expert’s Voice in Open Source)
by Bill Kropla

The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
by Ralph Kimball, Joe Caserta

Business Intelligence: The Savvy Manager’s Guide (The Savvy Manager’s Guides)
by David Loshin

e-Data: Turning Data into Information with Data Warehousing (Addison-Wesley Information Technology Series)
by Jill Dyché

Performance Dashboards
by Wayne W. Eckerson

Open Source for the Enterprise: Managing Risks, Reaping Rewards
by Dan Woods, Gautam Guliani

Data Mining and Business Intelligence: A Guide to Productivity
by Stephan Kudyba, Richard Hoptroff

Mastering Data Warehouse Design: Relational and Dimensional Techniques
by Claudia Imhoff, Nicholas Galemmo, Jonathan G. Geiger

Fuente: OSBI Squidoo

I’ve blogged a little bit about this before. In this entry we’ll explore the mathematics side of normalization and denormalization in the data model. Of course, this entry is also specifically targeted at very large data sets. On small systems that can’t handle the mathematics, performance degrades to a certain degree. There’s a rumor out there, an un-truth, a fallacy that states: if you’re having performance problems in your database, simply denormalize (flatten the tables, remove the joins). This works, but only to a point.

Well, I have been out here in Qatar for a week now. The work has been quite interesting and the days have been rather busy. Jon and I haven’t really had any time to take in any sights, until last night when we went to an outdoor market. It was very nice and relaxed, loads of people around but no one seemed to be in any hurry; the weekend had started and time to relax and enjoy some good company (weekends starts on Friday). I took some pictures but alas the camera didn’t really do any justice to the place, all the pictures are too dark for posting here. Jon had a lot nicer camera and took some shots as well, perhaps he can share some of these once he is settled in back home (he took the overnight flight back to the UK after the market trip).

Week two starts tomorrow. We are working with OWB, Discoverer and Portal to come up with a proof of concept as well as training the client in using these tools. The team is very enthusiastic and eager to learn so the work is very enjoyable. The working hours are rather different from what I am used to; Start at 8 and then take a break at around 15:00 and then start back at around 19:30 and work until about 22:00. In between, it’s back to the hotel, lunch and perhaps go to the gym. With the different rhythm, I seem to have lost close to 5kg here (or is it just because of the lack of beer ? :)) in just one week. Somehow I think I will manage to find these lost kilos when I get back …

I’m just holed up in the lounge at Schiphol Airport, The Netherlands, waiting for a plane back to Heathrow. Today was a very pleasurable day doing some training for Centennium, an Oracle BI & data warehousing specialist consultancy based down in The Hague, the team were really welcoming and keen to hear about what’s new with Oracle BI EE, Oracle Data Integrator and Oracle SOA Suite. Thanks to everyone who came today, and especially to Eric for inviting me over. The Hague’s a very nice place as well - very much like Amsterdam in terms of the architecture, canals, squares and so on, a bit less touristy though and with a very decent hotel in the Hotel Des Indes - a definite recommendation on my part.

Earlier in the week was the UKOUG Business Intelligence & Reporting Tools SIG in London. This time, we had a Hyperion/Planning and Budgeting theme, with one of the talks being on Hyperion as a company/platform, another by Pete Singleton from Analitca on Essbase. The Essbase talk was one I was particularly looking forward to, given my work over the past week or so on getting Essbase up and running, whilst the other talks, on Oracle 11g data warehousing new features, enterprise planning and budgeting and an Oracle BI update, were also pretty good.

For me now though, it’s back to Heathrow, then a layover at the Jurys Inn by Hatton Cross, then up early for the flight to Washington for the BIWA event. I’m starting to miss home now, but next week in particular should be good which hopefully should make up a bit for being away from the family.

I’m working with Wayne Eckerson (Director, TDWI Research,  The Data Warehousing Institute) on their new Best Practices Research report, "Strategies for Managing Spreadmarts and Integrating with Microsoft Office," which will be published in January 2008.

If you take the survey, you can be among the first to receive the report.

One of the biggest pain points BI professionals face is dealing with "spreadmarts" - renegade spreadsheets and desktop databases that suck up expensive analyst time and undermine data consistency and compliance efforts. The question is, how do you enable users to work in Microsoft Office applications without undermining a single version of truth? Our upcoming report will answer this question and provide several approaches for corralling renegade data shadow systems and explain technical advances for integrating with Microsoft Office applications.

To write this report, we need your help. We’d like to understand the scope and costs of spreadmarts and effective remedies. Please let us know how you are approaching spreadmarts by taking our 15-minute survey at the following Web address:

http://www.tdwi.org/info.aspx?id=42175

For completing the survey, you will be the first to receive the final report when it is published in January 2008. If you encounter difficulties, contact Wayne at   weckerson@tdwi.org.

Just a quick post to say thanks to everyone who attended last night’s BI evening in London. I thought it went really well - we had some good discussions springing from Mark and Suranjan’s presentations. I’m very grateful to Vaughan, Sandra and Sanjay of IMGroup for hosting us and providing so much beer and pizza.

I think I’ll try to organise the next one around December when more details on Katmai should have emerged. Since the last few have been in London, perhaps we should go back to TVP to avoid any accusations of being too London-centric…

It’s a fairly common occurrence to have several attributes in a dimension describing the same thing: products can have long and short descriptions, for example, or you might want to display dates in different formats, or show the full names of states or just their abbreviations. AS doesn’t give you any functionality to specifically handle this - something I’ve heard from people who’ve used other OLAP tools is that it would be great to set up ‘aliases’ for attributes, rather like how you can set up translations - so you just have to design it into your dimension. One thing I’ve noticed, however, is that when you’re designing attribute relationships to cater for this scenario it’s tempting to do so in a particular way that leads to problems later on; at least I’ve done this in the past and seen several other people do the same thing, so I thought it was worth blogging about. 

Here’s an example Time dimension based on the Adventure Works relational data:

We’ve got a simple Year-Quarter-Month-Date structure, but we want to see the same structure once with a short description (eg ‘2004′ for the year) and once with a long description (eg ‘Calendar Year 2004′). There are two natural user hierarchies that reflect these two choices, but notice the way the attribute relationships are designed: they take no account of the fact that there’s a one-to-one relationship between the two sets of attributes. If you use BIDS Helper’s excellent ‘Visualise Attribute Lattice’ functionality you can see the relationships make a fork shape:

What’s wrong with this? Well, rather than answer that question, let’s look at my preferred alternative:

Here’s the accompanying lattice:

You can see that instead of a fork shape we’ve now got a long chain, and the one-to-one relationships I mentioned earlier are now in place; remember that this doesn’t affect the way the dimension appears to the end user at all. Incidentally, you might wonder like I did if the Cardinality property of a relationship should be changed from ‘Many’ to ‘One’ for one-to-one relationships: it’s probably worth doing but according to BOL it doesn’t have any effect at the moment:
http://msdn2.microsoft.com/en-us/library/ms176124.aspx

The advantages of this design are as follows:

• More efficient autoexists. In the fork approach, if you crossjoin Year with Month Long Desc AS will have to resolve that relationship through the Date attribute; in the chain approach because there’s a direct relationship between the two working out which members on Month Long Desc exist with Year is going to be faster. You’ll probably only notice this on fairly large dimensions though. UPDATE - see Mosha’s comment below, this isn’t correct.
• In general, I reckon that the aggregation design wizard will produce better designs with the chain approach. Again I’m not sure how much difference there will really be, but when I ran the wizard on a test cube containing just my fork dimension  it came up with three aggregations at Month/Quarter Long Desc; Month Long Desc/Quarter; and Year. When I did the same thing on a test cube containing just my chain dimension I got four aggregations at Month; Quarter; Year; and All. In the latter case, of course, the Calendar Long Desc hierarchy can benefit from aggregations built on the non-Long Desc attributes, and overall the aggregation coverage seems better. Results, however, may well vary in the real world…
• Scoping calculations becomes much, much easier with the chain approach rather than the fork approach. Imagine that you want to implement something like a Total-to-Date calculation, which sums up a measure from the beginning of Time to the currentmember on Time. Naturally you want it to work on both your user hierarchies, but you’ll find that with the fork approach it becomes fiendishly difficult to get your scoping correct - it’s a lot harder than you think, and in fact I’ve not come up with a satisfactory solution. With the chain approach however there’s a really elegant solution: because of strong hierarchies, every member you select on the Calendar hierarchy automatically forces the currentmember on the Calendar Long Desc hierarchy to the equivalent: so, selecting ‘2004′ on Calendar changes the currentmember on Calendar Long Desc to ‘Calendar Year 2004′. This means you can scope all your calculations on the Calendar Long Desc user hierarchy and they’ll automatically work on the Calendar hierarchy; for example, here’s how to do the TTD calc:

tCube.Measures.TTD as null;

Scope(Measures.TTD);
    Scope([ForkTime].[Calendar].Members, [ForkTime].[Calendar Long Desc].members);
        This=sum({null:[ForkTime].[Calendar].currentmember}, [Measures].[Sales Amount]);
    End Scope;
End Scope;

(Thanks are due to Richard Tkachuk for answering some emails on these issues)

In yesterday’s posting, I set the scene for an Oracle Real-Time Decisions project that I’m going to integrate into the Oracle SOA Suite Order Bookings BPEL process. At present, the Order Bookings process allocates manufacturing jobs based on who provided the lowest quote; in the demo I’m going to build, jobs will be allocated based on a predictive model that takes into account the customer and their order, information about the context of the order, directives from me on what my current business priorities are, and comes up with a manufacturer recommendation that is mostly likely to help me meet my business goals.

Before diving in though, it’s worth taking a few moments to think about some of the key concepts in Oracle Real-Time Decisions.

At the most basic level, what you build in Real-Time Decisions is an “Inline Service”, a process that you can call using in and out parameters that makes a decision for you. The inline process takes your input parameters, runs them past it’s model and comes up with a decision, based on various weightings that you give to your various business priorities.

At the end of the process, you feed back to the Inline Service the actual choice that the customer made, so that Real-Time Decisions can fine tune it’s predictive model in order to come up with more and more appropriate decisions, of offers, for the customer. The Inline Service can either be called via a Java client interface, or we can register it as a Web Service and call it from, for example, a BPEL process.

Within a Real-Time Decisions project, there are five main “elements” that you need to work with:

1. Entities, the main “actors” in a system. Entities have properties and are effectively the variables that you populate at the start of the process, read details from as needed and the load up prior to passing back the decision to the calling application.
2. Performance Goals are the key performance indicators that the the Inline Service tracks and optimizes.
3. Choices, the possible process outcomes, such as as offers, agents, service providers and so on. In our example, the choices will be the various manufacturers that can be used to deliver the order - Select Manufacturer, Rapid Manufacturer, and some others that we’ll come up with.
4. Models and Rules are the predictive models that RTD builds up, and the rules that you define as part of the inline service definition, that are used to work out what score each particular choice attracts, and
5. Decisions take the output of the model, apply weightings based on your performance goals and come up with the most appropriate decision.

Another key concept in RTD is “informants”. Informants are the interfaces into the Inline Service, and provide, for example, feedback on what the customer’s call is actually about, whether they accept an offer, whether they take up the offer some time down the line and so on. All of the above elements, plus the informants and the predictive models, are built up using a tool called Oracle RTD Studio, a Java GUI based on the Eclipse framework that builds up an RTD project in much the same way that you’d build an OWB project, or a Java application.

Tomorrow, we take the Order Bookings process and pull out from it the RTD elements and potential informants that we’ll use in the project.

In the
business intelligence and data warehousing industry we are continually inundated
with new product categories and killer apps that are going to revolutionize what
we are doing and finally enable a "single version of the truth."

And,
they’re going to do it cheaper than we have ever been able to do it in the past!

Yeah,
right. (And I’ve got a bridge to sell you.)

You can
easily gather a list of these miracle solutions by looking at various data
warehousing and business intelligence (DW/BI) magazine or vendor websites.
You’ll find DW/BI appliances, OSS (open source software), SaaS
(software as a service), SOA (services oriented architecture), MDM (master data
management), CDI (customer data integration), CPM (corporate performance
management), in-memory OLAP (on-line analytics processing), EII (Enterprise
Information Integration), EIM (Enterprise Information Management), and real-time
BI.

Keep
digging and you’ll find even more. Sometimes these categories or technologies
go by another name or acronym (just to confuse matters more).

Are any of
these truly revolutionary? Do any really deliver on their hype?

Often these
products or technologies do indeed provide business and technology value, but
maybe what is delivered doesn’t match the expectations presented by the media,
analysts and vendors.

Are these
expectations purely built on pretty Marchitecture slides? Are the customer references from "regular"
customers like you? Or are they from companies that are eager early adopters or
serial buyers of many different products anyway – always searching for the next
killer app?

Most importantly, will you really realize the supposed gains because you
bought these new products? Or will it be because you implemented your latest
DW/BI project differently? Oftentimes, a project is successful because of the investment
in time, resources and commitment to
the DW/BI effort – not because of a magic tool.

Sorry vendors, but your new
product may have been merely incidental to the project’s success rather than
the reason for it. But, you’ll still write case studies proclaiming that your
tool is DW/BI’s latest salvation.

This is the kick-off to a series of posts on hyped products and trends.
We will look behind the curtain to discuss what is real versus myth.

Please feel to comment on what technologies or trends you would like to
discuss. A word of caution: this discussion is NOT meant to trash specific
companies, technologies or products. 

Canals - check. Cobbled streets - check. Trams and lots of bicycles - check. Erotic shops - check. Refreshingly liberal attitudes and some eye-watering channels on the hotel TV - check. It can only be the Netherlands, and I’m over here staying at a rather nice hotel in The Hague, getting ready for a one-day workshop I’m running for a local consulting partner who are looking to get up to speed with all the latest and greatest Oracle BI products.

This is only a flying visit and I’m here just for tomorrow, flying back later in the evening for a lay-over at Heathrow prior to flying to Washington for the BIWA Summit. Tomorrow should be good actually - the company I’m working with seems pretty much “on the ball” when it comes to Oracle consulting and development, we’ve got a good agenda and it should be a productive day. In my spare time, I’m working on putting together an Oracle Real-Time Decisions project, to go through the process step-by-step to I can enhance the RTD part of my BI Masterclass; apart from that there’s the finishing touches for the BIWA keynote, and hopefully I should get the Oracle BI EE and SOA article back from my Oracle co-author so that we can get it off to OTN and hopefully get it published soon.

Apart from this, Jon’s been working over in Qatar with Borkur on a pretty intense OWB, Discoverer and Oracle Portal project, which apart from ten hour days is also taking place during Ramadan, so I’ll probably keep quiet about the nice bottle of wine and relaxing three-course meal I had tonight when Jon next calls. Apart from working over there though, he did get a chance to zip over a couple of links to some interesting articles; this first one by Joel Spolsky looks at the benefits of starting your own consulting/software company and funding it yourself, the second is about something the author calls “tact filters”, and goes some way to explain why technies and business people sometimes end up unwittingly rubbing each other up the wrong way. Both articles are well worth a read.

Anyway, time to unpack the suitcase, press a shirt and get ready for tomorrow.

And for my next trick, I shall be …. building an Oracle Real-Time Decisions project. For anyone who didn’t catch the posting on this blog earlier this year, Real-Time Decisions is a part of Oracle BI Suite Enterprise Edition, originally built by Sigma Dynamics and included as part of Siebel CRM, and now available standalone to add real-time marketing features to your business process or application.

Real-Time Decisions is effectively a data mining engine that takes on board details, for example, of a customer, adds to it some contextual information - what the customer has rung up about, how long they were on hold waiting for a call centre operator, what part of the year it is and so on - and comes up with a recommendation, or a product offer, that’s most likely to help you meet your business goals.

For example, you may have a call centre application that is used by operators dealing with incoming customer service calls. A customer may call in who’s in your “Gold customers” category, and normally you’d try and cross-sell them a savings account, or a bond or whatever. However when it turns out that they are calling with a complaint, and it’s the time of the year when customer retention is particularly being focused on, Real-Time Decisions might actually recommend a free gift, or a bonus interest rate, or whatever. The key thing is that Real-Time Decisions builds up a predictive model of what offers are most likely to be taken up by particular segments of customers, and it comes up with recommendations that you can influence by emphasizing particular business goals - minimize transaction costs, retain more customers and so on. One of the main differentiators for Real-Time Decisions is that the process of picking a data mining model, and the building of that model, is completely automated meaning that you don’t need statisticians to work out whether to use neural networks, bayesian models or whatever for your predictive model - RTD does all that for you.

I do in fact have a working demo of RTD which I use as part of the Business Intelligence Masterclasses that I run with Oracle University (next venues, Sweden, Hungary, Ireland and The Netherlands), but it’s one that was put together by the RTD product management team and I want to now build one myself, to see how the process works. I have been through the RTD tutorial that you can access on OTN, but again there’s no substitute for building your own demo, working through all the problems and so on, as opposed to working through a step-by-step tutorial.

So, on to the demo scenario. One of the first tricky points that I encountered was to come up with a scenario that matched how RTD works. RTD generally makes marketing recommendation based on a predictive model plus contextual information you feed through in real-time. For my example, I wanted to really avoid having to build an application as working with JDev, ADF and so on is a bit outside my area of competence, so one area I thought of was to add some decision-making capability to a BPEL business process, which would let me build up a workflow but avoid all the GUI development, etc. As the rest of my BI Masterclass is based around the Oracle SOA Suite Order Bookings BPEL process, the scenario I eventually came up with is around picking manufacturers for a customer order.

In the Order Bookings process, customers place orders via a Web interface which eventually make their way, via Enterprise Service Bus, to a BPEL process. The BPEL process then logs the order, gathers some customer information, verifies the credit card number and then gets to a point where it has to decide which manufacturer to use.

It does this by first of all getting quotes from two manufacturers, Select Manufacturer and Rapid Manufacturer, via a Web Service. Once the quotes come in, it chooses the one it wishes to use based on who provides the lowest quote. In my example, I’m going to replace this with an RTD “Inline Service” that takes the quotes, plus some contextual information and information on the customer and order, and selects the most appropriate manufacturer based on my business goals at the time - minimize cost, increase customer satisfaction, give us the greatest chance the manufacturer will accept the order an process it first time (as opposed to reject it, or end up delivering late). This scenario isn’t too far from the RTD demo I currently have (which allocates satellite TV installations based on similar criteria), but it’ll be integrated into the SOA Demo Order Bookings process, I’ll have to figure out the plumbing myself, and I’ll be on my own.

So, in the next posting, I’ll be looking at what sorts of data and processes I’ll need to get the most out of Oracle Real-Time Decisions.

The subject of multiselect friendly calculations is a popular one in forums. I have written about it before here, here and here. In February 2008 I will write a blog which will give a definite answer on how AS2008 will deal with it (which is follow up on this post). But in the meantime, questions about AS2005 keep coming. By now, most people realize that it is possible to detect presence of set in WHERE clause by using EXISTING operator, which takes care of multiselect queries generated by Excel 2003 and other tools. However, Excel 2007 for multiselect uses subselects which are stealthier, their presence cannot be detected neither by EXISTING nor by any other MDX function. The standard answer for detecting subselects always was to use query scoped named sets, because they are subject of implicit autoexist with subselects. However, this approach isn’t applicable to Excel 2007, since it is up to Excel 2007 to generate MDX queries, and the user can only control the expression for the calculation inside MDX Script.

So the question that gets asked is “Is it possible in AS2005 to detect and obtain subselect restriction from the MDX calculation?”. Up until now the answer to this question was “No”. I have thought about it, and I found a way how to make the answer to this question “Yes”. I must warn, that the solution is neither elegant nor efficient, and probably is mostly of the theoretical interest. I wouldn’t recommend using it in the real implementation. Still, it is interesting one in my opinion.

Let’s start with the assumption, that the lowest attribute on which multiselect is possible has only few members. More precisely it should have 63 or less members. We will see how we can lift this limitation later, but for now let’s go with it. For our example, we will choose [Ship Date].[Calendar Year] attribute, which only has 4 members. For the calculation itself, let’s pick example of calculating the maximum yearly sales. The classic formula for this is below:

Max(Descendants([Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])

Indeed, if we run the following query

WITH
 MEMBER Measures.[Max Yearly Sales] AS Max(Descendants([Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])
SELECT
{Measures.[Max Yearly Sales]} ON 0
FROM (
 SELECT  { [Ship Date].[Calendar].[Calendar Year].&[2001], [Ship Date].[Calendar].[Calendar Year].&[2003] } ON 0
 FROM [Adventure Works])

When we run this query, the result is $10,158,562.38. Quick check query:

SELECT {[Measures].[Internet Sales Amount]} ON 0
, [Ship Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works]

it shows that the number we got was actually for year 2004. But it wasn’t even included by our subselect restriction which covered 2001 and 2003 only. So how does the solution works. First, we create another small table (or named query in DSV) which looks like following:

In general case we want to list every member in the lowest attribute by which the multiselect is expected, and assign values which are powers of 2. The idea here is that each member then gets assigned a bit in the 64-bit representation of the integer. Adding Encoding values for different members will be equivalent for OR’ing their representative bits.

Next we build a small measure group off this table, by linking Year column to the [Calendar Year] attribute in the [Ship Date] dimension, and Encoding is used as a measure with Aggregation Funciton Sum. But due to a special nature of Encoding values, the Aggregation Function here really behaves as OR.

This is the root of the idea. When there is a subselect restriction, all physical measures get automatic Visual Totals applied to them if the coordinate [Calendar Year] hasn’t been overwritten by formula. Applying Visual Totals to the Encoding measure will mean that it will be equal to a number, which has bits set to 1 only for those members which participated in subselect ! Now all we need to do is to decode back from the bitmask to the MDX set. It may seem that the easiest way to do it is to run Filter over [Calendar Year] and do filtering by AND’ing bitmasks. However, this won’t work, since using Filter over [Calendar Year] will cause [Calendar Year] attribute to be overwritten, and Visual Totals won’t get applied - the trick won’t work. Therefore, we need to do something a little more sophisticated - write a special purpose stored procedure which will behave exactly like Filter, only without coordinate overwrite. Such stored procedure is simple to write, below is the full source code for it:

        public Set DecodeSet(Set set, System.UInt64 Encoding)
        {
            SetBuilder sb = new SetBuilder();
            System.UInt64 bit = 1;
            foreach (Tuple t in set.Tuples)
            {
                if ((bit & Encoding) != 0)
                    sb.Add(t);

                bit *= 2;
            }

            return sb.ToSet();
        }

Now, the expression for the calculated member will look like

Max(ASSP.ASStoredProcs.Util.DecodeSet([Ship Date].[Calendar].[Calendar Year], [Measures].[Encoding]), [Measures].[Internet Sales Amount])

And we can verify that it works correctly by running the following query:

WITH
 MEMBER Measures.[Max Yearly Sales] AS Max(ASSP.ASStoredProcs.Util.DecodeSet([Ship Date].[Calendar].[Calendar Year], [Measures].[Encoding]), [Measures].[Internet Sales Amount])
SELECT
{Measures.[Max Yearly Sales]} ON 0
FROM (
 SELECT  { [Ship Date].[Calendar].[Calendar Year].&[2001], [Ship Date].[Calendar].[Calendar Year].&[2003] } ON 0
 FROM [Adventure Works])

This is nice, now back to the case when we have more than 63 members for the attribute. For example, we want to go over days, not over years, and in Adventure Works we have 4 years worth of data, which translates to 1158 days. 1158 > 63. Well, the only solution in this case is to break the attribute into groups of 63 members and assign dedicated measure to each group. In our case this makes 19 new measures. It is not pretty, but at least it can be automated. The sproc will have to change to accept set as a second parameter, where the formula will pass MeasureGroupMeasures(”ShipDateMultiselect”), and sproc will have to make nested loops in order to move to the right member for the right measure. The exact code is left as exercise to the reader, if the reader is still interested.