Defining DeepSee Models
Details of Defining Levels
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter provides details on defining levels. It discusses the following topics:

See the previous chapter for basic information, including information on specifying the source values.
Ensuring Uniqueness of Member Keys
If a level has a parent level, it is possible for member keys not to be unique. For details, see Defining Member Keys and Names Appropriately,” earlier in this book. InterSystems recommends that you ensure that member keys are unique for each level, so that you can easily access any member directly. Also, member keys must be unique within the first 113 characters.
If it is possible, considering your source values and your hierarchies, to have duplicate member keys, do the following:
Specifying the Null Replacement String for a Level
For a level, the Null replacement string option specifies a string to use in place of any null values. This string takes precedence over the null replacement string that you specify for the cube.
Defining a List-Based Level
You can define a level that is based on a list, an array, or a Caché class relationship. Each distinct list item is indexed as a separate member of the level. (For such levels, be sure to read Using List-Based Levels,” earlier in this book.)
DeepSee can directly use a source value that has the type %List, that is in the format returned by the $List function, or that is a character-delimited list. For other formats, you must convert the source value and then use it.
Note:
A list-based level must be contained in its own hierarchy; there cannot be another level in this hierarchy.
Defining a Level When the Source Value Is in a Standard Format
If the source value is available in $List format, in %List format, or in a character-delimited list, define the level as described earlier, with the following additional steps:
  1. For Source value list type, select one of the following:
  2. If you selected Other delimited, then for List delimiter, specify the character used to delimit the list.
Defining a Level When the Source Value Is in Another Format
If the source value is not available in $List format or as a character-delimited list, use the following approach:
  1. Create a utility method that converts it into one of the required formats. For example:
    ClassMethod GetAllergies(ID As %Numeric) As %List
    {
        Set allergies=##class(DeepSee.Study.Patient).%OpenId(ID,0).Allergies
        If (allergies.Count()=0) {Quit $LISTFROMSTRING("No Data Available")}
        Set list=""
        For i=1:1:allergies.Count() {
            Set $LI(list,i)=allergies.GetAt(i).Allergen.Description
            }
        Quit list
    }
    
    Notice the following points:
    Tip:
    You can place this utility method within the cube class. Also, you can easily test the method within the Terminal.
  2. Within the Architect, when defining the level, do the following:
    1. For Expression, invoke the utility method and pass %source.%ID to it as an argument. For example:
      ##class(Cubes.StudyPatients).GetAllergies(%source.%ID)
    2. Select List.
    3. If the source value is a character-separated list, then for List delimiter, specify the character used to delimit the list.
      If this is null, DeepSee assumes that the source value is in $List format.
Examples
In the SAMPLES namespace, the Patients cube provides the following examples of list-based levels:
Defining a Time Level
A time level groups the data according to a date/time value in the data (such as a birth date, order date, response date, and so on). This section describes how to create this type of level. It discusses the following topics:
Introduction
A time level is a level defined within a time dimension. For an example of a set of time levels, see the BirthD dimension in the Patients cube. The BirthD dimension is based on the BirthDate property in the source class. This property contains the patient’s birth date.
Each level in this dimension uses an option called Time function to extract a specific part of the birth date. For example, the Year level extracts the year part of the date.
As a result:
With time levels, you can use a special member called NOW, which uses the current date (runtime) and accesses the appropriate member of the level.
Note:
By default, DeepSee uses the Gregorian calendar. You can instead use a Hijri calendar. In that case, the member names are different from those listed here (and the records are assigned to members as suitable for that calendar).
Using the Locale to Control the Names of Time Members
When you build a cube, DeepSee can use the current server locale to determine the names of members of any time dimensions. It is useful to check the relevant option, as follows:
  1. This page displays the current server locale.
  2. Examine the setting of the option Use locale date/time/number formats and change it if needed.
    If this option is Yes, then DeepSee considers the locale shown on this page when it builds or synchronizes the members of time dimensions.
If you later change the locale, it is necessary to recompile and rebuild the cube.
Also, see Locale Definitions in the Caché Additional Configuration Settings Reference.
Defining a Time Level
To define a time level:
  1. Define a dimension as described earlier, with two changes:
    When you define the dimension, the system creates the dimension and a hierarchy.
  2. Define a level within this hierarchy.
    In this case, you will see different options than you saw for a data dimension.
  3. For Extract value with function, choose a function that extracts the desired part of the date/time value. Use one of the values in the following table. Note that for a Hijri calendar, not all functions are supported.
    Time function How Value Is Stored How Value Is Displayed Available in Hijri Time Dimensions?
    MinuteNumber 60958,5083 01:24 Yes
    HourNumber 60958,5083 1am Yes
    DayMonthYear 60958 Nov 24 2007 Yes
    DayNumber 24 24 Yes
    WeekYear 2007W47 2007W47 No
    WeekNumber 47 47 No
    MonthNumber 11 November Yes
    MonthYear 200711 November 2007 Yes
    QuarterNumber 4 Q4 Yes
    QuarterYear 20074 Q4 2007 Yes
    Year 2007 2007 Yes
    Decade 1950 1950s Yes
    DayOfWeek 1 Sunday No
  4. For Time Format, optionally specify a format string that describes how the dates should be displayed. For details, see the first subsection.
  5. If you are using the Hijri (observed) calendar, use the class %Calendar.Hijri to add lunar observations.
    For details, see the class reference for %Calendar.Hijri.
For the WeekYear and WeekNumber functions, DeepSee follows the ISO 8601 standard, which assigns a week number (from 1 to 53) for every date within a year. Because 7 is not a factor of 365 or 366, some years have 53 weeks. The first week of a year is defined as the week that contains the first Thursday of the year. That means for some years, January 1 is in the last week of the prior year. Similarly, December 31 may be in the first week of the next year.
Specifying a Time Format
For a level within a time-type dimension, the Time Format specifies the format of the display names of the members of the level. DeepSee applies this formatting at query execution time. There is no effect on how values are stored or indexed.
For the Time Format attribute, you specify a string that consists of the following case-sensitive date pieces and other pieces:
Piece Replaced By
y Year number
q Quarter number
mmmm Full month name
mmm Short month name
mm Month number with leading 0 if needed
m Month number without a leading 0
dddd Full day name
ddd Short day name
dd Day number of the month with leading 0 if needed
d Day number of the month without a leading 0
\x x
period (.), slash (/), hyphen (-). space Unchanged
other characters Ignored
All names are based on the current server locale. See Using the Locale to Control the Names of Time Members,” earlier in this chapter.
Important:
For time levels, member display names must be unique. Also, a member can have exactly one display name. These rules mean that not all the preceding date pieces are appropriate for all time levels.
The following table lists the date pieces that are suitable for different time levels.
Kind of Level (Setting for Extract value with function) Suitable Date Pieces Default timeFormat Other Examples
Year y y (2004) \F\Y y (FY 2004)
\F\Y (FY2004)
QuarterYear y, q \Qq y
(Q3 2004)
\Qq \F\Y y (Q1 FY 2004)
\Qq \F\yy (Q1 FY2004)
QuarterNumber q \Qq (Q3)  
MonthYear y, mmmm, mmm, mm, m mmmm y (February 2004) mmm y (Feb 2004)
y-mm (2004–02)
mm/y (02/2004)
m/y (2/2004)
\F\Yy-mm (FY2004-02)
MonthNumber mmmm, mmm, mm, m mmmm (February) mmm (Feb)
mm (02)
m (2)
DayMonthYear y, mmmm, mmm, mm, m, dddd, ddd, dd, d mmm dd y
(Feb 1 2004)
mmmm dd y (February 03 2010)
y-mm-dd (2010-02-03)
mm/dd/y (02/03/2010)
m/d/y (2/3/2010)
dddd, mmmm dd y (Wednesday, February 03 2010)
ddd, mmm dd y (Wed, Feb 03 2010)
DayNumber dd, d d (1) dd (01)
DayOfWeek dddd, ddd, dd, d dddd (Tuesday)
dd (03)
d (3)
Using a Custom Data Type
You can base a time level directly on a property that uses a custom data type, in any of the following scenarios:
See the implementation of %ConvertDate() in %DeepSee.Utils.
Time Levels and Hierarchies
As noted earlier, the order of levels in a hierarchy affects how the members of the levels are created. For any two adjacent levels in the same hierarchy, the first level (level A) becomes the parent of the second level (level B). The hierarchy is a parent-child hierarchy, which means the following:
Therefore, a level based on Year cannot be the parent of a level based on Quarter, for example. Consider two patients, one born in Q3 2007 and one born in Q3 1982. These two patients both belong to the same member (Q3) of the Quarter level but belong to different members (2007 and 1982, respectively) of the Year level.
The following table lists time levels and their typical child levels. For reference, it also shows example members of each level:
A Level Based on the Time Function... Has Members Like This... Child Level Is Typically Based on the Time Function... Notes
Decade 1950s Year, QuarterYear, MonthYear, or DayMonthYear These levels use all parts of the date
Year 2007 QuarterYear, MonthYear, or DayMonthYear
QuarterYear Q4 2007 MonthYear or DayMonthYear
MonthYear November 2007 DayMonthYear
WeekYear 2007W47 WeekNumber
DayMonthYear Nov 24 2007 No typical child level
QuarterNumber Q4 MonthNumber These levels are independent of the year
MonthNumber November No typical child level
WeekNumber 47 No typical child level
DayNumber 24 No typical child level These levels are independent of the year and independent of the part of the year
DayOfWeek Wednesday No typical child level
HourNumber 1am MinuteNumber These levels are independent of the day
MinuteNumber 01:24 No typical child level
Handling a Calendar That Has a Date Offset
In some cases, you may need a time level to match a financial calendar that includes a date offset. For example, in many companies, the financial year starts 1 Oct. Consider the following pivot table:
In the innermost grouping, this pivot table groups the records by the actual period (year and month, combined). For example, there are 14 records associated with the actual period October 2001. The periods are grouped into fiscal quarters, and the fiscal quarters are grouped into fiscal years. Note that the quarter FY 2002–Q1 includes October, November, and December of 2001.
For such a time level, edit the cube class in Studio and specify the timeOffset and timeFormat properties of the <level> element. For the example shown here:
For information, see Specifying a Date Offset in the appendix Reference Information for Cube Classes.”
Examples
The SAMPLES namespace provides the following examples of time levels:
Defining Custom Time Levels
You can define custom levels that use time in other ways. To do so:
Example
In the Patients cube in the SAMPLES namespace, see the BirthWeekdayD dimension. For this dimension, Expression is as follows:
$system.SQL.DAYNAME(%source.BirthDate)
This method executes the DAYNAME() method in the %SYSTEM.SQL class. That class provides a large set of methods that handle date values.
With this dimension, you can create pivot tables like this:
This sample dimension defines only a single level. With the appropriate logic, you can define a hierarchy with weeks, months, and quarters in a 4–4–5 calendar, for example.
Defining an Age Level
An age level groups the records according to an age value in the data, relative to the cube build date. For example, it could group patients by their ages (as computed at cube build time).
Note:
To keep age levels accurate, it is necessary to rebuild the cube; the DeepSee cube synchronization feature has no effect on them. Thus, age levels are not generally recommended. If you need to group records by age, InterSystems recommends that you instead define calculated members; see Defining Age Members,” later in this book.
To define an age level:
  1. Define a dimension as described earlier, with two changes:
    Important:
    If you specify Property, the property must be in $Horolog format or must be of type %TimeStamp (or a subclass).
    If you specify Expression, the source expression must return data in $Horolog format.
    When you define the dimension, the system creates the dimension and a hierarchy.
  2. Define a level within this hierarchy.
    In this case, you will see different options than you saw for a data dimension.
  3. For Extract value with function, choose a function that extracts the desired part of the time value. Use one of the following values:
Note that the Patients sample does not demonstrate this type of level; the Age, Age Bucket, and Age Group levels group patients in the fictitious study according to their ages at the date of the study; this is common practice with retrospective studies.
Specifying a Range Expression
For any level in a data dimension, you can use the Range expression option. This option lets you use new values in the place of the actual source values. For numeric data, this transformation can replace source values with discrete bins. For any data, this transformation can specify replacement strings.
In either case, the new values become the names of the members of the level. The values also become the keys for the members.
To specify a range expression:
  1. Click the Find button next to the Range expression field.
    The Architect displays a page like the following:
  2. For Form of original values, choose either Numeric or Strings.
    This choice affects the form of this page.
  3. Add a series of replacements as follows:
    The order of the rows determines the default sort order of the members of this level.
  4. Click OK to close this page.
    The Range expression field now shows something like this (for numeric bins):
    (,0]:None;(0,0.2):1-19%;[0.2,0.5):20-49%;[0.5,1]:50%+;
    Or this (for string data):
    NONE:None;MINR:Minor;SERS:Serious;FATL:Fatal;UNK:Unknown;
To make member names localizable, see Manually Specifying the Members of a Level,” in the chapter “Using Additional Advanced Features of Cubes and Subject Areas” in the Advanced DeepSee Modeling Guide.
Defining Numeric Bins
When you define numeric bins, the Range Expression editor looks like this:
Each row in this table defines a numeric bin as follows:
For this level, DeepSee ignores records whose source values do not fall into any of the given bins.
Defining String Replacements
When you define string replacements, the Range Expression editor looks like this:
Each row in this table defines a string replacement as follows:
For this level, DeepSee ignores records whose source values do not match any of the given original values.
Examples
The SAMPLES namespace provides the following examples of range expressions:
Configuring a Level to Use Display Values
A Caché class property can have both a stored value and a displayed value, via the VALUELIST and DISPLAYLIST property parameters. For example:
Property Gender As %String(VALUELIST = ",F,M", DISPLAYLIST = ",Female,Male");
When you use such a property as a level, by default, the system uses the value given in the VALUELIST parameter.
In the level definition, if you select the Use value in DISPLAYLIST option, the system uses the value given in the DISPLAYLIST parameter instead. For example:
Examples
The SAMPLES namespace provides the following examples of levels that use the Use value in DISPLAYLIST option:
Using Property Values as the Member Names
By default, the source values for a level become the names for the members of the level.
You can instead specify the member names by defining a property for this level and using the values of that property as the member names. This is useful in the following scenarios:
To use property values as the names for members of a level:
  1. Define a property for this level, as described in the next chapter.
    Each member of the level has a value for this property.
  2. For this property, select the Use as member names option.
  3. Optionally, to retrieve the property value at runtime, select the Get value at run time option.
    If you use this option, note the following requirement: For the parent level (the level that contains the property), the source property or source expression of that level must evaluate to an ID. The system assumes that (at least for this level), the source data is normalized. That is, for the level, the data is in a different table and the source table contains a link to that table.
Examples
The SAMPLES namespace provides the following examples of levels that have properties with Use as member names, Get value at run time, or both:
Specifying Tooltips for Members
When a user hovers the cursor near a member name in the Analyzer, DeepSee displays a tooltip. By default, the member name is the tooltips. You can specify additional information to display in these tooltips for any level. To do so:
  1. Add a property to that level. For the source value, use a value that contains the desired tooltips.
  2. In Studio, edit the cube definition and modify the newly added property. For this property, specify the isDescription attribute as "true". See <property> in the appendix Reference Information for Cube Classes.”
    Now the tooltip is the member name, followed by a hyphen, followed by the value of the specified property. For example:
This is useful when you want to use short member names, for reasons of space, but also need to provide a fuller description of each member.
Specifying the Sort Order for Members
This section describes how to control the sort order for members of a level.
For a Data Level
For a data level, by default, members are sorted as follows:
To change the sort order for the members of a data level, use any of the techniques described in the following subsections.
Specifying the Level Sort Option
The simplest way to specify the sort order for members of a level is to set the Sort option for the level. Select asc, asc numeric, desc, or desc numeric.
asc and desc sort members alphabetically, in ascending or descending order, and asc numeric and desc numeric sort numerically, in ascending or descending order.
Sorting Members by Property Values
To sort the members of a level in order by property values:
  1. Define a property for this level, as described in the next chapter.
    Each member of the level has a value for this property.
  2. For this property, select asc, asc numeric, desc, or desc numeric for Sort members by property value.
    asc and desc sort members alphabetically, in ascending or descending order; asc numeric and desc numeric sort numerically, in ascending or descending order.
    (By default, no value is selected, and the property does not affect the sort order of the members.)
If you specify Sort members by property value within more than one property of a level, the members are sorted by the first of those properties. Then they are subsorted by second of those properties, and so on.
For an example, see the Patients cube in SAMPLES namespace. In this cube, the Allergy Severities level has a property that is configured with Sort members by property value. This property is based on a source expression that returns a numeric value.
Using Property Names as Member Names
An alternative approach, similar to the preceding, is as follows:
  1. Use a source expression for the level that forces the members in the desired order, even if the member names are not desirable.
  2. Define a property for this level, as described in the next chapter.
  3. For this property, specify a source value or source expression that returns a better name for each. For this property, select the option Use as member names.
Listing the Members in the Desired Order
To list the members in the desired order, edit the cube class to add <member> elements to the level. For information, see Manually Specifying the Members of a Level,” in the chapter “Using Additional Advanced Features of Cubes and Subject Areas” in the Advanced DeepSee Modeling Guide.
For a Time Level
By default, members of a time level as sorted in increasing order by time.
To specify a different sort order for the members, specify Sort option. Choose either asc (ascending in time) or desc (descending in time).
Making the Member Names Localizable
For cubes as well as dimensions, hierarchies, levels, and other cube elements, you can specify the Display name value, for which you can later define translations in different languages. The same technique is available for members of a level, if you know all the members in advance and if you edit the cube class in Studio. See Manually Specifying the Members of a Level,” in the chapter “Using Additional Advanced Features of Cubes and Subject Areas” in the Advanced DeepSee Modeling Guide.
For a time dimension, the current locale determines the names of members of a time dimension. (See Using the Locale to Control the Names of Time Members,” earlier in this book.) If you change the locale, it is necessary to recompile and rebuild the cube.
Defining Dependencies Between Levels in Different Hierarchies
In some cases, there is a virtual dependency between levels that are not in a hierarchy with each other. For example, you might have a cube with a Country level and a Product level. These levels are logically independent from each other; theoretically any product could be sold in any country. It would not make sense to put these levels into a hierarchy in the same dimension.
But if specific products are sold only in specific countries, there is a virtual dependency between these levels. When a user selects a country, it is desirable to show only the products appropriate for that country. In such a case, you can add a dependency between the levels as follows:
  1. Open the cube class in the Architect.
  2. Find the definition of the dependent level.
  3. Edit the value of the Depends on option. Use a value like the following:
    [dim].[hier].[level]
    Where dim is the name of the other dimension, hier is the name of the hierarchy, and level is the name of the level. [dim].[hier].[level] is the MDX identifier of the level.
    For example:
    [Region].[H1].[Country]
    If the level depends on multiple levels, specify a comma-separated list. For example:
    [dim1].[hier1].[level1],[dim2].[hier2].[level2]
    You could also specify a relationship instead of a level.
This option attribute adds another index to the fact table and consequently consumes more disk space. Use this attribute sparingly. Also, this option is completely unrelated to the DependsOn compiler keyword.
Example
To see how this feature works, try the following demonstration:
  1. In the Architect, open the Patients cube.
  2. Edit the Patient Group level of the PatGrpD dimension. Specify Depends on as follows:
    [HomeD].[H1].[ZIP]
  3. Save and compile the cube.
  4. In the Terminal, execute the ReassignPatients() method in DeepSee.Populate, in the SAMPLES namespace:
    d ##class(DeepSee.Populate).ReassignPatients()
    This method modifies the data for this sample so that there is a virtual dependency between the Patient Group level and the ZIP code level. Patients in specific ZIP codes belong either to Patient Group A or no group; patients in the other ZIP codes belong either to Patient Group B or no group.
    The method synchronizes the cube, so there is no need to rebuild this cube after executing this method.
Then open the Basic Dashboard Demo and try the Home ZIP Code and Patient Group filters. When you choose a ZIP code, that affects the list in the Patient Group filter.
Specifying the Field Names in the Fact Table
When you compile a cube class, DeepSee generates a fact table class and some related classes. When you build a cube, DeepSee populates these tables, which are described in Details for the Fact and Dimension Tables.”
By default, DeepSee generates the names of the fields in the fact table, but you can specify the field names to use instead. To do so, specify a value for the Field name in fact table option for each applicable level. Take care to use unique names. This option is not available for time levels or iKnow levels.
Important:
For Field name in fact table, be sure not to use an SQL reserved word. For a list of the SQL reserved words, see Reserved Words in the Caché SQL Reference. The name must start with either a letter or a percent sign (%). If the first character is %, the second character must be Z or z. For more details on restrictions, see Class Members in the appendix “Rules and Guidelines for Identifiers” in the Caché Programming Orientation Guide. Also, do not use fact or listing, in any combination of lowercase and uppercase characters.