Olap Mdx Functions 536z1h

Tip or Technique

Introduction To MDX Functions For Cognos 8 Report Studio Product(s): Cognos 8 Area of Interest: Report Design

Introduction To MDX Functions For Cognos 8 Report Studio

2

Copyright Your use of this document is subject to the of Use governing the Cognos software products and related services which you have licensed or purchased from Cognos. The information contained in this document is proprietary information of Cognos Incorporated and/or its licensors and is protected under copyright and other applicable laws. You may use the information and methodologies described in this document 'as is' or you may modify them, however Cognos will not be responsible for any deficiencies or errors that result from modifications which you make. Copyright 2006 (c) Cognos Incorporated. All Rights Reserved. You can print selected pages, a section, or the whole book. Cognos grants you a non-exclusive, non-transferable license to use, copy, and reproduce the copyright materials, in printed or electronic format, solely for the purpose of providing internal training on, operating, and maintaining the Cognos software. This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to Best Practices, Product and Technologies.

Proprietary Information


3

Contents 1

INTRODUCTION ............................................................................................ 4

1.1 1.2 1.3 1.4 1.5

PURPOSE .............................................................................................................. 4 APPLICABILITY ....................................................................................................... 4 EXCLUSIONS .......................................................................................................... 4 DOCUMENT CONVENTIONS AND ING TECHNOLOGY ................................................ 4 ING MATERIALS ........................................................................................... 4

2

REPORT STUDIO AND OLAP DATA ................................................................ 4

3

OVERVIEW OF OLAP FOR COGNOS 8 ............................................................ 5

3.1 3.2

OLAP TERMINOLOGY ............................................................................................... 5 OVERVIEW OF REPORT STUDIO MDX FUNCTIONS ............................................................ 7

4 FUNDAMENTAL CONCEPTS FOR WORKING WITH OLAP DATA IN COGNOS 8 REPORT STUDIO....................................................................................................... 8 4.1 4.2 4.2.1 4.2.2 4.2.3 4.2.4

IMPLICIT SETS, TUPLES, AND IN REPORT STUDIO ............................................... 8 EXPLICIT SETS, TUPLES, AND IN REPORT STUDIO ............................................... 9 Tuples .............................................................................................................. 10 .......................................................................................................... 10 Sets ................................................................................................................. 11 The currentMember and tuples........................................................................... 11

5

NAVIGATING HIERARCHIES WITH MDX FUNCTIONS ................................ 13

5.1 5.2 5.3 5.4 5.5 5.5.1

REFERENCING WITH PARENTS, CHILDREN, AND PREVIOUS ...................... 13 DIMENSION ROLLUP AGGREGATIONS .......................................................................... 15 LEVERAGING THE DIMENSION STRUCTURE.................................................................... 15 USING SET FUNCTIONS........................................................................................... 19 MEMBER UNIQUE NAMES (MUNS) ............................................................................. 20 When is [2006/Dec] not [2006/Dec]?.................................................................. 21

6

SYNTAX FOR USING REPORT STUDIO MDX FUNCTIONS ........................... 23

6.1 6.2

PARAMETER TYPES ................................................................................................ 23 OPTIONAL PARAMETERS .......................................................................................... 24

7

WORKING WITH SETS OF .......................................................... 25

7.1 7.2 7.3

USING THE MDX UNION() FUNCTION ......................................................................... 25 USING THE MDX EXCEPT() FUNCTION TO MANIPULATE MEMBER SETS ................................ 28 USING THE MDX ORDER() FUNCTION TO SORT MEMBER SETS .......................................... 29

8

MEMBER SUMMARY FUNCTIONS ................................................................ 31

8.1 8.2

THE MDX TOTAL() AND AGGREGATE() FUNCTIONS ........................................................ 31 THE MDX PERCENTAGE() FUNCTION .......................................................................... 33

9

CONCLUSION .............................................................................................. 34

APPENDIX A............................................................................................................ 35 MDX FUNCTION CATEGORIES .............................................................................................. 35 MEMBER FUNCTION DESCRIPTIONS ........................................................................................ 35 SET FUNCTION DESCRIPTIONS.............................................................................................. 36 LEVEL AND HIERARCHY FUNCTION DESCRIPTIONS ...................................................................... 38 TUPLE FUNCTION DESCRIPTIONS........................................................................................... 38 OTHER FUNCTION DESCRIPTIONS .......................................................................................... 39



4

1 Introduction 1.1

Purpose This document introduces the fundamental concepts behind the OLAP query language, MDX, and how they apply to creating Report Studio reports. The content is not a comprehensive review of all the MDX functions and their interactions.

1.2

Applicability The techniques described apply to Cognos 8 MR2 Report Studio functionality. It is assumed that the reader is already familiar with using Report Studio.

1.3

Exclusions Topics including dimensional modeling, specific cube implementations (i.e. PowerPlay cubes versus MSFT Analysis Services cubes), or writing complete MDX functions by hand will not be covered.

1.4

Document Conventions and ing Technology The examples in this document are built from the example PowerPlay cube, [Great Outdoors Company] that ships with Cognos 8 MR2. References to the [Great Outdoors Company] are abbreviated to [GOC] to improve readability.

1.5

ing Materials It is highly recommended that the reader obtain Cognos 8 MDX-OLAP Functions from the Proven Practice documents for a comprehensive set MDX function descriptions and examples, and as a companion document to the following material.

2 Report Studio and OLAP Data Taking advantage of the sophistication and speed of OLAP cube data when developing reports can be a complex task using MDX, the OLAP query language. Report Studio developers will initially turn to books or the web to learn OLAP technology and MDX. It soon becomes apparent to the Report Studio developer that learning to write MDX queries manually is good background information, but is more than is needed to be effective writing reports on OLAP data sources in Cognos 8 Report Studio. Cognos 8 Report Studio manages the complexities of building MDX queries for the report writer through a drag and drop interface, while still enabling custom MDX functions to be used. Some of the key benefits of this powerful Report Studio capability to the OLAP report developer include: 1. Sophisticated OLAP reports are quick and easy to develop because no time is required to devise and test the base MDX query structure for a report.



5

2. The learning curve for developers is substantially reduced. Report writers only need to understand the concepts behind MDX queries and how to use specific functions without having to become expert in writing full MDX queries. 3. Report Studio provides functionality and layout capabilities that are beyond what the MDX query language can accomplish. Report Studio developers use MDX functions in reports to: 1. Building custom calculations (typically to meet specific report needs) such as creating values not suitable for keeping in a cube or not originally designed into the cube. 2. Dynamically displaying data, changing the behavior of the report, providing interactive data driven functionality to the end , etc., creating calculations not suitable for keeping in the cube or not originally designed into the cube, or for providing interactive capabilities to end s. It is recommended that anyone using Cognos 8 Report Studio eventually learn how to write MDX from scratch. This will give the an intuitive understanding of how to approach certain problems, and an appreciation of the power and sophistication of the unique capabilities of Cognos 8 Report Studio.

3 Overview of OLAP for Cognos 8 This section defines key OLAP a Report Studio will need to be familiar with. These will be referenced throughout the document. 3.1

OLAP Terminology Cube – An OLAP data source (OLAP = OnLine Analytical Processing). Cubes have a different structure than relational sources and can be used to precalculate all the different combinations of values and interrelations amongst data . MDX – MultiDimensional eXpressions, the query language for multidimensional data sources. Dimension – A hierarchy or tree “describing” data, usually with multiple hierarchies and levels. Each hierarchy will consist of at least one level, and commonly start with a root “all” level that represents every member of every level for a hierarchy.



6

Hierarchy / Level – A tree structure in a dimension that has ordered levels containing data which provides structure to the data. A Level is a position in the Hierarchy. An example from the [Great Outdoors Company] PowerPlay cube is the [Time] dimension. The [Time] dimension contains the hierarchies [Years], and [Current Month]. The [Years] hierarchy consists of the [Years], [Quarter], and [Month] levels. Hierarchies and levels define drill paths, and aggregate rules on the measure or dimension will define how member values are “rolled-up” through the dimension. The most common rollup aggregation is SUM (i.e. the [Revenue] value for the [Quarter] level will be the sum of all the [Month] for that [Quarter]).

Level Hierarchy

Dimension Member Folder Level in Hierarchy

in the Hierarchy

Member Description and Caption for the Level

Member – An item in a dimension hierarchy. [Q2 2006] is a member of the [Quarter] level of the [Years] hierarchy Measure – Refers to actual data values, i.e. [Revenue], or [Quantity sold]. Measures are also often referred to as “facts”, and as “Key Figures” in SAP BW. Measures have their own dimension. The value returned for a measure will be determined by the intersection of the other dimensions that are being referenced in a query. If only the [Product line] level member [Golf Equipment] is being used in a query, the [Revenue] value will be the revenue for all [Golf Equipment]. If the product line [Golf Equipment] and the country [Canada] is being used, then the [Revenue] value will be the revenue for all [Golf Equipment] sold in [Canada]. The intersection of the dimension used to define the value of a measure is known as a tuple. Tuple – Refers to an intersection of one or from one or more dimensions to define a specific measure value. The tuple of [Camping Equipment], [2004], [Revenue] is the revenue measure value for camping equipment in 2004. Set – A group of or tuples. In the [Years] hierarchy, the [Q1 2006], [Q2 2006], [Q1 2005], [Q2 2005] taken together is a set of .



7

Slicer – A slicer is similar to the SQL “where” clause that limits the data returned in a query. As an example, a slicer of [2005] will limit any data returned to 2005. There are some considerations for the report writer when using a slicer: 1. Values returned will be only for the slicer condition. In the previous example, the [Revenue] measure value will be in context of the [2005] member for all calculations and values returned. 2. With a slicer for [2005], year over year comparisons will not be valid since the other years have been “sliced” from the returned data set. 3. Slicers can be sets of data. A valid slicer could be the set of the year [2004] and [2005]. 4. Unlike the SQL “where” clause, slicers do not remove from other rows or columns. For example, by slicing on [2004] this will not remove [2005] if the years are displayed on the rows. The result would be that [2005] is still present but does not show any measure values. Properties / Attributes – Extra information attached to . Properties are usually used as pieces of descriptive information that do not have values that roll-up like a measures. Properties are associated with . Using a [Store] dimension as an example, properties could include the [Store Manager] name, or [Store Type], the store’s type. In Report Studio the member properties show up as insertable objects under the same level that contains the themselves. In this example, it would make no sense to have a [Store Type] property on [City] level member, such as [Los Angeles].that contains many store types. 3.2

Member Properties

Overview of Report Studio MDX Functions Among all the OLAP technologies, MDX functions have common names and in most cases the similar input parameters. Typical MDX function syntax is a mix of curly braces that can delimit sets of data, parenthesis to denote value combinations or parameters in a function, operators such as “+” to mean either addition or a union of data, and “dot” notations to reference other capabilities. Rather than a mix of operators and character cues, Cogno8 Report Studio provides a consistent function / parameter syntax for all MDX functions. This makes the functions easier to read and helps avoid mistakes that can easily occur with the standard MDX syntax. A simple comparison of the MDX union statement shows the improvements of standardization, readability, and maintainability of the Cognos 8 MDX syntax:



8

Standard MDX

Cognos MDX

union( [Golf Equipment].Children, [Camping Equipment].Children )

union( children( [Golf Equipment] ), children( [Camping Equipment] ) )

OR { [Golf Equipment].Children, [Camping Equipment].Children }

OR { [Golf Equipment].Children } + {[Camping Equipment].Children }

The above example shows that there is often a correlation between a version of a “standard” MDX syntax and the Cognos 8 syntax. Report Studio also provides extensions and added capabilities to MDX that simplify complex calculations and aggregations. MDX functions can be broken down into five categories: 1. Member functions – used for retrieving specific and their properties. 2. Set functions – working with sets of . 3. Level and hierarchy functions – information on dimension and hierarchies in them. 4. Member summary functions – total, aggregate, percentage values on a set or report. 5. Logic functions – if, contains, is null, etc.

4 Fundamental Concepts for Working With OLAP Data In Cognos 8 Report Studio Report Studio generates the MDX query for the report writer as they drag data items into a list or crosstab. This section introduces key MDX concepts by examining how MDX is utilized by Report Studio through its graphical interface. The concepts introduced here will be used as building blocks to be used later for more complex custom calculations. 4.1

Implicit Sets, Tuples, and In Report Studio Report Studio provides a graphical design interface for writing reports using sets, tuples, and . Using a crosstab as an example, measure values are placed in the measure drop zone of a crosstab, while sets exist on the column and row drop zones. Sets can consist of single (i.e. [Camping Equipment], or a collections of (i.e. ( [Q1 2005], [Q2 2005] ) ).



9

The simplest report that could be written is a list report with just a measure as a data item. From the [Great Outdoors Company] cube, a list with the [Revenue] measure dragged in returns:

$171,576,387.88 is the value of all revenue for the intersection of all dimensions at the default level, which is usually the “All” level. The “All” level means the set of all [Time] dimension , the set of all [Product] dimension , etc., for all dimensions. The resulting value $171,576,387.88 can be defined by a tuple. The concept is that a tuple references a value defined by the intersection of dimensions. If we add context to the list report by dragging the dimension level [Product line], the report returns:

Figure 1

By dragging in the [Product line] level, the report shows the [Revenue] for each member of [Product line]. That is, [Product line] represents the set of [Camping Equipment] through [Personal Accessories].

Figure 2

If another dimension level is added to the report, such as [Year] after [Product line], the report would return the [Revenue] value for each [Product line] member for each [Year] member. 4.2

Explicit Sets, Tuples, and In Report Studio The following examples show how MDX functions can be used to produce a report layout that normally would be done using Report Studio’s drag-anddrop capability. The objective is to: 1. Demonstrate how Report Studio layout that drives what MDX is created to return data for the report.



10

2. Provide a frame of reference on how data interacts in Report Studio reports, and how MDX functions can be used to create custom calculations. 4.2.1

Tuples The above example can be used to show how to use the MDX tuple() function to get a value in the context of multiple dimensions. The tuple() function is used whenever a report writer needs to explicitly retrieve a value. The syntax for tuple() from the Report Studio tip is: tuple ( member { , member } ) Identifies a cell location (intersection) based on the specified , each of which must be from a different dimension. Implicitly includes the current member from all dimensions not otherwise specified in the arguments. The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. The value of this cell can be obtained with the "value" function.

The tuple() function will return the value for the combination of every member included in the function. To illustrate, creating the same list report as in Figure 1 with [Revenue] and [Product line] using the tuple() function, the [Revenue] measure would not be dragged onto the list, but instead would be replaced by a custom [Data Item] calculations with the formula tuple( [Revenue] ). The use of the tuple() function here is for illustration only. When using a measure such as [Revenue], the tuple() function is implied and does not need to be explicitly written. 4.2.2

Dragging the [Product line] level from the [Products] hierarchy into a list or crosstab will reference a set of data consisting of the of the [Product line] level. In this case, Report Studio uses the MDX () function to return all [Product line] when producing the underlying MDX query for the list. The () function will return the set of in the [Product line] level of the products dimension. The definition of the function in Report Studio is: ( hierarchy | level ) This returns the set of in a hierarchy or level. In the case of a hierarchy, the order of the in the result is not guaranteed; if a predictable order is required, an explicit ordering function (such as hierarchize) must be used.

“Returns the set of ” is easy to visualize using Report Studio. Examining the “Insertable Objects” pane source tab (Figure 3), the [Product line] level of the [Products] dimension will show the of the [Product line] level, [Camping Equipment] through [Personal Accessories].



11

Instead of dragging in the [Product line] level to the list, a [Data Item] could be created which contains the function ( [Product line] ) to achieve the same result as in Figure 1. However, as with the tuple function the reference to the level itself will implicitly include a reference to the function and in most cases the use of () within an expression is not necessary. The order of the in the Insertable objects pane is defined by the structure of the cube and will be the default order of the on a report.

Figure 3

4.2.3

Sets Similar to the () function, Report Studio uses the MDX set() function to create1 a set of when specific are dragged onto a report layout. Using a crosstab (to avoid nesting in a list), [Camping Equipment] and [Outdoor Protection] could be placed into the rows of a crosstab and [Revenue] to the measures, giving the result in Figure 4

Figure 4

The MDX set() function could be used in a query data item with the function set([Camping Equipment], [Outdoor Protection]) instead of dragging individual . Sets are the key to driving many reports, including creating custom aggregations over sets of data, or choosing what data to display. 4.2.4

The currentMember and tuples The tuple() function does not have be explicitly referenced in order to identify the current [Product line] member. This relationship is implied by the nesting in the list, crosstab, or chart.

1

The set of data returned is actually done using the union() statement, which will be covered later in this document. Proprietary Information


12

Set of [Product line]

tuple (value) of the current [Product line] member [Revenue]

Figure 5

The tuple() function automatically references the currentMember() function to determine the context for the [Revenue] value based on the current member of [Product line]2. The current member is defined as: currentMember ( hierarchy ) Returns the current member of the hierarchy during an iteration. If the specified hierarchy is not present in the context in which the expression is being evaluated, its default member is assumed.

The key phrase is “member of the hierarchy during an iteration.” In the above example, the MDX query that drives the list iterates through each member of [Product line]. The currentMember() function gets the member currently in the context of the list or crosstab. currentMember() is one of the more extensively used functions when writing reports to get values in the context of the intersection of for conditional formatting, custom calculations, etc. Note that the currentMember() function takes a hierarchy as parameter, not a level3. The currentMember() function will return whatever the current member is regardless of what level in the member is from. In the current list example, the tuple() and currentMember() functions could be used to explicitly reference the [Revenue] value for the current member of the [Product line]. The [Revenue] member dragged into the report could be changed to the function: tuple( currentMember( [GOC].[Products].[Products], [Revenue] ). As the query cycles through the of [Product line], the tuple() function will get the value from the intersection of [Camping Equipment] and [Revenue] first, [Golf Equipment], [Revenue] next, etc., through all the of [Product line] down to [Personal Accessories], [Revenue]. This will give the exact same result as in Figure 1.

2 The completeTuple() will use the default member of a hierarchy rather than the currentMember(). This typically evaluates to the root of a hierarchy unless otherwise defined within the completTuple arguments. This function is useful when the current context of a query should be discarded to reference another value explicitly. 3

A more detailed explanation of this is covered later in the document.



13

Crosstabs work the same as lists, with two exceptions: 1. The nesting of dimensions is more sophisticated 2. Sorting works differently. The same concepts of sets, tuples, and current member still apply.

Sets

S e t s

Values

Figure 6

The value of 1,384 in Figure 6 could be explicitly referenced by the function: tuple([United States], [Golf Equipment], [Quantity sold], [2006 Q4])

- Or tuple([United States], currentMember([Product line]), [Quantity sold], [2006 Q4])

This is the same as the tuple example in the list report, with the addition of having to reference from other dimensions that exist in the crosstab. For the most part the use of tuple and currentMember are not necessary for simple reports because the current context and measure value will be evaluated based on the structure of the list, crosstab, chart, etc. These functions will play a more significant role in advanced reporting where the current context from the query container needs to be overridden to retrieve values that do not match the current row, column, and slicer intersection.

5 Navigating Hierarchies with MDX Functions 5.1

Referencing with Parents, Children, and Previous Dimensions are visualized as tree-structured hierarchies that contain multiple levels. The MDX query language provides formulas to “navigate” hierarchies to reference specific at different levels or positions in a hierarchy. Using a time dimension [All Time] as an example, Figure 7 below shows some MDX pseudo-coded functions and what they will return.



14

All Time Parent of [Q1 2005]

Q1 Jan

Feb

2005 Q2 Mar

FirstChild of [2005]

Q3

Q4

Apr

May

PrevMember of [May 2005]

2006 Q1 Jun

Q2

FirstSibling of [Q4 2006]

Q3

Q4

FirstSibling of [Q1 2006]

NextMember of [May 2005]

Cousin ([Jan 2005], [Q4]) Figure 7

The parent() of the [Q1 2005] member is [2005]. The parent() of the [Jan 2005] member is [Q1 2005]. The hierarchy navigation functions follow the family tree paradigm of children, parents, siblings, etc.. For example, the function firstSibling() returns the member within the branch under the parent of the hierarchy that it is a part of. In the above diagram, the firstChild([2006]) is [Q1 2006]. The firstSibling([Q1 2006]) is [Q1 2006], not [Q4 2005] because it stays within the context of its parent, [2006]. This behavior keeps from having to code around situations such as “if at the firstSibling() of the Quarter level, then return Q1, else…”. Conversely, functions that do not follow the family tree naming convention do not restrict themselves to staying within the dimension tree hierarchy structure. The () function returns all the of a level, regardless of the parent member. The function ([GOC].[Time].[Quarters]) returns all the in the quarter level ([Q1 2005] through [Q4 2006]) Similarly, the prevMember() and nextMember() functions do not restrict themselves to the parent of the current member it is under. The prevMember() of [Q1 2006] is [Q4 2005]. The prevMember() of [Jan 2005] in Figure 7 would be null.



5.2

15

Dimension Rollup Aggregations Dimensions and measures in a cube have rollup calculations that assigned when the cube is designed to define how measure values are aggregated or rolled up. The simple dimension shown in Figure 8 has months with individual revenue values at the lowest level of the dimension. In this example, the rollup aggregation function is sum. The revenue value for the quarter level is the sum of all the month level revenue values for each quarter, while the year level is the sum of all rolled up quarter values. In the scenario below, the all level will be exactly the same as the year level since there is only one year member, [2005]. If there were multiple years then the All level would be the sum of the rolled up values for each year. MDX calculations can often be simplified by leveraging this aggregation behavior already present in the cube.

Figure 8

5.3

Leveraging the Dimension Structure An example of how to use a navigation function would be to extend the [Product line], [Revenue] list report example in Figure 1 to add a percentage calculation. The percentage calculation used in this example will be the percentage [Revenue] that the current product line contributes to its parent’s revenue. For example: ƒ [Camping Equipment] is X% of the total [Product line] [Revenue] ƒ [Cooking Gear] is X% of the total [Camping Equipment] [Revenue] The calculation would be to take the total [Revenue] for each [Product line] member, and then divided it by the total [Revenue] for all [Product line] . The approach is to think of the relationship between the current [Product line] member in the list, and the parent member it belongs to. The problem can be defined as: “take the [Revenue] for the currentMember() of [Product line] and divide it by the total [Revenue] for the parent of the currentMember() of [Product line].”



16

All the values in the cube are determined by the intersection of the dimensions referenced in a function and the rollup aggregation function. The rollup of values is SUM for the [Revenue] values in the [Products] dimension. Therefore, the parent of current member of [Product line] level will be the [Products] level in the hierarchy, which gives the total [Revenue] for all [Product line](s).

[Products] (all products) is the parent of [Camping Equipment]

Figure 9

Writing the function to explicitly get the values would be as follows: tuple( currentMember( [GOC].[Products].[Products] ), [Revenue] ) / tuple( parent( currentMember( [GOC].[Products].[Products] ) ) ,Revenue])

The [Revenue] value for the currentMember() of [Product line] member in the list Divided by The [Revenue] value for the parent of the current [Product line] member in the list

When the currentMember() is [Camping Equipment], as the report iterates over the [Product line] , the value calculated is the [Revenue] value for [Camping Equipment] divided by the [Revenue] value for [Product line] (the parent). Because the rollup function is sum, the [Revenue] value for the parent of [Camping Equipment], [Product line], is the total revenue for all product lines. The results are shown in Figure 10.



17

Figure 10

A Report Studio may think that if the report is calculating the percentage for a product line by dividing by the total for all products, why use the parent( ) function? Why not explicitly reference the [Products] member? The products member has all the [Product line] as its’ children, so it represents all the [Product line] . It would seem simpler to just drag the [Products] member into the function to replace the parent( currentMember([GOC].[Products].[Products]) ): tuple( currentMember( [great_outdoors_company].[Products].[Products] ) , [Revenue] ) / tuple( [Products] , [Revenue] )

Figure 11

Running the report shows that the numbers are equivalent between the two formulas…

Figure 12 Proprietary Information


18

Or at least the numbers will be the same until the drills down on a product member (i.e. [Camping Equipment]):

Figure 13

The results are different because the formula: tuple( currentMember( [great_outdoors_company].[Products].[Products] ) ,[Revenue] ) / tuple( parent(currentMember( [great_outdoors_company].[Products].[Products] ) ) , [Revenue] )

has a denominator that is the parent of the current member in the list. This is [Products] when at the [Product line] level of [Camping Equipment]. However, when the current member is at the [Product type] level, such as with [Cooking Gear], then the parent will be in the [Product line] level; in this case [Camping Equipment]. On the other hand, the formula: tuple( currentMember( [great_outdoors_company].[Products].[Products] ) ,[Revenue] ) / tuple( [Products] , [Revenue] )

explicitly references the [Revenue] measure value for the [Products] level. This is regardless of which product member is being evaluated in the current context of the list. The results are that no matter where a drills, the first formula calculates the percentage [Revenue] for a product member of the next level up in the hierarchy (the parent). The second formula always calculates the percentage of the total [Products] revenue.



5.4

19

Using Set Functions Sets of data are used in virtually every Report Studio OLAP report whether the report writer realizes it or not. Sets can be a collection of from one or more dimensions and levels, can contain one member, or be empty (null). MDX provides several functions that allow a developer to explicitly create and manipulate sets of . Figure 14 shows some MDX pseudo code set functions and the resulting sets using an example time dimension.

Tail(([Quarters], 2)

All Time PeriodsToDate ([Year], [Q3 2005])

2005

Jan

2006

Q1

Q2

Q3

Q4

Q1

Feb

Mar

Oct

Nov

Dec

Q2

Q3

Q4

Children of [2006]

( [Months] ) Head(([Months], 2)

Figure 14

Many set functions will require a member as a parameter. The function children([2005]) will return the set of [Quarter] level for the [Year] level member [2005]. Report Studio will build the underlying MDX query using set functions to keep the nested member sets in context of one another as they are nested in the crosstab or list. The simple report shown in Figure 15 can easily be created by dragging in objects into the crosstab. It can also be built by using explicit references through MDX functions that nest or union the sets of data together. The important feature to note is in the nesting of the of the [Month] level under the [Quarter] level. The () function will return all of a level, but not every [Month] member is returned when the ([Month]) function is nested under the quarters for [2006] ( children( [2006] )). Report Studio generates the MDX that returns only the [Month] that are in context of the [Quarter] that they are each nested under.



20

Children ( [2006] )

( [Months] ) C8 = Months in context of the Quarter Nesting

Head(([Months], 2) Tail(([Quarters], 2) PeriodsToDate ([Year], [Q3 2005])

Figure 15

5.5

Member Unique Names (MUNs) One of the major differences between cubes and RDBMS’s can be summarized by describing RDBMS’s as keeping interrelated data separate until a SQL statement describes how the data is to be put together. OLAP cubes are the exact opposite; the interrelations between data and values are pre-calculated when the cube is built4. The internal pre-built data interrelations could be considered analogous to a roap within the cube. The roads are laid out and pre-built in the cube, not cobbled together at runtime as with RDMBS’s. Continuing the map analogy, in a dimension could be considered “destinations”, which have a precise location. There may be more than one road that goes to the member “destination”, but they all end up at the same place. The “roads” in this case would be the dimension hierarchies. Each hierarchy has a specific path to a member destination, but are independent of one another. The hierarchy “roads” in this example translate to Member Unique Names (MUN’s) in a cube. MUN’s pointers through a hierarchy to specific dimension or levels. MUN’s can also be compared to a unique key that defines a path to the member through a hierarchy. All cube technologies use some form of MUNs to identify . Using the Great Outdoors Company [Years] dimension from the Cognos 8 sample data, a Report Studio can right mouse on any member in a dimension, choose view properties, and see the MUN for that member. Using the [2006/Dec] member from the [Years] hierarchy, the MUN is defined as:

4

This is a generalization; of course custom calculations can be created and used in a report.



21

[GOC].[Years].[Years].[Month]->:[PC].[@MEMBER].[2006120120061231]5

MUN’s can change if the cube is re-organized (old roads demolished, new one’s built). Report writers need to be cognizant of this if they use specific MUN references. Fortunately, many cube structures are relatively static due to selection of appropriate keys during cube creation, and are also often referenced indirectly through formulas or queries instead of through explicit use of MUN’s. 5.5.1

When is [2006/Dec] not [2006/Dec]? The underlying structure of MUNs does have an impact on Report Studio report design. Different hierarchies in a dimension can reference the same member, but have different MUNs. A common example can be seen with Cognos PowerPlay cubes where the time dimension includes hierarchies for [Years] and [Current Month]. The MUN values in Figure 16 show that the the MUN for [2006/Dec] under the [Current Month] hierarchy is not the same as the MUN for [2006/Dec] under the [Years] hierarchy. Both hierarchies can be used to get the [2006/Dec] member, but they take different paths, and therefore have different MUNs. The hierarchies are not interrelated. This same concept also applies to other cube technologies.

[GOC].[Years]. [Years] .[Month]>:[PC].[@MEMBER].[20061201-20061231]

[GOC].[Years]. [Current Month] .[Month]>:[PC].[@MEMBER].[20061201-20061231]

Figure 16

The implication of this internal cube MUN structure to the report writer can be best shown by an example. One common request is to use the [Current Month] member as an anchor date for a report.

5

The syntax of MUN references can change from release to release. Older MUN syntax will continue to be ed in later releases, although new MUNs will be generated with the syntax for the current version. Proprietary Information


22

Figure 17

If the [Current Month] is [2006/Dec], then the current quarter logically would be [2004 Q4]. The report writer drags the [Current Month] member from the [Current Month] level in the [Current Month] hierarchy onto a crosstab. To get the current quarter, the report writer drags the [Quarter] level from the [Years] hierarchy and nests it above the [Current Month]. If the [Current Month] member is [2006/Dec], the expectation is that the [Quarter] will be [2006 Q4]. The following error occurs when the report is run.

Figure 18

Why this “error” is thrown can be seen by examining the MUNs and the hierarchies in Figure 16. The [2006/Dec] member as referenced in the [Current Month] hierarchy does not have a path or relationship to the [2006 Q4] member in the [Years] hierarchy. There is no [Quarter] level above the [Current Month] member or level in the [Current Month] hierarchy. This is not an error, but a consequence of how cubes work. The same results will be encountered regardless of the underlying OLAP technology being used. Fortunately there are several options for the Report Studio developer using MDX functions to mitigate this issue if it arises. One solution, the closingPeriod() function will be covered in the next section. NOTE: One of the key strengths of Report Studio is that a developer can place unrelated dimensions/hierarchies on the same edge. The following report specification will run even though [Current Month] and [Quarter] are from different hierarchies:

Figure 19



23

6 Syntax For Using Report Studio MDX Functions MDX is no different than any other query language in that the type expected for parameters and returned objects need to be followed. What is different is that MDX functions will use cube , sets, hierarchies, and levels as parameters and return values. It is often the case that parameters to MDX functions are also position independent. Developers will see that MDX is not universally strict with typing and will often coerce a parameter from one type to another when it makes sense. 6.1

Parameter Types One of the most common errors in building MDX functions is using the wrong MDX type as a function parameter. The typical error message caused by this will be similar to: Invalid coercion from ‘level’ to ‘member’, or ‘level’ to ‘hierarchy’, etc.

The most common occurrence of this coercion error is when using the currentMember() function. A typical case is when a developer drags the [Products] level into a crosstab or list, and wants to use the currentMember() function in custom calculation. It is logical to think: “I’m at the [Products] level; therefore I need the currentMember() of [Products] level.” Running report will then give the coercion error. The reason is that the currentMember() function requires a hierarchy as a parameter. This makes sense since the current member can be any member in any level in a dimension. The level within a hierarchy for the currentMember() will change when a drills up or down within the hierarchy.

A Level has been inserted currentMember calls for a hierarchy

The Correct Expression currentMember( [great_outdoors_company].[Products].[Products] ) Figure 20



6.2

Optional Parameters Many MDX functions have optional parameters that will affect what is returned. One example is the closingPeriod() function that allows a specific member to be returned from a hierarchy:

closingPeriod ( level [, member ] ) Returns the last sibling among the descendants of a member at a specified level. Typically used with a time dimension.

closingPeriod() is used to find the last member of a specific level, optionally restricted by another member (a.k.a. the closing period). Using the [Years] hierarchy in Figure 21 as an example, using closingPeriod() to get the last [Month] member can be broken down into: 1. “at a specified level” The choices for levels in the [Years] dimension are [Years] through [Month]. The level parameter would be [Month] if the member needed is the last month in the [Years] hierarchy. 2. Continuing the [Month] example, “Returns the last sibling among the descendants of a member at a specified level” translates to: a. The specified level is the [Month] level b. The “descendants” means that the descendants of [Month] is the set of at the [Month] level which can be traced up the tree to the referenced member. This works in a similar way to the children function but can operate further down than just one level. When the optional member parameter is not defined then the member used is the default member for the hierarchy. In our case that is the All member so the set of descendants is all months: [2004/Jan], Figure 21 [2004/Feb], …, [2006/Dec]. 3. The “last sibling” is the last member in the list of descendants. In this example, the closingPeriod() for the [Month] level, closingPeriod( [GOC].[Years].[Years].[Month] ), returns [2006/Dec]. Adding the optional member parameter of the [Year] member [2005] to the above example, closingPeriod([GOC].[Years].[Years].[Month], [2005]), returns [2005/Dec]. This translates exactly the same as the first example, except that “of a member” is now specified (get the last [Month] member under the [Year] member [2005]). Similarly, changing the level to the [Quarter] level, the closingPeriod([GOC].[Years].[Years].[Quarter], [2005]), returns [2005 Q4])


24


25

7 Working With Sets Of 7.1

Using The MDX union() Function Report writers often have the need to combine or manipulate member sets to build a report that does not match the hierarchies in the cube, or to focus on specific . MDX set functions allow member sets to be created, include or exclude from a set, return a set based on specific criteria, or change the ordering of a set. An example would be using the topCount() function to return the top two [Product type] based on [Quantity sold] combined with the parent [Product line] member for the top two [Product type] as shown in Figure 22 below.

Figure 22

The report in Figure 22 can use the topCount() function while taking advantage of Report Studio nesting. In the example above, the [Product line] level is dragged into the rows of a crosstab, followed by a nested data item with the topCount() function6. The syntax of the topCount() function is as follows: topCount ( set_exp , index_exp , numeric_exp ) This function sorts a set according to the values of "numeric_exp" evaluated at each of the of "set_exp", and returns the top "index_exp" .

topCount() takes a set, orders it by the value in the “numeric_exp” parameter, and returns the top “index_exp” . The function to get the top two [Product type] by [Quantity sold] for each [Product line] member would be: topCount([GOC].[Products].[Products].[Product type], 2 , [Quantity sold] ) )

6

that dragging a level into a crosstab or list in Report Studio is shorthand for the MDX function ( [dragged level] ). Proprietary Information


26

If the report was run at this point, the nested topCount() function would return only the top two [Product type] nested after the [Product line] . For [Camping Equipment], the nested returned by topCount() would be [Lanterns] and [Cooking Gear]7. The next step is to get the [Product line] member into the report. As shown in Figure 22, the requirement is to include the [Quantity sold] for [Camping Equipment] before [Lanterns] and [Cooking Gear], and on for each [Product line] member. The MDX union() function can be used to combine the two sets8: union ( set_exp1 , set_exp2 [ , ALL ] ) This function returns the union of 2 sets "set_exp1" and "set_exp2". The result retains duplicates only when the optional keyword ALL is supplied as the third argument.

Because [Product line] is nested before the data item with the topCount() function in this example, the union() function can be used to combine the current [Products] hierarchy member with the topCount() set of as follows: union( [GOC].[Products].[Products].[Product line] , topCount( [GOC].[Products].[Products].[Product type] , 2 , [Quantity sold] ) )

This will give the results of having [Camping Equipment], and all other the [Product line] shown before the top two [Product type] by [Quantity sold] for each [Product line] member as shown in Figure 22. Report Studio’s nesting capabilities often help keep the number MDX functions needed to a minimum. The same result as shown in Figure 22 for the topCount() example could be obtained by using the following MDX formula using the children() function on the current member of [Product line] in the crosstab: union( currentMember( [GOC].[Products].[Products] ) , topCount( children( currentMember( [GOC].[Products].[Products] ) ) , 2 , [Quantity sold] ) )

7 8

The proof is left up to the interested reader. sets can consist of multiple, one, or no (empty set).



27

To illustrate the drag and drop capabilities, the [Product line] data item could have also been nested next to the topCount data item to produce the same results union query and the same results. The layout after the drag and drop operation would look like the following:

Figure 23

On a more advanced note the results could have also been obtained without nesting by using the nestedset() function to define the nesting rules within the expression rather than by using the layout: nestedSet([great_outdoors_company].[Products].[Products].[Product line], union(currentMember([great_outdoors_company].[Products].[Produc ts]), topCount(children(currentMember([great_outdoors_company].[Pr oducts].[Products])),2,[Quantity sold])))

Figure 24

The specific details of the nestedSet() are not covered in this document.



7.2

28

Using The MDX except() Function To Manipulate Member Sets Just as MDX provides functions such as union() to combine sets of , it also provides functions to remove from sets. One of the more commonly used functions for removing is except(). Continuing the union() report example above, a common request is to provide the top two [Product line]’s sold in conjunction with the that are not part of the top two [Product line]’s sold. One approach might be to use a combination of count(), order(), and subset() MDX functions to get all the [Product type] that are not the top two [Quantity sold]. This approach will work but is overly complicated for our purposes. Thinking in of the member sets which are already in the report, it becomes clear that the answer to the question of “everything but the top two…” is simply all the [Product type] , except the top two. The syntax of the except() function from the Report Studio function tip is: except ( set_exp1 , set_exp2 [,ALL] ) Returns the of "set_exp1" that are not also in "set_exp2". Duplicates are retained only if the optional keyword ALL is supplied as the third argument.

The except() function to return all [Product type] except the top two by [Quantity sold] would be: except( [GOC].[Products].[Products].[Product type], topCount( children( currentMember( [GOC].[Products].[Products] ) ), 2 , [Quantity sold] ) ) )

The first parameter to the except() function is the set of all [Product type] . The second, is the set of that are the top two by [Quantity sold], just as in the previous union() example. The result set from the topCount() function are the that are to be excluded from the result set. To complete the example where the report is required to have the [Product line] member total before the [Product type] member set, the except() statement can be used as a parameter to a union() statement. union( [Product line] , except( [GOC].[Products].[Products].[Product type], topCount (children( currentMember( [GOC].[Products].[Products] ) ), 2 , [Quantity sold] ) ) )



29

The [Product line] level can be used in the union statement because it takes advantage of Report Studio nesting giving the result shown in Figure 25.

Figure 25

7.3

Using The MDX order() Function To Sort Member Sets The except() example report output in Figure 25 is not ordered by [Quantity sold]. The order of the is the order that is defined in the cube for the [Product type] , minus the excluded by the except function. MDX provides the order() function to sort based on a specific value. order ( set_exp , value_exp [ , ASC | DESC | BASC | BDESC ] ) Arranges of a specified set, as determined from the set of values created by evaluating "value_exp" for each value of the set, and modified by the third parameter. There are two varieties of order: hierarchized (ASC or DESC) and non-hierarchized (BASC or BDESC, where B stands for "break hierarchy"). The hierarchized ordering first arranges according to their position in the hierarchy. Then it orders the children of each member according to "value_exp". The non-hierarchized ordering arranges in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.

Using order() to sort the is done by placing the order function in the correct position in the query, shown in bold below: union( [Product line] , order( except( [Product type] , topCount( children( currentMember( [GOC].[Products].[Products] ) ) , 2 , [Quantity sold] ) ), [Quantity sold], DESC ) )

By placing the order() function within the union and around the member set returned by the except() function, the report will always show the current [Product line] first as seen in Figure 26.



30

Figure 26

The value that the order() function sorts on is simply [Quantity sold] in this example. If [Quantity sold] were nested under another dimension, such as [Years], then a tuple() function would be needed to explicitly reference the sort value defined by a specific combination of the [Product type] and [Years] . There are two types of sorting that can be done by the order function: 1. Within the dimension hierarchy 2. “breaking” the hierarchy. By default, the order() function will sort within their respective hierarchies. “Breaking” the hierarchy means that the order() function ignores the hierarchical structure of the dimension. The list report in Figure 27 orders the of the product dimension using the DESC (non-breaking dimension ordering) and BDESC (break dimension ordering) parameters.

Figure 27



31

The ordering of the two lists in Figure 27 is accomplished with the following functions: order_DESC =

order( ( [GOC].[Products].[Products] ) , [Quantity sold], DESC )

order_BDESC =

order( ( [GOC].[Products].[Products] ) , [Quantity sold], BDESC )

The DESC descending order parameter used in the list on the left in Figure 27 tells the order() function to sort each member within the context of its hierarchy. Based on the hierarchy, [Products] is the first member returned by order(), then [Camping Equipment] ([Product line] level). The next set of will then be the children of [Camping Equipment] ([Product type] level), followed by the children of the [Product type] ([Product name] level). Once all of the of [Camping Equipment] down to its lowest level has been sorted, the next [Product line] member in the sort, [Cooking Gear] and all its’ descendants are sorted. [Camping Equipment] returns before [Cooking Gear] since [Camping Equipment] has a greater [Quantity sold] value. The order() follows the structure of the dimension hierarchy with ordering occurring within each hierarchy level. The BDEC parameter used in the order() function in the list on the left, tells the order() function to ignore the hierarchy structure and sort on [Quantity sold] values only. This “breaks” the hierarchy ordering defined in the dimension, hence the “B” in the BASC and BDESC optional parameter names.

8 Member Summary Functions 8.1

The MDX total() And aggregate() Functions Member Summary functions return value calculations based on a member set. Two of the more common member summary functions are total() and aggregate(). total (<currentMeasure | expr> within set set_expr {, set_expr }) total (<currentMeasure | expr> within < detail | aggregate > expr) Returns the total value of selected data items.

aggregate (<currentMeasure | expr> within set set_expr {, set_expr }) aggregate (<currentMeasure | expr> within < detail | aggregate > expr) Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression.



32

Each function has two signatures, with the first definition for each applying to MDX usage. total() will total either the “currentMeasure”9 or a value defined by “expr”. The same applies for the aggregate() function. The two functions are often interchangeable because the rollup aggregation function defined within the cube is typically sum, and the totals for a report are done in the context of a dimension’s hierarchies. How dimension hierarchies can affect the results of aggregate() and total() can be illustrated by modifying the list example in Figure 27:

Figure 28

The total() and aggregate() functions used for the calculations in Figure 28: total() [Quantity sold] = [order_BDESC] ) aggregate() [Quantity sold] = within set [order_BDESC] )

total( [Quantity sold] within set aggregate( [Quantity sold]

Notice that the set parameter is the ordered set of product from the previous example10. The total() function returns the total for all in the [order_BDESC] set ( [Products] + [Camping Equipment] + [Outdoor Protection] … = 8,861,416 ), which is greater than the total [Products] sold. The number using total() includes lower level values which have already been rolled up into the higher level . The aggregate() function uses the defined aggregation for the [Products] hierarchy, which gives the correct amount for the total [Products] sold, 2,215,354. The aggregate() function in this case is equivalent to: tuple( [Quantity sold] , [Products] )

where [Products] is the [Products] member in the [Products] hierarchy. The tuple() value is the same as the total() function because the ordered set being aggregated is the complete set of [Product] hierarchy and the aggregation rule for the [Quantity sold] in the [Products] dimension is sum. 9

currentMeasure refers to the current measure being evaluated in a crosstab. No particular reason for this outside of illustrating that sets can be used for more than just crosstab edges.

10



8.2

33

The MDX percentage() Function The percentage() function is similar to total() and aggregate(): percentage ( numeric_expr [ tuple member_expr {, member_expr } ] within set set_expr {, set_expr} ) Returns the percent of the total value for the selected data items.

The percentage calculation for the [Quantity sold] of all [Products] in the list shown in Figure 29 could be: percentage( [Quantity sold] within set [Products] )

The percentage of [Quantity sold] is for the [Product] member, that is, the total [Quantity sold] for all [Products]. This will give the percentage of each [Products] member [Quantity sold] of the total [Quantity sold]. The formula to calculate the percentage of the [Quantity sold] of a member’s parent is: percentage( [Quantity sold] within set parent( currentMember( [GOC].[Products].[Products] ) ) )

Here the currentMember() and parent() functions are used to find the member one level higher in the [Products] hierarchy. The resulting modified list report using the order_DESC is shown in Figure 29.

Figure 29

The first percentage column gives the overall percentage for each [Products] member. The percentage calculation in the second column using the parent() calculates the percentage [Quantity sold] for the current member’s parent [Lanterns] is 40% [Camping Equipment], [Firefly 2] is 14% of [Lanterns], etc. For the second percentage the value for [Product] is blank because [Product] is at the top of the hierarchy and no parent member exists.



34

9 Conclusion Report Studio is a very powerful report writing tool that exposes powerful MDX functions to extend and enhance the types of reports that can be built from OLAP cube or Dimensionally Modeled Relational data sources. This document outlines the three key concepts a Report Studio report writer needs to be comfortable with to be effective with MDX: 1. Referencing specific values using the tuple() function. 2. Being cognizant of how MDX functions iterate through a set and the currentMember() function. 3. Using and manipulating member sets in functions or by nesting to get the desired data in a report. Examining the MDX function list in Appendix A, it is apparent that there is a relatively small number of MDX functions that exist, and that many functions are related11 or have similar behaviors. When starting to use MDX functions in Report Studio, s quickly realize that understanding and applying the three basic concepts of referencing values, how member sets are iterated, and how to manipulate member sets are key to using all of the MDX functions for effective report writing.

11

topCount() and bottomCount(), or topPercent() bottomPercent() are examples of this.



35

Appendix A Appendix A categorizes the Cognos 8 MDX functions on whether they work with , sets, dimensions, or provide other functionality, such as information about a member or set. The descriptions are directly from the Cogno8 MR2 Report Studio tool tips. Categorizing the functions provides a quick reference for the report designer to determine what functions apply when building calculations that have , sets, or need other information. MDX Function Categories Member Summary (numeric) completeTuple aggregate tuple average count maximum median minimum percentage percentile quantile quartile rank standarddev total variance

Level and Tuple Hierarchy

Member

Set

ancestor closingperiod cousin currentmember defaultmember firstchild firstsibling item lag lastchild lastsibling lead

ancestors hierarchy bottomCount level bottomPercent levels bottomSum children descendants except emptySet filter generate head hierarchize

member nextmember openingperiod parallelperiod parent prevmember

intersect lastPeriods nestedSet order root set periodsToDate siblings subset tail topCount topPercent topSum union unique

Other caption currentMeasure ordinal roleValue value

Member Function Descriptions Function ancestor

Description Returns the ancestor of the specified member at either the specified (named) level or the specified number of levels above the member. Note:



36

Function

Description The result is not guaranteed to be consistent when there is more than one such ancestor. closingPeriod Returns the last sibling among the descendants of a member at a specified level. Typically used with a time dimension. cousin Returns the child member of member2 with the same relative position as the member1 is under its parent. currentMember Returns the current member of the hierarchy during an iteration. If the specified hierarchy is not present in the context in which the expression is being evaluated, its default member is assumed. defaultMember Returns the default member of a hierarchy. firstChild Returns the first child of a specified member. firstSibling Returns the first child of the parent of a member. item Returns a member from a specified location within a set. The index into the set is zero based lag Returns the sibling member that is a specified number of positions prior to a specified member. lastChild Returns the last child of a specified member. lastSibling Returns the last child of the parent of a member. lead Returns the sibling member that is a specified number of positions following a specified member. member Defines a member based on the specified expression in the specified hierarchy. "string1" is used to identify the member created by this function it must be unique in the query, and must be different from any other member in the same hierarchy. "string2" is used as the caption of the member; if it is absent, the caption is empty. If the hierarchy is omitted, the measure dimension is assumed.

nextMember openingPeriod parallelPeriod

parent prevMember

Note: All calculations used as grouping items whose sibling items are other calculations or member sets, should be explicitly assigned to a hierarchy using this function, otherwise the results are not predictable. The only exception to this is where the calculation involves only of the same hierarchy as the siblings. In that case the calculation is assumed to belong to that hierarchy. Returns the next member in the level to which the specified member exists. Returns the first sibling member among the descendants of a member at a specified level. Typically used with a time dimension. Returns a member from a different period in the same relative position as a specified member. This function is similar to the "Cousin" function, but is more closely related to time series. It takes the ancestor of "member" at "level" (call it "ancestor"); then it takes the sibling of "ancestor" that is offset (follows) by "int exp" positions, and returns the descendants of that sibling in the same relative position as the specified member as under "ancestor".. Returns the member that is the parent of the specified member. Returns the member that immediately precedes the specified member in the same level.

Set Function Descriptions Function

Description



Function ancestors

bottomCount bottomPercent bottomSum children descendants

except

emptySet filter generate head hierarchize intersect lastPeriods

nestedSet

37

Description Returns all the ancestors of a member at a specified level, or distance above the member. (Most data sources only one ancestor at a specified level, but some more than one. Hence the result is a member set.) This function sorts a set according to the value of "numeric_exp" evaluated at each of the of "set_exp", and returns the bottom "index_exp" .. This function is similar to bottomSum, but the threshold is "numeric_exp1" percent of the total. This function sorts on "numeric_exp2", evaluated at the corresponding member of "set_exp", and picks up the bottommost elements whose cumulative total is at least numeric_exp1. Returns the set of children of a specified member.. Returns the set of descendants of a set of at a specified level(qualified name) or distance(integer 0..n) from the root. Duplicates will be removed from the set. Multiple options may be specified (separated by a space) to determine which are to be returned. Returns the set of descendants of a set of at a specified level(qualified name) or distance(integer 0..n) from the root. Duplicates will be removed from the set. Multiple options may be specified (separated by a space) to determine which are to be returned. Returns an empty member set for the specified hierarchy. Returns the set resulting from filtering a specified set based on the boolean condition. Each member is included in the result if and only if the corresponding value of "boolean_exp" is true. This function evaluates "set_exp2" for each member of "set_exp1" and s the resulting sets by union. If ALL is specified, duplicates in the result are retained. Returns the first "index_exp" elements of "set_exp". The default for "index_exp" is 1. This function orders the of a set in a hierarchy. in a level are sorted in their natural order, which is the default ordering of the along a dimension when no other sort conditions are specified. Returns the intersection of two input sets. The result retains duplicates only when the optional keyword ALL is supplied as the third argument. Returns the set of from the same level that ends with the specified member. The number of returned is the absolute value of "integer_exp". If "integer_exp" is negative, following and including the specified member are returned. Typically used with a time dimension. Returns the set of in a hierarchy or level. In the case of a hierarchy, the order of the in the result is not guaranteed; if a predictable order is required, an explicit ordering function (such as hierarchize) must be used. Returns the set of of set_expr2 evaluated in the context of the current member of set_exp1.



Function order

root set periodsToDate

siblings subset

tail topCount topPercent topSum union unique

38

Description Arranges of a specified set, as determined from the set of values created by evaluating "value_exp" for each value of the set, and modified by the third parameter. There are two varieties of order: hierarchized (ASC or DESC) and non-hierarchized (BASC or BDESC, where B stands for "break hierarchy"). The hierarchized ordering first arranges according to their position in the hierarchy. Then it orders the children of each member according to "value_exp". The non-hierarchized ordering arranges in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default. Returns the root of a hierarchy Returns the list of defined in the expression. The must belong to the same hierarchy. Returns a set of sibling from the same level as a given member, as constrained by a specified level. It locates the ancestor of "member" at "level", and returns that ancestor's descendants at the same level as "member", up to and including "member". Typically used with a time dimension. Returns the children of the parent of the specified member. Returns a subset of from a specified set starting "index_exp1" from the beginning. If the count "index_exp2" is specified, that many (if available) are returned. Otherwise, all remaining are returned. Returns the last "index_exp" elements of "set exp". The default for "index_exp" is 1. This function sorts a set according to the values of "numeric_exp" evaluated at each of the of "set_exp", and returns the top "index_exp" . This function is similar to topSum, but the threshold is "numeric_exp1" percent of the total. This function sorts on "numeric_exp2", evaluated at the corresponding of "set_exp", and picks up the topmost elements whose cumulative total is at least "numeric_exp1". This function returns the union of 2 sets "set_exp1" and "set_exp2". The result retains duplicates only when the optional keyword ALL is supplied as the third argument. Removes all duplicates from the specified set. The remaining retain their original order.

Level and Hierarchy Function Descriptions Function hierarchy level levels

Description Returns the hierarchy that contains the specified level, member, or member set. Returns the level of a member Returns the level in a hierarchy whose distance from the root is specified by "index".

Tuple Function Descriptions Function completeTuple

Description Similar to "tuple", identifies a cell location (intersection) based on the specified , each of which must be from a different dimension.



tuple

39

However, completeTuple implicitly includes the default member from all dimensions not otherwise specified in the arguments, rather than the current member. The value of this cell can be obtained with the "value" function. Identifies a cell location (intersection) based on the specified , each of which must be from a different dimension. Implicitly includes the current member from all dimensions not otherwise specified in the arguments. The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. .

Other Function Descriptions Function Description caption Returns the caption values of a specified argument. currentMeasure Keyword that can be used as the first argument of member summary functions. ordinal Returns the zero-based ordinal value (distance from the root level) of the specified level. roleValue Returns the value of the attribute that is associated with the role whose name is specified by "string" within the specified context. The second argument is optional only in a number of limited circumstances, where it can be derived from other context. Applications can be made portable across different data sources and models by accessing attributes by role, rather than by query item ID. (For dimensionally modeled relational data sources, assignment of roles is the modeler's responsibility.) Intrinsic roles that are defined for of all data source types include: _businessKey, _memberCaption, _memberDescription, _memberUniqueName.. value Returns the value of the cell identified by a tuple. Note that the default member of the Measures dimension is the Default Measure.


Olap Mdx Functions 536z1h

Overview 26281t

More details 6y5l6z

Related Documents 3h463d

Olap Mdx Functions 536z1h

Olap 1a2h66

Essbase Mdx j6v5t

Mdx Primer 1c4s15

Mdx Primer 1c4s15

Ensayo Olap a1k1a

More Documents from "Sravan Arella" 714l4v

Olap Mdx Functions 536z1h

Mini Inverter Mini Project Report. 2h1n11

50+ Top Datastage Interview Questions And Answers Pdf 533t4l

4p345f

Mba R15 Ii Semester Syllabus Jntu 536136

Lpg Detection Alarm Mini Project Report. 1u1c4p