ODI 12c new features: Dimension and Cubes! Part 3 (Settings for Surrogate Keys)

Hi all! First of all, sorry for the delay. We really wished to have published the rest of this series earlier, but we are overwhelmed by projects, which keep us very busy. So let’s not waste time and go directly to what matters. I really recommend you to ready part 1 and part 2 (if you didn’t already) because we will assume some things here that were already done, so we don’t keep repeating ourselves.

Today’s post is how to setup ODI dimension objects to work with Surrogate Keys. In the first post we said that there was a bug in ODI 12c that was preventing us to create dimensions with SKs. We opened an SR with Oracle and it turned out that it was not a bug, but it was some missing configurations that were not enabling us to create the objects in the right way. So, apologies to Oracle 🙂 I hope this post may explain those little specific setups, so other people does not fall on the same mistakes that we did when we tried to create these dimensions.

First let’s begin with the DB script for this example. Our source tables will remain the same as the previous example (SRC_* tables). Our stage tables will be different and we will use the STG*S tables for this example. The final dimension/fact tables will be the S* tables found below.



Also, please create the following Native Sequences that will be used to create our SK values:



Let’s talk a little about the SK setup requirements. There are some key points that were not clear in Oracle’s documentation and that’s why we were not able to complete it successfully. After talking to Oracle Support, we got the following key requirements to make SK setup to work:

  • Each level of the dimension must have its own Natural Key and Surrogate Key columns. The SK column MUST be different to the PK of the dimension (this is very important. This was the wrong setup that we were trying to do and it was failing). This allows ODI to manage SCD type 2 changes that occur across a hierarchy (while not applicable to a Time dimension it still needs to be setup that way);
  • The dimension MUST have a Primary key defined on it;
  • Each staging table for each level MUST include all the attributes of any level above it in the hierarchy (MONTH must have all attributes of QUARTER and YEAR). The easiest way to accomplish this is to just create the staging tables to have all the attributes of the dimension. (But you may create only the needed ones. The scripts in this post only contain the necessary attributes);

Let’s get as example S_TIME table. It contains the following columns:


S_TIME has three levels and for each level we are going to have:

  • One attribute for each member name (YEAR, QUARTER and MONTH);
  • One ID (that will be setup as Natural Keys) for each member level (ID_YEAR, ID_QUARTER and ID_MONTH);
  • One SK for each member level (YEAR_SK, QUARTER_SK and MONTH_SK);
  • And finally the tables PK – TIME_PK;

After you run this ODI component (in our fourth post), you will notice that some information gets replicated on IDs and SKs. It may seem odd for you, but it is actually correct, since those objects are prepared to handle SCD2 type of data, so even if you don’t use it right now, you’ll need to setup them this way on your ODI dimensions (the good thing is that, if you decide later on to use SCD2, then the setup will be already done for you).

Now let’s create the TIME_SURROGATE dimension as below:


For level Month, do the following setup:






On Hierarchies tab, do the following setup:


For the other two dimensions, the process is very similar, so I’ll not add screenshots here. For the Cube setting, it is exactly as we did for the cube in the first post:



And that’s it, we are ready to load those components using Mappings. Our fourth post will show you the differences when using SK models and the benefits that it may bring to you.

See you soon!


8 Responses to “ODI 12c new features: Dimension and Cubes! Part 3 (Settings for Surrogate Keys)”

  1. There’s a lot of useful and good information in these blogs, thanks so much! But I am confounded by the Staging Datastore field in the Levels section. What creates these datastores? Good information on this subject is difficult to find!

    • Hi how are you? Thanks for reading our blog, we really appreciate it. About the Staging datastore, we need to create it manually before we start to use it. I know, I think the KM could do that for ourselves too but for some reason (We didn’t dig too much on the KM yet), It doesn’t.
      Basically what we did was to create table as target table and use it as stage tables. Please take a look in the first part of this series (it explains a little bit better the staging process). https://devepm.com/2016/08/19/odi-12-new-features-dimension-and-cubes-part-1-settings/
      I hope this can help you! Thank you!

  2. Once ODI has built and loaded dimension and cube data, how does it get consumed? Is it still necessary to build out Dimensions and Cubes in Analytic Workspace Manager?

    • ODI does not “know” which tool will consume the dimension and cubes. ODI is only used to make it “easier” for you to create those objects in the Oracle database. Once they are created and populated by ODI you would still need to “create/import” their metadata in other tools, like Analytic Manager or OBIEE. Maybe this “cube and dimension” ODI feature evolves in the future to integrate automatically with other tools, but right now it doesnt work that way.

  3. Anibalcrombie.Soup.io

    ODI 12c new features: Dimension and Cubes! Part 3 (Settings for Surrogate Keys) | DEV EPM

  4. julesdba Says:

    Thank you for a great set of posts. Wondering what values you have in ID_YEAR, ID_QUARTER and ID_MONTH as compared to YEAR, QUARTER and MONTH.

    Why not use YEAR, QUARTER and MONTH as Natural Keys?

    Thanks again!

    • Please take a look on post Part 4, where we display the SQL results. About YEAR, QUARTER and MONTH, we just wanted to display how “attributes” worked on this case. Althought it seems redundant and kind of useless in this case, you could have any number of different attributes sets to your dimension, so you could use them later on.


Leave a Reply to radk00 Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: