Home

SpagoBI QuickStart

1. 0 8 May 17 2006 pag 11 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform 3 Recurring themes SK Both the development and the administration interface are under revision to achieve SS a better usability 3 1 PORTLET LAYOUT Every user portlet points out some common caractheristics e On the top there is the title identifying the portlet meaning e On the right side of the title some icons allow the access to the general functions acting on the portlet s content The main functions are where admitted _ going back to the previous page without saving changes Every portlet A creating a new element Every portlet switching from the list view to the tree view Document config switching from the tree view to the list view Document config saving information Details pages saving information and going back to the previous page Details pages pE mA testing before saving LOV details e The character identifies the required fields 3 2 LIST AND DETAILED VIEW One of the most common views in SpagoBI is a simple table showing a list of elements Common characteristics are e On the top
2. Checks 0 n Te Te ge Roles 1 n Predefined and fixed value LOV 1 switched on the users role No checks Fig 5 Parameter s implementation example Every parameter can be associated to many different analytical documents also for category driving their behaviour according to its rules SpagoBI QuickStart ver 0 8 May 17 2006 pag 9 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae ee Oe EED http creativecommons org licenses by nc sa 2 0 XS pagoB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform The recording phase of an analytical document must therefore set its structural driver engine CMS and executive parameters information When a user with its role runs an analytical document the structural information are read and then a custom page for the parametric input is produced on the basis of the execution information At the end the document is produced on the basis of the inserted values explicitly or implicitly A sample follows in the picture below Analytical document Analytical document Analytical document Output format Choose City Product Department Choose Month Customer Name Output format HTML gt imer Surname oe Lo 4 Lookup PARAMETERS Analytical document Analytical
3. This wizard allows you bo create a configuration For Hibernate Console a Mame DatamartJarconsole Property File Browse Gonfiguration file Osbamertdearfsrehibernqat Entity resolver Browse Enable hibernate ejbs annotations requires running eclipse with a Java 5 runtime Mapping Files Mame sss Add Remove s j r Classpath tonly add path For PoJ and driver Mo Hibernate jars oS Add JOPDir CHDownload drivers database postgresgl S 0 311 jdbec2 jar 4dd External JARS I carce Figure 9 New Hibernate console SpagoBI QuickStart ver 0 8 May 17 2006 pag 59 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Exec the hibernate code generation tool Figure 10 Run Window Help o gt EFG e P 1 DabamartGen Run As d 2 Hibernate Code Generation ai Organize Favorites Figure 10 Hibernate code generation In the main tab give a name to the generation select as output folder the src folder of the project check the reverse engineer from jdbc connection option give a package name for the generated java classe
4. pag 8 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0 COM MON S D EED XSpageB S p a g O B l Q u i C k S t a r t rY the Business Intelligence Free Platform Parameter Modalities 1 n s 0 n Role Role Role Roles 1 n Use Mode LOV 1 a O lt Check Check Check Checks 0 n Role Role Role Roles 1 n LOV 1 m O Use Mode Check Check Check Checks 0 n PARAMETER Role Role Role Roles 1 n LOV 1 O lt Use Mode Check Check Check Checks 0 n Fig 4 Parameter s abstract structure Call center Internal Roles 1 n operator support Manual input without default LOV 1 Is Exists on prd family table Checks 0 n Pr brand director Ope Roles 1 n secretary Query for the prd family list LOV 1 matching the brand responsability Parameter Modalities 1 n s 0 n Manual and free input for support users a pS ae Predefined lists for brand directors ontains the right brand prefix Exists on prd family table Checks 0 n PRODUCT FAMILY f Q oO Oo S s 3 AR a p o O g o g n lt lt a Default value for prd family directors
5. s role No matter whether he owns the role or not Instead if the document is in Released state the list includes only his own roles in case he is associated with more than one role SpagoBI QuickStart ver 0 8 May 17 2006 pag 28 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike common License To view a copy of this license visit COM MON S D EED http creativecommons org licenses by nc sa 2 0 X SpageB tY the Business Intelligence Free Platform SpagoBI QuickStart By clicking on the EXECUTION icon the Analytical Document will be run and if necessary a page for the input parameter will be displayed allowing the user to insert the required information from and according to the Analytical Document configuration Business Intelligence Documents single Customer profile Single Customer profile Gender All HTML Output fonmat City Customer Name Customer Sumame Reset fields Figure 32 Parameters page OQ The page is under a revision phase When a parameter is defined as a Query type the corresponding input field becomes a Lookup table where you can choose your value Business Intelligence Documents J Customer Name by Query Name Anna Rev Beverly Beverly Bishop Brenda Bryan Surname Albright Desalvao Cardoza Baker Meastas Marshall Rutledge Br
6. the as attribute identifies the alias that will be used by the SpagoBI document For this example it is necessary to create an XML file containing the following text lt xml version 1 0 encoding UTF 8 standalone yes gt lt olap gt lt connection name defaultDWH gt lt cube reference WEB INF queries FoodMart xml gt lt MDXquery gt select Measures Unit Sales Measures Store Cost Measures Store Sales on columns Parameter ProductMember Product Product All Products Food wat willste children ON rows from Sales where Time 1997 lt parameter name prdCd as ProductMember gt lt MDXquery gt lt olap gt Save the file as productSales olap 5 3 2 CREATE PARAMETERS Connect to the home page of SpagoBI portal http localhost 8080 portal and log on using bidev both as username and password This user is a Developer for the SpagoBI Demo and therefore you will access the Developer Tools page To create a new Parameter the following steps are required 1 Predefined List of Value LOV 2 Predefined Values Constraints 3 Parameters Management 5 3 2 1 Predefined List of Value LOV 1 The Predefined List of Values page can be accessed by selecting the corresponding link from the Developer tools 2 Click on the INSERT icon to add the new desired element SpagoBI QuickStart ver 0 8 May 17 2006 pag 44 of 64 Engineering Ingegneria Informatica
7. the title identifying the table meaning e The first row shows a label for each column displayed e The list can be divided into pages that can be turn over using the two arrows on the bottom row e The current page and the total number of pages are displayed in the middle of the bottom row e Every list has a detailed page showing and allowing to modify all the data about a single element Every list is alphabetically ordered on the first column s content the label and each row shows the essential data of an element always identified by a unique label or title On the right side of every row some icons drive the operativeness on the single element row of the list The main possible functions are where admitted e 4 accessing the details page for the selected element row Every list e i deleting the corresponding element row Every list e a executing the corresponding element row Analytical Doc list only SpagoBI QuickStart ver 0 8 May 17 2006 pag 12 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Aen ae ne a D http creativecommons org licenses by nc sa 2 0 X SpageB S p a g O B l Q u i C k S t a r t TY the Business Intelligence Free Platform e Selecting all Tree management A standard view of a list and detailed page follows
8. 63 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 SpagoBI QuickStart 7 Glossary amp y Work in progress Analytical document Portal Analytical portal Parameter LOV list of values User role Report OLAP Data Mining Dashboard Scorecard XS pageB Y the Business Intelligence Free Platform SpagoBI QuickStart ver 0 8 May 17 2006 pag 64 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed creative under the Creative Commons Attribution NonCommercial ShareAlike Ce arnons COM MON S D EED License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0
9. A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform eW a0 Into Open in New window e i JAR Export JAR Package Specification Define which resources to package into which JAR Copy E Faste Select the resources to export J Delete E gt DatamartJar C x classpath Move E Me EM C x project ae M 8 it eng hib datamart C it hibernate cfq xm pay Import 2 Refresh Close Project a M Export generated class files and resources ee Compile JSF Export all output folders for checked projects Convert to AOF Project ek Export java source files and resources Run s an ii Debug As Select the export destination Team t Compare with t JAR File Cidatamart jar x Browse Restore from Local History Options Deployment M Compress the contents of the JAR file Source a Add directory entries PDE Tools f Overwrite existing files without warning Properties 0 Ruri Packaging CHRR z Back Next gt __ Cancel Figure 15 16 Export jar file The datamart jar model can be taken from the destination directory and loaded as template into a SpagoBI datamart Object SpagoBI QuickStart ver 0 8 May 17 2006 pag
10. By clicking on the name of one of them the user can set respectively the order by and the group by fields If the user tries to access one of these pages without having already selected at least a field in Field Selection page the Qbe displays an error message Each selection can be erased by means of the relative icons g To delete a v To sort SpagoBI QuickStart ver 0 8 May 17 2006 pag 34 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ak ODE ae ne ae ED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t Y the Business Intelligence Free Platform 4 4 1 3 View Query In the View Query the query realized through the wizard procedure is displayed on the left hand side of the page FOODMART DATAMART FOODS QUERY COMPOSITION RESUME QUERY Qbe automatically composition Expert Composition Select select aSalesFact1997 store storeName as StoreName aSalesFact1997 store store pn RAE VR ave aSalesFact1997 storeSales as StoreSales from n it foodmart SalesFact1997 as aSalesFact1997 where avg aSalesFact1997 storeSales as Store Sak F aSalesFact1997 store storeName Store 7 group by Erom aSalesFact1997 store storeName order by i E se iat aSalesFact1997 store storeName itfoodmart SalesFact1997 as
11. CREATE A REPORT TEMPLATE USING IREPORT 1 If you don t have iReport 0 5 1 you can download it from http sourceforge net project showfiles php group id 64348 2 Unzip the downloaded file 3 Before starting iReport it is necessary to copy the file hsg db1_8_O_2 jar that can be found in the common lib directory of Exo Tomcat in the lib directory of iReport Furthermore delete the hsg db1_61 jar file 4 Now it s possible to run iReport 5 In order to create a simple report example select New Document from the File menu Type SpagoBI _ Example as Report name and click on OK From the Datasource menu select Connection Datasources Click on New and fill in the following information e Name SpagoBI_foodmart e JDBC Driver org hsgldb jdbcDriver e JDBC URL jdbc hsgldb hsal localhost foodmart e Username sa Select Save 6 Open the Report query window from the Datasource menu and in the Report SQL query text field enter the following example query select FIRST_NAME LAST_NAME from EMPLOYEE e POSITION p where p POSITION_ID e POSITION_ID and p POSITION TITLE P EmployeePosition This simple query will visualize the First Name and the Last Name of every employee whose position title is equal to the value of the parameter EmployeePosition 7 From the Project Browser on the left hand side of the window expands Object library Right click on the Project Browser and click on Add and then on Parameter Type Emplo
12. Hibenate Dialect Hibenate Dialect STRING HIB_SQLSERV_DIALECT HIB_SQLSERV_DIALECT HIB_SQLSERV_DIALECT STRING JNDI_DS Jndi Datasource Jndi Datasource STRING OUT_HTML Output HTML Output HTML STRING OUT_TP Output Type Parameter fo Output Type selection STRING gy K KK KK KK KK a page i of 3 The value of the column LABEL starts with gt Filter All Figure 23 Parameters list A parameter is identified by the following information Label unique identifier of the parameter Name name of the parameter Description brief description of the parameter Type define if this parameter is a date a number or a string To insert a new parameter the developer has to fill the following form Development Environment PARAMETER DETAILS Type Date C Number C String SpagoBI QuickStart ver 0 8 May 17 2006 pag 23 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Figure 24 New parameter form When entering instead an existing parameter detail page he can see also the use modes of the parameter the tab list in the following picture f Development Environment PARAMETE
13. Number Surname Name Title First address Second address Third address Other address City Figure 36 QbE Field Selection The user can display each section simply by clicking on the corresponding tab on the title line at the top of the window In the following paragraphs each section will be described in more detail 4 4 1 1 Field Selection SpagoBI QuickStart ver 0 8 May 17 2006 pag 32 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 X SpageB Y the Business Intelligence Free Platform SpagoBI QuickStart ij FoodMart acs On the left hand side of the window a logic view of the datamart is displayed Therefore the user can easily navigate the tree m Promotion oe i en and select the desired fields simply by clicking on them B Store Product i a F Eg H Annual Sales Fact 3 if _ Annual Sales Fact Store Name gt Store Name gee Product identifier ae i rg z F a x TER S a _ Annual Sales Fact Store Sales G Store Sales uen Ime Eni ET gt Customer Identifier E detinet i i _ Promotion Identifier sum i a ced i i Hea Store Identifier l avgi g Store Sales ae Store Costs mint og Unit Sales i ne l maxi Hg Promot
14. QuickStart ver 0 8 May 17 2006 pag 7 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit COM MON S D EED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t TY the Business Intelligence Free Platform SpagoBI builds an executive environment which places in the middle the operative model referred to the particular reality in use Analytical document Analytical document Analytical document Structural configuration Analytical document Analytical document EA y a v of Product Department es c Store Sales ts amp Store Cost gt Unit Sal eta E Data Mining QbE Query by Example Structural configuration Structural configuration Fig 3 Executive configuration A parameter models a concept or a datum frequently used as discriminant on the global data context Every parameter can be used in many different ways according to the different end users roles So every use mode refers to an initial visualization method to one or more validation rules and to one or more end user roles Follow a parameter abstract schema and an implementation example SpagoBI QuickStart ver 0 8 May 17 2006
15. activation of the reports with default values Olap a multidimensional analysis model Dashboard a synthetic static and dynamic presentation of historical and current series with especially interests SpagoBI QuickStart ver 0 8 May 17 2006 pag 30 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 MS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform e Dynamic Dashboard a synthetic and dynamic presentation of the performance indicators values to be monitored at fixed time intervals e QbE Query by Example the module for a free and visual inquiry of the predefined data items e Manual the user manual Even if the navigation portlet can run all the visible documents the portal can be composed by several pages and sections every portlet addresses a specific document for a free composition of the informative scene and for an immediate view of the particular business context Every user can use the Released document according to his role s visibility When the user owns different roles and the documents have different behaviours the role for which the document has to be executed is required The end user has different freedom degrees of movement and of personalizat
16. and PARAMETER but when the system runs regularly you have to associate just the parameters already created to the new documents You have to create new ones only for exceptions rs 4 2 1 PREDEFINED LIST OF VALUE LOV From the Developer Tools view it is possible to access the list of the Predefined List of Value LOV It is described by some identifying data label name and description and by its Input Type SpagoBI QuickStart ver 0 8 May 17 2006 pag 19 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit ee ee a ee http creativecommons org licenses by nc sa 2 0 X SpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Development Environment J PREDEFINED LIST OF VALUES LABEL NAME DESCRIPTION INPUT TYPE CITY_QY_01 City by Query City List of Values Managed by Query QUERY CITY_QY_02 City in Canada City in Canada by query statement QUERY CON_SOQL_SERVER CON_SQL_SERVER CON_SQL_SERVER SCRIPT COUNTRY_FX_01 Country Canada Country by fixed value FIX_LOV COUNTRY_QY_01 Country by Query Country List of Values Managed by Query QUERY CST_NAME_OQY Customer Name Customer Name by Query QUERY CST_SURNMAME OY Customer Surname Customer Surname by Query QUERY DEPA QY 01 Department by Query Department Descritption Managed by Query QUE
17. back to the Development Object List by clicking on the BACK icon SpagoBI QuickStart ver 0 8 May 17 2006 pag 42 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Now all the parameters have been correctly created and configured on the Analytical Document So you can execute it 13 Click on the EXECUTE icon corresponding to the new Report Document 14 In the new page you will have to enter a value for the Report Doc Param 15 Click on the DETAILS icon and choose one of the predefined values in the list by clicking on the corresponding DETAILS icon Select HQ Information System 16 Click on the EXECUTE icon to execute the document 17 Now it is possible to Update State by clicking on the corresponding icon at the bottom of the window Notice that when the Document Example is updated it will not be listed in the Development Object List anymore 18 Logout 5 2 4 TEST THE ANALYTICAL DOCUMENT amp y Work in progress 5 2 5 EXECUTE THE ANALYTICAL DOCUMENT amp y Work in progress 5 3 OLAP ANALISYS This following example is designed to quick introduce new users to create a new OLAP in SpagoBI T
18. document Analytical document Analytical document Data Mining Structural configuration Structural configuration Structural configuration Structural configuration Fig 6 Analytical document execution A new analytical document requires a process handling as the one shown in the following schema SpagoBI QuickStart ver 0 8 May 17 2006 pag 10 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed ORAMO under the Creative Commons Attribution NonCommercial ShareAlike ons License To view a copy of this license visit MONS DEED http creativecommons org licenses by nc sa 2 0 XS pageB the Business Intelligence Free Platform SpagoBI QuickStart Analytical document development Parameter create Parameter use mode create Roles association Analytical document registration Structural and executive config lt Ghech n gt Check association i Update state to N Document show Developed se right data State update to Released Analytical document ready for the end user Fig 7 Analytical document handling The schema points out also the management of the approval flow for the analytical document passing from the developer to the tester reaching the end user when certified only Please see in the next paragraphs how SpagoBI allows to run all these operations SpagoBI QuickStart ver
19. e to test if the hibernate tools are working open the view Navigator and then into the navigator box press the right button of the mouse and select New others menu Into the new window you should be able to see an hibernate folder Figures 1 2 Select a wizard Create a new hibernate cfg xml file Helping with the initial IDEC setup etc a copi AY Package Wizards We rasis Class E cys fay thibceE amp Interface gt EJB 3 0 El hiner as fe Export G En ate Conti al it Annotati 2 Annotation P Fibernate Cone EEA Refresh a ia ai ie Source Folder Folder File P Hibernate Reverse Engineering File reveng xml tf Java wG JBossGOP JBoss IDE i JBoss jBPM i Other wS Plug in Development gt Simple Untitled Text File EF JUnit Test Case LP Example el XML ul a akkeri Ctrl h Tele rT ee Examples Cancel Figures 1 2 Eclipse Navigator and New Hibernate options SpagoBI QuickStart ver 0 8 May 17 2006 pag 56 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Egreative under the Creative Commons Attribution NonCommercial ShareAlike ons License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0 COMMONS DEED SpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Once installed and tested the hibernate tools c
20. op EAE ENE TEONE ERE A OEE ee inven conan oneness 39 5 2 2 1 Predefined List of Value LOV 0 ec ecccccccccccceeeccccesececeesecceeeececsesececeuseceseeecessueecessueecesseceeeeneeees 39 5 2 2 2 Predefined Values Constraint cccccccssssssssseeeecccccccceceeeaaeeesssseeeeeeeeeeeeeeeeeeaaaessssseeeeeeeeeeeeeeeeeeaaaaggaseses 40 5 2 2 3 Parameters Mana Seimei oieivassssnesssnscesncoaepetvosassusyvessestwncnscanwasususaerwdadynoneaddantendseuouamandwessansdamenonicsandsi tes 40 Idd Register the Analytical Document the built report into the platform cccccccccseeeeecccccceceaeeseeseeeeeaaaeees 4 5 2 4 Tesi the Analyn cal DOCUMENT seriis na NATENANE AE 43 Dela FE XCCUIC THE TNGIVIICA Document vinsascusdcrantinsssuawsk Goaasiveawcineuuadierap EEEE NEEE EEEE E 43 To EAA A e no sete ns rc eres nec E Osan eens b leans nae se tine E E EE 43 5 3 1 VC LMI OG EE EEE E E EEEE AE EEE OAO N 44 2 CHEGIO POPAIICICIS aa E E AENA AA 44 5 3 2 4 Predefined List Or Value LOY vesssscssssessssasacieanonsssanidatsaeasoasbonanaaaadoiesnsidnddadidankwoaduamsoudadansaaibacnaaanbosancts 44 SpagoBI QuickStart ver 0 8 May 17 2006 pag 2 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 S p a g Oo B Q u i c k S t ar t
21. reader to the SpagoBI concepts by means of a full example based on the SpagoBI demo distribution The demo is freely downloading from the ObjectWeb forge http forge objectweb org project showfiles php group_id 204 The document includes the following main chapters e Conceptual overview Introduction of the core concepts of the SpagoBI free open source platform e Getting started with SpagoBI How to build step by step an analytic application by means of the case study available in the SpagoBI demo distribution Starting from the development of a report template using the iReport tools you are introduced to the document parametrization and configuration following a logical path Issues regarding Portal e System configuration are intentionally placed at the end of the chapter as they involve more complex topics The chapter uses a static simple report example to explain the SpagoBI main concepts e In more depth How to build an analytical portal in more detail References For further information about SpagoBI platform refer to the following documentation available on the project site http spagobi eng it 1 Cazzin G Ruffatti G SpagoBI Overview 2 Cazzin G SpagoBI Architectural Design 3 Zoppello A SpagoBI Installation Manual SpagoBI QuickStart ver 0 8 May 17 2006 pag 4 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution No
22. t tY the Business Intelligence Free Platform The current SpagoBI version uses the following drivers e JasperReport report analysis e Jpivot OLAP analyis 4 1 2 FUNCTIONALITIES MANAGEMENT SpagoBI uses its own file system named Functionality Tree that allows to better organize documents by grouping them by folder regulating the access to them This multi level hierarchical structure Fig 12 can be created and modified exclusively by the administrator in the Function Administration area iS Notice that it is only possible to add a new folder to the root element called Functionalities Areas but not to modify it Mites Lievelanpment Environment FUNCTIONALITIES TREE iy Functionalities Tree Ey Functionalities Areas 0 Business Departments Ey Analytical Areas Fig 12 Functionalities Tree A list of possible actions can be visualized by clicking on a node of the Functionalities Tree The administrator can Delete an existing functionality if this doesn t contain any sub nodes Moreover he can create a new functionality By choosing the Insert option he can access a new page where he can fill in all required information This new element will be child of the selected one Detailed information regarding an existing functionality can be displayed and modified by selecting the Detail option Seti em Development Environment FUNCTIONALITY DETAILS Name Static Reporting Description Static Re
23. 6 The Development Objects List will be updated with a new row containing the document just created 7 Now you can access the Document Details page simply by clicking on the DETAILS icon on the row of the new document This page will list the general detailed information of the document On the right side of the page a new table listing the just added template is displayed 8 In order to set Document Parameters it is necessary to fill the DOCUMENT PARAMETER DETAILS form below the WEw tab insert the following information e Title OLAP Doc Param e URL Name prdCd Then click on PARAMETERS LIST icon in the PARAMETERS LIST page now displayed select OLAP Parameter from the table Notice that the URL Name must match the alias as attribute of the parameter created in the template example created in the paragraph 9 Save and go back to the Development Object List by clicking on the SAVE AND RETURN icon SpagoBI QuickStart ver 0 8 May 17 2006 pag 47 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Now that all the parameters have been correctly created and configured on the Analytical Document you can
24. AMETER DETAILS Title Month Parameter Month Url Name ParMonth Fig 15 Administration Object Details e Engine the name of a registered engine that has to be used to execute the Analytical Document e Template a file containing the model of the document to be created with an external application suitable for the specific type of the Analytical Document On the right hand side of the page in the Template Version table all templates that have been selected for this document since it has been created are listed For each template this list specifies the version identifier the date when this selection occurred first and the name of the file Through this view the user will always be able to erase download or select one of the listed templates In the lower part of the page you can see all the parameters applied to the document In the DOCUMENT PARAMETER DETAILS section there are the following information e Title the title of the document parameter e Parameter the parameter name that is applied to this document e Url Name the key of the parameter the URL for the document execution will contain an attribute with key specified by the content of this field and value specified by the parameter value You can switch from one parameter to another by clicking on the required element in the tab list The meaning of the field Parameter will be clarified in the next paragraph and then in the examples in chapter 5 Spa
25. Development Environment PREDEFINED LIST OF VALUES eS 3 LABEL NAME DESCRIPTION INPUT TYPE CITY_QY_01 City by Query City List of Values Managed by Query QUERY CITY_QY_02 City in Canada City in Canada by query statement QUERY 4 CON_SQL_SERVER CON_SQL_SERVER CON_SQL_SERVER SCRIPT 4 2 COUNTRY_FX_01 Country Canada Country by fixed value FIX_LOV 4 3 COUNTRY_OY_01 Country by Query Country List of Values Managed by Query QUERY 4 x CST_NAME_QY Development Environment QUERY 4 x CST_SURNMAME C PREDEFINED LIST OF VALUES DETAILS Asih bs DEPA_QY_01 QUERY 4 K EMPPOS QY_01 Sa emvovons idCz 7 QUERY 2 FOODMART_JNDI_ ones lciytyQuy OOOO O O 7 SCRIPT 4 amp lt a Description City List of Values Managed by Query A input Type Query statement bd Wizard Query Connection name Foodmart Data Warehouse Query definition jselect distinct city state_province as province country from customer order by city 4 Update fields from query Value column Visible columns city Iv province A country A Fig 8 List details Example SpagoBI QuickStart ver 0 8 May 17 2006 pag 13 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Fre
26. OT eck te cree ss os ceri oo wc cae ce cise cs sedges EAEE ER 14 4 1 2 FURCHONGUIICS MANO EMENT erates r e NETE NE 16 4 1 3 Docume a COM LOI scree erins see sarge T E E E ER sae Peels atauamaeceneanesteten Iy k DE OE eesie sciesuse en cain od eco sessed tate E E 19 4 2 1 Predejined Listo Value LOV esre aea EE a E EEA AEE S 19 4 2 2 Prede ined VLC SCO IIIS caisesesrieie eiir E EE EOE E EES 21 4 2 3 Paramelers Monar CII IU casrsedetarcsapu ren OANA E EEE ENEE 23 4 2 4 Docu em con PaO ia a EN S A R es 25 A EG TIP sec aten cree ssa saceonea ates seaencoanem A E ae neaenedsateunaamemeneness 21 AE MEDS T E E E O E aaeuijaenoenaess 30 4 4 1 ODIL OID DYL NOND Cerena asda asec eases 3 AA Field Sele CIO Ms tacnes osceossateaanes pasncaebanacecusauadeces E a Nai GP AA Cond Ol ieina N E E E E ENa 33 BAM NCW O a E E eetaanes 35 BAM AVC OE a a E E caatessyecaatniceneoimcseeowmatseaeaee 35 gS ROS PVO Wy ee r E E EA E OEE E E E 36 5 GETTING STARTED WIH SPA GOD ccc ccscccccccctuscscesnceseweststcccessses evestuedecesnvecevestacccuesssececestuedecestuenewestuzsexes 36 5 1 INSTALL EXOTOMCAT AND SPAGOBI DEMO eecsssssseecccceccceeeeeeescesstsnsseceaaaacceeeeseseseseesetsssaceesaseceeeeeeeeees 37 Po lt 1 5 21 20 UA ere nr TE er eee ee ee er ee ee ere 37 32d Create a Report template using IRepoftt cccccccccccceeeccccccecce ee eeeseeeeeee eae eeeeeeeeeeeaaaeeeeeeeeeeeaaaeeeeeeeeeeeaaaaeeeeeeeaaas 38 Jnl VCO GE ON ONET OT EE E past nates en eag retina
27. R DETAILS Label prop co Name Product Code Description Parameter for Product Code selection Type Date Number String Pronus PROD_FOOD New PARAMETER USE MODE DETAILS Label prop List Name lProduct from lst Description Product from predefined list of values Lov Name Product by Fixed Lov Roles Associations g Spaga biadmin g spaqobidey a apago bitest ia ipago biuser Ai Spago bi Predefined values constraints E Internet Address E Numeric E Alfanume rc Letter String Mandatory Fiscal Code E Mail American Date italian Date MaxLenght20 MaxLenght30 Range 10 20 2 decimal paces Figure 25 Parameter details As described before each parameter can manage different user roles simply by assigning a specific way to collect data and specific constraints to validate the final input This means that each role must be assigned to a specific Use Mode Each Use Mode requires the following information e Label Use Mode unique identifiers e Name Use Mode name e Description brief description of the Use Mode e Predefined List of Value it is possible to select just one way to collect data from the list of available LOVs Click on the LOV LOOKUP image and then select a Predefined List of Value from the lookup page To create a new List of Values please refer to the Predefined List of Value paragraph e Roles Association list of the roles associated to this Use Mode A role canno
28. RY EMPPOS_OQY_01 Employment Position by Query Employment Position Tite Managed by Query QUERY FOODMART_JNDI_DS Foodmart_Jndi_ Ds Foodmart Jndi Datasource SCRIPT gy KK KK KK KR 4 page 1 of 4 The value of the column LABEL gt starts with j Fitter All Fig 17 Predefined List of Values Accessing the details page general information are displayed Label LOV unique identifiers Name LOV name Description brief description of the LOV optional Input Type four different types of LOV are admitted allowing o Manual Input the free parameters allocation o Query the database retrieval of all the selected values o Fixed LOV the organization of an arbitrary value list o Script the registration of methods and classes delegated to the recovering of all the values to suggest Development Environment PREDEFINED LIST OF VALUES DETAILS abel evaa S Name oy byQuery S input Type Query statement v Wizard Query Connection name Foodmart Data Warehouse Query definition jselect distinct city state_province as province country from customer order by city Update fields from query Columns Value column Visible columns city province country Figure 18 Predefined List of Values Details SpagoBI QuickStart ver 0 8 May 17 2006 pag 20 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAli
29. S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XSpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform 3 In the Predefined List of Values Details page fill in the following information Label OLAP LOV FIX_LOV 1 Name OLAP LOV FIX_LOV 1 Description Input Type Fixed list of values New item name Non Consumable New item value Product All Products Non Consumable click on the ADD icon e New item name Food New item value Product All Products Food click on the ADD icon e New item name Drink New item value Product All Products Drink click on the ADD icon 4 When the data entry is completed click on the SAVE icon to save the information and exit to the Predefined List of Values page 5 Now click again on the INSERT icon to create a second LOV 6 In the Predefined List of Values Details page fill in the following information Label OLAP LOV FIX_LOV 2 Name OLAP LOV FIX_LOV 2 Description Input Type Fixed list of values New item name Non Consumable New item value Product All Products Non Consumable click on the ADD icon e New item name Food New item value Product All Products Food click on the ADD icon 7 When the data entry is comple
30. X SpaqeB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform SpagoBl QuickStart Authors Grazia Cazzin Luca Scarel SpagoBI QuickStart ver 0 8 May 17 2006 pag 1 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed greative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit MO oe O nee a D http creativecommons org licenses by nc sa 2 0 S p a g o B Q u i c k S t ar t the Business Intelligence Free Platform Index VR SIN assests E one ee he mcece ce sndeee uaneeeeessonree S 4 DT VN assesses nc ees tesa sss sees ane natecetic usec sabe cate seueuccesenecapecereueecsenenes 4 REFERENCE aac strc c aecet cence ce tuc sce E E 4 BP RE acca coc cass cc acc E E e 5 1 1 Pl TST ONY ION 5 ceraian r A see rate E AE OE vectra geese 5 Lo OIA SONS gtr teense eee tate elec eet teeta ese E seein cere eee asco sete ences 5 2 SPAGOBI CONCEPTUAL OVERVIEW sssssssesssssssssoccsscccscsessssoscssesssssossosssscsssssssssessssesssessssssssssssssssssssssssssssso 6 3 RECURRING THEMES ee ee eS ee ee eer E RR 12 3 1 FOr TETELA TOU err rere een eee ee re ee nT Re eee N E ee E er ee ere 12 32 LBTANDDETALED VIEV gisroeiistsonikorionniini h i an a a R a Eaa a S TENER 12 A FNC TIONAL OVERVIE Y coireanna apaa EE aap aE Ea ENEE aaae 14 4 1 ADMINISTRATOR apeiron ana oE weer tees ve epson EEN E OEE ESEN 14 4 1 1 Lnomnes Conf oural
31. aSatsFaot 99 Resume from Qbe Where a Sales Fact1997 store storeName Store 7 Group By aSalesFact1997 store storeName Order By BE Resume bast Expert Query a Sales Fact1997 store storeName Execution Modality E Execute query compose Figure 40 QbE View Query The user can also try to write his own query in the text area on the right hand side of the page This is called Expert Composition The Resume From Query icon can be used to copy the query automatically realized by the Qbe in the text area in order to modify it The user can also save the expert query realized and resume the last expert query saved simply by clicking on the corresponding icons The two radio buttons on the bottom line of the page must be used to set if the default query to be executed is the one realized in the Qbe Automatically Composition or in the Expert Composition 4 4 1 4 Save Query FOODMART DATAMART FOODS SAVE Final Query The query is correct store Name store Sales Expert Query The query is correct storettame storesates SpagoBI QuickStart ver 0 8 May 17 2006 pag 35 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike common License To view a copy of this license visit COM MON S D EED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the B
32. abe ali run time float f 15 f rand nextFloat 100 10 buf append f buf append gt j buf append lt rows gt return buf Output Single alue C List of alues Figure 20 Script wizard 4 2 2 PREDEFINED VALUES CONSTRAINTS The developer can register some typologies of formal controls applying to the values inserted in the documents activations phase SpagoBI QuickStart ver 0 8 May 17 2006 pag 21 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed greative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit ee ee a DEED http creativecommons org licenses by nc sa 2 0 XSpageB Y the Business Intelligence Free Platform SpagoBI QuickStart Development Environment CONFIGURABLE CONSTRAINTS LABEL CK CUS 01 CK CUS 02 CK CUS 03 CK CUS 04 CK CUS 05 CK CUS 06 a NAME American Date Italan Date MaxLenght20 MaxLenght30 Range 10 20 2 decimal places PREDEFINED CONSTRAINTS LABEL CK FIX 01 CK FIX 02 CK FIX 03 CK FIX 04 CK FIX 05 CK FIX 06 CK FIX 07 4 NAME Internet Address Numeric Alfanumeric Letter String Mandatory Fiscal Code E Mail DESCRIPTION The format of american Date Define the format of the italy date MaxLenght20 Length 30 characters Value between 10 20 2 decimal places page 1of1 The value of the column LABEL bd starts with Fil
33. accessing to the Document Configuration by the administrator This area allows the administrator to manage the extraordinary maintenance of the documents ES Notice that the administrator is also a user and therefore he can execute all the released documents belonging to a folder on which he has the Execution permission Static Settings Development Environment ADMINISTRATION OBJECTS LIST LABEL NAME DESCRIPTION TYPE Bests ProdMonthSaks Best 5 Products for Monthly Sales Cob DASH BestProdDailySakes Best Products for Daily Sales Lines DASH BestProdDailySalTab Best Products for Daily Saks Tab DASH BestProdMonthSalTab Best Products for Monthly Saks Tab DASH Budg Satisi Budget satisfation DASH EmpOccDegree Emp occupation degree DASH FOODMART_DATAMART Foodmart Datamart Foodmart Datamart DATAMART Incid Cost Incidence of Costs DASH OLAP PROD 02 Product Analysis 02 Online analysis about product saks OLAP OLAP PROD 03 Product analysis 03 Online analysis about product sakes OLAP a page 1 of 4 The value of the column LABEL starts with 7 Filter All BRR BRR RK KB RB Fig 14 Administration Objects List Each document is described by a subset of its attributes as follows e Label the document unique identifier e Name the document name e Description a brief description of the document optional e Type this field shows if the document is a Report an On line analytical processing OLAP a Data Mining model a Dash
34. ageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Figure 28 Analytical document details existing document In every parameter tab for Document Parameter Details the user is required to fill in the following information e Title Document Parameter name e Parameter clicking on the PARAMETERS LIST icon a lookup page for the available parameters list will be displayed e URL Name parameter name on the document execution URL This must match the corresponding parameter belonging to the template The developer can switch from one parameter to another by clicking on the required element in the tab list and can insert new parameters by clicking on the Wew tab and fill in the form Notice that during this phase it is not necessary to specify anymore about users roles because they are completely managed through the visibility rules of the functional tree view and through the behavioural parameters description 4 3 TESTER The tester bitest bitest user main tasks are e validating the produced Analytical Document to simulate all its predefined roles e updating the Document state to release the documents that becomes available for the end user Business Intelligence Documents EXECUTION OBJECTS TREE View only documents in test state ATE d Objects Tree A 4 Functionalities Areas i Business Departments 45 Analytical Areas Ely Static Reporting a Customer profile S
35. ailed information of the document On the right side of the page a new table listing the just added template is displayed 8 In order to set Document Parameters it is necessary to fill the DOCUMENT PARAMETER DETAILS form below the WEw tab insert the following information e Title Report Doc Param e URL Name EmployeePosition Then click on PARAMETERS LIST icon in the PARAMETERS LIST page now displayed select Report Parameter from the table Notice that the URL Name must match the name of the parameter created in the report template example in the Create a Report template using IReport paragraph 9 When this operation is completed save information by clicking on the SAVE icon 10 It is possible to create a standard parameter in order to choose the output format of the document Click on the Wew tab and add the following information e Title Choose output format e Parameters Output Type e URL Name param_output_format Same parameter is used by many of the existing Analytical Document listed in this k Notice that the Parameter is already present in the SpagoBI DEMO Moreover the DEMO This is an example of how to use the same resource for different document Furthermore notice that the URL Name refers to a predefined parameter for the Jasper Report engine 11 When this operation is completed save information by clicking on the Save AND RETURN icon The new parameter will be displayed in the list 12 Go
36. ales by city ae Product profile 02 z i Employment position fy Employee in store SE Single Customer profile ve Lr Montly Sales ir Product details Or Emp Pasition mae y Single Customer H Dimensional Analysis com Data Mining Models cm Dashboard Ely Queries fica Foodmart Gatamart SpagoBI QuickStart ver 0 8 May 17 2006 pag 27 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit ee ae ee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Figure 29 Tester s documents tree This type of user must verify the formal correctness of the registered documents and check if the documents in a test state works correctly and if they fulfil the requirements By means of the functional tree view this page lists all documents having Test or Released as current state a document in Test state shows a T in its icon They belong to a folder for which the user has at least one role with a required permission respectively for Test and Execution ED For a better understanding of the Security Policy please refer to the Document organization and to the Security Policy paragraph The Tester can only execute a specific document by selecting it from the Functionalities Tree or fro
37. ame Surname The part on the left side of the equal sign is the complete name of the hibernate object with a prefix class for the tables and field for table fields The QBE tool will use the right part of each row as a graphical label for the correspondent object The file label properties is the default one and if you need to define your labels in only one language it s enough but if you want to translate each label in different languages you have to define one properties file for each language Each file must be named with the following rule label_ lt lt ISO country code gt gt properties So the files for english italian labels will be named label_en properties label_it properties Qbe will automatically choose the correct file based on the portal or browser language in case there isn t a suitable file it will use the default one To create the jar file first compile the project selecting the Projects clean menu Figure 14 ee Run Window Help y Operi Project y Build Working Sek w Build Automatically Generate Javadoc A Figure 14 Compile project and then export the project as a jar file Into the export window properties select the src folder of the project give a destination for the produced jar and then press the next button next button another time and finish button at the end Figures 15 16 SpagoBI QuickStart ver 0 8 May 17 2006 pag 62 of 64 Engineering Ingegneria Informatica S p
38. are the main project activities The development of an analytical portal with SpagoBI doesn t require the implementation of some J2EE services you have only to set up the analytical documents and to register them correctly in the platform The analytical documents provide the end user with the needed information in the most suitable way SpagoBI allows to use many categories of analytical tools Report OLAP Data Mining Dashboard Visual Inquiry Customer profile Measures Product Store Unit Sales 9 Store Cost Store Sales Name Derry Gender F Marital Status M Carousel All Stores 841 298 30 1S0 TE Ciy Issaquah Postal Code 73980 Checkout All Stores 1 779 779 12 3 767 71 County USA Address 7640 First Ave Health and Hygiene All Stores 16 284 4 601 99 32 571 86 Education Bachelors Occupation Professional 50K 70K Bathroom Products lt All Stores 5 885 1 719 32 11 881 60 St Sales Unit Sales St Costs Month StSales Unt Sales St Costs Canada cuss shu Lard Ries Hed cay 4 Mexico 4USA 1 719 32 11 881 60 Cold Remedies All Stores 5 496 32 3 356 71 Decongestants All Stores i 412 29 3 300 54 Hygiene All Stores f 545 38 6 062 45 Pain Relievers All Stores 1 428 68 7 970 56 Household All Stores 038 6 993 68 60 469 89 Periodicals All Stores 1 933 53 9 056 76 Data Mining 4 Foodmart Datamart Qa v of Product Category Product Catego
39. board etc e State this information indicates if the document must be developed Development tested Test or can be executed Released Moreover the document can also be Suspended if it cannot be executed anymore SpagoBI QuickStart ver 0 8 May 17 2006 pag 17 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 SpageB S p a g O B l Q u i C k S t a r t TY the Business Intelligence Free Platform The administrator has the List View but he can switch to the Functionality Tree in order to have a list of documents grouped by functionality clicking on the VIEW AS TREE icon The complete list of information can be seen in the Document Details page Static Settings MISAN DOCUMENT DETAILS _ i eset ince 2 5 Fri Nov 18 18 32 58 CET 2005 Top_10_product jrxml Name pto procit 2 4 Thu Oct 20 14 13 08 CEST 2005 Top_10_product jrxml Wed Sep 28 16 27 25 CEST 2005 Top_10_ product jrxml Type Report Tue Sep 27 09 02 32 CEST 2005 Top_10_product jrxml Tue Sep 27 08 52 46 CEST 2005 Top_10_product jrxml Engine Jasper Report Rel State Devebpment Template Sfoglia Month Number tops Output New Tue Sep 27 08 40 27 CEST 2005 Top_10_product jrxml DOCUMENT PAR
40. ckStart ver 0 8 May 17 2006 pag 14 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae ee Oe EED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform the same engine inside different environments development test production in order not to invalidate its performances and to use different and parallel engines inside the same environments For the correct use of the engines it is very important to set the proper driver this is a SpagoBI component delegated to configure the analytical document properly communicating to a specific engine Therefore they can be seen as an Adapter set between the analytical document and the specific engine A list of all the registered engines can be displayed accessing the Engine Configuration w ENGINES LIST LABEL NAME DESCRIPTION OLAP BO DEV Business Objects DEV Business Objects Engine for Development Environment OLAP CST ORA DEV Custom Oracle Engine DEV Custom engine for Orack rdbms in Development Environment OLAP JP MD DEV Jpivot Mondrian Dey Jpivot Mondrian Olap Engine in Development Environment OLAP JP MDB REL Jpivot Mondrian Rel Jpivot Mondrian Olap Engine in Release Environment REP BIRT DEV Birt Report Engine DEV Eclipse Birt R
41. e HQ Information System SpagoBI QuickStart ver 0 8 May 17 2006 pag 39 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t t the Business Intelligence Free Platform New item value HQ Information System click on the ADD icon e New item name HQ Marketing New item value HQ Marketing click on the ADD icon e New item name HQ Human Resources New item value HQ Human Resources click on the ADD icon e New item name HQ Finance and Accounting New item value HQ Finance and Accounting click on the ADD icon Notice that usually Name is the field that allows the comprehension of the Value field 7 When the data entry is completed click on the SAVE icon saving the information and exit to the Developer Tools page 5 2 2 2 Predefined Values Constraints 1 The Predefined Values Constraints page can be accessed by selecting the corresponding link from the Developer tools It is divided into two parts on the top side a list of Predefined Constraints is displayed on the bottom the Configurable Constraints 2 Click on the INSERT icon to access the Constraint Details page and create a new constraint 3 Insert the following information e Label Repor
42. e HibernateTools 3 1 working we advise to follow these steps e download eclipse 3 1 sdk It s strongly recommended to install the tools over a new and clean installation of eclipse 3 1 It s possible to download the 3 1 version from the url http download eclipse org eclipse downloads index ph e install the eclipse platform simply unzip the zipped downloaded file everywhere you want a new folder named eclipse will be created ECLIPSE HOME SpagoBI QuickStart ver 0 8 May 17 2006 pag 55 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t TY the Business Intelligence Free Platform e download JbossIDE 1 5 final tools file which contains the hibernate tools It s possible to download it selecting the JBossIDE 1 5 ALL zip file from the link http sourceforge net project showfiles php group id 22866 amp package id 72248 amp rele ase id 382687 e install the JBoss IDE tools over the eclipse platform unzip the JBossIDE 1 5 ALL zip file a new folder named eclipse will be created TOOLS HOME copy all the contents of TOOLS HOME plugins and TOOLS HOME features into respectively ECLIPSE HOME plugins and ECLIPSE features folders e Start the eclipse platform
43. e Platform 4 Functionality overview 4 1 ADMINISTRATOR If you do not have familiarity with SpagoBI yet we suggest you to skip all this section chapter 4 1 using the standard demo settings The administrator biadmin biadmin user main tasks are e Registering and configuring each analytical engine used inside the platform e Configuring the functional structure that classifies the analytical documents and distributes the rights required in order to use it and to access it e Maintaining the registered analytical documents b Notice that the administrator manages the structural configuration of the platform These functions are provided by means of a single portlet that can be included into a portal environment supporting its specification Melati i evelopment Environment ADMINISTRATION TOOLS Engines Configuration Functionalities Management Documents Configuration Fig 9 Administrator portlet 4 1 1 ENGINES CONFIGURATION Engines are external applications or internal SpagoBI classes delegated to display the final results of an analytical document There are different engines to deal with different analytical areas Report OLAP Data Mining and Dashboard More than one engine could be available for the same area The SpagoBI administrator has to mark the attributes that are necessary for the correct use of an engine By means of a correct configuration the user can both use the same instances of SpagoBI Qui
44. each file and make the changes src Gt Fl eng hib datamart o E Account hbm xml J Account java J Accountid java X Category hbm xml J Category java J Cakegoryid java X Currency hbm xml J Currency java J Currency ld java X Customer hbm xml J Customer java J CustomerId java X Days hbm xml Figure 13 Java classes and Hibernate mapping files SpagoBI QuickStart ver 0 8 May 17 2006 pag 61 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit COM MON S D E ED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform By default the qbe tools will show the objects into the graphical interface using the native names of the database table adding to it the complete package name of the hibernate classes This behaviour can be changed adding to the source directory a label properties file This file must contain a set of association between the native object name and the label to show into the graphical interface An example of this file is listed below class it foodmart Promotion Promotion class it foodmart Store Store class it foodmart Product Product field customerId Customer Identifier field accountNum Account Number field ln
45. eeasaseceeeeeeeeeees 53 6 5 USER DEFINITION AND ROLES MANAGEMENT sssesssssssseeeecccceeeeeeeeeseesssssnceessaaeceeeeeeseesseessesssseesssaaeeeeeeeeeeees 54 CO PORTE DETINI pase cece O T 54 O ADDAN ENO a E E 55 6 5 FUNCTIONALITY TREE MANAGEMENT sciciacsitisirinpnioian a nni iiaa a iaia a e a aai aE 55 6 9 DATA MART JAR DEVELOPMENT FOR QBE FEATURE esssesssseceessresercresrceesstersetersecresereeseceesererseeeesereesereest 55 6 9 1 BD TOC O O O aE A E EA E EE 55 6 9 2 How to create database model Jar ile sireriioreoriene r s nn ATEAN E A 55 1 GEOSSARY sasssa EEE EE aarsiaes 64 SpagoBI QuickStart ver 0 8 May 17 2006 pag 3 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit ae ee a ee http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Version Version Release n 0 6 Data Version Release December 14th 2005 Update description First release Draft Version Release n 0 7 Data Version Release January 31st 2006 Update description Second release Draft Version Release n 0 8 Data Version Release May 17th 2006 Update description Third release Draft Added paragraph on datamart jar creation 1 Document goal The document aim is to introduce the
46. el a name a description and a type report OLAP etc Development Environment DEVELOPMENT OBJECTS LIST LABEL NAME DESCRIPTION Best5Prod Month Sals Best 5 Products for Monthly Sales Cob BestProdDailySaks BestProdDailySalTab BestProdMonthSalTab BudgSatisf EmpOccDegree Incid Cost OLAP PROD COS 01 ProdProfic RPT CUST PRF 01 Best Products for Daily Saks Lines Best Products for Daily Saks Tab Best Products for Monthly Saks Tab Budget satisfation Emp occupation degree Incidence of Costs Product Cost 01 Product Cost and Sales Olap Analysis Products profitability Customer Profi 01 Report about Customer detais BRR KRRRERE RE a page i of 3 The value of the column LABEL bd starts with v Fitter All Figure 26 Analytical documents list The Documents Details page displays a complete list of attributes Label document unique identifier Name document name Description brief description of the document optional Type document type report OLAP Data mining model Dashboard etc Engine engine that will be used to execute the document The available engines are registered by the administrator see Engines Configuration paragraph e Template file containing the model of the document It has to be created with an external application suitable for the specific type of Analytical Document When the developer inserts a new document he has to indicate the Parent folder under which the document wil
47. ent user typologies administrator developer tester end user Each user is assigned to a specific typology by the Portal Administrator L3 For a better understanding of the user typologies refer to the analytical Document life cycle section 6 2 ANALYTICAL DOCUMENT LIFE CYCLE Every SpagoBI document usually follows a three steps life cycle 1 Development this is the proper state of every document that has to be developed corrected modified or improved and therefore it is the initial state of every new document 2 Test it is the state of a document which has to be tested in order to check if it works correctly returning the requested result for each possible configuration 3 Released this is the state of a document that has been properly developed and tested and can be employed by the final user Moreover a 4 state Suspended can be assigned to a document that will not be used anymore Referring to this life cycle SpagoBI users can have a specific function which is assigned by the portal administrator Users can be classified in 4 different typologies 1 Administrator he deals with configuration and security aspects 2 Developer this type of user can create or modify documents 3 Tester he takes the responsibility to verify the formal correctness of the registered documents and if they fulfil the requirements 4 User he can use all the business objects in a released state according to his role and with the moda
48. eport Engine in Development Context REP JASP DEV Jasper Report Dev Jasper Report Engine for static reporting in Develbpment Environment REP JASP REL Jasper Report Rel Jasper Report Engine for static reporting in Release Environment a page 1 of 7 The value of the column LABEL starts with Filter All PHK KKKK Fig 10 Engines list Each engine is depicted by a unique label a name and a brief description The administrator can create a new engine erase an existing one or access their details page in order to change their configuration The information required for each engine are Label engine unique identifier Name engine name Description brief engine description optional URL location where the engine can be accessed by the server Driver Name class that creates an URL compliant with the specific engine Description asper Report Engine for static reporting in Release E Url htfpu localhost 8080 SpagoBlJaspe rReportEngine ast Driver Name lie ng spaqo bLo rivers jaspe rrepo rh Jaspe rReportD rive Fig 11 Engine details SpagoBI QuickStart ver 0 8 May 17 2006 pag 15 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XSpageB S p a g O B l Q u i C k S t a r
49. et and the List of Value for the movie values feeding 5 4 3 REGISTER THE ANALYTICAL DOCUMENT THE BUILT DASHBOARD INTO THE PLATFORM 1 From the Developer Tools page select the Documents Configuration in order to display the Development Object List 2 Select the icon to create a new Analytical Document 3 In new Document Details page you will be required to fill in the following information Label DASHBOARD Document Name DASHBOARD Document Description Type Dashboard Template click on the browse button to select the template created in the previews paragraph 5 Moreover you have to indicate the parent folder of the document selecting the check box corresponding to Dashboard located as child of Analytical Area in the Functionality Tree on the right hand side of the page 6 To save and exit from this page click on the Save AND RETURN icon Now that the document has been correctly created and configured on the Analytical Document you can execute it 7 Click on the icon ExEcuTE which corresponds to the new DASHBOARD Document 8 Now it is possible to Update State by clicking on the corresponding icon at the top of the window Notice that once updated the DASHBOARD Document will not be listed in the Development Object List anymore 9 Logout SpagoBI QuickStart ver 0 8 May 17 2006 pag 50 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Common
50. execute it 10 Click on the icon EXECUTE which corresponds to the new OLAP Document 11 In the new page you will be required to enter a value for the OLAP Doc Param 12 Select Food from the combo box 13 Click on the EXECUTE icon to execute the document 14 Now it is possible to Update State by clicking on the corresponding icon at the bottom of the window Notice that once updated the OLAP Document will not be listed in the Development Object List anymore 15 Logout 5 3 4 TEST THE ANALYTICAL DOCUMENT amp y Work in progress 5 3 5 EXECUTE THE ANALYTICAL DOCUMENT amp y Work in progress 5 4 DASHBOARD This following example is designed to quick introduce new users to create a new DASHBOARD in SpagoBI starting from a SWF movie already created with OpenLazslo In this document we will not explain how to build a SWF movie with OpenLazslo we invite you to visit the OpenLazslo home page www openlaszlo org for more information SpagoBI is released with some SWF movies you can find them in the folder webapps spagobi dashboards inside Exo Tomcat main directory For this example we will consider rot zx swf For the source code and documentation about SpagoBI movies download the pace SpagoBl Components from http Obj A Dashboard element must be fed with the values to be displayed these values are supplied by a predefined List of Value SpagoBI QuickStart ver 0 8 May 17 2006 pag 48 of 64 Engineering Inge
51. geB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform e Select Fields to choose the select fields e Conditions to set the where conditions e Ordering to select the fields for the order by e Grouping to select the fields for the group by e View Query to display the query realized following the wizard or to write a proper query using HSQL language e Save Query to verify if the query is formally correct and to save it e Result Preview to display a preview of the results obtained by the query realized FOODMART DATAMART FOODMART DATAMART QUERY COMPOSITION FIELD SELECTION 5 Y Foodmart Datamart O 47A Annual Sales Fact Name Name H O Customer OAY Annual Sales Fact Surname Surname H O Promotion BATA Annual Sales Fact City gt City H O Product Class ZF Annual Sales Fact State Province gt State Province H O Store OAY Annual Sales Fact Birthdate gt Birthdate H Product J sum Annual Sales Fact aSalesFact1997 id storeSales gt id storeSales EMED Annual Salas j O 7 sum Annual Sales Fact aSalesFact1997 id storeCost c gt id storeCost paligas akc O 7 avalAnnual Sales Fact aSalesFact1997 id unitSales id unitSales Time Identifier distinct amp Customer Identifier amp Promotion Identifier amp Store Identifier gt id storeSales gt id storeCost id unitSales H O Promotion O customer amp Customer Identifier Account
52. ght side every time the developer uploads a new template it will be set as the current default and will be added to this list specifying the version identifier the date when this selection occurred first and the file name By means of this view the user will always be able to erase download or select one of the listed templates e Parameters tabs bottom side there is one tab for each parameter associated at the Analytical document An additional tab New creating a new association is provided Development Environment DOCUMENT DETAILS N Fri Nov 25 18 13 34 CET 2005 Top_10_store jrxml Download zase Jowrioad Description Besi Stores in Month Thu Oct 20 14 15 11 CEST 2005 Top 10 store jrxml Mon Oct 10 16 43 36 CEST 2005 Top_10_store jrxml pase Downoad Mon Oct 10 16 31 53 CEST 2005 Top 10 store jrxml pase Downoad aig ec Wed Sep 28 16 29 53 CEST 2005 Top _10 store jrxml pase Downoad Template _Sfoglia 6 Tue Sep 27 09 02 51 CEST 2005 Top _10 store jmml Type Report month Number Top Output New DOCUMENT PARAMETER DETAILS Title Inonth Parameter Month Url Name ParMonth SpagoBI QuickStart ver 0 8 May 17 2006 pag 26 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit a ee ee http creativecommons org licenses by nc sa 2 0 XS p
53. gneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform The main steps to manage a dashboard are 1 Create the Lists of Value for movie feeding 2 Create a Template for movie configuration 3 Register the Analytical Document the built DASHBOARD into the platform a Add Template 4 Test the Analytical Document 5 Execute the Analytical Document 5 4 1 CREATE A LIST OF VALUE 1 The Predefined List of Values page can be accessed by selecting the corresponding link from the Developer tools 2 Click on the INSERT icon to add the new desired element 3 In the Predefined List of Values Details page fill in the following information Label DASHBOARD SCRIPT Name DASHBOARD SCRIPT Description Input Type Script Script StringBuffer buf new StringBuffer buf append lt rows gt buf append lt row value Random rand new Random float f 80 rand nextFloat 100 15 buf append f buf append gt buf append lt rows gt return buf e Output Single Value The above script will only return a random value 4 When the data entry is completed click on the SAVE icon to save the information and e
54. goBI QuickStart ver 0 8 May 17 2006 pag 18 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0 COM MON S D EED XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform 4 2 DEVELOPER The developer s role is quite complex This section explains its functionalities In the chapter 5 you can find an example showing how the single functionalities work together for the right environment settings The developer bidev bidev user main tasks are e to define the possible presentation and the preloading way LOV list of values for the parameters e to define the validation rules CHECK for the input value e to create the parameters PARAMETER and to set up their behaviour rules associating LOV and CHECK to the user s roles e to register and to configure each analytical document referring to the used parameters Development Environment DEVELOPER TOOLS Predefined List of Values ABD Predefined Values Constraints ve Parameters Management Documents Configuration Fig 16 Developer tools Notice that the developer manages the executive configuration of the platform and only a few structural configuration Notice that at the start up you have to create many LOV CHECK
55. he main steps to manage a report are 1 Create a Template 2 Create Parameters a Create Lists of Value b Create Constraints 3 Register the Analytical Document the built OLAP into the platform a Add Template b Assign Parameters 4 Test the Analytical Document 5 Execute the Analytical Document SpagoBI QuickStart ver 0 8 May 17 2006 pag 43 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t Y the Business Intelligence Free Platform 5 3 1 CREATE A TEMPLATE In order to create a proper template for an OLAP document it is only necessary to realize an XML file containing the following elements e lt olap gt root element e lt connection gt logical name that will be interpreted by the engine to identify the proper connection e lt cube gt the reference attribute of this element identifies the XML file that describes the datamart cube using the Mondrian syntax e lt MDXquery gt the text of this element is a query executed on the datamart written in the MDX syntax e lt parameter gt this element contained in the lt MDXquery gt identifies a parameter for the query The name attribute refers to the parameter name contained in the query while
56. igence Free Platform Analytical document Qa Qa 0a 0a Qa Qa 0a I dis Dashboard QbE Query by Example Structural configuration Executive configuration CMS data template version and location in Parameters the functional tree Engine and Driver Fig 2 Analytical documents support Regarding to the structural configuration every analytical documents category refers to a particular one or many execution engine interacting with SpagoBI through a specific driver For every analytical document SpagoBI keeps the history of the templating version and organize them in the functional tree The SpagoBI demo has got a preloaded metadata environment allowing to manage report on the JasperReport engine OLAP on the Mondrian engine with Jpivot interface Dashboard on the OpenLazslo engine free inquiry on a Hibernate implementation The Dashboard implementation by means of OpenLaszlo compiler is under a phase of greater integration through ag fo Data Mining integration is a planned activity Many other alternative engines in every analytical area will be integrated in the SpagoBI platform in the future Regarding to the executive configuration SpagoBI manages some parameters as autonomous and independent entities The parameters include the behaviour rules presentation and validation according to the end user roles Through the parameters SpagoBI
57. ineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit COM MON S D E ED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform e decimal decimal digits control e max length maximum number of characters for the inserted values The developer can select a Check Type from the list and filling in the required values Each constraint can have just one Check Type 4 2 3 PARAMETERS MANAGEMENT SpagoBI handles the parameters in term of autonomous entities each one with its own behaviour based on users roles So it is possible to associate to them different presentation LOV and validation CHECK rules The list view allows the developer to add new parameters to erase the existing ones or to access their detailed information in order to modify them Development Environment PARAMETERS LIST yh 3 LABEL NAME DESCRIPTION TYPE N USE MODES CITY_CD City Code Parameter for City Code Selection STRING COUNTRY_CD Country Code Parameter for Country Code selection STRING DEPAR_DESC Department Descrption Parameter for Department Descrption Selection STRING EMPPOS_CD Empbyment Position Code Parameter for Emplbyment Position selection STRING GENDER_CD Gender Code Parameter for Geder Code setction STRING HIB DIALECT
58. iness Intelligence Free Platform SpagoBI QuickStart Figure 38 QbE Condition On the left hand side of the window the logic view of the datamart model is displayed Notice that to help the user the Qbe will display only the clauses that contain a select field This is called Light Tree To display the complete datamart tree the user can simply click on the FULL TREE icon Once selected a field on the right hand side of the page the user can complete the where condition selecting the proper operator adding the right condition in the text area and choosing the logic operator that will be set before the following condition Annual Sales Fact Store Name gt E ano gt ig FoodMart E Annual Sales Fact ea i Bag Og a E E E ea a gE a aS W Food Mart ao Annual Saks Fact Store Name H Store 7 t ano f ce Customer SS aes E EA EA EA H Promotion P FoodMart H Product Cass H 0 Customer ae Store H Promotion a Product a Product Class H Annual Saks Fact HO Store H Product H Annual Sales Fact Figure 39 QbE Where condition Notice that the right part of the where condition can also be a field in this case the user can simply click on the TREE icon and select the desired field Notice that the datamart tree displayed is the same shown to choose the left condition Both the Ordering and the Grouping page display on the left hand side a list of the select fields of the query
59. ink Eyster BirthDate 22 02 1344 02 07 1946 23 08 1979 16 04 1974 06 10 1974 20 03 1928 08 07 1912 22 04 1920 04 02 1954 10 07 1923 Address 5927 Mt Hood Circ 7653 Millfield Place S652 Los Angeles 591 Merrewood Drive 4096 San Remo epar Ridge Road S074 Ardith Drive 4698 Royal Oak Ad 1648 Eastgate Lane 2639 Anchor Court page 1 of 8 City Fremont Beverly Hills sedra Wooley spring Valey Woodland Hile Aradia Lincoln Acres San Francisco Albany Ed mands g s 8 88 8 4 84 8 The value of the column Name starts with Filter All SpagoBI QuickStart ver 0 8 May 17 2006 pag 29 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit COM MON S D E ED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Figure 33 Lookup for parameter input value The list pages can be turn over using the two arrows on the bottom row The desired value can be selected by clicking on the corresponding icon Moreover to ease the user to find the required data it is possible to filter the list When the parameter selection is completed the user can execute the document by clicking on the icon Business Intelligence Documents Single Customer profile Single Cust
60. ion NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XSpageB S p a g O B l Q u i C k S t a r t TY the Business Intelligence Free Platform create two project subfolders called src and bin enter the project properties and select the Java Build Path menu Into the properties window set the src folder as source folder and the bin folder as output folder Figure 6 3 Properties for DatamartJar a type filter text Java Build Path Info Builders 2B Source E Projects h Libraries Ta Order and Export gt Deployment Source Folders on build path Java Build Path a Java Code Style 28 DatamartJar src Add Folder Java Compiler H Javadoc Location Project References Task Tags Remove Malidation T Allow output folders For source Folders DeFaulk output Folder DatamartJar bin Browse cana Figure 6 Java project build path options create a new Hibernate cfg file into the src folder of the project right click on the project and then select the new other hibernate hibernate cfg file menu item Into the properties window choose the right dialect for your database insert the driver class name the connection string user and password Remember to check the create console configuration option and then press the next button Figures 7 8 C
61. ion i i H Customer counti H Product i i Ghose l i i aa Store Figure 37 QbE Field Selection detail Once selected a field it appears on the right hand side The following option are available to erase the corresponding selection to move up or down the field in the list to set the order of the columns of the result table 3 to apply a group operator on a field by selecting the proper one from the list Gf to remove a group operator z to edit an alias name that will be displayed as header of the column corresponding to the selected field in the result table Moreover the distinct option can be set by clicking on the corresponding check below the selected fields 4 4 1 2 Condition ij FoodMart A Full Tree 8 Annual Sales Fact Product Identifier Time Identifier Customer Identifier pe Promotion Identifier Store Identifier gt Store Sales Store Costs Unit Sales C Promotion O Customer Product H O Store Annual Saks Fact Store Name z 4 FoodMart E Annual Sales Fact L SpagoBI QuickStart ver 0 8 May 17 2006 pag 33 of 64 greative commons COM MON S D EED Engineering Ingegneria Informatica S p A 2005 This work is licensed under the Creative Commons Attribution NonCommercial ShareAlike License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0 XS pageB Y the Bus
62. ion of the analysis The modules which allow the greater freedom degree are e QbE the user can produce and save in the repository its own interrogations e OLAP the user can freely reorient his data model saving his more interesting view hy To be continued 4 4 1 QBE QUERY By EXAMPLE Query By Example is a SpagoBI tool realized in order to ease the user to create simple queries through a graphical interface b Notice that this feature is in a RC release candidate state By clicking on the relative menu item a list of the QbE queries is displayed Foodmart Datamart Foodmart Datamart The document has no parameters applied For launch a new composition click here Saved SubBlObjects Name Description Vis ibility aY s501 Query for customer saes Public az ak Figure 35 QbE Query list On the lower side of the window the user can choose to modify an existing query by clicking on the corresponding EXECUTE icon or to create a new one by selecting the here link at the top of the window The composition process is scheduled into seven steps that will ease the user to create a new query SpagoBI QuickStart ver 0 8 May 17 2006 pag 31 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pa
63. is necessary to navigate to the temp data databases directory of Exo Tomcat and type start bat for Windows or start sh for Unix remember to check if you have the required permission for execution Then at the command shell navigate to the bin directory of Exo Tomcat and type startup bat for Windows or startup sh for Unix You can read the log file by typing tail f logs catalina out 5 2 REPORT The main steps to manage a report are 1 Create a Report Template using IReport 2 Create Parameters a Create Lists of Value b Create Constraints 3 Register the Analytical Document the built report into the platform a Add Template b Assign Parameters 4 Test the Analytical Document 5 Execute the Analytical Document b Notice that JasperReport is the first report engine chosen but it is not the only one allowed Similarly iReport is the first chosen interface for JasperReport engine but other solutions also exist and the developer can use what he prefers in order to produce the report template SpagoBI QuickStart ver 0 8 May 17 2006 pag 37 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t Y the Business Intelligence Free Platform 5 2 1
64. it is necessary to have specific rights which can only be assigned by the administrator For a better understanding of the Security Policy please refer to next paragraph 6 3 USER ROLES Every user is characterized by one or more functional roles SpagoBI manages users by their functional roles in order to regulate e the analytical documents visibility e the visibility of the data shown by documents e the behaviour rules of their parameters and the filters 6 4 DOCUMENT ORGANIZATION AND SECURITY POLICY SpagoBI sorts documents in a Functionality Tree which is a File System that can be modified only by an administrator user SpagoBI QuickStart ver 0 8 May 17 2006 pag 53 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 SpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform W Objects Tree Functionalities Areas C Business Departments Eig Analytical Areas Static Reporting l Dimensional Analysis iD Product Cost 01 id Product Analysis 02 Hi Product analysis 03 if caso 7 Data Mining Models 7 Dashboards Figure 44 Functionalities Tree This allows to better organize documents grouping them by folders and to realize a Security Polic
65. k is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform y Hibernate Code Generation H Create manage and run configurations Select or configure a code generation Configurations Mame DatamartGen Eb Hibernate Code Genere azr Datamartoen Tam E man s Exporters Es Refresh B Common IW Generate domain code tjaa IDK 1 5 Constructs generics bc 5 EIBS I5R 220 annotations M Generate DAO code java IM Generate mappings thbr sral IV Generate hibernate configuration thibernate ctg smli Generate schema html documentation Generate JBoss Seam skeleton app beta Apply Revert cose Figure 12 Hibernate code generation exporters options At the end Press the Apply button and then the Run Button After the end of the execution into the src folder of the project there will be a set of java and hibernate mapping files contained into the right package folder structure See figure 13 If the database is well defined with all the appropriate primary and foreign keys there s no need to change the code produced but if some problem occurs or you are an hibernate expert and you want to personalize the code it obviously possible to edit
66. ke commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 A SpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform According to the selected typology in the bottom of the page a wizard is available in order to ease its specific composition In the Query Statement case the picture example above the required information are o Connection Name logic identifier of the database source o Query Definition the SQL statement o Value Column name of the unique column of the dataset containing values that will be return as a result of the parameter o Visible Columns name of the columns of the dataset that will be displayed r New item nam i In the Fixed Values case you can Mmmm create a table of pairs Name Value ewitemvaue simply by filling the New item name and the New item value fields and name TR clicking on the ADD icon In Order tO Non Consumabe Product All Products Non Consumabk at erase an existing pair you can use the kod Product All Products Food m icon on the corresponding row of the brink Product All Products Drink ag list at the bottom of the window Figure 19 Fixed values wizard Script stringBuffer buf new StringBuffer In the Script case you have to write buf append lt rows gt i buf append lt row value the Groovy script to be executed at encom and
67. l be created this can be set by selecting the corresponding check box on the Functionality Tree displayed on the right hand side of the window SpagoBI QuickStart ver 0 8 May 17 2006 pag 25 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed greative under the Creative Commons Attribution NonCommercial ShareAlike common License To view a copy of this license visit Ae Oe O eee a D http creativecommons org licenses by nc sa 2 0 XSpageB S p a g o B Q u i c k S t a rt the Business Intelligence Free Platform The developer can see only the details of the documents that are in Development state so the State field visible by the administrator is hidden Development Environment DOCUMENT DETAILS Label P Functionalities Tree Name 4 AS Functionalities Areas oo _j Business Departments Description i i Ag E Analytical Areas Type Report E Static Reporting Engine Jasper Report Rel E Dimensional Analysis Template Sfoglia l E J Data Mining Models E 9 Dashboard cd i _ Queries Figure 27 Analytical document details new document An Multiple selection is allowed but at the moment is not implemented When the developer sees an existing document and selects the DETAILS icon additional elements will be displayed e Detailed information Top left side already explained data they are the same view of the document creation phase e Version template list Top ri
68. lities previously defined in the parameters configuration The User is characterized by his functional roles which regulates e the analytical documents visibility e the visibility of the data shown by documents e the behaviour rules of their parameters and the filters It is very important to notice that administrators developers and testers are also users and therefore they can act as specialised users with additional functions SpagoBI QuickStart ver 0 8 May 17 2006 pag 52 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0 COM MON S D EED XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Figure 43 User type hierarchy Every user will access a specialized main page that will contain specific tools When completed his own phase a Developer can update the document state to Test while a Tester referring to test results can change it to Development or to Released The administrator is the only one who can modify a document state without any constraints allowing extraordinary maintenance of the documents Notice that the simple user cannot modify the document state Finally it is important to observe that in order to develop test or execute a particular document
69. m the correspondent list view Business Intelligence Documents EXECUTION OBJECTS LIST LABEL NAME DESCRIPTION TYPE FOODMART_DATAMART Foodmart Datamart Foodmart Datamart DATAMART OLAP PROD 02 Product Analysis 02 Online analysis about product sals OLAP OLAP PROD 03 Product analysts 03 Online analysis about product sales OLAP RPT_CUST_PRF_02 Customer profile Customer profile REPORT RPT_CUST_PRF_FX Single Customer Single Customer REPORT RPT_EMPPOS_02 Empbyment position Emphbyment position by role REPORT RPT_EMP_STORE_02 Emplbyee in store Employee in store REPORT RPT_PROD_PRF_02 Product profi 02 Product detail about sales and data REPORT RPT_SALES_CITY_02 Saks by city Month sales for product and city REPORT T_CST_PRF Single Customer profik Single Customer profi REPORT a page 1 of 2 The value of the column LABEL starts with 7 Filter All Figure 30 Tester s documents list If the selected document is in a Test state with a parameter associated to different Use Modes the user has to choose a role from the Role combo box in the Select Role For Execution page Business Intelligence Documents SELECT ROLE FOR EXECUTION Role fspagabifadmin Figure 31 Role selection page Notice that the list includes all the system roles available for the document and not only the ones belonging to the Tester This means that the Tester will be able to test the different behaviour of the Analytical Document in relation to every user
70. nCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t Y the Business Intelligence Free Platform Help for lecture Follows a short description of the most common views in SpagoBI 1 1 STYLISTIC CONVENTIONS LITTLE CAPITALS The LITTLE CAPITALS references to the icon in a mask italics The italics refers to fields of the masks lt ITALIC CAPITALS gt In lt ITALIC CAPITALS gt the logical variables are Suitable boldface In boldface the main concepts 1 2 SPECIAL SECTIONS b Note Example D p g Reference to other section CQ In revision phase aK A Future implementation To be done Advice for the reading of the section SpagoBI QuickStart ver 0 8 May 17 2006 pag 5 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t Y the Business Intelligence Free Platform 2 SpagoBl Conceptual overview SpagoBI is a platform for the development of Business Intelligence projects SpagoBI offers all the tools and the necessary components for the realization of analytical portals whose designing and setting
71. omer profile gt Development lo Customer profile Name Albright Gender M Marital Status M City Fremont Posta Code 66160 County USA Address 5927 Mt Hood Circle Education High School Occupation Skilled Manual income 30K 50K St Sales Unit Sales St Costs Month St Sales Unit Sales St Costs 24 76 40 5 24 September 16 06 5 5 64 3 78 4 1 66 October 9 22 3 3 25 53 15 29 14 33 December 63 96 29 17 16 18 34 3 5 00 18 23 3 7 11 Aes An Anen Figure 34 Final execution Now the test user can update the document state to Release if all the required tests worked correctly otherwise to Development Moreover he can click on the BACK icon in order to execute a different test using a different role 4 4 END USER The end user biuser biuser user works with the Analytical Portal made by the Business Intelligence designer and developer The on line demo is an example of an analytical portal whose purpose is to let you see the basic elements for the building of your analytical portal For example from the menu of the Demo portal you can access many sections e Home many portlets which combine punctual views of the performance indicators with synthetic reports e Navigation a free navigation on the functional tree of the documents e Instanced Reports a page divided into subsections for the presentation of single reports already instanced not parametrics e Parametric Reports a page divided into subsections for the parametric
72. onfiguration in order to display the Development Object List 2 To create a new Analytical Document it is necessary to select the INSERT icon 3 In new Document Details page you have to fill in the following information SpagoBI QuickStart ver 0 8 May 17 2006 pag 41 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a http creativecommons org licenses by nc sa 2 0 XS pageB S p a g Oo B Q u i c k S t a rt the Business Intelligence Free Platform e Label Report Document e Name Report Document e Description e Type Report e Engine Jasper Report Dev e Template click on the browse button to select the report template created in the Create a Report template using Report paragraph 4 Moreover it is necessary to indicate which is the parent folder of the document by selecting the check box corresponding to Static Reporting located as child of Analytical Area in the Functionality Tree on the right hand side of the page 5 To save and exit from this page click on the SAvE AND RETURN icon 6 The Development Objects List will be updated with a new row containing the document just created 7 Now you can access the Document Details page simply by clicking on the DETAILS icon on the row of the new document This page will list the general det
73. oper executing a document associated to this parameter will use this specific Use Mode Then click on LOV LOOKUP icon in the Lov lookup list page now displayed select Report LOV QUERY from the table listing all Predefined List of Values the filter will help you to find it soon From the Predefined Values Constraints table don t select any constraints 6 Now click on the SAVE icon It can be useful to add another Use Mode in order to understand the roles management performed by Parameters 7 Click on New tab and a new empty Parameter Use Mode Details section will appear 8 Fill in the following information e Label Report Use Mode 2 e Name Report Use Mode 2 e Description Then click on LOV LOOKUP icon in the Lov lookup list page now displayed select Report LOV FIX_LOV from the table Select the Report Constraint from the Predefined Values Constraints list Notice that in the Role Association table the spagobi admin and spagobi dev cannot be selected In fact each role can be matched at most to one Use Mode Check the spagobi biuser 9 Click on the SAvE AND RETURN icon to go back to the Parameter List The Report Parameter will be now displayed in the list Notice that the Number of Use Modes should be 2 Then click again on the BACK icon to reach the Developer Tools 5 2 3 REGISTER THE ANALYTICAL DOCUMENT THE BUILT REPORT INTO THE PLATFORM 1 From the Developer Tools page select the Documents C
74. page 2 Click on the INSERT icon and open the Parameter Details page 3 Insert the following information Label OLAP Parameter Name OLAP Parameter Description Type String 4 Click on the SAVE icon An empty Parameter Use Mode Details section will be displayed Enter the following information e Label OLAP Use Mode 1 e Name OLAP Use Mode 1 e Description In the Role Association table select spagobi admin and spagobi dev The Administrator or the Developer executing a document associated to this parameter will use this specific Use Mode Then select OLAP LOV FIX_LOV 1 from the lookup page listing all Predefined List of Values From the Predefined Values Constraints table don t select any constraints 5 Now click on the SAVE icon It can be useful to add another Use Mode in order to understand the roles management performed by Parameters 6 Click on the Wew tab and add the following information e Label OLAP Use Mode 2 e Name OLAP Use Mode 2 e Description Then select OLAP LOV FIX_LOV 2 from the lookup page listing all Predefined List of Values This time select the OLAP Constraint from the Predefined Values Constraints list Notice that in the Role Association table the spagobi admin and spagobi dev cannot be selected In fact each role can be matched at most to one Use Mode This time check the spagobi biuser SpagoBI QuickStart ver 0 8 May 17 2006 pag 46 of 64 Enginee
75. porting x Label REPORT Roles Development Test Execution spagobi admin The spagobi admin group v v v spagobi dey The spagobi developers group spagobi test The spagobi testers group spagobi user The spagobi users group spagobi The spagobi developers group Fig 13 Functionality details SpagoBI QuickStart ver 0 8 May 17 2006 pag 16 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 MS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Each folder is characterized by a name a unique code and a optional brief description The list of the Ro es allows the administrator to choose for the selected functionality whether to assign or remove the permissions required for the development test or execution phase of each role For instance checking the Development and the Test boxes of the SpagoBI user group all the users logged as biuser will only be able to develop and test documents belonging to the selected functionality but not to execute them ES Notice that every new node inherits by default all its father s rights 4 1 3 DOCUMENT CONFIGURATION A list of all the Analytical Documents registered in SpagoBI can be listed
76. reate Hibernate Configuration file cfg xml Create a new hibernate cfg sml Enter or select the parent Folder Datamartilar sre I ta Dakamarkiar oe Bin gt src File name hibernate ctg ml lt Back Hext nish Cancel Figure 7 New Hibernate configuration file SpagoBI QuickStart ver 0 8 May 17 2006 pag 58 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Hibernate Configuration File cfg xml This Wizard creates a new configuration File to use with Hibernate Container t atamartilar src File name hibernate cfg xml Session Factory name Database dialect PostgresOL E Driver class org postgresql Driver Connection URL jdbc postgresqli fserver port schema Default Schema l e M U M I Default Catalog a Username database username sss Password database password gt S M Create a console configuration Cancel Figure 8 Hibernate configuration properties Give a name to the console add the jar of the database driver into the classpath and then press the finish button Figure 9 Create Hibernate Console Configuration
77. reate a new java project give it a name and maintain the default properties Look at figures 3 4 5 New Project 4 Select a wizard Create a Java project Wizards ERSE Ob m i reed ree i Package i Java Project From Existing Ant Buildfile oi ee oe A ae F i Class ig Plug in Project i CVS gy Import Gy Interface 3 0 ava Enum 4 Export G HE JBossAOP Bi E Annotation E JBoss IDE 2 Refresh x EE JBoss jBPM g pg fa Source Folder E Plug in Development Folder EE Simple gt Examples F File Untitled Text File E JUnit Test Case ie F3 Example Pr Other Cr Figures 3 4 new java project New Java Project Create a Java project Create 4 Java project in the workspace or in an external location Project name DatamartJar 7 Contents Create new project in workspace f Create project From existing source IBE Compliance Use default compiler compliance Currently 1 45 Configure default Use a project specific compliance Projeck layout Use project folder as root for sources and class Files C Create separate source and output Folders Configure default lt Back Pexck gt Cancel Figure 5 Default properties of a java project SpagoBI QuickStart ver 0 8 May 17 2006 pag 57 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribut
78. ring Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform 7 Click on the Save AND RETURN icon to go back to the Parameters List The OLAP Parameter Example will be now displayed in the list Notice that the Number of Use Modes should be 2 8 Then click again on the Back icon to reach the Developer Tools 5 3 3 REGISTER THE ANALYTICAL DOCUMENT THE BUILT OLAP INTO THE PLATFORM 1 From the Developer Tools page select the Documents Configuration in order to display the Development Object List 2 Select the icon to create a new Analytical Document 3 In new Document Details page you will be required to fill in the following information Label OLAP Document Name OLAP Document Description Type On line analytical processing Engine Jpivot Mondrian Dev Template click on the browse button to select the template created in the paragraph 4 Moreover you have to indicate the parent folder of the document selecting the check box corresponding to Dimensional Analysis located as child of Analytical Area in the Functionality Tree on the right hand side of the page 5 To save and exit from this page click on the SAvE AND RETURN icon
79. ry Customer 0a v of Product Department c Product Department H O Promotion Aa v of Product Family gt Product Family T Product Class Aa v of Product Subcategory c Product Subcategory Product Class Identifier of sum Store Sales Store Sales Product Subcategory a v of sum Store Sales Store Sales Product Category a v of sum Store Costs c gt Store Costs Product Department of sum Unit Sales ap ing cae 22 ict Family D distinc O Product H O annual Sales Fact Dashboard QbE Query by Example Fig 1 Analytical documents The Business Intelligence analyst and designer have to find the most suitable tool for every type of analysis and category of user In fact the building of an analytical portal is a balanced composition of different tools in order to give to each users category the right degree of visibility and at the same time a freedom of movement through the information of his pertinence SpagoBI realizes both the structural and the executive support to the single analytical areas SpagoBI QuickStart ver 0 8 May 17 2006 pag 6 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XSpageB S p a g O B l Q u i C k S t a r t tY the Business Intell
80. s Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t Y the Business Intelligence Free Platform 5 4 4 TEST THE ANALYTICAL DOCUMENT amp y Work in progress 5 4 5 EXECUTE THE ANALYTICAL DOCUMENT amp y Work in progress 5 5 DATA MINING a Work in progress 5 6 QUERY By EXAMPLE amp y Work in progress 6 In more depth 6 1 PORTAL ADMINISTRATOR AND PORTLETS ORGANIZATION Portlets are autonomous and independent application windows They are freely usable inside portal contexts supporting the JSR 168 specification by means of a simple configuration No development is necessary Every function in SpagoBI runs in portlets included into a corporate portal or into a particular Business Intelligence environment The portlet organization into the portal is realized by the Portal Administrator SpagoBI QuickStart ver 0 8 May 17 2006 pag 51 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae ee Oe EED http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform SpagoBI releases specialized portlets according to the differ
81. s and fill the reveng strategy field with a org hibernate cfg reveng DelegatingReverseEngineeringStrategy value Figure 11 7 Hibernate Code Generation Create manage and run configurations Select or configure a code generation Configurations Name DakamartGen E Hibernate Code Genere 6 DatarmartGen Main t Exporters wn Refresh Bet Comman Console configuration DatamartJarConsole Qutpuk directory DatamartJar sre Browse iv Reverse engineer From JDBC Connection Gg Package ik eng fib datarnart reveng xmi Setup reveng strategy org hibernate cfg reveng DelegatingReverseEngineeringStrateqy i Browse Iv Generate basic typed composite ids Use custom templates GS Delete Apply Revert ee Figure 11 Hibernate code generation main options If you want to filter the tables to process for the generation click on the setup button and create a new reverse engineering file Into the new window select the refresh button and wait until the schema of the database is showed into the left box From the database schema select the table you want to include and then press the include button All the tables in the right box will be processed by the tool Press the Exporters Tab and check the three options as in the figure 12 SpagoBI QuickStart ver 0 8 May 17 2006 pag 60 of 64 Engineering Ingegneria Informatica S p A 2005 This wor
82. s based on hibernate technology and it presents a database schema as a group of related visual objects Each database schema is modelled by a set of java classes and hibernate mapping files all packaged in a jar file In order to insert a new database model and to inquire it with SpagoBI it s necessary to define anew datamart object This kind of object doesn t need an engine and as template it takes the jar containing the hibernate class and mapping files Since qbe is based on hibernate it needs also to know the kind of the target database in order to use the right sql dialect and to connect to it To satisfy the two previous requirement it s mandatory to define two parameters for each datamart object e one parameter with url_name equals to JNDI_DS which must be filled with the jndi name of a datasource for the target database Qbe will use it to get the connection for the inquiry e one parameter with url_name equals to DIALECT which must be filled with the complete hibernate dialect class name suitable for the target database Qbe will use it to choose the right sql dialect for the inquiry 6 9 2 HOW TO CREATE DATABASE MODEL JAR FILE Since a database model jar is composed by a set of hibernate mapping files and relative java classes it s possible to create it manually only using java and xml editors However this operation can be too long and error prone so it s better to use some automatic tools like HibernateTools 3 1 To get th
83. t Constraint e Name Report Constraint e Desription 4 Select MAXLENGHT as Check Type and type 23 in the corresponding text field 5 When completed click on the SAVE icon saving the information and exit to the previous page then select the BACK icon to go to the Developer Tools page 5 2 2 3 Parameters Management When the required LOV and CHECK are created a new Parameter can be created too 1 Enter the Parameter List page by selecting Parameters Management from the Developer Tools page 2 Click on the INSERT icon and open the Parameter Details page Insert the following information e Label Report Parameter e Name Report Parameter e Description e Type String SpagoBI QuickStart ver 0 8 May 17 2006 pag 40 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Click on the SAVE icon and then a new empty Parameter Use Mode Details section will be displayed on the bottom part of the page Enter the following information e Label Report Use Mode 1 e Name Report Use Mode 1 e Description In the Role Association table select spagobi admin and spagobi dev The Administrator or Devel
84. t be associated to more than one Use Mode anyway a role would not be associated to any SpagoBI QuickStart ver 0 8 May 17 2006 pag 24 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB Y the Business Intelligence Free Platform SpagoBl QuickStart Parameter Use Mode In the second case a user having just that role will not be able to use a document related to this Parameter e Predefined Values Contraints it is possible to assign zero one or more constraints selecting them from the list of available constraints To create a new Predefined Values Contraints please refer to the Predefined Values Constraints paragraph 4 2 4 DOCUMENT CONFIGURATION The user simply by clicking on Document Configuration from the Developer Tools page can display the Development Object List This page catalogues all the Analytical Documents with a Development state or with a Released state They have to be contained in a folder for which the user has a role authorized respectively for Development and for Execution Please notice that every new document will have the Development state For a better comprehension please refer to the Analytical Document life cycle section Each document is described by a unique lab
85. ted click on the SAVE icon to save the information and exit to the Developer Tools page 5 3 2 2 Predefined Values Constraints 1 The Predefined Values Constraints page can be accessed by selecting the corresponding link from the Developer tools It is divided into two parts on the top side a list of Configurable Constraints is displayed on the bottom the Predefined Constraints 2 Click on the INSERT icon to access the Constraint Details page and create a new constraint 3 Insert the following information e Label OLAP Constraint e Name OLAP Constraint e Desription 4 Select MAXLENGHT as Check Type and type 35 in the corresponding text field SpagoBI QuickStart ver 0 8 May 17 2006 pag 45 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform 5 When completed click on the SAVE icon to save the information and exit to the previous page Then select the Back icon to go to the Developer Tools page 5 3 2 3 Parameters Management When the required LOV and constraints are created a new Parameter can be created too 1 Enter the Parameter List page by selecting Parameters Management from the Developer Tools
86. ter All DESCRIPTION Control if parameter is an Internet Address Control if a parameter is Numeric Control if a parameter is Alfanumeric Control if a parameter is a letter string Control if the parameter is present Control if parameter is a Fiscal Code Control if parameter is a E Mail page 7 of 7 CHECK TYPE DATE DATE MAXLENGTH MAXLENGTH RANGE DECIMALS CHECK TYPE INTERNET ADDRESS NUMERIC ALFANUMERIC LETTERSTRING MANDATORY FISCALCODE EMAIL Figure 21 Configurable Constraints and Predefined Constraints At the bottom of the displayed page there is a list of Predefined Constraints that cannot be modified On the top of the page it is possible to create a set of Configurable constraints simply clicking on the INSERT icon and entering the Constraint Details page The developer can add new configurable controls erase the existing ones or access their detailed information in order to modify them Figure 22 Constraints Details Every constraint is identified by a unique label a name and a brief and optional description In addition to some identification data label name and description it is possible to configure some different control typologies e date date format control e regexp control through regular expressions e min length minimum number of characters for the inserted values e range to control a value included into two limits SpagoBI QuickStart ver 0 8 May 17 2006 pag 22 of 64 Eng
87. the Business Intelligence Free Platform S322 Predefined Values COWS ais sreviseirsevreriisevsnrvinetitdta ven ton Nn TaN aen TaS ENA TEENS EUNATE NENIAN V ETENEE NEENA aE 45 39 2 Parameters Manae mem iresi aekin EREA EE NEARER A E EA E EENE EOE TEREN 46 JI Register the Analytical Document the built OLAP into the platform nooenennnnnnnnnnnnnsnnennnnnsssseennnnss 47 5 3 4 TSE eE ARON Ca DOCUIT airis TAE EE E E E A ENE ANE EEEE ous Ganawiasen 48 5 3 5 PROC he TING POG IE arisini e E E E E E 48 Ie DA TIBOA D a E E E E E E E E E E E E 48 5 4 1 COIS SiO US ace E E ss tea saa uaa Oa AS ne R 49 5 4 2 GFE MERI OCLs eee CTE A E E E eT E E E E ET EAA E 49 5 4 3 Register the Analytical Document the built DASHBOARD into the PlAtforim ccccccccccccccceneeeeeeeeeeeeees 50 5 4 4 TOSS TA CA OC ITE ax sarap nee A T Gasset ones Gen ia eas deen ora os Ese OA 51 5 4 5 Lecue he TGV CO DOCONENI areo eon E EEE AEETI EAE TEA 51 MING a E A E E reece teaecaus 51 5 6 Oa DT E AN e EAE E E E EA A 51 C TJIN MORE DECE e E E 51 6 1 PORTAL ADMINISTRATOR AND PORTLETS ORGANIZATION seseeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeees 51 6 2 ANALYTICAL DOCUMENT LIFE CYCLE eseeeseeesesrrresessreettttreessstrssssssseeetetrssssesnnsssssseeeeetreessesnnsssssseeeeetteesseenne 52 Co URRE eee ee eee ee ee er ee ee ee ee ere 53 6 4 DOCUMENT ORGANIZATION AND SECURITY POLICY ccccccsccccceeeeeeseessssssssceeasceceeeeseseseseesstsss
88. tical documents by means of the SpagoBI functionalities First of all you have to install ExoTomcat and the SpagoBI DEMO How to handle the different categories of analytical documents follows OL This chapter is in revision phase ZL p p SpagoBI QuickStart ver 0 8 May 17 2006 pag 36 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit http creativecommons org licenses by nc sa 2 0 COM MON S D EED S p a g O B l Q u i C k S t a r t rY the Business Intelligence Free Platform 5 1 INSTALL EXOTOMCAT AND SPAGOBI DEMO Download ExoTomcat 1 0 from http forge objectweb or roject showfiles php group id 151 amp release id 1378 Unzip the file exoplatform tomcat 1 0 zip but do NOT run the application because this would cause the failure of the following SpagoBI installation Download SpagoBI Demo 1 7 from http forge objectweb org project showfiles php group id 204 amp release id 1407 Unzip the file At the command shell navigate to the directory that contains the jar file obtained and type java jar SpagoBIDemol Installer jar Follow the instructions to complete the installation process Notice that it will be required to specify the directory where you have previously unzipped ExoTomcat 1 0 Once completed SpagoBI installation at the command shell it
89. ttp localhost 8080 portal and log on using both bidev as username and password This user is a Developer for the SpagoBI Demo and therefore you will access the Developer Tools page To create a new Parameter the following steps are required 1 Predefined List of Value LOV 2 Predefined Values Constraints 3 Parameters Management 5 2 2 1 Predefined List of Value LOV 1 The Predefined List of Values page can be accessed by selecting the corresponding link from the Developer tools 2 Click on the INSERT icon to add the new desired element 3 In the Predefined List of Values Details page fill in the following information Label Report LOV QUERY Name Report LOV QUERY Description Input Type Query statement Then in the Wizard Query fill in as follows e Connection Name Foodmart Data Warehouse e Query Definition select POSITION_TITLE from POSITION then click on Update fields from query e Visible Columns POSITION TITLE e Value Column POSITION_TITLE 4 Once completed the data entry click on the Save icon saving the information and exit to the Predefined List of Values page 5 Now click again on the INSERT icon to create a second LOV 6 In the Predefined List of Values Details page fill in the following information Label Report LOV FIX_LOV Name Report LOV FIX_LOV Description Input Type Fixed list of values Then in the Wizard Fix Lov add the following pairs e New item nam
90. usiness Intelligence Free Platform Figure 41 QbE Save Query Once selected the Save Query tab the Qbe will try to execute both the automatically composed and the expert query and will display on the right hand side a message to indicate if each query works properly or not On the other side of the page the user can fill in the form required to save the query The user can also specify the scope of the query e Public scope the query will be visible to e Private scope the query will be visible to 4 4 1 5 Result Preview FOODMART DATAMART FOODMART DATAMART QUERY COMPOSITION RESULT PREVIEW Execution Modality Execute query composed automatically by OBE Execute query composed in Expert Mode ee Page 1 Name Surname Store Sales l Anna Albright 249 0300 Bev Desalvo 155 4500 Beverly Baker 2154 2700 Beverly Cardoza 542 3900 Bishop Meastas 1734 7500 Brenda Marshall 42608 1500 Bryan Rutledge 4195 6500 Figure 42 QbE Result preview The Result Preview page first tries to execute the expert or the automatically composed query depending on the selection performed in the View Query page If the query works correctly a table containing the result set is displayed Otherwise a text area containing a description of the error occurred will be displayed 5 Getting started with SpagoBI This chapter explains using some examples the single steps allowing you to enrich the demo portal with new analy
91. xit to the Predefined List of Values page 5 4 2 CREATE A TEMPLATE In order to create a proper template for a DASHBOARD document it is only necessary to realize an XML file containing the movie configuration create an XML with the following content lt xml version 1 0 encoding UTF 8 standalone yes gt lt DASHBOARD movie spagobi dashboards rot lzx swf displayTitleBar true gt lt DIMENSION width 240 height 150 gt lt CONF gt SpagoBI QuickStart ver 0 8 May 17 2006 pag 49 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XSpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform lt PARAMETER name minValue value 0 gt lt PARAMETER name maxValue value 200 gt lt PARAMETER name lowValue value 50 gt lt PARAMETER name highValue value 120 gt lt CONF gt lt DATA url spagobi DashboardService gt lt PARAMETER name dataname value DASHBOARD SCRIPT gt lt DATA gt lt DASHBOARD gt Save the file as DASH _EXAMPLE xml b Notice the following the movie attribute of the DASHBOARD tag refers to the SWF file in SpagoBI context DIMENSION and CONF tags refers to the movie configuration DATA tag defines the Servl
92. y In fact a user can develop test or execute a document only if he has at least one role belonging to the corresponding permissions on the folder containing it Only an administrator user can set these authorizations for each role and each folder For instance in order to develop a document it is necessary 1 to be defined as Developer by the portal administrator 2 to have at least a role that belongs the Development rights on the folder that contains the document To execute a document it is required to 1 to have at least a role that belongs the Execute rights on the folder that contains the document 6 5 USER DEFINITION AND ROLES MANAGEMENT amp y Work in progress 6 6 PORTAL DEFINITION amp y Work in progress SpagoBI QuickStart ver 0 8 May 17 2006 pag 54 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed Ee creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae Oe ae eee a D http creativecommons org licenses by nc sa 2 0 XS pageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform 6 7 ADD AN ENGING amp y Work in progress 6 8 FUNCTIONALITY TREE MANAGEMENT amp y Work in progress 6 9 DATA MART JAR DEVELOPMENT FOR QBE FEATURE 6 9 1 BRIEF QBE INTRODUCTION The Query By Example QBE is a one of the SpagoBI tools which offers a visual mode for data inquiry It i
93. yeePosition in the Parameter name field selecting the Is for prompting check box and then select OK 8 It is now possible to create the report layout Select the T icon from the top toolbar and insert a new text field in the Detail area of the report Double clicking on the new field and in the Static Text type First Name Exit the properties dialog box and create another text field positioning in the Detail area below the first one Double click on the Fields element in the Project Browser and drag the FIRST NAME field next to the corresponding static text field just created Repeat the same action for the LAST_NAME field This report will simply display a list of all employees obtained by the query 9 Now it is possible to execute the report by selecting the corresponding command from the Build menu SpagoBI QuickStart ver 0 8 May 17 2006 pag 38 of 64 Engineering Ingegneria Informatica S p A 2005 This work is licensed E creative under the Creative Commons Attribution NonCommercial ShareAlike commons License To view a copy of this license visit Ae oe ae nee a D http creativecommons org licenses by nc sa 2 0 SpageB S p a g O B l Q u i C k S t a r t tY the Business Intelligence Free Platform Before displaying the final result the application will ask you to enter the EmployeePosition parameter value You can type president 5 2 2 CREATE A PARAMETER Connect to the home page of SpagoBI portal h

SpagoBI QuickStart

Contents

Download Pdf Manuals

Related Search

Related Contents