SQL Server, SSIS, T-SQL

Sane Database Warehousing

Database warehousing is a critical tool in the skill set of a developer. The ability to take normalized data and store it in a non-normalized dataset can increase loading times, reduce calculations and generate a large ROI. Large datasets will benefit dramatically from a warehouse of commonly/frequently accessed data facts.

ETL

Extract, Transform, Load ETL is the process to take normalized (usually) data into a faster data access view or tallied (summated) data view. In the Microsoft world there are a few ways to achieve this goal. T-SQL as a scheduled job can easy handle the simple data tasks. SQL Server Integration Services SSIS is also a great tool for connecting SQL Servers and diverse data systems together. SSIS is great when working with systems that are not SQL Server based. Which one is better? It depends on the need and the application.

Simple T-SQL Warehousing

Using SQL 2005 or later allows the using of the pivot command when querying data. Pivot Tables can simplify a lot of reporting and calculations (which take time). A very simple example of would be the following SQL Statement:

-- Example Table
CREATE TABLE [dbo].[employee_items](
[id] [int] IDENTITY(1,1) NOT NULL,
[employee_id] [int] NOT NULL,
[item] [varchar](10) NOT NULL,
[count] [int] NOT NULL,
CONSTRAINT [PK_employee_items] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Pivoted Data
SELECT Employee_id, [100], [200], [300]
INTO employee_items_warehouse
FROM (
SELECT employee_id, item, count
FROM Employee_items) AS SourceTable
PIVOT
(
SUM(count)
FOR item IN ([100], [200], [300])
) AS PivotTable

This is not the perfect example but should give you an idea of what you can do to manipulate the data to create a warehouse. You wouldn’t want to simply do an insert every time you run the process; you would want to do updates / inserts to get the best result. The data velocity (how fast the data changes) or the need of the data updating of the pivot table data can be changed would have an effect on the process itself.

SQL Server Integration Services

SSIS is much more than Pivot (though it can pivot data easily) and is great for combining data from multiple system into one data warehouse. The catch with SSIS and SQL Server is the versions of Business Intelligence Development Studio BIDS must match the SQL Server the SSIS Package is running on. A SSIS Package for SQL Server 2005 must be created in BIDS 2005. SQL Server 2008 must be created in BIDS 2008 and so on.

The amount of tasks/data manipulated/script tasks you can do in SSIS is quite staggering. I only wanted to mention SSIS since in today’s data warehousing world it is one of the best tools on the market.

I will dive more deeply into the world of SSIS in a later post.

Uncategorized

Why I love and hate JavaScript

2009.03.16 OaD #75 | Consumed by code

JavaScript is a powerful client side scripting language.  It allows developers to quickly add/remove functionality.  It can be dropped in or generated from code.  In WEB 2.0, JavaScript is the backbone of creating rich user environments.  It is quite the catch 22.  Oh how I love to hate and hate to love JavaScript.

JavaScript can pass some rudimentary tasks to the client so, as developers know, this can save processing cycles.  Sometimes it can actually add processing cycles, so it definitely depends.  Saving processing time allows application to run faster for the most part.  You don’t have to wait for a complete page cycle to do tasks ala 1998.  Basically, users are not patient nor should they be patient.  They are used to the Desktop world.  I feel JavaScript helps bridge that gap for users; sometime developers/designers go overboard.

Ease of Use

Love
Javascript is easy to use.  Compatible with any web programming language: Classic ASP, .NET, PHP, Ruby, JSP.  It integrates easily.  It is fairly lightweight and the syntax is not mind-blowing.

Hate
It is a little to easy to use and users can directly access things in your page.  It gives the user more control than what they know.  Look you can easily change the background of any page in the address bar.

javascript:document.bgColor = ‘#000000’;void(0);
^ Paste that into the address bar and press enter -> changes the background

This only touches the surface of what JavaScript can do.  It can get into your web app and change things around.  I use it for quick fixes on the fly or to try different things.  You can’t rely on JavaScript it will only cause you pain.  Can you trust the DOM?  No.

JavaScript is an interesting topic still it was once vogue, faded out, and then came back (with velocity).  I think at one point in JavaScript’s history it was somewhat confused for Java, but I don’t think that is currently valid.  There are so many great JavaScript libraries out there that enhance the UI and the total web experience.

They are even available from the Google API with no API Key which is an added bonus; unlimited connections to boot.  I don’t think a lot of these effects/plug-ins would be as robust or available if not for JavaScript libraries and the good old Goog.

I really don’t hate JavaScript, but I think it can be overused or used incorrectly (say for validation).  Sure it can help in notifying the end-user of validation issues; it should also be validated on the server side.  Web development is a many layered burrito.

Uncategorized

Adding Tooltips to Checkboxlist Items

checkbox-1 The CheckBoxList control in Microsoft ASP .NET 2.0 can be quite limiting in features.  You can set a tooltip for the overall control; we can say “HEY THIS IS A CHECKBOXLIST”, which is great if that somehow helps you.  The true power would be in adding a tooltip to each individual item in the checkboxlist.  It would be nice to databind the field like other ASP .NET 2.0 Controls, but again no such luck.

To add a Tooltip to each individual item you are going to have to loop through the control.  By setting the title attribute to whatever you want will give you the individual control to name each checkbox in the checkboxlist.  This can done be done when pulling items in or on the case of a control firing.  Whatever works for you.

[lang=”vb”]
‘ Loop through CheckBoxList and set tooltip to checked if selected
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Then
item.Attributes(“title”) = “CHECKED”
End If
Next
[/lang]

This is really just a quick and dirty way to add tooltips to checkboxlist items.  If you are already pulling in jQuery or some other library I would say use one of the many Tooltip plugins out there.  If you are not using any Javascript libraries this can be a quick way to help achieve a goal.  I’m not sure if this will work in Firefox, but if you are going for crossbrowser definitely check out a Javascript library.

Uncategorized

Growing Up or Growing Old

I think it may be the latter.  I am departing from my usual technophile view of sorts and taking a introspective look at some things that have been on my mind.  A lot has changed over the past few years, frankly, over last six months or so.  It has been a rapid fire of change and I guess that is what life is all about: CHANGE.

Home ownership, bills, working a lot, puppy, more bills, working around the house…  It feels like I barely have time to breathe most of the time.  I guess that is what life is about, the next phase in a way.  Less time for friends, less time for family, building my own family, my own life.  Not being in college anymore is a total departure from what I have been come accustomed to.  It is nice on one hand.. no homework or projects sucking the life out of me..  Now it is just house projects and work projects doing that.  It was a trade of sorts.  I am happy with my job and it is a lot less time consuming than school was.

k9 meadows grand opening 006.JPG

I was on a walk with the dog last night and I was remembering back when I was in High School; it seems like a lifetime ago.  A different town a different day.  I remember lifting weights in my parents basement at 1:00am listening to the radio.  It was just a neat time in my life.  I didn’t have many worries or concerns; I was young.

I have seen 1:00am only a couple times since I have been out on my “own”.  I think that is good having a schedule and such.

This post has nothing and everything to do with technology.  Technology is a passion in my life and it has taken me to where I am today, but technology is playing a different role in my life recently.  Since I have less time to see people that I have seen for years technology is a great way to keep in touch and see what people are up to.  Actually a friend I had in High School contacted me on Facebook yesterday, which was pretty cool.  I haven’t seen her in years.  She has a cute kid.

I have been on Facebook, Myspace, and other social networks for many years, but I think their role has changed.  I think when I was younger they were an addition to face to face encounters and now they take that place.  It is good, I think, because otherwise I would not no communication with my hectic schedule and life.

I don’t know if I am growing up or growing old.  Maybe neither.  I am changing because life is change.  It’s definitely not easy.  There are so many sacrifices that I am not used to.  With responsibility comes sacrifice to a degree.  I gain so much from my sacrifice though.  It’s a good trade.

Uncategorized

How to: Connect Basic Cable to a Projector

I found it quite difficult to find information on how to convert basic cable (plain old cable) into composite.  This is for the folks who have cable, but don’t want to pay for extended services and cable boxes.  There are two verbiages that you will find on your web searches:

1)  Just use an old VCR!
2)  Just use an old computer!

End of thread…  End of post… That is where the story ends and it leaves a lot to be desired.  I have problems with both of these solutions.

1)  The VCR is archaic
2)  I don’t want to leave a computer running in a room that will be used sparingly
3)  I don’t want to wait for a computer to boot up

This can be negated if you have a cable box that already does that.  But there are still people out there without cable boxes.  Or don’t want a cable box in every room or on every device; I’m sure the cable companies want you to.  I can’t seem to justify the extra $30 + fees for the box to move away from basic cable.  Renting a cable box seems like a dirty tactic to me; in the long run they have to make a killing.

I have NetFlix so if I want to watch a movie I can just get it off of there.  Also, there is NetFlix streaming which is awesome.  I do not want to give the cable companies more money – it’s already highway robbery.

The problem with this is that projectors do not have a TV tuner.  It doesn’t know what to do with the coax connection.  Projectors cannot tune (most of them that are out there anyway).  But I believe there is a happy medium… a product by Ambery.com -> Super Video to WXGA Converter.  This device works much like a cable box expect that you can buy it and use it how you like.

basement projector system 001.JPG

It takes a coax connection (Clear QAM), it won’t decrypt any of the pay channels like a real cable box, and converts it to composite, VGA, or S-Video.  Composite will most likely be your best choice for a projector.  I believe they also have one with component connections.  If you are looking for HDMI I’m not sure what will be needed – I’m guessing an upconverter of some sort.

Clear QAM is important.  If the device is not Clear QAM then it will not be able to tune to the cable frequencies.  If you have been looking at the Analog-to-Digital most of the ones that I have found do not have clear QAM meaning they will not work with cable; you will need an antenna to retrieve the signals.

Continue reading

Uncategorized

FLVPlayer Plug-in Fix for FCKEditor 2.6.3

I absolutely love the plug-in FLVPlayer for FCKEditor.  Inserting FLV into page or posts with the standard FCKEditor just doesn’t work.  This nice little plug-in is a champ.  Unfortunately, it has compatiblity issues with the new FCKEditor 2.6.3, so here is how to make this little plug-in work on FCKEditor 2.6.3

The problem I ran into was going to browse for media it would display a Page Not Found.  I’m guessing the method it was previously calling got depreciated.

Here’s where the error occurs:

flvplayer.js

function BrowseServer()
{
OpenServerBrowser(
‘flv’,
oEditor.FCKConfig.MediaBrowserURL,
oEditor.FCKConfig.MediaBrowserWindowWidth,
oEditor.FCKConfig.MediaBrowserWindowHeight ) ;
}

Replace that with the following:

flvplayer.js

function BrowseServer()
{
OpenServerBrowser(
‘flv’,
oEditor.FCKConfig.FlashBrowserURL,
oEditor.FCKConfig.FlashBrowserWindowWidth,
oEditor.FCKConfig.FlashBrowserWindowHeight ) ;
}

Voila!  This cleared everything up for me and I was off and running… 🙂

—update—

I see this update has already been add to the comments of the project on sourceforge, but it has not made it into the source code.  I wonder if the developer has abandoned it.

—end—

Another change that isn’t a bug that I made was automatically setting the width/height of the videos instead of having to enter that with every FLV file. Continue reading