Nilesh Patil's blog

Create MongoDB service in Windows OS

2016-01-28T03:35:00.000-08:00

1) Open Command Prompt

2) Create directory for Database and Logs

mkdir c:\data\db

mkdir c:\data\log

3) Create a file at C:\mongodb\mongod.cfg and specifies both systemLog Path and Storage database

path

Add following in mongod.cfg file :

systemLog: destination: file path: c:\data\log\mongod.log storage: dbPath: c:\data\db

4) Create service named as "MongoDB" using following command

sc.exe create MongoDB binPath= "C:\mongodb\bin\mongod.exe --service --config=\"C:\mongodb\mongod.cfg\"" DisplayName= "MongoDB" start= "auto"

5) To remove MongoDB service use following command

sc.exe delete MongoDB

utf8_general_ci VS utf8_unicode_ci what should we use?

2015-09-14T23:36:00.001-07:00

These two collations are both for the UTF-8 character encoding. The differences are in how text is sorted and compared.

Note: in new versions of MySQL use utf8mb4, rather than utf8, which is the same UTF-8 data format with same performance but previously only accepted the first 65,536 Unicode characters.

Accuracy
utf8mb4_unicode_ci is based on the Unicode standard for sorting and comparison, which sorts accurately in a very wide range of languages.
utf8mb4_general_ci fails to implement all of the Unicode sorting rules, which will result in undesirable sorting in some situations, such as when using particular languages or characters.
Performance
utf8mb4_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts.
On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.
utf8mb4_unicode_ci, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.

As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:

For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).
Some Unicode characters are defined as ignorable, which means they shouldn't count toward the sort order and the comparison should move on to the next character instead. utf8mb4_unicode_ci handles these properly.

In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.

What should you use?
There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
The difference in performance is only going to be measurable in extremely specialised situations, and if that's you, you probably already know about it. If you're experiencing slow sorting, in almost all cases it'll be an issue with your indexes/query plan. Changing your collation function should not be high on the list of things to troubleshoot.
In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.
One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.

Query to show all tables and their collation of a Schema

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,

COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE `TABLE_SCHEMA` = 'Schema_Name'

How to alter collation of columns of a table :-

alter table `dbname`.`tablename` convert to character

 set utf8 collate utf8_unicode_ci;

Ref :
http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

How to show records in same order where IDs are put into the IN() clause?

2015-02-06T01:28:00.001-08:00

How to show records in same order where IDs are put into the IN() clause?

1) SELECT id FROM table1 WHERE ... ORDER BY display_order, name

2) SELECT name, description, ... WHERE id IN ([id's from first])

The issue is that the second query does not return the results in the same order

that the IDs are put into the IN() clause.

Solution :-

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

Is a BLOB converted using the current/default charset in MySQL?

2015-02-02T22:41:00.000-08:00

Blob data used MySQL charset which is in my.ini

default-character-set=latin1

Suppose

I have a table with a BLOB field.
The charset of the table is UTF-8.
I connect to the DB and "SET CHARACTER SET utf8".
Then I save binary data into the field.
Then I retrieve the data, and it's not what I saved (corrupt).

Because while retrieving data blob data used mysql default charset set.

Ref :
http://stackoverflow.com/questions/14734812/is-a-blob-converted-using-the-current-default-charset-in-mysql

Pentaho/R Integration Plugin

2015-01-06T01:14:00.002-08:00

http://dekarlab.de/wp/?p=5

Pentaho R Integration Plugin is used to execute R code direct from Pentaho Data Integration (PDI).
R Script Plugin can be downloaded here: Downloads

Installation
R script plugin can be installed by performing the following steps:
1) Copy the plugin folder (RScriptPlugin) into the folder of your PDI installation:
data-integration\plugins\steps
2) Install R project from the site http://www.r-project.org/
3) Install rJava package in R by executing: install.packages(“rJava”)
4) Copy JRI library rJava/jri/jri.dll (windows) or rJava/jri/libjri.so (linux) to: data-integration/libswt/{your operation system}
5) Specify location of R using R_HOME environment variable. (like: C:\R\R-3.0.2).
6) For windows, it is also needed to put in PATH variable the path to R.dll, for example: C:\R\R-3.0.2\bin\{your operation system}
7) The plugin can be found under Sripting Folder in Design Tab:

Note: to check the installation press Help button in R plugin in Spoon.

Usage
We demonstrate the usage of “Execute R Script” plugin by solving the following task. In CSV file we have the values for two variables a and b. We should calculate the sum of these variables for every row in CSV file.
For this we create transformation, which contains three steps:

CSV Step to load input values
Execute R Script Step
Select Values Step

The transformation is demonstrated on the following picture:

CSV file has the following values:

R script contains two sections. The first one is used to define mapping between input fields in PDI (Pentaho Data Integration) and variables in R script. The second one is used to define mapping between output variables from R script and output fields in PDI.

We have two fields in PDI with names a and b. In R file we have two variables with names a and b. In R script we define the third variable c as a sum of a and b.
In R script there is one service variable OUTPUT of type list. Using this variable we can define output values. In our case the output is only variable c. For this variable we can define a new field in PDI. In our case this is c_res field. In Select Values step we show the values for three fields a, b, and c_res.

The result is demonstrated on the picture below.

For more complicated tasks it is recommended to define functions and use direct database connection in R script. For example, we use Run.R in PDI and call function MainScript, which is defined in MainScript.R.
/code/r/Run.R

library(RMySQL)

library(R.utils)

sourceDirectory(“/code/r/func/”);
ctx.date_from=’01.01.2000′;#this value can be transferred from PDI
ctx.date_to=’01.12.2010′;#this value can be transferred from PDI
OUTPUT<-list(“err_train”=0, “err_gen”=0);
OUTPUT<-MainScript(ctx);
/code/r/func/MainScript.R
MainScript<-function(ctx) {
con <-dbConnect(MySQL(), user=”root”, password=””, dbname=”lab”, host=”localhost”);
on.exit(dbDisconnect(con));
printf(“\nTime Period: %s-%s”, ctx.date_from, ctx.date_to)
flush.console()
#Select data from database
query = paste(“”,”select * from table where column between “);
query = paste(query, “str_to_date(‘”, ctx.date_from, “‘, ‘%d.%m.%Y’) and str_to_date(‘”, ctx.date_to, “‘, ‘%d.%m.%Y’)”, sep=””);
rs <- dbSendQuery(con, query);
result <- fetch(rs, n = -1)
printf(“\nSize: %g”, nrow(result))
dbDisconnect(con)
#Here the model can be evaluated and results can be saved in variables err_train and err_gen.
#results to PDI
OUTPUT<-list(“err_train”=err_train, “err_gen”=err_gen);
return(OUTPUT)
}

Debunking GROUP BY myths

2014-12-10T22:57:00.002-08:00

http://rpbouman.blogspot.co.uk/2007/05/debunking-group-by-myths.html

Maintain the Pentaho BA 5.0 Server Manually

2014-05-12T23:43:00.000-07:00

https://help.pentaho.com/Documentation/5.2/0P0/000/050

http://infocenter.pentaho.com/help/index.jsp?topic=%2Fadmin_guide%2Ftask_config_baserver_manually.html

\biserver-ce\tomcat\webapps\pentaho\mantle\home\content\welcome\index.html

comment line no 27 to 37 for unvisible Learn, Contribute, Meet the Family

\biserver-ce\tomcat\webapps\pentaho\mantle\home\content\welcome\img

add your logo in above path and add image path in line no 27

    <div class="header-navigation">
        <img src="img/company_logo.png">
    </div>

\biserver-ce\tomcat\webapps\pentaho\jsp\PUCLogin.jsp

Create your own app for the Pentaho BI/BA Server

2014-03-19T23:00:00.002-07:00

http://diethardsteiner.blogspot.in/2014/02/sparkl-create-your-own-app-for-pentaho.html

error in pentaho 5.0.1 - authentication via url parameters for iframe

2014-03-18T03:31:00.003-07:00

After passing userid and password in URL and page load it ask for user and password, seems pentaho server does not recognize the parameters.

check here for the workaround (it's on the comments): http://jira.pentaho.com/browse/BISERVER-10708

Workaround is to edit the system/applicationContext-spring-security.xml file and change the parameters for the filterChainProxy.

Lines 22 and 23 should be replaced by:

/api/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,requestParameterProcessingFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS
/plugin/**=securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,requestParameterProcessingFilter,basicProcessingFilter,anonymousProcessingFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS

The requestParameterProcessingFilter is missing from the config file in the CE release.

Performance tuning settings for Pentaho Reporting

2014-03-14T22:19:00.001-07:00

https://www.on-reporting.com/blog/performance-tuning-settings-for-pentaho/

remember if Alfresco Audit Analysis and Reporting plugin installed

2014-01-23T02:34:00.002-08:00

Installed Alfresco Audit Analysis and Reporting with branch Limited
This plugin requires CDA and CDE installed in the Pentaho BIServer and has been developed using Sparkl. All the configurations, logs, sources and scripts are stored in the AAAR folder. Several actions are performed during installation, in particular: - Creation of 2 database schema (one as Kettle repository and one as AAAR Data Mart). - Creation of a new JDBC data source in the Pentaho Server. - Creation of a repository structure with dashboards and reports installed inside. - Update of script stored into the file system.

how to remove WARNING: Security role name PENTAHO_ADMIN

2014-01-23T02:32:00.001-08:00

INFO: WARNING: Security role name PENTAHO_ADMIN used in an <auth-constraint> without being defined in a <security-role>

add following configuration before close </security-constraint>

<security-role>
        <description>security role</description>
        <role-name>PENTAHO_ADMIN</role-name>
    </security-role>

after installation of Pentaho Repository Synchronizer using Marketplace

2014-01-23T02:26:00.000-08:00

Installed Pentaho Repository Synchronizer with branch Trunk
This plugin copies a version of libpensol.*.jar to the server's WEB-INF/lib. It's possible that on Windows boxes there's an error upgrading the repositorySynchronizer. If that happens, stop the server and delete the WEB-INF/lib/libpensol.* jar file(s)

how to hide .CDA files

2014-01-23T02:21:00.000-08:00

To hide the .CDA files, its no use to the end user to see the datasource in the treeview so edit:

biserver-ce/pentaho-solutions/system/cda/plugin.xml

And comment the lines between:
<!–
<content-types>
…
</content-types>
//–>

MySQL server version for the right syntax to use near OPTION SQL_SELECT_LIMIT

2014-01-22T21:45:00.001-08:00

http://stackoverflow.com/questions/1...select-limit-1

mysql> select VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.6.13-log |
+--------------+
1 row in set (0.00 sec)

mysql> SET OPTION SQL_SELECT_LIMIT=10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=10' at line 1


mysql>  select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.11     |
+------------+
1 row in set (0.00 sec)

mysql> SET OPTION SQL_SELECT_LIMIT=10;
Query OK, 0 rows affected (0.00 sec)Older versions of MySQL employed SET OPTION, but this syntax is   deprecated in favor of SET without OPTION.

You should just use SET SQL_SELECT_LIMIT=10; instead.
Incompatible Change: The obsolete OPTION modifier for the SET   statement has been removed.

Setting up Pentaho Report Designer for OpenERP

2014-01-09T21:31:00.003-08:00

In order to generate reports from OpenERP v7 you need to have Pentaho Report Designer downloaded from Pentaho’s website and unzipped on your computer. The package is a binary package which doesn’t need installation on your computer and hence you can run it from the folder where you unzipped it.
Here are the steps that you can follow to setup and run Pentaho Report Designer to Connect to OpenERP v7 and create reports:

Pentaho Report Designer v3.9.1 is available from Sourceforge.net here. Download the package which is suitable to your computer architecture. For Windows Download prd-ce-3.9.1-GA.zip
You need Java JDK Version 1.6.0 and above. You can download java from www.java.com and install it on your computer.
Once Java is fully installed run the file named “set-pentaho-env.bat” from Pentaho folder. This will setup your environment and then click on “report-designer.bat”. Pentaho Report Designer should run now. Close it now and move on to the next step.
Report Designer is not yet ready to connect to OpenERP server. For that you need to download the following packages
Once you have downloaded the above packages. Copy all the jar files from point 4.1 to 4.4 to the lib directory of Pentaho Report Designer lib directory.
Extract Apache XML RPC Binary Package and copy the xmlrpc-client-3.1.3.jar, xmlrpc-common-3.1.3.jar and ws-commons-util-1.0.2.jar from the extracted lib directory to Pentaho Report Designer lib directory.
Done!! you can now run PRD again by running the “report-designer.bat” file and start developing reports for OpenERP.

Dynamic pivot tables (transform rows to columns)

2013-12-25T21:46:00.002-08:00

Dynamic pivot tables (transform rows to columns)

in MySql there is no function of automatically table transformation. Surely we can use some program (tool), which is able to connect to MySql and execute data transformation. But in this case we want to do it manually, and we have one variant – to write a query which will execute the data rotation in the table

http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value

http://stackoverflow.com/questions/10986031/sql-dynamic-view-with-column-names-based-on-column-values-in-source-table?rq=1

Transform a CDE Dasboard to be loaded in a CDF only Pentaho Server

2013-11-10T21:34:00.000-08:00

http://rhaces.blogspot.in/2011/08/transform-cde-dasboard-to-be-loaded-in.html

http://interestingittips.wordpress.com/2012/01/12/bullet-graph-dashboard/

max_allowed_packet error in mysql

2013-10-24T04:48:00.003-07:00

Caused by: java.sql.BatchUpdateException: Packet for query is too large (1434172 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

The max_allowed_packet variable can be set globally by running a query.
However, if you do not change it in the my.ini file (as dragon112 suggested), the value will reset when the server restarts, even if you set it globally.

To change the setting for everyone until the server restarts:

SET GLOBAL max_allowed_packet=1073741824;

CDE CCC charts

2013-10-23T02:33:00.002-07:00

http://redmine.webdetails.org/projects/ccc/wiki/FAQ_Main_Changes_New_Features_CCC_v2

http://forums.pentaho.com/showthread.php?150582-Bar-chart-colors-dynamically-changed-based-on-data-value-Along-with-JavaScript-vars/page2

http://www.webdetails.pt/ctools/charts/jsdoc/symbols/pvc.options.charts.Chart.html#clickAction

Property	Type	Description
scene	pvc.visual.Scene	A scene is a node in a rendering hierarchy. Rendering generally follows the shape of a scene hierarchy. Each scene contains participating datums and vars (see below).

function a(scene){
this.scene.vars.series.value //for retrieving series value
this.scene.vars.category.value //for retrieving category value
}

Creating Dashboards with CDE

2013-10-22T04:39:00.002-07:00

Creating Dashboards with CDE

http://helicaltech.com/blogs/tag/pentaho-cde/

Date Input Controls/ Components in Pentaho CDE with a working example

http://helicaltech.com/blogs/175/

http://type-exit.org/adventures-with-open-source-bi/2011/06/creating-dashboards-with-cde/

http://pentahohispano.blogspot.in/2011/07/como-hacer-cuadros-de-mando-v.html

http://translate.google.co.in/translate?hl=en&sl=es&u=http://pentahohispano.blogspot.com/2011/07/como-hacer-cuadros-de-mando-v.html&prev=/search%3Fq%3Dcde%2Bdashboards.firechange%26client%3Dfirefox-a%26rls%3Dorg.mozilla:en-US:official%26biw%3D1024%26bih%3D674

http://mbostock.github.io/protovis/

http://www.osbi.fr/vos-tableaux-de-bords-pentaho-avec-les-ctools/

Dynamic coloring of PRD Charts

2013-10-22T04:38:00.002-07:00

http://type-exit.org/adventures-with-open-source-bi/2011/05/dynamic-coloring-of-prd-charts/

D3JS Dashboard

2013-10-17T02:04:00.003-07:00

http://bl.ocks.org/diethardsteiner/3287802

Stylesheets with Pentaho Report Designer

2013-10-17T02:01:00.003-07:00

http://diethardsteiner.blogspot.in/2012/11/stylesheets-with-pentaho-report-designer.html

Pentaho Report Integration in iframe

2013-08-25T22:01:00.003-07:00

http://pentahoreportingandintegration.blogspot.in/2013/04/pentaho-report-integration-in-iframe.html

http://jira.pentaho.com/browse/BISERVER-8090

http://jira.pentaho.com/browse/BISERVER-4067

forum issue