MDXjs

A copy of the MDXpy library, but in javascript, credits to them.

Install

npm install hshp@mdxjs

Members

The ElementMember class represents a element of an dimension.

const m0 = ElementMember.fromUniqueName("[Article].[a1]")
console.log(m0.uniqueName)
> "[Article].[Article].[a1]"

const m1 = ElementMember.fromUniqueName("[Article].[HCP].[a1]")
console.log(m1.uniqueName)
> "[Article].[HCP].[a1]"

const m2 = ElementMember.of("Article", "a1")
console.log(m2.uniqueName)
> "[Article].[Article].[a1]"

const m3 = ElementMember.of("Article", "HCP", "a1")
console.log(m3.uniqueName)
> "[Article].[HCP].[a1]"

const m4 = ElementMember.of("[Article].[HCP].[a1]")
console.log(m4.uniqueName)
> "[Article].[HCP].[a1]"

console.log(m3.equals(m4))
> true

The CalculatedMember class extends the ElementMember class and represents a member in an MDX query.

const cm0 = CalculatedMember.aggregate(
    "Article",
    "HCP",
    "CalcArticle",
    MdxHierarchySet.members([m1, "[Article].[HCP].[a2]"])
)
console.log(cm0.toMdx())
> "MEMBER [Article].[HCP].[CalcArticle] AS AGGREGATE({[Article].[HCP].[a1],[Article].[HCP].[a2]})"

const cm1 = CalculatedMember.aggregate(
    "Article",
    "HCP",
    "CalcArticle",
    MdxHierarchySet.members([m1, "[Article].[HCP].[a2]"])
    new MdxCellCoordinates('Cube', MdxTuple.of(Member.of("Product", "p1"), "[Measure].[Measure].[m1]"))
)
console.log(cm1.toMdx())
> "MEMBER [Article].[HCP].[CalcArticle] AS AGGREGATE({[Article].[HCP].[a1],[Article].[HCP].[a2]},[Cube].([Product].[Product].[p1],[Measure].[Measure].[m1]))"

It includes methods for various MDX calculations.

• aggregate
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• avg
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• count
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • includesEmptyCells: boolean = true - Whether to include empty cells in the count
• max
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• median
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• min
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• sum
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• var
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• varp
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxSet: MdxHierarchySet - Set expressions
  • mdxCellCoordinates?: MdxCellCoordinates - Optional numeric expression
• lookup
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • cube: string - The cube name
  • mdxTuple: MdxTuple - The tuple for the cell coordinates
• lookupFromCoordinates
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • mdxCellCoordinates: MdxCellCoordinates - Cell expression
• lookupAttribute
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • attributeDimension: string - The attribute dimension
  • attribute: string - The attribute name
• lookupProperty
  • dimension: string - The dimension of the element
  • hierarchy: string - The hierarchy of the element
  • element: string - The element name
  • attributeDimension: string - The attribute dimension
  • attribute: string - The attribute name

MdxTuple

The MdxTuple class represents a tuple of ElementMenber objects.

const mt = MdxTuple.of(m1, "[Product].[Product].[p1]")
console.log(mt.toMdx())
> "([Article].[HCP].[a1],[Product].[Product].[p1])"

mt.addMember(Member.of("Version", "v1"))
console.log(mt.toMdx())
> "([Article].[HCP].[a1],[Product].[Product].[p1],[Version].[Version].[v1])"

MdxSet

The MdxSet class represents a set in MDX. Provides various static methods to create different types of sets.

• crossJoins
  • sets: MdxSet[] - The sets to cross join
• unions
  • sets: MdxSet[] - The sets to union
  • allowDuplicates: boolean = false - Whether to allow duplicates in the union
• tuples
  • tuples: MdxTuple[] - The tuples to include in the set

MdxHierarchySet

The MdxHierarchySet class extends the MdxSet class.

const mhs0 = MdxHierarchySet.tm1SubsetAll("Article")
console.log(mhs0.toMdx())
> "{TM1SUBSETALL([Product].[Article])}"

const mhs1 = MdxHierarchySet.tm1DimensionSubsetToSet("Article", "End_User")
console.log(mhs1.toMdx())
> "{TM1SUBSETTOSET([Article].[Article],'End_User')}"

const mhs2 = MdxHierarchySet.allLeaves("Article")
console.log(mhs2.toMdx())
> "{TM1FILTERBYLEVEL({TM1SUBSETALL([Article].[Article])},0)}"

Provides various static methods to create different types of sets.

• tm1SubsetAll
  • dimension: string
  • hierarchy?: string
• allMembers
  • dimension: string
  • hierarchy?: string
• tm1SubsetToSet
  • dimension: string
  • hierarchy: string
  • subset: string
• tm1DimensionSubsetToSet
  • dimension: string
  • subset: string
• allConsolidations
  • dimension: string
  • hierarchy?: string
• allLeaves
  • dimension: string
  • hierarchy?: string
• defaultMember
  • dimension: string
  • hierarchy?: string
• member
  • member: ElementMember
• members
  • members: ElementMember[]
• parent
  • member: ElementMember
• firstChild
  • member: ElementMember
• lastChild
  • member: ElementMember
• children
  • member: ElementMember
• ancestors
  • member: ElementMember
• ancestor
  • member: ElementMember
  • ancestor: number
• drillDownLevel
  • member: ElementMember
  • level: number
• descendants
  • member: ElementMember
  • levelOrDepth?: MdxLevelExpression
  • descFlag?: Order
• fromString
  • dimension: string
  • hierarchy: string
  • mdx: string
• range
  • startMember: ElementMember
  • endMember: ElementMember
• unions
  • sets: MdxHierarchySet[]
  • allowDuplicates: boolean = false

Instance methods to manipulate the set can be concatenated.

const mhs3 = MdxHierarchySet.tm1SubsetAll("Article").filterByLevel(0).filterByPattern("a3*").tm1Sort()
console.log(mhs3.toMdx())
> "{TM1SORT({TM1FILTERBYPATTERN({TM1FILTERBYLEVEL({TM1SUBSETALL([Article].[Article])},0)},'a3*')},ASC)}"

• filterByAttribute
  • attributeName: string
  • attributeValues: (string | number)[]
  • operator: string = '='
• filterByProperty
  • propertyName: string
  • propertyValues: (string | number)[]
  • operator: string = '='
  • typed: boolean = false
• filterByPattern
  • wildcard: string
• filterByLevel
  • level: number
• filterByElementType
  • elementType: 'NUMERIC' | 'STRING' | 'CONSOLIDATED'
• filterByCellValue
  • cube: string
  • mdxTuple: MdxTuple
  • operator: string
  • value: string | number
• filterByInstr
  • cube: string
  • mdxTuple: MdxTuple
  • substring: string
  • operator: string = '>'
  • position: number = 0
  • caseInsensitive: boolean = 0
• tm1Sort
  • ascending: boolean = true
• hierarchize
• head
  • head: number
• tail
  • tail: number
• subset
  • start: number
  • length: number
• topCount
  • cube: string
  • mdxTuple: MdxTuple
  • top: number
• bottomCount
  • cube: string
  • mdxTuple: MdxTuple
  • bottom: number
• union
  • otherSet: MdxHierarchySet
  • allowDuplicates: boolean = false
• intersect
  • otherSet: MdxHierarchySet
• except
  • otherSet: MdxHierarchySet
• order
  • cube: string
  • mdxTuple: MdxTuple
  • order: 'ASC' | 'DESC' | 'BASC' | 'BDESC' = 'BASC'
• orderByAttribute
  • attributeName: string
  • order: 'ASC' | 'DESC' | 'BASC' | 'BDESC' = 'BASC'
• generateAttributeToMember
  • attribute: string
  • dimension: dimension
  • hierarchy?: string
• tm1DrillDownMember
  • otherSet?: MdxHierarchySet
  • recursive: boolean = true

MdxBuilder

The MdxBuilder is used to build MDX queries. MdxSet are placed on the axes. Zero suppression can be switched on or off per axis. The actual MDX expression is generated with the toMdx method.

const mb = MdxBuilder.fromCube("Cube").addSetToColumnAxis(MdxHierarchySet.allLeaves("Article"))
console.log(mb.toMdx())
> "SELECT {TM1FILTERBYLEVEL({TM1SUBSETALL([Article].[Article])},0)} ON 0 FROM [CUBE]"

mb.columnsNonEmpty();
console.log(mb.toMdx())
> "SELECT NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([Article].[Article])},0)} ON 0 FROM [CUBE]"

Static method to create a MDX.

• fromCube
  • cube: string

Instance methods can be concatenated.

• withMember
  • member: CalculatedMember
• columnsNonEmpty
• rowsNonEmpty
• addSetToColumnAxis
  • mdxSet: MdxSet
• addSetToRowAxis
  • mdxSet: MdxSet
• addMemberToWhere
  • member: ElementMember
• removeMemberFromWhere
  • member: ElementMember
• where
  • ...members: ElementMember[]
• addPropertiesToColumnAxis
  • ...properties: ElementMember[]
• addPropertiesToRowAxis
  • ...properties: ElementMember[]