GiveCamp Tampa 2011

This past weekend I participated at GiveCamp Tampa 2011 (http://www.givecamptampabay.org/) as part as the Worldwide GiveCamp (http://www.givecamp.org) weekend sponsored by Microsoft and other partners. Per GiveCamp’s website:

GiveCamp is a weekend-long event where technology professionals from designers, developers and database administrators to marketers and web strategists donate their time to provide solutions for non-profit organizations. Since its inception in 2007, the GiveCamp program has provided benefits to hundreds of charities, worth millions of dollars of developer and designer time in services!

At GiveCamp Tampa 2011 we chose two non-profits to be the recipients of our development efforts. David Liebman and a group of developers, implemented a content management site for a school. I architected and developed a Data Warehouse and Business Intelligence solution along with 3 other developers for the Florida Children Services Counsel (FCSC) (http://www.floridacsc.org).

About Florida Children Services Council (FCSC)

The Florida Children’s Services Council (Florida CSC) is a non-profit association that represents children’s services councils in counties throughout the state. Florida CSC employs the collective strengths of these public organizations to improve young lives by making strategic investments in the well being of Florida’s children. Its mission is to promote policies that build effective primary prevention and early intervention systems for young children and their families

The GiveCamp Tampa 2011 Business Intelligence Team
(From left to right: Pam Shaw, Terry Brennan, Paul Drumm, Wes Helton, Jose Chinchilla)

Florida CSC Business Intelligence Solution

The scope of our work included the following tasks:

1. Installation and Configuration of SharePoint 2010 and Performance Point
2. Installation and Configuration of SQL Server 2008R2
3. Architecture and Development of a Relational Data Warehouse
4. Design and Development of ETL framework using SSIS Packages
5. Design and Development of SSAs OLAP cubes
6. Design and Development of SSRS Reports and Performance Point Dashboards

The dataset in scope for the FCSC BI solution included Budget Allocation and Participant Demographic data for the 8 different counties participating in the SAMIS Collaborative including Pinellas, Broward, Martin, Duval, Palm Beach, Miami-Dade, Hillsborough and St. Lucie.

Sample Performance Point dashboard developed:

Afterthoughts

It was overall a great experience being able to dedicate my time to the Florida CSC knowing that the BI solution delivered will serve as starting point to derive insightful analysis to solve the needs of children and families in the State of Florida. More than 90 hours of combined development time was dedicated to this solution. All the team members rocked!

I really enjoyed working with the GiveCamp Tampa 2011 team and look forward to the next GiveCamp event!

Todo lo que debes saber sobre SSIS en 1 hora!

Gracias a lo asistentes de mi charla Todo lo que debes saber sobre SSIS en 1 hora! en el evento 24HOP LATAM. La verdad me agrado mucho presentar en espanol. Aun cuando mi primera lengua es espanol, todas las presentaciones tecnicas en el pasado las he realizado en ingles.

Si deseas ver la presentacion PowerPoint puedes bajarla dando click en la imagen a continuacion.

Tambien puedes bajar el proyecto que utilize en mi presentacion dando click en la imagen a continuacion. Nota: Solo podras utilizar la base de datos y proyecto en SQL Server Denali, no servira en SQL Server 2008 o 2005.

MDS Error: MDMMemberStatus does not de-activate members through the StgMemberAttribute table

Master Data Services provides a way of de-activating entity members using the StgMemberAttribute table as described in Books On Line http://msdn.microsoft.com/en-us/library/ee633772.aspx

This is a somewhat straightforward approach and requires for you to populate the StgMemberAttribute table with AttributeCode (actual code of the member in MDS), AttributeName as “MDMMemberStatus” and AttributeValue as “De-Activated”.

In a recent MDS implementation, I came across an error when I was trying to de-activate all members from an entity. I followed the same steps I usually follow but the members were still not being deactivated. I noticed that after the rows were processed, a batch number was assigned but the Status_ID column in the StgMemberAttribute remained unchanged with the default value of 0 (ready for staging). The ErrorCode column also remained unchanged.

After several hours of research I bumped into a fix introduced in the MDS June 2010 Cumulative Update. The fix found under the Import and Export section states:

The staging process supports the deletion of members from an entity that supplies domain-based attribute values for another entity.

Bingo! The members I was trying to de-activate were part of an entity being used as the source of a domain-based attribute of another entity. I had confirmed that no members were being referenced and I even deleted this referencing domain-based attribute while trying to de-activate the members.

After applying the MDS June 2010 Cumulative Update I was able to delete the members of this entity. 

As of the date of this post, Service Pack 1 and two additional Cumulative Updates have been released for MDS. You can check what was fixed in these updates and how to install them at the MDS team blog site: http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

Useful queries #3: Get SQL Server Edition, Version, Build and Last Update date and time.

Keeping track of all the versions and editions SQL Server instances can be a daunting task if you don’t have the right tools and documentation. A very easy way to obtain version, edition and build information from your SQL Servers is by querying the SERVERPROPERTY function as follows:

SELECT
SERVERPROPERTY(‘Edition’) AS ‘Edition’,
SERVERPROPERTY(‘ProductVersion’) AS ‘ProductVersion’,
SERVERPROPERTY(‘ProductLevel’) AS ‘ProductLevel’,
SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS ‘LastUpdate’, SERVERPROPERTY(‘ResourceVersion’) AS ‘Version’

Additional info: http://support.microsoft.com/kb/321185