Archive for the DEVEPM Category

Oracle SQL for epm tips and tricks S01EP07

Posted in ACE, DEVEPM, EPM, Oracle, RANK, SQL with tags , , , , , on August 15, 2019 by RZGiampaoli

Continuing our Oracle SQL for EPM series, today we’ll start to talk about analytic functions and how can we use them for more than “just” analytics.

To start with, let’s talk about RANK(). As the name suggest, RANK() is used to rank our data based in something. It’s very useful to find out each data is more relevant than others. Let’s see a example:

Here we have a small table with 2 currencies and a few products. Let’s first start with the basic function of RANK() and see each product generated more income:

The basic syntax is RANK() OVER (ORDER BY COLUMN). Basically what you are saying to oracle is, rank my data based by a column (or multiple columns). Since I just ordered by data, the values of the RANK() got duplicated everything oracle finds the same value. This is because we have 2 currencies and they are both USD.

To fix data we can do 2 things: Or we can include currency in side the order by or we can use another more advanced use of RANK() that is OVER PARTITION.

Let’s see how it works:

If I just add another column in the ORDER BY, it’ll basically create the Rank based in the order of these 2 columns. It’s the same as do a ORDER BY and then follow the order of the data that returns. Then in this case, you can see that the products PR235 for Functional Data got Rank 1 and for USD rank 11, even both having the same value. By the way, you also can see that the Ranks is ordering in the opposite order that we would like to have. This was intentional to show you how the Rank is produce. To fix that we just need to put a DESC in the ORDER BY clause, like we would do in a normal ORDER BY.

Ok then let’s see the more advanced way to write this query:

Instead of inserting new columns in the ORDER BY we can use PARTITION BY instead. The results here is the same, but this can be used in other ways as well and I would say that this would be the best way to used it since is more clear what you want to do.

The PARTITION BY does exactly what the name says, it partition the data by the content of one or more columns. In fact, the PARTITION BY clause can be used in most off the analytics functions like MAX, SUM, MIN, AVG…. then it’s very powerful and the best thing is that, if you use it, you don’t need to use a group by (we’ll see that in the future).

Now, as I said before, we can have other uses for RANK than just ranking data. Let’s say that you have this table without the CURRENCIES column:

Without the CURRENCIES column we end up with duplicate data in the table right? In this case we could do just a distinct and use the data as is, but let’s say you want to create the CURRENCIES column based in the data that we have, and the rule would be, the first data you find is USD and the second (if exists) would be Function. We can use Rank for that too:

Since here the data is the same for the same product, the only thing that could differentiate them was the ROWNUM (or ROWID, that would be better to make sure each one was the first one, but harder to see the example) I used it to create a Rank that shows each row has the lowest ROWNUM and that would have the Rank 1, the second one will be 2 and with this information, I just did a decode to make the 1 USD and the 2 Functional (Also a NA in case we have more than 2 duplicated rows).

This can be used in exactly the same way if you have a metadata table without the datastorage information and you want to create it. Then the first member you find (Trough our friend CONNECT BY PRIOR) will be the Prototype (Store or never Share or Dynamic Calc and Store) and the other would be Shared members.

Of coarse there’s way more ways to use this function, and we’ll see more of them with the other analytics functions that we’ll going to see here.

See you soon guys.

Advertisements

ORACLE SQL for EPM tips and tricks S01EP05!

Posted in ACE, Connect By, DEVEPM, Dimensions, EPM, ETL, Oracle, Oracle 11.2.0, Oracle 11.2.0.4, Oracle Database, Query, REGEXP, SQL, Tips and Tricks with tags , , , , on May 3, 2019 by RZGiampaoli

Continuing the Oracle SQL for EPM series today we’ll see another usage of Connect by. I’m talking a lot about connect by because is a very useful function and we should use it a lot in the analytic space.

Let’s imagine that we are working with a planning application and we want to figure out in a query what is the region of each cost center and to what top level that cost centers belong. In this app we have the Entity dimension with Support Geography as a attribute.

The support geography has the region on the generation 2 of the hierarchy. How can we do that. Well, connect by to the help.

First of all we need to get the entire physical geography hierarchy. To do that we’ll do a query like this:

This is a simple query that joins the HSP_ATTRIBUTE_MEMBER table (this table contains all the attributes from all attribute dimensions) that’s why we have that sub-query to select just the Support Geography members (1).

Then we join this table with the HSP_OBJECT that contains all metadata from everything in Planning: Forms, Dimensions, Flows, everything. We do that to get the name of the members. And finally we do a connect by to rebuild the hierarchy (2).

With the hierarchy re-built we can use the Connect by root to figure out to witch top level each member belongs. (3)

With this query we know what’s the leaf member of the Support Geography dimensions (ID) and to what Region that member belong. Now we need to do the same for the Entity dimension.

With this query we are filtering the OBJECT_TYPE=33, that means Entity (1) (If you want to know what are the possible object types you can query the HSP_OBJECT_TYPE table to check that out), and then we do a Connect By to re-built the hierarchy and then use some Connect by Root to get the parents as well the Sys connect By Path to get the Path of the hierarchy.

Now we just need to join everything under the same query to get all the Cost Centers and to witch top level each one belongs and to witch region they are part of as well.

Another thing that I like a lot about the WITH clause is that is very easy to create nested queries. In this case I created 2 different queries, each one with a WITH and a select based in that WITH. Now everything I need to do is put everything under the same with by:

We can see that now I just got the Select that was under the WITH clause and just created a nested WITH by just creating the step 1 and 2 and now I have instead of 2 WITH queries I have just 1 WITH with 4 Clauses under it being 2 of then a nested one based in the previous one (Connect by from the filtered query).

All we need to do is putting everything together by joining the ENT_HIER and the SG_HIER using the HSP_MEMBER_TO_ATTRIBUTE table. This table basically is the map between the ENTITY dimension and the Attribute dimensions, in this case Support Geography.

The results is the Cost Center, the Path of that member in the hierarchy and with this we can use the REGEXP to extract any level of the hierarchy, and finally the region that cost center belong.

You may thing why we need to do that. well, this is a query I used to join with the HSP_GROUP table to get the groups and the members from each region and then create the security dynamically for each user. That means, I have a Planning application with Entities that has cost centers from different regions and I’m using the Attribute dimension to generate the security.

If a User has access only to AMER data, it’ll only see the AMER cost centers. This is just one example of what we can achieve using Connect by.

I hope you guys enjoy. Next time we’ll talk about another very very useful function that I really love it.

See you soon guys.

DEVEPM will be at Kscope19!

Posted in DEVEPM, Kscope, Kscope 19, ODI with tags , , on March 14, 2019 by radk00

Hi all, how are you doing? We are very happy to announce that once again DEVEPM will be at KScope! We are very honored to be selected to present on the best EPM conference in the word! We got one presentation and a panel in, so here is what we are going present at Kscope19:

OAC and ODI! A Match Made in…the cloud?

  • OAC stands for Oracle Analytics Cloud Services, and it’s another cloud solution offered by Oracle. It provides you a lot of analytic tools for your data. The question is, do you need to be 100% cloud to use OAC services?
    Well, with ODI we always have options, and for OAC that is not an exception.
    In this presentation we’ll take a look at three different ways to use ODI to integrate all your data with OAC, ranging from using your existing on-premises environment to a 100% cloud solution (no ODI/DB footprint in your environment).

205, Level 2 => Tue, Jun 25, 2019 (09:00 AM – 10:00 AM)

EPM Data Integration Panel

Is there a functional issue that you’ve been trying to solve with Cloud Data Management or FDMEE that you just can’t seem to break through on? Are you about to kick off a new project or phase and need to validate that Data Management or FDMEE is the right tool for your needs? Are you about to subscribe to an Oracle EPM SaaS offering and want to know your data integration options? Or do you just want to give some feedback about the product and features that will help you increase your utilization of it?

201, Level 2 => Wed, Jun 26, 2019 (11:45 AM – 12:45 PM)

Kscope is the largest EPM conference in the world and it will be held in Seattle on June 2019. It will feature more than 300 technical sessions, five symposiums, deep dive sessions, and hands on labs over the course of five days.

ys3header

Got interested? If you register by March 31st you’ll take advantage of the Kscope early bird rates. Don’t waste more time and let’s be part of the greatest EPM event in the world. If you are still unsure about it, read our post about how Kscope/ODTUG changed our lives! Kscope is indeed a life changer event!

BI Community Lvl up! Enter Analytics Community

Posted in ACE, Career, DEVEPM, EPM, ODTUG, Oracle with tags , , , on February 22, 2019 by RZGiampaoli

Hey guys how are you?

Today I have a very exiting news for all of us. ODTUG just announced the re-branding of the BI Community, to the Analytics Community.
The change is not in name alone—it’s an effort to create a more dynamic and inclusive community to better serve the needs the of ODTUG members. What does this change mean? It means we encompass more than just BI. It means if you are interested in any of the tools that are used to analyze data and turn it into information, we are here for you! Have a question? Want to share knowledge you gained from a project? Connect with other like-minded analysts who explore data in myriad ways, both on premises and in the cloud. Join the Analytics Community to share your passion for all things analytics, whether you are using OAC, OBIEE, ML/AI, R, Python, Essbase, DV, Big Data, Data Warehousing, or other platforms. This page is your portal to all things Analytics– a blog feed, a list of upcoming Analytics-related events, and a look at the Analytics content located in the Technical Database. Check out the most recent blogs from industry experts. Like what you read? Click on the title to visit the author’s blog. Would you like to see your blog featured here? Drop a note to Haleigh@odtug.com. Let’s explore analytics together!

Check the Twitter, LinkedIn and Facebook page to learn more, join us and help us to create a better Analytic Community.

BI Playlist Banner2.jpg

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 Speaker Award

Posted in ACE, Career, DEVEPM, EPM, Kscope, Kscope 18, ODI, ODI Architecture, ODTUG, PBCS with tags , , , , , , on September 17, 2018 by RZGiampaoli

Hey guys how are you?

It has been awhile since last time I wrote anything here…. and surprise, surprise, it’s because I’m crazy working in a project that was sized small but turn out huge and the size didn’t change…. 🙂 never happened before heheheh 😉

This is just a small post to tell how grateful and happy we are for receiving the EPM Data Integration Speaker Award in Kscope 18 with the presentation: How to Use Your ODI On-Premise to Seamlessly Integrate PBCS.

We start this blog in 2012 and we have been presenting at Kscope since 2013 and it has been very rewarding, not only because we become Oracle ACEs because of this, but because every single post or presentation we learn a lot with it.

When you do a presentation you need to stop to think in a solution for a specif project and start to thinking in a solution that can be used to all projects. This alone is a challenge, but the amount of thing we learn is a great deal. We can easily said that our code improved a lot since 2012 when we began with this blog and it’s in great part because of this blog and our presentations.

Then we thank you all of you that read our blog (even if we don’t post as much as we would like), to everybody that goes to KScope and decide to watch our presentations and to ODTUD that provide this bi-lateral learning platform.

Thank you all of you for supporting us and see you soon.

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