Advanced Reference Materials

Advanced Constraints Grammar

This section offers a more detailed explanation of constraints and other grammatical operators for creating an advanced constraints expression.

<constraint>[|…]

<constraint> =                                                 <specifier><operator><value>

<specifier> =                                                    <ANY> | <ALL> | <member-expr>

<member-expr> =                                          [] member-name | member-alias-name []

<value> =                                                         a number

<ANY> =                                                         Any

<ALL> =                                                          All

<operator> =                                                  <less-than> | <greater-than> | <less-than-or-equal-to> | <greater-than-or-equal-to> | <equals> | <not-equals> | <top> | <bottom>

<less-than> =                                                   <

<greater-than> =                                              >

<less-than-or-equal-to> =                                <=

<greater-than-or-equal-to> =                           >=

<equals> =                                                       =

<not-equals> =                                                 <>

 <top> =                                                           top

<bottom> =                                                     bottom

<AND> =                                                        And

<OR> =                                                           Or

[<quote-char>] =                                              ‘ | “

OLAPPivot Function

This function takes several parameters that allow the user to configure ranges of data in their Excel spreadsheet. The following defines the parameters required by the OLAPPivot function:

OLAPPivot

(database, cube, Range, dim1Members, constrainZeroRows, constrainZeroColumns, rowConstraint, colConstraint, DeleteRows, DeleteColumns, dimNMembers)

DatabaseName

The first parameter is the database name. This is the name of the currently open database. It is the same value that all functions take as their first parameter.

CubeName

The second parameter is the name of the cube. The cube maintains a list of dimensions and all the Fact Data. The number of additional parameters to this function depends on the number of dimensions in the selected cube.

Range

This parameter specifies the initial rows and columns to populate with the values returned from the function. If this Range is larger than the number of valid rows and columns returned from the function, the rows at the bottom will be left blank. We never want to delete rows or columns as it may affect other data entered by a User. If this range is not big enough to handle the returned values, additional rows or columns will be inserted and this parameter in the function will be updated to reflect the new range.

ConstrainZeroRows

This is a numeric parameter that, if 1, suppresses rows with zero values. (“0” would show all rows.)

ConstrainZeroColumns

This is a numeric parameter that, if 1, suppresses columns with zero values. (“0” would show all columns.)

RowConstraint

This is a string that must match the syntax of our constraints syntax in proCube. This parameter determines the rows that will be displayed in Excel.

ColumnConstraint

This is a string that must match the syntax of our constraints syntax in proCube. This parameter determines the columns that will be displayed in Excel.

DeleteRows

This is a numeric parameter that, if 1, Deletes extra rows. Be careful when specifying a true value for this function as it can delete data unexpectedly.

DeleteColumns

This is a Boolean parameter that, if true, Deletes extra columns. Be careful when specifying a true value for this function as it can delete data unexpectedly.

Members

The members for each dimension must be specified in the order of the dimensions in the cube. This is the same way the OLAPReadWrite and OLAPTable functions work. We support additional criteria, however, for this function. To display Members along the row or column axis, you must specify the Rows or Columns keyword before the member specifier. You can specify nesting by using a subscript identifier with the Rows or Columns identifier. The Member parameter can consist of any of the following:

  • A member or list of members. (see OLAPTable function)

  • The string ".All" This will return all the members in the current dimension in the order defined by the edit dimension dialog.

  • The string ".Children:MemberName" This will return all the children of the member specified by MemberName.

  • The string ".Subset:SubsetName" This will return all the values in the dimension subset named SubsetName.

  • The string ".Level:x" where x is the requested level. This will return all members at a specified level.

  • The string ".Details" which will return a list of all detail members.

  • The string ".Aggregates" which will return a list of all aggregate members.

proCube Cube Formula Grammar

Formulas take the following general form:

<LHS> = <RHS><semicolon>

Where,

LHS is the Left Hand Side, where you specify the data point or data range that will be acted upon by the formula (destination)

RHS is the Right Hand Side, where you define the actual calculation to be performed on the data point or data range (source)

The formula is followed by a <semicolon> ;

Left-Hand Side (LHS) of Formulas

Recall that the left-hand side of a formula allows you to specify the specific fact data point or data ranges that the formula should act upon.

General Form

<LHS> = <qualifier-expr><member-set>[<and><member-set>...]

<and> = and

Qualifier

<qualifier-expr> = <qualifier><and>

<qualifier> = All | Aggregates | Details

<and> = and

All

The All qualifier specifies that the LHS range includes all, i.e., both aggregate and detail level, cells within the specified member set(s).

Aggregates

The Aggregates qualifier specifies that the LHS range includes only aggregate level cells within the specified member set(s).

Details

The Details qualifier specifies that LHS range includes only detail level cells within the specified member set(s).

Member Set

A <member-set> specifies a set of members taking the general form:

<member-set> = <open-brace><member-expr> | <member-set-expr>[,<member-expr> | <member-set-expr>...]<close-brace>

Where,

<open-brace> = {

<close-brace> = }

A <member-expr> is an unambiguous reference to a dimension member within a cube.

A <member-expr> can be one of the following:

1. Member Name

a) If the member name is unambiguous, that is, that the member name is unique within all the dimensions within the cube, then the <member-expr> is simply the: MemberName

b) If the member name is ambiguous, that is, that two or more dimensions within the cube contain a member of the same name, then the member name must be qualified by specifying to which dimension the member belongs.

The <member-expr> then becomes: <dimension-expr>.MemberName

Where the <dimension-expr> is the name of the dimension.

2. Member Alias

a) If the member alias is unambiguous, that is, that the member alias is unique within all the dimensions within the cube, then the <member-expr> is simply theMemberAlias.

b) If the member alias is ambiguous, that is that two or more dimensions within the cube contain a member alias of the same name, then the member alias must be qualified by specifying to which dimension the member alias belongs.

The <member-expr> then becomes: <dimension-expr>.MemberAlias

Where the <dimension-expr> is the name of the dimension.

Member/Dimension Set Functions

A <member-set-expr> can be one of the following:

1. The Children Function

<member-set-expr> = <dimension-expr>.<member-expr>.Children()

Specifies all the members for a dimension where the <dimension-expr> is the name of the dimension and  <member-expr> is the name of a member.

Note that the Children function must specify both a dimension and a member.  If no dimension is specified, a parser error will occur.

2. The Subset Function

<member-set-expr> = <dimension-expr>.Subset(<subset-expr>)

Specifies all the members for a dimension subset where the is the name of the dimension and is the name of a subset as defined and saved in the Edit Slicedialog.

The Subset function must specify both a dimension and a Subset. If either no dimension or no subset is specified, a parser error will occur.

if your <member-expr>, <dimension-expr> or <subset-expr> contains non-alphanumeric characters, the name must be quoted using single quotes.

Examples:

1. Since the following <member-expr>'s are unique within the dimensions in a cube, the following LHS Ranges are identical:

All and {Accounts.Units} and {Months.June, Months.November}

All and {Units} and {June, November}

All and {Units, June, November}

2. Since the <member-expr> is unambiguous and contains non-alphanumeric characters, the following are valid:

Details and { 'Margin %' }

Aggregates and {Accounts.'Gross Margin','Margin %'}

3. Since the <dimension-expr> contains non-alphanumeric characters, the following is valid:

All and {'Actual vs. Budget'.Actual}

All and {'Actual vs. Budget'.Variance.Children()}

4. Since the <member-expr> is an unambiguous member alias, the following are valid:

Details and {Regions.ME,CA}

Details and {'OR'}

Details and {Regions.ME,CA} and {'OR'} and {Accounts.Acct1}

Note: the alias for Oregon is OR, which also happens to be a reserved word, and is therefore quoted.

5. When using the Children function, be certain to specify and dimension name and a member name.

All and {Regions.NorthWest.Children()}

All and {Months.'All'.Children()}

  1. When using the Subset function, be certain to specify dimension name and subset name as defined and saved in the Edit Slice. These examples assume that saved subsets exist for the dimension.

All and {Regions.Subset'West Coast States')}

Details and {Months.Subset(Vacation)}

Right-Hand Side (RHS) of Formulas

The body of a rule, or its Right Hand Side (RHS), defines an expression that can be made up from Cube References, standard mathematical operators such as *, /, and +, and built-in functions.

When the calculation engine applies a formula, it is evaluated in the context of a single point within the range of cells defined by the left-hand side (LHS) of the formula.

Formula Functions and Operators

Most of the functions in the proCube formula language are equivalent to a corresponding function in Excel.

IF(…)

The RHS may include conditional flow control structures of the form

IF(condition, result-true, result-false).

[ ]

The [ ] function returns the value of the current cell. Cells that are governed by a [] remain ‘writeable’.

For example:

Details and {Units, Oregon} = [ ] * 0.01;

The above formula considers all the cells within the regions specified by the LHS (i.e., Details and {Units, Oregon}), retrieves the value for each cell, and multiplies each individual value by 0.01.

Details and {Units} = IF(CURRENTINDEX(Regions)=6,999, []);

The above formula considers all the cells within the regions specified by the LHS (i.e., Details and {Units}), and if the current member index for the Regions dimension is equal to 6 (or in the Sample database, Maine) then it sets Maine to 999. Otherwise, all other cells retain their current value.

Operators:

<  Less than

>  Greater than

<=  Less than or equal to

>=  Greater than or equal to

==  Is equal to (Equality)

<>  Is not equal to (Inequality)

&   String concatenation

&&  String concatenation with space

Math

ABS  

Returns the absolute value of a number. The absolute value of a number is the number without its sign.

Syntax: ABS (number)

Number is the real number of which you want the absolute value.

 

INT

Rounds a number down to the nearest integer.

Syntax: INT(number)

Number is the real number you want to round down to an integer.

 

MAX   

Returns the largest value in a set of values.

Syntax: MAX(number1,number2,...)

Number1, number2, … are 1 to 30 numbers for which you want to find the maximum value.

 

MIN   

Returns the smallest number in a set of values.

Syntax: MIN(number1,number2,...)

Number1, number2, ...  are 1 to 30 numbers for which you want to find the minimum value.

 

MOD   

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Syntax: MOD(number,divisor)

Number is the number for which you want to find the remainder.

Divisor is the number by which you want to divide number.

 

NOT   

Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

Syntax: NOT(logical)

Logical is a value or expression that can be evaluated to TRUE or FALSE.

 

RAND   

Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated.

Syntax: RAND( )

 

ROUND   

Rounds a number to a specified number of digits.

Syntax: ROUND(number,num_digits)

Number is the number you want to round.

Num_digits   specifies the number of digits to which you want to round number.

 

SIGN   

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

Syntax: SIGN(number)

Number is any real number.

Support

AGGREGATESUM (dim, member)

Calculates an aggregate value for the specified member in the specified dimension. The hierarchy for the specified dimension defines the aggregation.  Weights are appropriately applied.

Transcendental

ACOS  

Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi.

Syntax: ACOS(number)          

Number is the cosine of the angle you want and must be from -1 to 1.

 

ASIN   

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2.

Syntax: ASIN(number)

Number is the sine of the angle you want and must be from -1 to 1.

 

ATAN   

Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2.

Syntax: ATAN (number)

Number is the tangent of the angle you want.

 

COS   

Returns the cosine of the given angle.

Syntax: COS(number)

Number is the angle in radians for which you want the cosine.

 

EXP

Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.

Syntax: EXP(number)

Number is the exponent applied to the base e.

 

LN   

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

Syntax: LN(number)

Number is the positive real number for which you want the natural logarithm.

 

LOG   

Returns the logarithm of a number to the base you specify.

Syntax: LOG(number,base)

Number is the positive real number for which you want the logarithm.

Base is the base of the logarithm. If base is omitted, it is assumed to be 10.

 

SIN   

Returns the sine of the given angle.

Syntax: SIN(number)

Number is the angle in radians for which you want the sine.

 

SQRT   

Returns a positive square root.

Syntax: SQRT(number)

Number is the number for which you want the square root.

 

TAN   

Returns the tangent of the given angle.

Syntax: TAN(number)

Number is the angle in radians for which you want the tangent.

Date/Time

The Date / Time functions that take a serial_number require a serial number or a function that represents a serial number.  For example, the serial number 37782 represents June 10, 2003.  All serial_number arguments must be numbers.

 

DATE   

Returns the sequential serial number that represents a particular date.

Syntax:  DATE(year,month,day)

Year: Can be one to four digits.

Month: A number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,14,2) returns the serial number representing February 2, 2009.

Day: A number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008,1,35) returns the serial number representing February 4, 2008.

 

DAY   

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax: DAY(serial_number)

Serial_number   is the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

 

HOUR   

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Syntax:  HOUR(serial_number)

Serial_number   is the time that contains the hour you want to find.

 

MINUTE   

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

Syntax: MINUTE(serial_number)

Serial_number   is the time that contains the minute you want to find.

 

MONTH   

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Syntax: MONTH(serial_number)

Serial_number is the date of the month you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

 

NOW   

Returns the serial number of the current date and time.

Syntax: NOW( )

 

SECOND   

Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

Syntax: SECOND(serial_number)

Serial_number is the time that contains the seconds you want to find.

 

TIME   

Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax: TIME(hour,minute,second)

Hour   is a number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.

Minute   is a number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.

Second  is a number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM

 

TODAY   

Returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.

Syntax: TODAY( )

 

WEEKDAY   

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax: WEEKDAY(serial_number,return_type)

Serial_number  is a sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

Return_type   is a number that determines the type of return value.

 

YEAR   

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.

Syntax: YEAR(serial_number)

Serial_number is the date of the year you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

 

TIMESTRING   

Returns a text string for the date and time specified by serial number.

Syntax: TIMESTRING(serial number, format_string)

Serial number  is the date and time you want to create a text string for. 

Format_String is used control what the resulting string will look like. 

The format_string argument consists of one or more codes which are preceded by a percent sign (%). Characters that do not begin with % are left unchanged in the output string. The formatting codes and their results are:

Format Code

Replaced By

%a

Abbreviated weekday name

%A

Full weekday name

%b

Abbreviated month name

%B

Full month name

%c

Date and time representation appropriate for locale

%d

Day of month as decimal number (01 – 31)

%H

Hour in 24-hour format (00 – 23)

%I

Hour in 12-hour format (01 – 12)

%j

Day of year as decimal number (001 – 366)

%m

Month as decimal number (01 – 12)

%M

Minute as decimal number (00 – 59)

%p

Current locale's A.M./P.M. indicator for 12-hour clock

%S

Second as decimal number (00 – 59)

%U

Week of year as decimal number, with Sunday as first day of week (00 – 51)

%w

Weekday as decimal number (0 – 6; Sunday is 0)

%W

Week of year as decimal number, with Monday as first day of week (00 – 51)

%x

Date representation for current locale

%X

Time representation for current locale

%y

Year without century, as decimal number (00 – 99)

%Y

Year with century, as decimal number

%z, %Z

Time-zone name or abbreviation; no characters if time zone is unknown

%%

Percent sign

For example, if the date is 6/10/03 and you enter LHS=TIMESTRING(Now(), “%A, %B %d, %Y”); The result will be “Tuesday, June 10, 2003”

String

CHAR  

Returns the character specified by a number.

Syntax: CHAR(number)

Number is a number between 1 and 255 specifying which character you want.

The character is from the character set used by your computer.

 

CODE   

Returns a numeric code for the first character in a text string.

Syntax: CODE(text)

Text is the text for which you want the code of the first character.

 

EXACT   

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.

Syntax: EXACT(text1,text2)

Text1   is the first text string.

Text2   is the second text string.

 

FIND   

Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.

Syntax: FIND(find_text,within_text,start_num)

Find_text   is the text you want to find.

Within_text   is the text containing the text you want to find.

Start_num   specifies the character at which to start the search.

The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

 

LEFT   

Returns the first character or characters in a text string, based on the number of characters you specify.

Syntax: LEFT(text,num_chars)

Text   is the text string that contains the characters you want to extract.

Num_chars   specifies the number of characters you want LEFT to extract.

 

LEN   

Returns the number of characters in a text string.

Syntax: LEN(text)

Text  is the text whose length you want to find. Spaces count as characters.

 

LOWER   

Converts all uppercase letters in a text string to lowercase.

Syntax: LOWER(text)

Text is the text you want to convert to lowercase. LOWER does not change characters in text that are not letters.

 

MID   

Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntax: MID(text,start_num,num_chars)

Text is the text string containing the characters you want to extract.

Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

Num_chars specifies the number of characters you want MID to return from text.

 

PROPER   

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Syntax: PROPER(text)

Text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

 

REPLACE   

Replaces part of a text string, based on the number of characters you specify, with a different text string.

Syntax: REPLACE(old_text,start_num,num_chars,new_text)

Old_text   is text in which you want to replace some characters.

Start_num   is the position of the character in old_text that you want to replace with new_text.

Num_chars   is the number of characters in old_text that you want to replace with new_text.

New_text   is the text that will replace characters in old_text.

 

REPT   

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax: REPT(text,number_times)

Text is the text you want to repeat.

Number_times is a positive number specifying the number of times to repeat text.

 

RIGHT   

Returns the last character or characters in a text string, based on the number of characters you specify.

Syntax: RIGHT(text,num_chars)

Text is the text string containing the characters you want to extract.

Num_chars specifies the number of characters you want RIGHT to extract.

 

SCAN   

Scan one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.

Syntax: SCAN(find_text,within_text,start_num)

Find_text   is the text you want to scan.

Within_text   is the text containing the text you want to scan.

Start_num   specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

 

SUBSTITUTE   

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Syntax: SUBSTITUTE(text,old_text,new_text,start_num)

Text  is the text or the reference to a cell containing text for which you want to substitute characters.

Old_text   is the text you want to replace.

New_text   is the text you want to replace old_text with.

Start_num   specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

 

TRIM   

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Syntax: TRIM(text)

Text is the text from which you want spaces removed.

 

UPPER   

Converts text to uppercase.

Syntax:UPPER(text)

Text is the text you want converted to uppercase. Text can be a reference or text string.

Logical

AND  

Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

Syntax: AND(logical1,logical2, ...)

Logical1, logical2, ...   are 1 to n conditions you want to test that can be either TRUE or FALSE.

 

IF   

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.  Use IF to conduct conditional tests on values and formulas.

Syntax: IF(logical_test,value_if_true,value_if_false)

Logical_test   is any value or expression that can be evaluated to TRUE or FALSE. For example, ["Months.January"]=100 is a logical expression; if the value in ["Months.January"] is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true   is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false   is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

 

OR   

Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

Syntax: OR(logical1,logical2,...)

Logical1,logical2,...   are 1 to n conditions you want to test that can be either TRUE or FALSE.

 

NOT   

Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

Syntax: NOT(logical)

Logical is a value or expression that can be evaluated to TRUE or FALSE.

Financial

FV  

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax: FV(rate,nper,pmt,pv,type)

(For a more complete description of the arguments in FV and for more information on annuity functions, see PV.)

Rate   is the interest rate per period.

Nper   is the total number of payment periods in an annuity.

Pmt   is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv   is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type   is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

 

IPMT   

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions, see PV.

Syntax: IPMT(rate,per,nper,pv,fv,type)

Rate is the interest rate per period.

Per is the period for which you want to find the interest and must be in the range 1 to nper.

Nper is the total number of payment periods in an annuity.

Pv  is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv  is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type  is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

 

PMT   

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax: PMT(rate,nper,pv,fv,type)

(For a more complete description of the arguments in PMT, see the PV function.)

Rate   is the interest rate for the loan.

Nper   is the total number of payments for the loan.

Pv   is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv   is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type   is the number 0 (zero) or 1 and indicates when payments are due.

 

PPMT   

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax: PPMT(rate,per,nper,pv,fv,type)

(For a more complete description of the arguments in PPMT, see PV.)

Rate   is the interest rate per period.

Per   specifies the period and must be in the range 1 to nper.

Nper   is the total number of payment periods in an annuity.

Pv   is the present value — the total amount that a series of future payments is worth now.

Fv   is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type   is the number 0 or 1 and indicates when payments are due.

 

PV   

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

Syntax: PV(rate,nper,pmt,fv,type)

Rate   is the interest rate per period. For example, if you obtain an automobile loan at a 10 percent annual interest rate and make monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as the rate.

Nper   is the total number of payment periods in an annuity. For example, if you get a four-year car loan and make monthly payments, your loan has 4*12 (or 48) periods. You would enter 48 into the formula for nper.

Pmt   is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. For example, the monthly payments on a $10,000, four-year car loan at 12 percent are $263.33. You would enter -263.33 into the formula as the pmt. If pmt is omitted, you must include the fv argument.

Fv   is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). For example, if you want to save $50,000 to pay for a special project in 18 years, then $50,000 is the future value. You could then make a conservative guess at an interest rate and determine how much you must save each month. If fv is omitted, you must include the pmt argument.

Type   is the number 0 or 1 and indicates when payments are due.

Metadata Functions

ALIAS  (dimension, group, index)

Returns the index’th alias of the specified Alias Group of the specified dimension.

ALIASTOMEMBER  (dimension, group, member)

Returns the alias for the specified member of the specified dimension Alias Group.

CHILD (dimension, member, index)

Returns the name of the index’th child of member in dimension.

CHILDCOUNT (dimension, member)

Returns the number of children of member in dimension.

CUBEDIM  (cube optional, index)

Returns the index’th dimension of the specified cube. If a cube is not specified, then the function assumes the current cube.

CUBEDIMCOUNT  (cube optional)

Returns the count of dimensions of the specified cube. If a cube is not specified, then the function assumes the current cube.

CURRENT (dimension)

Returns the name of the current member in dimension.

CURRENTINDEX (dimension)

Returns the index of the current member in dimension

ISCHILDOF (dimension, parent, child)

Returns true if child is a child of parent in dimension and false otherwise.

ISMISSING (value)

Returns true if value is missing and false otherwise.

LEVELCOUNT (dimension)

Returns the total number of levels in a dimension

LEVEL (dimension, member)

Returns the level of a member. Root level members have level 0 and the level increases with each generation of children.

MEMBER ( dimension, index )

Returns the name of the index’th member in the dimension. Members are numbered 1...n.

MEMBERINDEX ( dimension, member )

Returns the index of the member named member. Member may be an alias name. Members are numbered 1...n.

MEMBERCOUNT (dimension)

Returns the total number of members in the dimension.

MISSING ()

Returns the missing value.

PARENT (dimension, member, index)

Returns the name of the index’th parent of member in dimension.

PARENTCOUNT (dimension, member)

Returns the number of parents of member in dimension.

PROPERTY  (dimension, member, group)

Returns the property for the specified member of the specified dimension Property Group.

ROOTCOUNT  (dimension)

Returns the count of root members in the hierarchy of the specified dimension.

ROOT  (dimension, index)

Returns the index’th root of the specified dimension hierarchy.

WEIGHT  (dimension, parent, child)

Returns the weight of the specified child of the specified parent.

Text-to-Number Conversions

To convert a character string to a double-precision floating-point value, use the following function: 

TextToNumber (text)

This new function allows you to input a sequence of up to 100 characters that can be interpreted as a numerical value of a specified type. The function returns the number value produced by interpreting the input characters as a number.

Examples

TextToNumber("7.8912654773d210") converts to 7.891265e+210

TextToNumber("  -9885 pigs") converts to -9885

TextToNumber("98854 dollars" ) converts to 98854

TextToNumber(123) converts to !!UNKNOWN!!

Note that:

  • The return value is 0.0 if the input cannot be converted to a numeric value.

  • The return value is !!UNKNOWN!! in the event of overflow or an incorrect input type.

Strings

Constant string values in the RHS of a formula must be enclosed in double-quotes.

Examples

{ 'Margin %' } = [ 'Gross Margin' ] / [Sales] * 100;

Details and {Sales} = [Price] * [Units] ;

Aggregates and {Price} =  [Sales ] / [Units] ;

Details and {Regions.Children()} = 'Numeric Cube'.[Accounts.Units] * [January];

Details and {Actual} = Missing();

Details and {'Lastname Strings'} = "Last name:" && Right([Staff.'Last Name'],6);

Extended Formula Grammar

// Rules.ycc

//

 

%macro

{exp}                                           '(([eE]\-?[0-9]+)?)';

 

%expression Main

'\"'                                            %ignore, %push DQuoteString;

''''                                            %ignore, %push SQuoteString;

'\"\"'                                          emptyString, 'emptyQuotedString';

'/\*'                                           %ignore, %push MLCom;

'//'                                            %ignore, %push SLCom;

'[ \n\t\r]+'                                    %ignore;

'\+'                                            Plus, '+';

'\-'                                            Minus, '-';

'\/'                                            Div, '/';

'\*'                                            Mult, '*';

'%'                                             Percent, '%';

'\*\*'                                          Pow, '**';

'\('                                            OParen, '(';

'\)'                                            CParen, ')';

'\-?[0-9]+'                                     Dec, 'dec';

'='                                             Equal, '=';

':'                                             Colon, ':';

'\.'                                            Period, 'period';

'\['                                            leftBracket, '[';

'\]'                                            rightBracket, ']';

'\{'                                            leftBrace, '{';

'\}'                                            rightBrace, '}';

'[a-zA-Z][a-zA-Z0-9]*'                                 MetaName, 'MetaName';

'[aA][gG][gG][rR][eE][gG][aA][tT][eE][sS]'             aggregateSpecifier, 'Aggregates';

'[dD][eE][tT][aA][iI][lL][sS]'                  detailSpecifier, 'Details';

','                                             Comma, ',';

';'                                             SemiColon, ';';

'\-?[0-9]+\.[0-9]*{exp}'                        Float0, 'FloatZero';

'\-?[0-9]*\.[0-9]+{exp}'                        Float1, 'FloatOne';

'\-?[0-9]+[eE]\-?[0-9]+'                        Float2, 'FloatTwo';

'[iI][fF]'                                      If, 'if';

'<'                                             LT, '<';

'>'                                             GT, '>';

'<='                                            LTE, '<=';

'>='                                            GTE, '>=';

'=='                                            EQ, '==';

'<>'                                            NE, '<>';

'&'                                             StrConcat, '&';

'&&'                                            StrConcatSpace, '&&';

'[oO][rR]'                                      Or, 'or';

'[aA][nN][dD]'                                         And, 'and';

'[tT][rR][uU][eE]'                              True, 'true';

'[fF][aA][lL][sS][eE]'                                 False, 'false';

'[cC][oO][nN][tT][iI][nN][uU][eE]'              Continue, 'continue';

'[aA][lL][lL]'                                         All, 'All';

'[cC][hH][iI][lL][dD][rR][eE][nN]'              Children, 'Children';

 

//------------------------------------------------------------------------

// Multi-line comments (C Style)

//------------------------------------------------------------------------

%expression MLCom

'.'                                        %ignore;

'\n'                                       %ignore;

'\*/'                                      %ignore, %pop;

 

//------------------------------------------------------------------------

// Single line comments (C++ style)

//------------------------------------------------------------------------

%expression SLCom

'.'                                        %ignore;

'\n'                                       %ignore, %pop;

 

//---------------------------------------------------------------------------

//   Double quote string handler (For Strings)

//---------------------------------------------------------------------------

%expression DQuoteString

'[^\n"]+'                                  String, 'string', %goto DQuoteStringEnd;

'\"'                                       String, %pop;

 

//---------------------------------------------------------------------------

//   Double quote string end handler  (For Strings)

//---------------------------------------------------------------------------

%expression DQuoteStringEnd

'\"'                                       %ignore, %pop;

 

//---------------------------------------------------------------------------

//   Single quote string handler  (For Symbols)

//---------------------------------------------------------------------------

%expression SQuoteString

'[^\n'']+'                                  Symbol, 'symbol', %goto SQuoteStringEnd;

''''                                       Symbol, %pop;

 

//---------------------------------------------------------------------------

//   Single quote string end handler  (For Symbols)

//---------------------------------------------------------------------------

%expression SQuoteStringEnd

''''                                       %ignore, %pop;

 

//------------------------------------------------------------------------

// Precedence table

//------------------------------------------------------------------------

%prec

1, '<',   %nonassoc;

1, '>',   %nonassoc;

1, '<=',  %nonassoc;

1, '>=',  %nonassoc;

1, '=',   %nonassoc;

1, '<>',  %nonassoc;

1, '&',   %nonassoc;

1, '&&',  %nonassoc;

2, '+',   %left;

2, '-',   %left;

3, '*',   %left;

3, '/',   %left;

4, '**',  %right;

 

//------------------------------------------------------------------------

// Grammar

//------------------------------------------------------------------------

%production start

 

Start                      start                -> ruleList;

StartNull                  start                -> ;

 

ruleListOne                ruleList             -> ruleStatement;

ruleListMulti              ruleList             -> ruleList ruleStatement;

 

rule                        ruleStatement        -> LHS '=' RHS ';';

 

lhs1                       LHS                  -> LHS_expr;

lhs2                       LHS                  -> LHS 'and' LHS_expr;

 

ExprLHSNull                LHS_expr             -> '{' '}';

ExprLHSList                LHS_expr             -> '{' memberList '}';

ExprLHSExpr                LHS_expr             -> member_set_expr;

ExprLHSAgg                 LHS_expr             -> 'Aggregates';

ExprLHSDet                 LHS_expr             -> 'Details';

ExprLHSAll                 LHS_expr             -> 'All';

 

MemberLOne                 memberList           -> member_set_expr;

MemberL                           memberList           -> memberList ',' member_set_expr;

 

MemberSimple               member_expr          -> aSymbol;

MemberQualif               member_expr          -> aSymbol 'period' aSymbol;

 

MemberFunc1                member_set_expr      -> member_expr;

MemberFunc2                member_set_expr      -> member_expr 'period' member_func;

 

MemFunc1                   member_func          -> 'Children' '(' ')';

 

ExprRHS                           RHS                  -> value_expr;

 

ExprIF        value_expr    -> 'if' '(' value_expr ',' value_expr ',' value_expr ')';

 

StrSimple            aString                           -> 'MetaName';

StrQuoted            aString                           -> 'string';

StrQuotedNull        aString                           -> 'emptyQuotedString';

 

SymSimple            aSymbol                           -> 'MetaName';

SymQuoted            aSymbol                           -> 'symbol';

 

ExprContinue         value_expr                 -> 'continue';

ExprString           value_expr                 -> aString;

ExprNumber           value_expr                 -> number;

ExprBoolean          value_expr                 -> boolean;

ExprNested           value_expr                 -> '(' value_expr ')';

ExprPlus             value_expr                 -> value_expr '+' value_expr;

ExprMinus            value_expr                 -> value_expr '-' value_expr;

ExprMultiply         value_expr                 -> value_expr '*' value_expr;

ExprDivide           value_expr                 -> value_expr '/' value_expr;

ExprGT               value_expr                 -> value_expr '>' value_expr;

ExprLT               value_expr                 -> value_expr '<' value_expr;

ExprGTE              value_expr                 -> value_expr '>=' value_expr;

ExprLTE              value_expr                 -> value_expr '<=' value_expr;

ExprEQ               value_expr                 -> value_expr '=' value_expr;

ExprNE               value_expr                 -> value_expr '<>' value_expr;

ExprConcat1          value_expr                 -> value_expr '&' value_expr;

ExprConcat2          value_expr                 -> value_expr '&&' value_expr;   

ExprOR               value_expr                 -> 'or' '(' exprList1 ')';

ExprAND                    value_expr                 -> 'and' '(' exprList1 ')';

ExprFunction         value_expr                 -> 'MetaName' '(' exprList2 ')';

 

// this expression list must have at least on item in it. used for AND/OR functions

ExprOne1                   exprList1                  -> value_expr;

ExprList1                  exprList1                  -> exprList1 ',' value_expr;

 

// this expression list may contain zero or more items. used for function call parameters

ExprNull2                  exprList2                  -> ;

ExprOne2                   exprList2                  -> value_expr;

ExprList2                  exprList2                  -> exprList2 ',' value_expr;

 

CubeMemNull                cubeMemList                -> ;

CubeMemOne                 cubeMemList                -> member_expr;

CubeMemLL                  cubeMemList                -> cubeMemList ',' member_expr;

 

ExprCubeRef                                            value_expr                                                -> aSymbol 'period' '[' cubeMemList ']';

ExprCubeRef2               value_expr                 -> '[' cubeMemList ']';

 

NumberDec                  number                     -> 'dec';

NumberFloat0               number                     -> 'FloatZero';

NumberFloat1               number                     -> 'FloatOne';

NumberFloat2               number                      -> 'FloatTwo';

 

NumPercent1                number                     -> 'dec' '%';

NumPercent2                number                     -> 'FloatZero' '%';

NumPercent3                number                     -> 'FloatOne' '%';

NumPercent4                number                     -> 'FloatTwo' '%';

 

BooleanTrue                boolean                           -> 'true';

BooleanFalse               boolean                           -> 'false';

 

Dependencies Grammar Definition

%macro

{exp}                                                      '(([eE]\-?[0-9]+)?)';

 

%expression Main

'\"'                                                       %ignore, %push DQuoteString;

''''                                                                     %ignore, %push SQuoteString;

'\"\"'                                                    emptyString, 'emptyQuotedString';

'/\*'                                                     %ignore, %push MLCom;

'//'                                                       %ignore, %push SLCom;

'[ \n\t\r]+'                                                       %ignore;

'>>'                                                      DependOp 'DependOp';

'\.'                                                                    Period, 'period';

'\['                                                        leftBracket, '[';

'\]'                                                        rightBracket, ']';

'\{'                                                       leftBrace, '{';

'\}'                                                       rightBrace, '}';

'\('                                                        OParen, '(';

'\)'                                                        CParen, ')';

'[a-zA-Z][a-zA-Z0-9]*'                           MetaName, 'MetaName';

'[aA][gG][gG][rR][eE][gG][aA][tT][eE][sS]'          aggregateSpecifier, 'Aggregates';

'[dD][eE][tT][aA][iI][lL][sS]'                    detailSpecifier, 'Details';

','                                                                      Comma, ',';

';'                                                                      SemiColon, ';';

'[aA][nN][dD]'                                       And, 'and';

'[aA][lL][lL]'                                           All, 'All';

'[cC][hH][iI][lL][dD][rR][eE][nN]'                       Children, 'Children';

'[sS][uU][bB][sS][eE][tT]'                                    Subset, 'Subset';

 

//------------------------------------------------------------------------

// Multi-line comments (C Style)

//------------------------------------------------------------------------

%expression MLCom

'.'                                        %ignore;

'\n'                                       %ignore;

'\*/'                                      %ignore, %pop;

 

//------------------------------------------------------------------------

// Single line comments (C++ style)

//------------------------------------------------------------------------

%expression SLCom

'.'                                        %ignore;

'\n'                                       %ignore, %pop;

 

//---------------------------------------------------------------------------

//   Double quote string handler (For Strings)

//---------------------------------------------------------------------------

%expression DQuoteString

'[^\n"]+'                                  String, 'string', %goto DQuoteStringEnd;

'\"'                                       String, %pop;

 

//---------------------------------------------------------------------------

//   Double quote string end handler  (For Strings)

//---------------------------------------------------------------------------

%expression DQuoteStringEnd

'\"'                                       %ignore, %pop;

 

//---------------------------------------------------------------------------

//   Single quote string handler  (For Symbols)

//---------------------------------------------------------------------------

%expression SQuoteString

'[^\n'']+'                                  Symbol, 'symbol', %goto SQuoteStringEnd;

''''                                       Symbol, %pop;

 

//---------------------------------------------------------------------------

//   Single quote string end handler  (For Symbols)

//---------------------------------------------------------------------------

%expression SQuoteStringEnd

''''                                       %ignore, %pop;

 

 

 

//------------------------------------------------------------------------

// Precedence table

//------------------------------------------------------------------------

%prec

1, 'DependOp',   %nonassoc;

2, 'and', %nonassoc;

3, 'period', %left;

 

//------------------------------------------------------------------------

// Grammar

//------------------------------------------------------------------------

%production start

 

Start                              start                              -> ruleList;

StartNull                       start                              -> ;

 

RuleListOne                  ruleList                         -> ruleStatement;

RuleListMulti                 ruleList                         -> ruleList ruleStatement;

 

Rule                              ruleStatement     -> precedent 'DependOp' dependentList ';';

 

DependentListOne        dependentList               -> LHS_expr;

DependentListMulti       dependentList               -> dependentList ',' LHS_expr;

 

//DependentSimple      dependent                     -> LHS_expr;

LHSCompound                        LHS_expr                     -> LHS_expr 'and' LHS_expr;

 

ExprLHSNull                LHS_expr                     -> '{' '}';

ExprLHSList                LHS_expr                     -> '{' memberList '}';

ExprLHSExpr              LHS_expr                     -> member_set_expr;

ExprLHSAgg                LHS_expr                     -> 'Aggregates';

ExprLHSDet                LHS_expr                     -> 'Details';

ExprLHSAll                  LHS_expr                     -> 'All';

 

MemberLOne               memberList                  -> member_set_expr;

MemberL                     memberList                  -> memberList ',' member_set_expr;

 

MemberSimple              member_expr               -> aSymbol;

MemberQualif               member_expr               -> aSymbol 'period' aSymbol;

MemberString1             member_expr               -> 'string';

MemberStringString       member_expr               -> 'string' 'period' 'string';

MemberStringSymbol    member_expr               -> 'string' 'period' aSymbol;

MemberSymbolString    member_expr               -> aSymbol 'period' 'string';

 

SymSimple                    aSymbol                                    -> 'MetaName';

SymQuoted                   aSymbol                                    -> 'symbol';

 

MemberFunc1               member_set_expr                     -> member_expr;

MemberFunc2               member_set_expr                                 -> member_expr 'period' member_func;

 

DimSubset1                  member_set_expr                     -> aSymbol 'period' 'Subset' '(' aSymbol ')';

 

MemFunc1                   member_func               -> 'Children' '(' ')';

 

PrecedentSimple                        precedent                      -> cellSpec;

PrecedentCrossCube     precedent                      -> cubeName 'period' cellSpec;

 

CubeNameString                       cubeName                    -> 'string';

CubeNameSymbol        cubeName                    -> aSymbol;

 

CellSpec                        cellSpec                         -> '[' cellMemberList ']';

 

CellMemberListSimple   cellMemberList              -> member_expr;

CellMemberListMulti     cellMemberList  -> cellMemberList ',' member_expr

Metadata Import Commands

Metadata Import Script Format

Each line of a proCube Metadata import script specifies a command followed by delimited arguments. Each line is separated by either ASCII “\n\r” or “\n”. Import and Export scripts can generally be edited in the Windows Notepad or Wordpad.

The following commands may be used if you wish to create a Metadata import file, in Microsoft Notepad or Wordpad, for example (provided the file is created as a .txt file).

COMMENT

;

The remainder of the line is ignored

ADD DIMENSION

D+

Name

Adds a dimension with the given name to the database. Generates a fatal error if the dimension already exists.

MODIFY DIMENSION

D=

Name

New Name

Changes the name of an existing dimension in the database. Generates a fatal error if the dimension does not exist, or if the new name is already in use.

DELETE DIMENSION

D-

Name

Deletes the named dimension from the database. Generates a warning if the dimension does not exist.

ADD MEMBER

M+

Dimension

Name

Insert After

Adds a member to a dimension. The member is added after the member named in the Insert Before parameter. If the Insert After argument is not specified then the member is added to the end of the dimension. Generates a warning if the dimension does not exist or if any of the member names do not exist in the specified dimension.

MODIFY MEMBER

M=

Dimension

Name

New Name

Insert After

Changes the properties of a member in a dimension. Behavior of the arguments is as in M+ . Generates a warning if the member does not exist.

DELETE MEMBER

M-

Dimension

Name

Deletes a Member from a dimension. Generates a warning if the member does not exist.

ADD ALIAS GROUP

G+

Dimension

Name

Adds an Alias Group to the named dimension. Generates an error if the dimension does not exist.

MODIFY ALIAS GROUP

G=

Dimension

Name

New Name

Changes the name of an Alias Group. Generates an error if the Group does not exist.

DELETE ALIAS GROUP

G-

Dimension

Name

Deletes an Alias Group. Generates a warning if the Group does not exist.

ADD ALIAS

A+

Dimension

Group

Name

Alias

Adds an alias to the named alias Group/Member. Generates an error if the Group or Member does not exist.

MODIFY ALIAS

A=

Dimension

Group

Alias

New Alias

Changes the name of an alias. Generates an error if the alias does not exist.

DELETE ALIAS

A-

Dimension

Group

Alias

Deletes an alias. Generates a warning if the alias does not exist.

ADD PROPERTY GROUP

R+

Dimension

Name

Adds a Property Group to the named dimension. Generates an error if the Dimension does not exist.

MODIFY PROPERTY GROUP

R=

Dimension

Name

New Name

Changes the name of a Property Group. Generates an error if the Group does not exist.

DELETE PROPERTY GROUP

R-

Dimension

Name

Deletes a Property Group. Generates a warning if the Group does not exist.

ADD PROPERTY

P+

Dimension

Group

Name

Property

Adds a property to the named Property Group/Member. Generates an error if the Group or Member does not exist.

MODIFY PROPERTY

P=

Dimension

Group

Alias

New Property

Changes the name of a property alias. Generates an error if the property does not exist.

DELETE PROPERTY

P-

Dimension

Group

Alias

Deletes an alias. Generates a warning if the alias does not exist.

ADD HIERARCHY RELATION

H+

Dimension

Parent

Child

Weight

Insert After

Adds a hierarchical relationship to the named dimension. Adds the member named in the child argument as a child of the member named in the Parent argument. The child is added to the child list before the member named in the Insert After parameter. If the Insert After argument is not specified then the member is added to the end of the child list. Weight specifies the weight for this child.

MODIFY HIERARCHY RELATION

H=

Dimension

Parent

Child

New Weight

New Insert After

Changes weight and order properties of hierarchical relationship in the named dimension. 

DELETE HIERARCHY RELATION

H-

Dimension

Parent

Child

Deletes a hierarchy relation from a dimension. This may cause the deletion of other relations if Child itself has children in the hierarchy. Generates a warning if the relation does not exist.

ADD CUBE

C+

Name

Type

Dim1

Dim2

Adds a Cube with the given name, type and dimensions to the database. Generates a fatal error if the cube already exists. Generates an error if any of the dimensions do not exist.  The type field can be either A for Alphanumeric or N for Numeric.

MODIFY CUBE

C=

Name

New Name

Changes the name of an existing cube in the database. Generates a fatal error if the cube does not exist, or if the new name is already in use.

DELETE CUBE

C-

Name

Deletes the named cube from the database. Generates a warning if the cube does not exist.

MODIFY FORMULA

F=

Cube Name

… Formula Text …

… Formula Text …

F.

Changes the formula for the specified cube. Logs an error if the cube is not found.

Commands

Commands are one or two character case sensitive ASCII symbols.  The first character usually indicates the type of data manipulated by the command, and the second character indicates the operation (Add/Modify/Delete). See the list of Commands at the end of this topic.

Delimiters

The delimiter between each argument is specified in the import dialog and is discussed in the topic, “Importing Metadata.”

Arguments

Each argument is read as an 8-bit ISO/ASCII, case insensitive string. When appropriate, some arguments are converted to numbers. Numbers must be entered with an optional sign, a string of numbers possibly containing a decimal point, and an optional exponent field containing an E or e followed by a possibly signed integer.  Another equivalent description would be: [+|-]###.###[(e|E)[+|-]###].

Error Reporting

There are two levels of error reporting:

Errors - Input script lines that generate errors are printed to the log file, along with a comment line.

Fatal Errors - Input script lines that generate errors are printed to the log file with a comment. A message box is displayed and the input process stops. Fatal errors are used to avoid unintentional modification of data.

Fact data import commands

The Fact Data Import/Export process is more complex than the Metadata Import process. When importing or exporting data, it is often necessary to change the “dimensionality” of data – for example to export data from a 3-dimensional Cube, and then to import that data into a 5-dimensional cube. (An example of this was shown in Section 5). The import script syntax manages this problem with the I+ command, which describes the format of the source data and the Cube Selection dialog, which specifies how a given set of source data maps to a destination cube.

Text-based data import capabilities are provided, in addition to Excel-based import capabilities (also discussed in Section 5) because text-based is faster and much easier to use for Cube-to-Cube data transfers. (It is not designed, however, to be as flexible as the Excel-based data import mechanism.)

users will need to create, by hand, the data file’s header information for data that has not been exported from the proCube Client. (proCube automatically writes out appropriate header information when it exports Fact Data.) The proCube Fact Data import text requires this file header that describes the format of the data included in the file. 

The following lists the commands that may be included in the header.

SCHEMA

Schema

[Slice Name]

The Schema command marks the beginning of the file header. The optional Slice Name parameter is provided for documentation purposes only.

FOLD

Fold

[Dimension Name]

[Dimension Name]

The Fold command specifies any folded dimensions in the source data. These dimensions correspond to column labels across the top of a slice in the Client application.

The Fold command must precede any Column commands that have a type of N, S, or NS, and there may only be one Fold command in a header.

COLUMN

Column

M | N | S | NS

Column commands describe the type of data in each column of the source data. Column commands are order dependent, with each successive Column command referring to successive columns in the source data from left to right. If no Fold command precedes a Column command it is assumed that there are no folded dimensions.

The first argument specifies the type of column:

M - The column contains dimension members. The second argument of the Column command is the name of the dimension of the members.

N -  The column contains numeric data values. The remaining parameters are member names which correspond to the dimensions specified in the preceding Fold command. If the members are not found a fatal error occurs. During import proCube will try to convert each data value to a number. If it fails it issues a warning.

S -  The column contains numeric data values. The remaining parameters are member names which correspond to the dimensions specified in the preceding Fold command. If the members are not found a fatal error occurs. During import proCube will always write the data values as strings.

NS - The column contains numeric or string data values. The remaining parameters are member names which correspond to the dimensions specified in the preceding Fold command. If the members are not found a fatal error occurs. During import proCube will try to convert each data value to a number. If it succeeds it will write the data as a number, otherwise it will write the data as a string.

During import the columns are read left to right potentially modifying the same cell in the destination cube multiple times per source data row. Missing values in source data are ignored.

STARTINLINE

StartInline

The data follows immediately after the StartInline command and uses the same delimiter of the current import script. The data ends when the end of the file is reached, or when the first symbol in a line is “EndInline”.

DATAFILE COMMAND

DataFile

FileName

The data import script also supports file references.  Instead of including the data in the same file, an import script can use a file reference.  This file reference replaces the StartInline to EndInline section.  The file name follows immediately after the DataFile command and uses the same delimiter as the current import script. The data in this secondary file must correspond to the data normally found between the StartInline and EndInline section.

The file name can be either a fully qualified path name or a relative path name based on the initial import file.

Note that if you are using a Schema file that includes a Datafile command to import data from a file reference, both files need to use the same delimiter.

ENDINLINE

EndInline

Used to complete the StartInline section signifying the end of data.

 

Schema        Slice                            

Fold          Regions                          

Column        M             Accounts                  

Column        M             Months              

Column        N             California                

Column        N             Oregon              

Column        N             Vermont                   

Column        N             Maine               

StartInline                             

Account1      January       0.139407897   0.401852201   0.801796257   0.652370235

Account1      February      0.783904524   0.937655287   0.923899373   0.827072172

Account1      March         0.887124288   0.658957207   0.830059668   0.227329357

Account1      April         0.812941406   0.108872333   0.446512497   0.410558823

Account1      May           0.309530879   0.114093218   0.720820254   0.387044119

Account1      June          0.37162136    0.904706988   0.338660513   0.196221734

Account1      July          0.304975232   0.373084709   0.276257888   0.340046675

Account1      August        0.809719543   0.466957533   0.20125901    0.776064829

Account1      September     0.944015121   0.383826136   0.767805447   0.82862114

Account1      October       0.099546865   0.861087302   0.94971556    0.367844024

Account1      November      0.807482664   0.498567923   0.646897538   0.392043745

Account1      December      0.472949158   0.045011316   0.267465083   0.985907521

EndInline

Fact data security ranges

A Fact Data Security Range (FSR) grants User(s) and/or Group(s) access privileges to a region of a Cube. The Define Fact Data Security Ranges dialog allows multiple Fact Data Security Ranges to be entered. Therefore, the complete set of Fact Data security for a Cube is a list of Fact Data Security Ranges.

A set of Fact Data Security Ranges can be named within the dialog.

General form

A Fact Data Security Range (FSR) takes the general form:

<FSR> = <LHS><user-group-privilege><semicolon>

Where,

<user-group-privilege> = <allows><User-group-list><to><access-privilege>

<User-group-list> = <User-name> | <group-name>

<access-privilege> = <read> | <write> | <reserve> | <lock> | <commit>

<allows> = allows

<to> = to

<read> = read

<write> = write

<reserve> = reserve

<lock> = lock

<commit> = lock

See the section EXTENDED FACT DATA SECURITY GRAMMAR for a further view of grammar.

Access privileges

Read - The user has the ability to read data contained in the security range.

Write - The user has the ability to write the data in the security range.

Reserve - The user has the ability to temporarily reserve all of the data in the range. This allows the user to change the data in the range, but prevents all other users from changing the data.

Lock - The user has the ability to temporarily lock all of the detail level data in the security range.

Commit - The user has the ability to create an irrevocable lock for the security range.

Examples

Where JaneDoe is a User:

All and {Accounts.Units} and {Months.June, Months.November} allows {JaneDone} to Read;

Where BostonOffice is a Group:

All and {Accounts.Units} and {Months.June, Months.November} allows {BostonOffice} to Write;

Where JaneDoe, JohnDoe are users and BostonOffice is a Group:

All and {Accounts.Units,Accounts.Price} and {Months.June, Months.July, Months.November} allows {JaneDone,JohnDoe, BostonOffice} to Read;

or

Details and {Accounts.Units,Accounts.Price} and {Months.June, Months.July, Months.November} allows {JaneDone} to Read ;

Details and {Accounts.Units,Accounts.Price} and {Months.June, Months.July, Months.November} allows {JohnDoe, BostonOffice} to Read;

Extended fact data security grammar

 

// FactSecurity.ycc

//

 

%expression Main

 

''''                                            %ignore, %push SQuoteString;

'[ \n\t\r]+'                                    %ignore;

 

'[a-zA-Z][a-zA-Z0-9]*'                                 MetaName, 'MetaName';

'\.'                                            Period, 'period';

','                                             Comma, ',';

';'                                             SemiColon, ';';

'\{'                                            leftBrace, '{';

'\}'                                            rightBrace, '}';

'\('                                            OParen, '(';

'\)'                                            CParen, ')';

'\['                                            leftBracket, '[';

'\]'                                            rightBracket, ']';

 

'[aA][gG][gG][rR][eE][gG][aA][tT][eE][sS]'             aggregateSpecifier, 'Aggregates';

'[dD][eE][tT][aA][iI][lL][sS]'                  detailSpecifier, 'Details';

'[aA][lL][lL]'                                         All, 'All';

'[aA][nN][dD]'                                         And, 'and';

'[cC][hH][iI][lL][dD][rR][eE][nN]'              Children, 'Children';

 

'[aA][lL][lL][oO][wW][sS]'                      allows, 'allows';

'[tT][oO]'                                      to, 'to';

'[cC][oO][mM][mM][iI][tT]'                      commit, 'commit';

'[lL][oO][cC][kK]'                              lock, 'lock';

'[rR][eE][aA][dD]'                              read, 'read';

'[rR][eE][sS][eE][rR][vV][eE]'                  reserve, 'reserve';

'[wW][rR][iI][tT][eE]'                                 write, 'write';

 

//---------------------------------------------------------------------------

//   Single quote string handler  (For Symbols)

//---------------------------------------------------------------------------

%expression SQuoteString

'[^\n'']+'                               Symbol, 'symbol', %goto SQuoteStringEnd;

''''                                     Symbol, %pop;

 

//---------------------------------------------------------------------------

//   Single quote string end handler  (For Symbols)

//---------------------------------------------------------------------------

%expression SQuoteStringEnd

''''                                                                       %ignore, %pop;

 

//------------------------------------------------------------------------

// Grammar

//------------------------------------------------------------------------

%production start

 

Start                      start                -> securityRule;

StartNull                  start                -> ;

 

securityRule               securityRule         -> LHS 'allows' RHS ';';

 

LHS1                       LHS                  -> LHS_expr;

LHS2                       LHS                  -> LHS 'and' LHS_expr;

 

ExprLHSNull                LHS_expr             -> '{' '}';

ExprLHSList                LHS_expr             -> '{' memberList '}';

ExprLHSExpr                LHS_expr             -> member_set_expr;

ExprLHSAgg                 LHS_expr             -> 'Aggregates';

ExprLHSDet                 LHS_expr             -> 'Details';

ExprLHSAll                 LHS_expr             -> 'All';

 

MemberLOne                 memberList           -> member_set_expr;

MemberL                           memberList           -> memberList ',' member_set_expr;

 

MemberSimple               member_expr          -> aSymbol;

MemberQualif               member_expr          -> aSymbol 'period' aSymbol;

 

SymSimple                  aSymbol                    -> 'MetaName';

SymQuoted                  aSymbol                    -> 'symbol';

 

MemberFunc1                member_set_expr      -> member_expr;

MemberFunc2                member_set_expr      -> member_expr 'period' member_func;

 

MemFunc1                   member_func          -> 'Children' '(' ')';

 

RHS1                       RHS                  -> RHS_expr;

RHS2                       RHS                  -> RHS ',' RHS_expr;

 

ExprRHS                           RHS_expr             -> '{' User_group_list '}' 'to' action;

 

UGList1                           User_group_list      -> aSymbol;

UGList2                           User_group_list      -> User_group_list ',' aSymbol;

 

Action1                           action               -> 'commit';

Action2                           action               -> 'lock';

Action3                           action               -> 'read';

Action4                           action               -> 'reserve';

Action5                           action               -> 'write';

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk