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…
Posts
1.Created the spreadsheet and imported the same into the Business model.
2.Mapped the columns from the physical table source to the logical table source.
3.Specified the aggeration levels also.