Creating a View

Overview

A view is a metadata layer used by Yellowfin to hide the complexity of database structures from report writers. The view is used to define which columns in your database you wish to make available for building reports. These fields may come from multiple tables and therefore will require joins to be defined (the business logic that links rows in a table together).

The two major steps in creating a view are:

Relationship entity diagram — selecting the tables you need from your database and defining how data in these tables are joined
View field selection — defining which fields you wish to make available from these tables and providing metadata for them

See Views Overview for more information.

Creating a view

You can create a view based on an existing data source or from a new data source that you have access to connect to. Make sure you have the required details of any new data sources before you begin.

Views can consist of one or more tables, and advanced users have the option of using Freehand SQL. In the steps below, we will create a new view with multiple tables, which automatically opens the View Builder.

Click on the Create button in the top right corner of your screen
Scroll down to the View menu item and click on it

The Create New View dialog box will appear, where you can choose an existing source for your new view, or create a new one

We'll use an existing sample data source, Ski Team, in these steps
Click on Ski Team from the list of data sources
The next step will appear, where you can choose the components of your new view
Click on the radio button for Multiple Tables
A warning message will appear
Click on the Create View button to launch the View Builder window
Double-click on the text New View in the top left corner
Type Tutorial View and then press your tab button to rename the new view

Yellowfin automatically saves your updates, but if you can also manually save your view as a draft at any time, allowing you to save your new view to a particular folder
Click on the Publish button to display the Save View dialog box
Triple-click on the phrase New View and type This view will be used to learn about Yellowfin
Click on the first dropdown that displays - - Select - - and select Tutorial from the list
Click on the next dropdown that displays - - Select - - and select Training from the list
If you typically use optional tags and wish to add some now, click on + Add tags and add your tags
Click on the Save As Draft button to save your view in draft format
Yellowfin will close the draft and take you back to the Browse page, where you can see your new draft view

top

Creating entity relationships

Building entity relationships is one of the key purposes of the View Builder. This allows you to define all the key relationships between your selected database tables.

Double-click on the draft view you created in the previous steps to edit it in the View Builder
From the table list on the left of the screen, find the table ATHLETEFACT and drag. and drop it onto your canvas
Repeat step 1 to add the CAMP and PERSON tables to your canvas
Click on the Create New Join tool on the ATHLETEFACT table
The New Join dialog box will be displayed
Use the dropdowns to create the following join:
Join Type: Inner Join
Cardinality: One to One
Join To: Person
In the Join Details section, click on Detect Joins to prompt Yellowfin to automatically detect the join
Click on the Save & Close button
The join will now be displayed as a line between the two tables, and hovering over the join icon will display the logic in a tooltip
Repeat steps 3 to 6 to create a similar inner join between ATHLETEFACT and CAMP where CampId = CampID
Drag and drop one of the three tables to a new location on the canvas: you can move these around as needed to make any diagrams easier to read as you add and join more tables

See Model for more information.

top

Selecting fields

From the View Builder you can select which fields to make available to end users for reporting. Only columns selected from each table in your Unattached list will be available for reporting.

Ensure your new view is loaded in the View Builder and that you've created any entity relationships (if not, follow the previous steps)
Click on the expand tool for the AHTLETEFACT table to expand the list of table columns
Click on the Edit Table Properties tool to see the table's properties displayed in the panel on the right of the screen
Click on the expand tool for the Columns section to see the list of columns
Check the checkboxes for AGEATCAMP, AGEGROUPATCAMP, COST, DEMOGRAPHIC, INVOICEDAMOUNT, INVOICEDATE, INVOICEESTIMATE and PERSONID
Click on any Edit Table Properties tool once more — to refresh the display of the columns on the canvas
The columns you selected will now appear in bold
Repeat steps 1 to 5 to select the following fields for the CAMP table:
CAMPDEMOGRAPHIC
CAMPDESCRIPTION
CAMPREGION
ISOCODE
Repeat steps 1 to 5 to select the following fields for the PERSON table:
DATEOFBIRTH
GENDER
ISOCODE
REGION

Your tables and columns are now ready to be prepared.

See Table Properties for more information.

top

Setting field folders & meta data

Initially in the preparation phase, fields selected from the previous steps (the modeling phase) will be in the Unattached panel in folders that represent the tables that they originated from. Assigning fields to folders lets you organize your fields in a way that is logical for the report writer, giving you the chance to group them differently to how the columns were grouped in the table structure in the data source.

Assigning a field to a folder associates meta data with that field so that report writers will be able to view and use the field when they build reports. Without that meta data, a field won't be available in the Report Builder.

You can assign fields to folders in the Available Fields panel. In the steps below, we'll show you how to add folders and then assign fields to them.

Ensure you have completed the modeling phase for your new view (if not, follow the previous steps)
Click on the Prepare tab

Your view will change to a tabular layout, showing you the contents of each column
Click on the Create tool in the bottom left corner, then click on Add/Edit Folders from the list

The Field Folders dialog box will appear, where you can create new folders, delete existing folders and and add fields to existing folders
Hover over the Dimensions folder
The burger bun on the left lets you reorder the folder by dragging and dropping, and the red X on the right lets you delete the folder
Click on the Select dropdown and click on Athlete
The column will now appear in the list of field folders
Repeat step 5 to add Athlete Location, Athlete Payment, and Camp
Click on Add Field Folder to display a new empty field and type Date Fields
Click on the Add button to add the new field folder to your field folder list
Click on the Submit button to add the columns as field folders
The view will now show the added field folders in the field/folder list in the panel on the left

Now that the folders exist, we can add some fields to them
Click on the Create tool, then click on Add Fields
A new panel will appear further left that lists the related table names, which can be expanded to display the table fields
Click on the ATHLETEFACT table to expand it
Drag and drop the following fields individually to the Athlete folder (or use your usual keyboard/mouse combination multi-select them): AGEATCAMP, AGEGROUPATCAMP and DEMOGRAPHIC
Click on the Athlete field folder to expand it
The three fields you just added will be listed
Repeat the last few steps to add COST, INVOICEDESTIMATE and INVOICEDDATE to the Athlete Payment folder
Repeat the last few steps to open the PERSON field folder and add REGION and ISOCODE to the Athlete Location folder
Repeat the last few steps to to add DATEOFBIRTH and GENDER to the Athlete folder

The fields added to field folders in Tutorial View will now be accessible in the Report Builder
Click on the white arrow for Table Fields heading to hide the column

top

Changing field content display

The View Builder lets you choose how field content should be displayed. For example, you can change those uppercase field names to be more user friendly, and make values display in a more meaningful way. In the steps below, we'll make some basic and useful changes, including updating some reference codes.

Ensure you have set up your field folders and meta data for your new view (if not, follow the previous steps)
Use your mouse to scroll to the right of the displayed fields and locate the Athlete Location section
Click on the field name ISOCODE to select it
Type Athlete Country and then press your tab key to confirm the field label update
Click on the triangle next to the field label to display a dropdown list and click on Edit Format

The Field Settings dialog box will be displayed, where you can find all the information associated with a field and update it to suit your needs
Click on the Details section to expand it, and read through the options available
Click on the Format section to display the available formatting options
Click on the Format dropdown and take a look at what options are available
Click on Reference Code from the list
A new option will become available for you to select what type of reference code should be applied
Click on the Reference Type dropdown to display a list of available reference codes associated with the Ski Team view, and select Country
Click on the X in the top right corner of the dialog box to close the box (your changes are dynamically saved)
The Reference Code field will now display country names instead of country codes
Locate the column within Athlete Payment for INVOICEDDATE and click on it to change its name to Invoiced Date
Locate the column within Athlete Payment for INVOICEDESTIMATE and click on it to change its name to Invoice Estimate

See Prepare for more information.

top

Formatting fields

Once you're happy with the basic display of your field values, you can style them further with formatting options such as currency symbols and colours.

Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps)
Locate the COST field within the Athlete Payment section and click on the dropdown, then click on Edit Format
Add a prefix of $ and set Decimal Places to 0
Further down the list, locate Color and click on the circle and pick red from the options: this will set the default color for the field when used in charts (which can be overridden in each report)
Click on the Details section and update the Display Name field to display Cost
Scroll back up to the top of the dialog box and click on the X in the top right corner to close the box (your changes are saved dynamically)
The Cost field values will be updated to display your formatting changes

top

Setting up calculated fields

Calculated fields let you create new fields based on existing fields. You can use simple calculations right through to freehand SQL for more complex needs. These can be useful for displaying information that's likely to be used regularly in reports, saving the report builder from having to recreate the field at the report level each time. Calculated fields can also be useful when used in conjunction with Guided NLQ, providing pre-calculated fields for your users to include in their queries. In the steps below, we'll create a simple calculation that produces a metric field as output. We'll aim to calculate profit by subtracting the cost from the invoice figure.

Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps)
Click on the Create tool at the bottom of the screen, then click on Calculated Field

The Calculated Field dialog box will appear
In Calculated Field Name, type Profit
Click on the Field Folder dropdown and select Athlete Payment
From the Search dropdown, start typing invoice and click on INVOICEDAMOUNT to select it for use in the calculation
From the collection of calculation tools , click on the minus tool to add a minus sign to your calculation
From the Search dropdown, locate and click on the COST field
Click on the Validate button to check that your calculation has no errors
If there are no validation issues, a successful validation message will be displayed
Click on the Save button to save this new calculated field
Scroll to the left of your field table to check you can see the new field at the end of the Athlete Payment section

See Calculated Fields for more information.

top

Building date hierarchy fields

Date hierarchy fields are essentially calculated fields that let you build levels of a hierarchy based on a single date field in your data source. Date hierarchies enrich analysis through more flexible drill down options, a better Guided NLQ end-user experience, and for use with time series charts. Date hierarchies are created by using granularity to dictate day/week/month/quarter/year etc. Read more about date granularity if you'd like further information before performing these steps.

To build a date hierarchy, you must start with a field that already uses a date format, rather than a dimension (such as a string of characters) or a metric (such as a number). Yellowfin's tutorial database, Ski Team, already has a date hierarchy set up for INVOICEDDATE, so we'll use a duplicate of this field that we created in one of the previous steps and we'll create a new set of date fields for a hierarchy.

Building a date hierarchy requires four components, as outlined in the table below.

Comonent	Description	Example
Field	This component dictates which field from your view should be used for your new date hierarchy. It must use date format to be available for selection from the dropdown.	Invoiced Date
Date Function	This component lets you choose the granularity of your date field, from day name through to year end date.	Month Start Date
Format	This component lets you choose how to represent this date information, such as a short day name or a business week number, on charts and in tables and any other content within Yellowfin.	Month Name
Field Folder	This component provides the list of available view fields so you can store your new date field in an appropriate location

Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps)
Click on the Create tool at the bottom of the screen, then click on Date Function
From the Create Date Function dialog box, click on the dropdown for Field and click on Invoiced Date
Click on the dropdown for Date Function and select Month Start Date
You will notice that each time you select something from the dropdown, a sample date is displayed on the right
Click on the dropdown for Format and select Month Name
Click on the dropdown for Field Folder and select Date Fields
Click on the Save button to save your new date field
Repeat steps 2 to 7 to add a new date field with the following details:
Field: Invoiced Date
Date Function: Year Start Date
Format: Year
Field Folder: Date Fields

Your Date Fields field folder now has two new fields in it, and to keep our date field together, we can move the original date field here too
Use your mouse to drag and drop Invoiced Date from the Athlete Payment field folder to the Date Fields field folder to complete the hierarchy

top

Building a drill down hierarchy

The drill down hierarchy lets report users drill down within a dimensional hierarchy by limiting the result set as they select one level to the next. For example, drill from Year (2022) to Month (August), and so on.

When creating a drill down hierarchy, you need to start from the top level and work your way down. In this example, we will create a Year > Month > Date hierarchy, so we will start with the Year field at the top.

Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps)
In the field folder section, hover to the right of the Year Start Date field to display three blue dots
Click on the dots to display a menu and select Drill To to display a sub-menu

As we're drilling from year to month, we need to connect our year field to our month field
Click on Month Start Date in the sub-menu
The two date fields will now show that they're joined in a hierarchy with a grey line
Hover to the right of the Month Start Date field and click on the dot to display a menu
Click on Drill To, and then on the new sub-menu item, also called Drill To

The new sub-menu will appear as soon as you have associated any two date fields, as we have already done
Click on Invoiced Date to link our monthly field to our original date field
All three date fields will now be linked from their top level (first in the list) to their lowest, most granular level (last in the list)

Your drill down hierarchy is now ready to be used in reports.

See Drill Down Hierarchies for more information.

top

Reviewing and publishing your view

At any time when editing your view, you can check the summary information directly from the top toolbar. It's a good idea to check this before you publish a view. In addition, you can access other details, such a security and performance details from this toolbar, and these areas are covered elsewhere in this section of the wiki. We'll just show you the basics of where to find them here.

Locate the toolbar at the top of your View Builder screen
Hover over each of the options to see what is available
Click on the View Info tool to display the View Summary dialog box
Click on each of the tabs to see what information is available, including the general information about the view, which fields it contains, its raw SQL, and any reports using it, which can be useful if you need to edit an existing view and wish to check any reliant content
Click on the X in the corner of the dialog box to close it
Click on the Publish button
Yellowfin will now let you publish this view, although if you wish to save it as a draft, that's also possible
To save the draft, click on the dropdown tot he right of the Save and Publish button
To publish your new view, click on the Save and Publish button
Yellowfin will save and close the view, and return you to the Browse page, where your view icon will turn from grey (draft) to green (published)

Your view may now be used to create new reports and charts.