Fun times at SSAS Workshop

I had the extreme pleasure of attending the SSAS Workshop by PragmaticWorks this week, which was a two-day session with a bonus third day entirely focused on Denali (Expedition Denali). Brian Knight (blog | @brianknight), Dustin Ryan (blog | @SQLDusty) and Lonnie Mejia (LinkedIn) were on site at the Microsoft Southwest District office in Tempe, AZ which has a beautiful view of Tempe Town Lake.

I have only had a little exposure to SQL Server Analysis Services before this and from what I have learned I do know that our own data warehouse group could significantly benefit from this workshop. I am not mocking them whatsoever, but I am saying some processes could be handled differently. For example cube updates. Instead of providing me the entire visual studio solution they can easily provide me a XMLA script which I can use in SSMS to deploy the dimension update. Things like this I never knew, so this was a real eye opener for me and gives me the needed ammo to fight with our developers. Kidding! It does however allow me to extend my freshly acquired knowledge to that group in a non-confrontational way of course (fingers crossed behind back).

Business intelligence has a warm place in my heart and the time I did spend developing reports was exciting. To be honest I would love nothing more than to be able to go from zero to data warehouse to SSAS slice and dice to full publish on reporting services, sharepoint, etc… in a week or so. I believe as a DBA that would be a valuable skill-set to have under my belt. This course is my step towards that direction.

There is no doubt that this workshop packs in a lot of information. The two days are literally bursting at the seams with information but this is definitely a MUST for those looking to get into the SSAS world. The PragmaticWorks staff really demystified SSAS. Their lectures and labs are delivered in such a manner that it is really easy to keep up with the pace. Throughout the course you are walked through the process of setting up an SSAS project all the way through creating cubes, dimensions, mining structures, roles and everything in between. The price of the course is a bargain given everything you walk away with.

I think the most action came towards the ending of day two. The room was divided down the middle and the groups were paired against each other to build an SSAS project from start to finish following a set of requirements. Then you needed to create a report in either reporting services or excel based on the cube we published. Everyone participated either by being the designated drivers (at the computer), yelling out the requirements, providing assistance and so on. It was intense! I must mention that “Team Dustin” my group WON the challenge against “Team Brian”. Better luck next time Brian! We literally beat them by 1-2 seconds at best. Nonetheless a fantastic method to illustrate not only what we had learned but more importantly what we had retained. If you get the opportunity to attend this workshop I would highly recommend it. You will not be sorry!

Expedition Denali (Day 3) was very exceptional. I have not touched Denali at all but from what Roger Doherty (blog | @Doherty100) and Brian Knight were covering and demoing I cannot wait till RTM. I would totally spill the beans because there are so many very cool and sexy things coming… but their “body-guard/new sales guy” Lonnie is a pretty big guy so I will refrain. Here he is working through the demo.

Random pics

Date Parameters and Things to Consider

I touched on Reporting Services Date Parameters, but what I did not cover is the underlying importance of understanding how TSQL works in terms of date parameters. You need to know that there is an implicit midnight time-stamp of 00:00:00 when a date is only supplied. So when you are dealing with date parameters in terms of FROM and TO dates, the TO will need or should include all records for that date as well. However, since a date is only supplied your chances of including all records for that end date are very slim. I was able to correct this by adding a modifier in stored procedures that takes the input date and increments it by one day.

For illustration purposes here are some TSQL scripts that will allow you to test a few queries to see what is actually begin returned when you only pass a date to a DATETIME field. This will help you understand what the results of your report will be as well.

Step 1: Create Table

CREATE TABLE #TestDate
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[StandardDT] [datetime] NOT NULL,
	[MyKey] [int] NOT NULL,
	[Notes] [nvarchar](100) NULL,
)

Step 2: Populate Table

INSERT INTO #TestDate (MyKey, StandardDT, Notes) 
SELECT 1, '1/1/2010', 'Note 1'
UNION ALL
SELECT 2, '2/1/2010', 'Note 2'
UNION ALL
SELECT 3, '3/1/2010', 'Note 3'
UNION ALL
SELECT 4, '4/1/2010 10:42:22', 'Note 4'
UNION ALL
SELECT 5, '4/1/2010 22:42:22', 'Note 5'
UNION ALL
SELECT 6, '6/1/2010', 'Note 6'
UNION ALL
SELECT 7, '7/1/2010', 'Note 7'
UNION ALL
SELECT 8, '7/1/2010 01:10:19', 'Note 8'
UNION ALL
SELECT 9, '7/1/2010 02:11:20', 'Note 9'
UNION ALL
SELECT 10, '7/1/2010 21:21:22', 'Note 10'

Step 3: Verify Data

SELECT * FROM #TestDate

As you can see we have 10 records total with some records that have a time-stamp associated that are beyond midnight specifically April and July while the the other months are midnight.

Step 4: Test Queries

Let’s run through some test queries to help better illustrate my point. Let’s query the data using a FROM date of 3/1/2010 and a TO date of 4/1/2010. Looking at the data there is one record for 3/1/2010 and two records from 4/1/2010. How many records do you think will be returned? Well let’s just see for ourselves.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Interesting… there is only one record returned even though 4/1/2010 is within our query parameters. Take a look at lines 8 & 9 from the query above and pay attention to the time-stamps. You can see in the screen cast below that the TO date which is represented by @EndDate parameter is 4/1/2010 00:00:00 which is 4/1/2010 midnight. Well looking through the data proves we do not have any records that meet that specific criteria because there are no records for 4/1/2010 that have a midnight time-stamp associated.

Let’s try a few more test queries just to get a clearer picture. Let’s see how many records are returned when we search FROM 7/1/2010 TO 7/1/2010. We know that our data set contains four records for 7/1/2010, but can you guess how many records will be returned if we want to search FROM 7/1/2010 TO 7/1/2010?

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '7/1/2010'
SET @EndDate = '7/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

There was only one record returned because there was only one record that met the criteria 100%. If you look at the values for @StartDate and @EndDate they both are returning 7/1/2010 00:00:00.

Now let’s run through one last query, because practice makes perfect. If I were to search our data set for all records from 4/1/2010 and supply a FROM date of 4/1/2010 and a TO date of 4/1/2010 how many records would be returned? The answer is zero, because both you and I know there are no records within the data set that have a time-stamp of 00:00:00 for 4/1/2010.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '4/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Perfect. No records returned just as we thought.

Step 5: Solution

The easiest way to rectify this is to ask the end users to use the next day, but that is not a practical answer because you and I both know after the first weekend or vacation the end users will return to their normal mode and begin searching through the data using the date they did before. So in order to satisfy the requirements I decided to add a parameter modifier to my procedure which takes the received input value for the @EndDate and increments the day by one. Meaning if 7/1/2010 were passed as the TO or @EndDate value it would become 7/2/2010 00:00:00 and all records from 7/1/2010 will be returned. Pretty simple.

ALTER PROCEDURE sp_RP_MyReport
(
  @StartDate DATETIME
  ,@EndDate DATETIME
)

AS 

SET @EndDate = DATEADD(d,1,@EndDate)

Line #: 9 in the above syntax is what I implemented which makes the adjustment to the @EndDate parameter.

Step 6: Validate Solution

Let’s add the modifier just below the parameter declarations and above the SELECT statement and see what we get when we execute the script. Can you guess what will be returned?

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SET @EndDate = DATEADD(d,1,@EndDate)
SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Success! Three records returned. By adding the modifier (Line #: 5) the value was accepted, incremented by 1 and supplied back to the SELECT statement and the records which fell into the criteria were returned.

Step 7: Cleanup

Now that we are all squared away, let’s try not forget that we still need to drop the temp table.

DROP TABLE #TestDate

Side Note

You can always use the DATEADD in the BETWEEN statement like below in line 06.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate
WHERE StandardDT BETWEEN @StartDate AND DATEADD(d,1,@EndDate)

Personal Investments

I am a little late in the certification arena but I have a good excuse… my dog ate my ambition! Actually I have been plugging away at my Computer Information Systems (CIS) degree with a concentration of Database Management and it boils down to a few remaining classes. Seems like I have been working towards this degree for a lifetime. I would have been finished long ago, but I had to decide if I would: 1) either run full steam ahead with my education while working full-time in a travelling required position and let my grades suffer or 2) attend part-time and maintain a 3.5 or higher GPA. I choose the latter. Even though some have told me that a (D) will still get you a degree, that is just not my character. I believe if you are going to commit, then you need to commit 100%. Especially when the investment comes with a hefty price tag. Not to mention “half-ass-ism” can be habit-forming and that quite frankly is something I am not looking to catch.

During these next few classes I have decided to pursue DBA specific certifications. I mean I am certified but the certifications that I currently hold are desktop support centric and since my role has changed it is only natural that I change my focus. To be honest when I first started in the IT field I really wanted nothing to do with databases. I was set on being a Server or Network Admin and thought databases were boring. Looking back I realize I was foolish because now I am consumed in it and wish to do nothing more but DBA work. I literally started working with SQL Server 2000 by an initiative that was put into action by my previous employer. The overall goal was to eliminate the use of paperwork for field staff. So the consultants were brought in and shortly after careful observation a project was born. Instead of equipping the field staff with paperwork to shuffle they were issued laptops with a custom developed application and a local copy of sql server (MSDE) back then. The fact we implemented Merge Replication somewhat enticed me to refer to the field staff as subscribers rather than field staff because I was trying to immerse myself with SQL Server terminology as much as I could. Not to mention I felt cool just speaking it out loud during meetings since this was a new concept to our organization.

I have heard and read about the pros and cons about certifications, but I see it as another item to prove that I can take my own initiatives along with my willingness to continue and further my knowledge and skill-set. Another available avenue for continual education. I have also heard certifications combined with a degree tends to hold more weight over those with just a certification but that is debatable. In the end I suppose it is all up to you and how you feel about it. Some say it boosts personal self-confidence and I am all for that. Why not?

The first exam I might tackle is (70-431: Microsoft SQL Server 2005 Implementation and Maintenance). I know it is a SQL Server 2005 exam, but I am more familiar with 2005 at the present time over 2008. I picked up the Microsoft Press Self-Paced book and will use that as my primary study material. I have heard and read many good things about this title. I also thought about (70-445: Business Intelligence Development and Maintenance). I cannot decide actually because I have a love for both. I see a need for Administration and Business Intelligence development. Either way I figured I would set study time to one hour a day at minimum with weekends being optional. If I can or cannot cover multiple chapters in one hour then so be it. I need to be strict with a schedule in order to stick with it. Luckily I have a license for VMware Workstation 7 which allows me to build a VM study system that I can literally destroy and revert when needed. This will provide me an adequate study platform during my progress. One thing I need to do is schedule myself for the actual test as it will make the process more official since I will be actually setting a realistic and attainable goal. Not to mention it will keep me focused since the added penalty of wasting money will add fire to my priorities and will align my studies.

I am interested in knowing what you would pursue first? SQL Server Administration or Business Intelligence?

UNION and Reporting Services 2008

I have used the UNION operator a few times in the past and during a recent Business Intelligence project I quite possibly used it more then I had in the past combined. Typically you can merge queries together simply by placing a UNION operator between two queries. Well it’s not that simple there are some restrictions.

the number of fields must be the same in both queries
the order of the fields must be the same in both queries
data types must be compatible

What you don’t believe me? Well then visit the TechNet site: UNION Transact-SQL and see it for yourself.

syntax

The syntax is really nothing…

SELECT Column1, Column2 FROM table1
UNION
SELECT Column1, Column2 FROM table1

That’s the gist of the it. Kind of boring right? Yeah a little bit. When we incorporate it within a stored procedure that is used for Reporting Services it becomes a tad more interesting. I can’t speak for you but at least it is for me. Now let’s get into some scenarios where it comes handy.

Let’s say I want to offer a report that uses a dropdown list for selecting and passing the parameters. Well typically in a select list you can only choose one, but what if you wanted an option to select all? This is where the UNION comes in handy.

procedure

Typically you would construct your procedure similarly to the one below.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id)

The basics of the procedure is a simple SELECT statement. So let’s take a look at the results when I just run (lines: 12-18) only.

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

The only problem with this procedure is with (line: 19). The choice is either one value or another. There is not option for a “show all value” to be passed.

WHERE (ProspectiveBuyerKey = @id)

This is where the UNION operators comes in. Not necessarily in the stored procedure per se but in the dataset you create to populate the select list. However you still need to modify the stored procedure to prepare for whats to come. This is a slight modification to the WHERE clause of the stored procedure.

WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Here is the final base for the “sp_RP_GetProspectBuyerData” stored procedure with the added OR operator.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

dataset

Now we need to construct the dataset stored procedure that we will use to populate the select list which will be used in our report. This is the basics of the stored procedure that we will be using to drive the select list on the report.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Well the above procedure is cool and all but it does not meet our needs as we need to have an “ALL” option listed as a selectable item in the select list. In order to achieve this let’s add another SELECT statement that will correspond to the changes we made to the first procedure “sp_RP_GetProspectBuyerData” where we added the following: ( OR @id = -1 ) and use UNION operator to merge them.

To list the “ALL” in our select list we need to add it using the same data type, order and we must have the same amount of fields. So here is what we need.

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

So now we need to merge the two SELECT statements and this is how its done.

Step 1: First we take…

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

Step 2: Then we add

UNION

Step 3: And finally we append…

SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Step 4: We wrap them into a stored procedure

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'
UNION
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Now if we run (lines: 9-15) only here is what the results look like.

As you can see the “ALL” is on top which is what we need and for the sake of testing. Let’s run through two quick scenario of passing a value of 1 and a value of -1 to the @id parameter just to see what the end results look like.

Here I set the @id variable to 1…

USE [AdventureWorksDW2008]

DECLARE @id INT = 1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results

Now let’s see what happens when we pass a value of -1…

USE [AdventureWorksDW2008]

DECLARE @id INT = -1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results

Boom! All records are returned. Exactly what I needed. Now let’s run through the report! I already set the available values for the parameter properties so we can just jump right into the report and see it in action.

Business Intelligence Development Studio

Step 1: Select All

Step 2: Run Report

Results when value other than All is selected

Reporting Services Date Parameters

Being the guy that I am I often try to implement pure TSQL solutions especially when working with parameters via Reporting Services. I know it seems like they go hand-in-hand and to a great extent they do, but there are some cases where it is best to use expressions for the report parameters over a stored procedure. Don’t get me wrong my TSQL approach worked fine but when I used the expression I was able to accomplish the exact same results with slightly less syntax to write and possibly less overhead. I stumbled upon the blog post “Calculating the first and last day of the month” by datageek on blogspot which illustrates the expressions I used for my report.

TSQL Syntax

SELECT DATEADD(Month,DATEDIFF(Month,0,getdate()),-1)+1 'StartDate'
SELECT DATEADD(Month,DATEDIFF(Month,0,getdate())+1,-1) 'EndDate'

RS Expression Syntax

Note: The following can be used for Access as well to get the first and last days of the month from what I understand.

First Day of the Month

=DateSerial(Year(Now), Month(Now), 1)

Last Day of the Month

=DateSerial(Year(Now), Month(Now) + 1, 0)

So for illustration purposes I put together a report based on the AdventureWorks database and used the RS Expression Syntax to populate the date fields, but first let’s create the stored procedure.

Stored Procedure

CREATE PROCEDURE sp_RP_GetEmployeeData
(
	@StartDate datetime
	,@EndDate datetime
)

AS

SELECT	e.EmployeeKey
	--, e.ParentEmployeeKey
	--, e.EmployeeNationalIDAlternateKey
	--, e.ParentEmployeeNationalIDAlternateKey
	--, e.SalesTerritoryKey
	, e.FirstName
	, e.LastName
	--, e.MiddleName
	--, e.NameStyle
	, e.Title
	, e.HireDate
	--, e.BirthDate
	--, e.LoginID
	--, e.EmailAddress
	, e.Phone
	--, e.MaritalStatus
	--, e.EmergencyContactName
	--, e.EmergencyContactPhone
	, e.SalariedFlag
	--, e.Gender
	--, e.PayFrequency
	, e.BaseRate
	--, e.VacationHours
	--, e.SickLeaveHours
	--, e.CurrentFlag
	--, e.SalesPersonFlag
	--, e.DepartmentName
	--, e.StartDate
	, e.EndDate
	--, e.Status
	--, st.SalesTerritoryCountry
	--, st.SalesTerritoryRegion
	--, st.SalesTerritoryGroup

FROM AdventureWorksDW2008.dbo.DimEmployee AS e
JOIN AdventureWorksDW2008.dbo.DimSalesTerritory AS st
ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE (HireDate BETWEEN @StartDate AND @EndDate)
ORDER BY e.HireDate

Now that we have the sProc in place building the report is pretty much the same for the most part; however, the report parameters have relocated. They are now found on the left hand side under the report data section. In 2005 you would have found the parameters under the Report menu.