Business Intelligence Landscape Presentation

I’ll be delivering a “Business Intelligence Landscape” session tonight at the UCSD Extension Continuing Education & Career Showcase.  In talk I’ll go over what people mean when they say “Business Intelligence”, as well as some of the core concepts and common solutions.  We’ll talk about stuff like Extract, Transform and Load (ETL), Data Warehouses, Star Schema, Cubes, Aggregates, MDX, KPIs Reporting Tools, and Data Mining.  But if that weren’t enough we’ll see how the cloud and big data are changing the BI landscape as well.

Feel free to grab a copy of my slides:

Business intelligence landcape

Enjoy.

Extracting SSRS Report RDL (XML) from the ReportServer database

Overview

11/30/2011 – I have created a CodePlex project based on this blog post with some scripts in response to the questions I got in this posts comments. The project is called “SQL Server Reporting Services (SSRS) Catalog Queries”. I have a separate blog post about the project here.

12/17/2011 – I have updated the CodePlex project project and included the SSIS project that extracts catalog contents directly to the file system! w00t!

06/01/2011 – REPOSTED TO FIX FORMATTING ISSUES. LET ME KNOW IF YOU CAN’T READ IT.

Before I even start this entry I should state that the methods I document here, while instructive and useful, are frowned upon by Microsoft, and they are not supported. You should consider getting to the SQL Server Reporting Services (SSRS) item contents via the Web Services provided with SSRS rather than extracting them directly from the database as I describe here. As SSRS is versioned the structure and storage methods used to manage the Report Server items may change, and the methods described here may no longer work. You will have a better chance (although no guarantee) that your code will continue to work against new versions of SSRS if you use the web services instead. Ok, the disclaimer is out of the way.

There have been numerous times that I have wanted to extract the XML of a report (RDL) or the definition of a Shared Data Source (RDS) from the ReportServer database. One of the first times I had this need was as part of a disaster recovery. A client lost the source files for their reports, as well as the reporting services installation. The quickest way to get their report definitions back was to just extract the XML from the ReportServer.dbo.Catalog table’s “Content” column. For a current project I’m working on, I want to determine the database objects (tables, stored procedures, views, functions) that are used by the Datasets in each report. Other times, I have just been curious and wanted a quick way (from within SQL) to view the item definitions.

This blog post, as well as a few to follow document the methods I have developed to do just this.

The ReportServer Database:

SQL Server Reporting Services (SSRS) keeps the various Reports, Data Sources, Images and other resources it provides inside a SQL Server Database. This database may or may not be on the same computer as the Reporting Services instance itself. Also, while the database is named “ReportServer” by default, it could be named something else, so you will need to determine for your installation what instance that database is on, as well as what it’s name is. The scripts below assume the default database name of “ReportServer”.

Inside the ReportServer database there is a Catalog table (ReportServer.dbo.Catalog). The Catalog table stores the items (Folders, Reports, Resources, Linked Reports, Data Sources, Report Models, Report Parts and Shared Datasets) that are available via the SSRS instance. There are numerous columns in the Catalog table, and I won’t describe them all here. However, the ones of interest to this article include:

Column Name Data Type Description
ItemID uniqueidentifier The Primary Key. A system generated GUID.
Path nvarchar(425) The root (“Home”) based path to the item (including its name) in the virtual folder structure provided by the site.
Name nvarchar(425) The name of the report item without the path.
Type int Identifies the type of the item being stored:1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)
Content image The actual bytes of the report item. If this is a folder or Linked Report, the content is null, otherwise it is stores the binary image of the report item.

Armed with that knowledge you can see that a query to retrieve the binary content for all reports would would like:

SELECT
   ItemID
  ,Name
  ,Type
  ,Content
FROM ReportServer.dbo.Catalog

And the result might look like:

ItemID Name                    Type  Content
------ ----------------------- ----- -------------------------
909... AdventureWorks2008R2        5 0xEFBBBF3C3F786D6C2076...
8CC... AdventureWorksDW2008R2      5 0xEFBBBF3C3F786D6C2076...
A20... AdventureWorksLT2008R2      5 0xEFBBBF3C3F786D6C2076...
7BE... Northwind                   5 0xEFBBBF3C3F786D6C2076...
630... Products                    2 0x3C3F786D6C2076657273...
DF6... AnnualSalesByMonth          2 0x3C3F786D6C2076657273...
06F... SalespersonReport           2 0x3C3F786D6C2076657273...
D9E... ParameterTest01             2 0x3C3F786D6C2076657273...

The UTF-8 Byte Order Mark (BOM):

Unicode strings can have a special character called the “Byte Order Mark” at the beginning to indicate the byte order (endianess, big endian, little endian) and encoding (UTF-8, UTF-16, UTF-32) that was used to create the string. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. it just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. While it is supported, it can cause problems with the conversion to XML, so it may be worthwhile to remove it for Reports (Type=2), Data Sources (Type=5), Report Parts (Type=7), and Shared Datasets (Type=8). You can do that by testing to see if the first three bytes are 0xEFBBBF and then trimming them if they are. Before we do that though, we have to get rid of that pesky image data type used by the Content field.

Casting Out Image:

The ReportServer.dbo.Catalog.Content column uses the deprecated image data type. Unfortunately we can’t perform string manipulations or direct conversions to varchar or xml from the image data type. To fix the problem, we need to first convert (or cast) the image to a varbinary(max), and then perform any string manipulations or additional conversions on the varbinary value.

Getting the Content as Varbinary, Varchar and XML:

Ok, so putting all the stuff from above together, the following code will extract the contents of all Reports, Data Sources, Report Parts and Shared Datasets (Types 2,5,7, & 8) from the database. I’ve broken the query down into a two CTEs and an outer query to isolate the key parts. you should be able to copy the query below, and run it on the same server as your ReportServer database to view the results:

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
   ItemID,Name,[Type],TypeDescription
  ,Content --varbinary
  ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
  ,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoBOM

Here is a screen shot of my results so you can see that the XML type is recognized:

Catalog Content as XML Query Results

Querying the RDL Contents:

Ok, so big deal. We did some trimming and casting and we got XML back. Where this really starts to become useful is when you start using SQL Server’s built in XML data type to parse the XML, and extract key pieces. For example, the following query builds on the query from above and extracts the actual commands used for the each dataset in each Report or Shared Dataset. Cool!

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

Here is a screenshot of the results. Again, this would be more impressive on your own machine with your own reports and queries being shown:

Querying the Content XML

I know that the text in the image the above is really small, but you may be able to see that the CommandText column contains the actual SELECT statements used in each report or shared dataset.

This is just the tip of the ice berg though. Once we have the XML, we can do a lot of different things with it. I hope in the future to post some additional entries that outline creating a set of functions that could be used to parse your report xml in interesting ways. till then, enjoy!

Introduction to SQL Server Reporting Services Session Slides…

Here is the slide deck from the Introduction to SQL Server Reporting Services sessions I gave at SQL Saturday 44 in Huntington Beach, CA earlier today!  Thanks to everybody who came, and sorry for the technical problems getting the Report Manager to load.  It’s back to the same problem I was having about port 80 not getting registered right on Windows 7.  I thought I had that fixed, but I guess not. 

Anyhow grab the slides, and drop me a comment or an email if you have a question!

Slides Screenshot
Introduction to SQL Server Reporting Services 

Thanks again to all the SQL Saturday 44 organizers.  It was a great event!

Fixing SQL Server Reporting Services (SSRS) 2008 URL Reservations on Windows 7

UPDATE! (07/27/2010) – Even with the original post claiming to have fixed my problem, I have had repeated reoccurrences of situations where all of a sudden, it just didn’t work.  This seemed random and unpredictable.  In fact this morning, I realized my problem was limitied to just the reporting services /ReportServer and /Report virtual directories.  It was actually with any IIS hosted web. I normally do web dev in a VM, so I haven’t noticed that problem until today.  But in the middle of prepping some demos, websites that worked locally yesterday all of a sudden didn’t work this morning.  404.  File not Found!

I now believe I know the problem……..ITS SKYPE!!!!!  Skype? Yes! Skype!  There is an option in Skype to allow it to use port’s 80 and 443 as an alternative to its regular port.  This option appears to be on by default (I certainly never turned it on myself) and for a normal end user is probably a great choice.  Turns out in my case though (and with other developers or anybody that needs to host their own local webs on port 80) this could cause problems.  Not sure why it was never a problem on my previous OS’s.  I haven’t really seen this as an issue until Windows 7.  Maybe its a new option in Skype.  Not sure.  Anyhow, it appears that once I have this option turned off, I am once again able to access my reporting services web sites (any any other IIS website for that matter) on port 80 again.

The randomness seems to be that sometimes Skype would get to the ports first and grab them.  The other port 80 based services then wouldn’t get their messages.  On other occasions, Skype would get there last, and the other port 80 services would work.

To turn this off in skype, open Skype and go to “Tools” | “Options”, on the left hand side of the options dialog select “Advanced” | “Connection” and then turn off the checkbox for the “Use port 80 and 443 as alternatives for incoming connections”.

Skype port 80 usage

Original post (04/08/2010):

I finally got around to refreshing my Windows 7 installation about a month ago.  I had been running SSRS 2008 on Windows 7 RC without issue, but after re-installing everything on the release I had a problem with the “/Reports” and “/ReportServer” URL reservations.  I would get a 404 not found from IIS when I tried to access either of them.

I poked around a little bit and the best suggestion I found was to use the “Reporting Services Configuration Manager” to change the ports for the URL reservations to something other than port 80 (Like port 8080), and for a quick fix that worked.  I just didn’t understand why I had to.

This morning, I was determined to figure it out.  Unfortunately I didn’t document my steps better because now it works, and I can’t actually make it NOT work again.  Here are the two things I did that MIGHT have made the difference:

  1. I ran the “Reporting Services Configuration Manager” as administrator by right-clicking on it and choosing “Run as Administrator”
  2. I added host header entries in the “Advanced” buttons for “localhost” on port 80 for both the Web Service URL and Report Manager URLs.
  3. I tested the site using my new localhost host headers: (http://localhost/Reports) (http://localhost/ReportServer) and magically they both worked.
  4. I removed the host header entries, retested, and everything still worked.

It feels like there was just some block in Win7 that we keeping the original port 80 reservations from working, and I somehow cleared the blockage with one of the above. I had changed the URL reservations in the past (from port 8080 and back to port 80, etc) but nothing did the trick.  Something I did today caused it to finally start working.

Actually, NOW I found a Technet (I have been searching for weeks, but just found this now), that may have lead me to the solution first (Troubleshooting Configuration Problems)

Note, this is NOT the same problem as the “Protected Mode” in IE or having to run IE as administrator when running IE locally on the Report Server. Those problems can be fixed using the information here: How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008

Anyhow, let me know if this helped you at all. Sorry the instructions aren’t more detailed. I didn’t do a good job of documenting things as I went along.

SQL Server Reporting Services 2008 for .NET Developers Presentation…

I’ll be speaking at the Orange County .Net user group (www.ocdotnet.org) this evening (02/09/2010) on SSRS 2008 for .NET Developers.  I’ll cover the range of options that developers have for enhancing, extending and integrating SQL Server Reporting Services. 

You can get the slide deck, and the sample project for my session here:

image 
SQL Server Reporting Services for .Net Developers (6.6 MB)

T-SQL GO [Count] – New to me!

Ok, I learned a cool feature of the GO command in SQL Server client tools (osql, sqlcmd and SQL Server Management Studio).  That is that you can add a number [Count] after the GO keyword.  The client tool will then execute the batch that precedes the GO the number of times specified by the [Count] value.

It looks like this was added back in SQL 2005, and I just never noticed it.   So, it’s new to me!

For Example, the following batch submitted in a SQL Server Management Studio query window

SET NOCOUNT ON  –just to cut down on the output…
SELECT ‘Hey, that’’s cool!’ AS Neat
GO 3

Produced the following output:

Beginning execution loop
Neat
—————–
Hey, That’s Cool!

Neat
—————–
Hey, That’s Cool!

Neat
—————–
Hey, That’s Cool!

Batch execution completed 3 times.

You can read the docs at:  http://msdn.microsoft.com/en-us/library/ms188037.aspx

SSRS 2008 Architecture Slide

This is a PowerPoint slide I use in my SSRS presentations.  Feel free to grab and use it:

SSRS 2008 Architecture Overview PPTX Screen Shot

Introduction to SQL Server for Windows Administrators…

I presented at the WiNSUG user group here in San Diego last week (Feb 5th, 2009).  My topic was “Introduction to SQL Server for Windows Administrators”.  It was pouring rain, so I really appreciate the folks that made it to the meeting.

I have posted the slide deck from my session up on my website so feel free to download it if you would like: icon_pptxIntroduction to SQL Server for Windows Administrators

Slide Title Screen Shot

Reporting Services Architecture Diagram…

I have a standard diagram that I draw on the board when I teach reporting services classes.  I have lately however been teaching some classes online and drawing this diagram by hand on the screen gets a little tedious.  To simplify my life, I drew it in Visio so I could re-use it.  I am posting it up here so that my students, or anybody else for that matter can grab the diagram and re-use it. 

It represents a typical SQL Server 2005 native reporting services deployment.  This also describes the basic structure of a 2008 install, just replay IIS with HTTP.SYS) I may update it in the future, or create another version to include SharePoint installations. 

Use the links below to download the image.  If you update it, or have suggestions for improving it let me know! 

Reporting Services Overview Diagram for Blog

 icon_zip  Download the original Visio Diagram as well as a JPG and PDF (0.5MB)

LA Code Camp Slides…

Ok, here they are.  If you attended my sessions you know they are more demo than slides so it is questionable how much value you can get from the decks themselves, but people asked and I promised so here they are.  I would like to do screen captures of these and hope to do that soon.  If and when I get that done I will post links on the blog.

Sessions:

Enjoy!