Categories
Business Intelligence SQLServerPedia Syndication

SQL Server Management Studio and Excel – Column Headings on Copy/Paste

Here is a tip about a setting that I think should be on by default, but it isn’t. How many times are you writing T-SQL in SQL Server Management Studio (SSMS) and you need to just copy the results out, paste to Excel and either do more analysis or send off to someone. What happens when you have a result set with 20 columns? 30? 50?

Well, the old way to get the column headings in Excel was to just type them in. Ouch. This gets old realllly fast. But there is a setting in SSMS that most people don’t even know exists, and then once they find out about it, they are like, dang! I wish I would have known about that.

Well, fire up SSMS, Tools->Options, Query Results, SQL Server, Results to Grid. Then check the “Include column headers when copying or saving the results” Hit OK, and the any new query window you have will automatically copy the column headings from your result set with the data!

 

image

Categories
Business Intelligence SQLServerPedia Syndication

SSIS – Pulling Data from a non default collation DB to a default collation DB

So, you install SQL, and if you are in the US, you 99.9999% of the time install it with the default collation, or language, or whatever. US English, Codepage 1252, etc, etc Latin blah blah…

Then, you are tasked with pulling data from somewhere else, but it happens to be a different language or collation than your destination, what do you do?

In SSIS, there is a setting you can change on your OLEDB source and OLEB destination to make sure it works. The default codepage is 1252, but if you try pulling data from a non default DB, it will error out all over the place. If you change the setting “AlwaysUseDefaultCodePage” from false (the default) to true, on both your source and destination, then it should work.

image

Another thing to assist with this once you have a bunch of SSIS packages with these non-default settings changed (not just the one I describe above, but any setting) – if you install BIDs Helper, and then right click on the package, you can get a non-default setting report, which is pretty awesome actually, especially if you inherit any packages and want to know quickly what default settings have been tweaked.

Categories
Product Reviews

Book Review: The Cult of the Amateur by Andrew Keen

Recently, Amazon released their Kindle application for the iPhone. Since I don’t have a Kindle (but I want one!) I figured this would be the next best thing. They have this technology in the app called WhisperSync which sync’s up the page you are reading, so if you do have a Kindle, you can switch between that and the iPhone app and pick right up where you left off.

I have had the Kindle app for a few weeks now, but have just read samples on it (you can send samples of books from the Amazon site to your iPhone). Now, whenever I hear of some book that might sound interesting, I will note it in the iPhone, and then later go to Amazon and check it out. One of these books I heard about recently was “The Cult of the Amateur: How Today’s Internet is Killing Our Culture” by Andrew Keen


I heard Mr. Keen on an NPR program talking about his book, and it piqued my interest. First off, let me say that reading the book on the Kindle iPhone app rocks. I read this book in like 3 days just reading it here and there when I had some free time, just pulled up the iPhone and started reading, very slick.

Now, about the book. He goes into detail about how Wikipedia, Illegal movie and music downloads, blogs, YouTube, Google, User Generated Content, remixes, mashups, etc, and everything Web 2.0 (and even Web 1.0 – in my opinion sometimes he just blur’s the distinction) is killing our minds, and media, and jobs, and culture, and everything else.

I got the feeling while reading this, imagining an author back around the time cars started to get popular, but horses were still on the roads, where the author is complaining about autos and transportation using them, arguing we need to save horses as the method for transport.

If newspapers, local tv news, magazine, artists, etc don’t want to keep up with changing technology, then in my eyes they almost deserve to fail. He refers to Beethoven and Mozart and how they would never use the methods today to distribute their works, etc. It seems that there is this same arguement over and over. I can just see/hear it: “Sheet Music is killing the ability to play by ear!”.. “Radio is killing Sheet Music!”.. “Vinyl Albums are Killing Radio”.. “8 Tracks are killing Vinyl” (ok, I am joking on that one).. “Cassettes and CD’s will kill Vinyl”… “Mp3’s are going to kill CD’s!”..

Wait up. The part I didn’t add on all those quotes was this . ” and the artists suffer”, yet the artists always continue to survive. Its the fat cats, the middlemen who end up losing out. The publishers and then go betweens that need to change their models and they just don’t adapt to change fast enough, or do they want to change. It seems that they just want it how it is right now, and everything will be fine, and I guess I disagree.

Wikipedia, YouTube, all the mashups – they release creativity. What you as a user of these services need to realize is that you need to take everything at face value. You shouldn’t take Wikipedia as gospel, it is up to you to know that. Most people don’t do that though, and thats the problem. Instead of blaming the services and the content, how about we look at ourselves?

Ok, I could keep ranting on every part of this book in the same manner, but I will stop. It is a good read, gives another perspective of the “Web 2.0 Revolution” (as I roll my eyes – it isn’t a revolution at all, its an evolution – things will always change).

In the end ,it just seems as the old media and the old ways of doing things are trying to cling on to anything they can to try to make it stay the way it was, but that just isn’t going to happen. With everything, you CHOOSE to be a part of it or not. You don’t need to use Google, or the internet or anything, and you will be just fine, but if you do choose, then you play by the rules (or non-rules) of the net, plain and simple.

So, if you do have an iPhone, I would gladly recommend checking out the Kindle app from Amazon and start reading some more books (And if you have a Kindle as well, even better!)

The Cult of the Amateur: How blogs, MySpace, YouTube, and the rest of today’s user-generated media are destroying our economy, our culture, and our values


Categories
Blogging Geeky/Programming

Twitter or Yammer?

Recently I have been using Yammer more often than Twitter, but they both have their place. You probably know what Twitter is as it has been going mainstream like crazy the last month or two. Yammer on the other hand is less known. Yammer is sort of like Twitter, but just for your organization.

What Yammer brings, besides being just internal to your place of work, are other enhancements. Groups, for one, is huge. I can create a group say for “Microsoft SQL Server”, and anyone on Yammer in my place of work can join that group and share, discuss, and consume anything from that group. Instead of me sending out emails from articles and tips, people can just subscribe to them.

Also, departments can create groups , private groups, to have discussions just within their dept.

Yammer also has an iPhone app, and a Adobe Air Desktop App (I think I heard TweetDeck integration soon if not already), and it uses SMS message as well if you want, and also you can get daily digest emails from your groups and people you follow.

I would suggest groups of employees that are on Twitter going back and forth, take a look at Yammer for internal needs. What I try to do is save anything for Twitter that is more applicable to everyone, and Yammer for things I just want to share with people I work with.

On that note as well, I separated out my Twitter and Facebook feeds, and am going to try to use Facebook for more non-technical status updates.

So,

Yammer = company/corporate/internal groups

Twitter = colleagues outside of the company, and people I find interesting in my same line of work, etc

Facebook = family, friends, etc

Categories
Life

Instant Messaging

I have been using IM since 1995/1996 starting with “procomm plus” then ICQ (my number is in the first 600,000)

Now there are multiple clients, windows live, yahoo, aim, gtalk, facebook, myspace, icq, and more. Take all the IM programs, and the multi clients, trillian, digsby, pidgen. meebo, etc.; I have used them all. Nothing seems good enough to me. Adium on the mac is the best I have used so far.

What I am trying to do now is consolidate. I am going to try to just use google talk with my steve@stevienova.com account and will see how that goes. I will still keep my other accounts but probably won’t sign in as often. This is an experiment so we will see how it turns out.

Feel free to add me and chat/IM !!

* this post was written from the WordPress iPhone app

Categories
Business Intelligence Work

Sr. Technical Business Analyst position at Trek Bicycle Corporation (Waterloo, WI)

We are again looking to fill out our Business Intelligence team at Trek Bicycle Corporation. What I am looking for with this position is someone who is both technical and has the business analyst skills needed to work with end users, gather requirements, etc. You can read more about the position here:

http://www.trekbikes.com/us/en/company/careers/post/98/sr.+technical+business+analyst

Feel free to email your resume (make sure to say that you found this through my blog!) to my work email steve_novoselac@trekbikes.com

Categories
Blogging

Blogging in 2009

I have been looking through some of my Google Analytics logs, and I really find it funny. The top 3 posts on my blog really don’t have anything to do with stuff I am really into, but they are more things that I did in my spare time and blogged about.

1) Linux on my Ps3
2) iSight Camera Drivers on Macbook/Parallels
3) Ruby on Rails and MySQL on Vista

So yeah, I don’t really do any of that stuff “full time”. Even looking back over my blog posts, and seeing how things have changed since 2004. Talking about ASP development, SQL 2000, ASP.NET, C#/VB.NET, C++, BI, Other tech stuff. Fun stuff.

Now that I am doing Business Intelligence full time, but in more of a manager role than a “doing” role, I usually don’t fall into crazy development debacles or solutions that require a “blog” to explain, or a “blog” to save the next sorry sucker who comes across the setup or crazy steps I had to take to get something to work.

That, coupled with Twitter, and Facebook, and Yammer, and whatever else, blogging takes a back seat. But I still think there is a good time and place for blogging. You make it what you make it. You can use it as log to look back on (I do, I search on stuff I blogged all the time, instead of having to remember it), or just a good way to keep people up on what you are doing, in more than 140 characters.

Where do things go from here? I am sure there tons of things coming down the pipeline that I will be blogging about. SQL 2008, Sharepoint, Excel Services, More BI stuff (SSIS, SSRS, SSAS), Cloud Computing, iPhone, Mobile, Gaming, just good old plain geekery. And don’t forget lil Ella, and music and much more.

I have mentioned before that my blogging has also changed dramatically since I started using Mac’s full time at home. It makes it tough to get a “full solution” into a blog post. I have tried with VM’s but I just get too distracted 🙂

I have found that I get more though, out of reading and just consuming as much info as I can. Books, eBooks, Blogs through Google Reader, Twitter, etc. Still subscribing to 250-300 blogs, the cream of the crop, and I add more every day, mostly BI blogs these days. Unsub’d from most of the .NET Dev blogs since I Just am not into that as much any more. Things like that. I’d rather learn more than just regurgitate or create some kind of “echo chamber” here.

Blogging in 2009 is much different than 2008, or 2004, or whenever, and I am sure it will evolve. It should be fun! and I hope to embrace the changes that will come in the upcoming years.

Categories
Business Intelligence

Book Review: Information Dashboard Design by Stephen Few

A couple of months ago, a colleague lent me Information Dashboard Design: The Effective Visual Communication of Data by Stephen Few.


What a great book. I read it in one afternoon. It goes through a bunch of different software systems you can buy or implement in regards to Business Intelligence and Dashboards, KPI, Reporting, etc. It explains why most are worthless, and don’t convey information in the best way. Finally at the end it explains some great ways to convey the most information visually to the end user of your dashboard.

It talks about things like sparklines and bullet charts. It goes into why 3D, Thermometers and Gauges are pretty much the worst things you can add to a dashboard.

After reading the book, I was intrigued by the possibilities of getting information out to people in better way. Bullet graphs/charts, sparklines, well how? They don’t have them in Excel.

I then stumbled upon xlcubed.com, and MicroCharts – http://www.xlcubed.com/en/

It’s an add on type product that allows you to create bullet charts and sparklines and share excel over the web (kind of like Excel Services). Looks promising.

I am also reading a book about Information Dashboard Reporting in Excel 2007 and they actually walk you through on how to create bullet graphs from scratch in Excel, pretty sweet.

If you are sick of some of the limitations of SSRS, sick of some built into dashboard products that you might have used, and also want to display your information visually to your end users, instead of just raw data, then I would say read this book, right away, and go from there,


Categories
Business Intelligence

Business Intelligence != Reporting

Last week I was in a heated discussion about Business Intelligence, and it came up that “Business Intelligence is just writing reports, what is the difference from just writing some reports?”

I tend to disagree. Writing reports is, well, writing reports. You can write reports off of tons of data, and yeah, probably get some good info, but usually it ends up in disparate information.

I have blogged about this before, probably a few times, but this is usually what happens in organizations:

1) Some app or system is set up where data is created in some kind of data store, usually a database

2) People want to see the data in some type of report

3) Developers start cranking out reports right off the source system, stopping the bleeding for the current need for more reporting.

4) System keeps growing, more data is created/collected.

5) Reports off source system start slowing down, timing out, many different reports end up for the same thing and don’t match, end users complain.

6) Developers get clever, start moving and summarizing data on some kind of interval, all with custom code, custom design. They write some reports off the summary data to once again stop the bleeding. Some reports are still off the source data. Reports don’t match. End users complain

7) More data is collected, more users want reports, and want them with different views, grouped by this, sorted by that, add this column, remove this column, etc.

8) Developers are taking the heat. Their reports aren’t matching, they are running slow, etc. They can’t keep up with all the report requests. They decide to create a custom reporting engine on top of their summary data that allows end users to create their one reports using some quasi query language and drag and drop columns/grouping. This stops the bleeding for a while.

9) Finally someone in the organization realizes that the developers are reinventing the wheel. This is where Business Intelligence comes in. The source data is left untouched, the BI group creates and ETL to have some kind of data warehouse design and structure, using out of the box tools. (SQL Server Management Studio is and out of the box tool – you use that to write queries. SQL Server Integration/Analysis/Reporting services are out of the box tools, you use them to create ETL’s, Cubes, and Reports).

10) End users can now write reports using Excel off the OLAP Cube, or they use the Report Builder with Reporting Services to create reports off a UDM (Universal Data Model), or the BI devs create reports in Reporting Services, all off the same source data. One version of the truth, less custom reporting.

11) Developers are left to develop on the source system turning requirements into code/features, not focus on reports, and the BI group is focused on getting all the data turned into information.

Of course this is a perfect scenario described above. Nothing is ever as simple as that, but we can hope. Also I am biased towards the MSFT toolset, but there are other toolsets out there that would solve the same problem.

All I know is that I have seen everywhere I go pretty much the same thing happen from #1 to #8 above. In some cases you never see #9-#11 and you end up in a world of hurt.

In the end though, Business Intelligence just isn’t “writing reports”, far from it.


Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS Cube Rule #1 – YOU NEED CALCULATE

Ok, I just spent a few hours debugging a cube that wasn’t bringing back any data. Checking all the relationships, the source data, the marts, etc, etc. And lo and behold, find out that a colleague commented out all the calculations, including the CALCULATE statement which says:

The CALCULATE command controls the aggregation of leaf cells in the cube.
If deleted or modified, the data within the cube will be affected.
This command should only be edited if you intend on manually specifying how the cube will be aggregated.

Remember, you NEED the “CALCULATE” command!