Category: Geeky/Programming

Categories
Geeky/Programming

iTunes DJ – iPhone, Remote App – Party!

Not sure when this came about, but I just realized that there is an “iTunes DJ” in iTunes now. It obviously lets you just play a random selection it picks. But what I didn’t know, is allowing anyone on your wifi network to connect with their iPhone and the Remote App. Once you connect with the Remote app, you can browse the library, and request a song. Multiple people can request a song, and vote it up. Totally awesome!!



I also pump out the output from my MacBook to the MacBook and AppleTV through iTunes so I get total surround sound.

So the Apple Remote app, iPhone, iTunes DJ = ultimate geek iPhone party! Who’s game?


Categories
Geeky/Programming

Bookmark Bar – Uber Micro Sharing

I use pretty much every browser. I switch between them depending on mood, system I am on, phase of the moon, etc. But there are some essential “bookmarklets” that I always want to use/set up.


There are a few things I wish I could tweak though. Like with Yammer, the ability to choose a group before it hits the page. Also with the Yammer bookmarklet there is about a 50/50 chance the URL/title wont come through so you have to do it manually.

With the Gmail This one, on Safari, it opens in a tab instead of a popup, and loading Gmail is kind of slow (IMHO) when you want to just write a mail from out of no where.

What bookmarklets do you use? Are you a share-a-holic?


Categories
Geeky/Programming

One Year Ago Today…

One Year Ago Today, I bought my MacBook Pro. And guess what? It is still running, on the original config, original boot, no reformatting, no repaving, no major surgery, nothing. And it still runs great. I have a ton of apps installed, but just enough, just what I need.

What is funny though, is that using VMWare Fusion, I have reformatted or redone my Windows VM’s multiple times because of issues. Is that just how things are? I think so.

This is probably the longest time EVER that I have had a machine without reformatting it. I think back when I was eleven years old, like 1991, reformatting our 386 25Mhz Packard Bell every 6 or so months with Windows 3.11

I have to say, the MacBook Pro is an awesome machine. Every time I use it, I wish I could just run a Windows VM for work on it, off a USB drive, just take it back and forth, and that is one less physical machine I would have to worry about.. someday..

What is the longest you have went without reformatting, voluntary or involuntary?


Photo Courtesy of Alistair Israel


Categories
Geeky/Programming Life

New Bike, iPhone, RunKeeper

Since working at Trek, I have been hounded to get a new bike, even more so than by my friends in Portland when I lived there, and EVERYONE has like 5 bikes there.

So, I finally decided to pick up a new bike, a Trek 7.2 FX, Newport Blue, 22.5 inch. It’s a Hybrid – not a road bike, not a MTB, but somewhere in between.

Some pictures are here, of the bike building process, because when you work at a bike company, you take 25 minutes of out of the day to build a bike.



http://www.flickr.com/photos/scaleovenstove/sets/72157619164047726/

So after getting my bike, I had to get some more gear. The usual, helmet, lights, pump, bag, tools, all that stuff. Most other people will get a “computer” for their bike, a little electronic device to track speed, etc. Guess what? I already had one, it is called an iPhone (what doesn’t the iPhone do.. “there’s an app for that”.. is true.)

I jumped on Amazon and looked for a good armband for the iPhone 3G, with good reviews. This is the one I ended up with

So what other parts are there to this equation? The app of course. RunKeeper Pro. (http://www.runkeeper.com) It lets you set your activity type, you start the music before you start RunKeeper, lock the GPS on, and start your activity. You can “lock” the screen so you dont bump it, and then tap it to get pace/speed info. It will also tell you every 5 minutes and/or every mile what your speed/pace is. Once you are done with your activity, you upload it to their site. It maps it out, elevation, speed, nice google map interface. Aggregates miles over weeks/months, and lets you share with your friends.

Here was a ride I did the other day, http://www.runkeeper.com/ui/activities/1000402


RunKeeper really keeps you motivated. I haven’t used Nike+, but I am guessing it is pretty similar. But Nike+ doesn’t work on the iPhone, just iPod Touch.

I really like my bike, there was a Gary Fisher I had my eye on though, so maybe that will be my next bike. Using my iPhone and RunKeeper, I can keep track of my rides, miles, and share with my friends (which kind of helps you stay motivated too). Fun Stuff – Go By BIke!


Categories
Geeky/Programming Product Reviews

Pen Tablet Blog Post

Today, I picked up a Bamboo Fun Pen tablet by Wacom.

image

As you can see, I wrote this post using nothing but the tablet, pretty sweet!

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

ETL Method – Fastest Way To Get Data from DB2 to Microsoft SQL Server

For a while, I have been working on figuring out a “better” way to get data from DB2 to Microsoft SQL Server. There are many different options and approaches and environments, and this one is mine, your mileage may vary.

Usually, when pulling data from DB2 to any Windows box, the first thing you might think of is ODBC. You can either use the Microsoft DB2 driver (which works, if you are lucky enough to get it configured and working), or the IBM iSeries Client Access ODBC Driver (which works well), or another 3rd party ODBC driver. Using ODBC, you can access DB2 with a ton of different clients. Excel, WinSQL, any 3rd party SQL Tool, a MSSQL linked server, SSIS, etc. ODBC connects just fine, and will work for “querying” needs. Also, with the drivers you might install, you can usually set up an OLE DB connection if your client supports it (SSIS for example) and query the data using OLEDB – this works as well, but there are some caveats, which I will talk about.

In comes SSIS, the go to ETL tool for MSFT BI developers. You want to get data from DB2 to your SQL Server Data Warehouse, or whatever. You try with an OLEDB connection source, but it is clunky, weird, and sometimes doesn’t work at all (PrimeOutput Errors Anyone?). If you do manage to get OLEDB configured and working, you still probably will be missing out on some performance gains compared to the method I am going describe.

Back to SSIS, using ODBC. It works. You have to create an ADO.NET ODBC connection, and use a DataReader source instead of an OLEDB source. Everything works fine, except one thing. It is slow! Further proof?

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/162e55e5-b64b-423e-94c1-dd764ca1f683

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96977

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cfade7e7-50d5-4447-9821-35c5d5ae1b66

http://www.sqlservercentral.com/Forums/Topic702042-148-1.aspx

http://www.sqlservercentral.com/Forums/Topic666993-148-1.aspx

Ok, enough links. But if you do read those. SQL 2000 DTS is faster than using SQL 2005/2008 SSIS. WTF? The best I can guess is that it is because of the .NET wrapper around ODBC. DTS is using “native” ODBC.

So, now what? Do we want to use DTS 2000? No. What to do though?

Well, after a few days of research, and just exploring around, I think I have found a good answer.. Replace DB2 with SQL Server.. just kidding. Here is what you need to do:

Install the IBM Client Access tools. There is a tool called “Data Transfer From iSeries Server” which the actual exe is "C:Program FilesIBMClient Accesscwbtf.exe"

image

This little tool allows you to set up data transfers from your DB2 system to multiple output choices (Display, Printer, Html, and Text). We want to export to Text file on our filesystem. You have to set up a few options, like the FileName, etc. In “Data Options” you can set up a where statement, aggregates, etc.

If you output to a file, you can go into “Details” and choose a file type, etc. I use ASCII Text, and then in the  “ascii file details” I uncheck all checkboxes. You set up your options and then hit the “Transfer data from iSeries” button and it will extract data to the file you chose in the filename field. Pretty sweet. But this is a GUI, how can I use this tool? I am not going to run this manually. Well, you are in luck.

If you hit the “Save” button, it will save a .dtf file for you. If you open this .dtf file in a text editor, you will see all options are defined in text, in a faux ini style. Awesome, we are getting somewhere.

Now, how do you run this from a cmd prompt? Well, we are in luck again. Dig around in C:Program FilesIBMClient Access and you will find a little exe called “rxferpcb”

image

What this tool allows you to do, is pass in a “request” (aka a DTF file), and a userid/password for your DB2 system, and it will execute the transfer for you. Sweet!

Now what do we do from here?

1) Create an SSIS package

2) Create an execute process task, call rxferpcb and pass in your arguements.

3) Create a BULK Insert task, and load up the file that the execute process task created. (note you have to create a .FMT file for fixed with import. I create a .NET app to load the FDF file (the transfer description) which will auto create a .FMT file for me, and a SQL Create statement as well – saving time and tedious work)

Now take 2 minutes and think how you could make everything generic/expression/variable driven, and you have yourself a sweet little SSIS package to extract any table from DB2 to text and bulk load it.

image

What is so great about the .DTF files is that you can modify them with a text editor, which means you can create/modify them programmatically. Think – setting where statements for incremental loads, etc.

image

 

You can see from the two screenshots above, that is all there is. Everything is expression/variable drive. Full Load, and Incremental Load. Using nothing but .dtf files, rxferpcb, a little .NET app I wrote to automatically create DTF’s for incremental (where statements), truncate, delete, and bulk insert. I can load up any table from DB2 to SQL by just setting 3 variables in a parent package.

After you wrap your head around everything I just went over, then stop to think about this. The whole DTF/Data Transfer/etc is all exposed in a COM API for “Data Transfer Automation Objects’”

http://www-912.ibm.com/s_dir/slkbase.NSF/643d2723f2907f0b8625661300765a2a/0c637d6b03f927ff86256a710076ab22?OpenDocument

With that information at your disposal, you could really do some cool things. Why not just create a SSIS Source Adapter that wraps that COM object and dumps the rows directly to the SSIS Buffer, and then does an OLEDB insert or Bulk Insert using the SQL Server Destination?

I have found in my tests that I can load over 100 million row tables – doing a full complete load, in about 6-7
hours. 30-40 million row tables in 4 hours. 2 to extract, 2 to BULK insert. Again, your mileage may vary depending on the width of your table, network speed, disk I/O, etc. To compare, with ODBC, just pulling and inserting 2 million records was taking over 2 hours, I didn’t wait around for it to finish. Pulling 2 Million records with my method described in this blog takes about 3-5 minutes (or less!)

I know I have skimmed over most of the nitty gritty details in this post, but I hope to convey from a high level that ODBC/OLE DB just aren’t as fast as the method here, I have spent a lot of time over the last few weeks comparing and contrasting performance and manageability. Now, if I could just get that DB2 server upgrade to SQL Server 2008. . . Happy ETL’ing!

Categories
Geeky/Programming Life Product Reviews

Apple Time Capsule Rocks – Microsoft needs to make one.

Since I make a living and love working on Windows, SQL, Windows Server, Office, Exchange, etc, it is kind of weird I am a Mac guy. Never thought it would happen. iPhone, Mac, Macbook Pro, Apple TV, Time Capsule, accessories…

Anyways, since Ella was born, I have TONS of pics and videos of her, on my laptop. And it auto backs up to my Apple Time Capsule. Well, iMovie auto imports videos from iPhoto, and then removes them? I am not sure, but long story short, I was missing some videos from when we got home from the hospital.

So what do I do? Freak out? No.. just fire up the Time Machine on my Mac, go back to February, and there is my iPhoto Library, I restore it, and get the videos back.

Now, I just need to offload it offsite someone, there is MacMini Colo – Transport – http://www.macminicolo.net/transport/ which looks promising, just a little too much $$, but maybe my next step.

But really, Windows needs this. We have a few Windows machines here at home, and I just feel like, umm, stuff is volatile. Thank god for flickr..that is all I have to say. I know there is Windows Home Server, but that is another BOX and updates, and whatever. I just want a device, a dumb device. I know I can get NAS and whatever, but I just want simple. Where is the Microsoft Time Capsule?

Categories
Geeky/Programming

Using Windows Performance Toolkit to find System Issues in Vista/Win2k8/Win7

Windows 7 RC1 just came out. I am a TechNet subscriber, so I wanted to try it out. I have an old (2005) Dell desktop, 2.8 GHz, 2 GB ram, 160 GB drive box. 3.7 rating for Vista (because of the Graphics card mostly, would be 4.4 otherwise – not too bad, even for being kind of an old box). It has been sitting in the basement since I moved into my new place in October, doing nothing really. I use Mac full time at home, so it just sits.

A few times I have tried to get Windows Vista running smooth on it, Media Center, or just a file server,etc. Thing is, it was just flaking out. I knew it was a hardware issue, but figured it might be the CPU fan, or overheating, etc. Vista installed fine, but as I was using it, I would see just hang-ups, lockups. Not BSOD’s, but it would just hang, for 30 seconds, 1 minute, and then come back. WTF?

Nothing in the Reliability monitor, nothing I could see in event logs, etc. I rebooted, did Windows Memory test, nothing there. If you go into Computer Management, you will see Performance, then Data Collector Sets and Reports, Monitoring Tools. You can set it up to run a test on metrics of your system and it will give you a report

image

I did this, and everything was ok. BUT… Avg Disk Length Queue was > 2 – red flag. Disk issues. But I wanted to know more. So I started digging around, and there is a Windows Performance Toolkit you can download. Here is another good site going into detail about the WPT.

So I fire up cmd line (as admin! – start->run, cmd ctrl+shift+enter), and run

xperf -providers K

to see what providers are available for the Kernel flags. IOTrace looks like something I want, so I then run

xperf -on IOTrace

and let it run. I go and open/close things, play around, see if I can replicate the issue. Once I feel I have, I want to stop and analyze the trace. You need to stop it and output to a file using this command:

xperf -d iotrace.etl

Side note: Files are named ETL. Coming from a BI background, this makes my world explode, since it has nothing to do with Extract, Transform, and Load

Now that my trace is done, time to analyze:

xperfview iotrace.etl

And you get some awesome stats like this:
image

Although I didn’t save my stats from my tests that showed the bad IO, what I saw were just gaps in the graphs, glitches in The Matrix. Time missing. Something is really bad here. So I did the drive error checking in Vista:

image

And when that ran, after reboot, it got to 11% and croaked. Bad drive. So I went and bought a new 500 GB SATA drive and loaded it up, and I am running Windows 7 now. Pretty sweet.

After all this fun spelunking into Windows performance, it also got me thinking about things, like running these detailed traces on SQL Server boxes or other servers on intervals, and saving them somehow, reporting on the data. The IOTrace is just one of hundreds of traces, that you can then auto analyze. I know that there are perfmon tools but there are some added benefits to xperf that you can you utilize, and I am glad I learned more about it and put it to use, just another tool for the sysadmin tool belt.

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSIS – Two Ways Using Expressions Can Make Your Life Easier – Multi DB Select, Non Standard DB Select

In SQL Server Integration Services (SSIS), pretty much every task or transformation lets you set “expressions” up. Expressions are basically ways to set property values programmatically.

Here are two scenarios where you might use expressions (there are 100’s of uses, these are just two that are kind of related).

  1. Multiple Database Select – You have multiple databases – same schema, let’s say you have 300 installs of a 3rd party product and they all need their own database. I know it might sound impossible, but trust me, it can happen. Now, you want to run the same query over all databases, and pull data from a table, and dump into a data warehouse, for example. You could write 300 queries, and keep adding/removing based on the databases, you could create some elaborate dynamic SQL proc using loops, or you might have some other way, or, you could use SSIS Expressions.

    Now, how would you go about doing this? It is pretty easy actually. First step, you need to set up a loop in SSIS. You would want to grab a recordset of database names using an Execute SQL Task, or however you’d like, and store in an object variable. Then you can loop through that list. Your only difference in your query would be database name, so what you would do is have a variable for your SELECT statement. Name it whatever, but what you want to do is click on the variable, the properties of it. You will see Expression. Open the expression box and then set it to something like this

    ”SELECT Col1,Col2,Col3 FROM “ +  @[User:CurrentDatabaseName] + ".dbo.MyTable"

    image

    @[User:CurrentDatabaseName] is another variable to store the databasename that you would grab as you loop through your list of databasenames.

    Finally, in your dataflow, OLE DB source, you can change the Data Access Mode to “SQL Command From Variable”, and then it will let you choose your variable. As your for loop loops through your database names, and updates your SELECT variable, you can then select data from each database as you loop through them.

    image  

  2. Non-Standard Database Select – Not sure how to label this one, but here is what I am talking about. I like to make all my queries as stored procedures in SSIS, at least as much as possible. This works great when you are doing SQL Server to SQL Server, but what happens if its Oracle to SQL Server, DB2 to SQL Server, etc? Yes I know you can create stored procs on those systems, but you might be in a place or position where you just can’t or don’t want to. In that case you would want to use just standard T-SQL select statements to get data. You can easily put in params if the source is an OLE DB source, but what if it is an ODBC Source? You have to use the DataReader source, and you can’t easily set params – like a WHERE statement. You HAVE to use Expressions in order to have a query with a dynamic WHERE statement or passing in a variable as WHERE statement filter.

    So, throw a DataFlow on your package, and inside that, throw a DataReader source, and then set the connection to your ODBC Connection (ADO.NET Connection) and set the command text. Good to go. But where to set the connection? Not very intuitive. Go back to your DataFlow and look at the expressions for it. You will see one for DataReaderSource.CommandText (where DataReaderSource is the name of your DataReaderSource). You can set the expression up there. Now you can change an Oracle SQL Statement or DB2 or whatever to something that takes params without the need for a stored proc on that other database server.

So, while there are hundreds if not thousands of uses for expressions in SSIS, these are just a couple of uses that can make your life easier when trying to do more dynamic type queries in your DataFlow. Happy ETL’ing!

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

Using Offline OLAP to Develop Cube Reports Without SSAS

One feature of Excel 2007 that I think is really cool, and also a little hidden, is the “Offline OLAP” feature.

If you insert a pivot table connected to an OLAP cube into Excel 2007, and go to the PivotTable Options Ribbon Menu, You will see the “OLAP Tools” Button. Click on that and then “Offline OLAP”.

image

Once you go through the Offline OLAP Wizard, it will create a .cub file for you. What this ends up being is a local disconnected “cube” you can analyze in Excel, if you are on the plane, or in some remote area with no internet connection.

Other uses for the offline .cub files that I have found is this. Let’s say you want developers to develop Web based reports using .NET, maybe using the Dundas OLAP Services controls. If you don’t want to have to load SSAS or set them up to connect to any server so they can just develop locally, the .cub file is the way to go. In their .NET code, they can just change the connection string to point to the local .cub file, and then later when you are ready to go live, you can just change the connection string to the live cube. That way, if you are developing the cube at the same time reports are being developed, you don’t have to worry about uptime, etc. Just send them an updated .cub file every once in a while.

I don’t know much about the details for upcoming releases, like Microsoft Project Gemini, but I have a feeling that it might feel a bit like this, using Offline OLAP, or local analysis.

On a final note, if you really want to get geeky, you can actually create the .cub files from .NET, but that is another blog post 🙂