Category Archives: SSAS

Ten MDX Calculations For Your Cube (part 2)

February 28, 2012 Dustin Ryan 4 Comments

In this post I’ll conclude what I began in an earlier blog post covering ten of the most common cube calculations implemented for some of my clients. You can read the first blog post in this 2 part series here, which covers the first five calculations (in no particular order). As before, these calculations will be written against the Adventure Works 2008 R2 sample AS project.

5. Percent of a Parent

This is very common calculation that I’ve seen used countless times to determine what percentage of a measurement makes up the total measurement for a larger body. In this example, I’m calculating what percent of Internet Sales for a product make up the total Internet Sales for the product’s Sub Category.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percent of Parent]
AS
Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Internet Sales Amount]
)
Then Null

// Test for current coordinate being on the (All) member.
When [Product].[Product Categories].CurrentMember.Level Is
[Product].[Product Categories].[(All)]
Then 1

Else ( [Product].[Product Categories].CurrentMember,
[Measures].[Internet Sales Amount] )
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Internet Sales Amount] )

End
,
FORMAT_STRING = “Percent”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

4. Rolling Average Calculation

Another common calculation used is one that calculates an average over the period of time, such as the monthly average of the past 12 months.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Monthly Average Over Year]
AS

Avg
(
[Date].[Month Name].CurrentMember.Lag(11) :
[Date].[Month Name].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

3. Percentage Growth From Previous Period

If you’re wanting to measure the percentage of growth of a measure from a previous period, this is the calculation to use. A couple things to keep in mind: This calculation is hierarchy specific and is currently set up to measure the percentage of growth from the previous calendar year.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Growth From Previous Period]
AS Case
// Test for current coordinate being on (All) member.
When [Date].[Calendar].CurrentMember.Level Is
[Date].[Calendar].[(All)]

Then “NA”

// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
Then Null

Else (
( [Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount])
–
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
/
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
End,
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

2. Period to Date Average

This calculation is very similar to #4 up above but instead of giving you the average Internet Sales for the past 12 months, the calculation will return the average Internet Sales for the current period. For example, if you view this calculation at the day level, you will see the average daily Internet Sales for the current month.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Over Current Period]
AS

Avg
(
[Date].[Calendar].CurrentMember.FirstSibling :
[Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

1. Top Products Percent of Sales

The last calculation I have to show you utilizes a named set to return to the top 10 best selling products. Many clients like to monitor the sales of their best selling products this way.

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Internet Products]
AS TopCount
(
(

[Product].[Product].Children
)
,10
,[Measures].[Internet sales amount]
), DISPLAY_FOLDER = ‘Sets’ ;

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount]),
FORMAT_STRING = “CURRENCY”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

You can also add a calculated measure to show the percentage of sales of those 10 best selling products.

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Percent of Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount])/
[Measures].[Internet Sales Amount],
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

If you have any other calculations which you think should be on this list and would love to share them, please send them my way. I’d love to see ‘em!

If you don’t consider yourself an MDX expert or are looking to eliminate some of the calculation development time, I’d highly suggest checking out BI xPress. BI xPress has a great feature built in that allows you to quickly and efficiently add calculations and named sets to your cube, all without the knowledge of MDX. Heck, most of the calculations in this blog post were written in about 10 seconds with the BI xPress Calculation Builder.

To open the BI xPress Calculation Builder, open your SSAS project in BIDS, navigate to the Calculations tab and click the BI xPress Calculation Builder icon.

This will open the MDX Calculation Builder Wizard. Select the Set template you’d like to use. I’m selecting the Top 10 Count template.

The first step is to select the attribute that you would like returned by the set. As before, I’m selecting the Product attribute of the Product dimension.

Lastly, select the measure.

And its really that easy.

To download the free trial of BI xPress, head over to PragmaticWorks.com and check out BI xPress. You won’t regret it after you see how much time it will save you with your SSAS and SSIS development.

MDX, SSAS

Using the Descendants Function

February 21, 2012 Dustin Ryan 1 Comment

The MDX function, Descendants, is a very versatile and commonly used function that returns the members at, below, or even above a specified level.

The first argument of the descendants function is a member or set expression. The second argument can either be a specific level expression or a number indicating the number of levels to travel. The following queries return the same results:

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    2) 
    on 1
From [Adventure Works]

GO

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter]
    ) 
    on 1
From [Adventure Works]

Results:

The Descendants also accepts a third optional argument: a string expression to distinguish between possible descendants to return in the results. This query uses the SELF_AND_AFTER flag to returns members at the specified level as well as members subordinate to the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    SELF_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:

And this query uses the BEFORE_AND_AFTER flag to return all members except for the members of the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    BEFORE_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:

Notice that the specified level is excluded.

Also, if I use the LEAVES flag, I can return all the leaf members between the specified member and the specified level. Alternatively, if you don’t specify a level, the members at the lowest level (the leaf members) will be returned.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    ,
    LEAVES
    ) 
    on 1
From [Adventure Works]

Results:

Check out MSDN for more information on the Descendants function.

Community, SSAS, SSIS, SSRS

SQLRally Voting Opens to the Community

February 17, 2012 Dustin Ryan

PASS SQLRally 2012 voting is now open! There’s tons of great sessions out there and many of them have been submitted by some of my good friends here at Pragmatic Works. While voting look for great sessions presented by Pragmatic Works experts like

Adam Jorgensen
Chris Albrektson
Gareth Swanepol
Dan Clark
Brad Schacht
and me, too!

Make sure you head over to SQLRally.com and cast your vote! You can vote for as many sessions as you want but you can only vote once, so make your selections wisely! Yours truly has also submitted a few sessions that are currently up for vote! Here are my sessions up for vote and their descriptions.

Zero to Cube – Fast Track to Analytics
This session has been updated for SQL Server 2012. It is consistently a top session from previous events like SQLRally and PASS Summit. Come interact with your speaker and audience like you never have before. This session is 100% demo, packed full of fun and will get you up and running on Analysis Services 2008R2 and 2012 right away!

SSIS For Your Data Warehouse
The ability of SQL Server Integration Services to easily bring together multiple data sources makes it the perfect ETL tool for loading your data warehouse. Loading your data warehouse quickly and efficiently, however, is not always easy. In this session, we’ll explore some advanced techniques for loading your data warehouse and help you overcome some of the learning curve associated with SSIS. The topics discussed will include handling late arriving facts, loading slowly changing dimensions efficiently, and more.

SSIS Unleashed: Expressions and Variables Edition
One of the most powerful aspects of SSIS is the ability to create dynamic and powerful packages utilizing package variables and the SSIS expression language. Unfortunately, the SSIS expression language is also one of the more difficult features to learn regarding SSIS. In this session we will walk through the syntax of the SSIS expression language, common uses for SSIS expressions, as well as some more advanced ways to fully realize the power of SSIS.

Tackling the SSRS Expression Language
The SSRS expression language is one of the most powerful pieces when it comes to creating dynamic reports. Unfortunately, for reporting developers the SSRS expression language is also one of the more difficult features to use regarding SSRS. During this session we will walk through the syntax of the SSRS expression language, how and where to use the expressions, as well as some advanced ways to truly reveal the true power of your reports.

Now that you’re informed, go cast your vote!

Pragmatic Works, SQL Server, SSAS, SSIS, SSRS

BI Documenter 4.2 Released

February 8, 2012 Dustin Ryan

I’ve been meaning to blog this for a while but since I’ve been on the road for the past month I’ve been pretty busy. This past January, BI Documenter version 4.2 was released by Pragmatic Works. With it comes some pretty cool upgrades and some new functionality.

One of the biggest improvements was the upgrade to the SQL Server Instance Snapshot functionality. It is now possible to include in your documentation Backup Devices, SQL Agent Jobs, and Server Configurations. Personally, I’ve been waiting for the ability to document SQL Agent Jobs so I’m very excited about this release. There’s also many other improvements to the SQL Server Instance Snapshot functionality, so head over to PragmaticWorks.com to check out the other improvements.

Theres also a couple new enhancements that are really nice. Now you have the option to output your documentation in MS Help 2 format. Also, BI Documenter can now document Analysis Services Mining Structures.

With the ability to document SQL Servers, SSIS packages, SSRS reports, and SSAS cubes, its certainly the most encompassing SQL documentation tool out there. If you’re searching for a one stop documentation tool for your SQL Server environment, definitely check out BI Documenter.

MDX, Pragmatic Works, SSAS

Creating Named Sets In Your Cube

November 26, 2011 Dustin Ryan

Named sets are simply MDX expressions defined with an alias that return a set of members. If you find yourself writing complex MDX expressions to return a set of members often or if you have a commonly used expression, consider creating a named set. This will prevent you from having to duplicate your work.

To create a named set, open your SSAS project and head to the Calculations tabs. Find the icon with the curly brackets and click it.

Give your named set a name. For this example, I’m using the Adventure Works 2008 R2 cube and I’m creating a named set to return the top ten products with the highest Internet Sales Amount.

The MDX to return the top ten products with the highest Internet Sales Amount is very simple. We will use the TopCount function, which accepts three arguments.

The first argument is the dimension attribute we wish to return. The second argument is how many members will be returned in the set, and the last argument is the measure we would like to use to rank the members returned.

If I wanted to create a named set to return the Bottom 10 Products with the lowest Internet Sales Amount, I could write the same expression seen below except in place of the TopCount function I would use the BottomCount function.

When we create our named set, we can specify it as a Dynamic Named Set or a Static Named Set. A Dynamic Named Set respects the context of the subcube and the where clause and is evaluated at the time the query is executed. A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in your where clause.

Here is the calculation script for the named set:

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Products]
AS
    TopCount
    (
    [Product].[Product].Members,
    10,
    [Measures].[Internet Sales Amount]
    )
     ;

The next time we process our cube, our named set will be available for use in our calculations and reports.

To use our named set in an MDX query, all I have to do is use the Named Set’s alias, [Top 10 Products].

Named Sets are powerful and very useful, but they can sometimes take a long time to write, especially if the expression is complex or if you are new to MDX. That’s where BI xpress steps in. BI xPress is a powerful tool used to speed up and augment SSAS and SSIS development. BI xPress also recently won the Gold Editors Choice Award from SQL Server Magazine.

To open the BI xPress Calculation Builder, open your SSAS project in BIDS, navigate to the Calculations tab and click the BI xPress Calculation Builder icon.

This will open the MDX Calculation Builder Wizard. Select the calculation or named set you would like to build. Scroll to the bottom to find the Sets templates. I’m selecting the Top 10 Count template.

The first step is to select the attribute that you would like returned by the set. As before, I’m selecting the Product attribute of the Product dimension.

Then select the measure that you want to use to rank the selected attribute. I’m selecting the Internet Sales Amount measure.

After I click next and give my calculation a name, the named set has been added to my calculation script in my cube.

MDX, Pragmatic Works, SSAS

Ten MDX Calculations For Your Cube

November 16, 2011 Dustin Ryan 8 Comments

I get lots of questions about how to implement various types of calculations in a cube so I figured I’d cover some of the more commonly used calculations I run into on a regular basis. All of these example calculations I’ll be providing were written against the Adventure Works 2008 R2 example cube but these should still work against later versions of the AW cube.

10. YTD, QTD, and MTD Calculations

These kinds of calculations are pretty common and I see these in a lot of cubes. They’re pretty easy to wire up since there are only a couple simple MDX functions necessary to make this work.

CREATE
MEMBER CurrentCube.[Measures].[YTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING = “Currency”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

Let’s start with the PeriodsToDate function. The PeriodsToDate function is going to return all the sibling members at the specified level up to the current member. The outer function, the Aggregate function, calculates the value based on the aggregation type specified in the cube for the measure. In our case, the Internet Sales Amount aggregation type is Sum, so the Aggregate function could be exchanged for the Sum function.

If I wanted to calculate the Month to Date for Internet Sales, I would simply exchange the reference for the Calendar Year level of the Calendar hierarchy with the Month level, as seen here:

CREATE
MEMBER CurrentCube.[Measures].[MTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Month]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING =
Currency
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

9. Period over Period Growth

This is also a pretty common calculation I find and implement for my clients. It’s also pretty straight forward. In this example, we’re comparing this year’s Internet Sales Amount to the previous year’s Internet Sales Amount.

Create Member CurrentCube.[Measures].[Yearly Growth Internet Sales Amount]

As
([Date].[Calendar Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Calendar Year].PrevMember,[Measures].[Internet Sales Amount]),

FORMAT_STRING = “Currency”,
VISIBLE = 1 , DISPLAY_FOLDER = ’10 MDX Calculations’ , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

The function here to pay attention to is the PrevMember function. The PrevMember function returns the previous member at a given level based on the supplied member. For example, if the supplied member was 2011, the previous member would be 2010.

Create Member CurrentCube.[Measures].[Monthly Growth Internet Sales Amount]

As
([Date].[Month of Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Month of Year].PrevMember,[Measures].[Internet Sales Amount]),

FORMAT_STRING = “Currency”,
VISIBLE = 1 , DISPLAY_FOLDER = ’10 MDX Calculations’ , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

Similar to our last calculation, if we wish to calculate the growth from month to month, we should reference the Month attribute of our Date dimension.

8. Profit Margin Calculation

Another popular calculation for obvious reasons is the calculation for profit margin. Basically all you need to understand for this calculation is the logic of a Case statement. We use the Case statement to check for a zero value in the denominator.

CREATE
MEMBER CurrentCube.[Measures].[Internet Profit Margin] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
([Measures].[Internet Sales Amount]
–
[Measures].[Internet Total Product Cost])
/
[Measures].[Internet Sales Amount]
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

7. Percent of Total

More often then not, my clients want to be able to calculate the percent of a total amount for a hierarchy. In this example, I’m once again leveraging the Case statement.

CREATE
MEMBER CurrentCube.[Measures].[Percent of Internet Product Sales] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
(
[Product].[Product Categories]
,[Measures].[Internet Sales Amount]
)
/
(
[Product].[Product Categories].[All]
,[Measures].[Internet Sales Amount]
)
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

To adapt this calculation to your cube, just replace [Product].[Product Categories] with your dimension and hierarchy.

6. Count Leaf Members of a Hierarchy

I’ve seen this kind of calculation used a lot in combinations with other measures and/or calculation. This calculation counts the Leaves, which are products, of the Product Categories hierarchy.

CREATE
MEMBER CurrentCube.[Measures].[Product Count] AS
Count
(
Descendants
(
[Product].[Product Categories].CurrentMember,
,LEAVES
)
)
,FORMAT_STRING = “#,##0.00;-#,##0.00”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

There’s not much to this calculation. First we use the Descendants function with the Leaves function as the third argument to get all the Leaf members of the Product Categories hierarchy. Then we use the Count function to count those members.

While these calculations are not extremely complex, MDX can be a bit overwhelming. A very helpful tool that I’ve found useful, even as someone experienced with MDX, is the MDX Calculation Builder included with BI xPress. Adding calculations, like those seen above, is very easy and only take a few click with the wizard.

To open the MDX Calculation Builder, click the icon seen on the Calculations tab of your Cube Designer in BIDS.

After you click the Calculation Builder icon, the Calculation Builder wizard will open. The first thing you’ll need to do is select a calculation. At these step, we can also choose to create a named set, which the wizard will help us do.

After selecting the calculation, you’ll be taken through a couple more steps, such as selecting the Measure you wish to perform the calculation with or specifying which attribute to use as the Year attribute for the calculation.

You’ll also have the opportunity to apply conditional formatting to your measure values, which is a nice touch the end users like to see.

Finally, we’ll give our measure a name, select the measure group we’d like the measure to be a part of, select the formatting, and preview the calculation that was written by the tool.

Then we’ll click Finish. The calculation has been added to our calculation script within our cube.

BI xPress has made adding calculations to your cube simply and fast and I take advantage of the tool anytime I’m implementing calculations into my client’s cube. Head to PragmaticWorks.com to check out BI xPress and to check out some of the great training videos hosted there covering the in’s and out’s of MDX.

Stay tuned for my next blog post where we will cover the remaining 5 MDX calculations to add to your cube!

MDX, SSAS

MDX VBA Functions

October 17, 2011 Dustin Ryan

The Visual Basic for Applications library functions are a great way to extend the functionality of your MDX statements, allowing you to do some very handy stuff. Irina Gorbach has created a nice little document outlining the VBA functions available, so definitely check that out. There’s an important performance difference between the VBA functions and native MDX functions, which Irina points out.

One of the VBA functions I’ve been asked about by a couple people is the IsNumeric function. The IsNumeric functions returns a Boolean value indicating whether an expression can be evaluated as a numeric value. Here is an example (using the AW example cube) of how one might utilize IsNumeric:

With Member [Measures].[LastOrderYear] as

IsNumeric([Reseller].[Reseller].CurrentMember.Properties("Last Order Year"))

Select [Measures].[LastOrderYear] on 0,

[Reseller].[Reseller].children on 1

From [Adventure Works]

And here is what the results look like:

So check out the Irina’s document on the SSAS VBA functions. I think you’ll find it very informative and useful.

MDX, SSAS, SSRS

Showing an SSAS Hierarchy as Delimited String

October 11, 2011 Dustin Ryan

I recently encountered a requirement for a client to display a list of all the individual stores of a ragged hierarchy (the leaf members) on an SSRS report with a bunch of different measures. But they also wanted to be able to quickly see the the ancestor members of each level above each store quickly by using a tool tip to display the different ancestors.

Turns out there are a couple nifty, little MDX functions that can allow us to do this very easily. The Ancestors and Generate functions make this possible. Here an example I put together with the Adventure Works 2008 R2 cube so you can play along.

WITH MEMBER [Measures].[Management Structure] AS
            GENERATE(
            EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All]),
            [Employee].[Employees].CURRENTMEMBER.Name, " > ")

Select {[Measures].[Management Structure],
[Measures].[Reseller Sales Amount],
[Measures].[Reseller Order Quantity]} on 0,

NON EMPTY(DESCENDANTS([Employee].[Employees],10,LEAVES)) ON 1

From [Adventure Works]

Where [Employee].[Title].&[Sales Representative]

Pay attention to the calculate member [Measures].[Management Structure]. The way the Generate function works is that it takes one set and applies that set to each Member in another set. In this case, our first set is the ancestor from each level in our hierarchy of the current member, excluding the All member.

So this our first set:

EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All])

Our second set is the Name property of the CurrentMember of the Employee hierarchy being displayed:

[Employee].[Employees].CURRENTMEMBER.Name

The Generate function actually also excepts a third argument: A delimiter. We can specify a delimiter. In my example, I use “ > “ to make the string a bit easier to read. If we execute the query we can see the results of our calculated member.

Pretty cool, huh? So now that we have each employees management hierarchy in sweet, little string, we can easily display this in a report as a tool tip.

I added the tool tip to the cell in my report’s tablix that contains the employee name. Here’s the expression I used:

=REPLACE(Fields!Management_Structure.Value,">",VBCRLF)

This expression replaces the “>” with a carriage return, line feed. Here’s what our tool tip looks like:

Using this method, we can easily display the managers for each employee in a tooltip without having to waste any real estate with additional columns.

I hope somebody finds this useful. I’m out!

SSAS

Defining Member Properties for an Attribute in SSAS 2008

August 11, 2011 Dustin Ryan 2 Comments

I was working with a client the other day designing a couple cubes. These cubes used two pretty large dimensions, each containing many attributes. In order to increase the performance of these large dimensions, I worked with the client to identify attributes that the end users would not necessarily need to slice and dice with, but would still be useful to view the attributes. Once those attributes were identified, I displayed them as what was known as members properties. So lets walk through how we can accomplish this.

Here’s the Customer dimension in the Adventure Works 2008 R2. As you can see, there’s a whole lot of attributes, many of which probably would provide little value by allowing the end users to slice with.

Attributes with high cardinality are prime candidates to become a member property. A good example would be AddressLine1.

The first thing we need to do is set the AttributeHierarchyEnabled property to False for the AddressLine1 attribute.

This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

To allow the users to view this attribute as a member property, we need to create an attribute relationship between AddressLine1 and the attribute we would like to view AddressLine1 from. By default, all attributes are related to the key column in the dimension. But I would like our end users to be able to view AddressLine1 when the hover over the Name field of our customer.

To accomplish this, go to the Attribute Relationships tab in the dimension designer. In my Customer dimension, I’ve got a calculated column called FullName but you could use FirstName for this example. Right-click the FirstName and click New Attribute Relationship. Then in the Create Attribute Relationship window, find Address Line1 in the Related Attribute drop down list. Click OK.

After I processed the cube and connected to the AS database in Excel, I can now view the member properties for the Name attribute. Right-click the Name of a customer in your pivot table, click Show Properties in Tooltips, and click Show All Properties or select the individual attribute you’d like to view as a tooltip.

Now when I hover over the name of a customer, I see the properties of that customer. In the Adventure Works cube, you can see how many other attributes have been created as member properties of the Name attribute.

Alternatively, you can choose to display the member properties as a column in the pivot table by right-clicking the name of a customer, clicking show properties in report, and then select the field to display in the report.

This is a great way to increase performance of large, unwieldy dimensions and still give the end user their wildest dreams Winking smile .

MDX, SSAS

Daily Average Orders Calculation at Any Level of Date Hierarchy

August 5, 2011 Dustin Ryan 1 Comment

Today I was helping a fellow BIDN.com user come up with a calculation to figure out the average numbers of orders on day at any level of a date hierarchy. So I figured I’d post the calculation because 1) maybe it will be helpful to you and 2) I’m going to forget this calculation so I’ll at least be able to use this post as a reference.

WITH 
    //Gets a Summation of the Orders in the Fiscal period
    MEMBER [Measures].[Sum] AS 
    
        SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])
    //Counts the days in the Fiscal period
    MEMBER [Measures].[Count] AS 
    
        COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])})
    //Divides the sum of orders by the count of days to calculate the average daily 
    //orders in that fiscal period
    MEMBER [Measures].[AvgDailyIntOrdCount] AS  
    
        SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])/
        COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])})
        
SELECT     {[Measures].[Sum],[Measures].[Count],[Measures].[AvgDailyIntOrdCount]} on 0,    
{[Date].[Fiscal].Members} ON ROWS 
FROM
      [Adventure Works]

In the calculation, I use the SUM function to get an aggregations of the Internet Order Count for any level of the Fiscal Date hierarchy. I also used the Count function with the Descendants function to get a count of days beneath a member of any level of the Fiscal Date hierarchy.

It seems to work pretty good, but if you have an alternative way to calculate this, please post it in the comments section!

Data and Analytics with Dustin Ryan

Category Archives: SSAS

Ten MDX Calculations For Your Cube (part 2)

5. Percent of a Parent

4. Rolling Average Calculation

3. Percentage Growth From Previous Period

2. Period to Date Average

1. Top Products Percent of Sales

Like this:

Using the Descendants Function

Like this:

SQLRally Voting Opens to the Community

Like this:

BI Documenter 4.2 Released

Like this:

Creating Named Sets In Your Cube

Like this:

Ten MDX Calculations For Your Cube

10. YTD, QTD, and MTD Calculations

9. Period over Period Growth

8. Profit Margin Calculation

7. Percent of Total

6. Count Leaf Members of a Hierarchy

Like this:

MDX VBA Functions

Like this:

Showing an SSAS Hierarchy as Delimited String

Like this:

Defining Member Properties for an Attribute in SSAS 2008

Like this:

Daily Average Orders Calculation at Any Level of Date Hierarchy

Like this:

Dustin Ryan is a Data Specialist interested in Azure, SQL Server, and Power BI.

5. Percent of a Parent

4. Rolling Average Calculation

3. Percentage Growth From Previous Period

2. Period to Date Average

1. Top Products Percent of Sales

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

10. YTD, QTD, and MTD Calculations

9. Period over Period Growth

8. Profit Margin Calculation

7. Percent of Total

6. Count Leaf Members of a Hierarchy

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Dustin Ryan is a Data Specialist interested in Azure, SQL Server, and Power BI.