Data Geekery | Edafe Onerhime

Get @jamiet's SQLBits agenda in your calendar

2012-01-19T09:02:00.001Z

@jamiet has created a brilliant calendar for SQL Bits - on his blog - Get the SQLBits agenda in your phone's calendar. If you can't get the link to work, try this: In Google Calendar, go to Other calendars -> Add By URL and paste this link: http://cid-550f681dad532637.calendar.live.com/calendar/SQLBits/calendar.ics Add Calendar By URL Click Add Calendar and you're done!

Blast from the past: Top 10 tips for database developers

2011-12-27T21:32:00.000Z

New year means a clear out! Stumbled across this written between 2007 and 2009. I sound really lecturing, pompous and stuffy! I think this is the point I started doing more development than database administration. At the time it helped, not all of the tips would my top 10 today. The great thing about finding it? Food for thought on rewriting. If you could go back and give your younger self 10

Add your Amazon Wishlist to Blogger in 3 easy steps

2011-12-24T11:16:00.000Z

Step 1: Create an Amazon widget. Go to https://widgets.amazon.co.uk/Amazon-Wishlist-Widget/ (change amazon.co.uk to your Amazon site) and sign with your Amazon Associates details in to earn referral fees. No account? No worries, just sign up to earn fees or skip the sign up and search for your Amazon wishlist. You can search for an Amazon wishlist by name or email, then pick the

Study Brain Dump: Administering Service Broker

2011-12-21T09:59:00.001Z

Delving into the delights of SQL Service Broker. I've started a brain dump, might do a mindmap too, helps to focus my brain when faced with huge blocks of text! Study brain dump: Administering Service Broker Trying out the Google docs iframe - what do you think?

Switch between servers in one t-sql script with SQLCMD scripting

2011-10-12T14:14:00.000+01:00

If you haven't used SQLCMD scripting, you're missing a trick. Switching between query windows can be a pain, but in SQL 2005+, you can save time and effort with these commands. SQLCMD is a command line utility for running SQL queries, enabling the option in SSMS can either be: Temporary: Query -> SQLCMD mode Permanent: Tools -> Options -> Query Execution -> By default, option new queries in

Extending code: Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution

2010-12-22T11:39:00.001Z

The book was very useful when I first began developing in SSIS 2005/2008 packages. The original code can be is available to downloand from Wrox's website: Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution. There were two things missing which my client wanted - record counts and the ability to restrict the level of information recorded. For the logging level, I created

SQL Server 2008 R2 Trial

2010-10-12T13:21:00.004+01:00

It's been far too long since I blogged anything. So a reboot seemed apt, since I'm now looking very seriously at the MCITP certifications.To help this along, I'm downloading the 180 day SQL Server 2008 R2 Trial from Microsoft. The time limit is generous enough to complete the bulk of the study, you simply have to register first. This is made even easier using your Live account, a couple of

Pass Parameter from Excel to Stored Procedure

2010-05-25T10:34:00.000+01:00

In reply to a question on LinkedIn:There is a way to do this but it is extremely long winded. NB: I'm assuming Excel 2007.1. In Excel, add your parameter to a cell e.g. A1.2. Click the Data tab, then "From Other Sources" in the menu bar, then "From Microsoft Query".3. In the "Choose Data Source" window either select the data source or create a new one.4. In the "Query Wizard - Choose Columns"

Free ebook: Introducing Microsoft SQL Server 2008 R2

2010-05-24T13:31:00.001+01:00

Microsoft Press have published a free ebook: Introducing Microsoft SQL Server 2008 R2, by Ross Mistry and Stacia Misner.Get it here.

Dimension Hierarchy visible in dimension browser but not in cube [2005]

2010-02-25T12:56:00.007Z

I came across this recently and have been pondering why it's never happened before. In a SSAS 2005 cube, changes had been made to the User dimension removing a hierarchy that contained these attributes:Department Name, UsernameDepartment Name was altered to visible (AttributeHierarchyVisible = True) and a new attribute Department Code was created with AttributeHierarchyVisible = True. The

R.I.P Business Intelligence MSc at Sheffield Hallam University

2010-02-23T09:49:00.004Z

After some months of consideration, I finally decided on Sheffield Hallam University for their MSc. Business Intelligence programme. There appears to be a dearth of offerings for Microsoft products with the exception of Aberdeen University. SHU also won out due to it's maturity and the distance learning option.Sadly, it appears the course has bitten the dust. A phone call to Matthew Howe revealed

Moving hosts

2010-02-20T17:26:00.000Z

Blogger will soon be withdrawing it's FTP support, so it's time to move this blog to a new home. For the moment, I'm sticking with blogger and pointing my hosting to google for sheer simplicity. See you on the other side.

Where can I get Analysis Services sample databases?

2010-02-18T10:46:00.000Z

For AS sample databases and sample data warehouse databases, go to codeplex. You can also find forums, websites and other supporting information.

The Universal Dimensional Model

2010-02-16T11:52:00.000Z

The Universal Dimensional Model (UDM) allows data in various formats and repositories to be displayed to the end user as a single view using business terminlogy, taking into account business rules and performing aggregationss quickly. End users can access the UDM using client tools like Microsoft Excel without worrying about joins. In a practical sense, many OLTP systems have data stored in

Hidden gems: BCS Library - Books24x7

2010-02-10T15:44:00.000Z

Despite a 10-year membership of the British Computer Society, I've rarely used their services. Their first website was clunky and irritating to use. Recently, I've joined the BCS Women group, some posts inspired me to check out the BCS site once again. The improvements are excellent but the one thing that really caught my eye is the library - Books24x7. It gives you access to a tidy number of

Business Intelligence - Concepts - Data Mining

2010-02-08T11:53:00.000Z

The process of creating predictive models to find useful patterns and relationships in samples of data that can be applied to larger datasets Breaking this down: Data mining is also known as KDD which stands for either Knowledge Discovery and Data Mining (Kimball) or Knowledge Discovery in Databases (Wikipedia). It is the digital equivalent of traditional mining, finding patterns (valuable

Business Intelligence - Concepts - Data Warehousing

2010-01-26T08:35:00.002Z

An implementation of structured and unified enterprise information to support strategic and operational decision making Breaking this down: Unified - consolidating information from inside and outside the organisation by extracting data, transforming (cleaning, restructuring etc) and loading onto the data warehouse platform. Strategic - Providing key information to support make or break decisions

Group By Consecutive Minutes

2008-11-14T13:10:00.003Z

Let's say you have a a table that looks something like this:.nobrtable br { display: none } Company Time AAA 13:09 AAA 13:10 AAA 13:11 BBB 15:04 BBB 15:32 CCC 18:25 CCC 18:26 DDD 20:05 DDD 20:06 DDD 20:07 DDD 20:08 EEE 22:35 And you want to indicate against each row how many records follow it in a consecutive manner:.nobrtable br { display: none

Troubleshooting Performance Problems in SQL Server 2005

2008-11-05T11:38:00.003Z

This week, I'm spending time reviewing the excellent 104-page resource provided by Microsoft on troubleshooting performance problems in SQL Server 2005. I'm interested in creating a more robust monitoring toolbelt now that we've added several new SQL servers to our stable.If you haven't already reviewed this document, it's definitely worth taking a look. The resource isn't as dry and worthy as

UNPIVOT Multiple Columns in SQL 2005

2008-10-28T18:02:00.000Z

Continuing on from the previous example, UNPIVOT Single Columns in SQL 2005. Let's say you have this table.nobrtable br { display: none }ProductIdCategory1Category2Category3Region1Region2Region31100010011011123220002002202245633000300330337894400040044044101112And you want to unpivot it so it looks like this:.nobrtable br { display: none }

UNPIVOT Single Columns in SQL 2005

2008-10-27T12:48:00.000Z

Let's say you have a pivoted table that looks something like this:.nobrtable br { display: none } ProductId Category1 Category2 Category3 1 1000 1001 1011 2 2000 2002 2022 3 3000 3003 3033 4

Temporary Tables: Check if it exists

2008-10-13T13:02:00.000+01:00

It's easy to check if both local and global temporary tables have already been created.You can do this using the built-in function: Object_Id. This built-in functions is available in SQL 2000, 2005 and 2008 flavours. So your scripts won't break on upgrade or downgrade.The Object_Id function returns the unique identifier for any object that is schema-scoped. In a nutshell, these are objects that

Bullet-proofing MSSQL roll-out scripts: Columns

2008-10-08T12:51:00.000+01:00

Checking a column exists before adding, modifying or removing it is fairly straightforward. Using the INFORMATION_SCHEMA view: columns, we can check for the column. INFORMATION_SCHEMA complies with SQL-92 standards and ensures that no matter which flavour of MSSQL you are using, your script will work out the gate.Checking the column exists:USE [databasename]GOIF EXISTS (SELECT * FROM

Bullet-proofing MSSQL roll-out scripts: Tables

2008-10-08T09:40:00.000+01:00

New week, new project, new roll-out.For those of us not using Team Foundation Server or similar, roll-out scripts are a roll your own affair. Let's look at rolling out new tables. The CREATE TABLE statement will fail if the table already exists but rather than failing over, you may want to do something more constructive.For example, if the table already exists, you may want to drop it ( careful!

Hello - Hola

2008-09-25T12:13:00.001+01:00

This blog will document my learning progress on various subjects including Business Intelligence, SQL Server administration, t-SQL coding, screen scraping and other geeky topics that pique my interest.