Pentaho 5 Restful Web Services

How are you friends today I reblog for us this amazing post including new Pentaho 5 features from :

With the latest release of Pentaho V5 comes some very cool new features. One that we were really interested in had to be the new Restful Webservices.

Lets take a look at some of the core Repository features.

  • Navigate Repository Contents
  • Manipulate Repository Contents
  • Edit User and Group Permissions

Manipulating the Pentaho Repository

A quick word about the syntax of solution directories. With the new Restful API, solution directories are broken up using the : character. So for example, if we wanted to render a report called sales.prpt in the steel-wheels/reports/sales-reports directory the url would look a little like this:



There are two types of repository listing requests. One gets the list of files and folders (including child folders to a certain depth if specified) and the other returns properties of a certain file or folder.
We can start by listing all the files and folders in the public solution folder:
If we wanted to return the content of the public/cde directory we would execute:
By default the children request lists all files and folders including nested files and folders (not including hidden files and folders). Its possible to pass in some argument to filter our results


the depth argument defines the depth of folders we wish to query. by default the depth is -1. This returns ALL folders. If we set it to 0 we get only the contents of the currect folder. if we set it to 1 or more we get the contents of the child folders to that depth


By default hidden files are not returned. to show hidden files in the results we simple pass in the argument showHidden=true


It is possible to filter if we want to return Files or Folders. We do this with the filter argument. For example, to filter only solution files we would add the following argument: filter=*|FILES. To list onlt folder we would do filter=*|FOLDERS

Getting Solution folder / file properties

If we want to get the details for a simple solution file or folder we can use the properties webservice. For example, to get the properties of a solution file called sales.prpt in the public/mysolution/reports folder we would do something like this

Creating new Folders

Lets assume we want to create a new folder in the public solution directory called myreports. To create the new folder we simple execute the following request


Deleting Solution Files and Folders

Lets assume we now want to delete the myreports folder in the repository. before we can delete a file or folder we need to get its ID. We can use the properties request method to get a file or folder id.Once we have the id of the file(s) or folder(s) we want to delete we can execute the delete and pass in the list of ID into the response header as a comma separated list


Listing File or Folder ACLs

We can get the permissions for a particular solution file or folder using the acl service. For example, if we wanted to get the permissions for the public folder we could execute the following request:
This will return the ACL descriptor for the public directory

Setting ACLs for Files or Folders Summary

If we want to set new permissions to a certain solution file or folder we can use the same service but pass in a response header with the new ACL definition using PUT.

Web Services to interact with Mondrian

Here are the webservice calls for getting a Mondrian schema file into the repository and out of the repository. Note at this time you need to have admin permissions to perform these actions, but in future releases we’ll be able to control which users can import and export schemas.

Multipart POST request with post params:
uploadAnalysis – Mondrian schema
catalogName – name of the catalog (optional?)
parameters – parameter list

GET request to get the parameters of a Mondrian schema definition
SteelWheels should be the name attribute that appears in the Mondrian.xml schema

GET request to download the Mondrian xml schema

Most of the datasource UIs call REST services for these behaviours, you can look at the http traffic in Chrome or Firefox to determine the API calls for other datasource types like Pentaho Metadata.


So this is just a quick overview of some of the Pentaho BI Server Restful Web Services but it should be enough to get you started with integrating Pentaho into your own applications
Help me keep the guides up to date and the posts flowing by donating, every small amount of money helps!

Oracle tips (Display sessions by user,application and Tablespace info)

Sometimes you are developing with kettle and you need to open several connections to the same database, in this case I will show how to check the amount of connections opened in Oracle. Below there are some useful queries:

-- Show sessions by user
select osuser, username, machine, program
from v$session
order by osuser
-- Show sessions by application
select program Application, count(program) Number_of_Sessions
from v$session
group by program
order by Number_of_Sessions desc

If you detect you have too many connections it is time to convert your transformations transactional, using this feature you will prevent collapse the pool of connections. (Check the option Make the transformation database transactional? )

Transactional database

Below I attach a query to display the size and free space on the different tablespaces.

-- Show Tablespaces
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

Convert SSAS Snowflake Schemas to Pentaho Mondrian


Last weekend at a local tech conference, I was speaking to a few of my friends in the SQL Server & Microsoft BI consulting business about migrating cubes and BI solutions from SSAS to Pentaho using the Mondrian ROLAP engine with tools like ssas2mondrian. I explain different methods of starting over and generating new OLAP models using Pentaho’s auto modeler and Analyzer here and how to start with migrating simple SSAS cubes to Mondrian ROLAP schemas here.

So I felt like it would be a good challenge to see if I could convert a more complex SSAS cube to a Mondrian model that included Reference Dimensions in SSAS and convert those to a snowflake model in Mondrian using JOINs in dimension definitions.

Additionally, if your Microsoft BI solution is using Excel in SharePoint for visualizations (Excel is widely used for business intelligence), you will want to use the…

View original post 617 more words

AdventureWorks DW on Pentaho, part 3: Geo Mapping AdventureWorks

Great Geo OLAP post, learn about geographic dimensions syntax here


In the previous parts of this series, I’ve showed you how to make quick, auto-modeled OLAP cubes and reports on a SQL Server AdventureWorks data warehouse as well as how to take your existing SSAS cubes on top of those SQL Server DWs and turn them into Mondrian cubes, which runs natively inside of the Pentaho Business Analytics suite.

Here in part 3, I am going to take the Analytics a step futher by using Geo Maps on top of the SQL Server Adventure Works data warehouse by modeling the ROLAP schema manually instead of with the Pentaho auto-modeler. And since I’m finally upgraded to the latest 5.0 version of the Pentaho Suite. you may notice a few UI differences and workflow changes in the process. To follow along, make sure you download the latest & greatest Pentaho 5.0 product here.

Instead of starting the analysis process from…

View original post 555 more words

Calculate the difference between two dates in hours/minutes on PDI

Currently I am parsing logs in order to optimize a PDI job, and as I need to calculate the difference between time spent in several steps Calculator step is not enough for me so I decided to use Modified Javascript:

Hope it helps

//var1: Date - The Variable with the Startdate.
//var2: Date - The Variable with the Endate.
//var3: String - The Type which represents the the return value:
// y - Years , m - Months, d - Days , w - Weeks,
// wd - Working Days ,hh - Hours ,mi - Minutes, ss - Seconds
Alert(dateDiff(date1,date2,"ss")); //Get the date diff in seconds