Last week I read a post from ‘Slawomir Chodnicki’ on “Creating Dashboards with CDE”. Thanks to him for a really useful post.
I wanted to install the CTools on my Pentaho environment. But the issue was that I was on Windows and the CTools installer is made for Unix/Linux environment.
I am not a big fan of Cygwin plus due to IT restrictions it was a lengthy process to get CYGWIN installed on my machine, I so I tried to install CDF/CDE/CDA without a script. It’s not That difficult actually. You just need to follow the steps mentioned in the Shell script “ctools-installer.sh”.
Steps:
1. Get the CDF/CDE/CDA files from location mentioned under ‘downloadCDF/CDA/CDE’ functions.
2. Put them under a temporary directory.
3. Remove any existing CDE/CDF/CDA files.
4. Unzip them as per details given under function ‘installCDF/CDE/CDA’.
5. Copy them to respective location.
Things to remember:
1. You’ve to first Unzip the dist.zip files. This will give you another set of Zip and Jar’s.
2. Put the Jar’s into the location under Tomcat installation for Pentaho BI Server. In my case it was “tomcat\webapps\pentaho\WEB-INF\lib”.
3. Now the Zip files which You get from the Step 1, contain two sets of directory structures. One will be the configuration files for CDF/CDE/CDA and other will be Samples.
4. Samples need to be placed under ‘/biserver/pentaho-solutions’ and Configuration files under ‘/biserver/pentaho-solutions/system’
It took me 20 mins max to set this up. I started the BI server and lived “Happily ever After”
It’s no big deal but it might save you some time and un-necessary CYGWIN setup.
In the last Post I explained how I implemented Surrogate Key using SSIS, for Dimension Table Updates.
I tried to do the Same thing with Kettle and I was not amazed that How easy it was. Kettle gives you so many options to do a task.
There are many ways to implement it. I’ve implemented it using “Add Sequence” and “Modified JavaScript”.
1. Logic is to First get the Last Used Surrogate Value. This can be done in two ways, either we can use a sequence (if the DB supports) or we can use Kettle’s own sequence generator. In this example I’ve used the DB sequence as I want this example to be similar to the SSIS exercise which I did although Latter should be a better option as it’ll have Performance benefits.
2. Next to pass this value to a Kettle Variable using “Set Variable” Step.
3. Save this Transformation which will act as a Configuration Transformation for the Main Transformation.
4. Create a New transformation which will do the Actual Loading.
5. Get the Staging Data by using Input Step (I’ve used Flat file Input to keep things simple)
6. Then I used “Modified JavaScript” Step.
In this step first Get the Variable Value, which we set in the Step2. Then increment this Value for Each row and we’re done.
7. Now just pass the Output of above step to a Output step (a DB table or a Flat file).
Another alternative could’ve been: Pick Max value from the Primary Key column of the Table and assign it the Variable, but that can be a performance issue with Larger tables.
Issues which I faced:
How to use Variables into “Modified JavaScript Step”? There is a nice method “getVariable” available under “Special Functions” Category with which we can reference the Variable which we are using in the Transformation. This is the thing which I like most about Kettle, It is an ETL Tool with a Programmers Mind-set rather than being a mere Drag-N-Drop Tool.
Another issue was with Formatting. With the JavaScript I had converted few variables from String to Number and Vice-versa, without passing the Optional Formatting Parameter.
So a thing to note always use the formatter explicitly otherwise Kettle will format the Variables as per its Logic.
So I was expecting “1234” and I got “1234.00” or “000000001234”.
str2num(MAX_ID, "#")
num2str(strVarValue, "#")
I kept getting this Error “Sequence Not Found” when I validated the Transformation, although the Sequence was there. I tried the SQL Editor available inside the Kettle and even it verified that Sequence was there. It was really annoying. I restarted the Database, Cleared the Cache of the DB but nothing worked.
Actual issue was that I had created my DB schema under a DB with Multiple Schemas in it and while creating the Connection in Kettle I had filtered it (Attached image).
But it seems Kettle and DB2 aren’t so friendly and that’s why the Error.
Solution to this issue is that Create a OS User with same name as Schema and then Create the User Objects in the schema with the connection under this New User’s Credentials.
Yesterday I finally decided to try my Hands on Microsoft’s ETL tool, SSIS. I have worked with various ETL tools and this was the one I never used. It was part of a PoC (Proof of Concept) work which I had to. The objective was to read a text file and load it into a DB. This was kind of a Standard Dimension loading procedure. So I just had to extract the attributes from the Flat file, transform it and then load it into the DB.
Interesting part was the Creation of Surrogate Key or Primary Key. This is a really a good way to analyze capability of your ETL tool.
Informatica gives you many options like You can use “Sequence Generator Transformation” or You can use Global Variable or You can use Database Sequence (if your DB supports it).
Pentaho-Kettle ETL allows you to use Variables and “Database Sequence” to deal with this situation.
In SSIS you can do it with the Help of an “Execute SQL Task” and “Script Component” Transformation. It used a simple logic:
1. Pick data from a Source (flat file in my case).
2. Get Max value of the Surrogate key from Database Table.
3. Pass it to a Solution Variable.
4. Use the VB script, which will assign it to a Script Variable which will be incremented for Each row of data.
5. Concatenate the Resultset using Derived Column Transformation.
I googled for the Script (to increase the variable For Each Row) and found this Blog really useful.
There were few hiccups which actually were quite frustrating and time almost took me 2 Hours to finally solve them.
Firstly You should always match the datatypes of Variable and the Return type of Database Resultset.
Suppose your Query Return a SUM and the Variable is of type String you might get this Error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Max_ID": "The type of the value being assigned to variable "User::Max_ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
Next thing to take care of is the Resultset mapping under Execute SQL Task editor. When you’re using ODBC data source, you should use Resultset Name as 1 otherwise you might get this Error:
“[Execute SQL Task] Error: An error occurred while assigning a value to variable "Max_Seq_ID": "Result column index 0 is not valid.".”
Then I tried changing it to Column name which I’ve used in the Query and then it gives error: (I’ve connected to a DB2 database with ODBC)
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Max_Seq_ID": "Value does not fall within the expected range.".
If you change Resultset name to 1 with ODBC connection, you’ll get:
“[Execute SQL Task] Error: An error occurred while assigning a value to variable "Max_Seq_ID": "[IBM][CLI Driver] CLI0122E Program type out of range. SQLSTATE=HY003".”
Then I found out that we need to use “1” when using ODBC and use “0” with ADO.NET, as Resultset Name.
If you use Alias name from the Query in Resultset name with ADO.NET:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Max_Seq_ID": "Result binding by name "dasdas" is not supported for this connection type. ".
So many errors for one simple solution. I don’t want to Sound Critical but Informatica is Easy in regards to Transformation Usability but so is its Price.
Anyways Once the Execute SQL task is configured properly, I added a Data Task to the Solution and then Added a Script Component with Output to a Derived Column and then to a Flat file (You can use a database table as well). In the Script Component Editor, Please do the following changes:
1. Add the Variable we used in Execute SQL task to ReadOnlyVariables.
2. Add a Output Column to “Inputs and Outputs” Section with Correct Datatype and Encoding.
3. Design the script with proper Type casting between Variables and Columns.
4. Set the Pre-Compile flag to False or you might get error:
“The script component is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Component Editor by clicking Design Script button to cause binary code to be generated.”
I used an Derived Column Transformation to Add the Base column from the Flat file to the Generated Surrogate key from Script Component.
Last year I realized the importance of data backup when one of my colleague lost all of his critical data due to Hard disk failure and as usual he didn’t had any backup. So then I thought that it’s better to be Safe than Sorry. After this It occurred to me that my IT department has already provided me quota on backup servers, which I never utilized. IT provided me a batch file (as most of the machines in my organization are on windows), which goes like this:
echo Close Outlook @pause echo remove stale data echo Y | rmdir Old\c-data /S echo Y | rmdir Old\d-data /S @pause
echo Move Last backup to Old location move c-data Old\ move d-data Old\ mkdir c-data mkdir d-data
echo Start backup echo D | xcopy D:\Dev H:\d-data /c /e /h /y >backup.log echo D | xcopy "c:\Documents and Settings\manish.mishra\My Documents" "H:\c-data\My Documents" /c /E /h /y >>backup.log echo D | xcopy "c:\Documents and Settings\manish.mishra\Desktop" H:\c-data\Desktop /c /e /h /y >>backup.log @echo Backup is complete @pause
I used this for few weeks but still it was not an optimized method because it was copying entire data irrespective of the fact whether it was changed or not. It should be incremental.
So I Googled it and found a useful tool from Microsoft for this purpose.
It’s called “SyncToy”. I downloaded and installed it. It’s pretty simple to use and has a very intuitive GUI. I didn’t bother to read all the FAQ’s and Help manuals.
1. So first you need to “Create New Folder Pair”
2. Next Select the folder you want to backed up as “Left Folder”
3. Then select Backup Server location as “Right Folder”
4. Next comes the interesting part. “Synctoy” gives you an option so as to Select what kind of Synchronization you’ll like to perform.
a. Synchronize: This option will check both the left and right folders for “Add/Update/Delete/Rename” and synchronize both of them. This option can be useful in case you want to use Synctoy as a Development repository, when you’re working on the same project but from multiple machines.
b. Echo: This option only compares Left to Right not vice versa. (I chose this one as I don’t need to synchronize Backup server to Local).
c. Contribute: This one is similar to Echo with the only difference being Delete. File which are deleted on Left won’t be synced to Right.
You can change these options later as well.
5. Give this Synchronization pair a Meaningful name so that you can identify it later.
6. After this you’ll get this Pair listed in the Home Screen of the Tool and you can select which Pair you want to Run.
7. Selecting the pair will give you other options as well, which you don’t get at the time of Creating the Pair.
8. You can also click on Preview button which will show you a report displaying all the modified content.
Today I learnt a new caveat with Cognos Report Studio. I had a business case where I need to create two prompts, One cascading onto the other.
But the trick was that Cascading had to be conditional. For ex: for a particular value (let’s say ‘YYY’) of Prompt1, Prompt2 should display otherwise it should be blank. And to make it more complicated, Prompt1 was Multi-select.
I am no expert with Cognos so I started with my usual instincts and implemented the situation as: 1. Created Prompt1 with Multi-Select option. 2. Created Prompt2 without the cascade. 3. Add a filter to the Query of Prompt2: ?filter1? like ‘YYY’
Being a database guy, I thought that this should work and to a extent it did. Issue was that when I select ‘YYY’ as the first selection and rest of the values after that then it worked fine.
Now the problem arises when ‘YYY’ is not the first selection. Then second prompt remains blank.
I couldn’t understand that what was the reason. I changed filter to ?filter1? like ‘%YYY%’ but still no result.
Then I thought that Cognos might not treat Like as databases do. So I tried CONTAINS but still the same. Then I checked IN function and it works like a charm. ‘YYY’ IN ?filter1?
It’s nothing great but just a minor issue which might spoil your Friday evenings.
Hope this helps.
I’ll try to find exact difference between “LIKE” and “CONTAINS”.
Recently I had a annoying little issue in Excel. Every month I have to update a excel, which requires updating a source table with raw data in one of the sheets. Rest of the sheets require reference to this table for various calculations.
I’ll use an example to describe the issue more clearly.
I’ve to enter the raw data in the table defined at 5th row. Now there is a formula at Cell G3 which requires summation of 3rd column of the table. So the issue here is that every month raw data might change and hence the cell range will also change. So lets say for Month of June the formula range might be SUM(I6:I43) but for next month I have to update it depending upon the number of rows in the raw data.
So this is the problem and below is the solution for it.
First make the data table recognized as Table to Excel i.e. just making the Header as Bold won’t tell Excel that “Hey, I’m a Table and I have so and so Columns with these many Rows.”
So to do this, Select the Header and the row next to it then Select Insert Tab and Click on Table.
That’s it. Now Excel will recognize your table.
Now comes the second part “How to reference this Table into a Formula”. For this Just type your regular formula, like "=SUM(“ and then add Table name and add “[‘ after it, like “=SUM(Rally[”.
As soon as you do this excel will show you all the Columns in that table,
Just select the relevant column and close it with “])” and that’s it. So in the end your formula should look like:
=SUM(Rally[SP''s])
I hope it’ll save some precious time of my techno geek community who always wants to do stuff in an optimized and efficient way.
During my ETL development work, there have been instances when I had to do a DDL change in the Data Mart tables, sometimes for an enhancement or because of bug fixes.This involves fixing the Kettle Transformation as well. For this you need to follow this simple yet useful practice.
•Clear Database Cache whenever you do a DDL operation on Lookup or Target tables otherwise Spoon might mark Transformation as Invalid on Validation.
Whenever we introduce a table in Kettle, it keeps a cached version of the table metadata into the repository so as to optimize the transformation viewing/editing. But it doesn’t refresh this metadata from database every time we open the transformations as its memory expensive operation. For this reason we have to manually clear this cache whenever we make a change to the Database tables.
•For this you need to click on the Target (or Dimension Lookup) Step and then Click on SQL and then hit Clear Cache. It’ll ask you to Clean Cache only for this Table on Entire Database.
In tune with the last post, I’ll discuss more about ETL Best practices which I follow with Kettle.
• Enable cache for lookup on Dimension tables.
This improves the Performance of your Transformation for Small/Medium size Dimension tables. But you need to monitor the Logs to check whether It is beneficial or not. In some cases it might add un-necessary load on ETL server.
Its always a dilemma for ETL developer when it comes to Caching of Dimensional tables while doing the lookups. Usually right approach is collect Performance data, for the ETL where Caching is Implemented. This data usually includes Time taken,
To fetch Dimension table into memory
To filter this data
To update the Cache if underlying lookup table data gets changed.
Another good practice which I learned from Informatica, is to Place these Cached files on Faster Drives.
I would like to start this post with little info about Pentaho Data Integration(PDI):
Pentaho Data Integration (PDI), initially called Kettle, is one of the most successful Open Source ETL tool available in the BI space. Initially Pentaho released the only Community version of Kettle but when later it became popular they released the Commercial version called PDI. Kettle was first conceived about four years ago by Matt Casters, who needed a platform-Independent ETL tool for his work as a BI Consultant.
Pentaho includes most of the open source projects that comprise its BI suite: Reporting based on JFreeReports (converted more than four years ago and now known as Pentaho Reporting); ETL based on Kettle; OLAP based on Mondrian (with a GUI based on JPivot and a more interactive component that it recently licensed from Lucidera); and advanced analytics based on Weka.
Pentaho recently announced plans to provide integration with Hadoop, which can potentially address some of the scalability issues of open source BI tools.
Kettle Architecture
I re-searched a bit so as to get a visual representation for Kettle overall architecture and No surprise I found it on Matt Casters’s blog.
Kettle is built with the java programming language. It consists of four distinct applications:
•Spoon: Spoon is a GUI tool to model the flow of data from input step to output step. One such model is also called a transformation. •Pan: Pan is a command line tool that executes transformations modeled with Spoon. •Chef: Chef is a graphically oriented end-user tool used to model jobs. Jobs consist of job entries such as transformations, FTP downloads etc. that are placed in a flow of control. •Kitchen: Kitchen is a command line tool used to execute jobs created with Chef.
Few Key points about the Architecture:
•Model Based: Spoon and Chef are used to create Models which are XML based and can be interpreted by command line tools, Pan and Kitchen.
•Repository based: Kettle allows the model to be saved either in database repository or as XML Documents. You cannot mix the two methods (files and repository) in the same project.
Kettle – Best Practices
There are few things which I’ve learned with my experience with Kettle, which I feel can be termed as Best Practices.
•Use variables instead of Hardcoded strings while using Input SQL transformation. You can set these variables in an externalized file “KETTLE.PROPERTIES” defined in Kettle Home. Do remember to Select “Replace variables in script” option.
•This allows you to configure the business rules without the need to change the ETL code.
I’ll continue on the Best Practices in the next post.
In this post I’ll discuss about “"How to Create Mapping/Transformations with Kettle”. I needed to load set of flat files into database staging area for my staging ETL. I created the mapping for this using various Kettle transformations (I don’t know why Kettle doesn’t call a set of related transformations as Mapping, like Informatica ).
I used the Text file Input Transformation to read the tab separated data. Then using various filter transformations I extracted the relevant data and loaded into an database staging table.
Drag an Text file Trans from Input Trans category, from Explorer Tab, to mapping area. Configure the Text file Trans to get the source data. Configuration includes file directory location, Regular expression for file name, Data Separator and Field formatting etc.
Next step is to add filter Trans, to select relevant data from Flat file. Filter Rows Trans is listed under Utility Category in Explorer Tab.
Now we need to join two transformation, like Text file Trans to Filter Rows, you need an Hop. Just select the Trans , from which data flow will begin, press middle click with your mouse and drag the mouse to the Trans where finally data will pass.
Lastly after number of Filter Trans, we get the desired staging data, which we need to Insert into the database table. For this we drag an Table Output Trans from Output Category in Explorer Tab. You need to configure the Trans to connect it to respective table in Database, which requires database connection information and the table name. You need to check that whether your Kettle version has relevant database drivers installed under “$Kettle_Root_Dir\data-integration\libext\JDBC\”. If not then you won’t be able to connect to the database. For this you need to copy the respective drivers to the above location. For example I had to copy “ojdbc14.jar” for Oracle connection.
After this you need to verify the mapping which you’ve created. You can do this by pressing “F11” or use Tool bar button. Once you’re done with verifications then you can test the mapping execution by using Toolbar. Either you can Play this Trans or Preview.
In this console you can view all the Trans, used in the mapping and their statistics. Kettle gives you the facility to debug the mapping as well. For Debugging you need to use the Configure option. This functionality again compares Kettle with the Informatica. You can select the logging level as well. The thing which I liked most was the Performance Graph functionality, which can be really beneficiary while working with large volumes of data. You can log all the session details in a database repository as well. Only thing lacking here is the Metadata reporting which Kettle doesn’t provide.
You can view all the Execution related metrics like Rows Read, Updated, Deleted or Inserted for each Step in the “Step Metrics” tab under Execution results Tab, which you can select from Toolbar.
In the Next post I’ll discuss on how to Create and Execute Kettle Jobs.
In this post I will discuss my experience with Kettle Output Transformations. As like Input Transformations, Kettle has ample of Output Transformations as well. Most of them are similar to their Input counterparts. I’ve list down the ones which are different.
S No.
Transformation
Usage
1
Delete
Allows you to delete data from database table depending upon stream value
2
Excel Output
It allows you to write to a specific Sheet in Excel files. It also gives you an option to use Template as well.
3
Insert / Update
Another useful transformation. Ideally suited for Data warehouse applications. You can select columns for which you want to lookup the values and the corresponding columns which will be updated on match or Insert
4
SQL File Output
To generate SQL statements fir Inserts with option to Create Table or Truncate table alongside.
5
Synchronize after merge
As per the Kettle Wiki, this transformation should be used in conjunction with Merge Rows transformation. It commits the changes which are marked by Merge Rows transformation as changed, New and Delete etc. As per my understanding its similar to Update Strategy of Informatica.
6
Update
Similar to Insert / Update transformation, but for a very specific case where you just want to Update records and skip the Inserts.
In this post I’ll write about my experiments with ETL transformations in Kettle. As for the POC, I needed to load Flat files, XML Files and then Staging to Fact load as well. I’ll start with basics about Kettle UI and then discuss how I achieved the above mentioned ETL.
So when you open Spoon you get a nice Tip dialog box, which Can sometimes give you a good tip, but not always. Usually Its the regular stuff, which you yourself can figure out.
Next screen is the Welcome screen, which has links to all the documentation, Samples and Kettle related blogs. Main screen is divided into two parts, a Navigator and the Workspace. Navigator is provided with two separate tabs, Design and View. Design lists down all the transformations and View displays the transformation you are currently working with.
Different Transformations
Kettle provide loads of Transformations for your day-to-day ETL needs. All the Transformations have been divided into various categories depending upon theirs usage. Although I don’t find it useful as it an be real pain in the neck for some unusual transformations like for Dimension lookup is in Data Warehouse, instead of Lookup. Maybe this dislike is because of my Informatica mindset.
Another issue with Kettle is that there is no standard documentation for transformations, although I shouldn’t expect this from an Open Source Tool, maybe that’s how Pentaho earns their Dollars, by providing Support and Training for these transformations.
List of Kettle Transformations
I created a list of Kettle transformations while doing research for my project, which I think might help others, who are in a similar need.
Input Transformations
S No.
Transformation
Usage
1
Access Input
To get data from Access Database files.
2
CSV File Input
To read delimited data from CSV files.
3
Data Grid
To Insert Constant Data to flow e.g. You want to populate a Dimension table with Static master data
4
De-serialize from file
To read data from a Cube file, usually created by Kettle with the "Serialize to file" Output transformation.
5
ESRI Shapefile Reader
This is a custom plugin developed to read Shape files created via ESRI, which is used to distribute geographical information via shape files
6
Excel Input
Get data from Excel Files. It’s a useful and capable transformation to read complex excel a well. I found it better than Informatica Excel reader transformation.
7
Fixed file input
This is another variant of CSV file where data file has fixed size.
8
Get data from XML
Really useful transformation. This transformation lets you get data form an XML file, depending upon the level or Xpath Node you select. You can read multiple files with Regular Expression support.
9
Get System Info
Another Useful transformation which lets you get system info (Memory), Software versions, Date related manipulations First Day, last day of Week etc.
10
Mondrian Input
To get data from Mondrian Cube
11
OLAP Input
Another plugin to read OLAP data.
12
Property Input
To read key-value pair type of data.
13
RSS Input
Really unique transformation. This transformation lets you get data form RSS Feeds. You can specify the time frame as well, from which you want your Feeds to be refreshed.
14
Salesforce Input
Its similar to what other ETL tools have provided so as to work with Salesforce data. You just need to provide the SF link, credentials and the Module, with which you want to fetch the data.
15
SAP Input
Similar to Salesforce transformation, but it needs extra configuration. You may need to download few extra jars from pentaho SVN.
16
Table input
Simplest and Most useful Input Transformation.
17
Text file input
To get data from fixed data field files. You can add multiple files or just give the regular expression for File name.
There are few other Transformations as well, which are either very application specific, or for which I couldn’t find a practical use from my experience. These are mostly Plug-Ins developed by ETL developers for very specific application.
Few common and distinctive features about Input transformations, which I worth mentioning:
Lazy Conversion is a algorithm which Kettle uses in certain transformations (CSV and Fixed Input) where it doesn’t make sense to un-necessarily change the data types within ETL. Standard scenarios is where the source and the target type are of same type like Text files or same db schema. It also facilitates binary mode for data movement as well.
Regular Expression: I had a similar name for all of my flat files which I used as Input. So instead of adding them one-by-one, I gave regular expression as “req_uid*.*” and the directory where all my files were located.
Add Filename to Result: This can be useful in cases where we need to filer data based upon filename. For example we get similar files from different regions and we need to process them differently. So all we need to do is name the differently and we can add our logic based upon data fields.
In the next post I’ll discuss about Output Transformations.
Couple of months back I needed to do a POC(Proof of Concept) for our BI group, with Open source technology. After some quick analysis we decided to use Pentaho BI solutions. Kettle, Pentaho’s ETL solution was the leading Open source ETL solution, plus we had an experienced resource for Kettle as well. Regarding BI, we had few Java resources who had BI exposure (with BIRT) so we decided to try something new and finalized Pentaho for BI as well.
ETL with Kettle
I have previously worked on Informatica for ETL so I had this mindset so as to compare every other ETL tool with Informatica. But I knew that Kettle is an Open source so I didn’t raise my expectation too high. I downloaded the software from Sourceforge although you can try the Pentaho paid version called Enterprise Edition as well, which comes with an 30 day trial. I’ll discuss more about the features of Enterprise edition later with BI discussion. I couldn’t find any difference in ETL part between Open source and Enterprise edition, except that they call the paid version “Pentaho Data Integration”.
So after all the setup I started exploring Kettle. Unzipped the software and searched for setup.exe or MSI file to start the Installation. But I was surprised to find just few scripts (bat files) at the root. Secondly you don’t have to download different versions for Linux as well. Similar set of scripts(in the sh format) are provided for Linux as well. I really liked that feature. So Plus 1 to the score for Kettle.
After that I searched that which script should I use to start the ETL tool. There were 3 batch files, Spoon, Kitchen and Pan, strange name haan….even Funny as well. Then I searched the Net and found this wiki. Its a really useful link for getting yourself familiarized with Kettle with some basic transformations examples as well. So now I knew that Which tool to use for What:
Kitchen: is the tool used to execute Jobs from a terminal window. Limited functionality as that of Informatica Workflow Manager. Pan: allows you to execute Transformations from a terminal window. Spoon: The graphical tool with which you design various Transformations and test them. Similar to Informatica Mapping Designer
Its like this, Create a Transformation with Spoon, test it save it in the repository. Use Pan to run a single Transformation from command prompt. Kitchen is used to execute Job, which is created to execute a set of Transformations, which are Inter-dependent.
There are few more scripts present alongside the above mentioned like Encr, Carte.
Carte: Carte is a simple web server that allows you to execute transformations and jobs remotely. I really liked this utility. This is similar to what Informatica has Workflow Monitor or Web Admin Console, although not as robust or versatile as Workflow Monitor. It gives you a basic level of User Security as well.
Encr: This is another useful tool for enhanced security. You can encrypt your password as well. Carte allows you to store the passwords in an external file, which I found very strange (from the security perspective), but then I used Encr, which allows you to encrypt the password and then store in the file.
So after this brief about Kettle utilities, I started Spoon, but nothing happened, a command prompt appeared and vanished. Then I opened a command prompt and started Spoon.bat. Then it gave me a proper Error message that I need to set Java Home on my machine. Kettle requires the Sun Java Runtime Environment (JRE) version 1.5 or newer which you can get at Java or from your Local IT department. You need to set the environment variable JAVA_HOME correctly.
So after this I was able to start Spoon. Now you have to decided what kind of method you want to use to save Kettle Metadata i.e. Repository. Kettle allows you two ways for this, one is File based and the other as usual Database. Benefits are obvious like if you’re working in an distributed environment, use database solution otherwise File based. Secondly database solution allows user security as well.
Database Repository:
File based Repository:
In the next post I’ll discuss my experience with various Transformations which I used with Kettle.
There are various pragma directives used in Oracle PL/SQL interface. I read them recently and thought of listing the useful ones at my blog. Out these fours I've used just two in real life cases, EXECEPTION_INIT and AUTONOMOUS_TRANSACTION.
RESTRICT_REFERENCES Pragma
To be callable from SQL statements, a stored function must obey certain purity rules, which control side-effects. The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or DETERMINISTIC hints are used. The same rules that apply to the function itself also apply to any functions or procedures that it calls.
If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES. This pragma asserts that a function does not read and/or write database tables and/or package variables. Functions that do any of these read or write operations are difficult to optimize, because any call might produce different results or encounter errors.
DEFAULT: Specifies that the pragma applies to all subprograms in the package spec or object type spec. You can still declare the pragma for individual subprograms. Such pragmas override the default pragma.
function_name: A user-defined function or procedure.
PRAGMA: Signifies that the statement is a compiler directive. Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they convey information to the compiler.
RNDS: Asserts that the subprogram reads no database state (does not query database tables).
RNPS: Asserts that the subprogram reads no package state (does not reference the values of packaged variables)
TRUST: Asserts that the subprogram can be trusted not to violate one or more rules. This value is needed for functions written in C or Java that are called from PL/SQL, since PL/SQL cannot verify them at run time.
WNDS: Asserts that the subprogram writes no database state (does not modify tables).
WNPS: Asserts that the subprogram writes no package state (does not change the values of packaged variables).
You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, and WNPS) in any order. To call a function from parallel queries, you must specify all four constraints. No constraint implies another. Typically, this pragma is specified for functions. If a function calls procedures, then specify the pragma for those procedures as well.
When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object type spec (including the system-defined constructor for object types). You can still declare the pragma for individual subprograms, overriding the default pragma.
A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded subprograms always applies to the most recent subprogram declaration.
AUTONOMOUS_TRANSACTION Pragma
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.
Syntax
PRAGMA AUTONOMOUS_TRANSACTION;
Usage Notes
You can apply this pragma to:
■ Top-level (not nested) anonymous PL/SQL blocks
■ Local, standalone, and packaged functions and procedures
■ Methods of a SQL object type
■ Database triggers
You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method. You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.
Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.
In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction. If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
EXCEPTION_INIT Pragma
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.
Example
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN deadlock_detected THEN
NULL; -- handle the error
END;
/
Keyword and Parameter Description
error_number: Any valid Oracle error number. These are the same error numbers (always negative) returned by the function SQLCODE.
exception_name: A user-defined exception declared within the current scope.
Usage Notes
You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration. Be sure to assign only one exception name to an error number.
SERIALLY_REUSABLE Pragma
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
This pragma means that every individual call to the code is treated as a new call. That means that no package states or variables are retained.
Syntax
PRAGMA SERIALLY_REUSABLE;
Here's an extreme example of how you might want to use it. Let's say you have a process that takes 2 hours to run. The first thing the process does is call a procedure that requires 500M of memory, but only runs for 30 seconds. Using normal PL/SQL, that memory might stay allocated for 2 hours until the whole process completes. Using this pragma, the memory can be released as soon as the procedure completes, allowing it to be reused while the next 2 hours of processing is completed.
Keyword and Parameter Description
Usage Notes
This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session. You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both.
You cannot mark only the body. The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.
Pragma SERIALLY REUSABLE is responsible for removing a packages temporary area (for example global variables) after each call and allows for (re)compiling (a new version) of that package without causing an exception such as "ORA-04061: existing state of package has been invalidated". A similar behavior can be achieved by omitting global public (and private) variables in the specification and body of a package. The downside of this pragma is that no package function can be used in a SQL statement.
![clip_image001[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbXs_MEXsogd_qNlHtJ9ryN9JxdbhbcxPqBzuu1olcyLx6Gc8SMydEVs32yb4Zm7rLAXlpunfeO-WtKKrDWNR0_KllWpO-S0A392xaVI-gNDH-1HOPEudYdKnaDT_qDuyDDpx5kAP4gqs/s1600-h/clip_image001%25255B4%25255D%25255B2%25255D.jpg "clip_image001[4]")
