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
Download the latest version of the Power BI Architecture Diagram here!
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.
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:
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.
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!
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.
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]
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!
If 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.
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.
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:
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.
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.
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.
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.
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:
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.
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:
Some of the Gotcha’s:
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.
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:
There are several algorithms available for us to use:
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.
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
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.
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.
If the organizations you’ve worked with are anything like the ones I’ve had the pleasure of working with, then they probably had or have thousands of SSRS reports spread out all over the place. And back around the time a majority of companies were gearing up to migrate to SQL 2008 from 2005, getting a grasp on the number and complexity of these reports was quite a challenge.
This is just the scenario where BI Documenter really shines. Besides being able to document your SQL databases, SSIS packages, and SSAS cube, BI Documenter can also document your SSRS reports and shared data sources, all the way down to the XML behind the scenes in case your reports are lost.
Let’s walk through creating the documentation we need to understand how many and how complex the reports are that are being considered for migration. For the sake of this example, just pretend my SSRS 2008 reports are actually 2005 ;).
Open BI Documenter, click Add a New Solution. Give the solution a name and description and then click Create Solution.
After clicking Next, click Add Reporting Services Server on the next screen.
The reports you wish to document can exist either on the SSRS server or on a SharePoint Server. In my case, my reports are on my local SSRS instance so I’ll select Native for my Server Mode. Key in the server and any credentials. Click the hyperlink at the bottom of the screen to verify BI Documenter can reach the SSRS server. Click Next.
Give a name and description of the Report Server and click Finish.
Now that we’ve add the Report Server to our documentation solution, we need to create a snapshot. On the Filter tab, check the boxes next to Reports and RS Data Sources.
Click Next a couple times. All we need to do now is finalize the documentation. You can output the documentation in HTML format or in a .CHM file. Select the output destination.
On the last tab, Reporting Objects, make sure the box is checked to document the Report Definition. This is a great tool if you reports are lost. With a little work, the documentation of the Report Definition can be used to recreate the reports. Click Next. Once the process to create the documentation is finished, click Finish.
Now we have a precise and detailed document outlining all the reports living on our SSRS server. I can view the .rdl’s for my reports, the parameters, data sources, and data sets. I can even view the data set queries.
The great thing about this feature of BI Documenter is that I can easily document all my SSRS reports as long as I know the Report Server. Head here to download the free trial.
