Sutha's SQL BI Blog

Kilimanjaro

All

I am climbing Kilimanjaro end of Sep 09 / Oct 09 (Depending on travel arrangements) well before Kilimanjaro is Released by Microsoft.

I am doing this out of my own money, but I would like to raise some money to the charity which helped my colleague and friend Jim Wright in his last weeks and days.

Maybe Microsoft can double the collection I make though friends. I think it would be great publicity for Microsoft. SQL Server flag on top Kili

If anyone wish to donate please go to JustGiving website. Please donate for a very good cause.

Thanks
Sutha

Connecting To Sybase Via SSIS

Microsoft does not provide any drivers to connect to Sybase. Sybase have their own drivers (only 32 bit) but they do not have 64bit. I cant understand why they dont provide the 64bit drivers. Flipside of that why dont Microsoft provide any drivers for SSIS.

If you are using Oracle or Teradata as your source you can have Attunity Connectors for FREE as long as you have got SQL Server Enterprise Licence.

Unfortunately Microsoft didn't sponse Sybase Connector from Attunity. There is another option available from Data Direct. You have to purchase both connectors from respectable vendors. These connectors are not cheap!!

Thanks
Sutha

Late Arriving Facts & SSIS Contd

As discussed before if you have a type 2 dimension and late arriving facts you are in a messy situation. Most of us do it using SQL Statements.

Lets see we could do this using SSIS. One of my colleague the following way, which seems to be a very good option but SSIS data flow task might be bit messy.

Let me describe each steps.
1. Read the data from Stage using OLE DB Source.
2. Conditionally split into 2 pipeline. Conditionally split would be done on a key date column. For example if you loading orders on a daily basis and you have a control table which data you have loaded already. You could use that table as a source and map it to a variable. Then anything less than that variable would be sent down the late arriving facts pipeline, where the others will send down the default (New records) pipeline. Some of you would be happy would setting late arring facts as anythign less than 2 days old. That would work too as long as you are happy with that scenario.
3. New records pipeline would use full cache lookups to get their surrogate keys. They will run fast.
4. Late arriving facts need to do the Lookups with the range value. This will work but will  be slow. The reason for that is it needs to go to database for each record. By splitting up this way we have suggested only few records should go to the database for the surrogate keys.
5. Please note if you do this on SQL 2005, it will definitely go to disk for each record. In SQL2008, you can use Partial Cache to build the cache.

Since I wrote this article Matt Masson has put a great blog together about this. You can read it here.

Thanks
Sutha

Late Arriving Facts & SSIS

If you do have a warehouse with Type1 dimension late arriving facts is not an issue for you. This is an issue when you have type2 dimension. Kimball call this "messy situation".

Thanks
Sutha

Dependancy Analysis Viewer / Resource Governor

I have been asking for Dependancy Analysis Viewer within SSIS since TAP for SQL2005. At last Microsoft seems to have listened to most of our needs and it might appear in the next release. Well done, well overdue but well appreciated.

In SQL2008, MS have released Resource Governor, but it does NOT govern Analysis Services. I have been working with AS since SQL7 and we always have long running MDX queries, bu we dont know who is running etc. I understand next release might have Resource Governor which would support AS.

Thanks
Sutha

Performance Point 2007 Documentation

All

You can download the following Documentations from Technet site.

• PerformancePoint Server 2007 Planning & Architecture Guide
• PerformancePoint Server Operations Guide
• Deployment Guide for PerformancePoint Server 2007

Thanks

Sutha

SQL Server Beta

All

Most of you probably know this. I just found out about this site. If you want to play with SQL Server 2008 you can do it here at SqlServerBeta. It is totally free, you just need to sign-up and you would get 100MB database space. It will give you your own space to play with.

Keep an eye out for MDM area on the same page or check out SQLPass website. It should appear soon, where you can test and play with Microsoft's new MDM solution.

Thanks

Sutha

Presenting again at SQLBits

I am presenting on Microsoft's Master Data Management (MDM) at SQLBits.

Thank you for all voted for my session.

See you all there.

Thanks

Sutha

Data Profiling

I am very pleased to see Data Profiling Task is going to be part of SQL2008. I have been requesting this task since SQL 2005 TAP programme when I was at Reuters.

You can read all about Data Profiling Task here.

Another feature I would like to see and have requested in the past is Dependancy Analysis Graphical Tool, which will tell me what would the impact on the whole project if I delete a column x from the ETL. In otherwords, which tables, views, stored procedures, packages, cubes and reports are affected by this change. I know there is an add-on available which allows us to do Dependancy Analysis. This was shipped as part of SQL Server 2005 Business Intelligence Metadata Samples Toolkit.

Does anyone agrees with me or not?

Thanks

Sutha

What's new in SQL Server 2008 for SSIS - Part2

What's new in SQL Server 2008 for SSIS - Part1

http://blogs.msdn.com/mattm/archive/2008/01/10/what-s-new-in-sql-server-2008-for-ssis-part-one.aspx

Thanks

Sutha

SQLBits II

Most of you know that I am not a regular blogger. Since first SQLBits presentation I haven't updated my blog. Guess what, second one is around the corner.

You can get more information here.

I have submitted a session on Master Data Management (MDM). Hopefully the product will be available soon on TAP. Microsoft suppose to release the product end of last year. I hope they will release it soon.

There are over 35 sessions submitted and please vote for the topics you would like to see most.

You can vote now at http://www.sqlbits.com/information/PublicSessions.aspx

Thanks

Sutha

Thank You For Attending SQLBits

Thank you for all of you who turned up today for SQLBits. It has been well organised and well run, thanks to Tony, Simon, Martin & Chris.

Thank you for all of you who attended my session on Early Arriving Facts. It was a reasonable turnout for a Saturday morning 09:30.

Slide deck and demo will be posted on SQLBits website.

If you have any queries please do not hesitate to contact me.

Looking forward to the next year SQLBits already!

Thanks

Sutha

Performance Point RTM Available

Performance Point RTM is available for download. Please note this is only Evaluation version. MSDN download should be available in 2 weeks time.

http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en

Thanks

Sutha

BI User Group

I just got back from BI User Group Evening. Mark Hill and Suranjan Som did couple of excellent presentations.

It was nice to catch up with some old buddies.

Thanks

Sutha