Rittman Mead - Delivered Intelligence

Whilst in some ways it’s good that you can bring data from different data
sources into Oracle BI Suite Enterprise Edition, this is only of limited value
if individual fact tables and dimensions are only made up of data from a single
data source. Your sales fact table might take data from a relational Oracle
table, whilst your budgets fact table might be sourced from an Excel
spreadsheet. What would be of real value would be creating fact tables that
bring in data from different sources, with BI Server joining the data together
in the background; for extra points, the data sources should be of a different
type, so that for instance sales from an Oracle table are joined with quota from
a spreadsheet, or sales projections from an Analysis Services cube. So how do
you get Oracle BI Server to put this all in place?

In this scenario, I have some sales data taken from the Global Sample Schema,
that shows units sold by product, customer, channel and time, like this:

I’ve also got an Excel spreadsheet, put together by my sales
manager, with the sales quota for each Warehouse for 2002, 2003 and 2004.
Ideally, I’d like to make this quota information available in Answers, reading
directly from the spreadsheet but integrated in with the other measures in the
Units fact table. The spreadsheet looks like this:

Unlike the Oracle database and it’s external tables, BI Server
can access Excel spreadsheets directly using ODBC, with Excel presenting
worksheet named ranges to the ODBC driver as regular tables which the BI Server
can then select against (technically, Oracle can access Excel through ODBC, but
it’s complicated to set up and most people save to CSV and access through
external tables instead). The first step then is to create a named range around
the data and called it "unit_quotas" - this is the "table
name" that Excel will present to the ODBC interface.

I now create a System DSN that points through to this Excel
file, after copying it onto the server that’s running the Oracle BI Server.

At this point I can import the "table" into the
Physical Layer of the Semantic Model, using the Administration Tool. One thing
to bear in mind here is that Excel is not an officially supported data source,
and has it’s own quirks that you need to be aware of - you should uncheck the
"Count Distinct Supported" option in the Features tab for the data
source, and you should disable connection pooling as the driver doesn’t support
this. Once you’ve done this though and brought the data, your physical layer
will look like this:

Now comes the clever part. To associate this data with
the existing Global Electronics data in the business model, you need to drag the
Warehouse item within the Global Quotas "table" and drop it on top of
the Warehouse item within the Customer folder in the business layer, like this:

You obviously can’t see the dragging there, but I just dragged
the physical Warehouse item and dropped it on top of the logical Warehouse item
in the Customers folder. This creates two data sources for the one logical item,
and effectively "joins" the two data sources on this column. You can
see this when you view the properties for the Warehouse item:

I then do the same for the Year item, dropping it on top of the
Year logical column in the Time logical table.

The next step is to drag the Quotas column from the Global
Quotas physical "table" and drop it into the Units logical table.
Then, because the quota data is only at the warehouse and year level, not
available for all levels of aggregation or all the dimensions, I need to edit
the logical table source and say what are the lowest levels of aggregation it
(the quota column) is available for, like this:

Just to recap, what I’ve done here is say that the Warehouse and
Year columns in their logical tables have in fact two sources - the Global
Sample Schema tables, and my Excel spreadsheet. Then, when I bring the data
together by requesting both Units and Quota data, the BI Server will go away and
get the data from both sources, and join them together on the way out.

Before I display the results, there’s one final thing to do.
Because my Quota data was only for certain years - 2002, 2003 and 2004 -
browsing the time dimension will now only return values for 2002, 2003 and 2004
as they were the only values common to both data sources, even though the Global
relational data source had years going back to 1998, so that if you ran a quick
query to display just the Year column, you’d get just these years back.

To get around this problem, there’s one further step to do that
seems a bit strange at first, but sets up the business model metadata so that
you can browse the time dimension properly again. The first step is to go back
to the Administration tool, and copy the lowest level from each dimension -
Item, Ship To, Channel and Month - into the logical fact table, change the
aggregation type to COUNT (any aggregation will do), then change the names to
reflect the aggregation type, ending up with a fact table looking like this:

Adding these new columns in automatically brings in references
to the logical tables that they came from, and the next step is to double-click
on the TIME_DIM table - the one that we’ve got the issue with - and set the
logical level for each dimension other than the time one as being the grand
total level, and the time one as being the detail, lowest level, like this:

You only need to do this for the time dimension, the others you
can just leave at the default.

By doing this, we’re taking advantage of the fact that, when
selecting it’s dimensional sources, the BI server starts off at the fact table
and then works back to the dimension tables. Having sources at grand total for
all other dimensions makes these sources look the most efficient to the query
navigator in the server.

Now, when you list out the Year column, you get all of the
years, not just the ones that are in both the Oracle table and Excel sources.

Now, I can finally test out the new measure, by creating a
crosstab showing Units and Quotas over all years for a selection of Warehouses.

And there you have it - quotas displayed for just the years they
are for, all the other years displayed OK, and completely seamless to the user.

Just to recap then, what we’ve done is set up the semantic layer
so that a single logical query in this case takes data from two physical queries
- one from the Oracle database, one against the Excel spreadsheet. To do this
we’ve not needed to create joins between logical tables, we’ve just told the BI
server that two dimension columns - Warehouse and Years - have more than one
data source. The missing rows in the quota table don’t affect the final output,
and the data in the spreadsheet doesn’t need to be at the same level of
granularity as the existing data.

I’d like to say that I came up with the inspiration for this,
but in fact this is something that Kurt Wolff covered in the BI Suite Enterprise
Edition hands-on training at ODTUG this year, so thanks go to him for explaining
how to do it. It’s a bit tricky to conceptualize at the start, but once you work
through an example it becomes a bit clearer - the joining of two data sources is
fairly straightforward but the bit at the end to sort out dimension browsing had
me scratching my head for a bit. Hopefully others will now get to use this and
see how it works.

And for my next trick - bringing in an MDX data source…

Posted in Uncategorized |