All posts by Dustin Ryan

Dustin is a Senior Business Intelligence Consultant with Pragmatic Works. You can find Dustin blogging and speaking at events like SQL Saturday, Code Camp, and SQL Rally. Follow Dustin on Twitter @SQLDusty.

Choose Your Weapon: Self Service BI Edition

With an estimated 500 million Excel users in the world, it’s no wonder that Excel is the #1 business intelligence too in many organizations around the globe. And with the release of Excel 2013, the collection of powerful and flexible data analysis tools built into Excel has only continued to grow. Microsoft is constantly adding new features and functionality to Power BI at pretty fast rate, so now is a great time to start learning about everything that MS Power BI can offer your organization.

Because Excel is just full of a slew of incredible tools, its important for us to understand the difference between the tools, when you should choose each tool, and the role each tool can fill in your organization. That’s what this blog post is all about: Choosing the right self-service tool for the job.

Power Query: Data Discovery & Transformation

What: Power Query is a totally free add-in for Excel 2010 and Excel 2013 to search for and discover data from both within and outside your organization. All you have to do is open the Online Search window, type in what you’re looking for, and viola! Power Query will return a list of data source that you can preview and import into your Excel workbook and/or Power Pivot model.
image

And the list of possible data source types that Power Query can connect to is growing every month. Aside from the traditional data sources such as SQL Server, Oracle, Access, or SAP, Power Query can also connect to a pretty ridiculous amount of non traditional data sources such as: Hadoop, Facebook, Twitter, text files, Azure, Active Directory, Microsoft Exchange, and a bunch more. Gone are the days of being forced to work with IT in order to get the data you’re looking for. That’s kind of the point of self-service BI, right? :)

image

Not only can you import the data from your selected data source, you also have the ability to clean, transform, filter, and merge the data. With Power Query, we can easily apply a series of transformations in order to perfectly shape our data for our specific reporting requirements. Do you need to pivot your data? Transpose it? Split a column by a delimiter? Rename fields? Create calculations? Upper case the first letter of each word? Change a data type? Apply formatting? Merge census data with your corporate data? Power Query can do all of that and more without writing a single line of code. That’s pretty impressive if you ask me.
image 

And once you’ve customized the data to your liking, the query can be saved for future use or shared with other business users within your organization.

Where: Like I said, Power Query is a free add-in for Excel (2010 or 2013). Once the Power Query add-in is installed, Power Query gets it’s own little ribbon in Excel. Download Power Query here.

image

When: You’ll most likely find yourself using Power Query when you need to find a specific data set that may or may not currently exist within your organization in order to gain new insights into your business in ways that you previously haven’t had the capability for. If your data needs needs to transformed and shaped for your analysis, Power Query is a perfect candidate for the job.

Power Pivot: Data Modeling & Analysis

What: Power Pivot is a data modeling and data analysis tool all rolled up into one. With Power Pivot, we have the ability to extract data from multiple sources of differing types, create data relationships between the sources, and then model and format the data to our liking using Power Pivot’s drag and drop interface. We can embed hierarchies, unique calculations, and Key Performance Indicators into our Power Pivot model for use in Pivot Tables, Pivot Charts, and Power View reports.

image

With Power Pivot, your data can be refreshed and updated from its original data sources as often if you like. And if you deploy your Power Pivot workbook to SharePoint, your Power Pivot workbook can even be refreshed on a schedule of your choosing.

And with Power Pivot’s in-memory X-Velocity storage engine, performance is pretty incredible (like, don’t-blink fast), even with large data sets! 

Where: Power Pivot is an add-in for Excel 2010 that must be downloaded and install. Power Pivot comes included with Excel 2013 but must simply be turned on. Power Pivot gets its own ribbon in Excel and has its own development window that opens for designing your Power Pivot model.

image

image 

When: You’ll be using Power Pivot when you need to relate disparate data source to each other and create a custom data model, create calculations for new metrics, create natural hierarchies for navigating through your data, or create Key Performance Indicators (KPI) for dashboard or scorecard type reporting.

Power View: Interactive & Intelligent Data Visualizations

What: Power View is an adhoc & data exploration tool that is designed with the end user in mind. You don’t need to be a data visualizations expert to be able to put together impressive and always presentable reports with Power View. With Power View, you’re always working with live data whether you’re building a Power View report in Excel 2013 or in SharePoint 2010/2013.

image

Power View gives you the capabilities to explore you data in multiple formats quite easily by switching between chart types with a single click. No coding is required with Power View making this an ideal tool for the business person that just wants to do their job with no fuss or muss.

If you’re developing your Power View reports in SharePoint, you also have the very cool ability export your Power View report to Power Point in an interactive format! That’s right! I can export my Power View report into a Power Point presentation and still interact with it! Very cool!

Where: Power View reports can be developed in Excel 2013 using an Excel table or Power Pivot model as a data source. Power View reports can also be developed in SharePoint 2010 or 2013 using a Power Pivot model, SSAS Tabular Model, or an SSAS Multidimensional Cube as a data source.

Power Map: Geographic Data Visualizations

What: Power Map is a data visualization and data exploration tool that specializes in visualizes geographic data in a 3-dimensional format (using BING maps) and even animate it across time. Power Map allows you tell a story with your data by creating a Tour comprised of multiple scenes that focus on different aspects and trends in your data in a very unique and compelling way.

image

Once you’ve created your Tour, it can be exported as a movie to be shared with your team or to be shown during a presentation as an interactive guide of your data. I guarantee your audience hasn’t seen their data like this before!

Where: Power Map is a free add-in that can be downloaded and installed in Excel 2013. Get to Power Map on the Insert ribbon in Excel 2013. Download Power Map here.

image

When: Use Power Map when you want to tell a story with your geographic data on a 3D or 2D map, animate it across time to identify trends and gain new insights, or to simply explore your data in a different way.

I hope you find this information useful and that you’ve gained a better understanding of the self-service business intelligence tools that are part of Excel. Feel free to post any questions, comments, and let me know if you’ve enjoyed this post. Thanks for reading!

Navigating Hierarchies with MDX webinar recording is now available!

image 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:
image

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. :)
image

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!

My SSAS Tabular Webinar Recording with Pragmatic Works is Now Available to Watch for Free!

Thank you to everyone that attended my webinar yesterday! I hope you enjoyed the webinar and that you learned a little about SSAS Tabular Models!

I’m pleased to announce that the recording is now available for your viewing pleasure. Just follow this link, create the free login, and you should be good to go!

Just go to this link to view my PowerPoint slide deck!

If you’re looking for more in depth training on anything SQL Server Analysis Related, I highly recommend you check out the great online, virtual training options provided by Pragmatic Works.

1. Pragmatic SSAS: Introduction to SQL Server Analysis Services
2. Pragmatic Master SSAS
3. Tabular and Power Pivot for Developers
4. Introduction to MDX

In the mean time, if you have any questions about anything covered in the webinar just post a question!

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 returns a one-based rank of the supplied member of the supplied set. So how do we update our original query to accomplish this?

Here is our original query:

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]

To create the ranking, we need to create an additional calculated member to display the rank of the customers (which you can see below highlighted in red):

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]
}

member [Measures].[Rank] as

Rank([Customer].[Customer].CURRENTMEMBER,[Top 5 & Others])


Select {[Measures].[Internet Sales Amount],[Measures].[Rank]} on 0,


[Top 5 & Others] on 1


From [Adventure Works]

The Rank function excepts two arguments. The first argument is tuple (or member in this case) that we wish to rank. The second argument would be the set we want to rank. In our case, our set is [Top 5 & Others]. Don’t forget to add your new Rank measure to be displayed on the columns! :)

And here’s our results:

image

If you found this helpful, leave me a comment and let me know! And don’t be afraid to post any questions!

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         6

Any 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.

image

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”.

image

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.

image

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”.
image

(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.
image

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,
          [Date].[Calendar].[Month]),

     LEFT(
          [Date].[Calendar].CURRENTMEMBER.NAME,

          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]

image 
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.

image

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.

image

Here’s the complete screen grab of the Action if you need it:

image

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.

image

And the report correctly picks up the values from the Action:
image

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!