Administrators Guide
Connections, Views & User Management
Release 5.0 May 2010
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
Yellowfin ® Release 5.0 Admin Guide Under international copyright laws, neither the documentation nor the software may be copied, photocopied, reproduced, translated or reduced to any electronic medium or machine-readable form, in whole or in part, without the prior written permission of Yellowfin International Pty Ltd, except in the manner described in the software agreement. The information in this document is subject to change without notice. If you find any problems with this documentation, please report them to Yellowfin in writing at
[email protected]. Yellowfin does not warrant that this document is error free. Copyright © Yellowfin International 2010. All rights reserved. Portions © Copyright Microsoft Corporation. All rights reserved. Trademarks: Yellowfin and the Yellowfin Logo are registered trademarks of Yellowfin International. All other product and company names mentioned herein are the trademarks of their respective owners. Version: 1.0 Published: May 2010
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
3
Table of Contents Chapter 1 About Yellowfin Administration ......................................................................... 12 Overview .............................................................................................................................. 12 The Yellowfin Administrator’s role ................................................................................... 12 Audience .......................................................................................................................... 12 Send us your feedback .................................................................................................... 12 Administration Page ............................................................................................................. 13 Manage Data Access Panel ................................................................................................ 14 Source Systems & Views ................................................................................................ 14 Report Category Management ........................................................................................ 15 Export & Import ................................................................................................................ 15 Manage User Settings Panel ............................................................................................... 16 Group Management ......................................................................................................... 16 Role Management ........................................................................................................... 16 Configuration Panel ............................................................................................................. 17 Configuration ................................................................................................................... 17 Report Styles ................................................................................................................... 18 Images & Maps ................................................................................................................ 18 Ref Codes & Translations ................................................................................................ 19 Administration Panel ............................................................................................................ 20 Session Management ...................................................................................................... 20 Schedule Management.................................................................................................... 21 Dashboard Management ................................................................................................. 21 License Details ................................................................................................................ 22 System Information .......................................................................................................... 23 Chapter 2 Security, Audit & Approvals ............................................................................... 24 Overview .............................................................................................................................. 24 Security Framework ............................................................................................................. 24 Access Roles and Functions ........................................................................................... 24 Report Categories ........................................................................................................... 25 Source System Access Management ............................................................................. 27 View Access Management .............................................................................................. 28
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
4
Column Access and Restrictions ..................................................................................... 29 Access / Value Based Filters ........................................................................................... 30 Introducing the security analysis process ............................................................................ 31 Security design methodology .......................................................................................... 31 Analysis Approach ........................................................................................................... 34 Report Audit and Tracking ................................................................................................... 35 Should we maintain unique user logins? ......................................................................... 35 What data is stored in the report audit? .......................................................................... 35 Audit Event List ................................................................................................................ 36 Report Approvals ................................................................................................................. 44 Public Report Writers ........................................................................................................... 45 Why designate Public Report Writers? ............................................................................ 45 How to designate users for Public publishing?................................................................ 45 Chapter 3 Data Source Management ................................................................................... 46 Overview .............................................................................................................................. 46 What is a Data Source? ................................................................................................... 46 Source Types................................................................................................................... 46 Data Source Connection List ............................................................................................... 47 Add source connection .................................................................................................... 47 Edit Source ...................................................................................................................... 47 Delete Source .................................................................................................................. 47 Source Connection Parameters........................................................................................... 48 Details .............................................................................................................................. 48 Security Setting ............................................................................................................... 49 Connection Parameters ................................................................................................... 50 In-Memory Connection Parameters ................................................................................ 51 Database Specific URL Formats ..................................................................................... 52 ODBC Connections - JDBC-ODBC Bridging ................................................................... 53 Connection Management ................................................................................................ 53 Connection Availability .................................................................................................... 54 OLAP Source Connection ............................................................................................... 54 Database Connection Issues........................................................................................... 55 Source Security ................................................................................................................... 56 Securing the Connection ................................................................................................. 56
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
5
Source Access Filters .......................................................................................................... 57 Creating an Access Filter ................................................................................................ 57 Filter Parameters ............................................................................................................. 58 Refresh Type ................................................................................................................... 58 Defining a Filter Type ...................................................................................................... 59 Manual Entry.................................................................................................................... 60 Upload Data File .............................................................................................................. 61 Scheduled SQL Query..................................................................................................... 62 Assigning an Access Filter to a View .............................................................................. 62 Assigning an Access Filter to a Report ........................................................................... 64 Chapter 4 View Builder Introduction ................................................................................... 65 Overview .............................................................................................................................. 65 Who is the view Administrator? ....................................................................................... 65 What does a view contain?.............................................................................................. 66 Item / Field Types ............................................................................................................ 67 How are views used? ........................................................................................................... 68 Assisting Data Analysis ................................................................................................... 68 Who uses views? ............................................................................................................. 68 How are items presented for report writers? ................................................................... 68 View design methodology .................................................................................................... 69 Chapter 5 View Creation & Management ............................................................................ 71 Overview .............................................................................................................................. 71 Create a New View .............................................................................................................. 71 Drag and Drop Builder ..................................................................................................... 72 SQL view ......................................................................................................................... 72 Composite Views ............................................................................................................. 73 OLAP Cube View ............................................................................................................. 73 Stored Procedures ........................................................................................................... 74 View Options ........................................................................................................................ 77 Description ....................................................................................................................... 77 Caching and Performance ............................................................................................... 77 Row Limits ....................................................................................................................... 78 Category .......................................................................................................................... 78 Securing a View ............................................................................................................... 78
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
6
View Summary ..................................................................................................................... 80 General Information ......................................................................................................... 80 Columns........................................................................................................................... 81 Data ................................................................................................................................. 81 SQL .................................................................................................................................. 82 Elements .......................................................................................................................... 82 Reports ............................................................................................................................ 83 Cache .............................................................................................................................. 83 View Management ............................................................................................................... 84 Saving a View .................................................................................................................. 84 Editing a View .................................................................................................................. 85 Rollback a View ............................................................................................................... 86 Deleting a View ................................................................................................................ 88 Copying a View ................................................................................................................ 88 Caching a View ................................................................................................................ 88 View Categorisation ............................................................................................................. 91 Field Category List ........................................................................................................... 91 View List Category ........................................................................................................... 91 Create Report View Category.......................................................................................... 92 Chapter 6 View Tables, Joins & Conditions ....................................................................... 93 Overview .............................................................................................................................. 93 Benefits of the Yellowfin View Builder ............................................................................. 93 What is a Relationship Diagram? .................................................................................... 93 Drag and Drop Builder ......................................................................................................... 95 Database Table List ......................................................................................................... 96 Drag Table onto Canvas .................................................................................................. 97 Multiple Table Versions & Aliases ................................................................................... 97 Table Properties .................................................................................................................. 98 Create Alias ..................................................................................................................... 98 View Sample Data ........................................................................................................... 99 Select Table Columns ................................................................................................... 100 Joining Tables .................................................................................................................... 101 What is a join? ............................................................................................................... 101 Join Types ..................................................................................................................... 101
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
7
Creating and editing a Join ............................................................................................ 102 Deleting Joins ................................................................................................................ 103 Table Conditions ................................................................................................................ 104 Condition Builder ........................................................................................................... 104 Create New Basic Condition.......................................................................................... 106 Current Date Conditions ................................................................................................ 106 Dynamic Conditions ....................................................................................................... 107 How they Work .............................................................................................................. 107 A Dynamic Condition ..................................................................................................... 107 Clear Conditions ............................................................................................................ 108 Virtual Table ....................................................................................................................... 109 Create Virtual Table ....................................................................................................... 109 Edit Virtual Table SQL ................................................................................................... 111 Common View & Join Design Issues ................................................................................. 112 Granularity ..................................................................................................................... 112 Indexes .......................................................................................................................... 113 Outer joins ..................................................................................................................... 113 Chapter 7 View Fields, Meta data & Hierarchies .............................................................. 114 Overview ............................................................................................................................ 114 Define View Fields ............................................................................................................. 115 Making Fields Available for Reporting ........................................................................... 115 Field Meta Data ............................................................................................................. 115 Field Format ....................................................................................................................... 116 Conversion..................................................................................................................... 117 Format ........................................................................................................................... 117 Additional Format Options ............................................................................................. 119 Semi Additive Fields ...................................................................................................... 120 Default Aggregation ....................................................................................................... 120 Reference Codes – Lookup Table ..................................................................................... 121 Create Reference Code ................................................................................................. 121 Editing a reference code ................................................................................................ 123 Field Access and Usage .................................................................................................... 124 Access Filter .................................................................................................................. 124 Access Level.................................................................................................................. 124
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
8
Secure Column Management ........................................................................................ 125 Filed Permissions .......................................................................................................... 126 Mandatory Filter and Field ............................................................................................. 126 Drill Down Hierarchies ....................................................................................................... 127 Column Constraints / Semi Additive Fields ....................................................................... 128 Field Categorisation ........................................................................................................... 131 Apply Category .............................................................................................................. 131 Edit / Add Categories ..................................................................................................... 131 Defining Attribute Display Order .................................................................................... 132 Chapter 8 Creating Calculated Columns ........................................................................... 133 Overview ............................................................................................................................ 133 Create New Column .......................................................................................................... 133 Formula Builder ............................................................................................................. 134 Case Statements ........................................................................................................... 135 SQL Editor ..................................................................................................................... 136 Custom Functions .......................................................................................................... 136 Creating New Custom Functions ................................................................................... 137 Pre-Defined Filters ............................................................................................................. 138 Create New Filter ........................................................................................................... 138 Filter Operands .............................................................................................................. 139 Chapter 9 Creating a View from Multiple Sources ........................................................... 141 Overview ............................................................................................................................ 141 Creating a Composite View ............................................................................................... 141 Define Storage Database .............................................................................................. 141 Prepare Data for your Composite View ......................................................................... 143 Joining Existing Views Together.................................................................................... 143 Creating Virtual Tables .................................................................................................. 144 Set the Refresh Schedule.............................................................................................. 145 Initial Data Population .................................................................................................... 145 Chapter 10 Report Categorisation ..................................................................................... 147 Overview ............................................................................................................................ 147 How Content is Organised ................................................................................................. 147 Taxonomy ...................................................................................................................... 147 The challenge ................................................................................................................ 148
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
9
Managing Report Categories ............................................................................................. 149 Report Categories List ................................................................................................... 149 Deleting Categories ....................................................................................................... 149 Create New Category ........................................................................................................ 150 Draft Report Category ................................................................................................... 150 Sort Order ...................................................................................................................... 151 Create Public Publisher ................................................................................................. 151 Management Information & Version Management ........................................................ 151 Determine Version Control ............................................................................................ 152 Category Security .......................................................................................................... 152 Chapter 11 User Administration ......................................................................................... 154 Overview ............................................................................................................................ 154 User Management ............................................................................................................. 154 User List......................................................................................................................... 154 Search Users ................................................................................................................. 155 Add User ............................................................................................................................ 155 Change Password ......................................................................................................... 155 Change Role Access ..................................................................................................... 156 Bulk User Import ................................................................................................................ 157 Deleting Users ................................................................................................................... 158 Chapter 12 Group Management ......................................................................................... 159 Overview ............................................................................................................................ 159 Group Management ........................................................................................................... 159 Group List ...................................................................................................................... 160 Delete Groups................................................................................................................ 160 Create Groups ................................................................................................................... 161 Default Dashboards ....................................................................................................... 161 Group Members ............................................................................................................. 162 Member Types ............................................................................................................... 162 Member Control ............................................................................................................. 163 Chapter 13 Role Management ............................................................................................ 164 Overview ............................................................................................................................ 164 Role List ............................................................................................................................. 164 Default Yellowfin Roles ...................................................................................................... 165
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
10
Edit / Create Role .............................................................................................................. 166 Security Functions Available.............................................................................................. 168 Chapter 13 Export & Import Report Repository ............................................................... 171 Overview ............................................................................................................................ 171 Database Independence ............................................................................................... 171 Exporting ............................................................................................................................ 172 Importing ............................................................................................................................ 174 Chapter 14 Schedule & Broadcast Management .............................................................. 176 Overview ............................................................................................................................ 176 View Schedules ................................................................................................................. 177 Edit Schedule ..................................................................................................................... 177 Pause Scheduled Task ...................................................................................................... 178 Run Scheduled Task ......................................................................................................... 179 Delete Scheduled Task...................................................................................................... 179 Refresh Schedules ............................................................................................................ 180 Chapter 15 Images & Maps ................................................................................................. 181 Overview ............................................................................................................................ 181 Loading Images ................................................................................................................. 183 Using Images in your Charts ......................................................................................... 184 Raster Maps....................................................................................................................... 185 How do they work? ........................................................................................................ 185 Loading a Raster Map ................................................................................................... 185 Using a Raster Map ....................................................................................................... 187 WMS Layers ...................................................................................................................... 188 Load WMS Layers ......................................................................................................... 188 Add WMS Layer ............................................................................................................ 189 Chapter 16 Reference Codes & Translations ................................................................... 191 Overview ............................................................................................................................ 191 Managing Reference Codes .............................................................................................. 192 Selecting your reference Code ...................................................................................... 192 Changing Values your reference Code ......................................................................... 193 Multiple Languages ....................................................................................................... 194 Usage ............................................................................................................................ 194 Chapter 18 Customisation .................................................................................................. 195
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
11
Overview ............................................................................................................................ 195 Analytical Functions ........................................................................................................... 196 Analytical Function Interface ......................................................................................... 196 Development Libraries ................................................................................................... 199 Registering Analytical Functions for Use ....................................................................... 199 Example: Percentage of Total ....................................................................................... 200 Example: Top 10 ........................................................................................................... 201 Custom Functions (XML Definitions) ................................................................................. 202 Schema.......................................................................................................................... 202 Adding New Functions ................................................................................................... 204 Example custom-functions.xml ...................................................................................... 204 Data Display Formats ........................................................................................................ 206 CustomFormatter class ................................................................................................. 206 Including embedded HTML in formatted values. ........................................................... 208 Compiling a Custom Formatter...................................................................................... 209 Packaging a Custom Formatter ..................................................................................... 209 Appendix .............................................................................................................................. 211 Code Examples ................................................................................................................. 211 Appendix A – CustomFormatter.java ............................................................................ 211 Appendix B – CurrencyFormatter.java .......................................................................... 212
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
12
Chapter 1 About Yellowfin Administration
Overview This guide describes the Administration functions of Yellowfin. It describes how to create views of source systems, the layer that represents database structure in business terminology. It also contains information on creating users, groups and report categories.
The Yellowfin Administrator’s role The role of the Yellowfin Administrator is to ensure users have: 1. Appropriate access to Yellowfin. This is done by creating users and managing their access rights. 2. Access to source databases and views for reporting. These are defined in Yellowfin and include a business definition layer for columns and table names. 3. Creation and maintenance of Report Categories / Folders in which to save their reports for future access.
Audience This guide is intended for the Yellowfin Administrator, user of the admin functions.
Send us your feedback Do you have a suggestion on how we can improve our documentation? Is there something you particularly like or have found useful? Drop us a line, and we will do our best to ensure that your suggestion is included in the next release of our documentation:
[email protected] Note: If your issue concerns the Yellowfin product and not the documentation, please contact our Customer Support experts. For information about Customer Support visit: www.yellowfin.com.au
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
13
Administration Page The administration Panel provides the administrator access to all the functions required to manage the Yellowfin application and users. To access the panel, click on the administration link on the main navigation menu.
Figure 1 Administration Panel
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
14
Manage Data Access Panel Managing access to data held in various systems is a core component of Yellowfin administration.
Figure 2 Manage Data Access Panel
Source Systems & Views Each source system that is accessed for reporting needs to be defined within the application. For example if reports are to be run off the HR system and the Finance system then each of these must be defined as a separate source system.
Figure 3 Source Systems & Views A view is a slice of data out of your database or simply an existing table. It may include information from a number of tables as well as calculations to aggregate or derive new information.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
15
The actual view is stored and implemented on the source data system. However, in Yellowfin the view is defined and made available for reporting. For example if you wanted to report off your People table in your HR source system you would define a Yellowfin view by selecting the People table and creating a definition for all the columns that you wish to report off.
Report Category Management Every report has to have a category and subcategory defined when it is saved into either a persons or the Public report repository. These categories serve a dual purpose: 1. To provide users with a standard meta-data for saving and retrieving reports into standardised reporting categories and structures. 2. To provided an additional layer of security. For instance only Finance Users could be provided access to the Finance Category. Thus any report placed in this category would only be available to finance users.
Figure 4 Report Categories
Export & Import Export and Import content from one instance of Yellowfin into another. This lets you export and import all report content.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
16
Manage User Settings Panel A critical role of the Yellowfin report writers is to manage access to Yellowfin by authorized users. Each user should have a unique logon. As such each user will need to be setup in the system and given a role. The role will define what reporting functions they have access to. As an example not all users should have access to the administration functions. Each function and its purpose is defined later in this manual.
Figure 5 Manage User Settings Panel
Group Management Using Yellowfin you can create Groups of users. These groups are used for security access to dashboards and data.
Figure 6 Group List
Role Management Create new roles and change the functions that are available to those user roles.
Figure 7 Roles List
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
17
Configuration Panel The configuration options allow you to tailor your installation of Yellowfin. This includes security management, user authentication and email servers. The configuration options are covered in detail in the Installation and Configuration manual.
Configuration Use this section to customise Yellowfin settings, such as authentication, integration (headers, footers, and navigation), page formatting, email server and defaults, system parameters, and regional settings. Click on the
Configuration link in Administration.
Figure 8 Configuration Link You should now see the Authentication, Integration, Page Format, Email, System Parameters, Region Settings tabs in the Configuration section.
Figure 9 Configuration Menu
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
18
Report Styles This area allows you to set custom colours and font styles for charts, reports and the dashboard.
Figure 10 Report Styles
Images & Maps Use this to load static images to be used as background images for reports and charts, as well as raster maps for the generation of heat maps.
Figure 11 Images & Maps
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
19
Ref Codes & Translations View and manage reference codes and create multi-language translations for reference codes.
Figure 12 Reference Codes & Translations
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
20
Administration Panel The administration options all you to manage various aspects of Yellowfin, such as sessions, schedules, dashboards, and licences. They also allow you to check system information, which can be useful for troubleshooting and support issues.
Session Management This lets you close open sessions of users that are currently logged on. This is useful if you have a user that has logged on from one IP address and then tries to logon from another but has been denied access. Click on the
Session Management link
in Administration.
Figure 13 Session Management
You should now be able to see a list of all current sessions.
Figure 14 Session List
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
21
Schedule Management View and delete reporting schedules that have been set up by users.
Figure 15 Schedules List
Dashboard Management View all available dashboards, check their status, subscriptions, and report numbers as well as having the option to edit and delete selected tabs.
Figure 16 Dashboard List
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
22
License Details This lets you update your license and view your current license parameters. Click on the
Licence Details link in Administration.
Figure 17 Licence Details Link
You should now be able to see both your licence and usage details.
Figure 18 Licence Details
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
23
System Information View current system information including Yellowfin version details, security settings, system resources and more. Sometimes support may ask you for an XMP export of these details to assist with troubleshooting.
Figure 19 System Information
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
24
Chapter 2 Security, Audit & Approvals
Overview Security of your Public information is critical. When deploying Yellowfin an analysis of the security needs of your business should be undertaken. Yellowfin has a number of security features that you can use to ensure the security of your Public information. These can be applied is a mix of ways depending upon the level of security that you require. This section will cover off the how you should approach your security design and once this has been completed the options that you have for implementing that design.
Security Framework This section describes the security framework available to you through Yellowfin. It has been set out so that the highest level security features are described first. For instance Access Roles are the highest and most easy to administer form of security whilst column level security is the most granular and by default the most complex to administer over a large user base deployment.
Access Roles and Functions Yellowfin user management is designed around the concept of user roles. This means that multiple users share a commonly defined role for access to the application. Individual users do not have a unique security profile. A role is a collection of available security functions. Each user will have a role associated with them. As the Yellowfin report writers you can either: 1. Change a person’s role – and thus the type of access they have to the application or 2. Change a role definition by adding or removing functions and thereby updating all users’ access to the system that share that role.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
25
When a user is logged in the system checks that they are still registered in the application and if so what role they should have. Based on the role access the users interface will be dynamically built – only showing them links and functions that their role has access to. For example – if a user’s role does not have access to the dashboard when they login they will be taken to the report list page. A user with dashboard will be taken in to the dashboard page.
When to use
Use if you wish to limit access to certain functions – such as the ability to write reports
When not to use
Roles cannot be used effectively to limit access to information and data..
Benefits
Easy to maintain for all users.
Tips
Limit the number of roles created at your organization. By increasing the number of roles the level of effort required to manage access increases. Generally only permit a single role per user. Although Yellowfin does support multiple roles it can lead to confusion in a business user.
Report Categories All reports are managed through a similar security and categorisation infrastructure which is managed through the configuration Content Access function. The security of your reports is managed at the category and subcategory level, not at the individual item level. The purpose of this is to simplify the creation of reports in the system. For Example Rather than having to specify who is allow to see a specific reports each time you create a new report the security for the report is inherited from the subcategory of the item that is created.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
When to use
26
Use categories to create meaningful business groupings for reports. If your views are ‘write’ secured then providing access securities to categories allows a user to publish sensitive reports into secure categories for wider but secure read only distribution.
When not to use
Category security is meaningless if users can write reports against a specific view (ie it is unsecure) but cannot see a category in which that view logically fits. For example the Category may be HR reports and the view is a view to the HR database. If a user can write reports and the view is not secure then whether there is security on the category is largely irrelevant since the user will have access to the base data through report builder. If all your sensitive views have READ level access defined – applying security to your categories is not required.
Benefits
Category Security is excellent for locking read only users out of specific subject domains.
Tips
Create Subject domains that are intuitive for users to understand. For example Executive HR – this category can then be made exclusively available to Senior Management for HR reports. Users publishing reports into categories must be aware of the security of these folders.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
27
Source System Access Management When setting up a source system in Yellowfin you can define which users have the rights to create views against the source as well as write SQL queries against the source. The general rule for source system security is that it is used for controlling Yellowfin report writers that wish to create views against the source. It is through this process that a user could write reports against the source system and thereby gain unauthorized access to data. For example if the HR system is to be setup as the source system any user with View Definition access will be able to view all tables including payroll data if the source is unsecure. By securing the source to only HR view builders, only those authorized users will be able to define and manage the HR related views.
When to use
Use if you have multiple view administrators – each of whom require access to specific source databases only. Use if some users have free hand SQL access to write reports and the data in the data source is sensitive.
When not to use
Do not set security on the source in an attempt to limit access to drag and drop report writer users.
Benefits
It is easy to maintain for a select number of users.
Tips
Limit the number of users that have administration access to views. Especially if they wish to edit the same source system. Multiple administrators can lead to contention issues when managing views.
Note: If there is only 1 Yellowfin report writers of your Yellowfin deployment, and no additional users writing SQL reports then you may consider leaving your source systems unsecure
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
28
View Access Management The main form of security for users creating reports and having access to views which allow them to write any report is through the VIEW security. When a report is written or edited a user must connect to the view record to determine what fields are available to them. At this stage security check is made to determine if the view that is being accessed is secure, and if so, does the user have the authority to access it. The security on your view is the most rigorous in terms of managing access to the data that is stored in it. Not only can you control edit access but you can also control which users are permitted to read reports created from the specified view. For example the Finance view is created. Only the finance department is permitted to write finance view reports. In this case the view would be defined as secure and the finance users would be added into the access list with edit access.
When to use
Use if you wish to limit users that have access to the report writing function using the specified view. Use if you wish to be specific in defining which users can read reports created by a specified view but are not permitted to write reports.
When not to use
If reports in the view are to be written by a handful of users and then published to a wider community it is preferable not to use READ level security. Use category access for this. For example even though the HR view contains sensitive data the HR report writers must write and distribute many reports from this view – most of which do not contain sensitive data. Simplify security of the view by having secure categories into which the report is saved rather than managing security in both the categories and the view. If the data contained in the view is not sensitive then do not apply security to it.
Benefits
Easy to maintain for EDIT level security – can become complicated if using READ level security in conjunction with category security.
Tips
If the view is sensitive determine who the users writing reports against the view are and for whom they are writing reports.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
29
Use this to determine the best security strategy for the view. If the reports are for a wide distribution view security for read access might not be appropriate.
Column Access and Restrictions In some cases a view might be created that is designed for general use but some columns within that view are highly sensitive. For example the salary column in the human resources view holds data that is not for general consumption. In this case you have two options. 1. Create a copy of the view and exclude the salary column from this instance. Save the view with a new name to indicate that the view is free of sensitive data. 2. Alternatively Yellowfin provides you with the opportunity to define the columns as restricted columns. Once this has been done an additional layer of security needs to be defined, which allows certain users access to the restricted columns of the selected view. Note security to restricted columns is globally defined. You cannot specify different users for separate restricted columns within the view. Only users with restricted access will be able to see the item when creating reports. In addition when a report is run only users with the access level will be able to see the column on the report.
When to use
Use if you wish to create a general view available to many users but restrict access to sensitive data to only a few users.
When not to use
Do not use if the view in general and the columns all have the same users that can access them.
Benefits
Can be used to secure specific columns within a view.
Tips
This is a difficult security option to maintain from an administration point of view. Consider alternatives first. Only users with access to the view will be able to have column level access.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
30
Access / Value Based Filters In some cases a view might be created that is designed for general use but you only wish report consumers to access data from the view that is relevant for their position in the organisation – such as cost centre manager. In this case you would create an Access or Value based filter. This is achieved by updating the source connection wizard to specify the available filters – such as cost centre and your users’ relationship to that source. You then specify the specific columns on the view that related to that source filter – eg you must indicate which column in the view is the cost centre column. When writing a report you would specify that the cost centre filter must be used as the access filter. In this case the cost centre that the report reader owns will be passed in as a filter on the query. Only users with access filters defined will be able to see the data in their reports.
When to use
Use if you wish to create a general view available to many users but restrict access to data based on a users relationship to the data – eg cost centre managers. This mechanism is very good for creating Privatised reports. By using value based filters you can create a single report which is distributes to many users. Each user will however, only see their specific / Privatised data.
When not to use
Do not use if the view in general and the columns all have the same users that can access them.
Benefits
Can be used to secure data within a view to only display relevant data.
Tips
This is an easy option to maintain from an administration point of view. This mechanism allows you to provide access to all data within a view to all your users with the security of knowing that they will only see their specific data.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
31
Introducing the security analysis process Before deploying Yellowfin to all your users you should determine the security management profiles that you wish to deploy. The following sections give an overview of how you should approach the security of your Yellowfin application and the access of users to your business critical information.
Security design methodology The security design methodology described in this guide consists of one planning stage, and two implementation phases: 1. Analysis of business needs and planning the security solution 2. Designing the security framework 3. Implementing your security framework
Each implementation phase is based on an assumption that you have completed an initial planning phase. The planning phase can be done without using administrator, and is the decisive phase for the success or failure of your security. A poorly planned security framework that is not based on a study of your business needs will be difficult to maintain and may enable unauthorized access to sensitive data. Each of these phases is described as follows: 1. Plan the your security framework before you start using Administrator Before starting the first phase, you should spend time understanding your businesses security requirements and how they related to the data that will be exposed to the business through Yellowfin. You must analyse the security need of the target audience for each data source and view to be implemented. The structures that you use to manage security should based on a clearly defined user need to access the data contained in those tables and columns and stay consistent with the overall security strategy of your business.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
32
2. Designing the security framework You create a security framework by understanding the needs of your users. The components of this framework are described in more detail throughout this chapter. You have choices to limit users to be report consumers only, limit access to database views, or limit the ability for users to publish reports to the Public repository. 3. Implementing your Security Framework Create the user roles, groups, report categories, and provide access to date sources and views to ensure your security requirements are met. Test these requirements against a sub set of users that have various levels of access.
The table below outlines the major phases in a typical view development cycle: Development phase
Description
Prepare
Identify the target data source and become familiar with its structure. Know what data is contained within each table of each of the target databases. Understand the joins. Identify the cardinality. Know what is possible.
Analyse
Identify the user population and how it is structured; for example is the user group structured by department or by task. Identify what information the users need. Identify what standard reports they require. Familiarise yourself with their business terminology so that you can name items sensibly. Plan Identify a project strategy. For example, how many views should be created and which ones should have the capacity to be linked and to what level.
Implement
Implement your physical view SQL on the target database Build the Yellowfin view using Administrator. This manual
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
33
covers this part of the view development cycle, the actual use of the tool. Test frequently during the build process for validity and reliability of inferred SQL. Test
Form a small group of users, preferably power users who have some knowledge of what information they expect to get from the view. Pre-Release the view to these users by adding them the access security list for the view. Ask the users to perform thorough tests simulating live usage of the view(s).
Deploy
Change access security of the view so that it is available to the target user base.
Evolve
Update and maintain the view as the data sources and user requirements change and grow.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
34
Analysis Approach The following questions and responses may assist you to define your security framework and strategy.
Do all my users need report
If no then use your reader role to only allow users
writing access?
with read access to the system
Is the data in my source
If yes then you will need to apply security to your
systems sensitive?
data source. This will stop unauthorized access to SQL report writers and users that have admin access to the product.
View Security – Is data in my
If some report writers do not have access to data
view sensitive. Can all report
in the view then view security is required. The
writers have access to the
security will stop unauthorized reports being
data contained in it?
written.
The majority of the view is not
Define view columns as secure.
sensitive but only 1 or 2 columns are. If publishing Public reports
If some users should be restricted from the salary
from the same view will some
data you should create two categories for report
contain sensitive data and
saving – one for general access which is unsecure
other reports not. For example
and one for secure access.
an HR report containing salaries could be written from the same view as an HR report containing Headcount Will I reporting from source
This assumes that people with report writing access do not have EDIT access to the view – if they do then they could edit a report and add the sensitive data. Best to set up diff categories
systems with completely different subject areas?
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
35
Report Audit and Tracking Yellowfin has a complete audit facility for tracking which users are accessing data from your data sources. Each time a report is run a unique instance of that report definition and timestamp is saved. As an administrator you will be able to run reports against the yellowfin database to determine your usage statistics and track access to specific data sources and views.
Should we maintain unique user logins? For security and audit purposes it is vital that each user be given unique access logons to the system. If users use shared accounts your ability as an administrator to manage security and audit reporting use will be compromised.
What data is stored in the report audit? Each time a report is run a record is created that stores the details of that report. Reader’s User ID
Which user accessed the report
Report ID
Unique instance of a report definition
SQL Statement
The SQL that was generated and passed to the database
Time Stamp
When the report was run
Duration
How long did the query take
Number of Rows Returned
How many rows were returned.
Source System Accessed*
Which source system was accessed
View Accessed*
Which view was accessed as part of the query.
* Derived fields
By accessing the administration view and reporting your usage statistics you will be able to track and audit all users’ access to the application and your Public data.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
36
Audit Event List In addition to the report instance tracking Yellowfin also maintains an event table which logs all major report related events. This table can be used for audit tracking for user activity. The following is a list of all event types and their descriptions as logged to the Yellowfin event table.
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
EXPORT
EXPORTCATEGORY
Export category
Yes
IpPerson
1
ContentManagementId
Category, SubCategory, LoginAccess, ShortDescription
EXPORT
EXPORTDASHBOARD
Export dashboard
Yes
IpPerson
1
Tab Id
GroupId (Tab Id), ShortDescription
EXPORT
EXPORTREPORT
Export report
Yes
IpPerson
1
ReportId
ReportId, ReportName
EXPORT
EXPORTSOURCE
Export data source
Yes
IpPerson
1
SourceId
SourceId, SourceName
EXPORT
EXPORTVIEW
Export view
Yes
IpPerson
1
ViewId
ViewId, ViewDescription
GROUP
CREATEGROUP
Group created
Yes
IpPerson
1
GroupId
Group
GROUP
DELETEGROUP
Delete Group
Yes
IpPerson
1
GroupId
Group
GROUP
UPDATEGROUP
Group updated
Yes
IpPerson
1
GroupId
Group
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
37
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
IMPORT
IMPORTCATEGORY
Import category
Yes
IpPerson
1
ContentManagementId
Category, SubCategory, LoginAccess, ShortDescription
IMPORT
IMPORTDASHBOARD
Import dashboard
Yes
1
Tab Id
GroupId, ShortDescription
IMPORT
IMPORTREPORT
Import report
Yes
IpPerson
1
ReportId
ReportId, ReportName
IMPORT
IMPORTSOURCE
Import data source
Yes
IpPerson
1
SourceId
SourceId, SourceName
IMPORT
IMPORTUSERS
Import users
Yes
IpPerson
1
99
UserN (IpPerson)
IMPORT
IMPORTVIEW
Import view
Yes
IpPerson
1
ViewId
ViewId, ViewDescription
REGiSTRATION
CREATEUSER
Create user
Yes
IpPerson
1
New User's IpPerson
IpPerson, PersonName, UserId, RoleCode
REGISTRATION
DELETEUSER
User deleted
Yes
IpPerson
1
IpPerson of deleted user
User (user’s name), email (user’s userid), org
REGiSTRATION
EDITUSER
Edit user
Yes
IpPerson
1
User's IpPerson
IpPerson, PersonName, UserId, RoleCode
REPORT
AUTOREFRESH
Scheduled report
1
ReportId
report
refreshed REPORT
DASHACTIVATE
Dashboard activated
Yes
IpPerson
1
Tab Id
reportgroup
REPORT
DASHADD
Existing tab added to
Yes
IpPerson
1
Tab Id
reportgroup
user's dashboard
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
38
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
REPORT
DASHADDREPORT
Report added to
Yes
IpPerson
1
Tab Id
reportgroup, report
Yes
IpPerson
1
Tab Id
reportgroup
Yes
IpPerson
1
Tab Id
reportgroup
Yes
IpPerson
1
Tab Id
reportgroup, report
Yes
IpPerson
1
Tab Id
reportgroup, parentgroup (if parent group
existing dashboard REPORT
DASHCREATE
New dashboard created
REPORT
DASHDELETE
Tab deleted from user's dashboard
REPORT
DASHDELETEREPORT
Report deleted from a dashboard
REPORT
DASHEDIT
Dashboard edited
exists) REPORT
DASHREMOVED
Corp Dashboard
Yes
IpPerson
1
Tab Id
reportgroup
Yes
IpPerson
1
ReportInstanceId
requestortype, requestor, timetorun, numrows,
removed entirely REPORT
DASHRUN
Report is run from dashboard
REPORT
EMAIL
Report emailed to
report Yes
IpPerson
1
ReportId
message, recipientN, subject
Yes
IpPerson
1
ReportId
filename, exporttype, filesize
someone REPORT
EXPORT
Report saved as external format (pdf, xls etc)
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
39
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
REPORT
FAVEADD
Report added to
Yes
IpPerson
1
ReportId
report
Yes
IpPerson
1
ReportId
report
BroadcastId
ReportId
report, error
favourites list REPORT
FAVEDELETE
Report deleted from favourites list
REPORT
RPTBROADCAST
Scheduled broadcast run
REPORT
RPTCOPY
Report copied
Yes
IpPerson
1
New ReportId
originalreport, newreport
REPORT
RPTCREATE
Report created
Yes
IpPerson
1
ReportId
report
REPORT
RPTDELETE
Report deleted
Yes
IpPerson
1
ReportId
report
REPORT
RPTEDIT
Report edited
Yes
IpPerson
1
ReportId
report
REPORT
RPTREFRESH
Refresh a manual-
Yes
IpPerson
1
ReportId
report
Yes
IpPerson
1
ReportInstanceId
requestortype, requestor, timetorun, numrows,
refresh report REPORT
RPTRUN
Report is run
report REPORT
RPTSEARCH
Report search
Yes
IpPerson
1
99
searchtext
Yes
IpPerson
BroadcastId
ReportId
report
performed REPORT
RPTSUBSCRIBE
User subscribed to a report
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
40
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
REPORT
XMLTOOBIG
VersionHistory flag is
Yes
IpPerson
1
Reportid
ContentManagementId, MaxSize, XMLSize
Yes
IpPerson
1
ContentManagementId
Category, SubCategory, LoginAccess,
on, but xml exceeds max size REPORTADMIN
CATCREATE
Report category created
REPORTADMIN
CATDELETE
Report category
ShortDescription Yes
IpPerson
1
ContentManagementId
deleted REPORTADMIN
CATEDIT
Report category
ShortDescription Yes
IpPerson
1
ContentManagementId
edited REPORTADMIN
REPORTADMIN
COMPOSITEVIEWREFRE
Scheduled composite
SH
view refresh
DELETESCHEDULE
Scheduled task
REPORTADMIN
DISTRIBUTEDASH
DISTRIBUTEREPORT
Dashboard tab
Category, SubCategory, LoginAccess, ShortDescription
Yes
IpPerson
1
ViewId
view, error
1
99
ScheduleSubjectCode, ScheduleUnitCode,
deleted REPORTADMIN
Category, SubCategory, LoginAccess,
ScheduleUnitId Yes
IpPerson
1
Tab Id
fullname (person sending the tab), userId
distributed to another
(userid of person sending the tab), tabId,
user
recipient (ipPerson)
Report distributed to
Yes
IpPerson
1
ReportId
another user
fullname (person sending the report), userId (userid of person sending the report), reportId, recipient (ipPerson)
REPORTADMIN
KILLSESSION
Session killed
Copyright Yellowfin International Pty Ltd 2010
Yes
IpPerson
1
www.yellowfin.com.au
99
KilledSessionId, UserName, UserId
guide_admin_v5.0.0.doc
41
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
REPORTADMIN
LICENCELOADED
New Licence file
Yes
IpPerson
1
DocumentId
DocumentId
loaded REPORTADMIN
SOURCECREATE
Data source created
Yes
IpPerson
1
SourceId
name, access, url, username
REPORTADMIN
SOURCEDELETE
Data source deleted
Yes
IpPerson
1
SourceId
name, access, url, username
REPORTADMIN
SOURCEEDIT
Data source edited
Yes
IpPerson
1
SourceId
name, access, url, username
REPORTADMIN
SOURCEFILTERREFRES
Scheduled source
1
ReportTaskId
source, filter, error
H
filter refresh
REPORTADMIN
UPDATECONFIG
Configuration updated
Yes
IpPerson
1
99
REPORTADMIN
VIEWACTIVATE
View activated
Yes
IpPerson
1
ViewId
name, access, status
REPORTADMIN
VIEWCREATE
View created
Yes
IpPerson
1
ViewId
name, access, status
REPORTADMIN
VIEWDEACTIVATE
View deactivated
Yes
IpPerson
1
ViewId
name, access, status
(changed from active to draft mode) REPORTADMIN
VIEWDELETE
View deleted
Yes
IpPerson
1
ViewId
name, access, status
REPORTADMIN
VIEWEDIT
View edited
Yes
IpPerson
1
ViewId
name, access, status
ROLEADMIN
CREATEROLE
Role created
Yes
IpPerson
1
99
Role
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
42
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
ROLEADMIN
DELETEROLE
Role deleted
Yes
IpPerson
1
99
Role
ROLEADMIN
UPDATEROLE
Role updated
Yes
IpPerson
1
99
Role
SYSTEM
SHUTDOWN
System shutdown
1
99
ShutdownTime
SYSTEM
STARTUP
System startup
1
99
StartupTime
SYSTEMTASK
ADHOC
Used for various
1
99
TaskName, StartTime
1
99
TaskName, CompleteTime
1
99
TaskName, StartTime
background system tasks (adhoc task started) SYSTEMTASK
COMPLETE
Used for various background system tasks (task completed)
SYSTEMTASK
SCHEDULED
Used for various background system tasks (scheduled task started)
USERACCESS
DASHBOARD
Dashboard record
Yes
IpPerson
1
99
message, dashboardid
Yes
IpPerson
1
99
email (userid), browser, AccessType,
cleanup USERACCESS
LOGIN
User logs in
ClientOrg?, ClientRefId?, webservices?
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
43
EventTypeCode
EventCode
Description
SessionId
IpSource
UnitId
ReferenceId
Data
USERACCESS
LOGOUT
User logs out
Yes
IpPerson
1
99
PersonName, PersonId, OrgName, OrgId, Userid
USERACCESS
PASSWORDINVALID
User enters invalid
IpPerson
1
99
attempt, userid
IpPerson
1
99
userid, AccessType, Timeout
IpPerson
1
99
attempt, userid
password during logon attempt USERACCESS
SESSIONTIMEOUT
User's session times
Yes
out USERACCESS
USERLOCKOUT
User enters invalid password 3 times and is locked out
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
44
Report Approvals One of the process management features of Yellowfin is the option of setting up report approval processes. This means that if a user wishes to publish a report to a public directory and they are not authorised to do so the report can be routed to an approver for approval prior to going live. To enable the approval you must set the policy on every report category that you want it applied to. This is described in detail in the Report category chapter later in this guide. The approval process will determine if the user who wishes to save the report has the publish rights for that folder. If not, Yellowfin will determine an expert from the category and send that report for approval to the approver’s inbox. Once approved, the report will be available to all users that have appropriate security access.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
45
Public Report Writers Yellowfin allows you to designate users that are permitted to publish Public Reports. Public reports are reports which are shared by all users with access.
Why designate Public Report Writers? To maintain the integrity of your Public report repository it is advised to limit the number of users that can publish Public reports. These users will usually be the experts in the subject domain. These users will be able to verify that the data displayed in the reports is correct and therefore ensure that only validated reports are accessed by your report consumers.
How to designate users for Public publishing? To be designated as a Public report writer your user must have: 1. Functional access to the capability – ie There role must permit them to be able to publish Public reports. 2. They must be able to save reports as Public reports in the specified report folders that they have access to. This means that either the report category: a. allows all users with functional access to publish Public reports or b. only permits users specified as experts can publish Public reports to the category. Note: This option is used if you wish to restrict users from publishing Public reports in a specified category. To restrict Public publish access either select a person or a group as the expert.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
46
Chapter 3 Data Source Management
Overview This chapter describes how the data source records are managed and maintained. Through the administration module you will be able to create new data sources and associated views. In addition you will be able to manage the security access to the data sources as described in the security management chapter of this document.
What is a Data Source? A data source is record within Yellowfin that contains the connection parameters for one or more database middleware. The source connection information is shared by a number of views created within the application to connect to the appropriate data source when a report is sun.
Source Types Yellowfin supports numerous data sources for you to report off. These include SQL databases such as DB2, Oracle and SQL Server, Lotus Notes Domino, and Yellowfin In-Memory.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
47
Data Source Connection List The source list shows you all the available source systems within Yellowfin. These source records provide connection parameters to your databases. Setting up new data sources allows a user to connect to an external database and define the contents of a view or table. From the dashboard page click the ‘administration’ link on the main navigation at the top of your page. The list page shows you all the data sources that you have access to and the views that have been defined against those sources. Click on the hyper link for either the source or view to access the details.
Figure 20 Source Connection List
Add source connection To add a new source to your connection list click the add connections link. This will open a new source record for you to edit. If you have a license restriction on the number of source systems you can use you will encounter an error if you try and connect a source when all are in use. An option for you will be to delete an existing source to continue.
Edit Source To edit an existing source you can either click the source name hyperlink or tick the check box and click the edit link. This will open the record for editing. Note: you can only edit source record for which you have access rights.
Delete Source To delete a source select the source connection you wish to delete and click the delete link. This will open the confirm page.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
48
If the source is being used, Yellowfin will indicate which views are in use. If you continue to delete the connection the associated views will be delete as well.
Figure 21 Confirm Delete
Source Connection Parameters
Details The detail section contains metadata that assists users select the right source and view for their reports. 1. Type in the Business name and description for the source – this is displayed to an end user when writing reports to assist them to make the correct decision about the source of the data they wish to report off. 2. Using the max row limit you can constrain the maximum size of a query against the source – you may choose unlimited as depicted. 3. Allowing the database to be Writable will enable you to use this source to store either view caching data or imported spreadsheet data. 4. If you have set the source to writable you will have the option to make the source the default storage for spreadsheet imports. 5. Define the time zone region for the source – this is used for managing scheduling and broadcasting if your users are located in a different time zone from the source system.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
49
Figure 22 Source Details
Security Setting The security settings allow you to determine whether access to the connection needs to be restricted, whether to apply source filters and whether broadcasts are permitted. 1. Select Unsecure or Secure. If you select secure an additional step will be added to your connection wizard. 2. Permissions against the source allow you to determine what actions are permitted. You may choose to stop users from creating broadcast reports or subscribing to reports that are initiated from the source. 3. Source Filters are used to create Privatised filters for users based on data within the source.
Figure 23 Security Settings
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
50
Connection Parameters The connection to the source system must be specified. The connection parameters define how Yellowfin accesses a database. 1. Select you connection method. Generally this will be JDBC. Note: Some data sources such as Access and Lotus Notes will require an ODBC connection. Please refer to specific Connection documentation for non-standard SQL data sources. 2. Select a driver appropriate for your database type. If the appropriate driver, for your data source, is not in the list then you will have to copy the JDBC driver to: programfiles\appserver\webapps\root\web-inf\lib 3. Define the Database URL: The connection string is of the format: jdbc:://:/ where the is the specified driver string for the db, is hostname or IP address, is the TCP/IP port that the database listens on and is the database you are attempting to connect to. 4. Insert the username and password for the Yellowfin to connect to and access the database. Your password will be encrypted when stored in the Yellowfin repository. 5. Select your Schema – the scheme is used internally to partition tables within the database. Select the appropriate schema for the Yellowfin user. 6. To test the connection, click the ‘test connection link’. If successful a connection succeeded message will be displayed. You should also see a list of tables found using the selected database schema.
Figure 4 Connection Test Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
51
In-Memory Connection Parameters Connection settings for the in-memory database don’t have variables so they are listed here; separate from the settings for other standard external databases. 1. Select JDBC as your connection method. 2. Set the driver to: com.hof.mi.database.jdbc.ColumnarDriver 3. Define the Database URL as: jdbc:columnar://localhost
Figure 4 In-Memory Connection 4. You will not be required to set a username or password for this connection, so just leave the fields blank. 5. To test the connection, click the ‘test connection link’. There will be a popup message informing you that no username was set, click OK. If successful a connection succeeded message will be displayed.
Figure 4 In-Memory Connection Test 6. The source is now ready for use to cache views to.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
52
Database Specific URL Formats Each database platform has its own specific connection string parameters. The table below provides you with a guideline for completing the database URL. The port numbers provided are the default port numbers for the specified database. You will need to confirm the port details for the database that you are connecting to.
Name
URLFormat
DB2 UDB
jdbc:db2://:/
HSQLDB server
jdbc:hsqldb:hsql://:/
HSQLDB embedded
jdbc:hsqldb:
IBM AS/400
jdbc:as400://naming=sql;errors=full
Informix
jdbc:informix-sqli://:/:informixserver=
JDataStore
jdbc:borland:dslocal:
JDBC/ODBC Bridge
jdbc:odbc:
MySQL
jdbc:mysql://:/
Oracle Thin
jdbc:oracle:thin:@::
Oracle OCI
jdbc:oracle:oci:@::
PostgreSQL
jdbc:postgresql://:/
Progress
jdbc:jdbcProgress:T:::
SQL Server (jTDS)
jdbc:jtds:sqlserver://:;DatabaseName=
Sybase ASE (JConnect)
jdbc:sybase:Tds::/
Sybase ASA (JConnect)
jdbc:sybase:Tds::/
Yellowfin In-Memory
jdbc:columnar://localhost
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
53
ODBC Connections - JDBC-ODBC Bridging If you intend to connect Yellowfin to a database which does not have a native JDBC driver (eg Lotus Notes and MS Access) then you will need to use a JDBC-ODBC connector. You will still select connection method and JDBC but the driver you select will have to be a bridge to the ODBC source. Included with the standard Java Runtime environment is the Sun JDBC-ODBC driver. This driver allows a JDBC connection to an existing ODBC source. In all cases it is more reliable to connect to a database via a native JDBC driver. In the case that a JDBC driver doesn’t exist for the source database, this may be a viable alternative. On the database source page, by default, the Sun JDBC-ODBC driver will be available in the driver drop down. It is listed as sun.jdbc.odbc.jdbcodbcDriver. The syntax for this driver is: jdbc:odbc:[;=] where data-source-name is a registered ODBC data-source, followed by any datasource specific attributes. For more information about the Sun JDBC-ODBC driver please visit: http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/bridge.doc.html rd
Note: You can also consider using 3 party JDBC-ODBC bridges that have been optimised for the database you wish to connect to. Please see the installation manual for more details.
Connection Management The connection management section provides the mechanism for optimising the connections to the data source. 1. Complete the Minimum and Maximum connections. These determine the number of connection within the connection pool. For example if you have a maximum of 5 connections and 5 users create very large reports at the same time no other user will be able to access the source until these reports have completed processing. Yellowfin automatically creates and keeps the minimum connects open and will use up-to the maximum if required. Setting the connections permits you to optimise system resources. 2. Refresh Time is hours before the database connections are refreshed.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
54
The refresh recycles the connections after the set period. This is done to ensure that no connections have become stale / or been left open even though the user is no longer using them. 3. The timeout is the time that is allowed for the SQL statement to return results. If queries against this source are likely to be large this field will have to be set to accommodate them. 4. The JDBC log and logging level are used to log errors – insert a file path and file name for the log.
Figure 24 Connection Management
Connection Availability To disconnect from a source database you can make a connection unavailable. This is useful if the DBA of the source database requires all connections to that database to be closed. Select unavailable from the connection management options. Note: Whilst the connection is closed users will not be able to edit or add views or run any reports from the source.
OLAP Source Connection An OLAP source connection differs from a standard JDBC connection. Select XMLA OLAP as the connection type. Refer to OLAP connection documentation for details for each OLAP source type.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
55
Figure 25 OLAP Connection
Database Connection Issues Some databases require specific configurations for JDBC connections to be successful. These issues and the resolution of them are outside the context of this guide. Please refer to individual guides for your specific database, for example: Access, Progress and Lotus Notes.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
56
Source Security Security on the source connection record is used to set who has access to the source system. You have two options 1. Unsecure – this allows all users to access the source system for writing SQL queries or creating new views (in both cases the user must still have a role which grants them functional access to these actions) or 2. Secure. By securing the source system you will control: i. Who can view or edit the source record ii. Who has the ability to create new views against the source. iii. Who can write SQL or Jasper reports directly against the source.
Securing the Connection 1. Once you have selected the ‘secure’ option from the connection details page the security management step will be displayed. 2. You will need to search for which users or groups have access to the source. Click the search link and select the user or group required.
Figure 26 People Search 3. Select the appropriate access level. Note at least one user must have DELETE access – else the source will be locked to all users for update purposes. 4. Click the add button to add them to the access list. You can add as many users or groups as users to the list as appropriate.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
57
Figure 27 Access Level
Source Access Filters Access Filters are used to restrict data based on the user running a report. A manager may only be allowed to see employee details from his or her own department, for example. Access Filters match users within Yellowfin to an arbitrary Reference Id. The Reference Id for the user can then be used as a filter when they run reports. The steps for setting up Access Filters are: 1. Create an Access Filter on the data source 2. Assign the Access Filter to a field on a view 3. Define a default Access Filter on the view 4. Assign an Access Filter to a report
Creating an Access Filter 1. Select source filters on the connection details page. This will make the source filter step available in the wizard. 2. Click Add to add in a new filter or edit to edit an existing record. If you click add you will be prompted for the type of filter you wish to create.
Figure 28 Filter Entry Type
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
58
Filter Parameters Each Access Filter contains a list of one or more records matching a Yellowfin user to a Reference Id. These can be loaded into the system in three ways: manual entry, from a CSV file, or by running a scheduled query on the source database. In each case you will need to enter records consisting of four fields: Identifier Type
The Identifier Type and Identifier are used to identify a Yellowfin user. The Identifier Type can be either “Email Address”, “Yellowfin Person ID” or “User ID”. The user id related to the user id used within Yellowfin – by default this is an email address but can be changed by your administrator to use an alternative identifier such as an LDAP id.
Identifier
The Identifier field will then be the corresponding user id.
Filter Type
The Filter Type field is used to determine what type of filter this is. This is critical to assist users when creating views or reports. Select a user friendly type name. A list of defined Filter Type will be shown on the right side of the screen when you are creating the filter. To add or delete Filter Types, click the Edit link. Only types in this list can be used as Reference Types.
Reference Id
The Reference Id field is the corresponding data for the matching user.
Refresh Type Access Filters also have a Refresh Type option, which allows you to specify how the filter will be processed when changes are made. The default option is for updates to be appended to the existing entries. In this case, the new records entered will be added to those already saved. If you choose to overwrite the existing entries, any previous records will be discarded before saving the new records.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
59
Defining a Filter Type Each source filter that you create needs to be categorised. You can share categories across various source systems but filters must have unique filter types within a source record. The filter type is used to link the data in your source system with Yellowfin meta-data. It also provides a mechanism to categorise the type of filters that you can make available for your views.
1. From the Source entry page you will see the Available filter types on the right hand side of the page. Click Edit to add new filter types.
Figure 29 Filter Type 2. From the popup click the add link to add a new filter type. This will open a section at the bottom of the popup. Enter a code and description of the filter type you are creating.
Figure 30 Filter Add
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
60
3. Once you have created a new type click the add link and then the OK link to save all your changes. This will take you back to the main access filter page and you should see a filter type specified in the available list.
Figure 31 Filter Type List 4. If you wish to delete a filter type then from the edit popup select a type and click the delete link. You will be promted to click OK to save your changes. Note: If you delete a type which is in use this may impact your reporting filter process.
Figure 32 Delete Filter Type from list
Manual Entry When you create an Access Filter manually, you will be presented with a list of records to fill in. Initially the page displays three records, but you can add more records by clicking the “More” link underneath. When you save the filter, empty records will be discarded.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
61
Figure 33 Manual Filter Load
Note: If records exist you can view these by clicking the view record link located at the bottom of the entry section.
Upload Data File When you choose to create an Access Filter by uploading a data file, the screen will show a file selection box. Click on the Browse button to open the CSV file containing the filter records. The file must consist of records of the four fields listed above. The file may contain a header row, which must have the titles “Identifier Type”, “Identifier”, “Reference Type” and “Reference Id”.
Figure 34 File Filter Load
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
62
Scheduled SQL Query When you create an Access Filter using the scheduled query option, you will need to enter a SQL query and schedule it for refresh. The query will run against the source database, and must return four fields corresponding to the fields described above. Click on the Test Query link to run the query and report any errors. This will test the SQL syntax, but will not validate the data returned.
Figure 35 SQL Filter Load
Assigning an Access Filter to a View Once you have set up one or more Access Filters on a data source, you can assign them to any views created against that source. 1. Create your view as normal, and then on the Fields page, select a field that corresponds to one of the Reference Types you have created. 2. On the Access tab, select the correct type from the Access Filter selection. You will not be able to select Reference Types that don’t have any filter records saved against them.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
63
3. Once you have selected the Access Filter, save the field. You can select more than one field to be different Access Filter types if you wish.
Figure 36 Apply Filter to Column
4. Once you have finished assigning access filter types to the fields on the view, go to the View Security page. You can select a default filter to be applied to reports created against this view here, or choose not to have one. If you select a default filter, any subsequent report created against this view will have the filter applied. Users creating reports that have the appropriate access level can change or remove the filter on a per-report basis.
Figure 37 Set Default Filter
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
64
Assigning an Access Filter to a Report When creating a report using the Drag and Drop Builder, an Access Filter can be applied if the selected View has had Access Filters defined. On the Report Data page, open the Report Data section in the Report Options box on the right hand side. The Access Filter selection box contains any filters that have been attached to the selected view. If you select a filter, it will be applied to the results for each user when they run the report. Note: that only users with the appropriate security access level will be able to change the Access Filter on a report.
Figure 38 Set Default Filter
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
65
Chapter 4 View Builder Introduction
Overview A view within Yellowfin is a metadata layer of a physical view or table within your data source. This is a subsection, or segment, of data in your data source. The view record within Yellowfin contains the following: •
SQL structures called items that map to actual SQL structures in the database such as columns, tables, and database functions. Items are grouped into categories. Items and categories are visible to report writers through the report builder interface and as data presented in a report.
•
A schema of the table or view used in the database. Items are built from the database structures that have include in your source schema. The schema is only available to Yellowfin report writers. It is not visible to report writers.
Report writers select a view to run queries against a database. They can do data analysis and create reports using the items in the view, without seeing, or having to know anything about, the underlying data structures in the database. The role of a view is to provide an intuitive interface for non technical report writers to run reports and perform data analysis against a database.
Who is the view Administrator? Views are created by an administrator. There is no standard profile for a view administrator. Within a company, the person designated as the view administrator may be the database administrator, an applications manager or developer, a project manager, or a business user who has acquired enough technical skills to create views for other users. A view administrator should have the following skills and level of technical knowledge: Skill/Knowledge
Description
Ability to analyse user needs
The view administrator must have the skills to conduct user needs analyses to create categories and items that are relevant to the user vocabulary,
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
66
and to develop views that meet the needs of the user community. Database knowledge
A View administrator needs to have a good working knowledge of the company’s database management system (DBMS), how the databases are deployed, the logical database structure, and the type of data stored in company databases
Stuctured Query Language
A working knowledge of SQL is necessary
(SQL)
What does a view contain? A view contains the following structures: Categories
The purpose of categories is to provide logical groupings of items within a view. The name of a category should intuitive to the business user and provide an indication of the items that it is likely to contain. For example a category called ‘Private details’ is likely to contain a person’s name, age and gender.
Items
An item is a named component that maps to data or a derivation of data in the database. The name of an item should be drawn from the business vocabulary of the targeted user group. For example, items used in a view used by a product manager could be Product, Life Cycle, or Release Date. A view used by a financial analyst could contain items such as Profit Margin, and Return on Investment. The items that report writers see in a view infer SQL structures that have been inserted into a database schema.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
67
Item / Field Types When creating a VIEW, you define and categories items. The definition of an item reveals how it can be used in analysis and reports. An item can be defined as a dimension or a metric. Each type of item serves a different purpose:
Dimension items retrieve the data that will provide the basis for analysis in a report. Dimensions typically retrieve character-type data (employee names, company names, etc.), or dates (years, quarters, etc.) Metric items retrieve numeric data that is the result of calculations on data in the database. Metrics tend to be dynamic: the values they return depend on the dimensions they are used with. For example, if you include Person and Age in a query, Age per person is calculated. Basic Metrics do not need to have an aggregate calculation (such as a SUM or AVERAGE) performed on them within a report. Pre-Defined Filters are items where a set of conditions have been set up when the view was created. This assists users to limit the data returned in a query to only the expected results. For example if the filter is called ‘United States’ then only data from the united states would be included in the results. Parameters are items which are used to capture user defined values and pass them into calculated fields or filters. These parameters can assist in conducting what if analysis.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
68
How are views used? Views are used by Yellowfin report writers. The view meta-data is stored within the Centralised Yellowfin repository. An end user connects to a view via a web browser when running a report. By using a view, the end user automatically has access to data within your source system. Access to data is restricted by the items that are available in the view. These items have been created by the administrator based on the report users needs.
Assisting Data Analysis A view can represent the data needs of any specific application, system, or group of users. For example, a view can contain items that represent the data needs of the Marketing or Accounting departments in a company. A view can also represent the data needs of a section within a department or any set of organized procedures such as a payroll or inventory system.
Who uses views? Yellowfin report writers use views for reporting and analysis. The view should provide them with categories and items relevant to their business domain.
How are items presented for report writers? Items are displayed as nodes in a folder tree as shown below.
Figure 39 View Data Fields
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
69
View design methodology The view design methodology consists of four stages: 1. Analysis of business problem and planning the view solution 2. Building the view 3. Defining Items and Creating Calculated Items 4. Publishing the view to users Each implementation phase is based on an assumption that you have completed an initial planning phase.
1. Plan the view The analysis of user requirements and design are the most important stages in the process. Users must be heavily involved in the development process if the view is going to fulfil their needs both with the business language used to name items and the data that can be accessed. Implementation will be very quick and easy if this stage is carried out properly. You should note the following points: a. You must fully understand the data analysis and reporting needs of the target audience for the view. Do not create items by looking at the columns available in the database, but identify columns that are required as a result your user needs analysis. b. Understand the source system data and business rules required for generating the required items for users. 2. Building the view You create a entity relationship diagram for the underlying database structure of your view. This includes the selecting the appropriate tables and columns of the source database and the joins by which they are linked. 3. Defining Items Select columns form your source system tables and organise these items into categories. These are items that you have identified from an analysis of user reporting needs. You can create additional calculated items and filters to enhance user reporting capabilities and optimise query performance. Test the integrity of your view structure. You should also perform tests using the report writer on the view.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
70
4. Publish the View You can publish your view to users for testing, and eventually for production use, by expanding the number of users that have access to the view. The table below outlines the major phases in a typical view development cycle: Development phase Prepare
Description Identify the target data source and become familiar with its structure. Know what data is contained within each table required for the view and the joins that related the tables to each other.
Analyse
Identify what information the users need. Identify what standard reports they require. Familiarise yourself with their business terminology so that you can name items sensibly. Plan Identify a project strategy. For example, how many views should be created and which ones should have the capacity to be linked and to what level.
Implement
Build the view either on the database or through the Yellowfin view builder. Test frequently during the build process for validity and reliability of inferred SQL.
Test
Form a small group of users, preferably power users who have some knowledge of what information they expect to get from the view. Pre-Release the view to these users by adding them the access security list for the view. Ask the users to perform thorough tests simulating live usage of the view(s).
Deploy
Migrate the view from your Test to Production environments. Change access security of the view so that it is available to the target user base.
Evolve
Update and maintain the view as the data sources and user requirements change and grow.
Note: View design should always be driven primarily by user requirements and NOT the data source structure. Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
71
Chapter 5 View Creation & Management
Overview The views within Yellowfin provide the link between users and their reports and the database. The purpose of the view is to simplify the knowledge required by end users of the source database. This section describes some of the basic view management options available to you.
Create a New View 1. To create a new view click the add view link from within the source record.
Figure 40 Create View 2. The view initiate page will prompt a select of the type of view you wish to create and the data source connection to use. The choices you have are:
Drag and Drop
Use the Yellowfin builder to create a view on your database.
Freehand SQL
Use an SQL statement to create your view
Composite View
Use to create a view which connect to multiple separate data sources and views.
OLAP
Select an existing OLAP cube – you will not be able to add additional attributes to a cube.
Stored Procedure
The ability to create a view from existing stored procedures on you database.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
72
Figure 41 View Options 3. Select a category for the view. This is used to limit the field item categories to an appropriate set. For example only Sales Related field categories will be displayed for a sales view. 4. To create a new category click the create category link. This will open a popup window in which you can add a new category.
Drag and Drop Builder The drag and drop builder is covered in detail in subsequent chapters. This option allows you to use Yellowfin to simply join multiple tables from a single selected database through a web interface.
SQL view The freehand SQL option allows you write your own view directly into the Yellowfin repository. The tables available from the source system are displayed in the table list on the left hand side.
Figure 42 SQL builder Note: When writing an SQL view it is very important that you do not include an order by – since results are sorted by the Java application not the database. The order by will cause your view to fail. Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
73
Composite Views A composite view allows you to create a view which joins multiple data sources together. This allows users to create a report which includes data from these multiple databases. The view builder for a composite view differs in that rather than displaying tables from a selected database it allows you to join existing Yellowfin views together and create virtual tables from any defined database. 1. To create a composite view select the composite view option from the author drop down. 2. You will also need to select a database in which you wish to store the view results. This is needed since composite views can be highly complex and result in slow query performance by your users. As a result Yellowfin schedules a query to generate the view and all its data on a regular basis to be stored in a specific warehouse database.
Figure 43 Composite View Note: Creating a composite view is covered in detail in a subsequent chapter of this guide.
OLAP Cube View An OLAP cube is a pre-aggregated structure on your database. Like a data mart it already contains a set of dimensions and measures which have been pre-aggregated to support rapid reporting. 1. To create an OLAP cube view select OLAP cube from the author drop down menu. 2. Choose the source connection – Yellowfin will only provide OLAP source connections for you to choose from.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
74
If no source exists you will have to create a specific OLAP source connection record. 3. Choose the cube from a list of available cubes.
Figure 44 OLAP View Note: When creating an OLAP cube as a view you will have limited options for updating field information. This is because most of the metadata is already contained in the cube itself.
Stored Procedures With Yellowfin you can connect to stored procedures running on your application database. 1. To connect to a stored procedure select the stored procedure options from the create view drop down list. 2. Select the Database and then the stored procedure you wish to connect to.
Figure 45 Stored Procedure View Creation
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
75
3. Click the next arrow to continue to the stores procedure parameters page. Yellowfin will automatically detect parameters which exist in your stored procedure and display this list on the page. 4. You must either mark a parameter as a return field or fill a value for the parameter for non-return fields. The value is only used to return column at this stage of the view builder. (The non-return fields will become automatic filters on the reports created from this stored procedure). 5. When you have completed the parameter section click accept to return the list of fields in the result set.
Figure 46 Stored Procedure Parameters
6. Complete the metadata as you would for a standard view. Note that the nonreturn parameters exist as filter fields.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
76
Figure 47 Stored Procedure Meta data
7. Filters will be added into each report created from the stored procedure automatically. They will not be visible on the report data page but will be displayed on the report filter page.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
77
View Options Once you are editing a view you will be able to edit a number of options within the view.
Description Complete the View Business name, description and audience– this is displayed to an end user when writing reports to assist them to make the correct decision about the source of the data they wish to report off.
Figure 48 View Options
Caching and Performance The Default Cache period is used to store queried data in a cache and reused by multiple report users. If the data in your source system is not time sensitive then you can set the cache time to be high and limit the impact of report users on the source system.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
78
Row Limits You can set the maximum row to be returned for any report written from this view.
Category If you wish you can change the view category – this will change the field categories available
Securing a View Security on the view is used to set who has access to the view. The three options available are: a. Unsecure – all users with functional access to the database can write reports against this view. b. Edit restricted – only defined
Figure 49 View Security
users will be able to create a report using this data. All other users will be able to read the reports that have been created by authorised users. c.
Restricted – only defined users can read or write reports from data in this view.
1.
Once you have selected the ‘secure’ option from the view option the security management step will be displayed.
2.
You will need to search for which users or groups require access. Click the search link and select the user or group required.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
79
Figure 50 People Search
3.
Select the appropriate access level.
4.
Click the add button to add them to the access list. You can add as many users or groups as users to the list as appropriate.
Figure 51 Access Level
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
80
View Summary The view summary allows you to browse the contents of a view without editing it. From the summary you will be able to access information about the purpose of the view and target audience, the columns and tables that are included in the view, view a sub set of the data produced, access the generated SQL and export the view metadata and XML file.
General Information The general tab provides a View summary, including the purpose and the intended audience, and the option to edit this information using the
Edit Metadata link. In
addition you will be able to export a view summary PDF and a View XML definition. The XML definition can be used to transfer a view from a test to a production environment.
Figure 52 View Summary - General
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
81
Columns The columns tab provides summary information of all the columns that are included in the view.
Figure 53 View Summary - Columns
Data The data tab shows the first 20 rows of data returned from the data source. This allows you to verify that the view does return the anticipated result set.
Figure 54 View Summary - Data
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
82
SQL The SQL tab displays the SQL that has been generated as a result of the business rules implied through the entity relationship diagram and the columns that have been created. You can use this tab to verify that the view you have built is generating the expected output SQL.
Figure 55 View Summary - SQL
Elements The elements tab provides a list of all source tables or views used in the view. The source database name, its description and alias are provided. In addition any joins and conditions are specified in detail.
Figure 56 View Summary - Elements
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
83
Reports This tab provides a list of all reports that use this particular view. This is useful when you want to know what reports may be effected by changes made to a view.
Figure 57 View Summary - Reports
Cache This tab provides view caching information. You can use this tab to populate the data manually and view the caching settings.
Figure 58 View Summary - Cache
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
84
View Management
Saving a View Saving a view enables you to make the view active. Only views which are active are able to be used by report writers for report creation. 1. From the view summary page click the save link 2. Complete the form – most of the information may already have been completed. 3. Update the status of the view from Draft to Active. Note: Only ACTIVE views are available for reporting by end users. 4. Click Save to save the view as Active and return to the View Summary page.
Figure 59 Save View
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
85
Editing a View If you wish to make changes to a view once it has been activated open you will have to take it through the change management process. This is in place to ensure that the view is not corrupted if being used for reports. Examples of the corruption that could occur may be that columns are deleted which are being used in existing reports. 1. To edit a view click the edit link from the view summary page. This link is only visible if the view is ACTIVE. 2. Select if you wish to ‘clone’ or ‘edit the existing’ view. If you edit the existing view then no users will be able to run reports against that view until you have activated it. Any changes you make to the view may impact existing reports and corruption may occur. You will also not be given the option to rollback the view to its previous state.
Figure 60 Edit Existing View
If you clone a view then the changes are made independently of report users. On activation the view will replace the previous version.
Figure 61 Clone View
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
86
3. Edit the view as required. Save and activate once changes have been completed.
Figure 62 Clone View Activation
4. When activating the view you will be prompted to update the view name and add a description of the changes made. This information will be available when rolling back the view.
Figure 63 Activate Changes
Rollback a View If you have made changes to a view via a clone, once it’s saved you will have the option to rollback to the previous version. You can rollback as many times as you’ve made changes. 1. Click on the Rollback link found on the View Summary page. This will only be enabled if a clone has been saved.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
87
Figure 64 Rollback View
2. You will now notice there is an extra option on the Edit View page allowing you to view the change description of the current view version and Rollback those changes.
Figure 65 Rollback Changes
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
88
Deleting a View 1. To delete a view select the view from the views list by ticking the checkbox. Click the Delete link. 2. You will have to confirm your deletion. The confirm page will show you how many active reports are currently attached to the view. If you delete the view all these active reports will be deleted as well. 3. Click delete to confirm deletion
Figure 66 Delete View
Copying a View If you wish to copy a pre-existing view and use it as a base for a new view you can do so by selecting the view you wish to copy from the view list and clicking the copy link. This will copy the view and take you into edit mode.
Caching a View View caching allows you to store the data a view returns into a nominated writable database (including the in-memory database). To do this you must already have a writable source connection established. 1. On the Cache tab of the view summary page, click on the Create a cached copy of this view link.
Figure 67 Cache View
2. You will now have a series of options to configure. Select the name of the data store you have setup.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
89
Figure 68 Configure Caching Settings
3. Decide if you wish the caching to be incrementally loaded. If Yes, you will be prompted to select an incremental load key from the view. This field must be an incrementing column as it will be used to determine which records are new since the previous refresh.
Figure 69 Incremental Load
4. Once you have set the refresh schedule, click Save. The view is now set to regularly cache its data, but will wait for the scheduled date to populate the first set of data. If you wish to populate the data manually click the Populate Data Now link.
Figure 70 Populate Data Manually
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
90
5. Yellowfin will now populate the data. Depending on the number of records and the complexity of the view logic, this task may take several minutes. You can now click on the data tab to view a snapshot of the data.
Figure 71 View Snapshot of Cached View Data
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
91
View Categorisation When creating or editing a view you will have the option to select a view category. This category is used in a number of ways. Note: For some of these options to occur you must set your view categories to be visible from the administrator’s configuration page.
Field Category List The view category is used to limit the available folders for field categories. For example if you have sales, financial and HR reporting it is likely that the field metadata will be quite unique for each of the areas. Therefore being able to limit the number of field categories makes it easier to create views and assign fields. In the example below the available list of field categories is limited by what is contained in the Yellowfin view list.
Figure 72 Field View category
View List Category If you are reporting of a large complex application you can use the view categories to assist you to limit the views displayed when managing your views. For example: when connecting to an ERP application. In the example below you can see another folder below the data source folder which contains a set of views.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
92
Note: you must have ‘Filter Views by Category’ switched on, on the configuration tab, for this option to be visible.
Figure 73 View category list
Create Report View Category The view category is also used to assist users to select an appropriate view when building reports. In the example below the view category is displayed when creating a report. Note: you must have ‘Filter Views by Category’ switched on, on the configuration tab, for this option to be visible.
Figure 74 Initialise Report View category
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
93
Chapter 6 View Tables, Joins & Conditions
Overview Yellowfin provides you with the capability to create Views of your database through a drag and drop builder or as a straight SQL statement. This section describes how to use the view builder to create views of your database that end users can access for reporting purposes. These views are built by first creating an entity relationship diagram which joins all required tables together. This chapter is only relevant if you have selected the Drag and Drop option from the initial view options.
Benefits of the Yellowfin View Builder By using the Yellowfin view builder you can optimise the SQL that will be generated by the query. For example if you create a view where multiple tables are joined – Yellowfin will only select data from the tables necessary to return data required for the report. This means that you can create relatively complex views without compromising on performance. For example you may want to build a fairly complex view that provides significant flexibility to your end users (more fields and tables). In general reports that are written will not include all possible fields. In this way you can deliver less views to your end users therefore reducing the administration complexity.
What is a Relationship Diagram? A relationship diagram is a representation of a database view that shows the tables and joins between those tables. Using the Yellowfin View Builder you can create a relationship diagram for a sub set of your source database. The relationship diagram contains tables and joins. The tables contain columns to which you apply a metadata so that business end users can use for creating reports.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
94
The joins link the tables so that the correct data is returned for queries that are run on more than one table. You build you relationship diagram onto you canvas by selecting tables from the source database using table selector. You create joins to link the tables. When you have built the relationship diagram for your view, you can test the view using integrity and impact analysis checks.
Figure 75 Entity Relationship Diagram
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
95
Drag and Drop Builder The drag and drop builder is used to build your relationship diagram by dragging tables onto your canvas.
1. The process wizard assists you to move between the various steps in building your view. 2. The search and expand option allows you to search for tables in your data base 3. The virtual table allows you to create your own tables using freehand SQL. 4. The data base tables allow you to view all the available tables in your data source. 5. Drag the table to resize the table list and expand the size of your canvas. 6. The canvas – it is on this section that you drop your tables and create joins between them. 7. Create Joins that link your tables together 8. View options – manage the view details and table properties.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
96
Database Table List The list of database tables assists you to build your relationship diagram. Use the Table List to view and select tables in your database that you want to add to your view.
Figure 76 Table List
By expanding the node next to a table name you display the fields for the table. The list also provides an indication if the element selected is a table or a database view. Database table Database View Virtual Table Note: the number of default tables shown is a configuration item – 50 is the default
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
97
Drag Table onto Canvas You start building your view by selecting available tables from the source system and dragging them onto the Design Canvas.
Multiple Table Versions & Aliases In your view you can drag multiple versions of the same table. As in the example above you can drag program onto the canvas more than once. This may be needed if you which to access the same table but in different ways.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
98
Table Properties You can update or view the properties of any table in your view by clicking on the properties link on the table. When you click on the link the View Options property section is updated with the selected table information.
Figure 77 Table Options
Create Alias When adding more than one of the same table to a view Yellowfin automatically creates table aliases, since two tables cannot have the same name. An alias is created by updating the table name. To change the table name you can update the display name in the options menu. Note: that the source name continues to be displayed in the original format.
Figure 78 Update Alias Note: once columns have been selected and meta data applied to them the table alias can no longer be edited.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
99
View Sample Data If you wish to see example data from the table that is currently being updated you can click the view data link located in the main table properties section or on the table within the table on the entity builder. Any conditions that have been applied to the table will be applied to the select statement for the example data. However, if the joins to the table have implied conditions these will not be included in the example select data.
Figure 79 Table Data
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
100
Select Table Columns When tables are selected and joined together you have the option to add as many or as few of the columns from each of the tables into your view. 1. To add a field into your view click the table properties link to display the properties in the View Options pane. 2. Click the expand icon on the column section 3. Select the columns you wish to add (Use the Select All link to select all columns) 4. Columns selected will be displayed in bold within the table on the canvas – as depicted below. 5. You can also add columns from the table data view – see above. Simply click on the column to highlight the column – these will then be added to your column list.
Figure 80 Table Column Selection
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
101
Joining Tables Once you have dragged more than one table onto your canvas, you need to create joins between related tables. Joins are critical to the relationship diagram as they allow the meaningful combination of data from multiple tables.
What is a join? A join is a clause that combines rows from two or more tables. Joins are used to ensure that queries containing data from multiple tables do not return an incorrect result. The tables usually have a parent-child relationship. If a query does not contain a join, the database returns a result set that contains all possible combinations of the rows in the query tables. Such a result set is known as a Cartesian product and is rarely useful. Note: With the Yellowfin you cannot create loop joins. A loop join is where a table is joined to multiple tables that join back on themselves creating a closed loop.
Join Types Inner Joins An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.
Outer Joins An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. The outer join is specified by selecting the appropriate ‘From Table’ as the starting point for the join builder. The outer table contains the column for which you want to return all values, even if they are unmatched. Note: The view builder has the limitation of not being able to have an INNER JOIN on the discretionary end of an OUTER JOIN. If you have a join error whilst creating an outer join you can change the direction of the join creation.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
102
For example: So if you are currently creating a outer join from Table A to Table B, go to Table B and create the outer join back to Table A instead.
Cross Join A cross join (sometimes called a Cartesian join) is a join that will include all rows from one table with all rows from the other table. No matching will be applied.
Creating and editing a Join 1. To create a new join click on the Join icon on the table you wish to include in the join. This will open the join popup.
Figure 81 Create Join 2. Select the join type and the table you wish to join to Note: you can also use the detect join option if the tables have key fields with the same name, 3. Select the ‘From Column’, the operator and the ‘To Column’ that you want to be included in the join. Click add to add the column relationships to your join. Note: Multiple join columns can be added into the join definition. For more detail on joins details see the conditions section below.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
103
Figure 82 Add Column to Join Properties 4. Click Save and Close to save your join and return to the view canvas 5. The tables will now have a join displayed as a line between them.
Deleting Joins If you want to delete an existing join without deleting the associated tables 1. Click on the Join icon 2. Click the delete join link on the popup and the join will be deleted.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
104
Table Conditions Table conditions permit you to limit the data returned from a specific table. For example you may wish to limit your query of a sales table to specific Product transactions rather than allowing a user to query all data in the table.
Condition Builder
Figure 83 Condition Builder
1.
The item that has been selected on the report data page for filtering. Eg. Cost.
2.
The Operand drop down lets you select what type of filter to be used. For example if the date is greater than 0
4.
The brackets allow for greater complexity in data filtering by using AND and OR with the where clause operator.
5.
The where clause operator allows the user to set multiple filters using AND or OR. For example filter all People where AGE is Greater than 50 or Gender = Female. 6
The move up and down buttons allows the user to build complex
.
bracketing filters. The level of a statement within the bracket will affect the overall filter statement and impact upon the data returned in the query.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
105
7
The condition logic display allows a user to read a summary of the
.
filter that they have defined. Click the refresh link to update the logic if conditions have changed in the form.
When defining a filter initially set the operand drop down to the desired value. The options within the drop down will vary based upon the type of field that the filter is to be applied to. Possible values for the operand include: Operand
Description
Equal to
Equal to a single alphanumeric or string value
Greater than
Greater than a single alphanumeric or string value
Greater than or
Greater than or equal to a single alphanumeric or string
equal to
value
Less than
Less than a single alphanumeric or string value
Less than or
Less than or equal to a single alphanumeric or string value
equal to Different from
Not equal to or different from a single alphanumeric or string value
Between
Between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.
Not Between
Not between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.
In List
One or more alphanumeric or string values
Not In List
Two or more alphanumeric or string values
Is Null
Record contains no value for selected attribute. No Parameter can be set
Is Not Null
Record contains a value for selected attribute. No Parameter can be set
Like
Records that contain the same letter or letters.
Starts With
String starts with letter or letters
Ends With
String end with letter or letters
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
106
Create New Basic Condition 1. Select the table you wish to apply a condition to and click the properties link. This will open the table properties in the View Option Panel. 2. Click the conditions expand icon to show the conditions and click the add link. This will open the conditions popup.
3. Select the columns you wish to place a condition on. Select the operand and enter a value. Click Add to add the condition into the list.
Figure 84 Add Condition 4. Continue to add conditions in this manner – use the AND OR operands and bracketing to create complex conditions. Click the OK link to close the popup window and apply the changes to the conditions section of the table properties.
Figure 85 Updated Conditions
Current Date Conditions Often it is useful to limit the result set in a view by a data condition. For example to only return the last 90 days data. This can be achieved through a Current Date condition on a view table. 1. On the condition popup select the date that you wish to filter on. Based on selecting a date the current date option will be displayed. 2. Select the + - for setting the condition anf the number of days which are relevant. 3. Click add to add the item to your condition list. Click OK to save and close the popup.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
107
Figure 86 Current Date Conditions
Dynamic Conditions A dynamic condition is a condition that is applied based on a user’s preference. For example a user may want to view currency data in their preferred currency. If you have a currency conversion table you will be able to create a calculated field based on a preferred currency. Dynamic conditions can be used in a variety of ways such as converting metric into empirical measures, converting currencies or converting product descriptions into preferred languages.
How they Work The dynamic condition works by allowing you to join you data to a translation table eg Currency. This table will have multiple values for each currency of interest – so normally you would create multiple records if the join had been applied by with no condition. A dynamic condition is then applied to ensure that for each user only one possible currency is used. This Privatises the data based on the currency selection in the user profile.
A Dynamic Condition To create a dynamic condition you will need to do the following. 1. Ensure you have a Dynamic Condition option configured in your reference code – within the User Parameter Folder
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
108
2. Tick the dynamic condition option when creating a join, and select the filter type that you wish to apply. This will be a list of all the valid user parameter reference codes that exist.
3. Choose the default value – this is used as the default if the user has not selected a preferred value in their profile.
Clear Conditions To remove or clear the conditions from a table select the table properties and click the Clear link. This will remove all the conditions associated for that table.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
109
Virtual Table A virtual table allows you to insert an SQL statement into the view which brings back a set of derived fields and used as a logical table in the view builder. Virtual tables have the following advantages: a. Reducing the amount of data returned in a query. You can include complex calculations and functions in a virtual table. These operations are performed before the result set is returned to a report, which saves time and reduces report complexity. b. Reducing maintenance of database summary tables. Virtual tables can, in some cases, replace aggregate tables stored on a database. These aggregate tables are costly to maintain. Derived tables can return the same data and provide real time data analysis.
Create Virtual Table 1. To create a new virtual table drag the virtual table icon from your table list onto the canvas.
Figure 87 Table Drag
2. The virtual table has an additional SQL link, click this to open the SQL popup. You can update the name of the table and insert the SQL to create your derived fields.
Figure 88 SQL Link
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
110
Figure 89 SQL Insert Note: a. Your SQL cannot contain order by statements as this will cause the reports to fail. b. For aggregate columns ensure that you define the column name (as statement). This is required by Yellowfin to generate column names. 3. Click the ‘Validate SQL’ Link to test your SQL against your target database. 4. Click save to save you SQL. This will show you a list of columns returned from your SQL statement.
Figure 90 Virtual Table Columns Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
111
Edit Virtual Table SQL To edit the SQL of your virtual table click the edit link on the virtual table summary page. This will open the SQL editor. If you have already attached columns from your Virtual table to your view you will have to be aware of the impact that any changes you make will have. For example you may drop columns or rename ones that are already in use.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
112
Common View & Join Design Issues
Granularity When designing a view you will have to consider the level of granularity of the view. The granularity It is quite common in parent-child transaction databases to encounter facts of differing granularity. On an order, for example, there may be a shipping charge that applies to the entire order that isn’t available at the individual product-level line item in the operational system. The designer’s first response should be to try to force all the facts down to the lowest level. We strive to flatten the parent-child relationship so that all the rows are at the child level, including facts that are captured operationally at the higher parent level, as illustrated below. This procedure is broadly referred to as allocating. Allocating the parent order facts to the child line-item level is critical if we want the ability to slice and dice and roll up all order facts by all dimensions, including product, which is a common requirement. If the shipping charges and other header-level facts cannot be allocated successfully, then they must be presented in an aggregate table for the overall order. We clearly prefer the allocation approach, if possible, because the separate higher-level fact table has some inherent usability issues. Without allocations, we’d be unable to explore header facts by product because the product isn’t identified in a header-grain fact table. If we are successful in allocating facts down to the lowest level, the problem goes away. We shouldn’t mix fact granularities (for example, order and order line facts) within a single fact table. Instead, we need to either allocate the higher-level facts to a more detailed level or create two separate fact tables to handle the differently grained facts. Allocation is the preferred approach. Optimally, a finance or business team (not the data warehouse team) spearheads the allocation effort.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
113
Indexes A common issue when designing a reporting view for an OLTP Database is that the indexes used for managing transactions are inappropriate for report writing. Whereas an index for transactions may be on the primary key for reporting these may be on multiple dimensional attributes. This can create a conflict of requirements that will determine your overall data architecture strategy depending on the size and complexity of your underlying database. As a result you may have to extract data from your underlying tables into specific reporting tables in the form of a datamart or OLAP cube to ensure optimised reporting.
Outer joins When creating a join with inner joins the "direction" of the join is not relevant, but when creating an outer join the direction does matter. A join rule in Yellowfin is that an inner join cannot reside on the discretionary end of an outer join. For example this will work: CASE_PARTIES inner join CASE STAGES outer join TEAMS But this will not work: TEAMS outer join CASE_PARTIES inner join CASE STAGES If you require complex joins like this you may have to use a virtual table, a SQL view or a hard coded view on the database. Even though the view above would only require simple insertion of brackets, Yellowfin does not cater for complex nested joins such as: A inner join B outer join (C inner join D) outer join E outer join (F inner join G outer join H)
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
114
Chapter 7 View Fields, Meta data & Hierarchies
Overview Once a view has been defined either through the View Builder, SQL clause or straight from the database you will need to define the field definitions and apply business metadata to them.
Figure 91 View Fields 3.
Each field originally selected will initially be unattached. The process of adding columns to the view requires that metadata be applied. Drag columns from the unattached table list to the folders you wish to apply them to.
4.
Fields are categorised into folders. These folders are related to the view category. The purpose of this is to assist end users to find the columns they require. Columns displayed in the list provide some indication of their metadata, such as; hierarchies, dimensions or metrics, and business name.
5.
Click the edit category link to create or delete categories for the current view type.
6.
On the field edit section a set of tabs provides reference to the metadata and business logic you can apply.
7.
The definition tab allows you to change the Business Name and Description of the column, as well as setting it to metric/dimension and active/draft.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
115
Define View Fields A major process of setting up a new view and creating meta data is column definition. Using Yellowfin you can give columns sourced from your database business terminology or create new columns derived from data included in the view. This section includes information on managing and updating the columns included in your view.
Making Fields Available for Reporting Unattached fields are grouped into their source table folders in the Unattached field list. Click on the field name and drag it into the appropriate folder. Note: You can add the same field multiple times into your view. This is useful if you wish to use the same field in multiple hierarchies or with different formatting applied. If you do add the same field multiple times ensure that the business descriptions are unique – since Yellowfin needs to be able to differentiate the fields during import and export processes.
Field Meta Data Each field has to be defined in your view prior to it being used for report generation. Some of the field information is automatically loaded into the field list from the database but additional information is required. 1. Once a field has been added into the main folder section double click the field name to open the edit tabs. 2. Give the field a business name – this is the name displayed on the report. 3. Add the field to a category – this is used on the report data page to group fields into logical groups. If you need new groups click the edit category link. 4. Determine the type of field that it is – dimension or measure. Yellowfin will automatically allocate a type based on the type of field that the column is in the database. For example if the field is a numeric then Yellowfin will default the meta-data type to a metric.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
116
Figure 92 Edit Meta Data
Field Format You can set the default format for a field on the format tab. This will then be the display type when a user adds the column to a report. The report writer may choose to change the format through the report formatting options if they wish. The formats are limited to the data type – For example the data type below is integer so only number based formats are permitted – such as currency, decimal, percentage or time stamp.
Note: The formats displayed in the drop down list are configurable by your administrator. Should you require specific format types which are not catered for then please contact your administrator to have these created for you. Details of this process are specified in the Analytical Functions section of this guide.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
117
Conversion For text fields this lets you convert a text field such as a blob into a geometry type field. This is used where a data base does not natively support GIS columns but the data exists. Yellowfin will convert the text to enable use for GIS visualization.
Figure 93 Blob to Image
Format Based on the type of field that the column being formatted is there are various format options. The ones listed below come default with Yellowfin, however as this is customisable there may be additional ones that comes as part of your installation. Common Format Options Link To URL
Allows you to pass the value of the returned data into a URL link. Use the hashes ## to indicate to Yellowfin where you want the column value to be placed in the url itself. For example: Formatting on a column of IP addresses and the url typed in is: http://www.google.com.au/search?hl=en&q=## This essentially means that every ip address will be placed into it into it i.e: http://www.google.com.au/search?hl=en&q=10.100.32.44
Org Reference Code
Converts the text in the cell to the value of an internal lookup table. Eg. AU to Australia
Raw Formatter
Displayed the data as it would have been returned from the
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
118
database – no additional formatting applied. Text Text
Displays as plain text
Email Address
Creates a hyperlink on the text that will open an email client and pre-populate the sent to address.
URL Hyperlink
Creates a hyperlink on the text and will open web page on click. Assumes the text is a legitimate URL.
Flag Formatter
If your data contains ISO country codes you can display these as flags of the world instead of text. Date
Date
Displays value as a date – multiple date options exist.
Time
Displays value as a time field – multiple date options exist.
Timestamp
Displayed full date and time value
Part Date Fomatter Numeric Numeric
Displays value as a decimal – allows you to set the decimal places to be used.
Percentage Bar
Converts a percentage value less than or equal to 100 into a bar.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
119
Additional Format Options The display options are used to change the data format of the column such as the number of decimal places and the prefix or suffix to be applied.
Option
Description
Sub Format
Depending on the format option you have chosen for the column above you will have a separate set of sub format options. Select the appropriate sub format option.
Date Other
If you select ‘Other’ from the date sub format you will be able to build your own custom date format. For example to create a Japanese date format which includes characters, eg.２００３年４月２ 日 would be created by adding in: yyyy年M月d 日
Decimal Places
If you have a defined a numeric format you can set the number of decimal places to be defined. This can be used to define cents in a decimal place for $20.00 by adding in: 2 Note: To convert numeric data by doing divide by 1,000 calculations etc you would use the data conversion options in advanced functions which are available on the Report Fields page. See the advanced function section of this guide for more detail.
Prefix
The prefix is used to include additional characters before the value that is returned from the data base. This can be used to define currency for $20.00 by adding in: $
Suffix
The suffix is used to include additional characters after the value that is returned from the data base. This can be used to define percentage for 30% by adding in: %
Rounding
The rounding format allows you to choose how a decimal value should be rounded. Round Up
Will round any decimal up eg. 1.1 to 2
Round Down
Will round any decimal down eg. 1.9 to 1
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
120
Round Half Up
Rounds 0.5 and above up
Round Half Down
Rounds 0.5 and below down
Thousand
Turns the defaulted thousand separator for your instance on or off.
Separator
For example: 1000 to 1,000
Semi Additive Fields When the field type is a metric you will be able to set the additive type, which can be set to either Additive or Semi-Additive. The additive type option should be set to Semi-Additive when there are fields upon which the metric is constrained by use (when business rules dictate how the column should be used in a report). When this is the case a new tab labelled Constraint is displayed to enable the user to select these constraints. See the constraint section below for more details.
Default Aggregation When the field type is a metric then you will be able to set the default aggregation (e.g. Sum, Average etc). This is used when adding the metric to a report it will automatically set the aggregation based on this default value. The options in the dropdown when the field type is a date are count, max and min. For all other field types the dropdown options are sum, average, count, max and min
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
121
Reference Codes – Lookup Table Numeric and Text based data displayed in a report can be translated from values held in the database to more meaningful values by adding a reference code to a dimension. This should be used if: 1. You wish to translate a value held in the data source into business terminology. For example if male and female are stored as M and F you may want to translate these into ‘Male’ and ‘Female’ 2. If you wish to apply customised sorting to a field. For example: data returned in a query is usually displayed in Alpha / Numeric sort order. If your result set included: Admin Staff, Managers and Executive Management your report may look like this: Admin Staff
500
Executive Management
10
Management
50
Hierarchically your data may actually need to be displayed in the following way: Executive Management
10
Management
50
Admin Staff
500
This can be achieved by creating reference codes for the values and assigning a sort order to those codes.
Create Reference Code 1. In the format tab the user must select org reference code from the dropdown option; the other option is to choose to display the text which is stored in the database. Selecting to display the org reference code the user is able to create a new type or use an existing type.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
122
Figure 94 Reference Codes 2. To use an existing reference code the user can simply select the code from a dropdown box once they have ticked the existing type option. 3. To create a new reference code the user must tick create a new type option and then select to pre-populate from the database or not. A pop up window will appear so the user can add there new reference code. In this window the user can also edit an existing reference code or delete an existing reference code.
Figure 95 Create new reference codes 4. If you pre-populate from the database you will see values already in the code list. You will have to give the reference code you are defining a name. The is so that this set of translations can be re-used across multiple views.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
123
Editing a reference code When editing a reference code you can change the values and the sort order of the codes stored in your database. Click on the code hyperlink to open the edit section in the lower section of the popup to change the name and sort order.
Figure 96 Edit reference Codes
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
124
Field Access and Usage The access tab is used to determine the access rights to the field. A number of options exist.
Figure 97 Access Tab
Access Filter The access filter is used to indicate the type of field – email address or employee id – this will be used for broadcast reports and automatic filters on reports thereby privatising a report for a report reader. Note: See the Source Access Filter Section more details
Access Level Access level indicates if all users with access to the view can see the field or if it is restricted to a sub set of users and finally if it is secure. If secure an SQL statement where clause can be used to ensure granular security rules. (the associate report functionality will be included in a later release) Columns can be restricted to a user and/or a group. This will allow only those given the permission to write reports with the restricted columns included. Columns cannot be restricted by field level data. The entire column is either restricted or available to end users. 1. To make a column secure the restricted option must be selected in the access tab.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
125
Figure 98 Field Access 2. On returning to the view details page a new section is displayed for determining who has access to restricted columns. A user or group can be added to the restricted access list by going to the view details page and adding the user or groups details in to the restricted column access section. This section is add to list – so many people or groups can be added in.
Figure 99 Field Security
Secure Column Management The Yellowfin column security restricts all non-approved from viewing any restricted columns in a view. For example it is not possible to place varying restrictions on columns on either: 1. A value basis – eg only display data where salary < 100,000 if employee id not = 29098 2. Restrict column A to user Fed and Jane, and restrict column B to Mary and Peter. – all columns will be restricted to a single set of users. If column value based security is needed then this can be delivered via source access filters. See the source management section of this guide. Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
126
Filed Permissions Field permissions dictate how the field can be used in reports. For example can the field be displayed, grouped or sorted.
Mandatory Filter and Field Mandatory field is displayed when the access level is set to ‘Global’. It is used when creating a report to determine if the field selected is a mandatory or suggested field. Mandatory filter is displayed when the access level is set to ‘Global’. It is used when creating a report to determine if the field selected is a mandatory or suggested filter.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
127
Drill Down Hierarchies Yellowfin allows you to define drill down hierarchies for dimensions. Hierarchies enable users to explore their data in a way that is appropriate for your business. Yellowfin re-calculates report data as you move down a hierarchy. Using hierarchies you can provide OLAP functionality from a relational data source. The common areas for which hierarchies are created include: •
Geography (Region, Country, State)
•
Date (Year, Quarter, Month, Week, Date).
•
Organisation Structure (Division, Line of Business, Cost Centre)
•
Customer (Country of Origin, Region, City, Customer).
•
Product (Category, Line, Product Code)
1. If you wish to add a hierarchy select the top level of your dimension hierarchy (country in the example below). 2. Click the hierarchy tab and select Yes for drill down 3. You will then select the column you wish to drill to. 4. As you progress down the hierarchy Yellowfin will display the drill path as information for you.
Figure 100 Dimensional Hierarchy
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
128
Column Constraints / Semi Additive Fields All measures that record a static level (inventory levels, financial account balances, and measures of intensity such as room temperatures) are inherently non-additive across the date dimension and possibly other dimensions. In these cases, the measure may be aggregated usefully across time, for example, by averaging over the number of time periods. The Constraint option is used for semi-additive values so that business users will be prompted if they try to create a report with incompatible values. Use constraints to define how the aggregation is to be applied if the field is semi-additive. An example of where this function is useful is shown below:
Name
Head Count
Month
Fred
1
June
Jane
1
June
Mary
1
June
Fred
1
July
Jane
1
July
Sum
5
----
Given the above data, to sum the Head Count alone would result in 5 if Month were not included in the report. This would be misleading for a user (since 5 people were never employed at one time). If you put a constraint rule on Month that it is a mandatory field when Head Count is being summed, then this would result in the following data:
Head Count
Month
3
June
2
July
As you can see from these results the report is more meaningful and correct. By applying a constraint to the aggregation of headcount the correct usage of the attribute is ensured by the end user in their reports. To access the rules applied to
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
129
fields the semi-additive option must be selected from the format tab; once it has been selected the “Constrain” tab will appear, this is where the user defines the rules.
Figure 101 Select Semi-Additive
1. Aggregation not permitted allows the user to indicate which action is not permitted for the metric. If sum or average is selected, the corresponding column will not be available to constrain against. 2. When constrain against fields/categories box is selected a new section is displayed. A column for the sum constraint and the average constraint is displayed next to the fields allowed in the report. The user selects the constraint the want against the field. These constraints are checked when creating a report and the metric is being aggregated with sum or average.
Figure 102 Define Constraints
The rules that can be applied to a field are: exclude, mandatory, and one of list. The default rule is no constraint; this will result in no constraint being applied to the field. Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
Exclude
130
being selected as the rule will result in the field not being allowed to be used in the report when the chosen aggregation is applied.
Mandatory
being chosen as the rule will indicate the field must be used in the report when the relevant aggregation is applied.
One of list
constraint will result in at least one of these fields being present in the report when being aggregated by the relevant field.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
131
Field Categorisation Fields are categorised to assist your end users to better understand the context of the field that they are adding to a report. The categorisation allows you to group columns into particular subject areas.
Apply Category To categorise a filed select a category from the category drop down on the Definition tab. This will place the filed into a folder with that category as title.
Edit / Add Categories The categories are related to the type of view you are creating – for example if the view has been categorised as a Finance view then the categories available should be those relating to the finance function. By editing categories you can either add existing categories into the Finance subject area or create brand new categories for the finance area. 1. To add or edit categories click the ‘Edit Categories’ link on the view definition page. This will open the add category popup. Existing categories for the view subject area will be displayed on the right hand side whilst all other available categories will be displayed in the left hand list. 2. Either select a category from the left hand column and click the add button to add into the view subject area; or 3. Click the ‘New Category’ Link to display a text box for a new category. Click ‘Add’ when to save. 4. Once your View Subject area has all the categories required click ‘save & close’ to close the popup and return to the field edit page.
Figure 103 Column Category
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
132
Defining Attribute Display Order The order in which fields are displayed in the report builder for the end user can be modified by the view administrator. By clicking on a field and dragging it above or below fields or to different folders you can change the order of the field as it will be displayed to the user when creating a report.
Figure 104 Move Field Display Order
Note: If you are moving a field below a hierarchy of fields then a single move up or down will move the field past the entire hierarchy not just a single field.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
133
Chapter 8 Creating Calculated Columns
Overview With Yellowfin’s view builder you can create derived columns based on the fields sourced from the database. These calculations can include simple mathematical calculations or more complex SQL statements. You can create a number of different types of calculated columns including: 1. Pre-defined filters, 2. User Parameters 3. Manually Created Calculations; or 4. Custom Built Functions. These options will create very different field types as described below.
Create New Column You can create new calculated fields within your view as required. 1. To create a new field open the Calculated Fields folder in the unattached folders list. Here you will see sub folders for formula builder, freehand SQL or Formula templates. These are different ways that you can create calculations. Select how you wish to create the filed by either selecting to create a new calculated formula, or type in an SQL statement, or use an existing function as defined in the custom function XML file (See Using Custom Functions). 2. Choose the type you want and drag it into the report folders like you would a normal field. This will open the field edit section. Define the metadata for the field as usual. 3. Advance to the formula entry by clicking the Formula tab at the top of the dialog.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
134
Figure 105 Formula Selection
Formula Builder The formula builder assists you to write valid formulas for your view. Calculated fields written in this way can be used as normal fields within a report – ie all functions can be applied to them such as aggregations etc. This is not possible with SQL calculated fields. 1. By selecting the Create New Formula option the formula generation dialog will open. 2. The formula builder will allow you to generate a valid formula that will be returned by this column. Highlighted buttons will help guide you to create a valid formula.
Figure 106 Formula Builder 3. Test your formula by clicking the test formula link.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
135
Case Statements More complex calculations can be created using the case statement. The case statement allows you to create new values in columns based on business logic. For example IF age is less than 20 then print “Young”.
1. To insert a CASE statement, click the CASE button. The When button will open a popup dialog. It operates similar to the formula builder, only allowing formula objects to be inserted where they are valid.
Figure 107 Case Statement 2. Once the CASE statement expressions have been created (Make sure you have included the END FUNCTION), clicking the OK link will return the generated expression to the formula builder. 3. On Save the formula builder will ask you to define the data type of the calculated column. It will query the database with the formula to populate the available types with formats that the formula is compatible with. 4. Once the calculated column is saved it will be added to the list of available columns in the fields view. 5. The column can then be treated as a normal field within the Report Data page.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
136
SQL Editor If you have SQL skills you may wish to write your SQL directly into the SQL edit box. In this case Select the ‘Enter SQL’ option from the formula tab. This will open the SQL edit box. Insert the SQL you wish to create your column. •
You should enter an SQL SELECT fragment, not including the SELECT keyword or any FROM or WHERE clauses.
•
Any columns referenced must exist in this view, and aggregate functions (AVG, MAX, MIN, SUM and COUNT) should not be used.
Custom Functions Custom functions are functions that are hand designed and that are stored in XML format on the Yellowfin server. These functions usually consist of advanced SQL functions that cannot be easily generated by the formula builder. These functions are configured by your system administrator. 1. Select the type of calculated field as Formula, and select the formula you wish to use from the drop down menu. 2. Functions defined in the XML file will require you to enter a number of parameters (or arguments). Parameters can be chosen from a list of columns where the data type matches the data type of the give parameter.
Figure 108 Formula Template 3. Assign a value by clicking each Argument and assigning a value to it. The value can either be a fixed data value or a column reference.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
137
Figure 109 Add Column 4. Once values for each of the arguments has been assigned, click the save button to save the column to the list.
Creating New Custom Functions Custom functions are a configurable item within your own installation of Yellowfin. To add new custom XML functions into the application contact your system administrator or see the Chapter 18 of this guide.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
138
Pre-Defined Filters Pre-defined filters can be created to assist users with adding filters or conditions to their reports that ensure the data they require is easy to extract. The use of pre-defined filters is especially useful in instances where: 1. A common set of filters are used by report writers – such as location, or business unit; or 2. If there are particularly complex filters that can be built ahead of time and are commonly used.
Create New Filter 1. Filters can only be created using the formula builder. Like a standard calculated field select the filter options from the unattached fields list. Drag the filter option into the report folder list.
Figure 110 Select Filter Option
2. On dragging the field into your folder you will be presented with the filter formula builder.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
139
3. Select the field you wish to filter and then use the operator, brackets, and or function buttons and text field to build your filter.
Figure 111 Use Builder to create filter 4. Click OK to save your filter and include it into your field list. The filter will be highlighted with a filter icon.
Filter Operands When defining a filter initially set the operand drop down to the desired value. The options within the drop down will vary based upon the type of field that the filter is to be applied to. Possible values for the operand include: Operand
Description
Equal to
Equal to a single alphanumeric or string value
Greater than
Greater than a single alphanumeric or string value
Greater than or equal to
Greater than or equal to a single alphanumeric or string value
Less than
Less than a single alphanumeric or string value
Less than or equal to
Less than or equal to a single alphanumeric or string value
Different from
Not equal to or different from a single alphanumeric or string value
Between
Between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.
Not Between
Not between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
140
In List
One or more alphanumeric or string values
Not In List
Two or more alphanumeric or string values
Is Null
Record contains no value for selected attribute. No Parameter can be set
Is Not Null
Record contains a value for selected attribute. No Parameter can be set
Like
Records that contain the same letter or letters.
Starts With
String starts with letter or letters
Ends With
String end with letter or letters
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
141
Chapter 9 Creating a View from Multiple Sources
Overview
A composite view allows you to connect to multiple different data sources and join data across these in a single view which users can then report off. Since the composite view is extracting data from multiple data sources there can be a long delay in report generation. To counter this Yellowfin stores the results sets of a composite view in the Yellowfin repository (or an alternative repository of your choice) and schedules the result set to be refreshed on a regular basis. In this way users writing reports will have a rapid response to their queries rather than having to wait for complex joins to be completed, and for IT these process intensive processes can be scheduled to run during off-peak times. The view builder for a composite view differs from the standard drag and drop builder in that rather than displaying tables from a selected database it allows you to join existing Yellowfin views together and create virtual tables from any defined database.
Creating a Composite View The process of creating a composite view is similar to creating a standard Yellowfin drag and drop view. The difference however is that instead of selecting tables from a specific source you will select pre-existing Yellowfin views from each source you have defined. You will also have to define where the data from your source database will be stored.
Define Storage Database The data from a composite view will be extracted out of your target databases and stored in a separate database. Therefore, you will have to define a target database to store your data into. The first step in this process is to specify which databases are available to store results from your composite view. 1. Selecting a Composite View Data Store Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
142
Open your database connection record that you wish to save the results of your composite view into. You can use the Yellowfin connection or alternatively any SQL database of your choice. The user specified in the source will have to have write access for the database selected. Open your connection record and click ‘YES’ for Writable.
Figure 112 Storage Database 2. Select Database from Initiate a Composite View When initiating a new view (by clicking the create link) you will need to select ‘composite view’ from the “I want to author the view using” drop down.
Figure 113 Storage Database
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
143
Prepare Data for your Composite View If you do not wish to write SQL through virtual tables that joins data from separate source systems then you will have to create an initial view for each data source that you wish to include in the composite view. This view will contain the subset of data and the keys which you will use to join in your composite view. Build these views through the normal view builder process and save the views to your database. Ensure that the columns you wish to join on are included in the views you create. If you do not create initial views then you will have to use the ‘Virtual Table’ builder and write SQL when creating your composite view. Note: All metadata applied to your baseline views will be applied in your composite view.
Joining Existing Views Together Once you have initiated your composite view you will be able to join multiple Yellowfin views together and also create virtual tables for freehand SQL queries against each of your data sources. The process of creating a composite view is similar to the standard drag and drop view builder. You will select views, define joins and select the columns you wish to make available to your end users.
Choose views you wish to join. If you have already created a set of views for the source systems you wish to connect then you will see these in the left hand database tables list in the appropriate source system folder. You can treat these as normal tables and join them as you would a standard view.
Figure 114 View Selector Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
144
In the example below a table has been selected from the international racer database as well as the Yellowfin database and a join created through the standard join process.
Figure 115 View join example
Creating Virtual Tables If you do not have the data you wish already defined in a pre-existing view then you can create a virtual table. The virtual table popup will prompt you to specify which source system you are connecting to. In the example below the virtual table SQL has an additional drop down for the source system. You will need to specify the source system for any freehand SQL statements that you wish to generate.
Figure 116 Virtual Table SQL Note: that for any aggregations used such as sums etc you will have to follow the aggregate function with an ‘as “Column Name”’ statement so that Yellowfin can generate a valid column name.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
145
Set the Refresh Schedule Since a composite view is refreshed on a regular basis you will need to define the refresh schedule. This schedule is located in the view options section on the right hand side of your screen. The view will be refreshed by default on a daily basis at 12 midnight. You can change the schedule based upon your specific needs.
Figure 117 Refresh Schedule
Initial Data Population In the view summary you can preview data created by your view by navigating to the Data Tab and populating the view. If you fail to do this no data will be returned in your report until the first cycle of the schedule has run. 1. Go to the Data tab and click on the Populate Data Now link.
Figure 118 Populate Data
2. You will now be told the view is being populated, click Refresh Page to view the data. If you have a large amount of data this may take some time to complete.
Figure 119 Refresh Page
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
146
3. Once the population is complete you will see a preview of the data your view will return.
Figure 120 Preview Data
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
147
Chapter 10 Report Categorisation
Overview All reports types are managed through a similar security and categorisation infrastructure. This is managed through the configuration Content Access function. The security of your reports is managed at the category and subcategory level, not at the individual item level. The purpose of this is to simplify the creation of reports in the system. For Example Rather than having to specify who is allow to see a specific report each time you create a new report the security for the item is inherited from the subcategory of the report that is created. This section explains how your report are organised and how to setup the security on the various content categories.
How Content is Organised Reports are managed on a two level hierarchy of Category and Sub category, with individual reports attached to a subcategory. The purpose of this is to allow you to define the way in which your reports are accessed by your users. It is critical that you carefully consider how your report ‘taxonomy’ is to be implemented.
Taxonomy An important part of the report management process is the creation of you ‘report taxonomy’. Taxonomy is how you categorise and define the information you wish to publish. It is used to help people find and search across all reports in your repository.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
148
The challenge Setting up a taxonomy is an act of communication. They basically capture an essence of the knowledge that resides in your organisation. A conceptual short-hand overview that describes what's important and how things you are interested in relate to each other. Creating a taxonomy, like anything that worthwhile can be hard work, timeconsuming and require considerable domain expertise and creativity. Imagine a "Creatures of the World" web site. It may have a taxonomy like this... • Animals o Vertebrates Mammals Birds Fish Reptiles o Invertebrates Arthropoda Crustaceans (Crabs, Lobsters, barnacles, etc) Insects
Already you may be thinking, I wouldn't have organized the Creatures like that, my users need to access them like this... • World o Seas o Land
Making a Taxonomy for your company or area of expertise can be very difficult. However, the context of information usage in your company will determine the most appropriate form of taxonomy to implement.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
149
Managing Report Categories To create and edit you report categories you will have to access the Administration function.
Report Categories List To access a list of all your report categories click on the Report Categories link from the Administration Panel. This will open a page containing all your report categories. Your secure categories and your default draft folder are highlighted by the use of icons on the left hand side.
Figure 121 Content Access
Deleting Categories By deleting a category it will no longer be available for user to attach reports into. 1. Select the category that you wish to delete by ticking the associated check box 2. Click the ‘delete’ link and the category will be deleted. If the category is already in use you will not be able to delete it.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
150
Create New Category 1. Click the create link button at the bottom of your list, or the create new category link from the Admin Console, to create a new Category. This will open the Category Form. 2. Select the Category or Subcategory Level. In your hierarchy of content you must first create a category level before you create its associated sub-categories. If a subcategory had been chosen you will have to choose a category to link the subcategory to. In the example below the subcategory is being linked to the Content Category created previously. 3. Complete the short description as it will be displayed on the side navigation for content, and associated drop downs. 4. Select the status of the category – NOTE if you leave the category in DRAFT mode users will not be able to access reports or create reports for this category.
Figure 122 Complete Details
Draft Report Category The draft report category is used to store reports in DRAFT mode for all users. The purpose of defining a draft category is to assist users to manage their DRAFT reports. This will be the default category where reports are saved. A user can still save DRAFT reports into the folder of their choice.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
151
Sort Order The order number will define what number in your list of categories this category should be displayed.
Create Public Publisher In the report Category edit page the Publish Access and Approvals section allows you to determine which users can publish public reports to the category. a. If you select ‘Users with Functional Access” then all users with the public report function can write to this folder b. If you select a person or expert group – then only specified users can publish reports into that folder. If a user does not have access to publish to that folder the report will be routed to an Export for approval.
Figure 123 Publish Rights
Management Information & Version Management One option available within Yellowfin is the ability to maintain previous instances of a report. This is known as a management information report. When a user selects this option not only is the report definition saved but the data as well. This allows users to view previous versions of the report and compare result sets. All versions of the report data are stored in the Yellowfin Database. As the administrator you can determine how much history and the maximum size of reports you wish to save. This is an application management process that permits you to control the use of your infrastructure.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
152
Determine Version Control In the report Category edit page the Report Version Control Section allows you to define the content management criteria and the archive and version history.
Figure 124 Content & Version Management
Version History
The version history allows you to store the data returned in a report. If version history is not set to YES then every time a report is viewed a new set of data will be returned from the data source.
Max Size
The maximum size for data to be saved in cache or in the database where version history is required.
Max Items
The maximum number of items to be displayed on the history
Displayed
drop down list for management information reports.
Delete Period
After what period of time should archived reports be deleted.
Category Security You can secure your categories to restrict users access to the report writing and view capability for the selected category. The options for setting the security are as follows: 1. Unsecure content (people can access this without logging on) 2. Secure (users will have to logon before being able to view the content – but all users of your site will be able to view it). 3. Access Security Level required – only selected users will be able to access this content.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
153
Figure 125 Security
If you choose to secure the category then you must select a person or group and then the level of access that they are permitted. Three types of access exist: Read
Users will only be able to read the content but not create new items of this type of content.
Edit & Update
If a content item exists a user will be able to edit and update an existing piece of content but not create new or delete items in this category. They will also be able to read all content in this category.
Delete
Users with delete access will be able to read, edit, create and delete content of this type. You must have at least one person with delete access.
Tip
If you want all your user to be able to read the item make sure you have a group created with all your users. Use this group for read access.
When you have selected a person, and or a group click add to add them to the access list. You can have as many groups and people in the list as you wish. You cannot finish the process if you do not have at least one person or group with delete access. Select a person with delete access to finish.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
154
Chapter 11 User Administration
Overview This section describes how users can be maintained within the application.
User Management Access to Yellowfin is based on users existing within the system. Through the Administration Panel administrators can create, edit and delete users within the application.
User List The user list provides you with the ability to browse all the users within the system. To access this list click the ‘User Management’ link on the Administration Panel. This will open a new page a display a list of all users. If you have many users and you want to find a specific user you may have to use the search facility or use the forward and previous links to browse the list.
Figure 126 User List
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
155
Search Users To search for users within your system you can either use the search facility from the Administration Console or the User List page. 1. Type in the users first or last name (filter by role drop down) and click the GO button. 2. A list of users matching your query will be returned.
Add User 1. To create a new user click the ‘create new user’ link on the Administration Panel or the add link on the User List page. 2. This will open a new user form. Complete the form by filling in their name, email address, time zone, allocate a role and provide a password – this will be changed the first time they login.
Figure 127 Add Person
Change Password If a user forgets their password as the administrator you can change it. Open their profile through the user management section. Type in new password details and confirmation email. You will have to inform the user of their new password. They will be requested to change it the first time that they log in.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
156
Figure 128 Change Password
Change Role Access As discussed in the security section of this document role management is a vitally important function of your system administration. This is because by granting people a role within the system you will be allowing them access to modify your data. If the role a person has a large number of permissions this could put you at risk if the person should no longer have access.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
157
Bulk User Import With Yellowfin you can load a large set of users into your application through the Bulk User import process. This is available through the user management forms. 1. To load you bulk users navigate to the manage users page. Below the list of your users you will see the Bulk Import Link. Click this link to continue.
Figure 129 Bulk Import Link
2. You users should be contained in a CSV format with data contained within quotes. See the example below and on the screen for more detail. The file must contain the following parameters: First Name, Last Name, Initial, Salutation Code, Language Code, Time Zone Code, Email Address, UserName, Password, Role Code
Figure 130 Bulk Import Form
3. Click the browse button to locate and load your file. 4. Click the Upload Data File to load and save your new users.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
158
Deleting Users To remove a user from the system you must delete them – they will then not be able to login and access reports. 1. To delete a user go to the user list 2. Select the person you wish to delete by ticking the check box and clicking the delete link
Figure 131 User Delete
3. You will have to confirm the deletion on a confirmation page. Click OK to continue with the user deletion.
Figure 132 User Delete Confirm
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
159
Chapter 12 Group Management
Overview This section describes how Groups are used within the application. Groups are a vital element of Yellowfin security and Broadcast processes. Groups assist users by allowing commons sets of people to added in to general groups which can then be attached as a single entity to various elements within the application.
Group Management Groups can be created and managed through the Administration Panel. Once a group has been defined there are processes within Yellowfin that will automatically maintain members of a group on a regular basis.
Figure 133 Manage Groups
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
160
Group List The group list page allows you to view all your Groups. Click the Group Management link on the Administration Panel to access the list.
Figure 134 Group List
Delete Groups To delete a group so that it is no longer available for adding into reports and use for security select the group you wish to delete, and click the delete link. This will not physically delete the Group from the system – since the group may be in use. The status of the group will be changed so that it is no longer available for use. Note: If a group is accidentally deleted it can be re-instated by your Yellowfin DBA by updating the group status code.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
161
Create Groups To create a new Group either click the ‘create new group’ from the Administration Panel or Create New from the Group list page.
Figure 135 Create Group Option
1. Type in the name and description of the group 2. Select the members you wish to add and click ‘add’ to include them into the members list. If you wish to specifically exclude a member – tick Exclude from the member control option. 3. Click ‘Submit and Close’ to save and close your group.
Default Dashboards If you wish to automatically add dashboard tabs to a user’s dashboard when they are created you can assign the default tabs for a user group. Choose the tab and click ‘add’ to add them to the list of tabs. The order you add tabs to a user group will be the order they are sorted on the dashboard (see the numbered list pictured below). All these tabs will be created for a new user when they first login to Yellowfin.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
162
Figure 136 Create Group Option
Group Members To assign users to a group you will have to create member records. This is done by deciding how members should be assigned. To assign members you can either add in specific users or specific types of users. For example you could create a group that includes all users with the role of ‘Public Writer’.
Member Types The following member types are available from within Yellowfin: Person
Individual users of Yellowfin
Role
If you wish to select all users from a selected Yellowfin role – such as administrators then select role.
Group
Pre-existing groups that have been created in the group management list.
LDAP Group
If you have configured your assess to use LDAP directories then you will be able to select an LDAP group. Yellowfin will synchronise with this group on a daily basis.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
163
Member Control The member control allows you to determine if the user should be included or excluded from the group. For Example you might want to include all Public Writers in a group but exclude John Smith. If a user is excluded from a group then this overrides any record from which they might be included. The exclusion process is the last step of building the group members. 1. Select the type of member you want to add to a group 2. Determine if you wish to include or exclude them from the group 3. Click add to add them into the members list
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
164
Chapter 13 Role Management
Overview This section describes how roles are created and managed within Yellowfin. Roles are allocated to users and define a set of functions that the user is permitted to access within Yellowfin.
Role List The role list is accessible via the administration panel. As a default you will have a number of pre-configured roles. These are listed below.
Figure 137 Role List
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
165
Default Yellowfin Roles When Yellowfin is initially deployed a standard set of roles is provided to you. These are: Administrator
The Yellowfin Administration role has access to database and user administration functions. These functions include managing users, groups and other configuration parameters. It also has all the report writing and reading access supplied by the other roles below plus advanced functions, including: SQL Reports, Public Publishing, Drill Through Reports
Public Writer
The Yellowfin Report Public writer has access for creating and editing Public reports. All users that are to publish reports for Public use must have the Public Role to do so. Reports created by a Yellowfin Public Writer can include drill through reports, multicast reports, and report emailing.
Report
The Yellowfin Report Consumer can only read reports that have been
Consumer
published to the Public repository. Access exists to favourites, a dashboard and report viewing.
Report Writer
The Yellowfin Report Writer has access to favourites, a dashboard and Private report creation. A Yellowfin report writer cannot produce reports with drill through, broadcast or MI capabilities.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
166
Edit / Create Role 1. To create or edit an existing role click either the create link or the role name that you wish to update. This will open the role edit form. 2. Provide your role with a meaningful name and description.
Figure 138 Role Details
3. You can choose to make this you default role. This means that every time you add a new user this will be the default role they receive unless you choose to allocate them a different one.
Figure 139 Role Functions
4. Tick the functions you wish to allocate to the user. A description of these is available by rolling over the report information icon – as seen in the example above. Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
167
5. Some functions will require you to provide the appropriate CRUD functionality. The acronym CRUD refers to all of the major functions that need to be implemented. Each letter in the acronym can be mapped to a standard SQL statement: Create
INSERT new records
Read
SELECT records from the database
Update
UPDATE records in the database
Delete
DELETE records in the database
On example of CRUD access is for dashboards. If a user only has R selected they will be able read dashboard but create new dashboard tabs or add reports to their tabs. Whilst a user with CRUD will be able to create and delete dashboard tabs. 6. The last option you have in the role settings is to specify what columns should be made available to users when viewing the report list.
Figure 140 Role Report List Columns 7. Click Save when you have completed editing to make your role available. Note: Yellowfin will do a function check to ensure you do not breach your license agreement.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
168
Security Functions Available The following is a list of security functions available in Yellowfin and their associated definitions. They have been grouped into sub sections to assist you to identify how and when these functions are used.
Function
Description
Administration Approval Administration
Allow users to see outstanding report approvals
Category Access
Allows users to configure report categories
Configure Logon ID
Allows a user to set the type of log on id used for accessing Yellowfin – on the configuration page.
Configure Page Format
Allows the user to change the header and side nav interaction – on the configuration page.
Configure Support Email
Allows a user to set the support and broadcast reply email – on the configuration page.
Data Source Admin
Provides access to all data sources regardless of security settings.
Data Sources
Allows users to create and edit Data Sources. If a user does not have access to this function they may be able to create views but bit actually change the connection parameters.
Database Views
Allows users to create and edit Database Views
Draft Public Reports
Allows the admin user to see all draft Public reports regardless of whether they are the current modifier or not. This lets the admin user reset these reports.
Draft Report List
Show a list of draft reports on the user dashboard page
Export View
Allows users to export a source, view, reports etc to an XML definition for import into another instance of Yellowfin.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
Import View
169
Allows users to import a source, view, reports etc to an XML definition from another instance of Yellowfin.
OLAP
Allows users to connect to OLAP Data Sources
Remove Access to Report Refresh
Remove access to the report refresh page
System Configuration
Allows users to modify Yellowfin site parameters
System Information
Allows admin users to view system information
Security & License Licence Management
Allows users to view Yellowfin Licence details and upload new Licence files
Access Groups
Allows users to create and edit Groups
Roles Management
Allows users to create and edit security roles
User Management
Allows users to create and delete other user accounts
Source Filters
Allows users to create data source filters
Report Writing Access Filter
Allows users to set or change the access filter on a report. This allows a user to over-ride any access / source filters set on a view.
Advanced SQL
Allows users to create formatable freehand SQL reports and reports from stored procedures.
Hide SQL
Will hide the SQL tab from the report information popup.
MI Reports
Allows users to create Management Information reports which saves the XML result set into the Yellowfin database creating a report history.
Refresh Dashboard Reports
Allows the user to set the auto refresh for dashboard reports.
Report Types
Allows users to create freehand SQL reports, and Jasper reports
Related Reports
Allows users to use Co-Display, Drill Down and Drill through functionality
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
Sub Queries
170
Allows the user to created advanced reports through the use of subqueries.
Yellowfin Reports
Allows users to log in to Yellowfin, and view, create, edit or delete reports. Read only users only require R access.
Personalisation Dashboard
Gives users access to the dashboard page, and allows them to create their Private dashboard
Favourites
Allows users to add reports to their favourites list
User Profile
Allow users to view and update their user profile
Report Publishing Broadcast Reports
Allows users to broadcast reports on a one-off or scheduled basis
Public Reports
Allows users to create and edit Public reports and views
Distribute Reports
Provides the user with the ability to distribute reports and dashboard tabs into another users inbox.
Email Reports
Allows users to send an ad hoc email copy of a report
Export Reports to CSV
Allows users to export report results to CSV
Export Reports to Doc
Allows users to export report results to DOC
Export Reports to PDF
Allows users to export report results to PDF
Export Reports to RTF
Allows users to export report results to RTF
Export Reports to XLS
Allows users to export report results to XLS
Multicast Reports
Allows users to broadcast reports to external users
Web Services
This function is required for a user account that will be used to log in to Yellowfin Web Services
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
171
Chapter 13 Export & Import Report Repository
Overview Yellowfin provides you with the capacity to export all your dashboard and report definitions including the related entities such as views and source systems from one instance of Yellowfin (for example a test environment) and import these into a separate instance (eg. Production). The sections describes the process of importing and exporting these components into Note: When exporting definitions from Yellowfin there are certain limitations. You cannot export security settings for categories, source systems or views. These will have to be reset when importing them into the new instance of Yellowfin Source filter information will be exported but only if it is a scheduled SQL query. Manual records and data file records will not be exported.
Database Independence If you are using the import and export functions to migrate Yellowfin across platforms then you will have to be cognisant of hard coded platform SQL. Examples of this may occur if you have: 1. created virtual tables within your view definition or 2. you have written freehand SQL queries 3. you have BIRT or Jasper reports with platform specific SQL Yellowfin cannot test for these SQL anomalies and you should test your import and export processes across platforms in your test environment prior to migrating into production.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
172
Exporting
1. To export definitions from Yellowfin navigate to your administration panel and select export.
Figure 141 Export 2. Select the elements you wish to export. a. From the drop down menu choose the item type (source system, categories, views etc). Based on your selection you will be presented with a list of items to choose from. b. Select those items you wish to export and click the add button to add selected items to the list. Click next to continue.
Figure 142 Item Selection 3. If you have chose to export items which have dependencies (for example a dashboard tab) Yellowfin will present you with a list of all the dependent items which you may wish to export as well.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
173
Select those items you wish to include in the export process and click add to add them to the list.
Figure 143 Dependencies 4. When ready to export click the export link
Figure 144 Export 5. You will be prompted to save the file to your local drive. Save the file onto your local file server – for later use.
Figure 145 Export to File
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
174
Importing
1. To import definitions into Yellowfin navigate to your administration panel and select import.
Figure 146 Import 2. Select the exported XML file from your file server by clicking the browse button and using the file upload form to select the appropriate file. Click next to continue.
Figure 147 Import 3. Yellowfin will now display each element in the import file on the right hand side of your screen and provide you import options in the central form on your screen. You can choose to import or skip any item in the import file. If you skip dependent items such as a data source for a report then Yellowfin will prompt you to select the appropriate substitutes for those specific items. Choose your import options for each item and click next to navigate through all the items in the file till you get to the summary page.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
175
Figure 148 Import Item validation
4. On the summary page – if all items have been processed correctly you can click import to import these. If an error has been detected with a report Yellowfin will display an error message. You can however continue to import the file regardless of the error and then manually update the file from within Yellowfin.
Figure 149 Import File Error
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
176
Chapter 14 Schedule & Broadcast Management
Overview Yellowfin allows user to create scheduled reports. In some instances as the administrator you will want to delete redundant schedule items. From the admin panel you will be able to view and delete all scheduled tasks without the need to open each task individually. To access the schedule list go to Administration > Manage Schedules. Click the manage schedule link.
Figure 150 Schedule Management
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
177
View Schedules The schedule list allows you to search, view, edit, pause, run, and delete scheduled tasks.
Figure 151 Schedule List
Edit Schedule By clicking on the
expand icon next to a task, you can view its schedule details.
Figure 152 Scheduled Task
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
To edit a schedule simply click on the
178
Edit Schedule link on the task and make the
changes you require. When finished, click the
Save link to commit your changes.
Figure 153 Edit Scheduled Task
Pause Scheduled Task To pause a task simply open it by clicking on the
icon and click on the
Pause
link.
Figure 154 Pause Scheduled Task
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
179
Run Scheduled Task To run a task simply open it by clicking on the
icon and click on the
Run Now
link.
Figure 155 Run Scheduled Task
Delete Scheduled Task From the list of scheduled items select the ones you want to delete and click the Delete link.
Figure 156 Delete Schedule
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
180
Refresh Schedules To refresh schedules, select the checkbox next to a task and click on the
Refresh
link.
Figure 157 Refresh Schedule
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
181
Chapter 15 Images & Maps
Overview Using the Images and maps configuration you can set load new images into Yellowfin, raster maps, and WMS layers. Raster maps are images with each zone specified as a unique colour which defines the zones to be replaced by heat map colouring.
Figure 158 Manage Images
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
182
In the Image list you will see an icon form both types of images supported: Basic images can be used as background images for Charts Maps which are used for generating heat maps.
Figure 159 Images and Maps list
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
183
Loading Images To load new images into your image library click the images and maps link from the administration panel as shown above. This will open the image list table.
Figure 160 Image List
1. Click the add link to open the ‘add new image’ page. 2. Type in the name and description of the image you wish to load and select ‘Image’ from the drop down.
Figure 161 Image Meta Data 3. The page will now display the browse and upload section. Browse for your image file and click the upload link to load the image to the server.
Figure 162 Image Upload 4. The image thumbnail will now be displayed. Click save to save your changes and return to the list.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
184
Figure 163 Image Save
Using Images in your Charts Now that the image has been loaded when creating a chart you will see on the background options drop down the ability to add images to the chart.
Figure 164 Adding Image to Chart
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
185
Raster Maps A Raster Map is an image where every zone is defined by a unique colour type. This is used by Yellowfin to detect each unique area of the map and substitute colours based on data in your report. The raster maps can be used for any type of location reporting not just ‘geographic’ maps. For example you could use these for: 1. Floor Plans 2. Car Body Parts 3. Network Maps 4. Country Maps
How do they work? With each zone of your map identified by a unique colour you need to link these zones to a specific value. These values are defined as reference codes. With each zone defined by colour and linked to a reference code Yellowfin will be able to substitute the values into the map.
Loading a Raster Map 1. To load a Raster Map Image ensure that your image has unique colour for every region required. 2. Ensure that you have reference code setup for your map data. E.g. For the example in Yellowfin you have a country reference code defined by ISO code. These ISO codes will be linked to each zone. 3. Similar to loading an image click the add link, provide a name and select map type. 4. You will now see a Map details section. For a basic Map you can leave the reference type as None. 5. In the layer section you will have to load the actual image you want to use. Maps support multiple layers for the same map. For example you could have a layer for country or region which use the same world outline. a. On the layer choose the name and description for the image layer that you are loading
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
186
b. Browse and upload the map you want to load. c.
Choose layer as the layer type.
d. Choose the zone reference type. This is used to link the zones to specific reference code values. e. For each colour select the appropriate reference code value. f.
Click Add to add the layer.
Figure 165 Loading Layers 6. The new layer should now be added to the layer list. 7. Click save to save your Raster map.
Figure 166 Layer List
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
187
Using a Raster Map Once the map has been defined you will be able to use it to create heat maps.
1. Select the map style chart option and image map from the chart selection list.
Figure 167 Map Selection
2. Complete the map details – eg select the map you want to use, the layer, and the label (which is the data from your report containing the appropriate reference codes) and click refresh. Note: in the example below the area coloured green indicated a region which has not been defined by a reference code. (The original colour is still displayed)
Figure 168 Map Output
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
188
WMS Layers A WMS layer is a georeferenced map image loaded from a Web Map Service. These are used as backgrounds and overlays for geographical maps in Yellowfin.
Load WMS Layers 1. To load a WMS Layer go to the Images and Maps link in Administration. Select the WMS Layer image type and enter the server details as per your WMS server. Click the
Load Layers link to view all the WMS layers on the server.
Figure 169 Load Layers 2. You should now have a list of layers that you can preview and save by selecting the checkbox next to a layer and then clicking the Preview and Save links.
Figure 170 Preview Layers
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
189
3. Once saved, you should then see the WMS layer listed on your Images and Maps list.
Figure 171 WMS Layer in List
Add WMS Layer
1. When you have created a GIS map using the chart builder, click on the
Add
Layer link at the bottom of the Chart Data section.
Figure 172 Preview Layers
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
190
2. You will then see the Layer popup window, from which you can select your WMS Layer and click
Add.
Figure 173 Add Layer
3. Your report should now have a WMS Layer added, like the one pictured below.
Figure 174 Add Layer
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
191
Chapter 16 Reference Codes & Translations
Overview Numeric and Text based data displayed in a report can be translated from values held in the database to more meaningful values by adding a reference code to a dimension. This should be used if: 1. You wish to translate a value held in the data source into business terminology. For example if male and female are stored as M and F you may want to translate these into ‘Male’ and ‘Female’ 2. If you wish to apply customised sorting to a field. For example: data returned in a query is usually displayed in Alpha / Numeric sort order. If your result set included: Admin Staff, Managers and Executive Management your report may look like this: Admin Staff
500
Executive Management
10
Management
50
Hierarchically your data may actually need to be displayed in the following way: Executive Management
10
Management
50
Admin Staff
500
This can be achieved by creating reference codes for the values and assigning a sort order to those codes.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
192
Managing Reference Codes To manage your reference code you can use the administration function located in the configuration section of your admin panel.
Figure 175 Manage Reference Codes
Selecting your reference Code When first entering the reference code management section you will have to choose the reference code that you want to manage. Select from the list provided on the left hand side.
The reference code types are: Report Ref Type Codes
Used for translating field values returned from the database. Eg ISO Country Codes to Country Names
Categories
Used for displaying report category names to the end users
System
Used for creating Reference Codes used for dynamic filters and user preferences.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
193
Click on the reference code name that you want to manage.
Figure 176 Select Reference Codes
Changing Values your reference Code
Once you have chosen the reference code you want the centre panel of the screen will open and you can update the data for the code. You can change the name, description and manage the code translations, as well as sort orders. If you want to add more codes and translations click the Add link to create a new blank record.
Figure 177 Edit Reference Code
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
194
Multiple Languages To create translations for multiple languages you can use the language tab. The appropriate translation will be displayed based on the users language settings. Click on this tab to choose a new language and then to populate the short description with the appropriate translation for that code. Note: You cannot add new codes on this tab. This can only be done from the main tab.
Figure 178 Multi Language Reference Code
Usage The usage tab lets you see where the reference code is being used. Click on the usage tab to view the details.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
195
Chapter 18 Customisation
Overview Yellowfin has a number of customisable elements that permit you to deliver advanced analytic and format functionality to your users. This chapter covers the three customisable elements within Yellowfin: 1.
Analytic Functions
These are used to transform results and apply post processing after the initial query result set has been returned to the server. Functions examples included with Yellowfin include – Top 10, % of Total, Running Sum etc. The functions are database independent since they are calculated by the Java code. These functions are applied by the user at the report building stage.
2.
Custom Functions
Custom functions are functions that are hand designed and that are stored in XML format on the Yellowfin server. These functions usually consist of advanced SQL functions that cannot be generated by the formula builder. The XML schema allows for the custom defined SQL functions to have parameters embedded so that numeric or column values can be assigned when the column is inserted into a report.
3.
Data Format
The Yellowfin application has the ability to format different data types based on a user-defined java class. This allows for parsing the returned database value and transforming it into another format. For example you can choose how you render values from your database – such as dates, currencies etc.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
196
Analytical Functions The Yellowfin application has the ability to run custom analytical functions against a dataset before rendering the data as a report or chart. Each analytical function is a java class that allows a user to program their own formulas for altering the dataset on a particular column. Some of the included analytical functions that are implemented by this method are Top 10, Bottom 10, Running Total & Running Percentage.
Analytical Function Interface The Analytical Function Interface consists of the following methods that must be implemented in a custom Analytical Function class.
String Name() This returns the name of the Analytical Function.
String Description() This returns a short description of the Analytical Function.
String ColumnPrefix() This returns the text that precedes a column when displayed on a report. It may be “Percentage of “, “Total of “.
String Category() This returns the text that will group this function with other functions with the same Category. This is used to group functions on the Yellowfin user interface. If Category() is not implemented the function will be placed in the “Other” group.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
197
void Setup() This Setup function allows Yellowfin to determine what the Analytical Function is capable of. By default the Analytical Function will not be available to any column data types. The Analytical Function has to be explicitly enabled to be compatible with a specific data type (either Numeric, Text or Date data types). Enable these by calling the following instructions. acceptsNumeric(boolean); acceptsText(boolean); acceptsDate(Boolean);
It is possible for the Analytical Function to accept more than one data type. The Analytical Function will assume that the return value is the same as the original data type. However this can be set manually using: returnType(int returnType); Where return type is: TYPE_UNKNOWN = 0; TYPE_TEXT = 1; TYPE_NUMERIC= 2; TYPE_DATETIME = 3; TYPE_PERCENTAGE = 4; TYPE_INTEGER = 5; TYPE_CURRENCY = 6;
If the Analytical Function is relies on the order of the data, ie a running total or running percentage, you will need to set the order dependent flag. This will tell Yellowfin that the column needs to be recalculated if any of the other columns in the report are sorted. The function to call is: orderDependent(boolean);
By default the analytical function will allow the column to have the following SQL aggregations applied: SUM, COUNT, MIN, MAX and AVG. If it is not logical for your Analytical Function to allow these aggregations they can be toggled individually using the following functions from within the Setup function. handleSum(boolean); Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
198
handleAverage(boolean); handleMaximum(boolean); handleMinimum(boolean); handleCount(boolean);
The Analytical Functions can also accept parameters. These are also defined in the setup routine. Define a parameter using: addParameter(String parameterName, String parameterDesription, int parameterType, int fieldType, boolean mandatory, Object defaultValue); parameterName can be any string to name the parameter. parameterDescription can be any string to describe the parameter. parameterType defines the parameter as a value or field.
Use AnalyticalParameter.PARAMETER_VALUE or AnalyticalParameter.PARAMETER_FIELD. fieldType defines the type of parameter. This defines the type of fields that will be allowed to be assigned to this parameter if the parameter is defined as a field. fieldType can be: TYPE_UNKNOWN = 0; TYPE_TEXT = 1; TYPE_NUMERIC= 2; TYPE_DATETIME = 3; TYPE_PERCENTAGE = 4; TYPE_INTEGER = 5; TYPE_CURRENCY = 6; TYPE_BOOLEAN = 7;
mandatory is a flag that defines whether the parameter is required or not. The Yellowfin User Interface will validate that this field is assigned before letting the user proceed. defaultValue is an object that will be assigned to the parameter by default. This can be null.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
199
These runtime values of these parameters can be accessed for use in your analytical functions (in preAnalyticFunction() and applyAnalyticFunction()) using the following functions: getValue(String parameterName) If the parameter references a field, the getValue() function will return a Object array containing the entire column of data. void preAnalyticFunction(Object[] columnValues) throws Exception; This function is used for pre-computing values vertically on the selected column. You will need this if you a generating data based on the entire column’s data, such as a running total or percentage of total. The function parameter is an Object array of all of the data in the selected column.
Object applyAnalyticFunction(int rowNumber, Object value) throws Exception; This function is called for each row in the dataset. This function will return the data that you would like to replace the existing data with. The parameters are rowNumber and value. The value is a java Object containing the original valueger and the rowNumber is an integer representing the values index in the original dataset. If you would like to remove rows from the dataset you can call the remove(rowNumber) function that will flag the row to be removed from the resulting dataset.
Development Libraries There is a development library with the classes required for creating Analytical Functions. This library is called i4-devel.jar. This will allow you to compile your class file without having to include all the Yellowfin libraries in your classpath.
Registering Analytical Functions for Use The Analytical Function will have to be listed in the custom-analytical-functions.xml file. This file resides in the appserver\webapps\ROOT\WEB-INF directory. You will have to specify the name and class for the analytical function you would like to add to Yellowfin. To add the class file you can add your class to a JAR file and place it in the appserver\webapps\ROOT\WEB-INF\lib directory. You will have to restart Yellowfin for your new class to be registered for use.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
200
Example: Percentage of Total package com.hof.mi.custom; import java.math.BigDecimal; import java.math.BigInteger; import org.apache.log4j.Category; import com.hof.mi.intefaces.AnalyticalFunction; public class PercentageOfTotalAnalyticFunction extends AnalyticalFunction { private static final String cvsId = "$Id$"; private static final Category log = Category.getInstance(PercentageOfTotalAnalyticFunction.class); private float Total = 0; private static final float hundred = 100; /// Set Analytical Function Details public String Name() { return "Percentage of Total"; } public String Description() { return "Returns percentage of total for the selected field"; } public String ColumnPrefix() { return "% of Total of"; } // Analytical Function will be available for Numeric column types // and will return a percentage. The Analytical Function will not // need to be recalculated if another column is sorted. protected void Setup() { returnType( AnalyticalFunction.TYPE_PERCENTAGE); orderDependent(false); acceptsNumeric(true); } // Calculate total of column, with all column data. public void preAnalyticFunction(Object Data[]) { Number Value = null; float Val = 0; log.debug("Processing Data for Percentage of Total..."); for (int i=0; i < Data.length; i++) { Value = (Number) Data[i]; Val = Value.floatValue(); Total = Total + Val; } log.debug("Percentage Total: " + Total ); return; } // Return the percentage of the row value against the total //calculated earlier. public Object applyAnalyticFunction(int i, Object Data) { Number Value = (Number) Data; BigDecimal Return = new BigDecimal((Value.floatValue() / Total) * hundred);
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
201
return Return; } }
Example: Top 10 package com.hof.mi.custom; import java.math.BigDecimal; import java.math.BigInteger; import java.util.Arrays; import java.util.HashMap; import org.apache.log4j.Category; import com.hof.mi.intefaces.AnalyticalFunction; import com.hof.util.UtilComparator; public class Top10AnalyticFunction extends AnalyticalFunction { private static final String cvsId = "$Id$"; private static final Category log = Category.getInstance(Top10AnalyticFunction.class); private int Rank = 0; private HashMap Values = new HashMap(); public String Name() { return "Top 10 Rank"; } public String Description() { return "Returns Top 10 values for the selected field."; } public String ColumnPrefix() { return "Rank of"; } public void Setup() { returnType( AnalyticalFunction.TYPE_INTEGER); orderDependent(false); acceptsNumeric(true); } public void preAnalyticFunction(Object Data[]) { float Val = 0; log.debug("Processing Data for Top 10..."); Arrays.sort(Data); Rank = 0; Values.clear(); for (int y=Data.length-1; y>=0; y--) { if (Data==null) continue; if (Values.get(Data[y])==null) Values.put(Data[y], new Integer(++Rank)); } return; } public Object applyAnalyticFunction(int i, Object Data) { Integer Rank = (Integer) Values.get(Data); if (Rank.intValue()>10) remove(i); return Values.get(Data); } }
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
202
Custom Functions (XML Definitions) Custom functions are functions that are hand designed and that are stored in XML format on the Yellowfin server. These functions usually consist of advanced SQL functions that cannot be generated by the formula builder. The XML schema allows for the custom defined SQL functions to have parameters embedded so that numeric or column values can be assigned when the column is inserted into a report.
Schema Most senior hierarchical object, encapsulates entire document. Only has one allow child type, .
This defines a unique function, and encapsulates the data fields needed to define it. Child objects include , , , and .
Name defines the display name of the custom function within the application. It is the primary identifier for each of the custom function, therefore the names of each function must be unique. Each function can only have one name only.
Argument defines an argument (or parameter) that can be inserted into the custom SQL at report design time. Arguments are defined by 3 attributes, , and .
The index parameter of argument uniquely identifies the argument in the context of the function and must be an integral positive value. The index is used for inserting the
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
203
argument at the defined location within the custom SQL statement. If the index is “1” then the argument will be replaced in the SQL statement for every instance of “$1”.
The name parameter of argument is the display name for the argument in the application.
The datatype parameter of argument defines the datatype of the argument. This allows the application to only allow compatible columns and values to be entered into this argument. Datatype must be one of “numeric”, “text” or “datetime”.
Sql defines the actual database SQL statement to be made for this custom function. The SQL in this field will be inserted into a parent SQL statement as a single column, so a full SELECT FROM WHERE is not required and therefore the SQL must be compatible with single column syntax. See the example below for a simple CASE WHEN ELSE END example of a single column custom function. This SQL can also contain variables where arguments should be inserted. “$1” will be replaced with the column or data value assigned to the argument with index 1.
The aggregate parameter defines which columns are aggregated within the custom function. This tells the application to not place these columns in the GROUP BY clause when generating the report SQL. The value of the aggregate parameter can also be a argument variable, for instance “$1” for the argument with index 1.
The group by parameter defines which columns should be inserted into the GROUP BY clause when the application is generating the report SQL. The value of the groupby parameter can also be a argument variable, for instance “$1” for the argument with index 1.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
204
The return function defines the data type of the information that is returned by the entire custom function. This must be one of “numeric”, “text” or “datetime”.
Adding New Functions To add new XML functions into Yellowfin: 1. copy your XML file into Yellowfin/appserver/webapps/ROOT/WEB-INF/ 2. Once these have been added into the directory you will need to restart Yellowfin for these to become available.
Example custom-functions.xml Ratio 1 Numerator numeric 2 Denominator numeric $1 $2
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
205
numeric
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
206
Data Display Formats The Yellowfin application has the ability to format different data types based on a user-defined java class. This allows for parsing the returned database value and transforming it into another format.
CustomFormatter class Developers can create their own formatter class that extends the class: com.hof.mi.interfaces.CustomFormatter See Appendix A for the full source of this class.
The following methods must be implemented in your formatter class: public abstract String getName(); This method returns a display name for your formatter. This is the name that is displayed to the user when choosing a formatter. Example: public String getName() { return “My Formatter”; }
public abstract booleanacceptsNativeType(int type); This method determines which native data types can be handled by this formatter. The available native types are defined in the super class of CustomFormatter: public public public public public public public public public public public public public public
static static static static static static static static static static static static static static
final final final final final final final final final final final final final final
int int int int int int int int int int int int int int
TYPE_NUMERIC TYPE_TEXT TYPE_DATE TYPE_TIME TYPE_TIMESTAMP TYPE_BOOLEAN TYPE_BINARY TYPE_GISPOINT TYPE_GISPOLYGON TYPE_GISLINESTRING TYPE_GISMULTILINE TYPE_GISMULTIPOLY TYPE_GISMULTIPOINT TYPE_GISMULTIGEOMETRY
Copyright Yellowfin International Pty Ltd 2010
= = = = = = = = = = = = = =
1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14;
www.yellowfin.com.au
guide_admin_v5.0.0.doc
207
public static final int TYPE_GISGEOMETRY
= 15;
Your class should return true for types that it supports, and false otherwise. Example: publicbooleanacceptsNativeType(int type) { // we accept text and numeric data if (type == TYPE_TEXT || type == TYPE_NUMERIC) { return true; } else { // don’t allow any other types return false; } }
Another example: publicbooleanacceptsNativeType(int type) { // we can handle any type return true; }
public abstract String render(Object value, intrenderType) throws Exception; This method renders a single value. The renderType argument determines which output type we are rendering for: public public public public
static static static static
final final final final
int int int int
RENDER_HTML RENDER_TEXT RENDER_CSV RENDER_LINK
= = = =
1; 2; 3; 4;
Example: public string render(Object value, intrenderType) throws Exception { if (value == null) return null; if (renderType == RENDER_LINK) { // Render the value for a drill-through link. // In this case we almost always want to return a generic // representation of the value. returnvalue.toString(); } // Return the formatted value
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
208
return “Value: “ + value.toString(); } The renderType parameter gives you a hint as to how to display the value. When outputting to document formats such as PDF/XLS, the RENDER_TEXT type is used.
Including embedded HTML in formatted values. In some cases you may want to return a HTML string from your formatter. This may be useful to customise the displayed text (eg. with embedded bold/italic tags), or present a link. This can be achieved by overriding the method: publicbooleanreturnsHtml() { return false; }
By default, this method returns false, indicating that any HTML entities in the formatted values should be escaped when outputting to HTML. By overriding this to return true, Yellowfin will not do any HTML escaping. An example of a formatter that includes HTML output: publicbooleanreturnsHtml() { return true; } public String render(Object value, intrenderType) throws Exception { if (value == null) return null; if (renderType == RENDER_LINK) { returnvalue.toString(); } else if (renderType == RENDER_HTML) { return “” + value.toString() + “”; } else { // rendering to non-html output returnvalue.toString(); } } See Appendix B for a full example of a custom formatter that takes a numeric value, and formats it as a currency value.
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
209
Compiling a Custom Formatter To compile your custom formatter class, you will need to include two libraries in your classpath: i4-core.jar i4-mi.jar
These are located in the WEB-INF/lib/ directory within the Yellowfin webapp – in a standard installation this is at: Yellowfin/appserver/webapps/ROOT/WEB-INF/lib/
Packaging a Custom Formatter Once you have compiled your custom formatter to use with Yellowfin, it must be packaged in a jar file along with a service descriptor file. The service descriptor file is named “com.hof.mi.interfaces.CustomFormatter” and is a text file containing the full classname of your formatter. This must be located in the “META-INF/services/” directory inside the jar file.
Using the example formatter from Appendix B, the contents of the jar file should look like this: META-INF/services/com.hof.mi.interfaces.CustomFormatter com/example/formatters/CurrencyFormatter.class
Your jar file can contain multiple custom formatter classes. In this case, each formatter’s full classname should be included on a separate line in the service descriptor file.
The jar file should be copied into Yellowfin’s WEB-INF/lib/ directory, and Yellowfin must be restarted to pick up the new formatter. You should then be able to select the formatter for columns of the appropriate data types:
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
210
Figure 179 Custom Formatter
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
211
Appendix
Code Examples Appendix A – CustomFormatter.java packagecom.hof.mi.interfaces; /** * Base class for Custom Formatters
* Copyright (c) 2007 Heaps of Flavour Pty Ltd
*/ public abstract class CustomFormatter extends UserInputParameters { private static final String cvsId = "$Id: CustomFormatter.java,v 1.1 2008-02-29 04:07:06 steve Exp $"; public public public public
static static static static
final final final final
int int int int
RENDER_HTML RENDER_TEXT RENDER_CSV RENDER_LINK
= = = =
1; 2; 3; 4;
/** * Returns the display name for this custom formatter * @return name */ public abstract String getName(); /** * Returns true if this custom formatter accepts the given native type. * @param type the data type to check * @return true/false */ public abstract booleanacceptsNativeType(int type); /** * Returns whether or not the value returned from this formatter * includes html code. If this returns false, a String value * will have any html entities parsed before output. * @return true/false */ Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
212
publicbooleanreturnsHtml() { return false; } /** * Method to render a value using this custom defined datatype format. * This is called internally when the report is rendered. The current render type is passed in so * that rendering can be modifed for each type (ie. CSVs cannot contain image data) * @param value - Object to be formatted * @paramrenderType - How the report is being rendered. * @return String - The value rendered to a String * @throws Exception - Throw an exception, Yellowfin will do an Object.toString() call if a exception is encountered */ public abstract String render(Object value, intrenderType) throws Exception; }
Appendix B – CurrencyFormatter.java packagecom.example.formatters; importcom.hof.mi.interfaces.CustomFormatter; importjava.text.*; public class CurrencyFormatter extends CustomFormatter { public String getName() { return "Currency Formatter"; } publicbooleanacceptsNativeType(int type) { // We only handle numeric types if (type == TYPE_NUMERIC) return true; return false; } public String render(Object value, intrenderType) throws Exception { if (value == null) return null;
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
guide_admin_v5.0.0.doc
213
if (renderType == RENDER_LINK) { // Return a generic version of the value returnvalue.toString(); } // Create a String representing the value as a currency value NumberFormatnf = new DecimalFormat("0.00"); return "$" + nf.format((Number)value); } }
Copyright Yellowfin International Pty Ltd 2010
www.yellowfin.com.au
