{"id":615,"date":"2015-03-11T03:26:35","date_gmt":"2015-03-11T12:26:35","guid":{"rendered":"http:\/\/blog.box.kr\/?p=615"},"modified":"2015-03-11T03:26:35","modified_gmt":"2015-03-11T12:26:35","slug":"%ed%8e%8c-learn-to-write-custom-mdx-query-first-time","status":"publish","type":"post","link":"https:\/\/blog.box.kr\/?p=615","title":{"rendered":"[\ud38c] Learn to Write Custom MDX Query First Time"},"content":{"rendered":"<p><a href=\"http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time\">http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time<\/a><\/p>\n<p>&nbsp;<\/p>\n<p><b><a href=\"http:\/\/www.codeproject.com\/script\/Membership\/View.aspx?mid=10225257\">Mubin M. Shaikh<\/a><\/b>,\u00a018 Jan 2014\u00a0<a href=\"http:\/\/www.codeproject.com\/info\/cpol10.aspx\">CPOL<\/a><\/p>\n<table>\n<tbody>\n<tr>\n<td><\/td>\n<td>\u00a0\u00a0\u00a04.60\u00a0(34\u00a0votes)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<table width=\"100%\">\n<tbody>\n<tr>\n<td><\/td>\n<td>\n<table>\n<tbody>\n<tr>\n<td>Rate:<\/td>\n<td><a href=\"http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time#\">vote 1<\/a><a href=\"http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time#\">vote 2<\/a><a href=\"http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time#\">vote 3<\/a><a href=\"http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time#\">vote 4<\/a><a href=\"http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time#\">vote 5<\/a><\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Learn Custom MDX Query Step by Step<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li><b><a href=\"http:\/\/www.codeproject.com\/KB\/database\/710387\/Practice_MDX_Queries.zip\">Download MDX queries &#8211; 3.1 KB<\/a><\/b><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2 style=\"font-weight: 200;\">Introduction<\/h2>\n<p>In this article, we will go through some basic concepts and terminologies used while writing MDX Queries on your OLAP Cube, We will also look into Why-What and How of MDX Query.<\/p>\n<p>While we Google, we can find some good articles on this topic, but I did not find an article with all stuff which I am looking for in one when I was searching as a beginner in this direction. So I have taken this small step to write an article and share with you all, so you can Learn Custom MDX with ease and enjoy.<\/p>\n<h3>Multi Dimensional Expression (MDX)<\/h3>\n<p>MDX Query Language is used to retrieve information stored in OLAP Cube created in various technologies like Microsoft SQL Server Analysis Services(SSAS), Oracle, Tera data, etc. Key difference between MDX and T-SQL is MDX Query build Multidimensional View of the data, where T-SQL builds Relational View. SQL Query designed to handle only two dimension while processing tabular data. While MDX Can process more dimensions in Query.<\/p>\n<p>MDX is also used to write expressions for custom calculation in Power Pivot and for creation of Calculated member in OLAP Cube.<\/p>\n<p>What do you mean by dimensions in Query? In general, we can say entities with related member details using which you have planned to study &amp; analyze Data in OLAP Cube.<\/p>\n<h3>Introduction to Basic Concepts Used within MDX Query<\/h3>\n<p>We need to have a clear idea in our mind about the various concepts and terminologies used while working with MDX Query. Initially, I found it very confusing to understand all these when I was new, but I don&#8217;t want you to be stuck on this all, so let us begin.<\/p>\n<h4><strong>Cube<\/strong><\/h4>\n<p>OLAP Cube is the basic unit of storage for Multidimensional data, on which we can do analysis on stored data and study the various patterns. You can take further idea on OLAP cube creation using this article\u00a0<strong><a href=\"http:\/\/www.codeproject.com\/Articles\/658912\/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv\">Create First OLAP Cube in SSAS<\/a><\/strong>.<\/p>\n<h4><strong>Dimensions<\/strong><\/h4>\n<p>The primary functions of dimensions are to provide Filtering, Grouping and Labeling on your data. Dimension tables contain textual descriptions about the subjects of the business. Dimensions in general we can say are the Master entities with related member attributes using which we can study data stored in OLAP Cube Quickly and effectively.<\/p>\n<h4><strong>Measure<\/strong>\u00a0 <strong>&amp; Measure Groups<\/strong><\/h4>\n<p>Metrics value stored in your Fact Tables is called Measure. Measures are used to analyze performance of the Business. Measure usually contains numeric data, which can be aggregated against usage of associated dimensions. Measure Group holds collection of related Measures.<\/p>\n<p>To learn about Data Warehouse quickly refer to the article\u00a0<a href=\"http:\/\/www.codeproject.com\/Articles\/652108\/Create-First-Data-WareHouse\"><strong>Create First Data Warehouse<\/strong><\/a>.<\/p>\n<p>Take a look at the image given below which represents terminologies discussed above.<\/p>\n<p>OLAP Database is container of Cubes. It is important to identify Cube Name before we start writing our query. Then after we need to select Measure from appropriate Measure Group and use related dimensions.<\/p>\n<h3><strong>Introduction to Level, Member,<\/strong><strong>\u00a0Hierarchy\u00a0<\/strong><\/h3>\n<p>Let us take a look at brief descriptions of frequent terms used in MDX query.<\/p>\n<h4><strong>Level<\/strong><\/h4>\n<p>Generally Attributes under Dimension are considered as levels, they are also called as\u00a0<strong>Attribute Hierarchy<\/strong>.<\/p>\n<p>Let&#8217;s take an example of Date Dimension in this we have various levels like Quarter of the Year, Semester of the Year, Week of the Year, Calendar Year, etc.<\/p>\n<h4><strong>Members<\/strong><\/h4>\n<p>Key component of the MDX query is member. Each Level contains one or more members.<\/p>\n<p>e.g.\u00a0<strong>Calendar Quarter of Year<\/strong>\u00a0contains various members like CY Q1, CY Q2, CY Q3, CY Q4 .<\/p>\n<h4><strong>User Defined Hierarchy<\/strong><\/h4>\n<p>We usually create this type of hierarchy while designing OLAP Cube as per their relations. You can refer Date Hierarchy shown in the figure shown below.<\/p>\n<p>This hierarchy also contains various levels, by\u00a0<strong>default Level 0<\/strong>\u00a0is reserved for\u00a0<strong>[ALL]\u00a0<\/strong>.<\/p>\n<h2 style=\"font-weight: 200;\">Background<\/h2>\n<p>Please refer to my previous articles if you are more interested to know about Data Warehouse and OLAP Cube Creation using Microsoft Business Intelligence.<\/p>\n<p>Here we are going to work with Microsoft SQL Server 2008 R2 (Standard, Enterprise edition) .<\/p>\n<h2 style=\"font-weight: 200;\">Using the Code<\/h2>\n<h3><strong>Let us make Our Test Environment Ready\u00a0<\/strong><\/h3>\n<ol>\n<li>Here you need to download\u00a0<strong>Adventure Works Data Warehouse<\/strong>from CodePlex Site.<\/li>\n<\/ol>\n<ul>\n<li><strong><a href=\"http:\/\/msftdbprodsamples.codeplex.com\/releases\/view\/105902\">Download Adventure Works Data Warehouse 2008 R2<\/a><\/strong><\/li>\n<\/ul>\n<ol start=\"2\">\n<li>Also download\u00a0<strong>Analysis Services Solution<\/strong>created using this AdventureWorksDW2008 R2 from CodePlex Site.<\/li>\n<\/ol>\n<ul>\n<li><strong><a href=\"http:\/\/msftdbprodsamples.codeplex.com\/releases\/view\/88252\">Download Adventure Works 2008 R2 Analysis Services Project\u00a0<\/a><\/strong><\/li>\n<\/ul>\n<p>3.Check in Services.msc that your SQL Server Analysis services were up and running.<\/p>\n<ol start=\"4\">\n<li>Configure Connection string in above SSAS Solution and Deploy your Cube.<\/li>\n<li>Now Open Microsoft SQL Server Management Studio (<strong>SSMS<\/strong>) and connect\u00a0<strong>Analysis Services<\/strong>using\u00a0<strong>Windows Authentication<\/strong>.<\/li>\n<\/ol>\n<p>Select\u00a0<strong>Server type<\/strong>: Analysis Services&#8211;&gt;Specify your SQL\u00a0<strong>Server name<\/strong>: e.g. mubin-pcfairy or localhost &#8211;&gt;Click:\u00a0<strong>Connect<\/strong><\/p>\n<ol start=\"6\">\n<li>\n<p>After Successfully Connecting to your SQL Server Analysis Server, you can view your OLAP Cube Deployed, just do the drill down by clicking on + button.<\/p><\/li>\n<li>Open\u00a0<strong>New MDX Query<\/strong>Editor Window<\/li>\n<\/ol>\n<p><strong>Right Click<\/strong>\u00a0on Database Name (<strong>Adventure Works DW 2008 R2<\/strong>)&#8211;&gt; Select\u00a0<strong>New Query<\/strong>\u00a0&#8211;&gt; Click\u00a0<strong>MDX\u00a0<\/strong><\/p>\n<ol start=\"8\">\n<li>\n<p>Now we are ready to start playing with MDX Query in our Query Editor Window.<\/p><\/li>\n<\/ol>\n<h3><strong>Introduction to Axis in MDX Query<\/strong><\/h3>\n<p>MDX queries can have 0, 1, 2 or up to 129 query axes in the\u00a0SELECT\u00a0statement. Each axis behaves in exactly the same way, unlike SQL where there are significant differences between how the rows and the columns of a query behave.<\/p>\n<p>Refer to the following table for Axis Numbers reserved and Alias given to them:<\/p>\n<table width=\"485\">\n<tbody>\n<tr>\n<td width=\"242\"><strong>Axis Number\u00a0<\/strong><\/td>\n<td width=\"242\"><strong>Alias<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"242\"><strong>0<\/strong><\/td>\n<td width=\"242\">Columns<\/td>\n<\/tr>\n<tr>\n<td width=\"242\"><strong>1<\/strong><\/td>\n<td width=\"242\">Rows<\/td>\n<\/tr>\n<tr>\n<td width=\"242\"><strong>2<\/strong><\/td>\n<td width=\"242\">Pages<\/td>\n<\/tr>\n<tr>\n<td width=\"242\"><strong>3<\/strong><\/td>\n<td width=\"242\">Section<\/td>\n<\/tr>\n<tr>\n<td width=\"242\"><strong>4<\/strong><\/td>\n<td width=\"242\">Chapter<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Using SQL Server Management Studio (SSMS), we can only browse values on two axis,\u00a0<strong>Columns\u00a0<\/strong>(Axis\u00a0<strong>0<\/strong>) and\u00a0<strong>Rows\u00a0<\/strong>(Axis\u00a0<strong>1<\/strong>).<\/p>\n<h2 style=\"font-weight: 200;\">Getting Started With MDX<\/h2>\n<h3><strong>1.<\/strong>\u00a0<strong>Start with Simple MDX Query\u00a0<\/strong><\/h3>\n<h4><strong>Syntax<\/strong>:<\/h4>\n<h5>Select From [Your Cube Name] ;<\/h5>\n<p>Which will give you aggregated result as shown in result pane, MDX is not Case Sensitive except member keys defined within dimension. This query will use default member defined in all the dimensions and use default measure defined by OLAP cube designer.<\/p>\n<p>You can do drag and drop of cube name, dimension members from left pane to query window instead of typing. This query is also known as no axis query.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select From [Adventure Works];<\/p>\n<h3>2. Dropping Dimensions on Axis<\/h3>\n<p>If we will not specify Axis for dimensions and measures, it may lead us to wrong result while design change take place.<\/p>\n<p><strong>Example:<\/strong><\/p>\n<p>Retrieve all customer names on Columns from Adventure Works Cube.<\/p>\n<p><strong>Syntax<\/strong><\/p>\n<h5>Select Dimension.Member on Column From [OLAPCubeName ]<br \/>\nor<br \/>\nSelect Dimension.Member on 0 From [OLAPCubeName ]<\/h5>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select [Customer].[Customer].[Customer] on 0 From [Adventure Works]; Select [Customer].[Customer].[Customer] on columns From [Adventure Works];<\/p>\n<p>As you can notice one thing here if your dimension is not associated with Measure Group, you can have same values in each result cell against every customer.<\/p>\n<p>But here we are trying to learn how we can bring Customer values on Columns, so we are not focusing on Measures right now. Let us proceed with next.<\/p>\n<h3>3. Using Both the Axis (Rows &amp; Columns)<\/h3>\n<p>You can select Dimension or Measure on any Axis.<\/p>\n<p><strong>Example:<\/strong><\/p>\n<p>Retrieve Internet Sales Amount As Per Customer. In other words, we can say show the Detail of amount spent by customers during purchase from Internet.<\/p>\n<p><strong>Syntax<\/strong><\/p>\n<h5>Select [Measure] on Columns,<br \/>\n[Dimension].[Members] on Rows From [Cube Name] ;<\/h5>\n<p><strong><em>OR<\/em><\/strong><\/p>\n<h5>Select [Measure] on Rows,<br \/>\n[Dimension].[Members] on Columns From [Cube Name] ;<\/h5>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select [Measures].[Internet Sales Amount] on Columns, [Customer].[Customer].[Customer] on Rows From [Adventure Works];<\/p>\n<p>Here, you can see Measure Value (Internet Sales Amount) is properly getting divided as per the customer.<\/p>\n<p><strong>Note<\/strong>: You can also do drag &amp; drop of Measures and Dimension members from left vertical Pane marked with number 2 to Query Designer portion number 3.<\/p>\n<h3>4. Introduction to .members, and .children in MDX Query<\/h3>\n<p><strong>.Members<\/strong><\/p>\n<p>If you will use this with hierarchy level, then it will retrieve all the values below it and also bring agreegation of that in the form of [ALL].<\/p>\n<h4><strong>Syntax<\/strong><\/h4>\n<h5>Select [Dimension].[Hierarchy].members on Columns from CubeName<br \/>\n<strong>or<\/strong><br \/>\nSelect [Dimension].[Hierarchy].[Level].members on Columns from CubeName<\/h5>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select [Measures].[Internet Average Sales Amount] on Columns, [Product].[Category].members on Rows From [Adventure Works];<\/p>\n<p><strong>.Children<\/strong><\/p>\n<p>When we want to retrieve all members values under particular level of a dimension at that time we use\u00a0<strong>.children<\/strong>,This will exclude aggregation values [ALL] in your result set.<\/p>\n<h4><strong>Syntax<\/strong><\/h4>\n<h5>Select [Dimension].[Hierarchy].[Level].children on Columns from CubeName<\/h5>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select [Measures].[Internet Average Sales Amount] on Columns, [Product].[Category].children on Rows From [Adventure Works];<\/p>\n<h3>5. Introduction to Tuple and Set<\/h3>\n<p><strong>Tuple<\/strong>:<\/p>\n<p>When we need to place more than one members of a dimension or hierarchy of that dimension on a axis at that time tuple comes into the picture, tuple is enclosed within curly bracket { }, for single tuple bracket is optional.<\/p>\n<p>We can say Tuple is used to identify particular location in the cube using your dimension members. Tuple will define slice of your cube. Tuple can contain one or more members, but it cannot have members from the same dimension.<\/p>\n<p>This is example of tuples from same date dimension members. Combination of more than one tuple will make a set.<\/p>\n<p><strong>Example:<\/strong><\/p>\n<p>View Internet Sales amount detail between year 2005 to 2007 using tuples.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]} on rows, [Measures].[Internet Sales Amount] on columns from [Adventure Works];<\/p>\n<p><strong>Set:<\/strong><\/p>\n<p>A set is an ordered collection of zero, one or more tuples. A set is most commonly used to define axis and slicer dimensions in an MDX query.<\/p>\n<p>Combination of tuple or tuples will give you set , When You want to include range at that time you can use\u00a0<strong>:<\/strong>instead of separating tuple members by comma if they are belonging to same dimension member.<\/p>\n<p><strong>Or<\/strong><\/p>\n<p><strong>Syntax<\/strong><\/p>\n<h5>{[Date].[CY 2008] : [Date].[CY 2005]} or {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]}<br \/>\nor<br \/>\n( [Date].[Calendar Year].members , [Product].[Product].members )<\/h5>\n<p><strong>Example:<\/strong><\/p>\n<p>View Internet Sales amount detail between year 2005 to 2008<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select {[Date].[CY 2008] : [Date].[CY 2005]} on rows, [Measures].[Internet Sales Amount] on columns from [Adventure Works];<\/p>\n<p>To use combination of tuples from various dimensions, we have to use Cross Join that we will learn soon.<\/p>\n<h3>6. Using CROSS JOIN<\/h3>\n<p>Cross Join Function returns cross product of one or more sets.<\/p>\n<p>Whenever we need to combine more than one member from same or different dimension at that time we can use cross join.\u00a0<strong>*<\/strong>\u00a0sign can be use to implement cross join between dimension members.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select {[Product].[Category].children <strong>*<\/strong> [Product].[Subcategory].children} on rows, [Measures].[Internet Sales Amount] on columns from [Adventure Works];<\/p>\n<p>We can also use Cross Join Function to implement cross join between different dimension members, but result will stay same if you use<strong>\u00a0*<\/strong>\u00a0or\u00a0CrossJoin\u00a0Function.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select <strong>CrossJoin<\/strong>([Product].[Category].children,[Product].[Subcategory].children) on rows, [Measures].[Internet Sales Amount] on columns from [Adventure Works];<\/p>\n<h3>7. Using Non Empty or NonEmpty<\/h3>\n<p>To element\u00a0Null\u00a0values from the result set, we can use\u00a0NonEmpty()\u00a0or Non Empty. Right now, I am not discussing difference between Non Empty and\u00a0NonEmpty\u00a0function.<\/p>\n<p>NonEmpty\u00a0function evaluated first so it will remove rows if there was no data in first measure. Let us take a look at the below example how we can remove\u00a0null\u00a0values from result set.<\/p>\n<p>Non Empty or\u00a0NonEmpty()\u00a0function can be used on any Axis.<\/p>\n<p><strong>Example\u00a0<\/strong><\/p>\n<p>Let us take a look on Cross join applied in below example, here you can see how we are retrieving multiple measures by placing them between curly bracket{ } .<\/p>\n<p>You can see\u00a0null\u00a0values in the result set while using following MDX Query.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select <strong>CrossJoin<\/strong>([Product].[Category].children,[Product].[Subcategory].children, [Product].[Product].children) on rows, {[Measures].[Internet Sales Amount],[Measures].[Internet Freight Cost]} on columns from [Adventure Works];<\/p>\n<p>Now to eliminate these\u00a0Null\u00a0Values from Result Set using Non Empty.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>Select <strong>non empty<\/strong> CrossJoin([Product].[Category].children,[Product].[Subcategory].children, [Product].[Product].children) on rows, {[Measures].[Internet Sales Amount],[Measures].[Internet Freight Cost]} on columns from [Adventure Works];<\/p>\n<h3>8. Apply Slicing using Where Clause<\/h3>\n<p>To Slice Data from cube we can use\u00a0<strong>Where\u00a0<\/strong>clause, it is similar to \u201cwhere\u201d clause we have in T-SQL.<\/p>\n<p><strong>Example\u00a0<\/strong><\/p>\n<p>I want to see detail of Internet Sales Amount for each product in the Year 2007.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select [Measures].[Internet Sales Amount] on columns, [Product].[Product].[Product].members on rows from [Adventure Works] where [Date].[Calendar Year].[CY 2007];<\/p>\n<p><strong>Example<\/strong><\/p>\n<p>If I want to see detail of Internet Sales Amount for each product in the Year 2007 and 2009.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select [Measures].[Internet Sales Amount] on columns, [Product].[Product].[Product].members on rows from [Adventure Works] where {[Date].[Calendar Year].[CY 2007],[Date].[Calendar Year].[CY 2009]};<\/p>\n<h3>9. Apply Filtering on data using Filter function<\/h3>\n<p>Filter function will also be used to apply filtering on members available in specified set as per the specified Boolean condition.<\/p>\n<h4>Syntax:<\/h4>\n<h5><strong>Filter( &lt;Set&gt;, Boolean Condition)\u00a0<\/strong><\/h5>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p><strong>select [Measures].[Internet Sales Amount] on columns, filter([Product].[Product].[Product].members , [Measures].[Internet Sales Amount]&gt;5000 ) on rows from [Adventure Works] where {[Date].[Calendar Year].[CY 2007]};<\/strong><\/p>\n<p><strong>Example:<\/strong>\u00a0If I want to retrieve only those products whose names begin with \u201cA\u201d and Internet sales amount &lt;5000.<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p><strong>select [Measures].[Internet Sales Amount] on columns, filter([Product].[Product].[Product].members , ([Measures].[Internet Sales Amount]&lt;19000 and _ left([Product].[Product].currentmember.name,1)=&#8221;A&#8221;) ) on rows from [Adventure Works] where {[Date].[Calendar Year].[CY 2007]};<\/strong><\/p>\n<h3><strong>10<\/strong>. Apply Sorting on your Data using\u00a0<strong>Order\u00a0<\/strong>Function<\/h3>\n<p>To sort your data you can use order function, using this function you can override default order specified in the cube design.<\/p>\n<p><strong>Syntax<\/strong><\/p>\n<h5>Order(&lt;set&gt;, Context, Asc | Desc|Bsc|Bdesc)<\/h5>\n<p><strong>Example:\u00a0<\/strong><\/p>\n<p>Retrieve all the products in descending order of their Internet sales amount of year 2007<\/p>\n<p>Hide\u00a0\u00a0\u00a0Copy Code<\/p>\n<p>select [Measures].[Internet Sales Amount] on columns, order([Product].[Product].[Product].members ,[Measures].[Internet Sales Amount],desc) on rows from [Adventure Works] where {[Date].[Calendar Year].[CY 2007]}<\/p>\n<p>Hope you have enjoyed this article. In this beginner article, I have tried to give Initial start up to technical newbies working in Microsoft BI and want to initiate in Learning Custom MDX.<\/p>\n<p>We will learn about different MDX Functions and their usage in my next article on Advance Custom MDX.<\/p>\n<p>I have included many sample queries with appropriate comments , Please download for further practise.<\/p>\n<p>If you find this article helpful, then please do not forget to vote for me.<\/p>\n<h2 style=\"font-weight: 200;\">Credits<\/h2>\n<p>Source code\u00a0<strong>may\u00a0<\/strong>contain reference to the following where appropriate:<\/p>\n<ul>\n<li>Microsoft Technet<\/li>\n<li><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms173781%28v=sql.120%29.aspx\">M<\/a>icrosoft MSDN<\/li>\n<\/ul>\n<h2 style=\"font-weight: 200;\">Copyright<\/h2>\n<p>By uploading my code to codeproject.com, I assume I inherit all open source terms of use, licenses and those specified by codeproject.com. However if you use this code for any purpose, I would really like to hear about it. It is my belief that by referencing the credited people, I demonstrate the ability to effectively read and re-use source code, rather than re-invent the wheel. I expect you would do the same.<\/p>\n<p>I always welcome suggestions from readers and experts for improvements.<\/p>\n<h4>Enjoy Learning MDX.<\/h4>\n<h2 style=\"font-weight: 200;\">License<\/h2>\n<p>This article, along with any associated source code and files, is licensed under\u00a0<a href=\"http:\/\/www.codeproject.com\/info\/cpol10.aspx\">The Code Project Open License (CPOL)<\/a><a href=\"http:\/\/192.168.1.200\/wordpress\/wp-content\/uploads\/2015\/03\/DownLoad-MDX-Queries.mdx_.zip\">DownLoad MDX Queries.mdx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>http:\/\/www.codeproject.com\/Articles\/710387\/Learn-to-Write-Custom-MDX-Query-First-Time &nbsp; Mubin M. Shaikh,\u00a018 Jan 2014\u00a0CPOL \u00a0\u00a0\u00a04.60\u00a0(34\u00a0votes) Rate: vote 1vote 2vote 3vote 4vote 5 Learn Custom MDX Query Step by Step &nbsp; Download MDX queries &#8211; 3.1 KB &nbsp; Introduction In this article, we will go through some basic concepts and terminologies used while writing MDX Queries on your OLAP Cube, We will also look into Why-What and How of MDX Query. While we Google, we can find some good articles on this topic, but I did not find an article with all stuff which I am looking for in one when I was searching as a beginner in this direction. So I have taken this small step to write an article and share with you all, so you can Learn Custom MDX with ease and enjoy. Multi Dimensional Expression (MDX) MDX Query Language is used to retrieve information stored in OLAP Cube created in various technologies like Microsoft SQL Server Analysis Services(SSAS), Oracle, Tera data, etc. Key difference between MDX and T-SQL is MDX Query build Multidimensional View of the data, where T-SQL builds Relational View. SQL Query designed to handle only two dimension while processing tabular data. While MDX Can process more dimensions in Query. MDX is [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[11,18,5],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-9V","jetpack-related-posts":[{"id":613,"url":"https:\/\/blog.box.kr\/?p=613","url_meta":{"origin":615,"position":0},"title":"Java\uc5d0\uc11c Cube \uc11c\ubc84 \uc811\uc18d\ud558\ub294 \ubc29\ubc95  Step1","date":"2015-03-11","format":false,"excerpt":"Java\uc5d0\uc11c MSSQL Cube\uc11c\ubc84\uc5d0 \uc811\uc18d \ud560 \uc218 \uc788\ub294 \ubc29\ubc95\uc744 \ucc3e\ub2e4 \ubcf4\ub2c8 \ud558\uae30\uc640 \uac19\uc740 Open Source Project \ubc1c\uacac http:\/\/www.olap4j.org \u00a0 http:\/\/www.jdbc4olap.org\/index.html \ud574\ub2f9 \ud504\ub85c\uc81d\ud2b8\uc758 \ubaa9\uc801\uc740 jdbc\ub4dc\ub77c\uc774\ubc84\uc640 \uac19\uc740 \ubc29\uc2dd\uc73c\ub85c MDX Query\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83.. \u00a0 \u00a0","rel":"","context":"In &quot;CUBE&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":139,"url":"https:\/\/blog.box.kr\/?p=139","url_meta":{"origin":615,"position":1},"title":"JDBC Connection Pool Timeout Errors","date":"2014-06-24","format":false,"excerpt":"If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection. If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection, and could throw the\u00a0following exception in Tomcat: Caused\u2026","rel":"","context":"In &quot;WAS, Servet Containers&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1134,"url":"https:\/\/blog.box.kr\/?p=1134","url_meta":{"origin":615,"position":2},"title":"Mysql dynamic query in stored procedure","date":"2016-07-26","format":false,"excerpt":"CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40)) BEGIN SET @t1 =CONCAT(\"SELECT * FROM \",tab_name,\" where team='\",w_team,\"'\"); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END or CREATE PROCEDURE `test1`( IN tab_name VARCHAR(40), IN w_team VARCHAR(40) ) BEGIN SET @t1 = CONCAT( 'SELECT * FROM ', tab_name, ' where team\u2026","rel":"","context":"In &quot;\uae30\uc220\uc790\ub8cc&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":896,"url":"https:\/\/blog.box.kr\/?p=896","url_meta":{"origin":615,"position":3},"title":"Working with Apache Cassandra on Mac OS X","date":"2015-06-16","format":false,"excerpt":"If you use Mac OS X as your platform for development work, then you may be interested to know how easy it is to use Apache Cassandra on the Mac. The following shows you how to download and setup Cassandra, its utilities, and also use DataStax OpsCenter, which is a\u2026","rel":"","context":"In &quot;\uae30\uc220&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1161,"url":"https:\/\/blog.box.kr\/?p=1161","url_meta":{"origin":615,"position":4},"title":"POCO \ub77c\uc774\ube0c\ub7ec\ub9ac\ub85c MYSQL  Query\uc2dc\uc5d0 &#8220;unknown field type&#8221; \uc624\ub958","date":"2016-08-31","format":false,"excerpt":"\uc6d0\uc778\uc740.. \u00a0POCO\uc5d0\uc11c\u00a0MYSQL\uc758 TIMESTAMP \ud0c0\uc785\uc744 \uc9c0\uc6d0 \ud558\uc9c0 \uc54a\uc544\uc11c \uc0dd\uae34 \ubb38\uc81c. \uc774\ub97c \ud574\uacb0 \ud558\uae30 \uc704\ud574\uc11c POCO\ub77c\uc774\ube0c\ub7ec\ub9ac \uc18c\uc2a4\ub97c \uc544\ub798\uc640 \uac19\uc774 \uc218\uc815 \ud55c \ud6c4\uc5d0 \uc7ac \ucef4\ud30c\uc77c \ud574\uc900\ub2e4. POCO \uc18c\uc2a4 \ud3f4\ub354\uc5d0\uc11c Data\/MySQL\/src\/ResultMetadata.cpp \uc758 \ub0b4\uc6a9\uc744 \uc544\ub798\uc640 \uac19\uc774 \uc218\uc815 std::size_t fieldSize(const MYSQL_FIELD& field) \/\/\/ Convert field MySQL-type and field MySQL-length to actual field length { ... case\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":379,"url":"https:\/\/blog.box.kr\/?p=379","url_meta":{"origin":615,"position":5},"title":"[\ud38c]\ube60\ub974\uac8c \ud6dd\uc5b4 \ubcf4\ub294 node.js","date":"2014-09-15","format":false,"excerpt":"http:\/\/bcho.tistory.com\/889 \ube60\ub974\uac8c \ud6dd\uc5b4 \ubcf4\ub294 node.js - #6 MongoDB \uc5f0\ub3d9 (mongo-native) \ud074\ub77c\uc6b0\ub4dc \ucef4\ud4e8\ud305 & NoSQL\/Vert.x & Node.js\u00a0|\u00a02014\/04\/03 23:49\u00a0|\u00a0Posted by\u00a0\uc870\ub300\ud611 \ube60\ub974\uac8c \ud6dd\uc5b4\ubcf4\ub294\u00a0node.js #6- mongo-native \ubaa8\ub4c8\uc744 \uc774\uc6a9\ud55c MongoDB \uc5f0\ub3d9 \uc870\ub300\ud611\u00a0(http:\/\/bcho.tistory.com Persistence\u00a0\uc5f0\ub3d9 node.js\ub294\u00a0DB\ub098\u00a0NoSQL\ub4f1\uc758 \uc5f0\ub3d9\uc744 \uc9c0\uc6d0\ud558\ub294\ub370,\u00a0\uc774 \uc5ed\uc2dc \ucca0\uc800\ud558\uac8c\u00a0non-blocking io\u00a0\ubc29\uc2dd\uc73c\ub85c \ub3d9\uc791\ud55c\ub2e4.\u00a0\uc989\u00a0db\u00a0\uc5f0\uacb0\u00a0socket\uc744 \uc5f4\uc5b4\uc11c\u00a0query\ub97c \ub358\uc838\ub193\uace0, query\u00a0\uacb0\uacfc\uac00 \uc624\uba74 \uc774\ubca4\ud2b8\ub97c \ubc1b\uc544\uc11c\u00a0callback\u00a0\ud568\uc218\ub85c \ucc98\ub9ac\ud558\ub294 \uc21c\uc11c\uc774\ub2e4. \uadf8\ub7ec\uba74 \uc5ec\uae30\uc11c\ub294 \uba87\uac00\uc9c0\u00a0persistence\u00a0\uc5f0\ub3d9 \ubc29\uc2dd\uc5d0 \ub300\ud574\uc11c \uc54c\uc544\ubcf4\ub3c4\ub85d \ud55c\ub2e4.\u2026","rel":"","context":"In &quot;\ucc38\uace0\ub97c \uc704\ud55c \uc800\uc7a5\ubb3c&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/615"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=615"}],"version-history":[{"count":0,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/615\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}