I’m very excited to announce that I’ve been selected to present a session entitled Designing a Data Warehouse from the Ground Up on October 10, 2015 in Orlando, Florida at Seminole State College of Florida! I’ll be presenting this session for the first time along side Mitchell Pearson (b|t). This is going to be an amazing event with tons of amazing, FREE training for everyone including SQL server newbies on up to those who have been in the profession for years. Continue reading Learn Designing a Data Warehouse from the Ground Up at SQL Saturday 442 Orlando, FL
Category Archives: SSAS
What’s New in SQL Server Analysis Services 2016?
There’s a load of new features that are included in the release of SQL Server Analysis Services 2016 CTP2. I’m pretty excited about these changes and while these changes have been public for a while now, I’d like to share my thoughts. I’ll say that these features are included in the SSAS 2016 CTP2 release. This release does not include all the enhancements to SSAS 2016 and these enhancements are subject to change. You can read about the enhancements here. Continue reading What’s New in SQL Server Analysis Services 2016?
Three SSAS Best Practices to Follow
There is a lot of work that goes into performance tuning a SQL Server Analysis Services solution for a client. And even though there may be many steps involved in identifying performance and management issues with a large SSAS solution, there are a few things that we can quickly check and implement to give us quick wins for improving the performance of our cube. In this post, I’d like to quickly point out three best practices that we can follow to improve performance and create a more positive experience for our users. These are not meant to be the top three best practices to follow, but rather three (among many) very important best practices you should follow. I believe following these three best practices will make a difference in your solution.
Create Hierarchies with Attribute Relationships
In my opinion, creating natural hierarchies are the single most beneficial thing an SSAS developer can do to improve the performance and usability of a cube. There are several reasons correctly defined user hierarchies are beneficial, but here are a couple of the top reasons.
Increased Query Performance
Creating attribute relationships between attributes that are included in a user defined hierarchy improve the performance of queries using these attributes for a couple different reasons. In this case, we’ll look at the Calendar hierarchy in the Adventure Works Date dimension. The Calendar hierarchy has five levels with the Calendar Year attribute at the top level and the Date attribute at the bottom level of the hierarchy. We can also see the following attribute relationships created to give SSAS an understanding of how the members of these attributes relate to one another. Once created, these relationships give SSAS the ability to understand that a given date member is related to a single month member, a given month member relates to a single quarter, and so on and so forth.
This also means that during processing, special indexes are created that map the relationships between each member of each level in the hierarchy. This means that before a query is written, SSAS also knows to which month, quarter, semester, and year each date aggregates. These indexes are only created if you correctly define the attribute relationships between the attributes in the hierarchy.
Improved User Experience
User defined hierarchies also improve the user’s experience with the dimension because the hierarchy provides a very natural way for the user to navigate through the data in the dimension. A user defined hierarchy does two things for a user: 1) Using the hierarchy obviously presents the data to the user in an understandable and clear way. And 2) the hierarchy organizes the data based on your business requirements. This means that the user doesn’t have to think about how should these attributes be organized or labeled because you’ve done that for them. The user can focus on reading their reports, understanding the data, and making business decisions.
Partition Measure Groups to Separate Volatile and Static Data
Measure groups that are larger than (about) one million records should be separated into multiple partitions. There are several advantages to separating larger measure groups into multiple partitions.
Increased Processing Performance
Partitions in a measure group are processed in parallel. This means that a measure group containing three years worth of data separated into one partition for each month will process faster than a measure group with all three years worth of data in a single partition. Your processing strategy for each measure group may vary depending on the amount of data. For instance, a large telecommunication company may collect hundreds of millions of records per day, dictating a more complex and granular partitioning strategy.
Also, we should consider which partitions contain data that is changing vs. data that is now static. If our businesses will continually log transaction in the current month, we can partition our data by month effectively separating our volatile data from the static data. This means we only need to process one months worth of data in our cube to pick up the latest changes instead of having to processing the whole measure group.
Increased Query Performance
By partitioning our data at the month level, certain queries will also perform better. For instance, imagine a user queries our measure group searching for last month’s sales figures. Because we have partitioned our measure group with each month being loaded into a separate partition, this means that the entire measure group does not have to be scanned. Only the partition containing the data for the month in question needs to be queried.
Create Aggregations for Large Measure Groups
You can think of Aggregations as indexes for SSAS. Aggregations are used to help SSAS find the answer to a user’s query faster. Aggregations are typically used for measure groups that are large and take a considerable amount to query.
Improved Query Performance
Aggregations contain the data of a measure group at a summary level typically higher than the lowest level of data included in the measure group. Aggregations are populated during the processing phase of the partition. You can think of Aggregations as exactly the same as the lowest level of the measure group just at a summary level. This means that when SSAS receives a query that can be answered using an Aggregation, SSAS does not have to spend the additional time required to retrieve the measure group data from the lowest level and roll up the data to the requested level because the Aggregation design already contains the data at the desired level.
Beware Too Many Aggregations
Because Aggregations are built during the processing phase, this means that for every aggregation you define more time is required to build the aggregations. This also means that it is especially important to only build useful aggregations that are necessary to improve performance. By creating aggregations that are not often used, you can degrade query performance and increase processing time with little to no benefit. Like indexes on a SQL Server table, too many aggregations or the wrong aggregations can actually hurt performance, so make sure you test, test, and then test to ensure your aggregations are helping your query performance.
I hope you have found this information useful. By following these three best practices, I really believe you’ll see a generous improvement in the performance of your SSAS cubes.
If you found this information helpful, I’d love to hear from you! Please leave me a comment and let me know what you think. Do you have any ideas on something I left out or should have included? Please let me know! Thank you!
I’m Speaking at SQL Saturday #391 in Jacksonville, FL May 9th
It’s that time of year again! SQL Saturday in Jacksonville, FL is upon us once again and I’m excited to be presenting a session titled, “Welcome to SSAS Tabular Models.” SQL Saturday #391 will be held on May 9th, 2015 in Jacksonville, Florida at the University of North Florida and is a totally free training event for SQL Server professionals and anyone wanting to learn about SQL Server!
My session is called, “Welcome to SSAS Tabular Models”, and will function as an introductory session on developing an SSAS Tabular model the right way. In this session I’ll discuss how to decide if building a Tabular model is the right choice, how to build a Tabular model completely from scratch, best practices you should follow, and things to avoid. If you’re new to Tabular Modeling or wanting to learn best practices, this will be a great session for you.
To get registered for SQL Saturday #391 completely for free, head over to SQLSaturday.com and click Register Now!
Navigating Hierarchies with MDX webinar recording is now available!
Thanks to everyone that attended my webinar on Navigating Hierarchies with MDX! We looked at a bunch of different ways we can navigate up, down, and side to side in our hierarchies in order to do some really neat things with calculations. If you would like to view the recording, you can do that here completely for free! Also, if you’d like to view my PowerPoint slide deck and scripts I used for the webinar, you can download those from here. Just download the Navigating Hierarchies with MDX .zip file. 🙂
Now on to the questions!
Q: Is there anything like storeproc / pre stroed mdx query in SSAS which can be called in .net application.
A: There is a concept of SSAS stored procedures, which you can read more about here: http://msdn.microsoft.com/en-us/library/ms176113.aspx and here: http://asstoredprocedures.codeplex.com/
Q: How would ParallelPeriod handle a leap year?
A: ParallelPeriod returns the member at the same position in the specified period. So if the 29th day of February does not exist in the previous year, then no value will be returned:
Q: Can you use PeriodsToDate() on a ‘custom’ period like an Academic Term?
A: PeriodsToDate can be used on any user defined hierarchy.
Q: Setting date property for MTD, QTD seems straightforward. What about WTD (week-to-date)? It seems it might take some careful work each year.
A: If you have a Week attribute in your Date dimension, that should be set to Week, as well. That’s all that is required. 🙂 
Q: What is the name of the zoom tool and highlighter used, just curious.
A: Zoomit. It’s free, too! I get asked that question every time I present.
Thanks for all the great questions, everyone! If you have any further questions, please feel free to post it here or to send me a tweet!
Navigating Hierarchies With MDX Webinar 9/23/14 11 am EST
Heads up, everyone! I’ve got a free training event coming up on Tuesday, September 23 at 11 a.m. EST! Next Tuesday I’ll be discussing Navigating Hierarchies With MDX.
One of the great strengths of SQL Server Analysis Services is the ability to create hierarchies by defining relationships between attribute fields. In this webinar, we’ll take a look at how we can fully leverage our SSAS hierarchies in our MDX queries and calculated measures using navigational functions such as PARENT, DESCENDANTS, PARALLELPERIOD, and many more! So if you’ve ever wanted to learn more about some of the cool, navigational functions built into MDX or have had questions about creating really powerful MDX calculations, this webinar is for you!
But the best part is it’s all free training! Just get signed up and register for the event, completely free of charge. I look forward to seeing you there!
The MDX Rank Function
If you read my blog regularly, you may have seen this post from last week where a reader asked how to create set to get the top 5 members while lumping every member outside of the top 5 into an “Others” member. As a follow up question, another reader asked how we can rank the members being displayed.
We can create a numeric ranking of the Customers by leveraging the MDX Rank function. The Rank function Continue reading The MDX Rank Function
Creating Calculated Members with MDX
This week I got an email from a reader named Brad asking a specific question regarding MDX. Here is his question:
I need an MDX query to return me the top 5 values and then default all others to an “other” group and ranks based on the resulting measure value. For instance, if we are talking about a measure by month for a full year, I need the output to look like this:
Dim Measure Rank
Nov $500 1
Mar $400 2
Feb $300 3
Jan $200 4
Dec $100 5
Other $350 6Any input will be most appreciated!
Thanks,
Brad
I thought this was a good question which is why I thought I’d take the time to blog this out. In this example I’ll be using the Adventure Works cube and the Adventure Works Customer dimension.
The first step is to determine what are our Top 5 Customers. To do this, we can use a simple TopCount function to build a set of the Top 5 Customers.
with dynamic set [Top 5 Customers] as
Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)
Once we have our Top 5 Customers, then we can easily determine everyone else. To create our custom “Others” group, we need to create a Calculated Member (Custom Member). To create the Calculated Member, you need to specify the dimension, attribute hierarchy, and member name for your custom member (ie [Customer].[Customer].[Others]).
member [Customer].[Customer].[Others] as
Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)
To create our Others custom member, we need to use the Except function to basically specify that we want our custom member to include all our customers except the Top 5 Customers set we previously created. Also, don’t forget to wrap the set in the Aggregate function.
Lastly, we create our set that will actually be used in our query. This set will include our first set containing our Top 5 Customers as well as our Others custom member. We can use the Top 5 Customers set with our custom member because all members in the set are from the same Customers hierarchy.
dynamic set [Top 5 & Others] as
{
[Top 5 Customers],
[Customer].[Customer].[Others]
}
Now all that’s left to do is to use our set in a query. Here’s the entire query so you can see the total syntax.
with dynamic set [Top 5 Customers] as
Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)
member [Customer].[Customer].[Others] as
Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)
dynamic set [Top 5 & Others] as
{
[Top 5 Customers],
[Customer].[Customer].[Others]
}
Select [Measures].[Internet Sales Amount] on 0,
[Top 5 & Others] on 1
From [Adventure Works]
And here’s the results in the SSMS query results windows.
I hope that was pretty straight forward and not too confusing. If you thought this was helpful leave me a comment and let me know! And if you have any questions or comments, feel free to leave a comment.
Thanks again, Brad, for the great question!
Check out my next blog post to see how to return the numeric rank of the customers!
How to Pass Multiple Values from an SSAS Report Drill Through Action to an SSRS Report
These past couple weeks I faced a client requirement to create an SSRS Report Action in an SSAS cube. This requirement dictated that a user should be able to browse a cube in an Excel pivot table and be able to drill through to an SSRS report (which used SQL Server as the data source) and view data at a monthly level. The twist was that if the user drilled from a year or quarter level, the appropriate months would be selected by default. I thought the solution I came up with was useful so here you have it, as recreated using Adventure Works 🙂 . I won’t walk through the step-by-step of creating a Report Action in SSAS, but I will hit the high points of overcoming this requirement.
I created the report action in the Cube Designer in SSDT and called it SSRS Sales Report. Because I want the users to be able to click on a measure, I set the Target type to “Cells” and the Target object to “All cells”.
But I really only want the users to see the action if they right-click the Reseller Sales Amount measure, so I added the following conditional logic so the action will only appear for the Reseller Sales Amount.
If you wanted to limit the action to appear beneath more than one measure but not all measures, just use “or” and specify other measures as seen in the example below. Also, in my specific requirement, the action should only work for a certain time frame (2007 forward in this example) so I added the logic you see after the “and”. 
(Measures.CurrentMember is [Measures].[Reseller Sales Amount]
or Measures.CurrentMember is [Measures].[Internet Sales Amount])
and
COUNT(
Intersect(
{[Date].[Calendar Year].&[2007]:NULL},
[Date].[Calendar Year].CURRENTMEMBER)
) > 0
Next, I added in the Report Server URL and SSRS report link. In my case, the report server was SharePoint Integrated. 
Now this is where things start to get interesting. I need to dynamically pass the year and month values to the SSRS report. What values to pass from SSAS really depends on the parameters in the SSRS report. If the SSRS report is using SSAS as a data source, then you may be able to use the unique name of the attribute members. In my case, the SSRS report used a regular ole SQL Server database as the data source so I needed to format the parameter values appropriately. The Parameter Name values should reflect the name of the parameters in the SSRS report. Also, if you’re using an SSRS report in SharePoint, don’t forget to include “rp:” as a prefix on the parameter name. For example, if your parameter is named “Year”, the parameter name you would enter into the action would be “rp:Year”. Again, this only applies if your SSRS report server is SharePoint integrated.
In the table below, you’ll noticed I’m using the Right function to return the right four characters of the name of the Calendar Year member. This is because in SSAS the Calendar Year caption appears as “CY 2007” for 2007 for example. In the SSRS report, however, the parameter value would be “2007”. The Month parameter is a little trickier. Because I want the user to be able to view multiple months at a time depending on what level of the Date Hierarchy they click on, I used the Generate function with the Descendants function to create a comma delimited string of the member values at the month level.
| Parameter Name | Parameter Value |
| rp:Year | RIGHT([Date].[Calendar Year].CURRENTMEMBER.NAME, 4) |
| rp:Month |
GENERATE( DESCENDANTS([Date].[Calendar].CURRENTMEMBER, LEFT( 3), “&rp:Month=”) |
So if you’re wondering what the Generate function is doing here, take a look at the following query and the query results and that should clear it up for you.
with member [Measures].[Months param] as
GENERATE(
DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Month]),
LEFT([Date].[Calendar].CURRENTMEMBER.NAME,3),
"&rp:Month=")
Select [Measures].[Months param] on 0,
DESCENDANTS(
[Date].[Calendar].MEMBERS,
[Date].[Calendar].[Month],
SELF_AND_BEFORE
) on 1
From [Adventure Works]
In the results you can see that one of the neat things the Generate function can do is to create a delimited list. The third argument of the Generate function is my delimiter and in this case, I want it to be “&rp:Month=” so I can specify multiple values to pass to the parameter in the SSRS report. So if the user right-clicks a quarter, 3 months will be passed to the report. If the user clicks a semester, 6 months should be passed.
And here’s a screen cap of the Parameters in the Cube Design in SSDT in case you were wondering.
The last step is to make the caption for the action dynamic. Set the Caption is MDX to “True” and then you can do neat things with the action like use the CurrentMember’s Name property in the caption of the action.
Here’s the complete screen grab of the Action if you need it:
So that’s fantastic and all but what does it look like on the end user’s end? Here I have a pivot table with my date hierarchy and a couple measures. If I right-click the Reseller Sales Amount for Q4 2007, select Additional Actions, you’ll see the caption for my brand new action: View SSRS Sales Report for Q4 CY 2007.
And the report correctly picks up the values from the Action:
Keep in mind that this action works for any level of the Date Hierarchy down to the date level. Very slick!
If you look at the URL, you can see that the parameters being passed to the report through the URL query string:
http://spsqlbi/sites/BI%20Center%20Test/_layouts/15/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/BI%20Center%20Test/Reporting%20Services/Adventure%20Works%20Reports/SalesReport.rdl&rp:Year=2007&rp:Month=Oct&rp:Month=Nov&rp:Month=Dec&rs:Command=Render&rs:Renderer=HTML5
Chances are that you’ll have to struggle through this a few times like I did. So here’s a couple tips to help you troubleshoot:
1. If the action does not appear where you expect it to in the pivot table, then there could be a syntax issue with your MDX in the Action definition in SSDT. Double-check your MDX.
2. If the action takes you to the report correctly, but the values in the parameters aren’t selected, then you probably aren’t passing the correct values to the report for the parameter. For example, if the Month parameter in the report uses Month Number as the values (ie 1 = Jan, 2 = Feb, etc) but you pass “January” from the Action, then the value will not be automatically selected.
3. Lastly, you are subject to the URL query string character limit in place by the browser. For example, IE has a limit of 2,083 characters last I checked, so if you’re trying to pass a selection of 500 customer values to an SSRS report you will probably run into some problems.
I hope that some of you out there found this useful. If you did, share it with a friend or leave me a comment and let me know! Thanks!
How To Display 0’s Instead of NULL in your SSAS Cube & MDX Query Results
I’ve done a decent amount of SSAS and MDX query development over my career and one of the requirements that continually pops up is to display zeros instead of NULL when browsing the cube. Take the following query as an example:
Select {[Date].[Calendar Year].Children} on 0, NON EMPTY {[Geography].[State-Province].members} on 1 From [Adventure Works DW] Where [Measures].[Reseller Sales Amount]
And here are the results:
There are a couple ways display 0’s instead of NULL. The first way is to create logic in your MDX script that uses a CASE statement of an IIF expression that manually checks for empty cube space using the ISEMPTY function, similar to the following example:
SCOPE ([Measures].[Reseller Sales Amount]); THIS=IIF(ISEMPTY([Measures].[Reseller Sales Amount]),0,[Measures].[Reseller Sales Amount]); END SCOPE;
The problem with this method is that any empty cube space will be populated with 0’s, potentially exploding your results! Rows that should not be displayed are now displayed! In the image below, I’ve highlighted a row that does not have any results that should not be displayed. Queries that usually only return a handful of results could now potentially display thousands or millions of rows! Yikes! And that’s something we definitely want to avoid.
There is another way that we can fill the NULL’s with a 0 or any other value we choose: Through the FormatString property. The FormatString property accepts four arguments separated by a semicolon (;). The first argument is how positive value should be formatted, the second is how negative values should be formatted, the third argument is how 0 values should be formatted, and the fourth argument is how NULL should be formatted. The fourth argument is the one we are interested in!
Here is an example of how I am formatting the Reseller Sales Amount: “#,##0.00;-#,##0.00;;0”.
By configuring the FormatString property this way, we are no longer filling the empty space in the cube with 0’s, rather we are configuring the measure to only display a 0 should a NULL value appear in the results. Check it out.
No more NULL values. If you’d rather display a non-numeric value, you can do this: “#,##0.00;-#,##0.00;;Zero”
If you thought that was helpful or enjoyed reading my blog, leave a comment. Feel free to post any questions, as well!