Category Archives: MDX, SSAS (Analysis Service), Cube

Common Latest Period in SSAS Cube

Recently I came across a situation where data was being delivered at different times for different countries and client wanted to see data for latest common month in Region/Continent level reports. Data arrive on monthly basis, but it arrives at different … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , , , , | Leave a comment

Slowly Changing Dimension in SSAS Cube

  This is a simple example of SCD (Type-2) in OLAP cube.  We have a Dimension table for Employee and their Departments. This dimension is an SCD. One employee worked in different department over the course of time. EmpID is … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , , , , | Leave a comment

Ceiling in MDX

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 ; … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , | Leave a comment

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

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 … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Leave a comment

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

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 … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , | Leave a comment

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

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 … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , | Leave a comment

Maximum value of a measure from an MDX result set

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 … Continue reading

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , | 2 Comments