Pentaho Cube Designer User Guide
Contents
1. Create Measures Create measures from the source fields from the earlier query Click Add button to create a measure and choose Aggregate Function Format and Display name for the measure Gaidi Remove smen agosto regato orderfact TOTALPRICE SUM ERRO FFF Total Price Page 9 Create Dimensions Step 5 of 6 Create hierarchical dimensions right panel using the available source fields left panel The created dimensions are displayed as a tree Within a dimension tree the top most element is the Dimension The second element is the Hierarchy The subsequent elements are the dimension levels as shown below Dimension Hierarchy Level 1 Level 2 Level n Dimension Hierarchy Level 1 Level 2 Level n The bottom panel displays the properties of the selected Dimension Hierarchy or Levels All the elements Dimensions Hierarchies amp Levels consist of fixed attributes displayed in the bottom property panel A level can also have custom properties A custom property for a level can be added using the Add Property button After a property is added select the desired source field from the drop down box for the property Property Column and specify a name for the property Value Column To start creating a dimension select a source field on the left side and click Add New Dimension button To add levels to a dimension select a source field on the left side select the parent level on the right sid2. Cube and Default Analysis lt Level name CustomerName table customer_w_ter column CUSTOMERNAME uniqueMembers False gt Property name First Name column CONTACTFIRSTNAME gt C Publish Property name Last Name column CONTACTLASTNAME I gt Publishes lt jLevel gt Level name Month table time column MONTH_ID nameColumn MONTH_NAME uniqueMembers false gt Hierarchy Click Finish button to save lt Dimension gt Measure name Total Price column TOTALPRICE aggregator sum datatype Numeric formatString 0 gt lt Cube gt Y Step 3 Preview the cube Spee o Dimension name Time ForeignKeyz TIME ID Hierarchy name Time hasAll true allMemberName Alll time primaryKey TIME_ID gt Psi Table name time gt Level name Vear table time column YEAR ID uniqueMembers False gt Step 4 Save your work Level name Quarter table time column QTR ID nameColumn QTR NAME uniqueMembers false Schema gt Go oo o G Cigencs Page 12 Publish Click Publish button to publish the Mondrian cube specification to Pentaho solution engine Specify the Publish parameters and click OK to complete publishing Pentaho Cube Designer Final steps Step 1 Review the XML Cheie Step 2 Publish to the Pentaho BI Sers Publish Information Publish to the Pentaho BI Server Cube and Default A
3. Query Designer panel limitations Map Tables Page 1 The query designer panel works best when automatic joins are used joins automatically created due to presence of Primary Key Foreign Key relationships In the event Primary Key Foreign Key relationships are not present Manual joins can be created in the Query designer panel When manual joins are created following are the rules to be followed For star schema Facttable always needs to be on the left side of the display Dimension tables always needs to be on the right side of the display For snow flake schema Facttable always needs to be on the left side of the display Dimension tables always needs to be on the right side of the display The highest level dimension table must start first and then the lowest levels to the right side Example Fact gt Year gt Quarter gt Month gt Day Page 2 Flow The following are the steps and screen shots of cube designer Application distribution Below is the directory structure for Windows distribution The lib jdbc folder contains the J DBC drivers To start the application on windows run CubeDesigner exe To start the application on Linux run CubeDesigner To start the application on Mac OS run CubeDesigner app S D myworkspace customersNPentaho dev deploy win32 win32 86CubeDesigner Bie Edit View Favorites Tools Help ay ene ee Tipe eed ates 1 File Folder 9 25 2006 11 24 AM File Fo
4. X TARERETRERIDRR REIR X RELHR E 15 IS CSI COS ER T TETTE CETTE a A a a E a 16 Overview This document describes the Pentaho cube designer The cube designer helps create Mondrian cube schema using a graphical user interface and publish the cube schema to Pentaho solution engine Software requirements JRE 1 5 and above Oracle MySQL Microsoft SQL Server Hibernate or any J DBC compliant database Mondrian server Pentaho server Windows Linux Redhat Suse Mac OSX PPC Carbon The Cube wizard supports any J DBC compliant database The J DBC drivers for Oracle MySQL Microsoft SQL Server amp Hibernate databases are included in the s w distribution If the need is to access any other JDBC compliant database the necessary J DBC driver must be copied into CubeDesigner lib jdbc folder Pentaho cube designer is distributed as stand alone application Mondrian supported schemas The following cube schema types are supported using Cube designer Single table schema both measures and dimensions are from a single table Star schema single fact table and multiple dimension tables Snow flake schema single fact table and hierarchical dimension tables Limitations Not supported features Following are the limitations not supported features in Cube Wizard Mondrian features not supported Shared Dimensions Aggregation Calculated Members nline Tables Multiple cubes within the same schema file
5. e and click gt button Page 10 BlPentaho Cube Designer Create Dimensions Create Dimensions Create dimensions from the source fields using Add New Dimension button You can add levels by using the Arrow buttons Once dimensions are created select the dimension hierarchy level to view change the attributes at the bottom section Click Add Property button to add custom properties for a level Add New Dimension Source fields Dimensions orderfact PRICEEACH EM Customer orderfact TOTALPRICE Eh Customer customer_w_ter CUSTOMERNAME fi customer w ter CONTACTLASTNAME CustomerName customer w ter CONTACTFIRSTNAME Cam B N Time J Eds Time Ble Year C Sle quarter Le Month nameColumn uniqueMembers False Page 11 Final Steps Step 6 of 6 After all the desired dimensions are created use Next button to go to the final page The last page provides 4 steps to finish View XML Click View XML button to review the XML specification for Mondrian cube OHI Cube Designer Final steps 1 e Bx Source xi Step 1 Review the xw JL Source XML DOM Tree lt xml version 1 0 encading LITF 8 gt ube Miewx j Dimension name Customer foreignKey CUSTOMERNUMBER gt Step 2 Publish to the Pen lt Hierarchy name Customer has ll true allMemberName All customer primaryKey CUSTOMERNUMBER gt ra Table name customer_w_ter gt
6. lder 9 15 2006 3 59 PM File Folder 9 25 2006 11 24 AM ti eclipseproduct ECLIPSEPRODUCT File 9 25 2006 11 24 AM S CubeDesigner exe Application 9 25 2006 11 24 AM T CubeDesigner ini Configuration Settings 9 25 2006 11 24 AM f readme txt Text Document 9 25 2006 2 13 PM El startup jar Executable Jar File 9 25 2006 11 24 AM le objects 208 KB 3 My Computer Page 3 Launch screen When user launches Cube designer the below initial screen is displayed Click the File 2 New Cube Schema menu item to start creating the cube schema BlPentaho Cube Designer Chl Shiites Page 4 Specify a cube name Step 1 of 6 Specify a name for the cube The same name will be used for both Mondrian schema name and cube name Once done click Next button BlPentaho Cube Designer Start Here Start here by specify a cube name To start creating a cube specify a cube name The specified cube name will be used as the name for the XML files generated Cube Name Mondrian S Cube Description Test Mondrian Cube through Cube Designer Page 5 Create new or select an existing data source Step 2 of 6 Using this screen you can create a data source connection BlPentaho Cube Designer Data Source Select a data source Create or choose a data source connection Data source connections can be made to relational databases via JDBC JNDI Add JNDI Connection x JNDI Name stee
7. lwheels Driver com mysql jdbc Driver z Connect String Username steelwheels Password E Cancel Ge Os GENE Page 6 Select the data source and click Next button BlPentaho Cube Designer Data Source Select a data source Create or choose a data source connection Data source connections can be made to relational databases via JDBC JNDI Page 7 Map Tables Step 3 of 6 Using the Query Designer panel you can select the tables and create the necessary schema including Star schema or Snow flake schema After the schema is created select the columns for measures and dimensions and click Next button O Pentaho Cube Designer WETERE LIEGE s I time MONTH ID time OTR ID AS Itime YEAR ID AS fF PHONE X ADDRESSLINE1 ORDERDATE X ADDRESSLINE2 i REQUIREDDATE p ar X SHIPPEDDATE i STATE i status X POSTALCODE JE COMMENTS JE COUNTRY JV CUSTOMERNUMBER X EMPLOYEENUMBER Jv TIME ID X CREDITLIMIT JF TERRITORY where l group by having order by expression Page 8 Create measures and set the attributes Step 4 of 6 Create the desired measures in the cube using Add button The values for Aggregator Format and Display Name can be changed as desired After the measures are selected and attributes are set click Next button O Pentaho Cube Designer Create Measures
8. nalysis View Publish Location Publish Isanplesjandysis Step 3 Preview the cube ese ee http localhost 8080 pentaho RepositoryFilePublisher Publish Password aR j Step 4 Save your work Server Userid Click Finish button to save your work Server Password Iv Create JBoss Datasource Chem Catanie Page 13 Preview Click Preview button to preview the Mondrian cube that was published to the Pentaho solution engine gi Pentaho Cube Designer Preview Cube Final Steps MondrianCube Ome Ofer A SeeBex Step 1 Review the XML Measures customer Time gt Tota Price au customer van time 0 645 249 16 Slicer Step 2 Publish to the Pentah Cube and Default Analysis View Ciao Step 3 Preview the cube Camine Step 4 Save your work Click Finish button to save you 2006 Pentaho version Page 14 Save The generated XML can be saved into the desired location Click the Finish button to select the directory location After specifying the directory location click Finish button The following files are created Mondrian Cube Schema file Pentaho XACTION file Pentaho XACTION property file Pentaho Cube Designer Final steps Step 1 Review the XML CMewxML j Step 2 Publish to the Pentaho BI Server Cube and Default Analysis View CsPublish j S
9. pentano open source business intelligence Copyright 2006 Pe respective owners For the latest information please visit our web site at www pentaho org ution permitted All trademarks are Contents VSI WIG Wc teeters ET mtd ed dr emt Rm LIE 1 Software requiremaernts uua cca comte Penta eene ani AGON UNE NENEA EN ENAKA NEARE RUR 1 Mondrian supported schemas siirsin eene nnn nnno t ehe nete EEEE ESENE EE aas baee RR ue bens 1 Limitations Not supported features ccc ec eter te ne eee e ne nnn nnne ener nnne enn nn 1 Mondrian features not supported roro titt ta sk unten e ces x Ph pn rie baec aniani annan aria 1 Query Designer panel limitations Map Tables 0 0 ee aie mereri 1 bloss mf nni c IE ond EL S nti e MED ANLE 3 Application distA BUTION Emm 3 Ehud E E EE AEAEE ides NAE ee EE i 4 Specify a c be name Step Lof 6 eec rir iia aa AAE AEGEA 5 Create new or select an existing data source Step 2 of 6 6 Map Tables Step 3 Of 6 remet neon baia tini near ri menda ges a rb Uv ea Nn Ru Kreative 8 Create measures and set the attributes Step 4 Of 6 nemen nennen 9 Create Dimensions Step 5 of 6 nini enn 10 Final Steps Step 6 Ob D x ssssssesnssunssarstaactneisaedacarcutvaweissastinneatsdoletuncstdacinssannedaeabtanchanedstancstsanesanieatanecite 12 PP jm 12 sl 13 diu 14 SAVE ssec sdsiesunit kiere x dear do d Ese D Ee RER TRY NOR AFER RE
10. tep 3 Preview the cube Previews Step 4 Save your work Save Information Save Location Documents and Settings Administrator Desktop Browse Click Finish button to save yc Gi oo CiEwihu GE Page 15 Preferences The default preferences for the Cube Designer can be set and managed through the interface To access the Preferences page click Window Preferences menu item as below O Pentaho Cube Designer Page 16 The below screen shows the default preferences page Bi Preferences inl x Cube Wizard Preferences M 7 _ Cube Wizard Preference Set deafault values for Cube Wizard preferences H General Install Update Save Location Publish Location Isamples analysis Web Publish URL http localhost 8080 pentaho RepositoryFilePublisher Preview URL http localhost 8080 pentaho ViewAction IV Create JBoss Datasource gt Restore Defaults Apply ome Page 17
Download Pdf Manuals
Related Search