Category Archives: SSRS

Setting up the Power BI Report Server Preview

Last week on May 17th the Power BI team announced the release of the Power BI Report Server preview. Power BI Report Server includes the ability to host Power BI reports, along with traditional SQL Server Reporting Services content, within the boundaries of your organization’s firewall.

I put together the following video that Continue reading Setting up the Power BI Report Server Preview

Advertisements

Power BI Architecture Diagram v2 is Now Available

Update: My Power BI architecture diagram v3 is now available! Head over to here get the latest version!

I finally finished putting together version 2 of my Power BI architecture diagram. I previously put together an architecture diagram but as fast as Power BI is changing, v1 of the diagram was quickly obsolete. I always wanted to come back and add more to the diagram to make it more complete but now that SQL Server 2016 is generally available and enhancements have been made to Power BI to facilitate integration with Excel and SSRS, it made sense to do another diagram.

The reason I originally created this diagram was to use as a tool during conversations with my customers. But I’ve also used the diagram in other presentations and found it beneficial while teaching about how Power BI works. I wanted to make this resource available to others for their benefit. So here’s v2.

Click here to download a .zip file with the .pdf and .png files.

Power BI architecture v2

I’ve included a few new things to make the diagram up to date with the latest changes to Power BI, SQL Server Reporting Services, and Excel.
This document includes components to illustrate:

  • Publishing Power BI desktop files and Excel workbooks to Reporting Services
  • Publishing Excel workbooks to a Power BI site
  • Browsing Power BI data models with Excel
  • Publishing Reporting Services content to the Report Server
  • Browsing Reporting Services mobile reports and KPIs using Power BI apps

Feel free to download this content and use it in your presentations, discussions, and for your own learning. I hope you find it useful and if you do, share it with a friend.

Feedback?

Do you have any feedback on this diagram or anything you’d like to see me change? Leave a comment or let me know! Thanks!

What’s New in Reporting Services 2016

Today I had the pleasure of presenting to the Higher Ed Data Warehousing and BI Group. We discussed the long list of new features and enhancements included with SQL Server Reporting Services 2016. The webinar has been recording and I’ve uploaded it to YouTube for your viewing pleasure.

If you’d like to download my PowerPoint slide deck, you can do that here.

And you can download my portal brand package here.

Continue reading What’s New in Reporting Services 2016

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!

BI xPress Report Deployment Tool

imageIf you’ve followed my blog for a while, you’ve probably seen me blog about BI xPress because its such an awesome tool. I use BI xPress on every gig I go on, even if the client has not purchased a license. There’s been several great enhancements since the earlier versions of BI xPress, but one of the more useful is the new Report Deployment Tool.

The practical need the Report Deployment Tool meets for the SSRS developer or administrator is the need to easily and quickly deploy SSRS reports from one environment to the next. With its drag and drop interface, I can move SSRS reports from the QA environment up to Production (or any other environment within my domain) simply by dragging and dropping the report.

After connecting to both SSRS servers, I can easily compare which reports exist in one environment versus the next. I can also bi-directionally move reports that are ready for deployment into Production.

I would highly recommend this tool for anyone who finds themselves facing challenging SSRS report deployment scenarios or for anyone who just wants to very easily move reports across servers in a split second. To learn more about BI xPress and the Report Deployment Tool, head over here.

SSRS Performance Monitoring With BI xPress 3.5

Earlier this month the latest version of BI xPress was released. There are several new features in this latest release of BI xPress, but one of the coolest is the SSRS Performance Monitoring.

The BI xPress SSRS Performance Monitor has four performance monitoring panels.

  • Average Run Time

    The Average Run Time panel displays the average run times of your report. This is a great place to figure out which reports are taking the longest to execute. This is the place to start if you’re trying to figure out why a report is taking a long time to run. There are three different very helpful metrics displayed in the panel:

    • Avg. Data Retrieval Time: This number shows how long it took to retrieve the data from the source system. If the Avg. Data Retrieval time is the majority of the report run time, take a look at the source query and find ways to optimize it.
    • Avg. Processing Time: This is the time the SSRS server takes to calculate and process the retrieved source data. Any formulas or expressions in the report are calculated during this time.
    • Avg. Rendering Time: This is the amount of time it takes to paint the graphics, charts, text, etc.
  • Longest Running Reports on Average


    This panel provides you a chart that can be read quickly and easily to allow you to see if a report’s execution is taking too long, which could negatively effect the performance of the entire SSRS server.

  • Frequent Reports


    The Frequent Reports panel displays the reports that are most frequently run. If your reports are being executed more than expected, this could lead to scalability issues. Tracking the number of executions is a good way for you to begin to determine if its time to scale up or out.

  • Active Users

    The Active Users panel displays report executions by users.

This newest feature is one I’ve been waiting for a long time since I first saw it during the development phase. To read more about BI xPress and the numerous other features included, head over to PragmaticWorks.com and check it out.

SQL Rally 2012 Recap In Yo’ Face!

This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business intelligence developer, like myself, hoping to take your skills to the next level, there was definitely some amazing material to take advantage of.

I focused heavily on the business intelligence space and spent most of my time attending those sessions. I learned some great tips, increased my knowledge in several areas, and also networked a little with some really bright and great all-around people.

So What Sessions Did I Attend And What Did I Learn?

Performance Tuning SSAS Processing with John Welch

The first sessions I attended was Performance Tuning SSAS Processing given by John Welch (blog | twitter). There’s no doubt about, John knows his stuff. I learned a ton from his session. Here’s a few of the tips I picked up from John’s great sessions:

    • Instead of bringing entire tables into your DSV, use views. Views will allow you to tweak the SQL and use query hints to improve the performance of the Select statement.
    • When Analysis Services processes data, it does so one buffer at a time. If AS detects duplicate rows in a buffer, it will aggregate the data. Because AS aggregates duplicate records, you can save on space and increase performance if you order the data in your Select statement in your partitions.
    • Tune SSAS processing in 3 steps:
      • Dimension Processing
      • Partition ProcessData
      • Partition ProcessIndexes
    • Set the AttributeHierarchyOptimize property to false for attributes that are not used in queries often or if the attribute has a high cardinality and a near 1:1 relationship with the key attribute.

The Report Part Library with Jessica Moss

The next session I attended was The Report Part Library with Jessica Moss (blog | twitter). Jessica is one lady who is an amazing speaker and an SSRS guru without a doubt. Jessica’s wonderful sense of humor and bubbly personality made the session enjoyable and exciting. We covered how to create report parts, share those report parts across multiple reports, and then update those same report parts. Jessica also covered how to organize and manage a large Report Part Library. It was really great to finally meet Jessica in person.

Stop! Consolidate and Listen! with Jorge Segarra

After lunch, I attend Jorge Segarra’s (blog | twitter) session called, “Stop! Consolidate and Listen!”. Jorge covered some of the different methods of consolidation and dug into virtualization. Between Jorge’s sad rap skills and Patrick Leblanc’s even weaker beat boxing, I managed to pick up a few pointers.

Some of the Pro’s to virtualization:

    • Great isolation!
    • Flexibility
      • Snapshotting virtual machines (so if something goes horribly wrong, you can just roll back to the last snapshot)
      • Creating and dropping virtual machines is easy as pie
      • VM’s can be migrated across servers without having to be shut down!

Some of the Gotcha’s:

    • Behind the scenes resources could actually be shared, so make sure you understand the systems that your VM’s are running on.

Also, check out the free MAP toolkit from Microsoft. Jorge gave a great intro into the tool and how to use it to determine which machines are prime candidates for consolidation and virtualization.

Data Mining with Devin Knight & Adam Jorgensen

The last session I attended was Data Mining with Devin Knight (blog | twitter) and Adam Jorgensen (blog | twitter). Being far less than a master of data mining, I really looked forward to this session.

Data mining does three things:

  • Explores the data
  • Identifies patterns in the data
  • Performs predictions based on those identified patterns

There are several algorithms available for us to use:

  • Decision tree
    • Allows us to see how someone could come to a decision?
  • Clustering
    • Measures the distances between the cluster of points on a graph and an outlier
    • This method is often used for fraud detection and data validation
  • Time Series
    • Predict 3 units (days, months, years, etc) into the future
    • Based on historical values
  • Sequence Clustering
    • Used to determine somebodies next step
    • Where will a person click next on a web site?
  • Association Rules
    • Market basket analysis
    • Amazon uses this: People who bought this also bought this.
  • Naïve Bayes
    • A data mining algorithm that is literally hundreds of years old
    • A classification algorithm that could be used to determine the differences between customer who buy a product and customers who do not buy a product.
  • Neural Networks
    • Typically used with FICO scores
    • Takes multiple factors into account but can’t really point to one or two things to tell you how it came to a decision

Zero To Cube: Fast Track To Analytics with Adam Jorgensen & Dustin Ryan

I also had the wonderful pleasure of presenting on SSAS with Adam Jorgensen (blog | twitter). Our session was titled, “Zero To Cube: Fast Track To Analytics”. We had volunteers (some were volunteered by others) drive through creating a cube from the ground up. Within an hour and 15 minutes, we created a connection to our data source, created the data source view, made some enhancements to the data source view, created our cube, built a couple hierarchies and fleshed out the dimensions, and even built a report in Excel with a pivot table and a chart. We had a great, very participative audience and we had a blast! Thank you so much to all those that came to our session!

All in all, this year’s SQL Rally was an amazing event. I learned a lot of new things, made some great friends, and had a great time. If you weren’t able to make the event, you missed out big time. Definitely put next years SQL Rally on your calendar and make attending that event a priority. You won’t be sorry.