ODI 12c new features: Dimension and Cubes! Part 2 (Loading using Natural Keys)

Posted in Cubes, Dimensions, ETL, New Features, ODI 12c, ODI Architecture with tags , , , , , on September 14, 2016 by radk00

Hi all, let’s continue with our posts regarding “ODI 12c new features: Dimension and Cubes”. As stated in the previous post, we can have two ways to build our new objects: with natural keys or with surrogate keys. Today’s post will focus on loading the dimensions and fact tables that where created using natural keys (please see our previous post for all the settings required for those objects).

Let’s begin loading our TIME dimension (which was mapped to our TIME Oracle table). This dimension will have information from three different source tables: SRC_YEAR, SRC_QUARTER and SRC_MONTH. Each of them has information regarding each TIME hierarchy level, so all of them needs to be loaded in order to have a complete hierarchy in our final table.

The load process is very easy and intuitive: first create a new mapping and drag and drop the TIME dimension to it. Then, just add the three source tables, map to its correspondent level in the TIME dimension and that’s it. A very cool thing here is that ODI understands each level as a “separate” table/process, so you don’t need to join your source tables before actually loading it to the target dimension. In other words, ODI allows you to have any kind of complex ETL to each dimension level and each level will be treated as “separate” data loads that will be glued together by the hierarchy setting that you mapped in the TIME dimension object. Here is what it looks like:

blog1

blog2

blog3

blog4

When you execute the mapping we are going to see that the first “MAP_BEGIN” section will try to create and truncate our stage tables that were set in our dimension object. Here is an odd thing (as we also mentioned in the last post): We could not understand yet why ODI “forces” you to have the stage tables created prior to execution (so you can select them in the Dimension object), as it could very well create them for you (like it does for C$ and I$ tables). I know that Oracle may had a reason behind it, but as for now, the entire “stage tables” thing seems an unnecessary setup. Anyway, the important thing here is that ODI will truncate the stage tables before any new execution.

blog5

In the “MAP_MAIN” section is where it gets interesting. We can see here how ODI threats this new dimension object: each level has its own ETL, as we can see that it is loading YEAR, QUARTER and MONTH separately. First YEAR step will load its source to its stage table STG_YEAR, then QUARTER step will join the information from its source table plus STG_YEAR to its STG_QUARTER table. Finally, MONTH step, that is our leaf/grain level, will join its source table plus STG_QUARTER table (which is already joined with YEAR source) and merge it all together in our final table TIME. The result will look like below:

blog6

Since we are not using Surrogate keys here, our Dimension table will contain only the grain/leaf members with all Natural Keys and its attributes for all levels that exists in the dimension. So one row will contain all information regarding all levels that it belongs to. When we create the mappings for the other two dimensions (they’re very similar, so I’m not adding them here) and execute them, we will get the following results:

blog7

blog8

Let’s to go our Fact table load. This one is way too simple, since our source table already contains all the Natural Keys that will be the ones that will also exist in our FACT table (remember, we are not dealing with Surrogate Keys in this example). Here we just need to map each NK to its respective dimension column and also our Measure data and execute the mapping.

blog9

blog10

When we take a look in Operator, we are going to see a single merge command in our Fact table, where ODI will use all dimensions to search if that row already exists in our FACT table. If it exists, the measure column is update, otherwise it is inserted.

blog11

The final result is below: as expected, all Natural Keys from our dimensions were inserted in the Fact table, together with our measure.

blog12

Now you may be wondering, why should I use these new features if it seems a lot of work (settings) for a little gain? Well, using ODI for Natural Key’s only is really not worth it, since the only benefit here seems to be ODI loading the dimensions levels all at once, with different sources/ETL, in a single mapping object, which is a very cool feature, since it enables us to better organize our DW objects and have a clear view on our ETL logic. But again, this is too little for the amount of work that we need to do to get there. But don’t worry, it will get way better when we start to work with Surrogate Keys, since ODI will be able to abstract all the Surrogate Key management and you will start to feel that all the necessary settings will finally be worth the work.

That’s it for today folks! We will be releasing the Surrogate Key settings and load posts very soon, so stay tuned in our blog! See ya!

ODI 12 new features: Dimension and Cubes ! Part 1 (Settings)…

Posted in ACE, Configuration, Cubes, Dimensions, ETL, New Features, ODI, ODI 12c, ODI Architecture, ODI Mapping, Tips and Tricks with tags , , , , , , , , on August 19, 2016 by RZGiampaoli

Today we’ll talk a little bit about the new feature introduced in ODI 12.2.1.1.0, Dimension and Cubes!

As everybody already know, Oracle is slowly merging OWB within ODI and in each release we can see a new feature from OWB arriving in ODI. This time were the Dimension and Cubes feature.

This feature helps you to create a DW based in a configuration that you do. Basically there is a new component in ODI that helps you to define the datastore to be mapped. Also, after you create all dimensions (that is the most time consuming part in the process), the cube or fact table creation and mapping is a lot easier than do it manually.

Right now there is just one type of dimension available (Star schema level based dimension), but in the future other kinds will be supported like snow flake and others.

Ok, let’s start. There’re two ways to build a star dimension in ODI: with natural key’s (where the natural key is stored in the FACT table) and with surrogate keys (where the surrogate key is stored in the FACT table). In this post we’ll cover how we create a DW using the natural key process since the surrogate key one is buggy (the interface fails on saving the surrogate key) and we have openned a SR with Oracle to get it fixed. As soon we have the fix we’ll cover that too here in the blog.

In the Designer tab we can now see that we have a new tab called Dimensions and Cubes.

1-Dimension and Cubes

Opening that tab you will find a blank area, you need to click the button in the “Dimension and Cubes” tab, and you can create a new DM or DW.

2-DW creation

By the way, here’s the first small bug. For some reason when you write the name you want, ODI does not fill automatically the code field (as it always do for all the other objects in ODI), then you need to manually insert a code there. Remember, no spaces and no special character.

After that we can expand it and see the Dimension and the Cube node.

3-DW creation

Right click on those and we can create a new Dimension or Cube. As everybody knows, the dimension comes first since we need them to maintain the data integrity of the cube.

4-Dimension Definition

Here you can give any name you want for the dimension. Also you have a Pattern Name (that has just one option by now) and in the side tabs we have all possible options for the Dimension, Levels and Hierarchies, that we’ll cover later.

There are two more option here: the Datastore, that is the target dimension datastore where all metadata will flow and the Surrogate key Sequence that you need to set in case you want to create a dim using surrogate key (We’ll cover this later since we have a bug here).

In our case we’ll have three dimensions and one cube. (Time, Products, Regions and Fact). Both the source and the targets tables were generated by me with dummy data, just for this post. If you want to replicate this example, the scripts are here:

No surrogate Script

Let’s create the Time dimension. Click in the “Levels” in the left side tabs and you will see a big screen in three big sessions: Levels, Levels Attributes and Parent Level References.

5-Level Canvas

Let’s begin with the level configuration. Clicking in the Plus Sign button will create a Level.

6-Level Creation

I always like to rename the Level to something more meaningful like “Year” but if you like you can keep as default. By the default the target datastore comes automatically mapped since you define it in the previous screen. The only thing left here is to define the “Staging Datasore”.

This is something that we didn’t understood why it was made in this way since ODI could create automatically based in the definitions we had in the previous step or even with the interface configuration.

Anyway, what we need to do is create the stage tables for each level, and for that we have a few approaches we can do here:

  1. We can create another table exactly in the same way of the target table (needs to be a new table because the way ODI integrates the data. We’ll cover that latter).
  2. We can create, in this case, 3 tables, one for Year (same way as the source table is), and one for Quarter (same way of the source plus all columns from the Year table) and one for Month (same way of the source plus Quarter and Year columns).
  3. And we can duplicate the sources or the target datastore and do the changes above (in the 2 approach).

With the Stage datastores created (manually or by reverse) we just need to click in the “…” button and choose it from the list. Now we just need to repeat the step 2 more times for the other levels:

7-Level Canvas mapps

After we associate the source datastores and the stage datastores it’s time to create the attributes and ID’s for each level. For this you just need to click in the Year level and click in the Plus Sign button below:

8-Level attibutes config

Here we need to create all the attributes for this level and the natural key for that level as well. (We have the option to create slowly change dimensions here, but this will be covered in a future post!)

For each attribute you need to Plus Sign and fill the name of the attribute, set the data type (yes it not get automatically….) and select the Stage attribute (click in the “…” button and select it).

After all Attributes and ID’s we need to click in the below Plus Sign to set the natural key of that level. Just select in the list available.

After that, we just need to repeat for all the other 2 levels that we’ll have in this dimension.

With this done, the last step for this tab is to create the relationship between one level and its parent level. For this, highlight each level again, in this case we’ll start from bottom up, then let’s start clicking in the Month level and click on Plus Sign button below. Here we just need to say that for the Month level his reference parent will be Quarter. To set this we just need to select the Quarter level from the drop box and select eh foreign key from the drop box as well. Do that again for the Quarter level and reference it to the Year level. We don’t need to create any reference for the Year since it has no parent.

9 Parent Level References

As you can see, after the level configuration, everything you need to do is click in buttons and select from drop box or from “…” Screen (other than rename the defaults values if you like).
For last but not least, we need to click in the tab Hierarchies on the left tabs to enable us create a new hierarchy.

This is something fun. We can create multiple hierarchies inside the target table as well as skip level and some other features that we’ll cover in another post. For now let’s stay with a single hierarchy.

10-hierarchy

Here we need just to create the hierarchy by clicking in the Plus Sign button, give a name for the hierarchy and then click in the plus button bellow and add all the levels for the hierarchy. The order doesn’t matter, the idea here is that you can have multiple hierarchies with different levels in each one. For example, we could have a hierarchy called Full_Time with Year->Quarter->Month and another Hierarchy called Small_Time with just Year->Month. ODI would know based in the configurations we did, how to handle the data. Nice.

Also we can set skip level for each level we defined.

We are done with the dimension settings. I know it’s a lot of settings and some of you could be thinking (as we thought, this is a lot more work than if I create manually), but believe me, after you get used, you can do it in a reasonable time and the cube part is worthy.

Now we just need to repeat the process for all the other 2 dimension and them we finally start the cube settings:

11-Cube

To start the same thing as the dimension, Right click in the Cubes node and new.

12-Cube definition

In this screen we need to give a name for the cube, select a pattern name (Same as Dimension, just one option here for now) and do a biding to the target datastore.
After that we just need to click in the Detail tab in the left menu and start to configure our fact table.

12-Cube config

As I said in the beginning, here’s where the use of this components pays off. To configure a cube we just need click in the Plus Sign button and add all dimension we have, in this case our three dimensions. Then we just need to select the level we want to join our Fact table with our dimensions and bind the keys from the fact and that dimension.

For the last but not the least we just need to create by Plus Sign the measures that the Fact table will have. Same as the attributes in the dimensions: Name of the measure, Datatype and the column that will receive the data.

And that’s it. We are all set to move to the Mappings. Since this is already a huge post, I’ll stop this one now and will start a new post just for the Mappings, since I want to analyze how ODI builds the queries and loads the data there.

Hope you guys enjoy this post and see you soon.

PBCS, BICS, DBCS and ODI!!! Is that possible???

Posted in 11.1.1.9.0, 11.1.2.4, ACE, BICS, DBCS, EPM, EPM Automate, ODI, ODI 10g, ODI 11g, ODI 12c, ODI Architecture, ODI Architecture, Oracle, OS Command, PBCS, Performance, Uncategorized with tags , , , , , , , on August 15, 2016 by RZGiampaoli

Hey guys, today I’ll talk a little bit about architecture, cloud architecture.

I just finished a very exciting project in Brazil and I would like to share how we put everything together for a 100% cloud solution that includes PBCS, BICS, DBCS and ODI. Yes ODI and still 100% cloud.

Now you would be thinking, how could be 100% cloud if ODI isn’t cloud yet? Well, it can be!

This client doesn’t have a big IT infrastructure, in fact, almost all client’ databases are supported and hosted by providers, but still, the client has the rights to have a good forecast and BI tool with a strong ETL process behind it right?

Thanks to the cloud solutions, we don’t need to worry about infrastructure anymore (or almost), the only problem is… ODI.

We still don’t have a KM for cloud services, or a cloud version of ODI, them basically we can’t use ODI to integrate could tools….

Or can we? Yes we can🙂

The design is simple:

  1. PBCS: Basically we’ll work in the same way we would if it was just it.
  2. BICS: Same thing here, but instead of use the database that comes with BICS, we need to contract a DBCS as well and point the DW schema to it.
  3. DBCS: here’s the trick. Oracle’s DBCS is not else then a Linux machine hosted in a server. That means, we can install other things in the server, other things like ODI and VPN’s.
  4. ODI: we just need to install it in the same way we would do in an on premise environment, including the agent.
  5. VPN’s: the final touch, we just need to create VPN’s between the DBCS and the client DB’s, this way ODI will have access to everything it needs.

Yes you read it right, we can install ODI in the DBCS, and that makes ODI a “cloud” solution.

cloud solution

The solution looks like this:

BICS: It’ll read directly from his DW schema in the DBCS.

PBCS: There’re no direct integration between the PBCS and DBCS (where the ODI Agent is installed), but I found it a lot better and easy to integrate them using EPM Automate.

EPM Automate: With EPM Automate we can do anything we want, extract data and metadata, load data and metadata, execute BR and more. For now the easiest way to go is create a script and call it from ODI, passing anything you need to it.

VPN’s: For each server we need to integrate we’ll need one VPN created. With the VPN between the DBCS and the hosts working, use ODI is extremely strait forward, we just need to create the topology as always, revert anything we need and work in the interfaces.

And that’s it. With this design you can have everything in the cloud and still have your ODI behind scenes! By the way, you can exactly the same thing with ODI on premise and as a bonus you can get rid of all VPN’s.

In another post I’ll give more detail about the integration between ODI and PBCS using EPM Automate, but I can say, it works extremely well and as far I know is a lot easier than FDMEE (at least for me).

Thanks guys and see you soon.

 

Why should you apply for ODTUG Leadership Program

Posted in Career, Kscope, ODTUG, Oracle on July 13, 2016 by radk00

Hi all! Everybody that reads our blogs knows that we are passionate about ODTUG. We already wrote about how it changed our lives, we have a lot of great friends there, we are frequent speakers at sessions/webinars and Kscope already became the “must to go” event of the year for us. But since last year, it got even more special because I was selected to participate in the ODTUG Leadership Program, which I graduated during Kscope16. It was an AMAZING experience and since the “2017 ODTUG Leadership Class Application” is now open, I highly encourage all of you to also apply! Let me talk a little bit about my experience there.

The leadership program is a nine-month program where the participants have the opportunity to join monthly webinars that talks about a range of leadership topics like leading a user group, being an effective mentor/mentee, work-life balance, to name a few. The participants also join a “project”, that is different for each leadership class. Our project’s class was about the creation of a new ODTUG community, aimed to attract young professionals to the Oracle career. The result of our project was the brand new community called “Career Track”, which I wrote a post about it here.

But this description is just about the “technical” side of the program: you participate over nine months, have webinars to attend, one project to build. But this, in my opinion, is not the most important feature of this program. The most important one is the people that you will meet there. You will have contact with such an extraordinary/talented group of people that you will be extremely amazed and honored to be part of such a great team. The networking experience is one of the most powerful ones that I ever experienced. Over nine months I’ve meet great leaders in the Technology field, the entire ODTUG board, Oracle folks and some great new friend from around the globe (yes, the Leadership program is international!).

IMG_20160627_074520

Another great thing about the program is “exposure”. There are many opportunities within ODTUG to show you work, like webinars, meetups, technical papers and even Kscope sessions. The leadership program will put you in touch with the people that run those opportunities, so you will understand how they work and how you may participate on those as well. The program makes you feel like you “belong” to something greater than your normal jobs, so you get inspired to also to do greater things and exposure your work to other people.

This is just a much resumed description of what I’ve lived during those nine months but I really hope that this may somehow encourage you to also apply for the next class. I totally guarantee that this is worth it and your live will never be the same again!

I’m grateful for all those people that I meet during the program (all of you are amazing!!!) but I must write here a special THANK YOU to Sarah Zumbrum that was our class’ leader that guided us through the entire period. Few times in my life I’ve seen such a dedicated and passionate person about what she was doing. Her actions and words were always of a true leader and I’ll always take her teachings with me in both my personal and professional lives. Thank you again Sarah!

Applications are now being accepted and the deadline to apply is 8/14/16. Apply here: ODTUG 2016-2017 Leadership Program Application

lplogo

See ya!

… Kscope 16 has finished and it was amazing!!!

Posted in Kscope 16, ODTUG, Oracle on July 4, 2016 by radk00

Hi all, how are you all doing? Man, it was too fast…. This year’s Kscope was great, but when we realized, it was just over already. We got so much involved and busy there that our plans to do a “live blogging” just failed, so we will just go with a resumed version of it today (sorry about that!).

This year for me was the “networking” year. We spoke with great new people, meet with old friends, recorded OTN interview, being in happy hours, parties, after parties, receptions, graduations and so on. It was a great social event for us this year. Luckily, we have the option to see all Kscope 16 recorded sessions afterwards (you too, if you have being at Kscope 16 or if you are an ODTUG full member) because there were some great sessions that I really wanted to attend, but there was no feasible time to do so.

Our two sessions where just amazing! We got a very good number of attendees (thanks all that were there!) and the feedback during the event about them was just great! We are very happy that we could deliver some good information to all of you and this kind of feedback motivates us to keep going and bring more and more content for you all.

If you want to take a look on our sessions’ slides, take a look on the links below:

Take a peek at Dell’s smart EPM global environment

20160627_102539

Incredible ODI tips to work with Hyperion tools that you ever wanted to know

20160627_163138

This Kscope was also very special to me because I was graduating from the ODTUG’s leadership program. It was an amazing experience and later this week I’ll write a post talking about my experiences there and how do you apply for it if you are interested in doing so. It was a nine months program with some great people that I was blessed to meet face to face at Kscope 16.

So, are you wondering where Kscope 17 will be? Check it out!

20160627_174501.jpg

Texas!!! I already had the opportunity to go to San Antonio and it is really a nice venue. It will be great to see once again the Alamo and walk down the River walk. A great thing about Kscope is that you get inspired by such a great content that you keep thinking about what are you going to present next. We are already taking notes on some ideas for next year, so you may be sure that we will do our best to also be present at Kscope 17.

That’s it folks, thanks again for all of those that were present at our sessions! I’m honored to be part of such a great and well organized event!

Hope to see you all again at Kscope 17!

Kscope 16 has started…

Posted in ACE, EPM, Kscope 16, Oracle, OTN with tags on June 26, 2016 by RZGiampaoli

Greetings my fellow friends. As you know it’s that time of the year when we get together with our friends to have a lot of fun and decide what we will do next…. and it’s not Christmas but it is KSCOPE!!!
Yesterday we helped a little bit the Odtug guys and our friend Cameron to organize this amazing event😉 (Very small help since we get there late :)).
And today we watch some cool news from Oracle and what is coming in the near future🙂
Talking about Oracle, we just received our oracle Ace plaques🙂

image

Very nice🙂 and I loved mine🙂 match with my beard🙂
Also, tomorrow, 27/Jun we will presenting two session. In the morning (10:15) we will talk about the Dell’s global EPM environment, you can expect to hear about all kind of solutions that we implemented over the Last 6 years, and I’m sure that you guys will get out of this session with a lot of ideas to implement. At afternoon (4:30) our session will be all about tips and tricks. You can expect to hear the best tricks we use in our development as well some ideas in how you can use them.
Come and join us in our presentations. We will be waiting for you guys.
And if we never match before, you just need to look for these badges🙂

image

Thank you and we are waiting for you tomorrow….

Here we go! Kscope 16!!!

Posted in ACE, Kscope 16, ODTUG on June 22, 2016 by radk00

Hi all! Here we go again! Kscope 16 is just a few days from us and we are really happy to be part of the greatest User group conference in the word! We will (at least we will really try) to cover the entire event here in the blog, so stay tuned for some posts over the next week. On this year’s Kscope, DEVEPM will be arriving from two different locations: Rodrigo is traveling from Brazil and Ricardo from Ireland (Ricardo decided to move to Ireland this year), so it will be great to see each other again.

poadublin

As you know, DEVEPM will be presenting two sessions on Monday and we really hope to see you all there:

Take a Peek at Dell’s Smart EPM Global Environment:

Ricardo Giampaoli , TeraCorp
Co-presenter(s):
 Rodrigo Radtke de Souza, Dell
When: Jun 27, 2016, Session 2, 10:15 am – 11:15 am
Topic: EPM Applications – Subtopic: Planning

In a fast-moving business environment, finance leaders are successfully leveraging technology advancements to transform their finance organizations and generate value for the business.
Oracle’s Enterprise Performance Management (EPM) applications are an integrated, modular suite that supports a broad range of strategic and financial performance management tools that help business to unlock their potential.

Dell’s global financial environment contains over 10,000 users around the world and relies on a range of EPM tools such as Hyperion Planning, Essbase, Smart View, DRM, and ODI to meet its needs.

This session shows the complexity of this environment, describing all relationships between those tools, the techniques used to maintain such a large environment in sync, and meeting the most varied needs from the different business and laws around the world to create a complete and powerful business decision engine that takes Dell to the next level. 

Incredible ODI Tips to Work with Hyperion Tools

Ricardo Giampaoli , TeraCorp
Co-presenter(s):
 Rodrigo Radtke de Souza, Dell
When: Jun 27, 2016, Session 6, 4:30 pm – 5:30 pm
Topic: EPM Platform – Subtopic: EPM Data Integration

ODI is an incredible and flexible development tool that goes beyond simple data integration. But most of its development power comes from outside-the-box ideas.

  • Did you ever want to dynamically run any number of “OS” commands using a single ODI component?
  • Did you ever want to have only one data store and loop different sources without the need of different ODI contexts?
  • Did you ever want to have only one interface and loop any number of ODI objects with a lot of control?
  • Did you ever need to have a “third command tab” in your procedures or KMs to improve ODI powers?
  • Do you still use an old version of ODI and miss a way to know the values of the variables in a scenario execution?
  • Did you know ODI has four “substitution tags”? And do you know how useful they are?
  • Do you use “dynamic variables” and know how powerful they can be?
  • Do you know how to have control over you ODI priority jobs automatically (stop, start, and restart scenarios)?

If you go to Kscope, please don’t hesitate in contact us there! We will be more than happy to meet you all!
Thanks all! Let’s see which ribbons we will get at this year’s Kscope!