ORACLE SQL for EPM tips and tricks S01EP02!

Posted in ACE, Connect By, EPM, Oracle Database, Performance, Query, SQL, Tips and Tricks, WITH Clause with tags , , , , , , , on March 21, 2019 by RZGiampaoli

hey guys how are you? Let’s continue the SQL for EPM series. Today I’ll continue to talk about With with a small bonus of Connect by :). let’s start.

A lot of people uses Connect By in a daily bases but as far I having seeing, most of then don’t know how to use it properly. I already lost count with the amount of people complaining about performance issue with Connect By.

The thing is, Connect By works a little different than everything else in Oracle. We can say that Connect By has 2 stages and we’ll see why I’m saying that with this example. Let’s get back to our metadata table and let’s do a Connect By to extract the Balance Sheet Hierarchy from the Juno application:

As we can see, inside this table we have more than one application and more than one hierarchies for each application. That’s ok, we just need to filter it in our SQL right?

If we filter the APP_NAME and the HIER_NAME we’ll get all accounts for that Application and this will generate 12,622 rows. By the way, this table has all metadata from all our applications and we always filter by APP_NAME and HIER_NAME to select what we want (the table is also partitioned and sub-partitioned by these 2 columns). It’s important to know that without filtering anything this table has:

Ok, now, if we want to get just the BS hierarchy we just need to do the Connect By right?

That works… perfect… or not? Well in fact, this the wrong way to use Connect by because what I said before, the 2 stages.

As you can see, this query took 25 sec just to return the first 50 rows. In a integration this will take way more time, in fact, if you join this table to a data table to do a SUM in the BS level, this will take ages to return.

The reason is that for the Connect by, first Oracle does everything that is after the word Connect by and after the word Start with and then, and only then, it does what is in the where condition. That means, first he did the connect by in those 2.260.372 rows (and they are all repeated) and then after all the processing, it filtered what we wanted, that is the APP_NAME and the HIER_NAME. Then the right way to use it is:

Now it looks way better. 0.375 seconds to do exactly the same thing as before, and the only thing I did was to move our filters to the right place. Now Oracle is filtering and doing the Connect by at same time.

Now, if you do a SYS_CONNECT_BY_PATH and want to get just the leaf (to have the complete path that the hierarchy does, you can filter the leafs in the where clause (and need to be there otherwise it’ll not have the entire hierarchy during the connect by). This is how:

Now you see that the connect by filtered what needs to be filter during the Connect by execution and afterwards, it filtered just the leafs (using the CONNECT_BY_ISLEAF that returns if a member is a leaf or not).

Also, i used the CONNECT_BY_ROOT to generate the Root member used in this query (BS) and the SYS_CONNECT_BY_PATH to generate the entire path of the metadata (Very useful to transform parent/child tables in generation tables using this Technic and a regexp [we’ll see this in another post]).

Ok, now that the “Bonus” is written, let’s talk about the WITH that was the main subject here. Even with this Connect by write in the right way with the filters in the right place, we can still improve the performance using WITH.

That’s right, the idea is to prepare our subset of data using WITH before we ask Oracle to do the Connect by and leave it as simple as possible. Let’s take a look:

This is by far the best way to use a Connect by clause. You can, instead of using WITH use a sub-query but I think this way is easier and more organised as well. Also, I know the time difference doesn’t look to big between the previous example and this one but when you join this with data and start to SUM everything, you’ll see a huge difference between this method and the previous one.

Also, some times Oracle get lost with the previous method making everything slower but with the WITH method, it never happens then I advise you start to use this.

I hope you guys enjoy this little tip and see you next time.

Advertisements

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

Playing with ODI and Groovy – Part 3 – Comparing ODI Scenarios from two environments

Posted in GROOVY, Java, ODI, ODI SDK with tags , , on February 1, 2019 by radk00

Hi all!

Now that we can connect to any ODI environment and list all sorts of ODI objects, its time to compare them and find its differences. Today’s example will do a simple check on ODI Scenarios between two environments based on its name and version. We will increment our code logic in later posts by adding different types of filters to make our code more efficient, but as for now let’s do the basic stuff just for us the get the main idea.

One important thing to notice here is that we will compare the objects only by its name and version number, not by its content. In other words, if you have the same ODI object regenerated with the same version number over and over again, this code won’t probably work in the way you are expecting too, since it may tells you that SCENARIO_001 is the same as SCENARIO_001, but in fact it may not be if you regenerated the source scenario more than once.

Let’s pause and talk briefly about ODI Scenario versions

Just a small comment on using different ODI scenario version numbers whenever the code has changed: I’m a huge fan of that and I always do a big effort to implement this concept in every project that I work on. The concept is very simple, but a lot of people don’t do it:

  • Every time that the scenario leaves the DEV environment to be imported in a subsequent environment, it should have an incremental version number.

For example: when you create a new ODI scenario in DEV, you may keep regenerating it repeatedly with the same number, let’s say 1_00_00. After some time, you will deploy it to test environment using version 1_00_00. Now you need to do some code fix or enhancement to it. Instead of regenerate it as 1_00_00 and move it forward, you should increment a version number, like 1_00_01. Your test environment will have both scenarios, but you may call them always using “-1” in the Load Plans or Scenario calls in procedures, which will ensure that ODI will always use the latest one (1_00_01). You may end up with several test interactions/fixes and end up with 10 versions of the same scenario, but that is fine. Once test finishes, you move just the latest one to Production, which will be 1_00_10.

Some people don’t like this approach and their main argument is that it creates a bunch of “repetitive” scenarios over the environments. However, they don’t realize (at first) that this approach creates a lot of benefits over time, such as:

  • Traceability: if you have a defect tracking tool, you may add a comment in the defect (or even in the ODI scenario comments) identifying which defect generated which scenario version, so you may answer all those answers like: “is the code fix xxxx that was generated in version xxxx already in test environment?”. It’s impossible to answer that question if the version is always 001;
  • Control: you may easily see how many defects/interactions a particular ODI scenario had in that development cycle and who did each one of them, so you may have a sense if a code (or even a developer) is having problems to deliver things properly;
  • Roll back is a breeze: if you add something to production but needs to roll it back due to an unexpected behavior, you just remove the latest scenario and that’s it. ODI will pick the old scenario and you are good to go. No need to export the ODI scenario first as a backup, then import it again in case of any problem;
  • Number of scenarios is manageable: if someone still argues about the number of similar objects over the environments, we may always adopt “the last three” versions approach, where we maintain only the last three versions of a scenario in each environment. Three is a good number because its very unlikely that you will want to rollback your code to older than three versions.

Getting back to our main topic

Getting back to our post (and really hoping that you start to follow the incremental version approach from now on, if you dont already), lets change our code to connect to two repositories instead of one.  We already have a code to connect to a source repository, so its just a matter to duplicate it to a target repository (or you may create a function as well to reuse some of the code there):

targetUrl = "jdbc:oracle:thin:@YOUR_SERVER_INFO"
targetSchema = "DEV_ODI_REPO"
targetSchemaPwd = "XXXXXXXX"
targetWorkrep = "WORKREP"
targetOdiUser = "XXXXXXXX"
targetOdiUserPwd = "XXXXXXXX"

targetMasterInfo = new  MasterRepositoryDbInfo(targetUrl, driver, targetSchema, targetSchemaPwd.toCharArray(), new PoolingAttributes())
targetWorkInfo = new WorkRepositoryDbInfo(targetWorkrep, new PoolingAttributes())

targetOdiInstance = OdiInstance.createInstance(new OdiInstanceConfig(targetMasterInfo, targetWorkInfo))
targetAuth = targetOdiInstance.getSecurityManager().createAuthentication(targetOdiUser, targetOdiUserPwd.toCharArray())
targetOdiInstance.getSecurityManager().setCurrentThreadAuthentication(targetAuth)

Next thing is that we will slightly change our listObjects function to receive a list object that will hold our source and target ODI scenarios:

def listObjects (odiInstance,odiClass,listOfObjects) {
	odiObjects = odiInstance.getTransactionalEntityManager().getFinder(odiClass).findAll().sort{it.name}
	for (Object odiSingleObject: odiObjects)
		listOfObjects.add(odiSingleObject.getName() + " - " + (odiSingleObject.getClass()==OdiScenario.class? odiSingleObject.getVersion() : "NA") )
}

We will call it for both source and target connections:

println("Creating Source Scenarios List")
sourceScenarios = []
listObjects (sourceOdiInstance,OdiScenario.class,sourceScenarios)

println("Creating Target Scenarios List")
targetScenarios = []
listObjects (targetOdiInstance,OdiScenario.class,targetScenarios)

Now that we have both lists, is just a matter to compare those. Its very easy to compare and even filter the results in Groovy. The code below will get all ODI scenarios that exists in the source environment but do not exists in target and will filter this list to retrieve only the scenarios that begins with TEST. This kind of filtering is useful, since sometimes you just want to search for certain scenarios, not all of them (again, we will increase the filtering options on later posts):

diffScenarios = (sourceScenarios-targetScenarios).findAll(){sourceScenarios -> sourceScenarios.toUpperCase().startsWith('TEST')}
println("The difference (with the filter) is:")
println(diffScenarios)

If we execute our code, the result will be something like this:

post1

TEST2 scenario was already imported in my target environment, so the result says that TEST1/TEST3/TEST4 exists in source but not in target.

That’s it for today folks. Next post we will learn how do we get this list of different objects and import them in our target environment. If you want the code that was presented in this post, please click here.

Thanks!

Playing with ODI and Groovy – Part 2 – Listing all kinds of ODI objects

Posted in Java, ODI, ODI SDK with tags , , on January 22, 2019 by radk00

Today’s post is short as we will learn how to list any kind of ODI objects using ODI SDK. Although it is simple, it can be used for several different reasons in your daily activities and we will use it to list all the existing scenarios, load plans and folders in our ODI utility. ODI SDK has a very simple way to search for its objects as we can see below:

odi.getTransactionalEntityManager().getFinder(odiClass).findAll()

From “odi” instance object, we get an Entity Manager, which provides the methods to interact with the persistence context in order to make CRUD (Create, Read, Update, Delete) operations against IOdiEntity instances. ODI entity is any object that reside in an ODI repository and so is capable of being persisted (like scenarios, load plans, folders, etc).

From Entity Manager, we may get a Finder, that will receive an ODI Class as a parameter and will return a collection of all objects that belongs to that class. You can “find” any object that implements the IOdiEntity interface. Some examples of ODI classes that you can use are:

  • OdiDataStore
  • OdiFolder
  • OdiIKM
  • OdiLKM
  • OdiLoadPlan
  • OdiLogicalSchema
  • OdiModel
  • OdiPackage
  • OdiPhysicalSchema
  • OdiProcedure
  • OdiScenario
  • OdiScenarioFolder
  • OdiSession
  • OdiSessionFolder
  • OdiUser

So, lets create a procedure in our code that will list all the correspondent ODI objects from a given ODI instance object and a class:

def listObjects (odi,odiClass) {
	odiObjects = odi.getTransactionalEntityManager().getFinder(odiClass).findAll().sort{it.name}
	if (odiObjects.size() > 0) {
		for (int i = 0; i < odiObjects.size(); i++) {
			odiSingleObject = odiObjects.toArray()[i]
			println(odiSingleObject.getName() + " - " + (odiSingleObject.getClass()==OdiScenario.class? odiSingleObject.getVersion() : "NA") )
		}
	}
}

A couple of things about this code. You can see that I’m sorting all the objects that will be displayed by its name. But if I needed something more complex, like sort by name and version number, I could write something like this:

sort {a, b -> a.name.toLowerCase() <=> b.name.toLowerCase() ?: a.version  b.version}

However, this sort wouldn’t work for all classes, since we are using VERSION, which may not be applicable to all ODI objects, like folders. On those cases, we may do a simple check to see if that object belongs to a specific class or not:

odiSingleObject.getClass()==OdiScenario.class? odiSingleObject.getVersion() : "NA"

This one is checking if the current class is OdiScenario. If true, then it will get its version value, otherwise it will just get “NA”.

To run our procedure, it is just a matter to do something like this:

try {
	listObjects (sourceOdiInstance,OdiScenario.class)
	listObjects (sourceOdiInstance,OdiLoadPlan.class)
	listObjects (sourceOdiInstance,OdiScenarioFolder.class)
}
catch (e){
	println(e)
}

The result will be a print of the list of objects:

1

2

That’s it for today folks. You can look at the code in this link (I’ll add one for each post, so its easier for the readers to follow).

See ya!

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”.

Guest Post – Automatic data type conversion for different technologies in ODI

Posted in Guest Post, ODI, Tips and Tricks with tags , , on January 14, 2019 by radk00

/* This is a guest post written by Eduardo Zancanella, one of our friends at DEVEPM. Enjoy and thanks Eduardo for the content!*/

Hello folks,

Hope you are having a great day today.

We would like to share a quick tricky when it comes to transport data between different technologies.

Let’s suppose we were requested to create an ETL process to migrate data from PostgreSQL to Oracle.

Our source has some columns set as TEXT, which does not exist in Oracle. To be able to perform the ETL we would need to translate it to CLOB or VARCHAR2 for instance. But how would ODI knows it to create the temporary tables accurately?

Easy peasy, go to your Physical Architecture, select PostgreSQL and check if the data type is in there. If not, create it following the steps below (if it already exists, go straight to the step 3!)

1) Right click on Data Types, New Datatype

figure01

2) Fill it up the information as below, special attention to what is highlighted:

figure02

3) Click on Converted To and set to which datatype you want it to match in your target, in this case we have chose VARCHAR2.

figure03

After getting all this setup done, let’s run through our example really quick.

Firstly, let’s reverse our source and check if the TEXT fields are in there, keep in mind that we are trying to simplify, so don’t expect to see a full picture of the tool.

figure04

Secondly, let’s create our target table, be aware that here you must use the datatype you chose on the step 3 above for any fields that will be converted. After reversing it you will see as below:

figure05

At this time, our mapping is created, LKM SQL to Oracle and IKM Oracle Incremental Update have been chosen. A quick check on how the CUSTOM_2716 field looks like:

figure06

The hint SOURCE is an extra tip, the transformation for this case has to happen before the data is inserted into the temporary tables, always keep that in mind.

Time to run!

As a first step, ODI will create the C$ and here is where the magic happens:

figure07

C$ was successfully created!

After, ODI will follow its flow and everything should be fine.

That is how we can automatically convert different datatypes among different technologies.

Thank you everyone.

Cheers!