Best Practices for Choosing Content Reporting Tools and Datasources - PowerPoint PPT Presentation

Best Practices for Choosing Content Reporting Tools and Datasources Andrew Grohe Pentaho Director of Services Delivery, Hitachi Vantara

Agenda Discuss best practices for choosing content with Pentaho Business Analytics Platform. This session will also cover the various datasource options for Pentaho content. • Pentaho Content Creation Options • Tool Review • Tool Option Decision Tree • Pentaho Content Datasource Options

Reporting Content Creation Options • Browser-Self Service • Client-Workstation

Browser Authoring Tools

Pentaho Analyzer • Analyzer creates cross tabular reports or charts • Exclusive datasource is Mondrian metadata model • Analyzer is highly interactive with drill-down, drag and drop • Color schemes can be customized to match your style • Only one table or a chart can be shown at once (multi-chart) • Formatting is limited to field names, width and numeric • Prompts can only be achieved via inclusion in a dashboard

Interactive Reporting • Only tabular reports can be created. No Charts • Best used on detail row-level data • Exclusive datasource is Pentaho metadata model • PIR is highly interactive with drag and drop and filtering • Color schemes can be customized to match your style • Only one tabular dataset can be shown at once • Formatting is limited to column headers, width and numeric

Dashboard Designer • Dashboard designer can create tabular reports or charts • Exclusive datasource is Mondrian metadata model • Recommended to used other reports vs creating in dashboard • Allows many tables or charts can be shown at once • PDD is highly interactive with drag and drop and prompting • Formatting is limited to widget titles and sizing • Color schemes can be customized to match your style • Prompts are available to send parameters to reports

Dashboard Editor • Dashboard Editor can create tabular reports, charts and maps • Datasources can be Mondrian, Metadata, PDI, SQL or custom • Recommended to use when highly interactive or a specific style is required • Allows many tables or charts to be shown at once • CDE can be built to do a lot or little interactivity • Formatting is unlimited and can use CSS • Color schemes can be customized to match your style • Prompts are available to send parameters to reports

Client Authoring Tools

Report Designer • Banded report designer that can also create charts • Can include many widgets over many pages • Datasources include MDX, MQL, PDI and SQL • Charts and tables can be placed side-by-side and top bottom • Interactivity is limited to hyperlinks and can be extended via JavaScript • Formatting is nearly unlimited to all field properties • Color schemes can be customized to match your style • Prompts can be automatically generated based on metadata • Pre-defined output formatting for PDF, XLS, HTML, CSV, RTF

Pentaho Data Integration • PDI is not a report writer but can output data • Output formats include XLS, CSV and TXT • Excel templates can be used during output • PDI can also run Report Designer or other content reports and email results • PDI transformations can be called via the Pentaho server REST API • Typical use cases involve producing large datasets and/or multi-tab excel sheets

Content Authoring Decision Tree

Reporting Decision Tree • Only one table or visualization – PIR, PAZ, PRD – PIR and PAZ do not support multiple visualizations • Is charting required? – PAZ, PRD, CDE, PDD – PIR does not have charting • Is interactivity required? – PAZ, PIR, CDE – PRD is static with interactivity limited to hyperlinks • Is self-service required? – PAZ, PIR and PDD – PRD requires local installation and is not self-service

Pentaho Content Datasource Options

Content Datasources • Pentaho Metadata • Mondrian Schema • Structured Query Language (SQL) • Pentaho Data Integration

Pentaho Metadata • Provide an abstraction layer between the database and consumer of the model • Generally used for detail oriented reports • Metadata models can be placed on relational or dimensional database schemas • Database columns exposed via the model should only be those useful in reports • Models should be kept as small as possible and under 10MB • Models can be used in PIR, PRD, PDD and CDA • Security can be defined centrally in the model or in a custom java SQL Generator implementation • Reporting tools express queries in terms of MQL. MQL is sent to server where it is translated into one SQL statement by a SQL Generator. Result sets are streamed back to the client

Mondrian • Provide an abstraction layer between the database and consumer of the model • Exclusively used with summary/aggregate data (sum, avg, min, max, etc..) • Mondrian allows for complex calculations to be performed on top of aggregates • Mondrian schema work best on dimensional models • Mondrian schemas can be used in PAZ, PRD, CDE, CDF • Security can be defined centrally via Grants or in a Custom Role Delegate (CRD) or Dynamic Schema Processor (DSP) • Reporting tools express queries in terms of MDX. MDX is sent to server where it is translated into one or more SQL statements. SQL is optional if the result data resides in cache. Result sets are streamed back to the client.

SQL • Direct access to underlying data structures • Generally used other layers do not satisfy requirements • SQL can be used to provide advanced queries for reports • It is typically best used for operational schemas • Security has to be implemented for each report • Report Designer is the only reporting tool that allows SQL • No caching is provided for SQL datasources • SQL can provide detail and summary reports

Pentaho Data Integration (PDI) • Provides GUI for advanced transformations and blending of data in realtime • Those transformations can be provide data for reports • It is typically used to join multiple data sets • Security has to be implemented for each report • Report Designer and CDA allow for PDI datasources • Caching is provided for PDI datasources used in Data Services • PDI can provide detail and summary reports

Community Data Access (CDA) • CDA queries are defined and executed on the server • CDA can provide an additional caching layer for reports • CDA can tie into Pentaho Security for report • The same CDA file/query can be used in CDF/CDE and PRD • CDA can join together disparate datasets

Reporting Tools and Database Access Report Viewer SQL Metadata MQL Interactive Reports SQL Mondrian MDX Dashboard Designer DB Analyzer SQL KTR Dashboard Editor SQL CDA Security

Summary

Summary What we covered today: • Discussed the variety of tools Pentaho provides to create content • Reviewed a Decision Tree to help in making a decision • Outlined the options for providing data in Pentaho Business Analytics

Next Steps Want to learn more? • Attend “Leverage the Power of Pentaho Visualizations Within Your Application” • Schedule a Solutions Workshop to tailor a roadmap for your implementation