• Home
• Blog
• MSBI
• Learn to Write MDX Queries in Analysis Services (SSAS)

# Learn to Write MDX Queries in Analysis Services (SSAS)

## MDX (MultiDimensional eXpressions)

–> To work with Normal two – dimensional applications, two-dimensional programming languages are enough ( C, C ++, .NET …etc)
–> To work with Two – Dimensional databases, two- dimensional Query language SQL is wrought (Oracle SQL, T – SQL, Teradata SQL – etc.)
–> To work with multidimensional allocations and multidimensional databases the above specified or not enough so we go for a separate expression and Query language ‘ MDX ‘.

If you would like to Enrich your career with an SSAS certified professional, then visit Mindmajix - A Global online training platform: “ SSAS Training Certification Course ". This course will help you to achieve excellence in this domain.

Important terminology in MDX :
a) Member: Dimension attribute is called member
–> Syntax:-
[Dimension table name] . [Attribute Name]
Ex:         [ Product ] . [ Product Name ]
[ Location ] . [ Location name ]
Measure: Fact attribute is called Measure.
Syntax: [ Measures ] . [ Measures Name ]
Ex:         [ Measures ] . [ Actual Cost ]
[ Measures ] . [ Estimated Cost ]
TUPLE: Collection of Measures or Members is called Tuple.
a. Starts with (
b. Ends with   )
Ex:      ( [ Measures ] . [ Actual Cost ] , [ Measures ] . [ Estimated Cost ] )
SET:     Group of Tuples are called as SET.
a. Stores with {
b. Ends with    }
Ex:          {
( [ Measures ] . [ Actual Cost ] , [ Measures ] . [ Estimated Cost ] ,
( [ Measures ] . [ Actual Cost ] , [ Measures ] . [ Estimated Cost ] )
}

### MDX Query

1. Generally, MDX queries we write in analysis services cube database.
2. For retrieving data from cube database we use Select statements.
Syntax:
Select { Measures / Members } ON columns , { Measures / Members } ON rows
From < cube name > where < condition > ;

Some Functions In MDX And There Meanings:
There  are two types functions
1) Some Functions takes Parameters
Ex:  TOP COUNT, BOTTOM COUNT, ISEMPTY etc
(Top count) ,  (Bottom Count)
2) Functions without Parameters
Ex: MEMBERS,ALLMEMBERS,CHILDREN,PREVMEMBER,CURRENTMEMBER Etc...
.MEMBERS: It displays the child members without including the total.
.ALLMEMBERS: Display All members and their total.
.PREVMEMBER: Display previous members to the current member.
.CURRENTMEMBER: Display the current cell member value

3) Functions with Parameters
ISEMPTY: It verifies whether the member is empty or not.
Syntax: Is Empty (set)
TOPCOUNT: Display Top values.
Syntax: Top Count (set, value)
BOTTOMCOUNT: Display bottom count of values
Syntax: Bottom Count (set, value)
FILTER: It filters the given set based on the condition
Syntax: Filter (set, condition)
ORDER: It displays the set by keeping a descending & ascending on the given column.
DISTINCT ( ): It displays the set values
DISTINCT ( {set} )

4) Working with Hierarchies
We refer hierarchies member values in 2 ways.
a. [Dimension] . [Hierarchy] . [Members]
b. [Dimension] . [Hierarchy] . [Level] . [Members]
NOTE: If we don’t specify the level, it displays all member values.
CROSS JOIN: CROSS JOIN ( {set} , {set} ) (OR) {set} * {set}
Important MDX queries:
SSMS –> Analysis Services –> TEXTILES_CUBE –> RC –> New Query –> MDX

Checkout SSAS Interview Questions

### MDX Queries

1) DISPLAY FIRST MEASURE SUM
Syntax: Select from [TEXTFILES_CUBE]  cube name
2) DISPLAY NO. OF ROWS IN THE CUBE
Syntax: Select [Measures] . [TEXT FACT COUNT] on columns from [TEXTFILES_CUBE]
3) DISPLAY ALL BRANCHES ACTUAL COST
Syntax: Select [Measures] . [ActualCost] on columns , [Product] . [Brand] on rows from [DSV_textfiles_cube]
Actual cost    9800.
4) DISPALY BRANCHES AND THEIR ACTUAL COST
Syntax: Select [Measures] . [ActualCost] on columns , [Product] . [Brand] . children on rows from [TextFile_cube]
5) DISPALY ALL BRANCHE ACTUAL COST AND SUM OF ALL ACTUAL COST
Syntax: Select [Measures] . [ActualCost] on columns , [Product] . [Brand] . ALLMEMBERS on rows from [TEXTFILES_CUBE]
6) DISPLAYING EVERY RAW MATERIAL AND LOCATION, THEIR ACTUAL COST
Syntax: Select ([Measures] . [ActualCost] , [Measures] . [Estimated cost]) on columns , ([RawMaterial] . [Rawmaterial ID] . children [Location] . [Loc Name] . children) on Rows from [TEXTFILES_CUBE]
7) DISPLAY THE FIRST RAW MATERIAL ACTUAL COST:
Syntax: Select [Measures] . [ActualCost] on columns . [RawMaterial] . [Rawmaterial ID]
first child on rows from [TEXTFILES_CUBE]
8) DISPLAYING TOP TWO VALUES OF THE LOCATION
Syntax: Select [Measures] . [ActualCost] on columns , TopCount ([Location] . [ LocName] . children,2 ) on rows from [TEXTFILES_CUBE]
9) DISPLAYING BOTTOM TWO LOCATION VALUES
Syntax: Select [Measures] . [ActualCost] on columns , Bottom Count ([Location] . [LocName] . children, 2 ) on rows from [TEXTFILES_CUBE]
10) DISPLAY THE LOCATION TOOLS ACTUAL COST is > 1000
Syntax: Select [Measures] . [ActualCost] on columns , filter ([Location] . [ LocName] . children,    [Measures] . [ActualCost] > 1000) on rows from [TEXTFILES_CUBE]
11) DISPLAY THE DATA IN ACTUAL COST SORTED ORDER IN ASC
Syntax: Select [Measures] . [ActualCost] on columns , order ([Location] . [ LocName] . children,    [Measures] . [ActualCost] , ASC ] on rows
12) DISPLAY THE CRO PRODUCT OF LOCATION PRODUCT AS WELL AS THEIR ACTUAL, ESTIMATED COST.
Syntax: Select {[Measures] . [ActualCost] , [Measures] . [Estimated cost]} on columns, cross join ([Location] . [ LocName] . children , [Product] . [Product Name] . children) on rows from [TEXTFILES_CUBE]
OR
Select {[Measures] . [ActualCost] , [Measures] . [Estimated cost]} on columns, cross join ([Location] . [ LocName] . children) * ([EProduct] . [Product Name] . children) on rows from [TEXTFILES_CUBE]
13) DISPLAY DISTINCT FROM DISTINCTWISE VALUES AND LACATIONWISE
Syntax: Select {[Measures] . [ActualCost] , [Measures] . [Estimated cost]} on columns, DISTINCT ([Location] . [ LocName] . children , [Product] . [Product Name] . children) on rows from [TEXTFILES_CUBE]
14) DISPLAY 2009 YEAR ACTUAL COST SUM
Syntax: Select [Measures] . [ActualCost] on columns from [TextFile_Cube] where [Time] .[Year] . & [2009]
OR
Select [Measures] . [ActualCost] on columns , [Location] . [ LocName] . children on rows from [DSV_TextFiles_cube] where {[Time] . [Year] . & [2009]}

Conditional Expressions

IF: IF ( , success stmt , failed stmt )
Eg: IF ([Measure] .[ActualCost] – [Measures] . [Estimated cost] > 0,1,0)
CASE: Evaluates against multiple conditions
CASE
When < condition1 > then < statement1 >
When < condition2 > then < statement2 >
When < condition3 > then < statement3 >
When < condition4 > then < statement4 >
ELSE < statement 5 >
END
Ex: Case when [Measure] .[ActualCost] – [Measures] . [Estimated cost] > 0 Then -1 / where [Measure] .[ActualCost] – [Measures] . [Estimated cost] < 0 Then 1 / When [Measure] .[ActualCost] – [Measures] . [Estimated cost] = 0 Then 0
END

### List of Related Microsoft Certification Courses:

 SSIS Power BI SSRS SQL Server SCCM SQL Server DBA SharePoint BizTalk Server Team Foundation Server BizTalk Server Administrator

Course Schedule
NameDates
MSBI TrainingSep 24 to Oct 09View Details
MSBI TrainingSep 28 to Oct 13View Details
MSBI TrainingOct 01 to Oct 16View Details
MSBI TrainingOct 05 to Oct 20View Details
Last updated: 03 Apr 2023