BI Tools - SQL2008 CTPFebruary Bug (SSIS)

Dear friends,
I found the gold… no...no… I found the bug! J This mistake is because I never thought that a simple person like me, here in my small country with the name of Portugal, that sometimes people think the country is a state of Spain would found a Microsoft bug in SQL 2008 (SSIS)... ok… but let’s talk about the bug. Sorry for this introduction…

As I told you in a previous post, I’m developing a SSIS project for my master BI degree, using the new SQL 2008 CTPFebruary version. I found a bug, that I think is critical and very important.
If you already worked with SSIS 2005, you are probably familiarly with the warning messages that advise you for unused columns in your pipeline:

For those people who never saw the messages, I made a very simple example. (Note: One of the best practices to develop an ETL package is to try to minimize the size of each row in your pipeline. If you forget to delete these unused columns you will be decreasing ETL performance. If you already used SSIS 2005 and never saw the messages, take care)

This simple example in previous version of SSIS (2005) only executes a query in the table “Person.Contact” on AdventureWorks and inserts the returned rows into a temporary table.

Steps:
1. Add a dataflow “DFT TestBug” to the ControlFlow
2. Goto the dataflow created in the previous step
3. Add an OLEDB Source to get some data from AdventureWorks database:
SELECT Title, FirstName, MiddleName FROM Person.Contact
4. Add an OLEDB destination to insert the data into a #tempTable. Map the source and destination columns title and firstname and intentionally leave the column MiddleName without map.

As you can see for the image below, the SSIS 2005 advice you for the unused columns with warning messages. In the new version, these messages doesn't appear!!!

I posted this bug in MSDN Forums
http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3044877&SiteID=1&mode=1
and James Thomson and Bob Bojanic kindly suggested me to submit this bug to Microsoft. I submit the bug as you can see for the link below:

Sorry MSFT team for this post, but for me this messages are very important and it will be a problem if the final version doesn’t correct this!
Cheers!!!
Pedro

BI Education - The Data Warehouse Lifecycle Toolkit

Dear Friends,
I ‘m very busy with my job and my master BI degree, but I will try to come here with more frequently. I have too many things to blog and I don’t know where I will start from.

For now, I’ll write few words about the recent book of Ralph Kimball. I confess that I always was a fan of Kimball books, but normally those books have a lot of years. Although the subjects of almost the books are in use, I think that they was a little old and could be better adapted and integrated.

Ralph Kimball Group launched a new and fantastic book, “The Data Warehouse Lifecycle Toolkit” (second edition) on last January 2008. I read only four chapters yet, but I really feel that this book is better than others from Ralph Kimball Group. Check the contents of the book in the amazon or wiley website.

Obviously, I bought this book, and it has a fantastic price on Amazon (UK) at

http://www.amazon.co.uk/exec/obidos/ASIN/0470149779/ref=nosim/schildnet0a I paid for the book about 18€ (Euro) with shipment included and received in 2 days!!! (I’m in Portugal… don’t forget it) Normally, I buy the books at Amazon directly to the Amazon partners because is more cheaply, but in this case there is no difference between Amazon official price and the partners price.

I’m preparing a post about Reporting Services 2005 and 2008 querying an OLTP database.
Regards!!

BI Conference - SQLBits - Birmingham (UK) - After

Dear Friends,
I’m just arriving from Birmingham (UK) and I only have one Word to describe the experience: FANTASTIC! I have several things to write but if you weren’t there you missed a fantastic day!
As you already know, I’m currently living in Portugal but last Friday I pick my girlfriend and went to Birmingham (UK) to attend the SQLBits conference and back to the city where I’ve finished my degree some years ago. Microsoft Portugal is not investing too much in SQL BI tools, and in the last summer it had only one person specialized in these tools… To prove it, the next TechDays 2008 in Portugal there will be just a few sessions in SQL BI tools and probably too basic… The possibility to hear some “gurus” and the authors of my preferred books from Wrox and Wiley, it was an opportunity that I couldn’t missed!
I will describe in general how it was this journey to Birmingham and I will detail my preferred sessions on detail in a next post.

The Conference
There was several sessions in this conference, and I attended the sessions related with Business Intelligence MS SQL tools. Darren Green, Allan Mitchell and Chris Webb didn’t disappointed me in their sessions, and the money expended in this journey was a very good investment on my career and a good opportunity to “open my eyes” more and more to the world.

Allan Mitchell Session
Allan spoke about the new features of SSIS 2008 and compares it with the SSIS 2005. He shows some details that usually some developers don’t pay much attention, the performance… he show some details behind the scene, and in my opinion he done it good!
MultiCast
Pipeline Limiter
LookUp Component (Cache Options)
Data Profiling Task
Change Data Capture

Chris Webb Session
This session was very interesting… I expecting to see something about new features and good advantages of SSRS, and what I saw was the opposite and I’m totally in agree with him and this session prove that this type of events are not to sell products but is a place where we can receive very good and sincerely tips and tricks from developers to developers. And Microsoft was one of the sponsors…

Darren Green Session
Darren session was very cool and he spoke about a subject that I normally in SSIS avoid but after the session I will take more attention… and the subject is custom data components. I bought the WROX book that Darren wrote and refer this subject but I didn’t read those chapters… But is really interesting , not only to understand better the pipeline engine but also to develop more specific and complex transformations.

The City and BullRing
As I told you before, I finished my degree in Birmingham some years ago. There are several improvements in the city but it still beauty! A very good improvement was the BullRing Center that is a big shopping place with an area equivalent to 26 football stadiums!!!





The Hotel
Normally, hotels in England are not comfortable or modern, but this Hotel is very modern and I knew a Portuguese man that works there!



The NightLife
The night bars and discos are incredible on pressure of safety guards…

So... in resume, was a fantastic journey!! I will try to detail more about the conference in a next post!

Cheers!!

BI Conference - SQLBits - Birmingham (UK)

Dear Friends,
Today I'm very happy!! I received the confirmation for the next SQLBits conference in Birmingham(UK). And I'm happy for several reasons...

• I will return to Birmingham where I finished my IT Degree some years ago (NewMan College 2001)
• I will attend to the sessions of Chris Webb and Darren Green that are one of my main sources of inspiration and Allan Mitchell that is one of the authors of my favorite SSIS book at WROX.
• Change knowledge with the BI comunity and see old and new friends.
  
• Learn more about BI and SQL 2008 tools
• Enjoy Birmingham lifestyle again!

I'm sure you have a lot of reasons like me to the conference. Dont waste more time, there are more than 400 people confirmed!!

For registations follow this link: http://www.sqlbits.com
There will be several sessions, I selected these ones:

Slot 1:
Master Data Management (MDM)
Taken by Sutha Thiru
Slot 2:
Extending SSIS with custom Data Flow components
Taken by Darren Green
Slot 3:
Understanding LINQ in .NET Framework V3.5 and Beyond
Taken by Mike Taulty
Slot 4:
Making more of SSIS in 2008
Taken by Allan Mitchell
Slot 5:
Using Analysis Services as a data source for Reporting Services reports
Taken by Chris Webb

The agenda: www.sqlbits.com/information/MainAgenda.aspx
I will book my flight and hotel tonight!!

And for some reason you cannot go to Birmingham, prepare to go to Glasgow on 10-May-2008... I will try to be there... so, vote in the sessions you want to see!! Follow the link image!

See you around!
Regards!

SSIS - Execute a package from an ASP.NET page

Dear Friends,
Today I'm describing the way I executed a package from an ASP.NET page and passing a parameter.

The Problem
I need to enable the user of an ASP application to execute a package and passing a parameter to it when the user press a button.

Solution

The first thing you must know, is where you ssis packge will be stored. You or the DBA Team could stor the SSIS package inside database, in the filesystem or in DTS. Each one has advantages and disadvantages, and in my opinion the package inside database could be better when you must unsure more security.

So, in my SSIS package I have created a global variable "strCFG_TextPath" and I will update this value from ASP page. I have created a function fExecSSISPackage in a clean webservice to make the call.
(You can create the function in the ASP page, but in my case I made like this because I insert all the connections to the database inside a WebService)

WebService

In this example the SSIS package is stored in the filesystem, but the function is prepared to accept each of the 3 types that I described before.

You only need to write few lines: (SourceLocation is the string value I want to pass to the SSIS package)

Dim myPackage as Package
Dim integrationServices As New Application
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
myPackage.Variables("strCFG_TextPath").Value = sourceLocation
myPackage.Execute()

ASP.NET Page
To call the function in the WebService is simple... see the image:

I hope this post could be helpfull for you.
Kind Regards,
Pedro

BI News

Dear Friends,
Sorry for this delay to write a post... you dont imagine the lot of things I have to share and change with you. This delay is because in the last week I was reading a new book... a book that tells me how I can earn money in the financial markets!! eheheh... the book is very good, and was written by a person that worked in the same bank that I'm currently working as outsource BI developer... I'm working for the big IT company in Portugal... It's Novabase (http://www.novabase.pt/)

The book is really good and explain how the markets work and explain some tips that we must care when we invest some money in equities or other financial instruments. I bought this book because I'm currently working as BI developer in a project in this business. This book is writetten in portuguese, but I will try to know if there is some version with english translation.(http://www.keditora.com/nov.html)

But the main purpose of this post is to tell tou that I will start to write more technical posts related to ETL using Microsoft tools. Not only because I have a ETL class in my master degree but because I have some tips that I think could be helpfull to you, like SSIS package configurations, SSIS audit packages, SSIS named conventions, and more and more... so, keep in touch with my blog!!!

Cheers!

Pedro

Microsoft TechDays 2008 - Portugal (Lisbon)

Dear Friends,
This post is only to inform you that next March 2008, it will take place the bigger Microsoft Conference in Portugal. Last year I was there, and it was amazing!!! If you can go there, don't hesitate, it will be very good for sure.

In the conference will be announced the new SQL Server 2008, Visual Studio 2008 and Windows Server 2008. Download the agenda http://www.techdays.pt/Download/AgendaDraft.pdf
Official Site: http://www.techdays.pt/default.aspx
Regards!!

Operational Risk - Societe General Fraud!

Dear Friends,
I was planning to write this post last week, but today It’s the right time to do that! And the reason is the Societe General… do you know what these two words mean? I confess that I didn’t know it, but it’s only the second bigger French bank, and today it’s notice in all newspapers of the world.

The Societe General Bank was victim of a “massive” fraud from an employee. The bank said the fraud was based on simple transactions, but concealed by "sophisticated and varied techniques". The losses are estimated around 5 billion Euros!! Too much zeros, but I will try: 5.000.000.000€…uuuauuuuu… see all the news in the following link:
http://news.bbc.co.uk/2/hi/business/7206270.stm

How can be possible that only one employee makes several trades and no one else knows?! This men control the entire bank?! It’s very strange… but as Gilles Glicenstein BNP Paribas chief executive told in the above link, "there is still some information missing to understand what happened” and "the scale of the fraud is so large, there must be a complex explanation”
I initiate this post with this amazing, but real history, to write something about operational risk that I’m learning in my master BI degree. So, how these things could be possible? This Bank doesn’t have a Risk Management Unit?!

Operational risks are not only in a bank sector but in all activities, the risk is everywhere every time!! You have the risk to get fired! You have the risk of a car accident… Normally, and mainly in financial sector, all this operational losses, caused by people, systems, external factors or processes were hidden! And they were hidden because critical information could be dangerous for the health of a company. But I think that nowadays, this kind of information is not so dangerous for a company than the past, because the shareholders want TRANSPARENCY, and only with it, the shareholders put their money in the companies! And don’t forget that we are in a globalization, bad news in china can affect Europe, and bad news in Europe can affect China…
So, what is Operation Risk?!

“Operational Risk is defined as the risk of loss resulting from inadequate or failed internal processes, people and systems or from external events. This definition includes legal risk, but excludes strategic and reputational risk.” In http://www.bis.org/

Operational Risk nowadays it’s so important as credit risk and market risk, and for today I leave you with the picture below that I will describe with more detail in a next post:

So, This was the first post about RISK, I promise that I will be back tomorrow not only to write about RISK and write about my fantastic classes with the teacher Rui Gonçalves (SAS Portugal).
Cheers!!

BI Master - BI Architecture

Dear friends,
Today, I will show you the BI Architecture defined to my master degree (data warehouse class) that it’s also based in the project that I’m developing to my company. The objective is not explain in detail each module definition, but describe how I apply the BI architecture to my project. I suppose you already read a lot about this subjects like ETL, ODS, DW, Cube, BSC … I want to show you what I’m doing and I want to receive feedback from you to improve my work and to share knowledge between us.

The architecture defined for the project follow the Inmon approach, the 3 tier-architecture.
It was necessary to customize the ODS in order to satisfy one of the business requirements that is to keep the data imported dynamic. Keep data dynamic means that the business user administrator could change the data (from last year) directly to ODS when he needs. The reason is because some data from operational system must be corrected or because it has bad quality or there is an error. The data inside ODS is stored for one year.

The following image shows the relation between all modules and how they are integrated between itself.

1. Operational Systems (OS)
Operational Systems record details of business transactions in real time. It’s here where data is stored and will support data warehouse. These operational systems include data from external data sources like interest rates and exchange rates, and others…

2. Extract Transform and Load (ETL)
The ETL, Extract, Transform and Load data from Operational System into OLAP database.
There are two ETL processes in this architecture:
2.1 Makes the extraction of the data from OS, transform and integrate into the ODS.
a. Initial ETL
b. Daily process ETL
2.2. Makes the processing update of the cube.

3. Operational Data Store (ODS)
After the extraction, cleanness and integration of the data from the operational systems into ODS, it is necessary to include additionally calculations and aggregations to allow indicators to be generated dynamically to our OLAP cube in spite of create it static inside an ETL. Normally, all the calculations are made inside an ETL, but in this case the business requirements imply a customization. And these requirements are:

· Problems in financial instruments evaluation.
· Requests from managing entities to change positions
· Positions corrections
· Data in operational systems could be incorrect

4. Data Warehouse – OLAP Cube
In this module, is implemented the multi dimensional view of the new system. It will be created the famous cube that will enable the users to visualize business indicators from different perspectives. This cube is supported by an OLAP database and an OLTP database that will process the cube and update periodically the OLAP database, MOLAP.

5. Reports and Data Analysis
In this module will be available all the value, measurable and complete information that will be important to decision makers. Will be created static reports (pre-defined) and dynamic reports (Adhoc-reports) that allows the business users to create their own questions to the cube.

Feel free to make questions!
Regards!
Pedro.

BI Master - MSc UK Universities

Dear Friends,
As I told you lot of times in this blog, I’m currently attending a master BI degree here in Portugal. But I also was seeking for BI masters not only in Portugal, but also in UK. Following a request of my friend Amin, I’m writing today about the research I made before start my master degree.

In Portugal, ISCTE University is the winner for BI master degrees, perhaps in UK there are several candidates, and probably will be someone that disagree with me, but I hope to receive some feedback from you.
First of all you have a website that could be your first step to begin your research! The website is www.findamasters.com and has thousands of masters to see. Second, you must visit the website of British Council, http://www.britishcouncil.org/learning-study-in-the-uk.htm?mtklink=corpo

I’m very impressed with the organization of some universities in UK. It was a surprised for me the possibility of take the master degree by distance and to visit the University in the open days!

If you are outside UK and cannot stay much time there, you can take the master on your country by a specific web platform and with an instructor exclusive for you. The main disadvantages are the price, that is higher, and the time to take the master is the double of the normal one!!! But you could conciliate the master with a part-time job!

I selected the universities that you should take a look and see the master contents and the respective prospectus.


http://www.soi.city.ac.uk/pgcourses/index.html


http://www.wmin.ac.uk/cscs/page-1578


http://www.gre.ac.uk/courses/pg/com/dwdm

My friend subhash, we know that you attended your master degree in Greenwich University, so please give us some feedback about it, ok? eheheh...

And a curiosity, if someone are interested, it was here http://www.newman.ac.uk that I finished my degree some years ago in Birmingham (UK), at NewMan College on Socrates European Program!
If someone could send some feedback I will appreciate, because maybe I will return to UK soon…
Regards!!