Day One of An OBIEE Project : Data Modeling

Yesterday I mentioned that I was working this week on an OBIEE proof of concept, I said at the time that I’d make some notes on how the project went and the methodology I used to pull the system together. As I obviously can’t write about the actual client or system I built, I’ll use the example of a university BI system in the examples and you can apply this back to your own particular situation.

The work I’m doing was originally planned as two phases;

1. An initial phase to put the OBIEE data model together, and
2. A phase where the reports and dashboards were generated.

As there was an expectation that the data might not be quite in the correct format on day one to report on, doing the work in two phases gives the client a week or so between my visits to create additional database tables or anything else that’ll make the reporting easier. The plan is for OBIEE to run directly against the source application, which in this case handled applications for courses that the university offers.

I turned up at 9am and had the initial meeting with the client, after that I was given the following:

1. A set of database diagrams showing the main areas of the application schema.
2. A login to the database, and SQL Developer to query the tables, view the schema and so on
3. Some example reports that they currently produce using Excel
4. Access to the business analyst and application designers to answer my questions and add a bit of context.
5. OBIEE installed and configured and ready to work with.

So, as projects go, this was not a bad start. Just to double check that everything was set up correctly, I logged in to OBIEE BI Administrator with the default Administrator/Administrator login, checked that it all looked OK, created an ODBC connection from the server through to the source database and then used BI Administrator to read in the table metadata, ran some row counts and generally made sure everything connected up OK.

Now that I was confident the software was set up correctly, I took some time out to discuss with the client the area we’d be reporting on (for the purposes of the example, the student registry at a university), and tried to identify the main business processes that made up their part of the organization. OBIEE needs the logical (or business) data model it works on to be a dimensional star schema (fact tables surrounded by dimension tables), and so through doing this I was trying to establish the facts (which generally are based on business processes, like students applying, offers being made, students being admitted, grades being published, courses being attended and so on) and the dimensions (the “actors” and “products” in the business process, in this case students, courses, faculties, sites, periods and so on), with the dimensions then being used to analyze the fact tables. All of this is actually all basic dimensional modeling, it’s not specific to OBIEE, and the deliverable out of this part of the work is a conceptual dimensional model that shows the flow of data from one end of the business process to the other.

Once I carried out the first interviews, I logged on to SQL Developer and exported a list of the tables in the source schema into a text file, which I then cut and pasted in to Excel. I then went through the table list and categorized each of them into candidate fact tables, and candidate dimension tables. As a general guide, candidate fact tables generally contained transactional information (student admissions, the qualifications that students stated on their applications, the offers that were made, the various documents that were sent out during the application process) whilst dimensions contained the lookup or reference information that generally joined to the transaction tables. In most cases, information on students, say, was split over several tables (student_details, student_type, student_address and so on), when we come to use these tables in OBIEE we’ll need to denormalize them in to a single logical dimension table.

At the end of this exercise, I had a list of candidate fact and dimension tables, which I then matched back to the notes I made when I spoke to the business analyst. This then gave me my initial fact and dimension tables that I would build in OBIEE BI Administrator, some of which would map one-to-one with the source tables, some of which would be a combination (denormalization) of several source tables. This, in a way, is the conceptual, theoretical dimensional model that represents the business and it’s business processes, and could be used along with OBIEE to provide general ad-hoc reporting for the client’s application. To take one example in particular, the candidate “applications” fact table contained a count of applications and the grades score of the applicant, and links through to a number of dimension tables including application status, which could be set to received, in processing, rejected, offer sent, in clearing and so on. A diagram of this fact and it’s dimensions would look like this:

As I said, so far, so good. The next stage was to take a look at the reports that the customer needed. And this was the interesting part – the first report, the most important report that they needed, wasn’t a listing of student types by faculty with application status, say, as a page item, it was a very long tabular report with a number of measures across the top that reported on applications at various stages and over varying time periods, something like this:

Now if you’ve tried to produce this report with the sort of dimensional model I’ve got above, you’ll have probably spotted the problem. The columns across the report represent applications at different stages, and any one measure you can obtain by selecting the correct value from the application status dimension and totalling up the application count. In this report though, you’ve got the same measure displayed over and over again, but not for all application statuses, just an arbitrary selection of them. Moreover, some of the measures are for one week, some are for a quarter and some are year-on-year comparisons. Now with an OLAP server and OLAP query tool such as Essbase and Web Analyzer, making these selections of measures by arbitrary application statuses and time periods is not a problem, but for relational query tools such as OBIEE it’s a bit more of a problem, especially if you’ve got more than one measure and each measure has an arbitrary selection of statuses and time periods.

The way I’ve got around this on other OBIEE projects is firstly, to break out each measure by application status and have it listed in the logical fact table, and create additional measures that take your base measures and calculate it over specific time periods, such as month-to-date, year-to-date, year-on-year comparison, and so on. This has the drawback of “exploding” your fact table model from one or two measures to one per status and time period, but it gives you a particular column you can select for each combination of measure, period and status, with the complexity of populating them handled by SQL DECODES and the Time Series (AGO and TODATE) calculations you now get with OBIEE 10.1.3.2 onwards. What this all leads to is a logical model that looks something like this:

So breaking the measures out like this gives me the ability to list applications by status across the report page, once I’ve got these base measures I can then use the time series calculation abilities of OBIEE to calculate totals over different time periods. And that’s where I stopped for the day, tomorrow I’ll finish off the model and produce the first iteration of the reports, which is interesting as the original plan was to create the data model in the first week and the reports in the second, but in reality you can’t really produce the data model to any sort of final degree until you’ve seen how data is used in reports. It’s because of issues like this that I try and encourage clients to build their initial OBIEE data model against source data in place, to drive out all these reporting “oddities”, before committing the data model to a data warehouse and doing all the work around ETL – it’s very easy to change the model around at this stage as it’s only “virtual”, if you’ve just spend six months populating a data warehouse of this design though, and then you find you need to break out the measures in this way, it can be a bit annoying.

Anyway, as I said that’s it for today. Tomorrow I’ll be putting in place all the DECODES, time-series queries and so on and producing the first reports. After that I’ll be building the logical model out, adding some additional fact tables and rounding off this stage of the logical model build.

in

Days Two and Three of an OBIEE Project : More Data Modeling

The other day I posted some notes on the first day of an OBIEE project, I’m just on the train back now after finishing the next couple of days, I thought I’d post some notes on how these two following days went as some interesting things came up during the two days of development.

The first issue we came across related to the technique I talked about in the previous post, where we broke out measures by a “scenario” or in our case, an “application stage” dimension. The original problem we had was that our fact table source had a set of measures that were dimensioned by an application stage dimension, and the client wanted a selection of these measures by application stage listed across the report. If they wanted all of the measures by a selection of application stages, or a selection of measures by the same set of application stages we could have used a crosstab and had application stage as one of the dimensions, day of month (say) as the other and we’d have been fine, but as the report used an arbitrary selection of measures and application stages, the only way around this was to “pivot” the fact table by the application stage and derive out the measures using a CASE statement, like this:

This would then lead to this measure appearing in the logical fact table as a calculation, rather a normal logical column mapped to a physical column. Notice in the list of measures below how it appears using the formula icon.

All of this worked well when displaying data from the fact table in detail form, i.e. with one report row per row in the fact table, which in my case equated to each event that was recorded against the course application. The problem came though when I needed to aggregate the data up to the day, or month or whatever level – the derived measures wouldn’t aggregate. If you go to edit the properties of the derived measure, the Aggregation tab is greyed out, and if you try and wrap the CASE statement with a SUM() aggregation, OBIEE complains that you’re using a deprecated method of aggregating measures – basically you can’t try and slip in a “double aggregation” by adding the SUM() clause to the logical column definition.

What you can do is to edit the column formula in the Answers report and add your own SUM() clause around the column definition, this seems to trick OBIEE in to adding the additional aggregation and your measures then roll up properly in to day, month and so on. But for us, this was going to be a bit complicated for end-users to understand – some measures aggregate properly, some require manual intervention, so we took a different route and created a view over the original fact table that broke out the measures in the same way, re-mapped the logical fact table to use this view instead and the measures then aggregated with no problem, as OBIEE just saw them as regular table or view columns.

We ended up doing this sort of “substituting OBIEE data transformation techniques with SQL views” thing at several points in the build, mostly when we were trying to do something complex – like, say, mapping an aggregate table on to a logical table that itself was derived from the fact table plus some calculated columns – which confirmed to me that to be good at developing using OBIEE, you’ve got to be pretty good with both the OBIEE tool and the underlying database underneath. In some circumstances, it makes sense to transform data and create calculations using the OBIEE Administrator tool, in other circumstances you’re better off doing the transformations and calculations in a view or an ETL process and then mapping OBIEE on to this updated physical data model. My advice in terms of getting data together for OBIEE is to try and do as much work as you can in the physical data source – ideally, transforming your OLTP data into a denormalized star schema – so that the mapping you create in the OBIEE metadata model is as simple as possible. You can certainly do a lot of clever things in OBIEE to transform normalized data, but the more abstractions you heap on top of other abstractions the more chance their is that OBIEE will trip up at some point and say that this certain combination of transformations isn’t valid. A lot of this is just trial and error, and experience you get from doing a few implementations, I guess the advice I’d give is to be flexible around your use of OBIEE transformations, creating views and materialized views over your source data and using ETL code to tackle more complex transformations, in the implementations I’ve worked on we usually use a mix of all three techniques.

The other interesting issue we had was around the time-series calculations we needed to create. On projects I’ve worked on in the past, we’ve typically had, say, a Vale measure that recorded the value of a particular transaction sale. When these were aggregated up to the day level, the measure showed the total sale value that day. If you then use the TO_DATE and AGO time series functions in OBIEE you can calculate that measure rolled up to a particular month, quarter or year, or for a particular month, year or quarter (or any other time period) offset. For this project though, the time offsets were pretty arbitrary – one measure had to be calculated based on a 30 day period offset by a number of days before the day of the report, others had complex conditional clauses where you could count an application if it was current but not declined except if the applicant was in clearing or something similar. One of the measures didn’t actually relate to applications at all, or at least it couldn’t be derived from individual application activity records, and so the decision was taken to not to try and derive these measures from the fact table, but to create a summary table, with a granularity of one row per day, that we’d then populate either through some Oracle SQL (potentially using analytic functions or subquery factoring), PL/SQL or Oracle Warehouse Builder. We’d then map this summary table on to the existing logical fact table, like this:

Then we’d make sure we’d defined a dimension over the Times logical dimension table that had a day level, and tell OBIEE to only use it at certain levels of aggregation.

Answers would only then show these day-level figures when a report was generated at that level, but the measures would appear in the same fact table as all the detail-level measures, giving the impression to users that it’s all part of the same data set. One thing we gave a bit of thought to was how to populate this summary table – we could either write some PL/SQL or use an OWB mapping to populate it overnight, which would have the benefit of, once the table was populated, providing access to these aggregates very quickly, but would have the drawback of the values only being updated once a day. We could use a materialized view using fast refresh, which might potentially give us a combination of up-to-date data and stored totals, or we could try and generate the total dynamically using a view. One thought we did have was to use the Fragmentation feature of OWB to get the best of both worlds – we could use PL/SQL or OWB to create an overnight set of totals and then combine this with an SQL view over todays data, using Fragmentation, to provide fast access to historical aggregated data todays data as well, with totals generated on the fly, if more current data was needed.

Anyway, what we then found was once we’d cracked this main fact table, which pretty much contained all the data on their main business process, the rest of the model came easily. Taking a look through some of the other reports needed, we noticed that we could satisfy all of them by adding a couple of extra dimensions to this main fact table, and taking one of the dimensions – Faculty, say – down to a lower level, School. This was pretty straighforward to do; we amended the SQL view that provided the fact table data to include the extra dimension keys, re-imported it into the repository, the foreign key links were remembed by the tool, we then generated the other dimensions and mapped those on to the model. In terms of how the time broke down, over the three days it was about 25% initial analysis of the business process and sample reports, 50% producing the first, main fact table, and then the remaining 25% was spent either enhancing this fact table, adding extra dimensions or the odd small fact table that ran alongside the main one. I’ve done a fair few OBIEE implementations now and this one reinforced my view that to be good with OBIEE, you need to have three main skills

• Knowledge of OBIEE, specifically how the physical to logical model translation works, how to use Logical Table Sources, how to derive measures, how to create joins in the Logical and Physical models, and in particular (and this is where experience on deployments, rather than demos comes in) knowledge of all the little quirks, like the non-aggregatibility of calculated columns issue I mentioned earlier on, how to resolve self-joins and multiple joins between tables through Aliases and so on.
• Knowledge of dimensional modeling, particularly how to pick a grain for your fact table, how to design a fact table that satisfies the most amount of queries, how to design a set of facts and dimensions that gives you analysis across the business – I spent most of my time on this and it’s not specific to OBIEE, it’s more to do with dimensional data warehouse design and the best way to read up on this is to read the Kimball books.
• Knowledge of your database platform, so that you know when it’s best to transform and calculate data in OBIEE, when it’s best to do it in the database, and when you’re going beyond what you can do on the fly and you should be using a data mart and an ETL tool such as OWB. Getting this balance right between doing it in OBIEE and doing it in the database is key I believe.

One last thing that I think’s worth mentioning before I wrap up is a couple of quirks around OBI SE One, the “cut down” version of OBIEE that’s aimed at smaller deployments of 50 users or less. Most people are aware that whilst most of the OBIEE products are in OBI SE One (what you lose is mainly Delivers, for example), a couple of interesting things that are also not included are firstly, the BI Office Plugin, so you can’t analyze Answers data in Excel or Powerpoint for example, unless you run it all through BI Publisher and use the BI Publisher add-in instead. The other thing that caught us out was that the version of BI Server that it ships with is 10.1.3.1, which is several releases behind 10.1.3.3.2 that’s currently shipping as part of OBIEE – which means that the EVALUATE and EVALUATE_AGGR functions are not present, which we were looking to use to get the Oracle database to perform some analytic functions for us. I’m not sure if license-wise you can update the BI Server element of OBI SE One to the latest release, but it certainly caught us out and pushed us more towards the SQL view solution.

Anyway, that it for me for a week or so, I’m leaving the client team to finish off the model now and start producing some of the reports. When I come back I’ll help them fine tune some of the data model and report layouts, then we’ll be adding row-level security to the model which we’ll do using OBIEE logical table filters rather the Oracle VPD, as their underlying Oracle database is Standard Edition which I don’t think supports VPD.

in

OBIEE Dimensional Data Modeling Redux

Several months ago I wrote a blog post on pulling together an Oracle BI Suite Enterprise Edition business model from a normalized data source. At the time I was working through in my own mind how best to join different data sources together, how to make use of logical and physical joins, how logical table sources worked and so on and I got some useful feedback from Adrian Ward and Martin Hammer on how best to make use of the data modeling features in Oracle BI EE. I’m now sitting on the flight going over from Auckland to Melbourne and practicing the various demos I’ll be doing tomorrow, one of which is on this subject, so as I’ve got a couple of hours to spare I thought it worth jotting down my current thoughts on the subject so it’s fresh in my mind.

In the example I use, I have a normalized data source that’s based on the SOADEMO sample schema that comes with theOrder Bookings SOA Suite demo. The source data model looks like this:

It’s a fairly simple normalized model, where orders break down into items, which reference products, and with orders being referenced by the time of the order and the customer who placed the order. Many customers can have many addresses, with the intersection between individual customers and addresses being held in an intersection table.

Within BIEE, you always try and represent the data in the business model in terms of a star schema. Using the Kimball methodology as the start point, the star schema contains one or more fact tables, which reference and re-use one or more dimension tables. Ideally, the fact tables contain just measures and foreign key links to the dimensions, which themselves contain information on the things you “slice and dice” measures by, such as the customer income band, product category and so on.

So how do you go about converting this source data model into a star schema? Well, the first thing I would do is identify the lowest-level transactional information in the source database, which in our case are orders which are made up of one or more order items. In the star schema that I’m going to built, this is the grain that I’m declaring, so I want to base my fact table on a combination of the items and orders tables.

To do this, I create a new business model that I’ll call Customer Orders, and drag and drop the items table from the source, physical layer over to this new business model to create my first logical table. Looking at the columns in the logical table, I can see IDs for the order and the item, together with some measures and some information on the product that’s been ordered.

Now I know from looking at the rest of the physical data model that this product information is essentially duplicated from the products table, and according to the Kimball methodology, it should be taken out from the fact table and placed in the product dimension, if it’s not there already. So I remove the PARTNUM and PRODUCTNAME columns from the logical table and leave just the measures and the links through to the other tables.

One more step that’s useful is to double-click on each of the measures, and use the logical column dialog to set the default aggregation method for each measure, otherwise Answers will display fact table data as detail, rather than aggregated.

Now I’m in the situation where I’ve got the details of the order items in the logical table, but I’m missing the details on the customer and the date of order, which are in the orders source table. To bring this across, I first make sure that the physical data model in BI Administrator has physical foreign keys registered between the source tables, and then double-click on the items logical table to edit the logical table source.

To do this, I click on the Sources tab after double-clicking the items logical table, and then double-click on the items logical table source in the dialog box to start editing it.

When I first created the items logical table, by dragging the items physical table over to the business model, the logical table had a single logical table source which maps to the items physical table. What I’m going to do now though is press the Add button below the items logical table source to add the orders physical table to it, which I can do as the two tables are linked together in the physical layer by a physical foreign key.

When I add an additional physical table to the logical table source in this way, I make sure that BIEE still performs a single SQL statement to get data from the two tables, and I can now go back to the logical model and start dragging columns from the orders physical table into the items logical table. If I just simply dragged items from the orders physical table across to the items logical table without performing this step, BIEE would set up a second logical table source for the items logical table, and perform two SQL queries, one for items, one for orders, to get the data back. If I just dragged the orders table across to the logical model and created a second logical table, I’d end up with some of my dimensions joining to orders, some to items, and I wouldn’t have a valid star schema.

Once I add the second table to the logical table source, and then drag and drop the orders columns into the logical table, my data model looks like this:

If you’re wondering what the blue splotches are next to the tables and columns, it’s because I’ve not checked the table into the repository yet – I’ll do that in a moment.

So now, I’ve got the fact table for my star schema, with a grain of individual order items, and links through to customer, time and product dimensions. Next then, I drag and drop the times and product tables across from the source physical model, whereupon the model looks like this:

Notice how BIEE thinks that all of the tables are fact tables (they’re highlighted in yellow, which is how BI Administrator indicates fact tables) – this is because BI Administrator doesn’t bring across the foreign key relationships if you import tables in separate stages, it only preserves them if you bring across both sides of the foreign key relationship at the same time.

To let BIEE know about the table relationships, and to tell it that products and times are actually dimension tables, I highlight the three logical tables and select Business Model Diagam > Whole Diagram from the context menu.

I then use the menu bar to create complex joins between the dimension tables and the fact table.

Complex joins, as opposed to foreign key joins, in the logical layer merely tell BIEE that there is a relationship between the two tables, and it leaves BIEE to come up with the most appropriate way to join the two tables, based on the metadata in the repository and the joins in the physical layer. If you instead create these joins as foreign key joins, you proscribe exactly the way that the two tables will join, for example from customer ID to orders, which may not be appropriate if your logical dimension table has multiple logical table sources for different levels of aggregation.

Looking back now at my logical model, just the items table is marked as a fact table, with the other two tables now being recognized as being dimension tables, as they have a one-to-many complex join relationship between them and the items table.

Now it’s time to add the customer table. This gets added as another logical table to the logical model, and I use the same technique as before to join it to the fact table with a logical complex join, so that it’s recognized as a dimension table.

This time though, I’ve got two more physical tables, customer addresses and addresses, to add to the logical model. As they join together in the physical model, I can just edit the customer logical table source and add the two physical tables to the logical table source, like this:

Then, as with the columns in the orders table, I can add the required columns from the addresses table to the customer table, to add address information into the customer dimension table.

And that’s it in terms of creating the star schema. All that’s left now is to create the dimensions that define the drill paths down the dimension tables, like this:

Finally, it’s just a case of dragging the logical model over to the presentation layer to create a presentation model, and I can then start querying the star schema using Oracle Answers.

And that’s all there is to it. Another hour to go on the flight, time for an episode of Extras on the in-flight entertainment.

in

OBIEE Data Modeling Tips #1: Integrating 1-1 and 1-Many Source Tables

Here’s a few data integration techniques that you can use with Oracle BI Server and the Oracle BI Administration tool. In this scenario, we’ve got a physical data source that’s a typical normalized transactional schema; the orders data that will go into our fact table is split into an order header record, and an items record, whilst the customer information is split into a customer table, an addresses table, and a customer address intersection table. What we’re looking to do is to build, in the logical and presentation layer, a simple three table star schema containing an orders fact, and customer and product dimensions.

The first task is building the fact table from the order header and items source tables. To do this, you first import the items physical table into the logical layer, as this contains records for each item that makes up the order, which is the level of granularity that we want the fact table to take. Then, I import the products table into the logical model, and join it to the items table using the common Product ID column, giving us this schema layout:

… and this logical model

Now I want to add the information in the Order header table to the main, Items, fact table. To do this I add the Orders table to the model, and join it, just like a dimension table, to the Items fact table, as there’s a one (order header) to many (order items) relationship between the two tables, like this:

…giving us a logical model that looks like this:

Now although the order header information (customer ID, order date and so on) can be accessed from the Order header table, just like a dimension lookup, ideally I want to put all the order information into the one fact table and dispense with this pseudo-dimension table, at least from a presentation perspective. Therefore, I create new logical columns in the fact table that are just copies of the corresponding tables from Orders, so that they’re all in one place and I can remove the Orders table from the presentation layer. The logical, derived columns look like this:

…and when viewed as part of the logical mode, they are shown as formulas (derived columns). This way, I don’t need to include the additional Order details table in the presentation layer, I can just use the more granular Items table as my sole fact.

Then I add the Customers table to the logical model, and then join it to the Orders table using a Complex Join, using the default settings, like this:

Now, I need to add the customer address information in. Now in the particular case of my data, each customer only has one address, so there’ll be at most one entry in the intersection table for each combination of customer ID and address ID. As there’s therefore a one-to-one relationship between customer, customer address combination and address, I can therefore add the address data by first dragging and dropping the Customer ID from the Customer Address intersection table on top of the existing Customer ID column in the Customer table, like this, to create a join between the tables:

Then I add the Address ID from the Intersection table to the Logical Customer table; note how this table now has two sources, the physical Customer table and the Physical Customer Address Intersection table, just like my previous example last year where I integrated Oracle and Excel data into the same fact table.

We can copy the column from the Address Intersection table into the Customer logical table, and create a second table source, because there’s a strict one-to-one relationship between these tables and they join on a specified column, so the BI server can go to either or both tables to get the required column. Earlier on, where we had a one-to-many relationship between products and items, and orders and items, we join the tables instead and create a derived logical column to obtain the “dimension” attributes we need.

Then I do the same thing again with the physical Address table, joining it on the newly-imported Address ID logical column and then adding the rest of the address data as additional columns. See now how the logical Customer table has all the address details as well, and it’s got three physical data sources, all joined.

Finally, I add just the Items, Customer and Products table to the physical layer, to keep the user table selection simple, like this:

and then go and load up Answers and create some simple reports that use data from all of the tables, like this:

Anyway, that’s fairly basic stuff but it’s useful to know how to integrate OLTP-style data models into simple stars, and how to incorporate data from tables with one-to-one and one-to-many relationships into the logical model. I’m sure there’s other ways of doing this (BIEE seems to have multiple ways to do common things), as usual feedback and other techniques are appreciated. Tomorrow, to make things a bit more interesting, I’ll be covering something called “Fragmentation”.

in