Guidelines for using EQL to define a view

The view definition EQL defines the content of the view. When defining a view, you need to keep the following guidelines in mind. For complete details on EQL syntax, see the EQL Reference.

View DEFINE and SELECT statements

A view definition may contain multiple DEFINE and SELECT statements.

The last statement must be a DEFINE statement that contains all of the view attributes. The name of that DEFINE statement is used as the view key, and cannot be changed. If you try to change the view key for an existing view, the corresponding components will no longer work.

View RETURN statements

Although the EQL Reference uses RETURN statements in its code examples, you should not add RETURN statements to any views you create in Studio. Studio automatically wraps your EQL with a RETURN statement.

You can copy EQL examples from the EQL Reference into a view and then delete the RETURN statement. This way, Studio wraps the EQL with an implicit RETURN and the example works correctly. If you explicitly add the RETURN the view fails.

Providing the source view for the view attributes

For all custom view definitions, you must use the FROM parameter to provide the original source of the view attributes.

For example, if you create a copy of a base view, you will see that the view definition includes the view key for that base view. 

DEFINE Customers as SELECT 
ARB(Customer_Name) as Customer_Name,
ARB (Customer_Address) as Customer_Address,
ARB(Cities) as Cities,
ARB(States) as States,
ARB(CustomerZip) as CustomerZip,
ARB(LatLong) as LatLong,
ARB(Business_Types) as Business_Types,
ARB(CustomerAgreedDaysCredit) as CustomerAgreedDaysCredit,
ARB(Credit_Rating) as Credit_Rating,
ARB(CustomerDiscount) as CustomerDiscount,
SUM(Number_of_Cases_Sold) as TotalCustomerCases,
CountDistinct(Transaction_Id) as TotalCustomerTransactions,
SUM(GrossDollars) as TotalCustomerGross,
SUM(MarginDollars) as TotalCustomerMargin,
SET(Shipping_Companies) as ShippingCompaniesUsed
FROM "wine-sales"
GROUP BY Customer_Id

When identifying the source view, make sure to use the view key, and not the view display name.

Also, when referring to another view, you can only refer to the final DEFINE statement that identifies the attributes for other view. You cannot refer to any intermediate DEFINE or SELECT statements in that view definition.

Grouping a view

All custom views should have at least one grouping (GROUP BY) attribute.

Studio uses the GROUP BY attributes as the identifying attributes for the view records.

If there are no GROUP BY attributes, then there are no identifying attributes, and the view cannot be used for Record Details or Compare. On component edit views, the Record Details and Compare options are then automatically disabled.

Using multi-value attributes for grouping

When using a multi-value attribute for grouping, you must indicate whether to group by the individual values, or by the sets of values assigned to the records.
  • To group by the sets of values, use GROUP BY.
  • To group by the individual values, use GROUP BY MEMBERS. The syntax for GROUP BY MEMBERS is slightly different from GROUP BY: 
    GROUP BY MEMBERS(attributeName) as attributeName

For example, the following data shows available product colors, and the number of outlets that carry each product:

ItemColors AvailableOutlets
Red, Blue 3
Red 5
Red, Blue, White 6
Red, Blue, White 4
Red, Blue 1
If you are calculating the total number of available outlets grouped by the item color, then:
  • If you use DEFINE NewView as SELECT TotalOutlets as sum(AvailableOutlets) FROM Sales GROUP BY ItemColors, then the result is:
    ItemColors TotalOutlets
    Red, Blue 4
    Red 5
    Red, Blue, White 10
  • If you use DEFINE NewView as SELECT TotalOutlets as SUM(AvailableOutlets) FROM Sales GROUP BY MEMBERS(ItemColors) as ItemColors, then the result is:
    ItemColors TotalOutlets
    Red 19
    Blue 14
    White 10

Providing aggregation methods when grouping view attributes

When defining a view, if you are using grouping, then you must provide an aggregation method for all of the attributes in the view, except for the grouping attributes.

If you using aggregation to derive attributes from the physical attributes, then you can use the applicable aggregation methods for the attribute data type. See Aggregation methods and the data types that can use them.

In some cases, you may want to display the actual values of physical attributes in a grouped view. For example, when you are grouping by product identifier, then you may want to include the product name, product description, and available sizes. When using grouping for a view, to include the actual values of physical attributes:
  • For a single-value attribute, use the ARB aggregation method. For example: 
    SELECT ARB(Region) as Region

    Note that if there are different values for an attribute within the grouping, Studio chooses a single, arbitrary value.

  • For a multi-value attribute:
    • To get a single, arbitrary value for the attribute from each record, use the ARB aggregation method. For example: 
      SELECT ARB(ItemColors) as ItemColors
    • To get the complete set of values for the records, use the SET_UNIONS aggregation method. For example: 
      SELECT SET_UNIONS(ItemColors) as ItemColors

If you are not using grouping, then you do not need an aggregation method. For example: 

SELECT ProductName as ProductName

In this case, you do not need to specify the name, so you could also use: 

SELECT ProductName

Naming view attributes

When naming attributes in a view:
  • Do not change the name of (also referred to as aliasing) attributes from the physical data.

    For example, for a "Region" attribute, when you add the attribute to a view, define it as "Region". 

    SELECT ARB(Region) AS Region

    Do not define the attribute under a different name, such as: 

    SELECT ARB(Region) AS RegionList

    End users can only refine by attributes that are present in the physical data. If you alias an attribute, then end users cannot use it for refinement.

  • Do not give a derived attribute the same name as an attribute from the physical data.

    For example, for an attribute that averages the values of the "Sales" attribute, do not define the new attribute as: 

    SELECT AVG(Sales) as Sales

    Instead, use something like: 

    SELECT AVG(Sales) as AvgSales

    This is also to prevent end users from trying to refine by an attribute that is not in the physical data.

Sample view definition

Here is an example of an EQL query for generating a list of products, including price, available colors, and sales numbers, from a single data set consisting of individual transactions. 

DEFINE Products AS SELECT
ARB(ProductSubcategoryName) AS ProductSubcategoryName,
ARB(ProductCategoryName) AS ProductCategoryName,
ARB(Description) AS Description,
SET_UNIONS(AvailableColors) AS AvailableColors,
AVG("FactSales_SalesAmount") AS AvgSales,
SUM("FactSales_SalesAmount") AS SalesSum,
AVG(StandardCost) AS AvgStandardCost,
AVG(ListPrice) AS AvgListPrice,
(AvgListPrice - AvgStandardCost) AS Profit
FROM Sales
GROUP BY ProductName