Search results matching tags 'Business Intelligence' and 'SQL Server'

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

The Data Scientist

BuckWoody — Tue, 15 Nov 2011 15:00:18 GMT

A new term - well, perhaps not that new - has come up and I’m actually very excited about it. The term is Data Scientist, and since it’s new, it’s fairly undefined. I’ll explain what I think it means, and why I’m excited about it.

In general, I’ve found the term deals at its most basic with analyzing data. Of course, we all do that, and the term itself in that definition is redundant. There is no science that I know of that does not work with analyzing lots of data. But the term seems to refer to more than the common practices of looking at data visually, putting it in a spreadsheet or report, or even using simple coding to examine data sets.

The term Data Scientist (as far as I can make out this early in it’s use) is someone who has a strong understanding of data sources, relevance (statistical and otherwise) and processing methods as well as front-end displays of large sets of complicated data. Some - but not all - Business Intelligence professionals have these skills. In other cases, senior developers, database architects or others fill these needs, but in my experience, many lack the strong mathematical skills needed to make these choices properly.

I’ve divided the knowledge base for someone that would wear this title into three large segments. It remains to be seen if a given Data Scientist would be responsible for knowing all these areas or would specialize. There are pretty high requirements on the math side, specifically in graduate-degree level statistics, but in my experience a company will only have a few of these folks, so they are expected to know quite a bit in each of these areas.

Persistence

The first area is finding, cleaning and storing the data. In some cases, no cleaning is done prior to storage - it’s just identified and the cleansing is done in a later step. This area is where the professional would be able to tell if a particular data set should be stored in a Relational Database Management System (RDBMS), across a set of key/value pair storage (NoSQL) or in a file system like HDFS (part of the Hadoop landscape) or other methods. Or do you examine the stream of data without storing it in another system at all?

This is an important decision - it’s a foundation choice that deals not only with a lot of expense of purchasing systems or even using Cloud Computing (PaaS, SaaS or IaaS) to source it, but also the skillsets and other resources needed to care and feed the system for a long time. The Data Scientist sets something into motion that will probably outlast his or her career at a company or organization.

Often these choices are made by senior developers, database administrators or architects in a company. But sometimes each of these has a certain bias towards making a decision one way or another. The Data Scientist would examine these choices in light of the data itself, starting perhaps even before the business requirements are created. The business may not even be aware of all the strategic and tactical data sources that they have access to.

Processing

Once the decision is made to store the data, the next set of decisions are based around how to process the data. An RDBMS scales well to a certain level, and provides a high degree of ACID compliance as well as offering a well-known set-based language to work with this data. In other cases, scale should be spread among multiple nodes (as in the case of Hadoop landscapes or NoSQL offerings) or even across a Cloud provider like Windows Azure Table Storage. In fact, in many cases - most of the ones I’m dealing with lately - the data should be split among multiple types of processing environments. This is a newer idea. Many data professionals simply pick a methodology (RDBMS with Star Schemas, NoSQL, etc.) and put all data there, regardless of its shape, processing needs and so on.

A Data Scientist is familiar not only with the various processing methods, but how they work, so that they can choose the right one for a given need. This is a huge time commitment, hence the need for a dedicated title like this one.

Presentation

This is where the need for a Data Scientist is most often already being filled, sometimes with more or less success. The latest Business Intelligence systems are quite good at allowing you to create amazing graphics - but it’s the data behind the graphics that are the most important component of truly effective displays.

This is where the mathematics requirement of the Data Scientist title is the most unforgiving. In fact, someone without a good foundation in statistics is not a good candidate for creating reports. Even a basic level of statistics can be dangerous. Anyone who works in analyzing data will tell you that there are multiple errors possible when data just seems right - and basic statistics bears out that you’re on the right track - that are only solvable when you understanding why the statistical formula works the way it does.

And there are lots of ways of presenting data. Sometimes all you need is a “yes” or “no” answer that can only come after heavy analysis work. In that case, a simple e-mail might be all the reporting you need. In others, complex relationships and multiple components require a deep understanding of the various graphical methods of presenting data. Knowing which kind of chart, color, graphic or shape conveys a particular datum best is essential knowledge for the Data Scientist.

Why I’m excited

I love this area of study. I like math, stats, and computing technologies, but it goes beyond that. I love what data can do - how it can help an organization. I’ve been fortunate enough in my professional career these past two decades to work with lots of folks who perform this role at companies from aerospace to medical firms, from manufacturing to retail.

Interestingly, the size of the company really isn’t germane here. I worked with one very small bio-tech (cryogenics) company that worked deeply with analysis of complex interrelated data.

So watch this space. No, I’m not leaving Azure or distributed computing or Microsoft. In fact, I think I’m perfectly situated to investigate this role further. We have a huge set of tools, from RDBMS to Hadoop to allow me to explore. And I’m happy to share what I learn along the way.

PASS Workshop “Creating a BI solution from A to Z” - Interview

manowar — Thu, 16 Sep 2010 22:16:27 GMT

If you’re still unsure if the workshop I’ll deliver at PASS Summit the next November - “Creating a BI solution from A to Z” - is a good choice for you or not, you can get some more details reading the brief interview here:

PASS Summit Post-Con Preview - Davide Mauri

Where you’ll find answers to the following questions:

Is there an audience that would benefit especially from this session?
After having attended your seminar, what are two or three things that an attendee will be able to take back to the office and put to use right away?
What background should attendees ideally have to be fully prepared for your seminar?
What experience are you, as a speaker, bringing to this session?

That may help you to take your final decision. More than 20 people already decided to come: you’ll be in good company! :)

Back from PASS Europe 2010

manowar — Sun, 25 Apr 2010 11:24:04 GMT

PASS Europe 2010 is finished and I’m now finally back at home and will stay here for a while.

I would like to thanks all the people who has come to my sessions for all their feedback, especially for the “Adaptive BI” session!

Slides and demos should be available for download from the PASS European Conference website in a couple of days.

Meanwhile if you want to rate my session online, you can do it here:

Adaptive BI
http://speakerrate.com/talks/3136-adaptive-bi-best-practices

Blazing Fast Queries
http://speakerrate.com/talks/3135-blazing-fast-queries-when-indexes-are-not-enough

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

SET IDENTITY_INSERT little bug(?)

manowar — Wed, 30 Sep 2009 05:52:22 GMT

While I was working on a package to incrementally load a dimension in the DWH I’m developing for a customer, I stumbled upon a tricky little bug (I think, I still have to have a response from MS) that had – and will have – a little impact on how I create my packages.

I’ve found that, when working on an empty table that has an identity column, if you insert a value forcing the identity value, the next time SQL Server will generate the identity value, it will generate it missing one number. So, for example, you’ll have

0	(Forced Identity Value)
2
3

instead of having the correct sequence

0	(Forced Identity Value)
1
2

Not a huge problem as you can see, but I’d like to see it fixed, since can cause little troubles if your forgot about it and you expect to have predictable identity values. I know that identity values are usually not 100% predictable, but I find them useful to speed-up SSIS package development for loading dimensions tables while keeping package complexity low. Since I apply Agile Principles to BI, simplicity is fundamental, since it allows me to change package quickly, easily embracing changes that comes from refactoring or from customer new needs.

You can find the connect item to vote here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=492452

SSIS 2005 on Windows 7

andyleonard — Mon, 28 Sep 2009 11:00:00 GMT

Introduction

I recently upgraded from Vista Ultimate (x64) to Windows 7 Ultimate (x64). So far I really like Windows 7, but I'm still in the honeymoon phase. I ran into an issue creating new packages using SSIS 2005 and found a solution I thought I'd share.

The Error

Unfortunately for both of us, I did not capture a screenshot of the error. I can tell you it occurred when I created a new SSIS project or attempted to add a package to an SSIS project. The text of the message read something like:

"Cannot create package" and " failed due to the following error:"

Now I know on the surface the text above is utterly meaningless. My fervent hope and desire is that search engines will catalog this page so people encountering the same error can find it when searching. Because frankly, I didn't find anything online that helped me figure this one out - it was all trial and error and, in the end, dumb luck that worked for me.

If you encounter this error, please capture a screenshot and/or the text of the error message and leave a comment to this post. I promise I will contact you and update this post so it's more accurate. Since I fixed it, I've been unable to reproduce the error.

The Solution

Run As Administrator. Simple, eh? Like searching for my car keys, the solution was in the last place I looked.

To set this permission on the Business Intelligence Development Studio application, click the Windows button and navigate to the All Programs\SQL Server 2005 folder. Right-click the Business Intelligence Development Studio object and click Properties:

Once Properties display, click the Advanced button on the Shortcut tab:

When the Advanced Properties dialog displays, check the "Run as administrator" checkbox (NOTE: This assumes you are a member of the local Administrators group on your workstation):

Click the OK button to close the Advanced Properties dialog. You will be prompted to confirm these changes. If you are a local administrator on your workstation, you can click the Continue button and move on. If not, you will need a member of the local Administrators group to login to your workstation and validate this change:

Click OK to save the changes to Properties.

After I made this change to my shortcut, I was able to create SQL Server 2005 Integration Services packages on my Windows 7 machine.

:{> Andy

SQLPass 2008: The PASS Summit - Day Two

andyleonard — Tue, 18 Nov 2008 23:15:00 GMT

Introduction

"Hey Andy? What happened to your Day One post?" Excellent question. I was going to post a Day One post - really I was - but I got side-tracked. I won't mention any names, but I will blame the major offenders by their Twitter handles: @BrentO, @sqlagentman, and @SqlBatman.

These gentlemen forced me - through coercion and peer pressure (you teenagers take note) - into joining them Sunday evening at a cool little bar named Zig Zag near the market in Seattle. Then they forced me to drink beer, which cut into my Day One Blogging Time that I had dutifully scheduled on my calendar. It was horrible, but I am willing to bear these burdens to bring those who cannot attend the PASS Summit 2008 information about what's going on here.

You're welcome.

Volunteer Training

If you're involved (or want to be involved) in a SQL Server User Group, the Volunteer Training is for you. It is awesome. Members of the PASS Board and Executive Committee explain what they do and why they do it. Earlier today, Kevin Kline presented a history of PASS as an organization. It was refreshing and honest.

Rushabh Mehta explained the finer points of financing an international organization that executes a couple large conferences per year.

Others explained their contributions and Areas of Responsibility, reporting on successes and challenges alike. It was enlightening and served to deepen my appreciation for the character and work ethic of these folks. Kudos to all.

Social and Social

There is a social aspect to the PASS Summit. I get to see folks I only communicate with via email most of the year. It's cool. I've met people for the first time that I've been communicating with using social networking tools, primarily (for me), Twitter.

Someone (who will remain nameless but not linkless) posted about some session on SSIS Scripting at PASS and started, well, a stir - at least on Twitter. Jessica M. Moss and I are presenting SSIS Scripting, but we're using a Twitter class to demonstrate the core concepts. In brief, our packages tweet.

But that's not the cool part. We'll write about the cool parts later. I promise. In the meantime I'll give you a hint: Social Intelligence.

Erik Veerman's Pre-Conference Day on Building Scalable Solutions

If you've never heard Erik Veerman present on Business Intelligence, you have missed out. Erik is not only a really smart guy, he communicates well. I'm jealous, actually, of how well he speaks. He's from Atlanta. Despite that, people understand him. Go figure.

His session ranges from the individual pieces and parts of Business Intelligence - the database, Analysis Services, Integration Services, and Reporting Services - to how to leverage the individual strengths of these products to produce a scalable solution.

Why scalable solutions? What's the big deal? Scalability sinks more business intelligence (and database-related) applications than you imagine. One reason is it's difficult to predict how and where a solution will scale. If you have a single table, it's pretty easy to predict scalability. Add a table and you cut your odds in half. It gets worse as you go.

This is a fantastic presentation and Erik is quick on his feet - drawing from years of experience architecting scalable BI solutions (ever heard of Project REAL? Erik was involved) to answer questions from the crowd.

In Conclusion

There's more to blog about throughout the week. SQLBlog.com is an official blog site for the PASS Summit 2008, and Yours Truly is assigned to cover the Thursday keynote.

:{> Andy