BI Blog

BI Tools - First Look to Excel 2010

2009-12-19T22:56:00Z

Create Dashboards never been so easy ... the new version of Excel 2010 (Beta) is really amazing. Exploration of a multidimensional cube has never been so intuitive and simple like it is now. The image below shows a simple dashboard that I made in this new version in 20 minutes for the Northwind database sample.

Read the entire post here: http://pedrocgd.blogspot.com/2009/12/bi-tools-first-look-to-excel-2010.html

StepByStep - SSIS Calling a WebService with Web Service Task

2009-11-30T19:09:00Z

Dear friends,

I'm back again and with a new StepByStep paper. This paper focus on a way to call a Web Service from a SSIS package.

There is lot of issues relating to this subject and I decided to publish about it... I hope this can help you.

Please let me know, if you check some errors/bugs in the attached material.

Read the entire StepByStep solution in the attached document below.

BI Certifications - CBIP/TDWI Certifications done!

2009-11-13T00:20:00Z

I started writing this post on the return trip to Portugal. It was a very enriching and experience trip, which I reached one more objective I had in mind for more than two years. I passed the four exams and got the specialization in Business Analytics and Data Management provided by The Data Warehouse Institute (TDWI - www.TDWI.org)

Read the entire post here: http://pedrocgd.blogspot.com/2009/11/bi-certifications-cbiptdwi.html

Regards,

Pedro

BI Tools - SQL Server 2008 R2 CTP version Announcement

2009-08-10T23:29:00Z

Today I finally received an e-mail from Microsoft announcing the release of the CTP version (Community Technology Preview) for SQL 2008 R2 ... and I'm really excited to take the first look and for sure tomorrow I’ll download it ... I'm still focused on my master thesis, any way I will try to "play" a little this release and give a little feedback to you.

I truly believe that today Microsoft made a big step to beat all the competitors. Microsoft doesn’t have several decades of proved work, but has few years that are being improving and investing a lot in the Business Intelligence “world”

Check the entire post in my personal blog at:

http://pedrocgd.blogspot.com/2009/08/bi-tools-microsft-beating-bi-market.html

Regards,

Pedro

StepByStep - SSIS Extract data from multiple Excel worksheets

2009-08-06T11:22:00Z

Scenario

Every month a company receive customers data from an excel file to extract and load data into company data warehouse. In brief, is required to extract data from a single excel file with multiple worksheets with same schema into a single destination table in SQL server 2005. Example of an excel file with three worksheets

Purposed solution

The purposed solution is creating a SQL Server Integration Services (SSIS) package that in two steps:

1. Get a list of all the worksheet names inside excel file (assuming that is unknown before running the package)

2. Use the foreach loop task to pick each worksheet name that will be dynamically read by excel source component and loaded into destination component (Ex: SQL Server, Oracle…)

Read the entire StepByStep solution in the attached document below.

SQL2008 - Cumulative Updates

2009-07-26T21:23:00Z

Microsoft released last week new updates for the recent SQL Server 2008 edition.

Check links below and don't forget to maintain your SQL Server updated.

Cumulative Update #3 for SQL Server 2008 SP1

Cumulative Update #6 for SQL Server 2008 RTM

regards,

Pedro

BI SSAS - What is an OLAP cube?

2009-06-04T16:44:00Z

I was answering some questions at Experts-Exchange website and found an interesting question with an even more interesting, simple and objective answer wrote by Josh. I did not resist to share with you… could be very intuitive for the beginners…

"A cube allows the administrator to define Dimensions, such as Time, Locations, Sales Orders, Invoice Numbers etc. It also allows you to define measures, such as sales amounts, invoice amounts, average days between quote & sales etc. etc.

The big thing with OLAP cubes, is the relationships between the Dimensions, and the measures, and the supporting data are designed once. Users can slice the data based on the dimensions any way they want and the data that is returned is in the ideal world, validated and up to date.

Cubes also allow you to take things such as Order Status, that in a traditional ERP database might be a number , say 1=open, 2=cancelled,3=complete to the average user, unless they have a good understanding of the ERP, that status field would be useless. During the ETL process, the DBA can define a Dimension for "order status" but rather than using 1, 2 or 3, can use meaningful words. (ETL stands for Extract, Transform & Load)

Analysis Services is a fun environment to work in... An advice if you are starting to design some Cubes in Analysis services…. If you are going to be doing any sort of KPI development, make sure you get the KPI definitions from management who will be using them. I'm currently retrofitting a set of cubes that have a lot of bum data because we didn't have all of the definitions up front."

Josh is an IT Analyst for a global manufacturing company. Most of his time is spent developing Business Intelligence, mainly reporting solutions in SSAS and SSRS.

BI Events - Tech Ed 2009, SQL Server 2008 R2

2009-05-17T19:12:00Z

Last year Microsoft shared information about Kilimanjaro at the company’s second annual Business Intelligence Conference, which kicked off on October 6 in Seattle. Kilimanjaro is set to include self-service analysis tools (codenamed “Gemini”) that Microsoft is saying will allow information workers to better “slice and dice data and create their own BI (business intelligence) applications and assets to share and collaborate on from within the familiar, everyday Microsoft Office productivity tools they already use.”

But last week at TechEd 2009 conferences, Microsoft announced SQL Server 2008 R2, which you might recall as previous code of Kilimanjaro. What? You thought Kilimanjaro was SQL Server 2010? Well, if you Google it, you'll see that many others think the same, and in fact there are announcements from very significant sources over the last few months talking about SQL Server 2010. So the information now is that there is no SQL Server 2010. "Never was." That the BI and self-service tools that were part of the release were actually the R2 release. That "Kilimanjaro" always referred to R2. The next full release of SQL Server will follow the R2 release by about 24-36 months. You can expect to see full release of R2 in the first half of next year - CTPs should follow later this year for testing.

I understand that Microsoft, like any company has to adapt its strategy when the world is currently in a big financial problem. In my opinion, Microsoft will be easier to convince customers to upgrade a SQL 2008 to SQL 2008 R2 than convince SQL 2008 to SQL 2010... well ... but starting to be many products, many names, many service packs, many cumulative packages, many functionalities ... truly begin to create confusion in people, that usually have great difficulty accept the change. I hope at least, that Microsoft does not change the format and design of the next version of Excel which is expected to have many more capabilities than Excel 2007. We shall see ... I am very curious about the improvements on the analytical tools and on the "Master Data Services"

Also, their advertising video is pretty funny (the introduction part). You can also sign up to receive notifications of the CTP:

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx
Regards, Pedro

BI Concepts - SQLBI Methodology

2009-05-15T23:18:00Z

I could not talk about business intelligence and Microsoft tools not mentioning Marco Russo and Albert Ferrari. Both have contributed greatly to help simplify complex problems in architecture and methodologies for complex problems. The discussion was launched few months ago in my personal blog and in the official blog. Please take a look, because it will be very helpful if you are looking to implement better methodologies and architectures.

I highlight three works from Marco and Albert that you should not miss:

1. Introduction to the SQLBI Methodology

This is the first paper about the SQLBI Methodology; it introduces the SQLBI Methodology architecture, comparing it to Inmon and Kimball methodologies. I made several questions to Marco and Albert that they kindly answered me in my blog. I also suggested a few changes... join the discussion and help Marco and Albert to make an even better work.

2. SQLBI Methodology at work

It provides a complete data model and ETL of data coming from the well known "Adventure Works" and put into practice the methodology described in by them in the "Introduction to the SQL BI Methodology". I've not read this paper yet, but I will surely do it soon.

3. The Many to Many Revolution

There is also a fantastic book publish with a symbolic price of 5 € (Euros) about advanced dimensional modeling. It’s an interesting book that describes real world problems

“Analysis Services 2005 (SSAS 2005) introduced the capability to handle many-to-many relationships between dimensions. At a first glance, you may tend to underscore the importance of this feature: after all, Analysis Services 2000 and many other OLAP engines do not offer many-to-many relationships. Yet, its lack did not limit their adoption and, apparently, only a few businesses really require it. However, as this paper shows, the UDM (Unified Dimensional Model) can leverage many-to-many relationships helping you to present data from different perspectives that are not feasible with a traditional star schema. This opens a brand new world of opportunities that transcends the limits of traditional OLAP. We will explore many different uses of many-to-many relationships that give us more choices to model effectively business needs.”

Visit them at here and also visit the discussion at my blog at here

I hope this article be useful to you. Pedro

StepByStep - SSIS Moving huge amount of files

2009-05-14T16:09:00Z

Scenario

You have a huge amount of files in a file system folder and you need to move the new or modified files to another location. This example could be applied to several other scenarios, like reading data for some specific file (depending on file attributes) and in spite of using File System Task, you use a Data Flow task.

The source and destination folders are set dynamically, through SSIS variables. It’s also assumed that new files are those between current date and current date less n days (n days is a variable that could be 2 or more days)

Purposed solution

One possible solution is to use the for each loop task to pick each file in a specific filesystem folder and check the respective attributes of each within a script task.

Read the entire StepByStep example in the attached document below.

Reference: SSIS_EX_02_FELC

StepByStep - SSIS Using Package Configurations

2009-05-13T16:11:00Z

Dear friends,
Today I’ll add to the community a new type of post, a StepByStep post, based on the support that I usually provide to users from MSDN Forums and Experts-Exchange… some real examples and real scenarios from real world. I'm trying to purpose the best solution but could have several other solutions based on your feedback and suggestions… this is the goal of this community... share knowledge…

Scenario

After being created several packages for an ETL project, it became necessary to allow them to be copied between multiple environments (development, production and quality) with the greatest possible autonomy.

Purposed solution

One possible solution is to use the XML functionality available in SSIS package configuration, which allows the configuration (server name / Database name) of the main database be stored in a XML file. You need to modify this file only once in each environment and you should store in the file system of each environment
(Ex: c:\MyProjectName.dtsconfig)

Read the entire StepByStep example in the attached document below.

Reference: SSIS_EX_01_PCKCFG

TDWI European Conference (Munich/June2009)

2009-05-11T23:26:00Z

TDWI will present next June, the European Conference held in Munich. For those who have ability or financial support from their companies (as are several that will be the sponsor) is without doubt an event not to be missed ... surely the best speakers in this area will be there!!!

I have learned much from TDWI, especially with its excellent articles and structured way to approach the Business Intelligence topic and its five areas of specialization and for which there is an official certification associated.

• Administration and Technology
• Business Analytics
• Data Analysis & Design
• Data Integration
• Leadership and Management

I will not raise the issue of certifications, maybe next time, because it’s undoubtedly a controversial issue ... I’m in favor of certification only if it’s a motivation for us to move and learn more ... if any of you are thinking to take the exams, please let me know to do it together (I already bought the training-kit) ... I'm thinking to go to Germany to make them…

Some interesting links:

Speciality Areas

Certified Business Intelligence Professional (CBIP)

TDWI 2009 Munich (English Version)

Regards. Pedro

BI Resort - Apresentação

2009-05-09T23:46:00Z

Este é o meu primeiro artigo nesta comunidade BI sendo um pouco uma introdução, e como tal, gostaria antes de mais, desejar que seja um ponto de encontro para todos os estudantes, professores e profissionais na área de BI, e que seja um lugar onde possamos partilhar conhecimento, trocar experiências e tornarmo-nos individual e colectivamente ainda melhores. Apesar de ser típico desvalorizar tudo o que se faz em Portugal, eu acho sinceramente que existem muitas pessoas com grande potencial neste país.

Os membros fundadores desta comunidade colaboram em empresas parceiras da Microsoft, mas poderiam ser muitos outros profissionais com interesse por "este mundo". O primeiro passo está dado, e resta contar com todo o teu apoio para tornar esta iniciativa um sucesso. Da minha parte, prometo que estarei presente por aqui, já tenho um blog à dois anos (www.pedrocgd.blogspot.com) e ainda não perdi o folego nesta longa caminhada. Já tive o prazer de me cruzar com muitas pessoas interessadas por este tema, mas de facto faltava um ponto de encontro para troca de conhecimento e nos organizarmos.

Se sentes curiosidade por conhecer algo mais sobre o Business Intelligence... se sentes que podes ajudar alguem a resolver problemas teóricos ou práticos... se tiveste uma experiencia nesta area e que podes partilhar... se achas interessante trazermos a Portugal comunicadores nesta area... se achas que consegues ajudar a tornar este mundo melhor... então participa nesta comunidade. As portas estão abertas a todos! À medida que formos evoluindo, mais detalhes sobre objectivos a curto, médio e longo prazo serão divulgados.

Porque acho que é hora de "abrirmos as portas ao mundo", e partilharnos não só conhecimento entre nós portugueses, mas também pelos excelentes profissionais que existem pelo mundo fora, os próximos artigos serão escritos em inglês (pelo menos enquanto não arranjar uma forma de o fazer nas duas linguas)

Por um mundo melhor! A tua ajuda é importante! Obrigado!

In english:

This is my first article in this community and I’m starting with a little introduction. First of all, I want to wish this community to be a meeting point for all students, teachers and professionals in the BI area, with the goal of share knowledge, exchange experiences and become better individually and collectively. Although a typical devalue of all that is doing here in Portugal, I sincerely think there are many people with great potential in this country.

The founding members of this community work in Microsoft's partner companies, but could be many other professionals with an interest in "this world". The first step is taken, and we hope to have all your support to make this initiative a success. For my part, I promise to be present here for long… I have a blog for two years (www.pedrocgd.blogspot.com) and have not lost my breath in this long journey. I had the pleasure to meet with many interested people in this subject, but in fact lacked a meeting place for exchange knowledge and for some organization.

If you feel curious to know something more about the Business Intelligence ... If you feel you can help someone to solve theoretical or practical problems ... if you had an experience in this area that could be shared... If you think it could be interesting bring to Portugal some communicators in this area ... if you think you can help make this world better ... then participate in this community. The doors are open to all! As we progressed, more details on objectives in the short, medium and long term will be shared with you.

I think it's time to "open the doors to the world", not only because there are excellent professionals around the world, but for ourselves, the next articles will be written in English (at least until I find a way to do so in the two languages)

Pedro Perfeito (PedroCGD)