Devin Knight

JSSUG Speaking Event: Reporting Services 2008

2008-07-17T14:07:00Z

We had a great group of about 35 people last night at the JSSUG (Jacksonville SQL Server User Group) meeting. If you missed it I spoke on Reporting Services 2008. I called the presentation Introduction to SSRS 2008 but we focused mainly on the new features in 2008. If you would like the slide deck just email me or it should be posted at www.JSSUG.com shortly. If you have any question about it feel free to email me.

Some of the top features we highlighted were:

Reporting Services without Internet Information Services (IIS)
New Word export option
Redesigned Report Builder
- Data pane
- Smart tags
- Grouping pane
Tablix
Gauges and Charts

I really would suggest getting a head start on getting used to the new reporting environment in either Report Builder or BIDS. Here's the link to download both: http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx

There are two separate downloads so make sure you download the feature pack as well to get Report Builder 2.0. Report Builder has changed drastically from the past and had almost every capability of BIDS.

Upcoming Speaking Event

2008-07-07T15:24:00Z

I will be speaking at an upcoming JSSUG (Jacksonville SQL Server User Group) meeting Wednesday July 16th. This event will cover what's new with Reporting Services 2008. It will cover mainly the new features in BIDS such as the added Dundas components and the new tablix. Last month, Brian gave an overview of what's new in SQL Server 2008 including how Reporing Services no longer uses IIS for rendering. That's why I've decided to go in depth about the Report Designer for this meeting. Hope to see you there. Meeting details at JSSUG.com

Answers To SSIS Interview Questions

2008-05-19T20:00:00Z

This is just a starting point for you to prepare for your interview so make sure not to limit yourself to what you find here. These answers could easily vary but generally someone who is interviewing is looking not only for a correct answer but confidence in your answer. If you have any questions about my answers or have something to add feel free to do so, but I did try to keep the answers short and sweet.

SSIS Interview Questions

1. What does a control flow do?

A control flow manages the workflow of the package. Pretty self-explanatory it controls the flow of how the package is run. Sorry to use the words control flow in the definition, but this is a real easy one.

2. Generically explain what happens inside a data flow task?

It moves data from nearly any source, performs different transforms to the data, then sends to nearly any destination.

3. Explain what ETL is?

Extract, Transform, and Load. Basically pulls the data from a location, change/transforms the data somehow, and then loads it in a new location.

4. Which task would you use to copy, move or delete files?

File System Task (This is just an example know all control flow tasks)

5. Which transform would you use to split your data based on conditions you define?

Conditional Split (This is just an example know all data flow transforms)

6. Explain the pros and cons of deploying to a file system vs msdb?

Functionality               Best in File System                Best in MSDB
Security                                                                                X
Backup and Recovery                 X
Deployment                                X
Troubleshooting                         X
Execution Speed                         X                                       X
Availability                                  X

7. If you did not know the answer to a question what would be your next step to find the answer?

Search online, read a book, or ask for help. I generally would suggest it in this order.

Answers To DBA Interview Questions

2008-05-19T18:51:00Z

Here are your answers to the questions from last week You should get some variation of what I have given you here. If you think I've made a mistake somewhere let me know and we can discuss it. This will be a short series of blogs on interview questions to help you learn what to expect when entering your first interviews.

Activities for Laptop

1. Using the AdventureWorks database and bring back all the columns from the HumanResources.Employee table where the title starts with Production, marital status is M and the hire date is between 1/1/2000 and getdate(). Hint: Should get 12 rows back.

SELECT *
FROM HumanResources.Employee
WHERE Maritalstatus = 'M' and hiredate between '1/1/2000' and getdate() and title like 'production%'

2. From the AdventureWorks database join the tables Production.Product and Production.TransactionHistory. Bring back columns ProductID, Quantity, and ActualCost from Production.TransactionHistory. Use Name and ProductNumber from the table Production.Product. Also, add a where clause that only returns the rows having an actual cost greater than zero.

SELECT h.productid, h.quantity, h.actualcost, p.name, p.productnumber
FROM Production.Product AS p INNER JOIN Production.TransactionHistory AS h
ON p.productid = h.productid
WHERE actualcost > 0

3. Using the AdventureWorksDW database show a join between Dim_Customer and Dim_Geography that brings back the columns LastName and FirstName aggregated separated by a comma, AddressLine1, and AddressLine2 from Dim_Customer. From Dim_Geography bring back the City, StateProvinceCode, and PostalCode columns. Then order by LastName.

SELECT c.lastname + ', ' + c.firstname AS fullname, c.addressline1, c.addressline2, g.city, g.stateprovincecode, g.postalcode
FROM dbo.DimCustomer AS c INNER JOIN dbo.DimGeography AS g
ON c.geographykey = g.geographykey
ORDER BY lastname

4. This time using the same SQL statement from the last question replace the AddressLine1 and AddressLine2 columns with a case statement. The case statement should return only AddressLine1 if AddressLine2 is null and AddressLine1 and AddressLine2 separate by a comma if AddressLine2 does exists

SELECT c.lastname + ', ' + c.firstname AS Fullname, g.city, g.stateprovincecode, g.postalcode,
CASE
when c.addressline2 is null then c.addressline1
else c.addressline1 + ', ' + c.addressline2
end AS Address
FROM dbo.DimCustomer AS c INNER JOIN dbo.DimGeography AS g
ON c.geographykey = g.geographykey
where c.addressline2 is not null
ORDER BY lastname

5. Using a left join on FactResellerSales and Dim_Employee from the same database show EmployeeKey, LastName, and FirstName from Dim_Employee. From FactResellerSales show the SalesAmount column summed and then group by Dim_Employee columns. Also, use having statement to bring back only summed sales amount that have a total greater than 1 million. Lastly order by the summed sales amount in descending order. Hint: Should be only 15 rows.

SELECT e.employeekey, e.lastname, e.firstname, sum(s.salesamount) AS totalsales
FROM dbo.FactResellerSales AS s LEFT JOIN dbo.DimEmployee AS e
ON s.employeekey = e.employeekey
GROUP BY e.employeekey, e.lastname, e.firstname
HAVING sum(s.salesamount) > 1000000
ORDER BY sum(s.salesamount) desc

SSIS Interview Questions

2008-05-19T18:08:00Z

I have decided to make a short series of blogs about possible interview questions to help you prepare for an interview. Last time I wrote about basic DBA questions. Today I have given you some basic SSIS question to study. These basic SSIS questions will give you a starting point on how to prepare for an interview. If you have any other suggestions feel free to add to this list but keep in mind these questions are probably for a mid to junior level BI consultant. I would assume if you're a senior BI developer you know what to expect.

SSIS Interview Questions

1. What does a control flow do?

2. Generically explain what happens inside a data flow task?

3. Explain what ETL is?

4. Which task would you use to copy, move or delete files?

5. Which transform would you use to split your data based on conditions you define?

6. Explain the pros and cons of deploying to a file system vs msdb?

7. If you did not know the answer to a question what would be your next step to find the answer?

Possible interview questions for Intern/Junior DBA or Developer

2008-05-10T23:22:00Z

I wrote these questions thinking about what some basic t-sql statements that I would want to see if I were to interview a intern or junior level DBA. These cover some basics using both the adventure works transactional database and the adventure works data warehouse. I'm interested in seeing how others test people they interview. Let me know what suggestions you have for me to add to my questions.

Activities for Laptop

SQL Saturday

2008-05-05T12:58:00Z

So if you missed out on SQL Saturday you missed a great event and I highly suggest making it to the next event in Orlando June 7th and 8th. Check www.sqlsaturday.com for registration and schedules. Hope to see you in Orlando!

This weekend brought hundreds of people from all over the country to Jacksonville to network and improve their skills. Both my sessions went well and if you attended my Intro to SSIS session and would like the few PowerPoint slides email me at knight_devin@homail.com and I would be happy to supply you with them. I had many great questions in both sessions but if you have any further questions let me know and I’ll be happy to help.

SQL Saturday - Jacksonville

2008-04-22T20:53:00Z

I am fairly new to the IT profession, so I think my blog will be great for others trying to get their break. I have always used blogs as a great source to find answers to my numerous questions. So, I hope my blog can be used similarly. My theory is if you write about what you have learned then you are twice as likely to remember it. So here it goes let’s prove myself right.

Here’s where you can find me:

I am a member of the Jacksonville SQL Server User Group although unfortunately I travel during the week so miss the Wednesday meetings. I will be speaking at SQL Saturday in two sessions on May 3 at the University of North Florida in Jacksonville. The first session is more of a discussion and will be about what steps to take to get your break in the IT industry. The second is an introduction to SSIS where I will show how to create a basic package. Expect this session to fill up quickly because it is an intro session.