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.

SQL Saturday 157 Azure SQL Database Presentations and Demos

This is an awesome jam packed weekend for me.  I had the Windows8 Dev Camp yesterday, and SQL Saturday 157 today.  If you are a Database Professional (DBA or Dev) and don’t know about SQL Saturday’s, do your self a favor and find out about them! They are free (or extremely inexpensive) events held on Saturdays at locations all around the world.  They are organized by local members of your community and supported by PAAS. If you don’t see a SQL Saturday in your region, maybe you could help get one organized!

Anyhow, at the SQL Saturday #157 in San Diego today I have two sessions.  You can grab my slides and demos here:

SNAGHTMLb00d8a

 

Read the rest of this entry »

“SQL Server Reporting Services (SSRS) Catalog Queries” CodePlex Project

I have received a fair amount of feedback and follow-up questions about my Extracting SSRS Report RDL (XML) from the ReportServer database blog post in which I show how you can simply get a Report’s (and other catalog items for that matter) XML out of the ReportServer.dbo.Catalog table. 

Once you have extracted the catalog item as XML, you can then leverage SQL Server’s built-in support for the XML data type and XQuery to further parse the XML.  At this point you need to have an understanding of the Reporting Services Report Definition Language XML Schema and a desire to extract specific pieces of information from a report definition. 

Based on the requests I received in my previous blog posts comments, and my own use of these queries in real-world situations, I decided it might be helpful to create a sample SQL Server Management Studio (SSMS) project that contained example objects and queries that could be used to work with the SSRS Catalog contents. 

To that end I have created a “SQL Server Reporting Services (SSRS) Catalog Queries” CodePlex Project (http://ssrscatalogqueries.codeplex.com/).  I hope to expand the project in the future, but for now, the SSMS project includes the following scripts:

  • "01 – Create the ReportQueries database.sql"
  • "02 – Query Catalog Content.sql"
  • "03 – Create the CatalogContent View.sql"
  • "04 – Find all Data Set Commands.sql"
  • "05 – Find all the Data Set Fields.sql"
  • "06 – Determine Fields Actually Used.sql"
  • "07 – Find all Connection Strings.sql"
  • "08 – Extract Connection String Components.sql"

Grab the project and play with the scripts.  If you don’t have the SQL Server 2008 R2 client installed, you won’t be able to SSMS project file (I created it using the SQL 2008 R2 version of SSMS), but you should still be able to open the .SQL script files directly in your current version of SSMS.

If you create a script that is useful, let me know and I will review it as a possible addition to the project.  Of course, make sure to let me know if you use it, like it, hate it, or have questions or changes!

Wouldn’t it be nice if SQL Server exposed the statement tree created by it’s parser?

If you have seen any of my previous blog posts on extracting SSRS Report XML from the ReportServer.dbo.Catalog table, you may have also noticed some of the comments hoping to determine the objects and columns that a report’s data sets reference.  It seems like that should be a simple task, but unfortunately it is harder than it should be. 

SQL Server has to answer that exact same question for itself when the statements are submitted.  SQL Server parses the SQL statements and generates an internal “tree” that represents the statement.  That tree includes a number of things, but certainly the names of the objects referenced by the command.  We could get something similar by using the plan information that is generated, but the plans don’t provide details on the higher level objects (views, etc) that are used in the query. 

I posted a suggestion in connect to have the team provide us with a system function that might return the objects that a statement depends on. 

Check it out, and vote it up if you agree:

https://connect.microsoft.com/SQLServer/feedback/details/709658/expose-sql-parse-tree

Adventure Works Logo Re-do

Ok, so I’m a nerd.  I’m also a Microsoft Certified Trainer, and I teach a lot of SQL courses so as a result I work with AdventureWorks databases a fair amount.  When I am building reports in SSRS that use AdventureWorks I like to have a nice looking logo to use, and the ones that are provided with the curriculum or are available online leave much to be desired. 

I took it upon myself to create a higher resolution version of the logo files, and thought I would post them here for other control-freak-nerds like myself to use.  As long as Microsoft doesn’t give me grief about copying and re-distributing their logo, then I’ll leave this up here.

Use the links below to download a .zip file with illustrator, corel, photoshop, xaml, jpg, png, and other formats of the logo.  Enjoy.

AdventureWorks Logo AdventureWorksLogo.zip (448k)

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!

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)

SQL Server Case Sensitivity and Accent Sensitivity Demo….

01/18/2010 – 12:18PM

I am in the middle of a SQL Server 2008 Administration course, and a question came up about width-sensitivity in collations.  I am not sure if half-width vs. full-width makes sense in non-Asian character sets.  The only references I seem to find about it is on Korean collations.

However, in my poking around I was trying to test the differences collations make on comparisons, and I generated the following script in my testing.  I am posting it here so I can find it later, but also thought others might find it enlightening….

<span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Case Sensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CS_AS) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'a'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CS_AS)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span><br /><br /><span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Case Insensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'a'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span><br /><br /><span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Accent Sensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'á'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span><br /><br /><span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Accent Insensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AI) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'Á'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AI)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span>

If you have any insight to offer on width-sensitivity (especially as it relates to non-Asian collations) please let me know!

Enjoy!

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

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