Playing with Jedox (BI + CPM)


 

Today I would like to introduce Jedox , this MOLAP tool is the perfect mate for Pentaho BI.

BI & CPM

Analyze. Plan. Act.

Jedox empowers you to analyze historical data for meaningful insight, and to plan and forecast intelligently. Jedox unleashes your capabilities in one solution, unifying Business Intelligence and Corporate Performance Management (BI & CPM) into your competitive edge.

Below I explain some examples of Jedox capabilities:

1-) If we start pasting a following view with 2014 Actual and Budget data by Region

jedox ini pasteview

2-) We could copy actual 2014 on empty 2015 Budget

copy actual;2014

2

 

2b

3-) Then we will increase value a 20%

##20%

3a3b

 

 

4-) Now we will predict 2016 based on 2014 and 2015 data

predict 2014:2015

4a4b

 

5-) Distribute a fixed amount based on a previous year

17M like 2014; Actual

5a5b

 

Advertisements

Pivotal Greenplum useful DBA SQL queries


1. Viewing table data distribution across segment Servers To view the data distribution of a table’s rows (the number of rows on each segment), you can run a query such as:

SELECT gp_segment_id, count(*)
FROM abc
GROUP BY gp_segment_id;

To see the data distribution of a table’s rows on segment servers and the directory location of where the data is located, you can run a query such as:

SELECT
table_name.gp_segment_id,
hostname,
fselocation as datadir,
count(*)
FROM table_name,
pg_filespace_entry pgfse,
gp_segment_configuration gsc
WHERE
gsc.dbid=pgfse.fsedbid
and table_name.gp_segment_id= pgfse.fsedbid
GROUP BY
table_name.gp_segment_id,hostname,datadir
ORDER BY
gp_segment_id;

Balanced Distribution: A table is considered to have a balanced distribution if all of the segments have roughly the same number of rows. 2. Check the size of a table Answer: Table Level:

select
 pg_size_pretty(pg_relation_size('schema.tablename'));

Replace schema.tablename with your search table. Table and Index:

select
pg_size_pretty(pg_total_relation_size('schema.tablename'));

Replace schema.tablename with your search table. 3. Check the Schema size Answer: Schema Level:

psql> select schemaname ,
round(
sum(pg_total_relation_size(schemaname||'.'||tablename))
/1024/1024) "Size_MB"
from pg_tables where schemaname='SCHEMANAME' group by 1;

Replace SCHEMANAME with your schema name. –4. Check the database size Answer: To see size of specific database:

psql> select
pg_size_pretty(pg_database_size('DATABASE_NAME'));

Example:
gpdb=# select pg_size_pretty(pg_database_size('gpdb'));

pg_size_pretty
----------------
24 MB
(1 row)

To see all database sizes:

 psql> select
datname,pg_size_pretty(pg_database_size(datname))
from pg_database;
psql>
select
sodddatname,
(sodddatsize/1073741824) AS sizeinGB
from gp_toolkit.gp_size_of_database;
 

6. Check partitioned table size including indexes and partitions Answer: Table size with partitions: The following SQL gives you employee_dailly table size, which includes partitions.

select
schemaname,
tablename,
round(
sum(pg_total_relation_size(schemaname || '.' || partitiontablename))
/1024/1024) "MB"
from pg_partitions
where tablename='employee_daily'
group by 1,2;

schemaname | tablename | MB
-----------+----------------+-----
public | employee_daily | 254

7. Generate Scripts to grant DML priv to other user or role

david=>
select
'grant select,insert,update, delete on '||schemaname||'.'||tablename||' to gpadmin'
from pg_tables
where tableowner='david';

?column?
------------------------------------------------------------------------------
grant select,insert,update, delete on david.countries to gpadmin
grant select,insert,update, delete on david.departments to gpadmin
grant select,insert,update, delete on david.employees to gpadmin
grant select,insert,update, delete on david.job_history to gpadmin
grant select,insert,update, delete on david.jobs to gpadmin
grant select,insert,update, delete on david.locations to gpadmin
grant select,insert,update, delete on david.regions to gpadmin
grant select,insert,update, delete on david.ext_countries_error to gpadmin
grant select,insert,update, delete on david.ext_countries to gpadmin
grant select,insert,update, delete on david.ext_departments_error to gpadmin
grant select,insert,update, delete on david.ext_departments to gpadmin
grant select,insert,update, delete on david.ext_employees_error to gpadmin
grant select,insert,update, delete on david.ext_employees to gpadmin
grant select,insert,update, delete on david.ext_job_history_error to gpadmin
grant select,insert,update, delete on david.ext_job_history to gpadmin
grant select,insert,update, delete on david.ext_jobs_error to gpadmin
grant select,insert,update, delete on david.ext_jobs to gpadmin
grant select,insert,update, delete on david.ext_locations_error to gpadmin
grant select,insert,update, delete on david.ext_locations to gpadmin
grant select,insert,update, delete on david.ext_regions_error to gpadmin
grant select,insert,update, delete on david.ext_regions to gpadmin
(21 rows)
-- Count
8. select count(*) from pg_tables where schemaname='david'; 

 -- List users
9. select rolname from pg_roles where rolcanlogin is TRUE;

--- List Roles
10. select rolname from pg_roles where rolcanlogin is FALSE;  

11. select * from pg_views ;
12. select * from information_schema.table_privileges;
13. select * from pg_user;
14. select * from pg_views where viewname like '%priv%';
15. select * from pg_views where viewname like '%role%';
16. select * from pg_views where viewname like '%gran%';
17. select * from pg_views where viewname like '%part%';
18. select * from pg_views where viewname like '%schema%';
19. select * from pg_views where viewname like '%stat%';
20. select * from information_schema.role_table_grants;
21. select * from information_schema.role_routine_grants;
22. select * from information_schema.applicable_roles;
23. select * from information_schema.enabled_roles;
24. select * from gp_toolkit.gp_roles_assigned;

25. Find dependent views created on any table The following query will give you the dependent views for that table:

# select
a.*,b.definition
from information_schema.view_table_usage
a, pg_views b where a.table_name='test' and
a.view_name=b.viewname;

When you alter an existing table (for example dropping a column), you may want to determine if there are any views that depend on the columns you are changing.

Example:
\dt test
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+------------+---------
public | test | table | gpadmin | heap

create view test_view as (select * from test);

--Run the following query
--with "where a.table_name='<table>'

select
a.*,b.definition
from
information_schema.view_table_usage a,
pg_views b
where a.table_name='test'
and
a.view_name=b.viewname;

view_catalog | view_schema | view_name | table_catalog |
table_schema | table_name | definition
-------------+------------+-----------+-----------+--------------
+------------+--------------------
ddata | public | test_view | ddata |
public| test | SELECT test.a FROM test;

–26. Connections per database and activity:

SELECT usename,
 application_name, -- only valid for 9.x
 client_addr,
 backend_start,
 query_start,
 current_query
FROM pg_stat_activity
order by usename,client_addr

–26 b. Running queries and age:

SELECT age(query_start, backend_start) AS queryage,*
 FROM pg_stat_activity 
WHERE current_query NOT LIKE '%IDLE%'
 ORDER BY queryage DESC

–27 List of users:

select * from pg_user

–28 Connections per database:

SELECT datname, numbackends 
FROM pg_stat_database
WHERE numbackends > 0
ORDER BY numbackends DESC, datname

–29 Connections per database v2:

SELECT datname, COUNT(*) AS numbackends
FROM pg_stat_activity 
GROUP BY datname HAVING COUNT(*) > 0

–30 List procedures by user:

select * from pg_proc, pg_user
 where pg_user.usesysid = pg_proc.proowner
 order by proname

–31 Top 20 tables by size:

SELECT nspname || '.' || relname AS table,
 pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND
 c.relkind <> 'i' AND
 n.nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

BARC BI Survey 13


bis13_invite_300x250

Today I will ask you to fill BI Survey 13 since it is a good way of getting the opinion as  Pentaho BI user and should only take you a few minutes.

Here I comment a little background on the survey:

The BI Survey, published by BARC, is the world’s largest and most comprehensive annual survey of the real world experiences of business intelligence software users. Now in its twelfth year, The BI Survey regularly attracts around 3000 responses from a global audience. It provides an invaluable resource to companies deciding which software to select and to vendors who want to understand the needs of the market.

The Survey is funded by its readers, not by the participant vendors. As with the previous eleven editions, no vendors have been involved in any way with the formulation of The BI Survey. Unlike most other surveys, it is not commissioned, sponsored or influenced by vendors.

Here is the link, let’s make Pentaho go beyond with our opinions and get excellent results on this Survey. Go #PentahoGeeks Go.

https://digiumenterprise.com/answer?link=1426-DRNG327T

Remember that the Survey is scheduled to run for the next two weeks, until the end of July.

Pentaho BI Plugins (Fusion Charts + Ruby Steps + BI iPhone + BIRT + Data Cleaner + GeoKettle + Weka Data Mining


Additional Software

There are lots of plugins that enrich the pentaho BI suite. There is a plugin page for the BI Server PUC and for the PDI). I still haven’t tried them all but here are some interesting ones anyway:

BI Open Flash Charts

You can now add the Open Flash graphs in your dashborads in addition to the Open Flash Chart and JFreeChart. This plugin was started from the Pentaho framework so building with it should be familiar. But the stunning graphs come with a v3 which is not OS.

Here are links to the FusionCharts Blog, the Open source version, and the Pentaho plugin.

You can download the 0.02 version and its samples.

To install

  1. Extract the zip file into the /Pentaho/biserver-ce/pentaho-solutions/system.
  2. Extract the samples zip file into the /Pentaho/biserver-ce/pentaho-solutions/bi-developers.
  3. Change the file system/pentaho.xml to include xfusion on the acl-files list:
    <acl-files>...,xfusion</acl-files>
  4. Open the Pentaho User Console (PUC) and refresh the solution repository.

Here you can see installation, demo and usage in a Youtube video.

Ruby Step for PDI

Slawomir Chodnicki released on march 2011 the Ruby plugin step for Kettle 4 here.

There are examples included in the file you should download in the github page. Click on downloads and the select RubyPlugin_1.1_Kettle_4.2.zip. You already know thay it should be unziped on the plugins folder.

In a forum post some caracteristics are mentioned.

Excel Writer Output PDI Plugin

It is no longer a plugin as it is included in the 4.2 Kettle release, but post about its usage are still labeled as that :)

It let’s you set more options on formating and range.

Release notes and usability post.

BI iPhone plugin

Edit: The infomation below is no longer acurate:

  • Since 3.8 the BI server includes the iPad code, as stated by richad3 on the BI forum. But for 4.0 improvements were made on a week of fun.
    It seems the Enterprise plugin works great with the iPad, check the video. On the CE Edition the PAT/jPivot should work too, I’ll let you know what I find.
  • An alternate option for mobile devices is made on this OS project: PentaGoMo
  • The new site redesign has made the original code (the one that needed fixing) unavailable redirecting everything here.
    That’s bad if you still want to play with your Blackberry, Android or iPhone devices. I’ll let you know what I find.

The original information will remain here until new links and information is found:


The original article about a plugin for the iPhone is as old as this device, it was made available by the now vicepresident of engineering in Pentaho.

The BI PUC detects the browser and present a special menu. When you select an action (for report, dashboard, etc) a special program makes the parameter selection easier and presents one by one. Then the report is shown, you can see this video.

Unfortunatelly some corrections have to be made to the code to work with the new Pentaho BI version. Here are the download and correction instructions from Will Gorman on 2008 and additional ones on 2011 by Herwin Rayen. And a tech-tip so you can modify it further in the BI version 3.0.

There is also an Android app in the marketplace, here is the forum post that mentions it and the link to the 3.5 version.

In the Pentaho Blog an anouncement was made on summer 2011 about a iPad prototype. If you can’t wait “expand the plug in to cover iPad, just modify the Java source code, recompiled its class, then updated the JAR accordingly. This method can be used to expand the plug in to cover Windows Mobile, BlackBerry, and Android” – Paul Pambudi.

If you dont have all these devices you can check the emulators in Firefox’: User Agent Switcher add-on or testiphone.com web-based iPhone browser emulator.

BI BIRT Report ‘Plugin’

You can use the BIRT report view engine in the BI Pentaho User Console (PUC) which is newer and different than the plugin that is used in pentaho.

You have to download an eclipse runtime and extract a directory to your pentaho tomcat/webapss/pentaho folder and also download the samples to your pentaho-sulutions folder. Check the description, instructions and samples here.

Or you can use the plugin for PDI/Kettle to just run and burst your BIRT specifications. Check this link.

Data Cleaning [future] Plugin for PDI

Data validation needs coding in the actual PDI, but a nice open source utility for doing validation and correction exist and it seems it will become part of the PDI soon.

Data Cleaner can analyze, profile, transform and clean data on its own. But Matt Casters is working on a plugin so Kettle can use it. Here is the link that briefly shows its functionality and mentions the plugin here.

GeoSpatial Analisys on Kettle

On July 2011 version 2.0 of GeoKettle was announced by Spatialytics.com here. It is a step add in for Kettle 4.0 that allows “spatial analysis functions such as buffer calculations, overlays, metric operators, etc” from and to different file formats. It even reads sensors. Sounds like fun.

Weka Plugin (Data Mining )

There are two plugin steps available for PDI ARF Output and Weka Scoring very useful combined with Weka

You can use the Knowledge Flow Plugin, that lets you use a weka predictive model as a step in a PDI transformation. Install an usage here.

 

 

How to use a Textbox Parameter for Multi-Value Selections in Pentaho Report Designer


Pentaho Report Designer (a desktop application that provides a visual design environment to create report definitions) allows you to create parameters that users can use to filter the data.  Usually this is done by providing buttons or lists or other standard UI types to select the values.  However, there are times when the list of possible values is quite long, making a selection list to long to be feasible.  In these cases it would be ideal to provide a simple text box and then enter the list of matching values to use.  Unfortunately a text box is associated with a single value and not a list.  But not to fear, there is a straightforward solution using a hidden parameter and a post-processing formula.

Lets say you want to look at information from an HR database that contains 1000 employees with dozens of records each an you only want the information for a few employees.  So you create a report with the query:
 
select * from employees where employee_id in (${empid});
You then create a parameter called empid with a text box and type of string and run the report.  Typing in 1002, 1005, 1007 you only get data back on the employee with number 1002.
To fix this problem, create a second parameter that we’ll call empid_array.  This parameter has a type of Object and a Post-Processing Formula of =CSVARRAY([empid];0;”,”;).  Check the box that makes the parameter hidden so that it won’t show up on forms.
This formula says to convert the parameter named empid to an array using a comma as a separator and don’t quote.
Now modify your query to use the empid_array parameter:
 
select * from employees where employee_id in (${empid_array});
Now run your report and enter 1002, 1005, 1007 and you should see all records for all of the employees.

Publishing Pentaho Reporting Templates Tips!


Yesterday I have issues updating Repository cache after uploading new .PRPT ‘s to my solution repository on a BI Server version 3.6 .

After a while I noticed that reports which failed had éá and latin characters so I turned it into ASCII ea and all worked correctly.

Second problem was to open on my Pentaho User Console, reports with + char on its title. The solution was to avoid the using of this type of strange characters.

Hope you help

Pentaho and Google Web Kit GeoMap issues resolved


During Last week while I was  developing a Dashboard on GWT to deploy in Pentaho (Remember to use GWT sdk of a version 2.0.4 newer versions are not valid for Pentaho)

I found several issues but finding docs about Charts was specially difficult

-) Youl’ll need to install GWT plugin for eclipse

-)New > Web Application Project (Blue logo google)

-) Add  GWT visualization jar  (1.1.2 version for example) as external library to your project

-)inherit in you module.xml from gwt.visualization

<!– Other module inherits                                      –>
<inherits name=”com.google.gwt.visualization.Visualization”/>

Here I  attach source code for creating a GeoMap chart, remember to add it to an RootPanel on the onModuleload() method of your project

//IMPORTS

import com.google.gwt.visualization.client.DataTable;

import com.google.gwt.visualization.client.visualizations.GeoMap;

//FUNCTION

// creation worldMap
private void createWorldMap(){

world_chart_options=GeoMap.Options.create();
world_chart_options.setDataMode(GeoMap.DataMode.REGIONS);
world_chart_options.setHeight(“400px”);
world_chart_options.setWidth(“1000px”);
world_chart_options.setShowLegend(true);

//grafico_mundo_options.setColors(0xFF8747, 0xFFB581, 0xc06000);
//grafico_mundo_options.setRegion(“world”);

data_world_chart= DataTable.create();

data_world_chart.addColumn(ColumnType.STRING, “Country”);
data_world_chart.addColumn(ColumnType.NUMBER, “Population”);

data_world_chart.addRows(5);
data_world_chart.setValue(0, 0, “Germany”);
data_world_chart.setValue(0, 1, 1000);
data_world_chart.setValue(1, 0, “Spain”);
data_world_chart.setValue(1, 1, 1500);
data_world_chart.setValue(2, 0, “Italy”);
data_world_chart.setValue(2, 1, 1000);
data_world_chart.setValue(3, 0, “France”);
data_world_chart.setValue(3, 1, 600);
data_world_chart.setValue(4, 0, “Portugal”);
data_world_chart.setValue(4, 1, 200);

grafico_mundo = new GeoMap(data_world_chart,   world_chart_options);

//Panel is a VerticalPanel
Panel.add(grafico_mundo);

}

GeoMap GWT