Top Posts & Pages
 MAT (Moving Annual Total), YTD (Year to date) calculation using BottomCount, Tail, Properties function in MDX
 Finding common maximum in Power BI
 Dynamic Column Mapping in SSIS : Part 1
 Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#
 Slowly Changing Dimension in SSAS Cube

Recent Posts
Categories
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
Slowly Changing Dimension in SSAS Cube
This is a simple example of SCD (Type2) 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
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
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: PreOrder … Continue reading
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
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
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
Understanding crossjoin, set, tuples with help of SetToStr function
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 … Continue reading
How to run MDX/Create local cubes from SSIS
Just drag an Execute SQL Task and double click to open SQL Task Editor. Under the SQL Statement section, you will find the slot for Connection. Click on New connection and you will see the Configure OleDb connection Manager window. … Continue reading
Market Share Calculation in MDX based on AXIS
Below is the MDX which will calculate market share at runtime/dynamically. It always reads data from the parent axis. iif ( Axis(1)(0)(Axis(1)(0).Count – 1).Dimension.CurrentMember.Parent is null, null, ( [measures].[x] / ( [measures].[x],Axis(1)(0)(Axis(1)(0).Count 1).Dimension.CurrentMember.Parent ) ) … Continue reading
Posted in MDX, SSAS (Analysis Service), Cube
2 Comments