If you're looking for Pentaho BI Interview Questions for Experienced or Freshers, you are in right place. There are a lot of opportunities from many reputed companies in the world. According to research, Pentaho BI has a market share of about 3.7%. So, You still have the opportunity to move ahead in your career in Pentaho BI Development. Mindmajix offers Advanced Pentaho BI Interview Questions 2020 that helps you in cracking your interview & acquire dream career as Pentaho BI Developer.
Top Pentaho BI Interview Questions and Answers
Q1). What is Pentaho?
Ans: It addresses the blockades that block the organization’s ability to get value from all our data. Pentaho is discovered to ensure that each member of our team from developers to business users can easily convert data into value.
Q2). Mention the major features of Pentaho?
- Direct Analytics on MongoDB: It authorizes business analysts and IT to access, analyze, and visualize MongoDB data.
- Science Pack: Pentaho’s Data Science Pack operationalizes analytical modeling and machine learning while allowing data scientists and developers to unburden the labor of data preparation to Pentaho Data Integration.
- Full YARN Support for Hadoop: Pentaho’s YARN mixing enables organizations to exploit the full computing power of Hadoop while leveraging existing skillsets and technology investments.
Q3). Define the Pentaho BI Project?
Ans: The Pentaho BI Project is a current effort by the Open Source communal to provide groups with best-in-class solutions for their initiative Business Intelligence (BI) needs.
Q4). What major applications comprised of Pentaho BI Project?
Ans: The Pentaho BI Project encompasses the following major application areas:
- Business Intelligence Platform
- Data Mining
- Business Intelligence Platform
Q5). Which platform benefits from the Pentaho BI Project?
- Java developers who generally use project components to rapidly assemble custom BI solutions
- ISVs who can improve the value and ability of their solutions by embedding BI functionality
- End-Users who can quickly deploy packaged BI solutions which are either modest or greater to traditional commercial offerings at a dramatically lower cost
Q6). Is Pentaho a Trademark?
Ans: Yes, Pentaho is a trademark.
Q7). What do you understand by Pentaho Metadata?
Ans: Pentaho Metadata is a piece of the Pentaho BI Platform designed to make it easier for users to access information in business terms.
Q8). How does Pentaho Metadata work?
Ans: With the help of Pentaho’s open-source metadata capabilities, administrators can outline a layer of abstraction that presents database information to business users in familiar business terms.
Q9). What is Pentaho Reporting Evaluation?
Ans: Pentaho Reporting Evaluation is a particular package of a subset of the Pentaho Reporting capabilities, designed for typical first-phase evaluation activities such as accessing sample data, creating and editing reports, and viewing and interacting with reports.
Related Article: Pentaho Tutorial For Beginners
Q10). Explain MDX?explain
Ans: Multidimensional Expressions (MDX) is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.
Q11). Define Tuple?
Ans: Finite ordered list of elements is called a tuple.
Q12). What kind of data, cube contain?
Ans: The Cube will contain the following data:
- 3 Fact fields – Sales, Costs, and Discounts
- Time Dimension – with the following hierarchy: Year, Quarter, and Month
- 2 Customer Dimensions – one with location (Region, Country) and the other with Customer Group and Customer Name
- Product Dimension – containing a Product Name
Q13). Differentiate between transformations and jobs?
- Transformations are moving and transforming rows from source to target.
- Jobs are more about high-level flow control.
Q14). How to do a database join with PDI?
- If we want to join 2 tables from the same database, we can use a “Table Input” step and do the join in SQL itself.
- If we want to join 2 tables that are not in the same database. We can use “Database Join”.
Q15). How to sequential transformations?
Ans: It is not possible as in PDI transformations all of the steps run in parallel. So we can’t sequential them.
Q16). How we can use database connections from the repository?
Ans: We can create a new conversion or close and re-open the ones we have loaded in Spoon.
Q17). How do you insert booleans into a MySql database, PDI encodes a boolean as ‘Y’ or ‘N’ and thus can’t be inserted into a BIT(1) column in MySql.
- BIT is not a standard SQL data type. It’s not even standard on MySQL as the meaning (core definition) changed from MySQL version 4 to 5.
- Also, a BIT uses 2 bytes on MySQL. That’s why in PDI we made the safe choice and went for a char(1) to store a boolean.
There is a simple workaround available: change the data type with a Select Values step to “Integer” in the metadata tab. This converts it to 1 for “true” and 0 for “false”, just like MySQL expects.
Q18). By default all steps in a transformation run in parallel, how can we make it so that 1 row gets processed completely until the end before the next row is processed?
Ans: This is not possible as in PDI transformations all the steps run in parallel. So we can’t sequential them. This would require architectural changes to PDI and sequential processing also result in very slow processing.
Q19). Why can’t we duplicate field names in a single row?
Ans: We can’t. if we have duplicate field names. Before PDI v2.5.0 we were able to force duplicate fields, but also only the first value of the duplicate fields could ever be used.
Q20). What are the benefits of Pentaho?
- Open Source
- Have a community that support the users
- Running well under multi-platform (Windows, Linux, Macintosh, Solaris, Unix, etc)
- Have complete package from reporting, ETL for warehousing data management,
- OLAP server data mining also a dashboard.
Q21). Differentiate between Arguments and variables?
Subscribe to our youtube channel to get new updates..!
- Arguments are command-line arguments that we would normally specify during batch processing.
- Variables are environment or PDI variables that we would normally set in a previous transformation in a job.
Q22). What are the applications of Pentaho?
- BI Platform (JBoss Portal)
- Pentaho Dashboard
2. All build under Java platform
Q23). What do you understand by the term Pentaho Dashboard?
Ans: Pentaho Dashboards give business users the critical information they need to understand and improve organizational performance.
Q24)What is the use of Pentaho reporting?
Ans: Pentaho Reporting allows organizations to easily access, format, and deliver information to employees, customers, and partners.
Q25). Define Pentaho Schema Workbench?
Ans: Pentaho Schema Workbench offers a graphical edge for designing OLAP cubes for Pentaho Analysis.
Q.26) Define Pentaho Data mining?
Ans: Pentaho Data Mining used the Waikato Environment for Information Analysis to search for data for patterns. It has functions for data processing, regression analysis, classification methods, etc.
Q27). Brief about the Pentaho Report designer?
Ans: It is a visual, banded report writer. It has various features like using subreports, charts, and graphs, etc.
Q28). What do you understand by the term ETL?
Ans: It is an entry-level tool for data manipulation.
Q29). What do you understand by hierarchical navigation?
Ans: A hierarchical navigation menu allows the user to come directly to a section of the site several levels below the top.
Q30). What are the steps to Decrypt a folder or file?
- Right-click on the folder or file we want to decrypt, and then click on the Properties option.
- Click the General tab, and then click Advanced.
- Clear the Encrypt contents to secure data checkbox, click OK, and then click OK again.
Q31). Explain the Encrypting File system?
Ans: It is the technology that enables files to be transparently encrypted to secure personal data from attackers with physical access to the computer.
Do you know: What is Oracle DBA?
Q32). What do you mean by repository?
Ans: Repository is a storage location where we can store the data safely without any harmless.
Q33). Explain why we need the ETL tool?
Ans: ETL Tool is used to getting data from many source systems like RDBMS, SAP, etc. and convert them based on the user requirement. It is required when data float across many systems.
Q34). What is the ETL process? Write the steps also?
Ans: ETL is an extraction, transforming, loading process the steps are :
- define the source
- define the target
- create the mapping
- create the session
- create the workflow
Q35). What is metadata?
Ans: The metadata stored in the repository by associating the information with individual objects in the repository.
Q36). What are snapshots?
Ans: Snapshots are read-only copies of a master table located on a remote node which can be periodically refreshed to reflect changes made to the master table.
Q37). What is data staging?
Ans: Data staging is actually a group of procedures used to prepare source system data for loading a data warehouse.
Q38). Data staging is actually a group of procedures used to prepare source system data for loading a data warehouse.
- Full Load means completely erasing the insides of one or more tables and filling with fresh data.
- Incremental Load means applying ongoing changes to one or more tables based on a predefined schedule.
Q39). Define mapping?
Ans: Dataflow from source to target is called mapping.
Q.40) Explain the session?
Ans: It is a set of instruction which tells when and how to move data from respective source to target.
Q41). What is Workflow?
Ans: It is a set of instruction which tells the Informatica server how to execute the task.
Q42). Define mapplet?
Ans: It creates and configures the set of transformation.
Q43). What do you understand by a three-tier data warehouse?
Ans: A data warehouse is said to be a three-tier system where a middle system provides usable data in a secure way to end-users. Both sides of this middle system are the end-users and the back-end data stores.
Q44). What is ODS?
Ans: ODS is an Operational Data Store that comes in between data warehouse and staging.
Q45). Differentiate between the Etl tool and OLAP tool?
Ans: ETL Tool is used for extracting data from the legacy system and load it into the specified database with some processing of cleansing data.
OLAP Tool is used for the reporting process. Here data is available in the multidimensional model hence we can write a simple query to extract data from the database.
Q46). Who is XML?
Ans: XML is an extensible markup language which defines a set of rule for encoding documents in both formats which is human readable and machine readable.
Q47). What are the different versions of Informatica?
Ans: Informatica Powercenter 4.1, Informatica Powercenter 5.1, Powercenter Informatica 6.1.2, Informatica Powercenter 7.1.2, etc.
Q48). What are the various tools in ETL?
Ans: Abinitio,DataStage, Informatica, Cognos Decision Stream, etc
Q49). Define MDX?
Ans: MDX is a multidimensional expression which is the main query language implemented by the Mondrian's.
Q50). Define multi-dimensional cube?
Ans: It is a cube to view data where we can slice and dice the data. It has a time dimension, locations, and figures.
Q51). How do you duplicate a field in a row in a transformation?
Several solutions exist:
- Use a “Select Values” step renaming a field while selecting also the original one. The result will be that the original field will be duplicated to another name.
It will look as follows:
- This will duplicate fieldA to fieldB and fieldC.
Use a calculator step and use e.g. The NLV(A, B) operation as follows:
- This will have the same effect as the first solution: 3 fields in the output which are copies of each other: fieldA, fieldB, and fieldC.
- This will have the same effect as the previous solutions: 3 fields in the output which are copies of each other: fieldA, fieldB, and fieldC.
Q52). Why can’t I duplicate field names in a single row?
Ans: You can’t. PDI will complain in most of the cases if you have duplicate field names. Before PDI v2.5.0 you were able to force duplicate fields, but also only the first value of the duplicate fields could ever be used.
Q53). I’ve got a transformation that doesn’t run fast enough, but it is hard to tell in what order to optimize the steps. What should I do?
- Transformations stream data through their steps.
- That means that the slowest step is going to determine the speed of a transformation.
- So you optimize the slowest steps first. How can you tell which step is the slowest: look at the size of the input buffer in the log view.
- In the latest 3.1.0-M1 nightly build you will also find a graphical overview of this: HTTP://WWW.IBRIDGE.BE/?P=92
- (the “graph” button at the bottom of the log view will show the details).
- A slow step will have consistently large input buffer sizes. A fast step will consistently have low input buffer sizes.
Q54). We will be using PDI integrated into a web application deployed on an application server. We’ve created a JNDI data source in our application server. Of course, Spoon doesn’t run in the context of the application server, so how can we use the JNDI data source in PDI?
- If you look in the PDI main directory you will see a sub-directory “simple-jndi”, which contains a file called “jdbc.properties”. You should change this file so that the JNDI information matches the one you use in your application server.
- After that you set in the connection tab of Spoon the “Method of access” to JNDI, the “Connection type” to the type of database you’re using. And “Connection name” to the name of the JDNI data source (as used in “jdbc.properties”).
Q55). The Text File Input step has a Compression option that allows you to select Zip or Gzip, but it will only read the first file in Zip. How can I use Apache VFS support to handle tarballs or multi-file zips?
Ans: The catch is to specifically restrict the file list to the files inside the compressed collection. Some examples:
You have a file with the following structure:
To read each of these files in a File Input step:
You have a simpler file, fat-access.log.gz. You could use the Compression option of the File Input step to deal with this simple case, but if you wanted to use VFS instead, you would use the following specification:
Note: If you only wanted certain files in the tarball, you could certainly use a wildcard like access.log..* or something. .+ is the magic if you don’t want to specify the children's filenames. .* will not work because it will include the folder (i.e. tar:gz:/path/to/access.logs.tar.gz!/access.logs.tar!/ )
Finally, if you have a zip file with the following structure:
access.logs.zip/ a-root-access.log subdirectory1/ subdirectory-access.log.1 subdirectory-access.log.2 subdirectory2/ subdirectory-access.log.1 subdirectory-access.log.2
You might want to access all the files, in which case you’d use:
Q56). Explain Pentaho Data Integration architecture?
Ans: Note: For some reason, the .+ doesn’t work in the subdirectories, they still show the directory entries. :/
The spoon is the design interface for building ETL jobs and transformations. Spoon provides a drag-and-drop interface that allows you to graphically describe what you want to take place in your transformations. Transformations can then be executed locally within Spoon, on a dedicated Data Integration Server, or a cluster of servers.
The Data Integration Server is a dedicated ETL server whose primary functions are:
Pentaho Data Integration is composed of the following primary components:
- Spoon. Introduced earlier, Spoon is a desktop application that uses a graphical interface and editor for transformations and jobs. Spoon provides a way for you to create complex ETL jobs without having to read or write code. When you think of Pentaho Data Integration as a product, Spoon is what comes to mind because, as a database developer, this is the application on which you will spend most of your time. Any time you author, edit, run, or debug a transformation or job, you will be using Spoon.
- Pan. A standalone command line process that can be used to execute transformations and jobs you created in Spoon. The data transformation engine Pan reads data from and writes data to various data sources. Pan also allows you to manipulate data.
- Kitchen. A standalone command line process that can be used to execute jobs. The program that executes the jobs designed in the Spoon graphical interface, either in XML or in a database repository. Jobs are usually scheduled to run in batch mode at regular intervals.
- Carte. Carte is a lightweight Web container that allows you to set up a dedicated, remote ETL server. This provides similar remote execution capabilities as the Data Integration Server but does not provide scheduling, security integration, and a content management system