Changing Pentaho Enterprise Console or Admin Console password & Publishing password :

         If you forget or if you want to change admin user password for Enterprise Console(Administration Console in Community Edition)

1. ----->
Do the following simple steps to change the admin console password:

1. Navigate to BI Server (biserver-ce-3.8.0-stable)\administration-console\resource\config
2. Open Login Properties file.
3. Get the MD5 encrypted text for your password (visit http://md5-encryption.com/ to create md5 encrpted password)
4. You will get something similar to 'vsdsdrfew232fesfq23'
5. Now replace 'admin: OBF:23f223ef23resf5ess' with 'admin: MD5:vsdsdrfew232fesfq23'
6. Restart your server and try login using your new password.
If any issue clear your browser cache and try.

2. ---- To change the publishing password.

Also If you want to change pentaho components publishing password, edit the following file and replace your own password, it is in plain text format.

<Pentaho-Home>\server\biserver-ee\pentaho-solutions\system\publisher_config.xml

<publisher-config>
<publisher-password>newpassword</publisher-password>
</publisher-config>

Save the file and try publish the pentaho bi component with new password. It would work fine.

SQL Resultset preparation in Pentaho Action Sequences ( xactions )

             While we are passing external query to the prpt report or some other purposes we need to modify the sql result set or we need to prepare our own sql resultset by adding some rows or some columns or need to make column wise data into row wise data  to fulfill our business requirement.


So we can prepare sql result set by using javascript step in pentaho xactions.



Add the action new action step "Relational"

      Process Actions > Click on + Button (Add new Action) >> Get Data From >>> Relational

The following is the sample query.

SELECT
    '10' AS AAA_Q1,
    '30' AS AAA_Q2,
    '15' AS AAA_Q3,
    '25' AS AAA_Q4,
    '20' AS BBB_Q1,
    '35' AS BBB_Q2,
    '15' AS BBB_Q3,
    '25' AS BBB_Q4 from dual

This action output value would be a resultset(query_result).

Add the another action, new action step "JavaScript". This is where you can prepare your own resultset.

  Process Actions > Click on + Button (Add new Action) >> Get Data From >>> JavaScript

   1. Use previous action ("Relational") output as input for this action.
   2. At javaScript console, you can use these following methods to get resultset values and to add the value to the result.

      2.1   To get the row count
            query_result.getRowCount();
      2.2  To get the column count
             query_result.getColumnCount();
      2.3   To get the value from the resultset, index values would start from 0.
              query_result.getValueAt( rowIndex, columnIndex);

      
      2.4  To create javascript resultset and assign to one variable.
                      records_result = new JavaScriptResultSet();      

      2.4  To add column header array to newly created result set.
                 2.4.1 column header array creation.
                 labelColumnVals = new Array('AAA','YYY');
         
              records_result.setColumnHeaders(labelColumnVals);

      2.5 To add rows to newly created resultset.
               records_result.addRow(rowArray);
             
      Sample logic to add rows to the newly created reultset.
            
                  numofrows = 2;
                  colCount = 0;
                  var row = new Array();
                
                    for(i=0; i < numofrows ; i++) {           
                           row[0] = query_result.getValueAt( 0, colCount++);
                           row[1] = query_result.getValueAt( 0, colCount++);
                           records_result.addRow(row);
                           }
     
    You can use your own business logic to create resultset.   

You can use this resultset output as Relational action output resultset where you want in your logic or in a query as a subquery.      
             
               

Kettle Conceptual Model Diagram

The following diagram illustrates, Pentaho Data Ingratiation overall idea for what it can do...

Bi-Passing Pentaho User Console (Removing Pentaho Security)

  We can remove the Pentaho Security features by simply updating two files. We achieve this, by creating single user and role and by giving system-level access to that user.

   Those two files are
 <PENTAHO-BI-SERVER-HOME>\pentaho-solutions\system\applicationContext-spring-security.xml
<PENTAHO-BI-SERVER-HOME>\pentaho-solutions\system\pentaho.xml


          Please follow the steps at following wiki link to update those files...
          http://wiki.pentaho.com/display/ServerDoc1x/Removing+Security

Passing external Query to Pentaho reports ( PRPT )

 
    Usually, we may have requirement like changing the query and and query parameters dynamically, in such scenarios, we need to pass the query externally to the report.
 
    So how to pass the query externally to the prpt report and how can we give the parameters in that ?

    Very soon, I would provide the detailed post for it...

Pentaho Data Integration Repositories & Commands to run transformations and Jobs

Pentaho Data Integration Repositories & Commands to run transformations and Jobs :

We can save kettle jobs and transformations in three type of repositories.
 1. File Repository
 2. Database Repository
 3. Enterprise Repository (We can create this repository in Pentaho Enterprise Edition )
             
          I have mentioned the commands to run the transformations and jobs which are saved in all three types of repositories for windows as well as Linux.
 
    To run the jobs, we can use kitchen.bat or kitchen.sh command.
    To run the transformations, we can use pan.bat or pan.sh command

 Do the following steps to run the commands.

 1. Open the command prompt
 2. Go to the tool home directory.
   $> cd <data-integration-home>
      for me, it is a c:\pentaho\design-tools\data-integration.
      ex : cd c:\pentaho\design-tools\data-integration 
 3. Now run the command

Windows :

 File Repository: 
      pan.bat /file:"C:\Users\Suribabu\Desktop\Pentaho\repository\Trans\Example.ktr" /level:Basic 
      kitchen.bat /file:"C:\Users\Suribabu\Desktop\Pentaho\repository\Jobs\Example.kjb" /level:Basic 

 Database Repository :
        pan.bat /rep:"DB_Rep" /trans:"Example" /dir:/Trans/ /user:admin /pass:admin /level:Basic 
        kitchen.bat /rep:"DB_Rep" /job:"Example" /dir:/Jobs/ /user:admin /pass:admin /level:Basic 

 Enterprise Repository:
        pan.bat /rep:"DB_Rep" /trans:"Example" /dir:/public/Trans/ /user:joe /pass:password /level:Basic
        kitchen.bat /rep:"DB_Rep" /job:"Example" /dir:/public/Jobs/ /user:joe /pass:password /level:Basic


 Linux : 
File Repository:
     pan.sh -file="/home/suri/repository/Trans/Example.ktr" -level=Minimal
     kitchen.sh -file="/home/suri/repository/Jobs/Example.ktr" -level=Minimal 

 Database Repository : 
       pan.bat -rep:"DB_Rep" -trans:"Example" -dir:/Trans/ -user:joe -pass:password -level:Basic 
       kitchen.bat -rep:"DB_Rep" -job:"Example" -dir:/Jobs/ -user:joe -pass:password -level:Basic 

 Enterprise Repository:
       pan.bat -rep:"DB_Rep" -trans:"Example" -dir:/public/Trans/ -user:joe -pass:password -level:Basic
       kitchen.bat -rep:"DB_Rep" -job:"Example" -dir:/public/Jobs/ -user:joe -pass:password -level:Basic 

 You can schedule the above commands or You can write wrapper script on top of the commands as follows and schedule it:

example.bat or example.sh 
# cd <data-integration-home>
cd c:\pentaho\design-tools\data-integration 
pan.bat /file:"C:\Users\Suribabu\Desktop\Pentaho\repository\Trans\Example.ktr" /level:Basic


 Now You can run and schedule example.bat or example.sh file for your ETL Trans.

Pentaho Business Intelligence

       Pentaho Business Intelligence Suite is a collection of software applications intended to create and deliver solutions for decision making.
        It's a BI Server plus standalone client tools and it is having a graphical interface for managing data sources, users and roles and other administrator functions ,called  Pentaho Enterprise Console("Pentaho Administration Console" for community edition ).
     BI Suite= BI Server + (standalone client tools and PEC).
     The BI Server is a Java Web application that contains specialized content creation engines plus a graphical user interface ( Pentaho User Console) for interacting with them.
BI Server Contains :
      BI Platform,
      Analysis Engine,
      Reporting Engine,
      Data Integration Engine,
      Pentaho User Console(PUC).

      This includes an application server (Tomcat) and three WARs: one for the Pentaho Web application, and two optional WARs that provide style sheets for BI Server content.

BI Platform:
       The BI Platform is a workflow engine that forms the operational core of the BI Server. It ties the other content engines(Reporting, Analysis, and Data Integration ) to the Pentaho User Console to provide content display, delivery, and scheduling functionality.

The Reporting (JFreeReport) engine provides functions which create and render data-driven reports and
charts in a variety of formats.

The Pentaho Analysis (Mondrian) provides the ability to create an analysis schema, and to form data sets from that schema by using an MDX query.

The Data Integration (Kettle) engine enables Data Integration jobs and transformations to run in conjunction with other BI Server processes.(Data Integration Server)

 Pentaho Client Tools:
 Pentaho Data Integration:
           It's an ETL tool, some times it is called as KETTLE, which enables us to access and prepare data sources for analysis, data mining, or reporting. This is generally where you will start if you want to prepare data for analysis.

 Schema Workbench:
           A graphical tool that helps us to create ROLAP schema for analysis.We need to create mondrian schema to create analyzer report or JPivot report to analyze the data.

Report Designer:
          An advanced report creation tool. If you want to build a complex data-driven  report, this is the right tool to use. Report Designer offers far more flexibility and functionality than the ad hoc reporting capabilities of the Pentaho User Console(PUC).

Design Studio:
          An Eclipse-based tool that enables you to hand-edit a report or analysis view xaction file. Generally, people use Design Studio to add modifications to an existing report that cannot be added with Report Designer.(To create pentaho solutions and action sequences)

Aggregate Designer:
         To improve mondrian schema efficiency and performance, we can create aggregate tables using aggregate designer tool.

Metadata Editor : 
          Pentaho Metadata Editor is a Graphical User interface for creating a user friendly metadata model on top of complex relational (initial release) data sources.  These business views allow administrators to suppress the complexities of underlying data sources, enrich the models through concepts (formatting, data suppression, descriptions, and more), and apply role based security .

Dashboards:
         Dashboards are used to monitor and analyze Key Performance
Indicators (KPIs). A set of tools incorporated to the BI Suite in the latest version
allows users to create interesting dashboards, including graphs, reports, analysis
views, and other Pentaho content, without much effort.

There are three ways to create dashboards:
1.Enterprise dashboard (In PUC)
2.Community Dashboard Framework
3.Pentaho Dashboard Editor.