ISIT 417/917 - Business Intelligence and Knowledge Management Lab 3 – Introduction to Jedox Topics covered Introduction to Jedox, Jedox pase view, Jedox paste element. What is Jedox for Excel? Jedox is a Business Intelligence tool that is capable of planning, analysis and reporting business data. It was developed for Microsoft Excel. Jedox for Excel consists of the Jedox OLAP Server and the Jedox- AddIns for Excel. Although Excel has strong analysis tools to present data summaries when it comes to data modelling its capabilities are limited. In a business environment where 100's of users are entering in plans in real-time, Excel alone is insufficient. In such an environment planning processes will require handling centralised business rules for rapid what-if analysis and scenario modelling. Excel for Jedox offers client-server architecture which allows multi-users and can be used in a network eliminating the need to distribute your spreadsheets, which in turn eliminates the risk of creating various versions. Jedox has an in-memory database. i.e. the database with which a Jedox-user works is totally loaded into memory. Therefore, Jedox is generally speed-oriented and makes working with large data quantities substantially easier and faster. In Excel data should be stored in worksheets. Therefore, a great number of worksheets are required to deal substantially larger datasets. Jedox multidimensional and hierarchical database can organize and represent data in a structured way and therefore overcome the restrictions of a two-dimensional Excel table. Jedox is cell-related; Although Jedox works with a database, it does not refer to data records. Instead it thinks in terms of cells. Jedox also supports automatic attribute management. To see more details on the bold words see "first-steps-with-jedox-for-excel-manual.pdf" (page 6-12). How to start Jedox Once installed Jedox can be launched either using the desktop icon or simply opening Excel. In Excel window Jedox appears as an additional tab. Jedox demo data In our Jedox exercises we will be using the Demo database that comes with Jedox. Jedox store its data as cubes. Demo database consists of a cube Sales and the cube has 5 dimensions; Regions, Months, Years, Datatypes and Measures. Each dimension is a hierarchy. Dimensions templates of Demo database is available in folder D:\Documents and Settings\All Users\Documents\Jedox\Jedox Suite\DimensionTemplates. Exercise 1 Visit folder …\Documents and Settings\All Users\Shared Documents\Jedox\JedoxSuite\ Dimension Templates\. Open each text file to see data used in Demo database. Exercise 2 Paste View - Jedox data summaries using Demo database Paste view prepares data summaries to view relationships between different dimensions of a data cube. In Excel worksheet, select Paste View under Jedox tab. The following dialog box appears. This behaves similar to Excel pivot tables. You can drag and drop the columns and rows that you need to find information about. Drag and drop Products into column titles area. Press Paste button. Resulting screen is in Figure 1. What is this value? See cells A3, A4, A5, A6 and A7. This value is the total of units for religion "Europe", for the full year (year), and for all years and all data types. Figure 1 Double click on A3 cell. Choose elements dialog appears.(Figure 2). Expand Europe and select "West", press OK. Double click on A4 cell. Expand Year and select "Qtr. 1", press OK. Double click on A5 cell. Expand All Years and select "2007", press OK. Double click on A6 cell. Expand All Datatypes and select "Variance", press OK. Double click on A7 cell. Expand Units and select "Cost of sales", press OK. Figure 2 The new value 73,015 is the total "Cost of sales" for religion "West (Europe)", for the Quater1 of year 2007, and data types variance. Exercise 3 Paste Elements - Jedox data summaries using Demo database Paste Elements is the more detailed paste view in Jedox. Paste elements allow us to select hierarchical dimensions in Jedox. To use Paste Elements; In Excel worksheet, select Paste View under Jedox tab. In Paste View Dialog box, drag and drop Regions into column titles area and Products into row titles area. Double click on "Select elements…" for Regions. In paste elements dialog box select regions as shown in Figure 3. Press Ascending button (see red arrow in Figure 3) to sort and then Ok. Again in paste view dialog box, double click on "Select elements…" for Products. Select all Stationary Pc's. Select Ok. In Paste view dialog box select Paste. Change other dimension values in A3 to A6 as shown in Figure 4 (Year 2009, All data types, Turnover, and Quarter 1). Compare resulting screen with Figure 4. Figure 3 Figure 4 Class Exercise – Lab 3 1. Explain the following. Use "first-steps-with-jedox-for-excel-manual.pdf'" for help. - Jedox has a multidimensional database, but able to display data in a two dimensional excel space. Explain how this is possible. - Jedox has a hierarchical database. Explain how this is possible. 2. Produce 3 different paste views using the demo database. Describe the information summarized in your screens. Name the workbook Lab3Solution.xls. Produce each view in a different sheet. Name sheets View1, View2, and View3. This is worth 1% of your assessment for the subject. If you do not submit this sheet in class, you will not be able to make up the lost marks. Upload your work to "Lab3 & Lab4 Submission Dropbox" before leaving class.