Archive for April, 2019

DEVEPM in the land of the free!

Posted in Career, ODI with tags , on April 29, 2019 by radk00

Hi all,

Today’s quick post is not a technical one, but it is just to share with you some good news. I (Rodrigo) have recently moved from Brazil to USA and I’m very excited about it! This means new challenges, new opportunities and probably some new content to the blog 😊

I have accepted an offer to work for Innive Inc, an IT consulting company that you may read more about it in our site. I’ll still work a lot with data integration, but I’ll probably move away a little bit from EPM. However, it doesn’t mean that I won’t play around and do some tests when new EPM stuff comes out 😉

Thanks everyone! And don’t forget that we will be at Kscope19 this year, so if you want to talk to us there, please feel free to send us a message!

See ya!

Advertisements

ORACLE SQL for EPM tips and tricks S01EP04!

Posted in ACE, Connect By, Oracle, Oracle Database, Performance, REGEXP, SQL, Tips and Tricks with tags , , , , , , on April 9, 2019 by RZGiampaoli

Continuing the Oracle SQL for EPM series, today we’ll extend a little bit the “Connect by” post to see some neat thing we can do when we mix regular expression with connect by.

First of all, let’s start explaining the basic of this approach. We can use Connect By and Level to generate data in Oracle like this:

This is very useful when you need to generate 2 rows for each row we have in a query, then you can do a Connect by Level <=2 and use it as a query without join, duplicating everything in the source query. Then you can use a Decode or Case to say, Case when level = 1 then x when level =2 then y….

But for us this is just the basic concept that we need to understand for our trick. let’s get back to our metadata table, the one with parent child relationship and with all metadata from all apps and all dims that we have. Let’s say we need to create an hierarchical table based in a parent child table. Some people does one union for each level that we have in the source table (too much work), another will do filter all over the places, I don’t, I just use connect by with regular expression. Let’s take a look.

As we can see, we have 17 levels or generations for the account dimension. quite big. First of all, let’s do a connect by path to flat the entire hierarchy in one row and remove the parent child relationship.

Now, as we can see, if we just do a connect by prior with the sys_connect_by _path, the query will generate 1 row for each level of data. We don’t want that, unless you want to have the ability to input data in the upper level of the hierarchy, that is usually not the case. What we want is to have one the entire path for each leaf member, then we need to filter just the leafs in our queries. In my case my table already have a column that says if a member has children or not, but I’ll proceed as if I don’t have it.

If we don’t have the leaf information we can generate it by using Connect_By_IsLeaf that basically return 0 if is a parent or 1 if is a leaf. Again, we are filtering this outside the connect by prior and now we have just the leafs and the entire hierarchy above him. Next step is to split this back into rows.

REGEXP_SUBSTR is the perfect tool to split the string we have into new columns. As you can see, we can use the follow expression: REGEXP_SUBSTR(Column, ‘[^|]+’, 1, 1) Basically the regexp uses what is in [] to find what yo want and split it in the way you want. In our case I’m looking for the first | (that is defined by the 1, 1) and get the first word after it. Then I have another regexp and I changed the 1, 1 to 1, 2 to get the second word and keep doing this until I get to the 17 generation (as we saw in the beginning).

Basically if you have 17 generation you need to have 17 columns in the table, then you need to have 17 REGEXP, one for each column, you just need to change the 1, 1, 1, 2, 1, 3… until the 1, 17.

Here’s when ODI come in handy because if you use ODI you just need to do a select in the source to figure out how many generations you will have and then generate dynamically the 17 REGEXP and pass this to the target dynamically. It’s very easy since the code itself is always the same and the only thing that changes is the second parameter, and it’s a sequential parameter.

I hope this can be as useful for you as it’s for me and see you soon.

Playing with ODI and Groovy – Part 4 – Exporting/Importing ODI Scenarios with SDK

Posted in GROOVY, Java, ODI SDK with tags , , on April 9, 2019 by radk00

Hi all, I’m back with the continuation of this Groovy and ODI series. Last post we saw how to find the different scenarios between two environments. Today we will look on how we may export those different scenarios from our source repository and how to import them in our target repository. We will do a two-step operation: first we will export the different ODI objects from our source repository as XML files into a folder and then we will import those xml files into our target repository.
Our code is very similar to the one that we did for post 3, but we will need to enhance it a little bit. First thing that we will have to change in our code is the function that creates the list of objects. In the previous post, we were just adding the name of the scenarios to the list. Now we will need to store the object itself in the list, since we will need to have the ODI object (scenario) to have it exported.

def listObjects (odiInstance,odiClass,listOfObjects) {
	odiObjects = odiInstance.getTransactionalEntityManager().getFinder(odiClass).findAll().sort{it.name}
	for (Object odiSingleObject: odiObjects)
		listOfObjects.add(odiSingleObject)
}

Also, we will need to create a variable that will indicate the path where the objects will be temporarily exported.

exportPath = "C:\\Odi"

One import thing that we will need to change is how to compare the objects. In the previous post, we were simply comparing them, as they were strings, which was ok for our propose there. However, now we cannot simple compare the java objects because they will be different even if they represent the same scenario name/version. They are considered “different” because they came from different environments and logically, they represent different ODI entities.

diffScenarios = []
	for (Object odiSingleObject: sourceScenarios)
		if (targetScenarios.find {targetScenarios -> targetScenarios.getName() == odiSingleObject.getName() && targetScenarios.getVersion() == odiSingleObject.getVersion()}.equals(null))
			if (odiSingleObject.getName().startsWith('TEST'))
				diffScenarios.add(odiSingleObject)

I’m basically doing three tests to see if the source scenario will be migrated or not: first I compare its name, than its version and finally if its name starts with TEST (this last step does not need to be done if you want to get the complete scenario list). Next step I just print the scenarios names and versions that will be exported/imported:

println("List of ODI Scenarios that will be migrated")
		for (Object singObject: diffScenarios)
			println(singObject.getName() + "_" + singObject.getVersion())

Now comes the new code:

encode = new EncodingOptions();
transSource = sourceOdiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());
	exportService = new ExportServiceImpl(sourceOdiInstance);
	for (Object singObject: diffScenarios)
		exportService.exportToXml(singObject, exportPath, true, false, encode)

Export objects in ODI SDK is very straight forward: you need to inform which scenarios you want to export (in our case, all objects that were stored in diffScenarios array), the path where the object will be exported and the encode option that will be used. In this case, I just went ahead with the default encode options.

Importing objects is also easy, but similarly to a database, you need to explicitly commit your actions to make it effective in the target repository. Also, for the sake of simplicity, we will import all new scenarios under “root”, but we could explicitly say under which ODI objects we would want to have it imported to:

tm = targetOdiInstance.getTransactionManager()
	transTarget = tm.getTransaction(new DefaultTransactionDefinition());
	importService = new ImportServiceImpl(targetOdiInstance);
	for (Object singObject: diffScenarios)
	{
		println(exportPath+"\\SCEN_"+singObject.getName() + "_Version_" + singObject.getVersion()+".xml")
		importService.importObjectFromXml(ImportServiceImpl.IMPORT_MODE_SYNONYM_INSERT_UPDATE,exportPath+"\\SCEN_"+singObject.getName() + "_Version_" + singObject.getVersion()+".xml", true, null, true)
	}
	tm.commit(transTarget)

Once you run the job, you will get the following:

1

Our target repository already had TEST2, so that’s why its not in the list. When the user connects to the target repository, he will see the following:

2

That’s it for today folks! Hope you like it! See you soon! The code for part 4 can be found here.