I SQL

Implementing an Agile ETL process in SQL Server

2011-11-11T15:22:00.001+11:00

I was fortunate enough recently to attend the DW 2.0 Conference in Sydney. One of the presenters, Therese Ahlstam (from the Genesee Academy) advocated a technique for building data warehouse's she referred to as Hyper-agility. The idea was to avoid the need to constant schema changes by storing Staging & ODS tables as Key/Value pairs. To me this seems to fit the Serialisation/de-serialisation design pattern so common in many software frameworks these days.

This idea seemed quite radical to me, but I've been thinking on this ever since and I decided to have a look at how feisible this really is. As it turns out, while the syntax is a bit tricky, this is not only possible but relatively easy to do in MS SQL Server thanks to the PIVOT/UNPIVOT commands.

Here is an example:

CREATE TABLE SourceTable
(pkID int,
Name VARCHAR(50),
Address VARCHAR(250),
Phone VARCHAR(30)
)
GO

INSERT INTO SourceTable VALUES (1,'Bob Smith', '1 Miller St Sydney', '555-1234');
INSERT INTO SourceTable VALUES (2, 'John wayne', '10 california Dr Hollywood', '+1-555-4567');
INSERT INTO SourceTable VALUES (3,' Billy Bowden', '4 Wainui road Wellington', '+64-21-3456789');
GO

--Unpivot the table.
SELECT pkID, KeyCol, ValCol INTO unpivotSourceTable
FROM
(SELECT pkID, CAST(Name as VARCHAR(250)) as Name,Address,cast(Phone as VARCHAR(250)) as Phone from SourceTable) ST
UNPIVOT
(ValCol FOR KeyCol IN (Name,Address,Phone)
)AS uST
GO

select * from unpivotSourceTable
ORDER BY pkID, KeyCol

pkID KeyCol ValCol
1 Address 1 Miller St Sydney
1 Name Bob Smith
1 Phone 555-1234
2 Address 10 california Dr Hollywood
2 Name John wayne
2 Phone +1-555-4567
3 Address 4 Wainui road Wellington
3 Name Billy Bowden
3 Phone +64-21-3456789

Note we need to convert all the columns to the same data type to get this to work (which makes sense) . This is how we would store the data in our Operational data store and/or staging database.

But how do we get back to a regular row when we need it ? We pivot it !

SELECT pkID, [Name],[Address],[Phone]
FROM
(select pkID, KeyCol, ValCol FROM unpivotSourceTable) uST
PIVOT
(MIN(ValCol) for KeyCol in ([Name],[Address],[Phone]) )as pST

pkID Name Address Phone
1 Bob Smith 1 Miller St Sydney 555-1234
2 John wayne 10 california Dr Hollywood +1-555-4567
3 Billy Bowden 4 Wainui road Wellington +64-21-3456789

Do you think there is value in this approach ? I've yet to put it into practice, but if you have, let me know. I'd be a bit concerned about the performance for very large tables, but I can see how this would speed up development.

SAP (Business Objects) Data Services

2011-10-13T16:05:00.000+11:00

Hi All,

I am about to embark on a project with the Data services tool set. It seems like a great tool, but there are two things I am struggling to find at present:

Any published books
Good websites, blogs, forums etc.

If you have any good resources for me please leave a comment here for me.

Thanks !

SSIS - What’s New in SQL Server Denali

2011-07-15T10:26:00.001+10:00

whats new in SQL Server Integration Services - Denali
Sounds like some good stuff coming up in SSIS !

Different Rules for Different Employees? Expect Different Behaviors

2011-06-08T09:03:00.000+10:00

Different Rules for Different Employees? Expect Different Behaviors

Dirty little secrets of HR

2011-03-17T20:21:00.002+11:00

Bernie Madoff's Client List vs. MDM Software: Lessons Learned

2011-02-27T11:59:00.000+11:00

Great article on master data management...

Great quote !

2011-02-26T10:00:00.000+11:00

The beginning of wisdom is a definition of terms. Socrates

SQL Server 2011 download page

2011-02-19T14:15:00.000+11:00

FYI - I've found CTP releases not worth the trouble in the past, but if you are keen to download SQL Server 2011, here is the link.

MDM in SQL Server 2011

2011-02-18T11:15:00.001+11:00

Similar to my earlier post on SSIS, here is the page for Master data management.

Whats new in SSIS 2011

2011-02-18T11:08:00.001+11:00

I've been looking for some information about what is coming in SQL Server 2011 for SSIS. There isn't a lot out there, but what Microsoft has released is available here. If you know of a better source of info, please let me know !

Best etl for complex files

2010-04-03T09:12:00.001+11:00

Further to my last post, if anyone knows of an etl tool that eats complicated proprietary files (multiple lines per record, arbitrary format) please drop me a line !

Thanks

Re-learning perl

2010-03-31T09:00:00.000+11:00

I haven't used perl much in the last 5 or so years. I prefer 'modern' languages like ruby but some ETL tasks have come up on our project that are way beyond what can nicely be handled with SSIS. Rather than having to use SQL as a text parsing language, we have decided to use perl.

An example of the type of problem we are facing is this: We get a file where the first character determines the format of the rest of the file. To get a complete transaction, you need to process 3 lines. For example we have (this is for a telco system):

1[TAB]123345[TAB]Acme Co
2[TAB]+61290099009
3[TAB]+61380088008[TAB]OUTBOUND[TAB]0.56[TAB]0.09

Where [TAB] means a tab character.

So we get the account number and customer name on line 1, calling number on line 2, the called number, call type, duration and cost on line 3. There is a 1-N relationship from 1 to 2 and 2 to 3.

so rather than write the most complicated cursor ever we are using perl. The thing is i'm a bit rusty.

I'm interested to know:
a) What are some good books for re-learning perl (as opposed from starting from scratch)
b) Do you agree with the approach ? how would you tackle this problem ?

Leigh.

Mobile blogging

2010-03-14T21:28:00.001+11:00

I just got a new mobile blogging app. Doesn't really suit the more detailed posts I prefer to make but I'll give it a try !

My boss resigned last week largely due to the lack of focus management have been giving BI. Looks like a turbulent period ahead. Hopefully it gives management the wake up call (I believe) they need.

Can your Business Intelligence team be too good for your organisation ?

2010-03-11T21:04:00.000+11:00

Firstly, I don't think for a second I am too good, but I suspect my team is 'too good' for our organisation.

What do I mean by this ? We have been quite a successful business intelligence team delivering to various areas of the business for the last couple of years so when management decided to put in a new multi-million dollar billing system we were brought in from the start to integrate this into out BI world.

And here is where things started to go wrong. Because we have more experience and skill in extracting data from our various systems, we were quickly roped into the data extraction, cleansing and migration tasks. Because the application vendor was too expensive in developing interfaces to our other systems and to external organisations, these became deliverables of the data warehouse. It has ended up that we are now responsible for 75% of technical tasks on the implementation of this new billing system.

Now we are receiving a lot of praise and thanks from management, but we no longer have capacity to cover the BI work we had planned for this financial year. Our people are getting frustrated and we are starting to lose staff.

The billing system still has another 3-6 months of work to complete (not counting any BAU tasks we may be left with at the end) and most of our team are out of patience.

I have tried to think of ways we can get out of this and back to our core area, but short of us screwing op royally (which we would never do intentionally) I can't see how we can convince management to let us return to our core area.

Any ideas ? Have you been in this situation and what did you do about it ?

Inspiring...

2009-11-25T08:37:00.003+11:00

I just finished reading William Kamkwamba's book - The boy who harnessed the wind. It is truely an inspiration - I'd recommend this to everyone, especially if you tend to make excuses for why you haven't achieved anything.

I hope a bit of it rubs off on me !

Peak oil & end of the information age

2009-09-25T08:57:00.002+10:00

Up until fairly recently I would say I've been very ignorant around oil. I had heard of peak oil, but had no understanding of what it really meant beyond the price of petrol and the environmental impact.

But I have recently done a lot of reading of the subject of peak oil and now understand that that is just the tip of the iceberg. Our society is completely dependent of cheap power. Due to the continuing growth in both the developed and developing world, demand increases every year, but supply does not. On day soon - maybe tomorrow, maybe in 10 years, the amount of oil coming out of the ground will reduce. This will permanently change the dynamics of the world - we will find alternative fuels, but they won't be anywhere near as cheap, at least in the short term. Think of all the manufacturing that has been moved out of western countries - it only makes sense because it is cheap to transport to where the customers are. This applies to food also - it won't be economical to transfer fruit from South America to Europe for example. So now we come to this thing - the internet. The biggest cost (when you add the manufacturing, transport, and running costs) of this is power related. Will is continue to be feasible to ran suites like face-book of twitter when costs double of triple ?

I think there is a really danger that a lot of what we do will not last in the post oil world.. The millions of people sitting at PC's in office buildings may need to head out into the real world again and start growing and making things.. which might not be so bad after all !

SQL Server Integration Services 2008 64 Bit frustrations

2009-09-17T09:04:00.001+10:00

We recently needed to purchase new infrastructure for our data warehousing environment. As we aren't likely to do this for a while, we decided to bite the bullet and go 64 bit everything; OS & SQL Server 2008. This has been a much harder than were envisaged and given how straight forward 64 bit UNIX is, somewhat disappointing.

The first issue we became aware of is there are no 64 bit excel drivers available yet. Apparently they will arrive in Office 2010, which given we are using Office 2003 at the moment, won't arrive here for another 6 years. This has meant for about 1/2 our packages they will only work in 32 bit mode.

Next we found that BIDS only runs in 32 bit anyway, so if we addressed the above issue, we would have problems maintaining these packages anyway. This pretty much says to me that at least from an SSIS point of view, 64 bit SQL Server is not really for prime time.

Finally we had a lot of problem installing 32 bit ODBC drivers. They installed fine, but we then couldn't find them in the odbc administrator. It turns out their are 3 (maybe more ) obdc administrators in Win Svr 2K8 64bit. Solution: Using the odbcad32.exe located in %windir%\syswow64 .

I have seen the Microsoft adds that state the WOW starts now. Maybe they were referring to that directory...

NB: This process is ongoing, so I will update this post if we discover any more gotchas...

Best practice for slowly changing Fact tables.

2009-07-06T09:55:00.005+10:00

I've been trying to find some evidence of best practice for facts which are updated and have been unable to find much written on this subject. While this is quite common for case management type systems (e.g. HP Service Centre, Salesforce.com), it is not typical for data warehousing in general, as most literature focuses on more traditional transaction based systems (e.g. bank accounts, PABX data, order/billing etc.).

The challenge we have then is to determine when the dimensions of a fact record should be changed, and when they should be left unchanged if the fact itself is altered. Obviously for SCD Type 1, this is a non-issue; we aren't keeping a record of the previous Dimensions value, so the fact must be linked to the newest version anyway. But for SCD Type 2, there are three possible options:

Determine the Dimension at the first instance of the fact being recorded in the warehouse.
Update the fact's dimension's to the version of the dimension that existed at the time of the fact being modified.
Update to the Current version of the dimension at the time the fact is updated in the warehouse.

While 1 is the simplest to implement, it doesn't reflect the real world usage of many source systems. For example an address Dimension may start of with very minimal details initially, but will gain more details over time. It is unlikely that we would want to stay with the minimally populated dimension once we have richer data available.

Option 2 to me is probably the best 'default' option in this case. It makes sense to me that if the fact is updated, it should reflect the dimensions at that point in time. For example if sale was made to the sales rep for region 1 (e.g. 'Bob') and Region 1 is later re-assigned to 'Fred', we don't necessarily want that sale updated to 'Fred' as he never made that sale. I'm sure there are cases though where this may not be the right approach.

Option 3 to me in most cases would give an incorrect view of the data, so therefore is not what we want either, however it may be that in the source system certain data is only updated periodically and that for that system the most recent version is deemed to be the 'correct' version.

I'm include to use Option 2 in most cases, but I would appreciate any feedback on this, as there may be other scenarios I haven't considered. Also, if you have come across any articles linked to this, please pass these on to me.

What are your thoughts ?

10 skills developers will need in the next five years

2009-04-27T08:21:00.002+10:00

Click on the title for the original article. Here is how I score on the list:

1: One of the “Big Three” (.NET, Java, PHP) -CHECK
2: Rich Internet Applications (RIAs) - FAIL
3: Web development -A BIT
4: Web services -A BIT
5: Soft skills -CHECK
6: One dynamic and/or functional programming language -CHECK
7: Agile methodologies -A BIT
8: Domain knowledge -CHECK
9: Development “hygiene” -CHECK
10: Mobile development -A BIT

So I give myself 7/10. Need to geek up a bit !

iMovie 08

2009-03-26T09:27:00.002+11:00

I'm currently learing how to use iMovie 08. Despite all the criticism it gets, I am starting to like it. I was wondering though, is there any way to add functionality ?

Also, are there any public domain clip libraries I can use to enhance my projects ?

Off topic...

2009-03-12T09:36:00.001+11:00

Just found a great website for money management. Click on the litle to have a look...

Gartner's 5 predictions for BI in 2009 and beyond

2009-01-19T09:49:00.002+11:00

I'm not a huge fan of Gartner, due to the cash for comments nature of their business, but I thought the following was an interesting piece. Of particular relevance, I found the last point one that I identify with:

"And Gartner's fifth prediction is that by 2012 one-third of analytic applications applied to business processes will be delivered via coarse-grained mashups. IT shops are no longer focusing on grand visions such as SOA, which involve building composite applications out of fine-grained services or portals that merely display operational and analytical information next to each other, the analyst firm explained. Coarse-grained mashups, on the other hand, can be used to overlay analytical insights."

I interpret this, as tactical solutions will be successful, where as big bang solutions will fail. In my experience, that is almost always the case!

a Big gap...

2009-01-19T09:46:00.002+11:00

I haven't posted anything for a few months. Sorry for the gap. I have started a new job (based around SQL Server and SSIS) and have a new daughter, so I have been a bit busy !

I'll try to make more regular posts this year - promise !

Recession proof IT jobs

2008-10-17T10:06:00.002+11:00

If like me, you've been on the job market recently, you may be a little concerned about taking a job and then being made redundant down the line. Its for this reason I think that people tend to stay in the one place a lot more whne things are tough.

I found this link, to supposedly recession proof IT jobs. Thankfully, database administration is on the list. Lets hope its right !

If only it was real !

2008-10-09T14:50:00.003+11:00

I know this is obviously, fake, but I saw it today and thought it was pretty funny. Bill has left Microsoft, so who knows what he might do in his spare time !