Home / MSBI

Learn to Write MDX Queries in Analysis Services (SSAS)

Rating: 5.0Blog-star
Views: 12120
by Raunaak
Last modified: March 25th 2021

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:
Navigation:
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               

Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

 

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

 

About Author

author
NameRaunaak
Author Bio

As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.