One of the products Oracle are currently pushing very heavily is Oracle BI Cloud Service (BICS). BICS allows customers to leverage the features of Oracle Business Intelligence (a limited set of them at least) without the need for on-premise hardware, maintenance overhead, upgrade management and so on. Data can be either uploaded directly into the tool or can be scheduled directly from a source system (such as an Oracle E-Business Suite environment) using Oracle’s new tool BICS Datasync. I emphasize “new” because it actually appears to be a re-brand of the old-faithful Database Administration Console (DAC) used in the legacy BI Applications 7.9.6.x.
Whilst BICS is clearly still a product that has a long road to journey upon before it contains the same functionality as the full OBIEE product, one great thing is that you get a little sneak preview of the kind of features that one can expect in the next up and coming major releases of OBIEE.
As with the downloadable SampleApp virtual instances that can be downloaded from Oracle, the BICS subject area comes with the Sample Sales objects installed. This is all very well however sometimes it can be a little “boring”, so to get something a bit more meaningful and useful I went for a look around the UK Government Data portal. This is a great resource for getting real-world public-domain datasets of all sizes. I eventually choose the NHS Sickness Absence Rates data set, for no other reason that it was reasonably sizeable (30k+ rows) and broadly interesting to someone who generally works with HR data. I loaded this quickly into a staging table using the BICS upload tool and then created some basic dimensions from those.
create table xxnhs_dates as select distinct abs_month date_key, to_date(abs_month,'YYYY-MON') date_val, to_char(to_date(abs_month,'YYYY-MON'),'Mon') date_month, to_char(to_date(abs_month,'YYYY-MON'),'YYYY') date_year from xxnhs_sickness_absence; create table xxnhs_regions as select distinct hee_region_code region_key, hee_region_name region_name from xxnhs_sickness_absence; create table xxnhs_orgs as select distinct org_code org_key, org_name, org_type from xxnhs_sickness_absence; create table xxnhs_sick_absence as select abs_month date_key, hee_region_code region_key, org_code org_key, fte_days_sick, fte_days_available from xxnhs_sickness_absence; drop table xxnhs_sickness_absence;
Ok, I know that there was no need to be doing multiple conversions on the date dimensions but in the interests of copying and pasting, it’s what worked out the quickest for me at the time!
Anyway, once that was done it was then time to create my data model. No Admin Tool needed here – just the (quite basic at the moment) online model tool.
Unfortunately one of the key restrictions in BICS at the moment is that you can only have a single subject area (without using DAAS – Database as a Service). So we therefore have to build within the seeded subject area for now. Anyway, all that saved back we can start to use the data. Here is a Visual Analyzer project, tool which is a more dynamic version of OBIEE Answers, allowing multiple views to be combined in a single project where data of interest can be focussed and drilled into.
The TreeMap View introduced in the latest OBIEE version is a great tool for visualizing relative contribution to a measure. You can see this blog post by Mark Daynes for further information.
Funtionality from the existing product line is of course still available, as is demonstrated below using a trellis chart to show annual performance by organization type.
So there’s a very quick demo from start to finish of something that’s (hopefully) a bit more interesting than Sample Sales data! In fact the data.gov.uk holds some genuinely interesting data sets. If you are starting out getting to grips with a tool such as BICS/OBIEE then I’d definitely suggest using something like this. Using your own data can get users a little too tied down with the detail at times and obscure the bigger picture, whilst Sample Sales is quite often too abstract.
As I mentioned earlier, there still looks to be a reasonable way to go for BICS to be able to compete against OBIEE from a functionality perspective however it’s certainly on its way!
Contains public sector information licensed under the Open Government Licence v3.0