SSRS/MDX Parameter values based on a (filtered) dimension (which actually relates to records in the facts)

[Dim Period] dimension values:
For this demo, I have a dimension called [Dim Period] in my SSAS cube which looks like:

ssas-cube-dim-period-id-values


All values
So here’s how you could diplays all values (children) of a certain dimension:
ssas-cube-dim-period-id-values-all-code

ssas-cube-dim-period-id-values-all-results



Filtered by NOW()/FORMAT

([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")


ssas-cube-dim-period-id-values-filtered-by-now-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Filtered by HAVING

HAVING NOT ISEMPTY([Measures].[--Value--])


ssas-cube-dim-period-id-values-filtered-by-having-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Code
Here’s the MDX script I used:

--All members in the [Dim Period] dimension:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM [CUBE]

--Filtered based on the current date:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT
--Here's a trick that will work on date/time values:
([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)

--But for other non time related dimensional members, you need to a different method:

WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC)
HAVING NOT ISEMPTY([Measures].[BS Balance])
ON ROWS
FROM [CUBE]








SSAS Ragged Hierarchy (BottomUp approach)

Hi there reader!
There are a couple of methods on how to populate so called ‘ragged hierarchies’ in SSAS, a few tricks:

Naturalizing a Parent-Child Hierarchy:
http://martinmason.wordpress.com/2012/02/26/the-ssas-financial-cubepart-1ragged-hierarchies/

TopDown and hide middle levels:
http://asmdx.blogspot.nl/2008/04/parent-child-hierarchy-to-level-base.html

BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility):
http://cwebbbi.wordpress.com/2009/11/11/ragged-hierarchies-hidememberif-and-mdx-compatibility/

I really liked the BottomUp approach that Chris Webb proposed. This is how a TopDown-hierarchy looks like:

SSAS-Ragged-Hierarchy-TopDown-Approach

And here’s the BottomUp-hierarchy we’re going to be using for this demonstration:

SSAS-Ragged-Hierarchy-BottomUp-Approach

First things first, here’s the cube’s structure:

SSAS-Ragged-Hierarchy-Cube

Define the relationships (nothing fancy here):
SSAS-Ragged-Hierarchy-Cube-Structure-Relationships

Here’s where the magic happens! Set the HideMemberIf-property to ‘OnlyChildWithParentName‘, so basically hide the child when it has the same name as its parent.

SSAS-Ragged-Hierarchy-Cube-Structure

And here’s the result of that hard work:

Dimension-Browse-BottomUp

Excel-Browse-BottomUp

Important note
This method works for Excel without setting the “MDX Compatibility = 2” requirement, but in SSRS and your MDX query it will only return those records which have no hidden levels. Strange issue, I suggest you make another seperate hierarchy for reporting that has the HideMemberIf property set to ‘Never’ for all levels.








MDX Cheat Sheet

I’ve Googled for MDX Cheat Sheets, found a couple, but never exactly what I was looking for.
So I made my own, hope this one explains things a lot easier for you (and also as a reference for myself) 😉

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

Dim Period Hierarchy

Code:

MDX Cheat Sheet

Results:

MDX Cheat Sheet results

Here’s a more graphical explanation on how things work:
SSAS Dimension hierarchies levels

Download it!
SSAS MDX Cheat Sheet


Here’s another MDX Cheat Sheet I posted after, all about Calculated Measures:
https://clinthuijbers.wordpress.com/2014/08/15/mdx-cheat-sheet-calculated-measures/








Yahoo! 24TB SSAS Cube – Big Data Case Study + Slides

Thanks Denny Glee for sharing this.

It’s old news, but still it’s very cool 🙂
I always thought that Yahoo! had a 7TB SSAS Cube, but it has been growing of course.
As of dec 2012, it reached 24TB!!

Microsoft Case Study:
Yahoo! Improves Campaign Effectiveness, Boosts Ad Revenue with Big Data Solution

Some key numbers from this case study include:

24TB Analysis Services MOLAP cube
2PB source data of a 14PB Hadoop cluster
700M unique users, 47% of the global online population
3.5B ad impressions/day

Yahoo-24TB-Cube-Platform-Architecture

Yahoo-24TB-Cube-Querying-the-Platform-Architecture

More slides can be found here.

Source:
http://dennyglee.com/2012/12/08/yahoo-24tb-ssas-big-data-case-study-slides/