Before we glue it all together in ODI, let us organize the data that we just got from Essbase. In the last post we saw that we gathered all the information we needed inside a generic stage table. Although a generic table is great from an extract perspective, we may decide to split, organize, and load this information in some kind of “historical DW tables” which will help us to analyze the data better over time.
For this series of posts, we have 3 different kind of information: DB Statistics, File Statistics and Outline Statistics. Their structure are very different, so we will generate one historical table for each kind of statistic.
Since we created a generic table to hold all extracted information in rows, now we need to PIVOT the data into columns and load it into the historical tables. This can be easily achieved with a SQL like the one below:
First, we define the columns to be pivoted and use a consolidation function on the data column, like (SUM, AVG, MIN, MAX, COUNT…). Then we specify the data to be pivoted and this data must be a constant in the “IN” clause. Finally, this data is loaded to the DW table using a Control Append approach, since we want to keep adding the current statistics to a historical table for an analysis over time.
If we follow this approach for all three metrics that we retrieved from Essbase, we will end up with three DW tables, like the ones below:
Hi all, let us demonstrate some examples of Java codes that you may use to retrieve statistics out of Essbase. Since we want to store those stats in an Oracle table, let us begin with an example of how to connect to an Oracle DB, which is simple, as we just need the DB URL, username, and password. Below is how we may create the connection. We also have a prepare statement that we will use to do inserts in our Oracle table.
Since we have a generic table, we can have just one prepare statement and use it to insert all kind of stats there.
Connecting to Essbase is also easy, the only additional information is that you also need to pass the Provider Service, which may be “Embedded” or by Provider Service. Here we are basically doing a sign in to the provider service and then select the OLAP server that we want to use.
Now let us see the first code to get some cube stats. First, we get all the cubes from all the apps and for each cube we get its properties (which is an array in a Key and a Value format). This information is added to the prepare Statement and executed (inserted) into the DB.
The result will be something like this:
Let us jump to a second example. In Java we can issue any Maxl command using the “IEssMaxlSession” class. The result set contains columns and rows, similar with what we get in EAS. Then we need to loop through the rows and get the columns that we need. This information is also added to the prepare Statement and executed (inserted) into the DB.
The result will be something like below.
The last example is how we can get stats from the Essbase outline. We can get member information using IEssMemberSelection with a custom query or find the member directly in the outline using find member. The results contain a set of members that we may loop and analyze its properties. In this case we decided to categorize the results by its storage type.
Again, the result will be something like the one below.
That is it for today. Next post I will show you how you can glue it all together with ODI. See you soon!
Hi all! Let us talk now about how we can automate this stat gathering using Java Essbase API. Java is the key technology here since it can easily connect and manipulate Essbase through its API. It also can connect to Oracle Database to store our results, run OS commands and more all in one single code. Java is also great since it may be easily deployed to ODI using Procedures and scheduled using ODI Operator. All in all, combining ODI and Java code creates a powerful and seamlessly integration going beyond the database boundaries.
Let us begin with some Java Essbase API basics. The main goal is to develop one single code that will connect in Essbase, retrieve the statistics information and load that in the Oracle database.
Essbase API is very similar with what we see in EAS, in the sense that the structure of the classes follows the same architecture as in a Essbase server (Server->App->Cube->Otl), which makes it easy to find out what you are looking for when looking at the API documentation.
Since we will store this information in an Oracle table, we will also need to know a little bit about Oracle Java API, but luckily this one is straight forward.
With those two sets of APIs, we are good retrieve all the information that we need from Essbase. Each stat has its own number of columns and metrics, so if we create one table for each kind of structure it will be very tricky to maintain and harder to create any kind of generic code. The best way to extract information is to have just one table where we have the properties in the rows instead of columns, this way we have just one structure for all kind of information, no matter the number of columns that returns and we may create generic code around it. Our final table would look like below.
That is it for this post. Next one I will share some examples on how to connect to Oracle DB, Essbase and how to retrieve some stats out of it. Stay tuned!
Hi all, today’s post is a quick one, but I hear this question very often. Is ODI version XXXXX certified to XXXXX technology version? It is very easy to check it since Oracle keeps it all in one place called “Oracle Fusion Middleware Supported System Configurations” page, but sometimes people get a little bit confused about it.
Once you reach the website, select your ODI version (remember, ODI is inside Oracle Fusion Middleware). My ODI is 12.2.1.4, so let’s check it out:
There will be multiple tabs there. If you want to check in which system your ODI Agent is certified to run for example, you may click on the System tab:
It gives us a lot of information regarding which system, version, java version is supported for your agent. If you want to check which DB version is certified, just click on Database tab:
One last import thing that is always checked is which technology and versions are supported for Source-Target ETL. This can be easily checked on “ODI Source-Target” tab:
That is its folks. Very simple, but useful post. See you soon!
Hi all. I came across a requirement to create Json objects from a set of Oracle tables. We have ODI, so it was natural that the solution would be created there. However, working with JSON objects in ODI is not that easy. For those who already worked with “Complex File” technology knows what I am talking about. Too much setup and any misconfiguration will cause an error that is generally very hard to troubleshoot.
In the past, I did several mappings that would read from Complex Files (including Json), but this time it was an outbound process, so I would need to create the Json objects, not read from them. I tried to search on the internet, but nothing was clear. I was not sure if the Complex File technology would work to create outbound files and I was not in the mood to play with XSD files this time, so I needed to find some other solution.
Talking to a friend/co-worker of mine, he asked me if we could not leverage the Oracle’s JSON_OBJECT function somehow in ODI. First, I did not know what that function was about, so I researched about it:
The SQL/JSON function JSON_OBJECT takes as its input one or more property key-value pairs. It returns a JSON object that contains an object member for each of those key-value pairs.
It looked very interesting. So, I give it a try in SQL Developer. It worked very well:
Basically, you may add any number of columns to the function and it will create a valid JSON object out of it. Pretty neat, right? Now it is just a matter of how to add it to ODI, which thankfully is very easy. First, I created a model in the File technology:
It contains only one column, with 4000 characters. Then I created a mapping and mapped all the required columns to the JSON_OBJECT function, like this:
That’s it. Pretty simple. When we run the mapping, we have an outbound file like this:
Now you may send the outbound file to any application that needs to consume JSON objects. One thing that you may need to consider is that you will probably get into trouble if your Json objects ends up being larger then 4000 characters each. I didn’t test it, but either Oracle or ODI will probably complain about it.
I hope you have liked it. This is not a “traditional” way to create Json objects, but for sure it is the easiest one! See you next time!
Hey guys how are you? I hope you guys are not insane after this 2 months of quarantine. Anyway, is time for us to finish the send email job. In the previous post HERE I explained the Jython code and the HTML code that we need to use to create our HTML table in our email. Today we’ll going to do it become dynamic.
As we saw, for every row we want it we need to have a block of HTML code that will draw the table, color the table and write the content of the cell in our table. We need this to change dynamically if we want to be useful for us, and to do that we need to write a SQL code to create this HTML code for us.
In my case I generate this code here to be my header:
This is saying that I’ll have 16 columns (COLSPAN=”16″) with Center alignment and the name of the table will be “Restatements Control – Group ID: 1” (where the 1 will be dynamically generated as well).
Now we first need to write a query to get this info for us. Since this is a very project related query, I don’t think it’ll do any good for you guys to put my query here, but I’ll explain what I was looking for. First I’m querying the ALL_TAB_PARTITIONS to get all partitions related with that table. Then I was querying a control table that every time the jobs run, it inserts in this table the period loaded, if there’s errors or not, the log folder path and the interface that run the job.
After that I do a FULL OUTER JOIN between this 2 tables to see all partitions I have and how many of these partitions were already executed. Next I PIVOT the information to get a table like data and the results is similar to this:
I created some simple Status code to make easy to manipulate later. NP is “No Partition”, N is “Never Run”, Y is “Warning”, R is “Error” and G is “Success”. Also, when is Y or R I have the Log Path associated with that run, this way the users can click and go to the log folder of that execution.
In my case this is important because this is for an restatement process where the business want to restate the entire past and we have millions of rows per partition, and they want flexibility to run as fit. Then we need to track the executions over time.
Now, the only thing that needs to be done is to convert this information in HTML code. This is easy since we just need to concatenate strings all over the place. Let’s see how I have done it:
The result is one big string for each row the query results. Each column was concatenated with a “Enter” between than, so when this code is used, we’ll have proper indentation for readability. This is the query I used to concatenate everything:
Basically is a lot of DECODE’s to convert my STATUS code in colors and some REGEXP to split the STATUS from the Log path. That’s it for SQL. Now the only problem we have is that this is a very big string and the only way for us to store this is to use a PL/SQL because inside a PL/SQL a Varchar2 (32767 bytes) variable is bigger than inside SQL (4000 bytes).
We just need to create a simple PL/SQL to insert and concatenate all this rows into a CLOB that is a little big bigger (4 GB). To do that we just need to do something like this:
DECLARE
CURSOR C_HTML_TAG IS
SQL HERE;
V_HTML_BODY CLOB;
BEGIN
FOR DADOS IN C_HTML_TAG LOOP
V_HTML_BODY := V_HTML_BODY || TO_CLOB(DADOS.SCRIPT);
END LOOP;
INSERT INTO FDM_ODI_RUN.TMP_HTML_BODY_DW (HTML_BODY) VALUES (V_HTML_BODY);
END;
That’s it, now for the easy part, use it in ODI. To do so we’ll have a command in the SOURCE querying the TMP_HTML_BODY table and then we’ll pass #SCRIPT info to our Jython target code:
Hey guys how are you? Today I’ll talk a little bit how can we create a dynamic HTML table email from ODI using Jython.
First of all, let me give you a little bit of context. I had to build an ODI process to restate the past data in our DW. That means, the business wanted, to a certain point in time, to go back all the way to the first period we have in our DW and restate the data based in a map table that they provided.
That’s all right, the biggest problem is that this table is partitioned by Source System and Period, and the business wanted the process to be flexible enough to let them run 1 period and 1 source system at time or to run an range of period and ALL sources at time (and any combination of these 2).
Also all right, my problem now is how to provide the business with a reliable way to tell them what they already run, what is still pending, if we had an error in a period or if there’s some validation fall outs in a period. In other words, how to track the process during execution.
My answer to that, I decide to send a email with a table that shows the source system and years in the rows and the months in the columns, and based in a color code, I paint the cells based in the status of the execution.
This post will be about the Jython/HTML code we wrote and the next post will be about how to make it dynamic in ODI. Let’s start it with the Jython part:
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
mailFrom = "ODI Services <donotreply@ODI.com>"
mailSend = 'email@here.com'
msg = MIMEMultipart()
msg['Subject'] = "Subject here"
msg['From'] = mailFrom
msg['To'] = mailSend
html =
"""\
HTML CODE HERE
"""
part = MIMEText(html, 'html')
msg.attach(part)
s = smtplib.SMTP('SMTP SERVER HERE')
s.sendmail(mailFrom, mailSend.split(','), msg.as_string())
s.quit()
This is everything you need to have in your procedure to send a HTML code by email. It’s a very simple code, basically we import “smtplib” lib and that will handle the email sending, after that we just need to inform the user, password and SMTP server and use the “sendmail” to send the email. Pretty straight forward.
Now, in the meddle of the code, we have the HTML part that needs to be included. In our case, it’ll be a table. To test the HTML code, you can google “HTML test runner” that it’ll bring a lot of places in the internet where you can run your HTML code and test to see the results. It’s pretty handy, and I’m using this one here.
To create a simple table in HTML we just need this code here:
This code is also fairly simple and basically we have:
<TABLE> tag, where you define the margins, border size, width of the table, cell padding and cell spacing. There’s more options there but you can easily find in the HTML doc.
<TR> tag, where you define the amount of columns using the COLSPAN property as well the alignment of the text there
<TH> tag, where we define the cells of our table itself. There’re a lot of properties for this but I’m using juts a fix 20% width for each cell, just to size them the same (since I have 5 columns), the Color of the cells and the message I want to send.
This is my legend table that will come above my real table, but the configuration is the same in both cases. We’ll have one <TR> block for each line we want to have and as much <TH> lines we need for each cell we want to have. In the end my final table is like this:
As you can see, I send an email with all periods that needs to be restatement showing if the interface already ran, if that was a success, or it had warnings or errors (with the link straight to the error file, if it was not loaded yet and even if we don’t had the partition created for that period/source.
Now, as I said, we need one <TR> per line and, in this case, 16 <TH>, one per cell. As you can imagine, that’s a lot of code that needs to be write there. thanks god I’m using ODI to do that for me, and we’ll take a look on this in the next post.
Today I’ll going to show you how to “Save As” an essbase outline using ODI and Java. I normally use Maxl and OS commands to do this kind of things but turns out, there’s no Maxl to do that.
In fact, this is very interesting even if you don’t like java, because it’ll show exactly what Essbase does behind the scenes to save a outline. Let’s start.
First of all we’ll going to need some essbase API libraries. In ODI, the Client and the Agent already include some Essbase Jars in the Lib folder (one Lib folder for the Client and one for the Agent).
If you need anything outside what you have there you need to copy from essbase to the Lib folders and restart the agent. In this case we’ll need to import these:
Since I’m using an ODI procedure, I can set in the Command on Source tab the Essbase connection I want and then I can get in the Command on Target the User name, password and the server name as well, using the ODI substitution API, this way I can use what is store in the Topology without to worry about hard-code any password in the code.
In the next step we need to connect in Essbase using:
Basically what this is doing is to instantiate and essbase server, connection in the domain using the Command on Source information and then connect into a specific Olap server. After this we are ready to start execute some commands. And now it gets interesting. This is exactly what essbase does behind the scenes:
As you can see, the command ask for the name of the app you want to save the outline, the type of the object (that is OUTLINE), the folder path for the source Outline and the last parameter is a true or false to tell if we want to unlock the object or to lock. True is unlock
If you look into the target folder during this step, you’ll see that Essbase will copy the source .otl to the target folder as .otn
We just need to ask for the cube to restructure and pass the option KEEP_ALL_DATA, after that we can close the outline
Interesting thing here is that if you get a outline, rename to .otn, put this file inside a folder and force the cube to restructure (via EAS), it’ll automatically merge the created .otn with the .otl.
Also, this is why oracle recommend to have double the size of the cube in memory, because when we do a restructure, we have 2 outlines open at same time, the .otn and the .otl.
Hey guys how are you? Let’s take a look today in the opposite of S01EP12 situation, in fact we’ll use the same example again to show how can we convert a string in a list of values in a easy and dynamic way, starting with this query here:
I’ll transform this query in a with and I’ll use REGEXP to put this back into a list of values. This is very useful when we extract metadata from essbase for example, because essbase exports the UDA’s as a list of values. Of coarse this has many uses other than this but let’s keep this one in mind.
Now what we need to do is to split the strings by comma in this case, then the idea is to count the amount of commas we have in a row and split the strings by that amount.
The idea here is to use the REGEXP_COUNT to count how many words we have in between the commas and then use it to multiply the rows in the CONNECT BY LEVEL. For example, if we have 3 words, the connect by will create 3 rows of the same row, one with the LEVEL = 1 another with the LEVEL =2 and the last one with LEVEL=3.
With that we just need to use the REGEXP_SUBSTR to extract the words based in the LEVEL, this way we’ll have the REGEXP_SUBSTR(STR, ‘[^,]+’, 1, LEVEL (that will be 1 for the first row, 2 for the second and 3 for the third one).
Today I’ll post something that is very simple but very useful specially when working with ODI.
When we work with partitioned table we know that if we filter that table by the partitioned column Oracle will use that partition as source of data. But what if we are doing an Insert, Update or Merge?
There’s another way to explicit refer to a partition and make sure Oracle will be working inside that one and is by defining it in the From clause.
For example if I want to query the Partition “DELL_BALANCES_FY20_FEB” I can query:
As we can see, after the table name I specified the PARTITION (DELL_BALANCES_FY20_FEB) and put inside the parentheses the partition name (don’t specify as string) and that makes oracle distinct all the rows in that partition, and my Distinct of the PARTITION_KEY shows only one results as expected. (this command needs to come before the table alias).
If we are doing an Insert, Update or Merge the idea is the same:
This way we can, specially in the MERGE, make sure Oracle will be working in the right partition in the target table.
And it’s specially useful with ODI because we always know the partition we want to query or insert data when we use ODI, then we can always bind Oracle to a specific partition and make sure he’ll stay there.
DEV EPM 