Justinek.si

Auto increment identifier in MSSQL 2008

I’ve had quite a long ride finding a MSSQL 2008 equivalent to MySQL auto_increment primary key. It’s not that IDENTITY didn’t work using it while typing the CREATE table statement in T-SQL. I wanted to utilise it using Microsoft SQL Server Management Studio. There, I just didn’t scroll down enough on my 13″ screen to check the properties and flick the Identity Specification property to Yes. Pertty lame, really.

MSDN help (F1, yes, sometimes it really does help me. Not as many times as on Mac OS platform. But it’s better than nothing) also describes what data types the property identity can be assigned to: Continue reading…

Business process analysis and reengineering using IDEF0

Business processes are almost never perfect. There’s always room for improvement. Be it improving the existing process by eliminating bottlenecks, changing the way information flows or reengineering the process steps from scratch. And every single company has problems with its’ processes. Problems might be a harsh word but every company could improve its’ business by polishing the processes. A university professor of mine once said that if a CEO (or CIO, or any manager for that matter) says their company doesn’t have any problems or bottlenecks, there’s something extremely wrong with the company. Continue reading…

Took my black MacBook apart

I have a black MacBook from late 2006. It still works perfectly. For my needs at least. I have it hooked up to a 24″ Samsung SyncMaster. No complaints. Well, it did work. Lately the fan really started to spin fast and loud and the CPU temperature was averaging at around 75° Celsius plus. But the temperature doesn’t bother me that much as does the loud fan spinning at 6000+ rpm. Well, the MacBook is old and I thought the dust inside the cooling ribs and in the fan was the cause of all this. So I followed the ifixit manual to disassemble it. I blew the dust out the fan and I hoped that would take care of it. Continue reading…

Clarifying the JOINs in T-SQL

In any database development system you do come across a task where you need to maintain the exchange of information between two tables. Using primary and foreign keys does help us here. And this technique of linking tables plays a major part when creating a JOIN SQL query. It allows us to decide whether we want to include all records or only isolate some of them. In respect to this, T-SQL supports three types of joins: CROSS JOIN, INNER JOIN, and OUTER JOIN. Continue reading…

Dynamic SQL from two tables using a cursor

I’ve been trying to use SSIS and T-SQL in MSSQL Server 2008 to report on how many times a certain image has been served by our Apache server. All good and well to see how our newsletter reached our customers. I know we could have used Google Analytics or any other tracking service but this is the way we do it. For now. Using SSIS to automatically extract, transform and load from many flat files (httpd-access.log) into the database. From then on I am able to do lots of different analysis on the traffic being serverd to our customers.

I wanted to run the SQL query only once for different number of columns I should count on. I’ve managed to do this with a cursor and a dynamically built SQL query that retreives the columns from one table and counts the rows in another table with a pattern operator LIKE. Continue reading…

MSSQL Server 2008 data types

My experiences with SQL were mostly MySQL, PostgreSQL and Oracle. Now I’m working with SSIS and MSSQL and I sometimes find it hard to match and declare the right data type. So for easier development and architecture, here are the data types and what values they hold. Continue reading…

Average values per hours in a day

Here’s a simple MS SQL query that returns the average calls per hour in a day.

Let’s say we have two columns called onlineTime and onlinePeople in a MS SQL table called CCPHourly. These two columns hold the values of how many people were online in that hour. And the values in this table look like this (semicolon separated): Continue reading…

Count distinct unique values from two columns

One of my latest tasks working as a business intelligence analyst was to count distinct unique values from two columns in a MS Excel 2007 spreadsheet. This isn’t quite as straightforward for most but I’ve managed to gather enough information to crack this little nut.

Let’s say we have a column with ice cream flavors and a country that has that flavour in the shops (or wherever, it’s just an example): Continue reading…

Organizational culture and collaboration

I’ve just finished reading the book Collaboration by Morten T. Hansen. An interesting book I must say. It’s not focused on “how-to” and not about technology. It’s about enterprise psychology. What I’ve read lately regarding the psychology of collaborative work is the fact that collaboration is not something that just happens. Providing the employees the tools to collaborate easier will not yield collaboration by itself. Collaboration has to be promoted by the managers and they need to be the leaders in collaborative environment by acting as collaborators.

Changing the enterprise culture is no easy task. Converting employees with serious collaboration barriers such as hoarders, not-invented-here, search and transfer, into collaborative employees takes time and strategic planning. Should human resources take over in this conversion? I believe not. Continue reading…

Pepsi Co. and social media marketing endeavors

Collaboration and current development in social networking is turning the business processes in companies upside down. Which is not a bad thing. To succeed in this new environment companies need to keep up and use the changes to their advantage. Reading about PepsiCo (DEWmocracy) and how they’ve used the social networks and collaborated with their customers to launch a new flavor of their already well known fuzzy drink proves what Ross Dawson pointed out in his book. Continue reading…