Category: MDX, SSAS (Analysis Service), Cube

Sync Azure Analysis Service Replicas with Azure Automation

July 5, 2021

Azure Analysis Service Replicas Azure Analysis Service lets you scale out your queries very easily through replicas. Replicas are nothing but multiple copies of your Azure Analysis Service (AAS) database. In critical times, queries can run in replicas and reduce the load on Query Processing Unit (QPU). You can read more about Analysis Service replicas … More Sync Azure Analysis Service Replicas with Azure Automation

Azure Analysis Service – current context is read-only

June 27, 2021

Ever faced the following error while changing any properties in Azure Analysis Service or while processing a Analysis Service Database: “ This operation is not allowed by The Azure Analysis Service product as the current context is read-only” All you have to do is to add “:rw” at the end of your server name URL … More Azure Analysis Service – current context is read-only

Ceiling in MDX

June 19, 2013

It’s just a simple solution to find ceiling of a measure with help of some string operation. We have a measure called [Measures].[Marks]. Now, we define a calculated measure like below: CREATE MEMBER CURRENTCUBE.[Measures].[CeilingMarks] AS CInt(Mid(cstr([Measures].[Marks]),0,InStr(cstr([Measures].[Marks]),“.”)-1))+1, VISIBLE = 1 ; It works like this; Let’s say our number is 315.5, first it finds the decimal … More Ceiling in MDX

Pre order, Post order, Level order (BFS) traversing with MDX

December 17, 2012

A tree or hierarchy of my sample cube is being shown below: BFS or Level Order Traversing: with member [Measures].[x] as [Dim Category].[Parent].currentmember.level.ordinal select order(([Dim Category].[Parent].members),[Measures].[x],BASC) on 1 , [Measures].[x] on 0 from SalesCube And, here goes the output: Pre-Order traversing: By default, traversing in MDX is always in pre-order. select [Dim Category].[Parent].members on 1 … More Pre order, Post order, Level order (BFS) traversing with MDX

Leaf members do not sum up to parent – Part 2 (parent child hierarchy)

October 11, 2012

In part 1, we saw user defined hierarchies. For parent child hierarchy, we can use Unary Operator Column. Let’s take the same example from Part1. We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this: Id    CategoryItems        Parent 1        Phone                            4 2         Tab                               4 … More Leaf members do not sum up to parent – Part 2 (parent child hierarchy)

Leaf members do not sum up to parent – Part 1 (user defined hierarchies)

October 9, 2012

We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this: Id    SubCategory                 Category 1    Phone                              Hardware 2    Tab                                  Hardware 3    Web Applications         Software The key attribute is Id and attribute relationship is     Id->Sub Category->Category There is one user-defined hierarchy called “Hierarchy” in DimCategory. In BIDS … More Leaf members do not sum up to parent – Part 1 (user defined hierarchies)

Maximum value of a measure from an MDX result set

July 27, 2012

Below is an MDX Query which returns the sales of different product categories: select {[Dim Category].[Category].[Category]} on 1, {[Measures].[Measure Value]} on 0 from [MY CUBE] result set returned: We can see that the maximum value is 42092.83. Using max and axis function, we can show the maximum value as a seperate measure. So, we add … More Maximum value of a measure from an MDX result set

Understanding crossjoin, set, tuples with help of SetToStr function

July 26, 2012

MDX cross join function returns the cross product of two sets. For example, we have two sets A = {1,2} and B = {x,y}. After crossjoin, we get, A X B = {(1,x),(1,y),(2,x),(2,y)}. (1,x), (1,y) are individual tuples. The resultant set consists of these tuples. Now, let’s see the below mdx query which uses crossjoin … More Understanding crossjoin, set, tuples with help of SetToStr function