Kirk Haselden

Kimball group webcasts

 Over the last two decades, the Kimball Group has pioneered a method for successfully defining and implementing an enterprise data warehouse / business intelligence system. The Kimball Group’s The Microsoft Data Warehouse Toolkit, specifically applies this method to the SQL Server 2005 platform.

Please join us at the upcoming webcasts as the experts from the Kimball Group demonstrate, step-by-step, how the SQL Server 2005 platform solves the tough issues faced in implementing Data Warehouses and Business Intelligence applications.

TechNet Webcast:

Microsoft Business Intelligence (BI) Using the Kimball Method (Level 200)

When:  June 6, 2006

9:30 a.m. to 10:30 a.m. Pacific Daylight Time

Presenter:  Warren Thornthwaite

Real success in business intelligence (BI) is defined by both short and long-term results. For the short term, you can win with top priority, data quality, appealing to broad interest and high value. Long-term success however, requires constant growth and expansion, as well as a solid, well-designed foundation. Join this webcast to learn how to build a strong, scalable Microsoft BI architecture. Learn about the basic Kimball Method, the data warehousing/BI Lifecycle, the three tracks of the development phase, and many more details and issues you would be hard-pressed to anticipate on your own.

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032297084&Culture=en-US

TechNet Webcast:

Designing a Scalable Data Warehouse / Business Intelligence (DW/BI) System (Level 200)

When:  June 20, 2006

9:30 a.m. to 10:30 a.m. Pacific Daylight Time

Presenter:  Joy Mundy

When you are building a data warehouse / business intelligence (DW/BI) system, scale is likely to be a major concern. Either you have a large system now, or you hope to grow to have a large system; or you have a small system but a tight budget, and you want to do more with less. What does large scale even mean? Is it determined by data volume, the number of users, complexity, or something else? What are the most important factors to consider? Join this webcast to learn techniques for addressing these and many other DW/BI issues. The session introduces and explains the Kimball Method lifecycle, and shows how to apply it to a scalable DW/BI system.

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032297070&Culture=en-US

MSDN Architecture Webcast:

Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse (Level 200)   

When:  July 11, 2006

11:00 a.m. to 12:00 p.m.  Pacific Daylight Time

Presenter: Joy Mundy

How do you combine the tasks and transforms offered by Microsoft SQL Server 2005 Integration Services (SSIS) into a real extraction, transformation, and loading (ETL) system? In this webcast, we present design patterns for building an application that is maintainable, auditable, and scalable to populate your dimensional Kimball Method data warehouse and Microsoft SQL Server 2005 Analysis Services database. Learn best practices for overall system design, for populating dimension and fact tables, and for populating the audit dimension.

http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032297072&EventCategory=4&culture=en-US&CountryCode=US

TechNet Webcast:

Getting Started with Data Mining (Level 200)

 When:  July 17, 2006

11:30 a.m. to 12:30 p.m. Pacific Daylight Time

 Presenter:  Warren Thornthwaite

Join this webcast for a comprehensive overview of data mining from a database development perspective. We begin with a discussion of the business value and uses of data mining, such as prediction and forecasting. Learn how to detect anomalies, and how to recognize scenarios for which Microsoft data mining technology is best suited. Using a typical business-driven approach to data mining, we show how to identify data mining opportunities, and cover the practical elements needed to make it work well, such as data preparation, model building, and validation. We then examine the output, consider different implementation methods, and conclude with recommendations on how to maintain your data mining solution.

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032297086&Culture=en-US

New SSIS GotDotNet website...

There's a new GotDotNet site dedicated to Integration Services and it already has some cool content.

Check it out!

http://www.gotdotnet.com/codegallery/codegallery.aspx?id=042f5bda-78c6-4c94-a68e-c1917b036db3

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

Writing contents of a variable to a file

In this blog I showed how to read the contents of a file into a variable.

Recently a customer asked how to do the opposite. He needs to query an SQL table with an XML result and write the results to a file.

Here's how:

1. Drop an Execute SQL Task onto the designer.
2. Double click to open the task.
3. Set result set to XML
4. Set connection type to whatever type you wish to use
5. Create or use an existing connection, I'm using the AdventureWorks sample database.
6. Set SQLSourceType correctly. In this case, I used Direct Input
7. Set the SQLStatement. The following is an example:
   1. SELECT     CurrencyCode, Name, ModifiedDate
      FROM          Sales.Currency
      FOR XML AUTO
8. Select the Result Set node on the left of the TaskUI
   1. Click "Add" and set the Result Name to zero (0)
   2. Set the Variable name to the one you want to contain the XML

That's it for the Execute SQL Task.

Now, to get the resulting XML into a file, do the following:

1. Drop a Script Task onto the designer.
2. Open the Task UI by double clicking
3. In the Script node, add the name of the variable that holds the XML you set above in the Execute SQL Task to the ReadOnlyVariables. I used RESULTSXML
4. Write a snippet of code to write out the contents into a file. I've hardcoded everything to keep it simple. Don't do that in production code.

5. Public Sub Main()

   Dim errorInfo As String = ""

   Dim Contents As String = ""

   Contents = Dts.Variables("RESULTSXML").Value.ToString()

   WriteVariableContents("C:\\TEMP\\resultsxml.xml", Contents)

   End Sub

   Public Sub WriteVariableContents(ByVal filePath As String, ByVal contents As String)

   Dim objWriter As IO.StreamWriter

   Try

   objWriter = New IO.StreamWriter(filePath)

   objWriter.Write(contents)

   objWriter.Close()

   Catch Ex As Exception

   MsgBox(Ex.Message)

   End Try

   End Sub

That's pretty much it in a nutshell.

HTH

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

BI and EIM at Microsoft

I was excited to read Bill Gates' vision around BI and EIM for the next ten years as articulated in his latest executive mail entry.

It represents a comprehensive, orchestrated and unified strategy for building infrastructure and tools enterprises need in the information age.

I found his comment on the data store silo problem particularly compelling:

"One of the biggest barriers to information access in the enterprise is the fact that data is often stored in so many different repositories. This leads to painfully inefficient processes that force information workers to leave one application, logon to another, find a single piece of data and write it on a piece of paper, and then return to their original application, just to complete a simple task like sending an email to a customer. This is a significant drag on productivity. Microsoft’s goal is to deliver enterprise information access solutions that present information workers with a single, unified way to get at the information they need no matter where it resides without leaving the application they are currently working in so they can make smart decisions and take action with greater speed."

http://www.microsoft.com/mscorp/execmail/

Thanks

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

What's happened to this blog...

Well, it's been a while since I've blogged. And for good reason. The last six months, and in particular, the last three have been extremely busy and difficult. For the most part, if I was doing any writing, it was on my forthcoming book. Yes, it's done. Now it's simply a matter of letting the publisher machinery churn. I suppose, like anyone who has ever written a book, especially a technical reference, I've had some real disappointments, some trying times and some highs. I won't bore you with them all, but just to say that writing a technical reference is extremely difficult. The writing isn't so hard, in fact, it was pretty easy. I just had to poor out my brain into the chapters. The difficult part is the time and the things you have to give up to make it happen. In the time I took to write the book I missed essentially 2.5 child years out of my children's lives. That's the tough part.

On the bright side, I truly believe that I've been able to provide some real value to readers. Time and time again I've seen questions about the product that are answered by the book. There are roughly 40 sample packages, 7 custom tasks in various degrees of completeness including the expression task that let's you modify the value of a variable with an expression and an encryption task with a stock task UI that let's you encrypt/decrypt text files, 4 custom components including a JPEG metadata extractor source adapter, data profile transform and two ADO.NET destination adapters. There are two configuration utilities. One for editing configurations in a package directly and one for bulk editing a configuration in multiple packages simultaneously. All have the source code available. There are some especially helpful chapters on configurations, tuning the dataflow task, SSIS security and writing custom components. Although the original concept and table of contents for the book was much more expansive, I believe that the final results address the lionshare of topics that people really want to know. I hope you enjoy the book and that it helps you get the most out of Integration Services. You can pre-order the book here:

http://www.amazon.com/gp/product/0672327813/ref=sr_11_1/002-5741340-2320813?%5Fencoding=UTF8

Another factor in my recent recording reticence is a role change. I've taken a position in the Office Business Applications group. This is a great opportunity to help continue the emphasis on Business Intelligence at Microsoft. While my primary focus has been Integration Services for the last 5 years, this new role will allow me to spread my wings a bit and impact a wider range of products. My current project is not announced and probably won't be for some time, however I will be focusing more broadly on the full Microsoft BI Suite of tools and future entries you find here will likely reflect that. I still have a number of SSIS blogs that I want to get around to, but I'll also be tackling other subjects such as BSM, BPM, UDM, AS, MDM, EIM and other such TLAs.

Well, that's enough blathering from me for one night.

Thanks,

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden