Ohio Business Intelligence Road Trip 2013

imageNext month Mike Davis and myself will be making a epic road trip.  We will be flying into Columbus, OH to deliver a two day (7/16 – 7/17) workshop on advanced Reporting Services techniques.  We will then pack up and drive to Cleveland, OH for another two day (7/18-7/19) workshop on advance Integration Services techniques. 

Come join us for what should be a fun week!  I’ve already talked to a few folks that will be making the drive with us and attending both sessions.  Hope to see you there!

Columbus Master SSRS Workshop

Duration: 2 Days

Schedule: 7/16/2013 – 7/17/2013 8:30 AM – 5:00 PM

Price: $299

Location:

Microsoft Office
Polaris Center
800 Lyra Dr.
Suite 400
Columbus, OH 43240

Master Report Developers must hold unique traits to set themselves and their work apart from others. This two day class is designed to take you to the next level in your understanding of SQL Server Reporting Services (SSRS). We use a variety of lab exercises to ensure lecture sessions are backed up by hands on experience. With the hands-on labs, you will learn how to create complex reports that use Analysis Services as a data source and custom code assemblies. This course will cover all the advanced topics of Reporting Services like scaled-out deployment, .Net integration and monitoring Reporting Services usage.

Agenda

Cleveland Master SSIS Workshop

Duration: 2 Days

Schedule: 7/18/2013 – 7/19/2013 8:30 AM – 5:00 PM

Price: $299

Location:

Microsoft Office
Park Center III
6050 Oak Tree Blvd. S., Third Floor
Independence, OH 44131

During this 2 day advanced SSIS training course, you will learn how to use SSIS in your enterprise to solve common extract, transform and load (ETL) challenges. This class assumes you know the basics of SSIS and takes you from the novice level to a more effective, advanced ETL developer. Much of this class focuses on implementing common real-world SSIS patterns taught from the experts of SSIS. You’ll also learn how to performance tune SSIS. Be prepared for a very in-depth day of SSIS that gets advanced in many areas.

Agenda

SSRS – Using Lookup Function for Multiple Column Join

When SQL Server 2008 R2 was released several new Reporting Services expressions were made available that would help join multiple datasets together.  Those functions were the Lookup Function, LookupSet Function and the MultiLookup Function.  My general recommendation for these function is not to use them when you have two dataset from the same data source.  They’re really intended for connecting datasets together that are not directly related because otherwise I could just handle it in my source query.

My experience with them had been fairly academic until recently when I had a real scenario where the Lookup Function would be beneficial.  I was working with a customer that had a report requirement that needed data from DB2 and Oracle in a single tabular report.  What made this circumstance more difficult was that the join between the DB2 dataset and the Oracle dataset was not a single column but two.

Seems basic enough right?  If we were writing T-SQL we would simply do a join on multiple columns with an AND between them.  Because these are two different data sources in Reporting Services I had to get a little more creative.  If you’ve used the Lookup function in SSRS (template below) expressions then you know it can only accept one field from the source and one from the destination.  So what if you have two columns to join on?

Lookup(source_expression, destination_expression, result_expression, dataset)

The approach I took was to concatenate the two join columns together and then place the concatenated value in the Lookup function.  I also placed a literal string between the two columns to protect myself from coincidental matches.  To make the solution a little cleaner I created a calculated field on each dataset that handled the expression.  So both datasets had a calculated field I called JoinColumn and was defined similar to this:

=Fields!JoinColumn1.Value + ”||” + Fields!JoinColumn2.Value

Notice the double pipes that are used here to protect from coincidental matches.  With these calculated field created on each dataset now my Lookup function would be fairly standard.  Here’s my end result:

Lookup(Fields!JoinColumn.Value, Fields!JoinColumn.Value, Fields!ReturnColumn.Value, “DataSet2”)

I hope this helps as you find practical uses for the Lookup Function in Reporting Services.

SQL Lunch #68 – Designing Your First Power View Report

Next week I will be speaking on designing your first Power View report for SQL Lunch.  Be sure to join me for the event and as always “No fluff, just stuff”.

Add To Outlook: Add To Calendar

Date and Time: 7/18/2012 11:30:00 AM CST

Topic: #68-Designing Your First Power View Report
Power View has the capability of creating eye-popping visualizations within SharePoint 2010. It provides intuitive ad-hoc reporting that can be used by a variety of business users to make critical decisions. In this session you will learn how to design your own dazzling reports using Power View.

Choosing the Right Microsoft Reporting Technology Part 1: Report Services

As Business Intelligence has evolved over the years the number of tools we have to choose from for presenting data has advanced drastically.  With so many tools to choose from it can be rather confusing (especially when your company is just getting their feet wet in BI) to determine which tool is the right one for an organizations reporting needs.  Just within the Microsoft suite of tools (not including third-party tools) you have Reporting Services, Excel, PowerPivot, PerformancePoint and Power View.

Many companies try to marry themselves to one or two reporting tools and fit their needs into the restrictions of the tool(s) they have chosen.  The truth is not a single one of these tools can solve all reporting needs.  While each one of these tools by themselves may be able to present your data, you will find that using a combination approach will conclude in a much more well rounded and impressive reporting solution.

That’s why in this blog series I hope to demystify the decision process and educate you a little bit on each of the tools so that you can easily determine which tool would be best in different scenarios.  Like I mentioned earlier the Microsoft reporting tools we will cover will be:

  • Reporting Services
  • Excel (From the perspective of building PivotTables without PowerPivot)
  • PowerPivot
  • PerformancePoint
  • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

After discussing each of the tools each individually I’ve bring it all together by showing you how to use a decision matrix to determine which tool makes sense for your work examples.

Reporting Services

What it is

Reporting Services is traditionally used for developing static reports.  The word static here is referring to the report layout rather than the report data (obviously the data will change).  For example, I want my sales team to be able to review a line item detail of each of their sales from the prior day.  With Reporting Services I could have the sales team either log onto a web front end to view a live version of the report or I could have the report emailed to them daily.

image

The reports developed with the tool are highly customizable.  In fact just about any object of the report can be manually changed or made dynamic with the expression language it uses.

If you’re looking for a tool for creating dashboards then Reporting Services also has the capability of filling that need.  With charts, gauges, indicators, sparklines and maps you have many of the necessary tools for creating an impressive dashboard.  There are other tools that like PerformancePoint that can create more impressive interactive dashboards but Reporting Services can get the job done.

Reporting Services is also highly scalable.  Without much effort the workload for report processing can be shared across multiple instances of reporting services.  After installing multiple instances the native wizard guides you through creating a farm of report servers.

Another thing worth noting because it will not be true of all the other tools I discuss in this series is that it can connect to virtually any data source you have.

What it isn’t

While there are some parts of Reporting Services that can provide ad hoc reporting capabilities the core of the tool is for static reporting.  If you really have a need for ad hoc reporting and could care less about report layout than you would likely pick another tool like Excel.

Who Uses it

While Reporting Services does have functionality for a power user to create their own reports it is typically thought of as a developers tool.  So report developers would use the tool Business Intelligence Development Studio (BIDS), which is part of the SQL Server install) to create incredibly detailed reports with almost endless flexibility when it comes the visualizations.

The tool that power users can use for creating reports is called Report Builder.  It has almost all the same functionality as BIDS but has been simplified a little for end users with more wizards and easier ways to bring in datasets.  Because it has a lot of the same functionality of the developers too you will find that this tool is for highly technical end user.

How is it consumed

Like I mentioned earlier Reporting Services reports can be consumed through a web front end of the native Report Manager, SharePoint, or even embedded in a custom application you’ve written with the Report Viewer control.

In addition to viewing the reports from a web browser they can also be delivered directly to the users via email or in a shared folder that they can access.  This is done using an incredibly useful part of the tool call Subscriptions.

Limitations

Again, Reporting Services is a static reporting tool therefore if your users find the need to change report layout frequently than that may eliminate this tool as an option for that particular report.

Another limitation you may run into is some of the functionality with Subscriptions, the report delivery tool mentioned earlier, are enterprise only features.  For example, if you wanted to make the reports delivered dynamic based off a list of emails you have in the database that must be done using Data Driven Subscriptions, which are Enterprise only.

Summary

As we go through this series remember these high level characteristics about Reporting Services:

  • For static report development
  • Extremely customizable
  • Dashboard capable
  • Highly scalable
  • Traditionally thought of as a developers tool
  • Has a native web front end but can also view reports through SharePoint and direct delivery with subscriptions

I hope you’ve found this helpful and stay tuned for the Part 2 in this series on Excel.  To read any of the other parts to this series follow the links below.

SSRS – Creating a Reporting Services Web Farm

Reporting Services performance problems can often be narrowed down to report design or resource constraints and sometimes a combination of the two.  In this post I’ll review the process of scaling-out Reporting Services so server resource constraints can be shared across multiple servers instead of relying on a single server for all report processing.

If you are new to understanding Reporting Services architecture it is important to understand the general steps that are occurring behind the scenes with Reporting Services. From the beginning of a user request the steps that happen are:

  • Client makes a request to the report server
  • Report server then makes a request to the report catalog
  • Report catalog sends back to report server report definition and tells the report server which data sources to hit.
  • Report server request data from source database
  • Source database sends data and the report server renders report metadata and data for the client

Understanding Scaling-Out Architecture

If you have ever installed installed SQL Server and the BI stack locally on your machine for testing purposes than you’ve likely done a single server install.  This means you have SQL Server, Reporting Services, Analysis Services and Integration Services engines all running on the same machine. 

image

While this might be fine running in a local testing environment on your machine this obviously doesn’t scale well on a server that requires thousands of users to connect.  In an environment that requires this many users a scaled-out deployment is ideal and can support a heavier workload. 

In a scaled-out deployment multiple Report Server instances are installed that all use the same repository or ReportServer database.  So when a thousand client machines make a request to the ReportServer the report rendering can be split the requests across multiple servers to do the work.  They still must use the same repository database because they still want to view the same report metadata but the report processing should be helped.  The screenshot below depicts a scaled-out deployment with three instances of Reporting Services installed and a single SQL Server database repository to return back report metadata.  This is an example of a common setup but not the only way to do this of course.

image

With this approach we could have our west coast customers using a URL that points to Server A, mid west customers using a URL that points to Server B, and all east coast customer use Server C. 

Another approach to this is to use a Network Load Balancer (NLB).  The idea behind a Network Load Balancer is that you provide a single virtual URL to the report server and the NLB sends the user to the Report Server that is least utilized.  So if you have two servers and Server A is only 20 percent utilized and Server B is 80 percent utilized then the NLB would choose Server A for the next incoming user instead of you manually providing a URL for each user.  The Network Load Balancer can either be a physical device on your network for making these decisions or you can enable the feature in Windows Server.

Setup and Configuration

The setup and configuration for scaling Reporting Services into a Web Farm is actually fairly easy.  In fact to oversimplify it here is an overview of the steps required:

  • Install SQL Server (Database Engine) on one server.
  • Install Reporting Services on two servers without configuring either.
  • Pick one of the Report Server installations and configure it using the Reporting Services Configuration Manager.  Go through each page of the configuration manager except the Execution Account and E-mail Setting, which are optional setting depending on you required setup.
  • Configure the second Report Server but use same report catalog database as the first and set URLs.  So on the database page ensure it points to the same database that was setup during the setup of the first Report Server

image

  • Go back to first Report Server you configured and click Add Server under the Scale-out Deployment page to join the instances as shown below.

image

Without overcomplicating this that is really all it takes.  This would be done for as many Report Servers as you would like to add to the farm.  Hope this helps.

SSRS – Embedding Refreshable Reports in Office Tools

Have you ever experienced this?

You have developed a set of Reporting Services report that your users love and they often show them off. The most common way users show off the reports is by taking screenshot images of the reports and embedding those images in a PowerPoint slide deck along with other information they plan to discuss.

I personally have seen this several times and I’ve always thought wouldn’t it be better to have a live version of the report available in the PowerPoint slide deck so it would always be up to date. This way new screenshots never have to be updated if visual aspects or data changes of the report occur because the slide deck would always have a live view of the report.

While this is a feature that has been touted in Power View you don’t have to wait because it is possible with Reporting Services now. Now of course the new Power View method of embedding reports is much more impressive because the reports remain interactive as if they were viewing on the SharePoint.

For this post let’s focus on how this can be done for Reporting Services because many people do not realize this is possible. Many of the Office products have the ability to embed images which can come from a URL. With this in mind we could also embed a URL of a report image and anytime the file is opened it the report will be updated.

Open a PowerPoint deck and go to the Insert menu in the Office Ribbon to insert a Picture. for the file name use the below template replacing the bold text with the relevant information about the report you want to embed.

http://<servername>/ReportServer?%2f<folder+name>%2f<report+name>&RS:Command=Render&RS:Format=Image&RC:OutputFormat=TIFF

**Note** The plus signs between folder name and report name are for any spaces you have between each word.

Then before you click Insert you will need to hit the dropdown arrow and select Insert and Link. Using this options retains the link to the image so that is can be refreshed each time the PowerPoint file is opened.

image

You should now see an image of your report embedded in your document and any time the files is reopened the view of the image will be updated to reflect new data and visualization changes. My example used PowerPoint but this could be done in other tools like Word or Excel too.

I hope this proves to be helpful to the reporting solutions you build.

SSRS – Using Analysis Services Dynamic Management Views to Build Dynamic Reports

Dynamic Management Views are a very powerful tool that are part of Analysis Services in 2008 and 2008 R2.  They give you the ability to monitor Analysis Services usage and metadata. 

MSDN articles on SSAS DMVs – http://msdn.microsoft.com/en-us/library/ms126079.aspx

Good post on using SSAS DMVs – http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

A report developer could leverage the capability to read Analysis Services metadata to build a report that can dynamically change the type of information that is displayed on a report.

Let’s walk through a basic example of how we can Dynamic Management Views to build a dynamic Reporting Services report.  I’m starting with a basic report that has already been built off of an Analysis Services cube that looks like this:

image

As you can see it shows Internet Sales by category over several calendar years.  The MDX query used for this is pretty standard as you can see.

Select
    [Measures].[Internet Sales Amount] ON Columns
    ,NON EMPTY ([Date].[Calendar].[Calendar Year]
    ,[Product].[Product Categories].[Category]) ON Rows
From
    [Adventure Works]

I start by changing the query to use an in session calculation with the WITH clause so I can basically alias it to [Measures].[Generic Measure].  By aliasing the measure it will make since more later because the field name doesn’t identify with any specific measure.

With
    Member [Measures].[Generic Measure]
        AS STRTOVALUE("[Measures].[Internet Sales Amount]")

Select
    [Measures].[Generic Measure] ON Columns
    ,NON EMPTY ([Date].[Calendar].[Calendar Year]
    ,[Product].[Product Categories].[Category]) ON Rows
From
    [Adventure Works]

The next step is to create a parameter in the report so I can pass in the measure name to the query dynamically.  Eventually I will populate this parameter using a DMV but for now I’ll just have it as a text field that I can type in the measure name.

Now to make this query dynamic with my newly created @MeasureName parameter I replace the dataset query with an expression that looks like this:

="With "
+"    Member [Measures].[Generic Measure] "
+"        AS STRTOVALUE("""+Parameters!MeasureName.Value+""")  "

+"Select "
+"    [Measures].[Generic Measure] ON Columns "
+"    ,NON EMPTY ([Date].[Calendar].[Calendar Year] "
+"    ,[Product].[Product Categories].[Category]) ON Rows "
+"From "
+"    [Adventure Works] "

image

You’ll notice that I’ve basically changed the query to a string in the expression except for the measure name which I’ve changed to use my new parameter.  This report will now work dynamically but the user would have to type in the measure they would like to see in the report.  Not only would they have to type it but they must know the MDX for it.  For example, users would have to type [Measures],[Internet Tax Amount].  Obviously, that’s not going to happen so we need to make this simpler for the user. 

This is where Dynamic Management Views help.  We can use the mdschema_measures DMV to return back a list of measures from our cube.  The following query will return back all my measures names, MDX, and format: 

SELECT Measure_Name, Measure_Unique_Name, Default_Format_String
FROM $System.mdschema_measures
WHERE Cube_Name = ‘Adventure Works’
ORDER BY Measure_Name

Unfortunately, not all format strings translate to Reporting Services but this could be manipulated using expressions.  Using this query we can create a new dataset and have that dataset populate our @MeasureName parameter.  The dataset cannot be used in entered into a dataset using the Query Builder because it’s not really MDX.  So you must select to build an expression on the new dataset and paste in the query.  It does not require an actual expression so remote the equal sign before the query.

image

With the dataset completed go back to the @MeasureName properties and change the available values to use the Measure_Unique_Name and Measure_Name fields to populate the parameter dropdown box.

image

Now that the parameter dropdown box it populated with the DMV query go ahead and preview the report.  You’ll see you can select any measure front the dropdown box and it will change the information shown on the report as shown below:

image

SSRS – Embedded Custom Code

When developing reports in Reporting Services you will often use the built-in expression language to make report data and formatting dynamic.  The expression language can do a lot to within Reporting Services but any gaps in functionality that it has can be filled with custom code. 

There are two ways to implement custom code in Reporting Services.  The first way, which I will show in the post, is with embedded code.  The other method is to create an external assembly that can be imported into a report.  I will discuss this method in a future post.

Embedded code requires that you use VB but you do not have to go through the process of creating an assembly in Visual Studio like you would with the external assembly method.  Let’s go through the process of creating a report using embedded custom code and discuss some of the other benefits and disadvantages of this approach.

Here’s a basic report that I’ve developed with a list of employees and phone numbers.

image

What I’d like to do is format all the phone numbers to look like this (###)###-####.  This could easily be accomplished with some custom code.  To add custom code in Reporting Services select the Report->Report Properties menu.

image

Go to the Code page and use the following code to apply the phone number formatting:

Function PhoneFormat(PhoneNumber As String) As String
    Select Case PhoneNumber.Length
    Case 7
        Return PhoneNumber.Substring(0,3) & "-" & PhoneNumber.Substring(3,4)
    Case 10
    Return "(" & PhoneNumber.Substring(0,3) &")" & PhoneNumber.Substring(3,3)&"-" & PhoneNumber.Substring(6,4)
    Case 12
    Return "(" & PhoneNumber.Substring(0,3) &")" & PhoneNumber.Substring(4,3)&"-" & PhoneNumber.Substring(8,4)
    Case Else
    Return PhoneNumber
    End Select
End Function

image

Click OK once this code has been entered.  This code reads in different variations of phone numbers that could be provided and converts it to the appropriate format.  We can now use this code in our report.  I can replace the current column that stores the phone number data with an expression that calls this custom code to correct that data presented in the report.  That expression would look like this:

=Code.PhoneFormat(Fields!Phone.Value)

image

When you preview this report now the data will look like this (I’ve kept the original column so you can tell the difference):

image

This is a basic example but shows how powerful custom code can be.  Now there are a few things I should point out about this embedded code example that are not ideal.  Some embedded code disadvantages are:

  • You must use Visual Basic (no C#)
  • No Intellisense in the code window like you experience in Visual Studio
  • Code errors are not visible until you actually preview the report

While embedded code is powerful you will find external assemblies have even more benefits.  My next post will walk you through the process of creating a custom assembly and using it in Reporting Services. 

A real quick plug for a new class I will be teaching this month.  Pragmatic Works will start offering in January a new Reporting Services Masters class for those of you that feel experienced with the basics of SSRS.  The Masters class will assume you know all the basics of building reports and will focus on advanced topics only.  Check out the class outline and register for the class now here:

http://pragmaticworks.com/Services/training/Course/Pragmatic-Master-SSRS-2008.aspx

SSRS – Dealing with truncating spaces

I apologize ahead of time on this post today because it will require lots of pre explanation and screenshots to set the stage for what I want to show you!  Often when building a report with multiple row groupings I like to change the default way SSRS places the fields on the report.  Default multiple row groups:

image

While this behavior is fine it can lead to a lot of useless white space when browsing the report.

image.

So what I like to do is still have the three groups by eliminate all that dead space.

image

Of course this presents a new problem.  When you view this report now the white space is eliminated but, it appears that all row groups are at the same level, which would confuse an end user.

image

So I thought to do the obvious  I’ll make each group and expression with some leading spaces to indent the second and third groups.  I could have also used the SPACE function to simulate the problem I’m demonstrating as well.

="   "+Fields!Subcategory.Value

This works beautifully from the BIDS preview screen but once deployed to the Report Server those spaces are then truncated.  This happens because the compresses the leading spaces for you automatically.

The workaround is to use a non truncating space character.  By using the ASCII key code for 0160 you will get the spaced effect you’re looking for.  So by holding Alt + 0160 on your number pad non truncating spaces will be added to your report expression.  Do this the desired number of times on each group and your report will look great!  By the way number pad was intentionally bolded here because if you’re working on your laptop make sure to turn on the number lock and use the appropriate number pad keys for this to work.  The final report looks like this!

image

SSRS – Join Function

Often when designing a Reporting Services with parameters you may be required to display those parameter values on your report.  This is beneficial when you have a lot of values for one parameter or maybe you just want to print the report and need to know how the report is being filtered.

Normally if you would like to use a parameter anywhere in the design of your report you can simply drag in the parameter name from the Report Data tab.  Here I’ve concatenated the parameter value with some text:

="Category: "+Parameters!Cat.Value

However, when you have a parameter that can have multiple values you will run into an issue with displaying these parameter values correctly.

The problem it is displaying an array where normally a single value would be placed.  The fix for this is a pretty simple function that exists in Reporting Services called Join.  By changing my parameter textbox to the following:

=”Category: “+Join(Parameters!Cat.Value, “, “)

Making changes places the array in a comma delimited list visible to the user.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers