Archive for the Uncategorized Category

Fragmented and Aggregated tables in OBIEE using ODI Part 4/5: Populating the Aggregated tables

Posted in Hacking, OBIEE, ODI, ODI Architecture, Oracle, Oracle Database, Tips and Tricks, Uncategorized with tags , , , , on February 12, 2020 by RZGiampaoli

Hey guys, how are you?

Continuing the series Fragmented and Aggregated tables in OBIEE using ODI and today we are talking about how to Populating the Aggregated tables using ODI.

Just to make easier for you to navigate in this series, here’s the parts of it:

Creating the tables: I’ll explain the advantages and how you can create the fragmented tables and also how to further improve performance using partitioning and sub partitioning.

Managing the partitions of the Fragmented/Aggregated tables: Here you’ll see how to manage all partitions using ODI.

Populating the Fragmented tables using ODI: Here you’ll find how to create generic components to load all fragmented tables we need to.

Populating the Aggregated tables using ODI: Here you’ll find how to create generic components to load all Aggregated tables we need to.

Setting the OBIEE Repository: Finally, we’ll going to setting up the OBIEE repository to make use of all tables.

Today we are in the final step before we can work in our OBIEE repository to put all these 18 fact tables together. The data load for our Aggregated tables.

The aggregation in fact is a very simple process, we just need to remove all detailed columns we have in the detail fact table and leave just the ID’s columns. After that we just need to reduce the level of the ID of the column we want to aggregate and sum all the data at that new level.

In our case we going to use the PERIOD_ID to do that, because period is the most common choice when we talk about aggregated table and serve well in most of the cases.

And that’s why I design the PERIOD_ID as YYYYQMMDD, because is very simple and easy to manipulate this number to go up or down a period, as well to do range or even transform it back to date. It’s way easier than create a surrogate key or whatever, even if you want to work with SCD.

As you probably already guest by now, we’ll use the command on source and command on target again to do the aggregations, this way we can have only one code to spread the data through out aggregate fact tables.

In the command on source for the monthly level table, we just need a query in the source that return the name of the detailed table plus the name of the monthly table. Since I designed all tables with a specific name pattern, we can easily manipulate the table name to get the month table from the detail table like this:

We don’t need anything fancy here because in the last post we create a proc to call six time the same scenario passing different parameter to it.

One of these parameter is the name of the fact table it needs to be loaded and this information is store in the variable #JAT_TABLE_NAME (already replaced by the value in the picture) and what I have done there is just split the table name using REGEXP to get the forth and the fifth occurrences of ‘_’ and concatenate everything back adding a _M_ in the middle of it, creating my Monthly level fact table name.

With data we have the detail table name that the scenario needs to load and also the monthly level fact table name that we need to use for that loop. We just need to create a query to aggregate the data, what’s very straight forward.

As the query will change depending of the design of the table, this one is just intended for explain what needs to be done since the idea is very simple. First we replace the name of the table that  we wish to insert data and the table that we wish to get the data from for our to variables: #JAT_TABLE_NAME that we are sending when we call this scenario and #JAT_M_TABLE_NAME that we just create in the Command on Source tab.

With this, for each loop, the scenario will get the data from the right source table and will insert in the right aggregated table. Now we need to aggregate the data, also a very simple matter. All we need to do is to join the detailed fact table with the period dimension and, because this is a range partition, we need to get the first date of that month and the last date of that month, that’s why we have that MIN and MAX filtered by the Year and Month.

With the right data filtered, we just need to aggregate the data and use the FISCAL_MONTH_ID instead of the PERIOD_ID, this way the data will be aggregated by month. And we are done here.

By the way, we could instead of using the between to get the right range of the partition filtered the partition itself using explicit partition filtering:

This will make oracle to go straight to that partition. The only thing is that you need to pass the partition name, that in our case is very straight forward, specially because we are creating and managing the partitions ourselves. We could have oracle create the partitions automatically using INTERVAL (for another post) but if we do that oracle will also create the names of partitions like SYS###### and that will make everything harder to filter by partitions. Anyway, this is just another option we can have.

For the quarter level it’s even easier because we don’t need to worry about range partitions anymore. We just need to have our Command on Source return the Monthly level table name and our Quarterly level table name:

As you can see, the query is the same, the only difference is that we insert a ‘_Q_’ in the middle of our string. And for the insert in the Target Command tab we just need, as before, replace the tables using the right variable in each case, join with the DIM_PERIOD_MONTH to have the right level of data, filter the Monthly level table using any method and then use the QUARTER_ID to sum the Monthly level data to the Quarterly level data and that’s it.

That’s all we need to do to populate all aggregated table. And we finally have all data populated in our tables and now we can start to create the OBIEE repository. It’s wort to mentioning that if you resume everything that I said until this point, we basically had to:

  • Create our tables;
  • Create one procedure to:
    • Create the partitions;
    • Truncate the partitions before we load data;
    • Call and loop the scenario that will load data;
    • Drop the old partitions
  • Create another procedure to:
    • Load detail data;
    • Load Monthly level data;
    • Load Quarterly level data;

What this means is that with just two procedure we can maintain and populate a very complex environment in a very clean way with very little points of failures.

This is a very elegant approach and as I always said, if is too complex and/or difficult, you were doing something wrong. It is just a meter of think about the design first, all that needs to be done, and split it in a logical and reusable way. If you do that, everything will be simple to be archived using ODI.

I hope this series has been useful so far and I see you in the last chapter.

ORACLE SQL for EPM tips and tricks S01EP01!

Posted in DEVEPM, ETL, Oracle, Oracle Database, Performance, SQL, Tips and Tricks, Uncategorized, WITH Clause with tags , , , , , , on January 21, 2019 by RZGiampaoli

Hey guys how are you? I decide to start a new series called ORACLE SQL for EPM tips and tricks. The idea here is to show the most useful SQL commands for EPM, how to improve performance, tips, tricks and everything that can be useful from a SQL point of view!

And to start well, I’ll show something very old but very useful that I don’t see too many people using these days. “WITH” clause.

I love to use “WITH” in my code. It helps organize the code, helps to optimize it and more important, to make it more efficient.

When you use “WITH” Oracle treats your query inside it as an inline view or resolved as a temporary table, making it easier and faster for Oracle to access that data if you need it again.

Simply putting, every time you needs to right a query that uses the same table over and over, it’ll probably be way more efficient if you use “WITH”.

The “WITH”clause works a little bit different from a regular SQL. We can say that we split the query in 2, one is the “WITH” declaration (That will behave like a table) and the other is the SQL that will Query the “WITH”.

WITH name_of_temp_table_here AS
(
    YOUR QUERY HERE
),
   name_of_temp_table_here2 AS
(
   SELECT *
   FROM name_of_temp_table_here, another_table...
)
SELECT *
FROM name_of_temp_table_here, name_of_temp_table_here2 

In the “WITH” you can have any kind of query you want. You can do joins, group by, you can also have more than one “WITH”, you can use the result of one “WITH” in the next “WITH”, you can do a lot of things.

But for now, lets take a look in a more real example. Let’s say that you have a table like I do, that contains all metadata from all yours applications:

Let’s say you want to get the Parent of a attribute that is associated with your Entity dimension. You probably will be doing something like this:

In the “FROM” we call the table 2 times and we join and filter everything we need. Since we don’t have attribute association in all levels we do a “Left Join” to make sure all Entities comes in the query. If we run a Explain Plan now we’ll get something like this:

As you can see, Oracle is querying the METADATA_EXTRACT table twice and each time it’s doing a FULL in one Partition (‘ENTITY’ and ‘PHYSICAL_GEOGRAPHY’ partitions).

Now, if we change the query (and we can do it in different ways, this is just one of them) to a “WITH” clause we ‘ll have something like this:

As you can see, we achieved the same results with the code a little bit different. Now I have all my filters in the “WITH” query and in the bottom I just call the “WITH” query 2 times and do what needs to be done.

If we run a Explain Plain now we will have:

As you can see, Oracle now is querying the METADATA_EXTRACT table just once and then his queries the SYS.SYS TEMP table twice. The only problem with this query and the way I did is that since we are creating a temporary table filtering 2 partitions and then later I’m filtering again, it’s basically doing 2 FULL scan in the same TEMP table, and even so, it’s a few seconds faster then the original query.

But this is just an example on how we can reduce the amount of times that Oracle needs to query a table. WITH is not a miracle clause or anything like that, is just another tool that we can use to optimize our code, and its performance needs to be evaluated in a case-by-case basis.

And even if the performance doesn’t change, I believe using “WITH” clause makes any query easier to ready, to test, to update and to right since you can divide your huge query in small bits and then join
everything in the bottom query.

“WITH” is a huge subject and we’ll be talking more about it in the next post, and this time we’ll be improving performance as well using “WITH” with “CONNECT BY”.

KScope 18! It’s a wrap.

Posted in ACE, DEVEPM, EPM, Kscope, Kscope 18, PBCS, Uncategorized with tags , , on June 21, 2018 by RZGiampaoli

That’s it guys, one more year of KScope finished successfully.

This year was a big one for us since we had 3 sessions, one lunch and panel and one lip-sync battle…. that we lost… but was a lot of fun (way better than I thought it would be).

The sessions were great and we are very proud to receive the Top Speaker Awards for EPM Data Integration track with the session How to Use Your ODI On-Premise to Seamlessly Integrate PBCS.

This means a lot to us since we are always worried about our speeches because our marvelous English and our subtle accent (I sound like a famous robot from the future “Come with me if you want to live….”), then we always try to compensate with the content.

And this year I think we made it. We’ll try very hard to keep the content this interesting. We always try, but some times what is interesting for us is not for others. Would be very nice if you guys leave comments with thing you would like to see in our presentation or blogs.

Thank you very much for all people that attended our sessions and look forward to see you next year!

Thanks

 

Kscope 18 is coming fast and DevEPM will be all around the place!

Posted in Uncategorized on June 4, 2018 by RZGiampaoli

Hi guys how are you?

It has been so long that I almost forgot about the blog :). We have being very busy lately, with a lot of projects and Kscope. This year we’ll be all around the place in Kscope.

Yes we’ll be presenting 3 sessions, one lunch and learn panel and a lip-sync battle….

On Monday 11 at 1:15 PM, we will present the session How to Use Your Existing ODI On-Premise to Seamlessly Integrate PBCS where we will be talking about some nice ways to use you current environment to integrate your new PBCS app.

And at night, 8 PM, I’ll be lip-syncing for my life in the EPM Community Night Event. I think this will be really fun (or funny to watch at least).

On Tuesday 1 pm, I’ll be in the Lunch and Learn Essbase Panel at and I’m planning to put some twists in our regular essbase talk.

On Wednesday 13 we also have two presentations: Dynamic Metadata Integrations for Multiple ASO Applications at 11:45 AM where we’ll show a way to load metadata to any number of ASO apps in a very easy and dynamic way and a updated version of Incredible ODI Tips for Working with Hyperion Tools 2.0 at 3:30 PM this time we add some demos to make easier for everybody see how things happens.

I hope I can see you all there and that you guys have a blast in Kscope 18 🙂 I know I will.

See you soon guys!

All about Meetups!

Posted in ACE, MeetUp, ODTUG, Oracle, Uncategorized with tags , , on January 23, 2018 by RZGiampaoli

Hey guys how are you doing? It has been a long long LONG time since our last post…. and we are sorry for that. We having been very busy with a lot of projects (both work and personal) and we had to get some time for us!

But we are finally getting back to action! And I’ll do that posting something about one thing that everybody loves…. Meetups..

First of all I would like to talk about the next ODTUG meetup that will happens in 2 days in Orlando, inside the Walt Disney World Dolphin Resort…. I think I don’t need to say anything else about it other that it’ll be awesome…. but anyway, here’s the link for you guys to attend:

All Community Social Meetup with the ODTUG Board of Directors

Thursday, Jan 25, 2018, 5:30 PM

Walt Disney World Dolphin Resort- Phins Lounge (Lobby Bar)
1500 Epcot Resorts Blvd Orlando, fl

8 ODTUGers Attending

Join us on Thursday, January 25, 2018, at 5:30 p.m. for an ODTUG social Meetup in the Phins Lounge at the Walt Disney World Dolphin Resort. Come enjoy a drink with the ODTUG Board of Directors and mingle with fellow ODTUGers! Date: Thursday, January 25, 2018, at 5:30 – 7:00 p.m. Location: Walt Disney World Dolphin Resort – Phins Lounge (lobby bar) …

Check out this Meetup →

Now that this is sort out, do you know that if you want to do your own Meetup you can do it together with ODTUG? And you can even get financial support to do it????

Well it’s true. You just need to plan your meet up and then fill out the Meetup Planning form. This will provide ODTUG with all the information they need in order to move forward.

It’s also helpful to provide them if you are seeking financial support and if so, how much, and also your estimated attendee count as well. Another advantage is that there’s an list of services that ODTUG will provide as a sponsor along with the qualifications for sponsorship.

  • Promotion for your local Meetup:
    • ODTUG will post your event on the ODTUG Meetup page.
    • Email Campaigns: ODTUG will invite members to your event
    • Social Media: ODTUG will promote your event on Twitter, Facebook, and LinkedIn
    • ODTUG will publish the success of your meeting in your community’s quarterly newsletter
  • Source an Oracle speaker if needed
  • Ship ODTUG swag for you to distribute at your event.

Off coarse there’re a few Sponsorship Requirements: Communicate ODTUG Resources and Events:

  • Educational resources

o   Provide an overview ODTUG’s memberships and benefits

o   Provide ODTUG Kscope18 deadlines

  • Early bird rate deadline – March 29, 2018

If you get exited with this piece of information and want to do you own Meetup, here are some useful tips and strategies for planning a Community Meetup.

 1- Choose a Date and Location

Be mindful of holidays and tech conference dates when planning a meetup. A free venue is best—workplaces and universities tend to be free, while private rooms at restaurants and hotels usually have a rental fee.  When securing a space, be sure to ask what the room capacity is and monitor your RSVP’s accordingly. If you need assistance with securing a venue please be sure to fill out the Meetup Planning form.

 2- Schedule Speaker(s)

Education and content should be a part of a Meetup. Even if presentations are brief, education helps to engage members. If you need assistance with securing presenters, please be sure to fill out the Meetup Planning form.

 3- Include Time for Networking with Food and/or Drinks

This could be as simple as having pizza delivered to an office after a speaker presentation. If there is a nearby bar/ restaurant the group may want to go for a Happy Hour.

 4- Promote ODTUG Membership and ODTUG Kscope18 Conference

Share the benefits of ODTUG membership, encourage others to volunteer with the community, and promote ODTUG Kscope18.

 Tips for a successful Meetup:

 Scheduling

o   Schedule quarterly Meetups if possible. Five-week months result in higher attendance and commitment. (Customers have an extra week to plan for maintenance, testing, and some downtime.)

o   Wednesdays and Thursdays are the best days for scheduling meetings; whereas Fridays you will be competing with families and/or social lives.

 Location is important

o   Provide an environment that is fun and inviting. Seek popular local bars or restaurants that provide private rooms for formal meetings.

 Budget

o   Everything is negotiable! Find creative ways for customizing menus for your meetings. Order appetizers for a small group or buffets for larger group.

o   The best option for savings is scheduling meetings in the workplace if possible.

 Greeting your guests

o   Have someone available to greet your guests. Provide a sign-up sheet, name tags, and pass out ODTUG/Kscope18 swag.

 Engaging your guests is KEY!

o   Creative ice breaker activity is a great way for people to engage and find common ground.

 Follow-up is vital for success

o   Feedback is important – this gives you an opportunity to thank your guests and provide a questionnaire (limited to 10 questions or less) to help improve and plan future Meetups.

 Non-solicitation

o   The user community is interested in learning how other organizations are leveraging the tools today within their environment, not a sales pitch. Solicitation is not allowed.

 

I hope you guys enjoy all this info and let’s start to plan our meetups 🙂

ODTUG Leadership Program 2018!!

Posted in ACE, DEVEPM, Kscope, Leadership Program, ODTUG, Uncategorized with tags , , , on September 1, 2017 by RZGiampaoli

Hi guys how are you?

ODTUG is opening the application for the 2018 leadership program. For those that don’t know what it is, it is a eight-month program (remote sessions) to help people to advance into leadership positions along their career track, improve their effectiveness in their current position or switch careers.

Rodrigo and I are past participants and we definitely advise anyone that wants to get involved and learn in the process to participate in this wonderful program.

To learn more about the program or give it a try you can click Here.

Hope you guys enjoy it.

Are You an ODTUG Kscope Aficionado?

Posted in Kscope, Kscope 17, ODTUG, Uncategorized with tags , , , on June 13, 2017 by RZGiampaoli

Hi guys, today I have a very exiting opportunity for all kscope veterans that would like to help the newcomers.

The ODTUG K Team

If you are an ODTUG Kscope aficionado and think you can help guide the ODTUG Kscope newcomers down the right path? Join the K Team! K Team members are here to help newcomers take advantage of everything ODTUG has to offer. Interested in being involved? Sign up now and we’ll send you all the information you need.

Let’s help the newcomers to get the most of our beloved conference and of course have a lot of fun in the process 🙂

See you guys in a couple of weeks at Kscope 17!!!