Archive for the Oracle 11.2.0.4 Category

Oracle SQL for EPM Tips and Tricks S01EP12

Posted in ACE, Data Extract, Hacking, Hyperion Planning, ODI, ODI 10g, ODI 11g, ODI 12c, Oracle, Oracle 11.2.0, Oracle 11.2.0.4, Oracle Database, SQL with tags , , , , , on March 31, 2020 by RZGiampaoli

Hey guys how are you keeping? I hope everybody is healthy and keep this way in this difficult times.

And to make our life less complicate, here’s another tip. Let’s talk about how to concatenate stuff in Oracle.

Imagine a simple case, we want to query the Planning repository to get the list of UDA’s a member have. We can easily do that by query the HSP_OBJECT, HSP_MEMBER_TO_UDA and HSP_UDA tables.

I’m filtering just 3 products to make it easier for us to see. The results shows that each project has a different number of UDA’s, and we never know how many it’ll be, then the easiest way to concatenate them is to use the command LISTAGG (or WM_CONCAT if you are in a DB version prior to 11.1).

The command is very simple LISTAGG(Column, Separator) WITHIN GROUP (ORDER BY column). As we can see the command allow us to select the separator we want (can be comma or any string really) as well to order the results by another column). Let’s take a look in the example above.

As you can see, it easily create a list split my comma (as specified) for me, and the nice thing about it is that I don’t need to do any string treatment if return null or if I have just one string on it and things like that.

This is an extremely good Function and we heavily use it in ODI to generate dynamic code because its simplicity, for example, we can generate a SQL statement on the fly using the command on source and command on target:

With this results we can easily pass this info to the command on target to generate a dynamic query where ODI will replace the columns we got in the target as well the table name and will also loop for each row we have in the source. This is very handy.

And for the ones that are not in the ORACLE 11.2 and ahead, we can still do that using WM_CONCAT. Is not as powerful as LISTAGG, but works pretty well. Let’s try the first example again:

I cannot show you the results since WM_CONCAT was decommissioned in the 12c (my version), but it’ll work like this. We don’t have the option to choose the separator and to make the string unique and to order by it we need to add DISTINCT in the command WM_CONCAT(DISTINCT column).

I hope that is useful and have a great day.

Advertisement

1 Comment »

Oracle SQL for EPM Tips and Tricks S01EP08

Posted in Connect By, DEVEPM, Dimensions, EPM, Oracle, Oracle 11.2.0, Oracle 11.2.0.4, Oracle Database, Performance, REGEXP, Tips and Tricks with tags , , , , , on November 26, 2019 by RZGiampaoli

Hey guys how are you? Today I’ll expand a little bit more the REGEXP team just to show how easier and powerful these functions are, not to mentioning how dynamic.

In the last post S01EP04 I explained how to split a string in different columns. The thing is, what if you don’t want to split in different columns? what if you want just to have the string as a list in the same column?

We can also do that with a very nice trick using REGEXP_COUNT. Let’s take a look on this.

In the previous post we had a query like this:

As I said there, using REGEXP is very simple to “walk”trough the string and get any part of it you want. And is also simple to transform a string in a list of values. To do that we just need to add one extra connect by and the REGEXP_COUNT function like this:

What that CONNECT BY LEVEL <= REGEXP_COUNT(PATH, ‘[^|]+’) is doing is basically increasing by 1 for each “|” he finds in the string we have, in other words, he is dynamically “walking” through the string looking for “|” and passing the Level he find a “|” to the REGEXP_SUBSTR above. The result is:

As you can see, the REGEXP_COUNT will return the number of “|” he finds in the string, the CONNECT BY LEVEL will multiply that string by that number and the REGEXP_SUBSTR will “walk” the string using the LEVEL of each row to extract the list of members from that string.

This is very useful to manipulate strings that you don’t know the amount of members you want to extract from that, like UDA members for example, that when you extract from ASO cubes, comes in a string all together.

I hope this is a useful trick for you guys and see you soon.

Leave a comment »

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.

Leave a comment »

5 Steps to do an In-Place Migration of OWB/DB from 11.2.0.1 to 11.2.0.4

Posted in EPM, InfraStructure, Install, Migration, Oracle 11.2.0, Oracle 11.2.0.4, Oracle Database, OWB, Upgrade on September 1, 2014 by RZGiampaoli

 

Hi all, today I decided to get out of my comfort zone to talk about the in-place OWB migration and a little bit of infrastructure. I decided to do that because the follow reasons:

First, I hate infrastructure, probably because I am not good at it and in all clients that I’ve worked, this is always a headache. Some times because it is poorly done, some others because everything grows but the infra. But the worst part is:

Nobody knows Hyperion or the BI tools enougth and in the end when something bad happens, it ends up that I’ll need to fix the problem, and normally there’re two problems, the infra itself and the guys from the infra asking for evidences that the issue is from infra. Sorry guys, but this is a consultant point of view J

Second, because it took me 2 days to understand what was the OWB migration and how to do it.

Third, I searched the entire internet and did not find any complete step-by-step tutorial about it. Probably because the Migration happens to be the database migration not the OWB migration.

Before I start, let me talk a little about my infra. As I said, I hate infra and I am not good at it then please consider this post as a tentative to help people and not an absolute truth coming from an expert or something.

Because I hate infra, I tried to make mine the best it could be (or at least, the best I could make it).

The Server:

The_Server

This is a cheap machine (even in Brazil that everything is expensiveJ). I only upgraded it with 32 gb of memory and by now has only one HD with 1 Tb (I have more HD in my drawers But I’m not needing it by now).

The Virtualization Software:

For this server I installed in a 1 Gb pendrive the VMware ESXi 5.1.0. This is a good tip, the ESXi is by far (and I mean lightning years far) the best virtualization software in the market, and for a server with max 32 Gb memory, it’s for free.

The VMs:

The_VMs

I have six VMs up and running all the time in this server. Never had any performance issue, in fact they are a lot faster than the servers in some of my clients are. Of course, I have a clean installation of everything.

By the name of the machines you guys could figure out that I like old mythology J and I’ll explain the infra using it.

First we have Cerberus, the hell hound that has a zentail installed on it to protect the entrance of the hell realm. Also it’s used to external access, VPN, FTP and DNS.

Then we have Tartarus, the realm of the dead’s, and also what supports the earth (or gaia that’s the name of my extra net). This is an oracle 11g Database, because all the repositories are on it. It is the base for everything.

Also I have Niflheim, that’s almost the same thing but for the Nordics. This is an Oracle 12c database. There are Golden gates running between both databases for replication and test proporses.

After that, I have Olympus, where all the foundation, planning, eas server and reports are installed.

Then we have Pantheon, which is on top of Olympus, where OBIEE and Endeca is installed.

All these machines are running on top of Oracle unbreakable Linux OS.

In the end, we have Hyperion, which is a Windows server 2008 R2 machine where I have only Essbase and ODI agent installed. Why I did that, because both tools heavily use the file system and using windows make everything a lot easier to access, configure, and work with.

Also, I found in an old version of Essbase (11.1.1.3) that some ESSCMD commands doesn’t exists in Linux, like “deploy studio” command. I had a client that had to migrate the Essbase to windows because this and some performance issues.

However, the thing was the file system and I installed some clients in this machine this way my friends needs only my VPN and a terminal Server to access this machine and make some tests.

Enough about my infra and let us talk about the migration. First of all there’s no OWB migration. OWB is not like ODI that you need just to get/patch of a new version and update the repository.

For OWB you need to upgrade the entire database, and then you can connect using your new client (and if you use windows, you will not have the client in the same version of the repository. However, we’ll talk about this later).

Ok, sinceI do not like to have many oracle homes in my server I decided to do an “In-Place” upgrade. Upgrade is a strong word for this because it is an installation from the scratch not a patch or something.

If you have any type of Virtualization now is an excellent time to create a snapshot from your database. Thanks God I did that because he knows, I needed.

Ok, after try a lot of stuff and commands that never worked I did it in my way, and worked, then please if someone see something incredibly ugly (and maybe I’ll not know that it was an ugly thing) please let me know.

Basically for the “In-Place” installation we need to deceive Oracle installer to believe that your server has no other oracle instance. For this, you need to detach the oracle home from your inventory.xml. Oracle give us a command that I could never make it work:

On Windows:

%ORACLE_HOME%\oui\bin\setup.exe -detachHome ORACLE_HOME=D:\oracle\product\11.2.0.2\dbhome_1

On Linux:  

$ORACLE_HOME/oui/bin/runInstaller -detachHome ORACLE_HOME=11.2_ORACLE_HOME_LOCATION

I found other “Versions” of this command but I couldn’t make any of them works as well. Then I had to find my own version, which I can guarantee, it is a lot easier that this command. Rename the “inventory.xml” to anything you want. In my case this files is in this folder:

/u01/app/oraInventory/ContentsXML/

This will make the oracle installer believe that he will be the firstJ.

After that you need just to rename the dbhome folder or the version folder, anyone will do the trick.

/u01/app/oracle/product/

Renaming the folder will remove any need of backups because everything you need will be there.

Cool, after these two-steps you can install the DB software. Make sure to install only the software. The DB we will migrate later on.

I will not cover the installation here because even I did not have any issue with that, and, there are many tutorials that cover the installation in the internet. Basically you need only to click in next and remember to select software only. That is all.

Ok, after the software install we need to copy some files from the backup that we did when we renamed our previous directory.

We need to copy everything that is inside the folder “dbs” to the new “dbs” Folder. For me the path is like this:

/u01/app/oracle/product/11.2.0/db_1/dbs/

Doing that you have the initialization files from the old db in the new one.

Also copy the files in the follow folder to the respective ones in the new installation:

  • ORACLE_HOME/network/admin
  • ORACLE_HOME/hostname_dbname
  • ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname
  • ORACLE_HOME/owb/network/admin

With this, we have everything to upgrade the old db to the new version. Now we need to connect in the sqlplus as sysdba:

Go to the folder $ORACLE_HOME/rdbms/admin/ (this is the location of all sql we need to run to upgrade the repository) and then:

$ sqlplus / as sysdba

We need not to start the DB in upgrade mode. For this:

SQL> startup UPGRADE;

This will mount the database but will give access only for sysdba users. Now we just need to run the follow scripts:

Pre-Upgrade Script

SQL> spool upgrade_info.log

SQL> @utlu112i.sql

SQL> spool off

(This is a pre-upgrade script. Oracle said to copy it from the rdbms directory but I didn’t get why then.. If you have any problem please follow oracle orientations);

Upgrade Script

SQL> spool upgrade_info.log

SQL> @catupgrd.sql

SQL> spool off

(This is the upgrade script. It takes a while to run then, be patient);

Pos upgrade scripts

SQL> spool pos_upgrade_info.log

SQL> @utlu112s.sql

SQL> spool off

(This script provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed)

Recompile Invalid Objects

SQL> @utlrp.sql

(This will recompile any remaining stored PL/SQL and Java code in another session)

After this, we need just to:

SQL> shutdown immediate;

SQL> startup;

That’s it. Everything I did to upgrade the Oracle database in place. As I said, I am not a DBA and probably if a DBA read this post he will laugh, a lot, but it worked and it was the only way I found to do it.

The Client

Ok now, if you have your client in a windows machine, you will realize that oracle did not release a windows version for the OWB client 11.2.0.4. The latest client for windows is in the 11.2.0.3 version. However, Oracle said that this version is fully supported to work with the 11.2.0.4 version. You need only to:

Download the 11.2.0.3 version for windows:

Install the patch 16568042:

Change the file:

$OWB_HOME/owb/bin/admin/Preference.properties

Set:

OverrideRepositoryVersionCheck=true

OverrideRuntimeVersionCheck=true

That is it. You database is upgraded, your client is upgraded and you need only to upgrade the OWB repository. I will not cover this here because this can be found in the internet easily.

Also, if you want to change the language of your OWB client you need only to:

Open $ORACLE_HOME/ide/bin/ide.conf and add the following line to the file:

AddVMOption -Duser.language=en

I know that this is very different from what we have been posting here but it took me 2 days to make this works, and I do not want anybody else losing other 2 days to do something, that in the end was very simple. If you think of it now, for the “In-place” migration we only need to:

  1. Rename the inventory.xml file;
  2. Rename the db_home or the 11.2.0 folder;
  3. Install the new version of the software;
  4. Copy some files from the old install to the new one;
  5. Run three scripts;

That is all. I wish you guys found this useful and until next time. Thank you.

 

1 Comment »