Handling Slow Changing Dimensions (SSIS2012)

This post will explain the following:

  • Slow changing dimensions (SCD0,SCD1,SCD2 and SCD3)
  • Inferred Members
  • How to configure a SCD component in a data flow using SSIS2012

Slow-Changing Dimensions are a special data warehouse modeling technique, that allow you to manage how your ETL process will resolve updates and inserts into a dimension, so that you can tell your packages what to do if an attribute value changes.



Staging table from several Excel files (SQL Server 2012)

The last weekend I had an interesting challenge, creating a staging table from 13 excel files that have the same columns, only its data changed, according to the year they were referring to. This way, since 2000, my friends excel files, had data from soccer statistics, one for each season.

He challenged me to create a Business Intelligence solution in just 2 hours so that he could analyze some portion of the data in order to decide if he would invest in a full BI solution or not. This way I came up with this solution:

1.Create a linked server for each excel file

2.Create a query that would select the data from each linked server and then union all records fetched and insert them into a new table.

3.Create five dimensions based of the staging table data

4.Create one fact table

5.Create all packages for dimensions and facts

6.Create a cube

7.Show the data in excel 2013


What is Business Intelligence? (My Vision)

Business Intelligence is a very trendy term these days, managers want it, IT companies are selling it and users are loving the colors of the charts, the ability to drill and roll up between hierarchies drives them crazy, however most of them don´t know what Business Intelligence really is. They often refer to it as building a data warehouse where all company data is integrated with ETL tools like SQL Server Integration Services and analyzed with Analytical tools as SQL Server Analysis Services or even Excel. I am sorry guys, if you think BI is only about this, you are either using it or making it wrong. And start by thinking in this, although emails are a great technology, if you need to talk to a college seated right next to you, will you send him and email? What is the core of your problem here, the communication channel or the message being sent?


Fix 64-bit Dataflow Excel source/destination error (SSIS 2012 – EN-US)

While dealing with SSIS 2012, you might want configure connections to third party platforms, like SQL Server, Oracle DB or even Access or Excel inside your packages data flows. SSIS uses, what Microsoft called Connection Managers to configure this connections, and its easy to think about several scenarios in which your source or destination target could need to use the Excel Connection one, to select or insert data.

The problem this post wants to clarify is only applied to 64-bit environments, to which the Excel Connection manager isn´t compatible. This can be a problem in integration projects, and I speak for my self when I say that several of them, have an initial loading stage in which we get data from several Excel files and integrate them in a SQL Server database for instance.


Non-COM DLLs in C#

This post is a short and very express version of my learnings on how to interact with non com dlls (Native code compiled) such as a C dll, in a C# WCF, which made me loose a lot of time and effort as i didn’t find any good article explaining how should i pass as a parameter a pointer to of a string or how should i pass a whole object based on a custom struct as a parameter with the insurance that all parameters are in the correct order for example.


Custom Snippets Creator (C#)

After a while developing in C# all of you will eventually start using Visual Studio Snippets, however building Custom ones can take time and some effort. When i started to use snippets i searched for tools that would create the snippet for me and even having found some that made that for me i belt my one tool.

This tool is very easy to use and it will allow you to be working in your projects and at the same time create customs Snippets (Once again WITHOUT having to exit your projects, it will only take you 3 seconds to create it).


SyncFusion Ebooks

Hi have recently had a nice challenge in a local Football Club from my town, making a simple user friendly application to manage all their associates as well as their monthly fees. Since i have no time to build complete solutions i resorted to a fantastic Microsoft Solution for Enterprise Management Solution in wich the main develop core is Databases and the front end is almost built for you automatically, i am talking about lightswitch.


Analyse Facebook Friends Geography (en-US/C#) [FOR FUN]

After talking with a friend of mine about our facebook friends, i decided to tell him a more precise information about where are my friends in the globe. In order to give him a response tomorrow i make a simple tool to give me that information.

What i am going to show you here is how i made it and maybe incourage you to check on your friends as well. First thing to do is study the facebook C# API : https://github.com/facebook-csharp-sdk/facebook-csharp-sdk

Technologies used:

  • C#
  • Excel
  • Excel PowerView
  • Facebook C# API


“Lets learn PowerShell” – Pplware

This is my first constribuition for the portuguese technology website pplware (http://pplware.sapo.pt/), in wich every week i will post an article about PowerShell.

This first one is just an introduction to the technology. Its in portuguese but i promise to translate it to english.

Heres is the link for the article : http://pplware.sapo.pt/windows/vamos-aprender-windows-powershell-parte-i/


Think inside of the box? [pt-BR]

Caros amigos e amigas, há já algum tempo que a minha escrita sobre temas da sociedade actual tem estado suspensa, muito devido à minha grande paixão tecnológica que me leva a querer partilhar com a comunidade informática artigos que possam auxiliar os meu homólogos espalhados pelo globo a não passarem pelas mesmas dificuldades que eu venho a passar durante a minha experiencia profissional.