Category Archives: Data Architecture

SSIS Event Log Business Intelligence

Posted on by
2

Last Thursday I presented at the San Diego SQL Server User Group (www.sdsqlug.org) a codeplex project I developed based on the SSIS Event Logs. Below are links to the codeplex project as well as the slides I presented.

The inspiration for this project comes from a former position I held managing a team of database developers in which we were responsible for all the Customer Data Integration (CDI) processes as well as the Extract Transform and Load (ETL) processes for our Enterprise Data Warehouse (EDW).

While in that role I found that some of the CDI proceses were running slow to the point that it was affecting business operations. Poking around the native SSIS Event Log data it was really difficult to get any clue as to what part of the process was the bottleneck.

Now I knew that Microsoft had previously released some report packs (link) for the SSIS Event Log data that contained the logic I needed to extract this data. So I took these reports and reverse engineered them into an ETL process to load a new Data Mart (DM) based on the SSIS Event Log data.

Add an SSAS cube and some basic reports and I was now able to analyze the performance of my CDI and ETL processes easily and determine where bottlenecks exist and improvements can be made. Another interesting outcome of this is the ability to measure the improvements of changes to the CDI and ETL processes over time.

This project is designed to be generic so that anyone using SSIS can take advantage of this and gain some insight into their process performance. If you are interested in obtaining a copy of the source code please create a codeplex user account and send me a request on the project site (below).

Hope this helps!
Ben

SSIS Event Log Business Intelligence Codeplex Home – http://ssisbi.codeplex.com
Codeplex Registration – https://www.codeplex.com/site/register
San Diego SQL Server User Group – http://www.sdsqlug.org
SSIS on MSDN – http://msdn.microsoft.com/en-us/library/ms141026.aspx

Microsoft Healthcare User Group Presentation

Posted on by
1
Me and Rick trying to decide who is going to try the mic first

Me and Rick trying to decide who is going to try the mic first

Wow, what a day yesterday. Gave a presentation at the Microsoft Healthcare User Group Conference with Rick Garcia of Methodist hospital to two completely packed rooms. Funny thing, Rick and I had no clue we were supposed to be using the microphones because there was another room full of attendees that obviously couldn’t hear us without them.

In the presentation Rick talked about how they, with the help of Courtyard Group, have reformed their Business Intelligence (BI) organization to be driven by the end-user versus by the IT team. This is key in all BI organizations as, it is not something that IT (in most cases) will truly understand usually.

When IT is the driver behind BI initiatives what I’ve found is that users will simply take the data that IT is providing them, import it into Excel or Access, and then build their own BI from it. This is bad. From an organizational standpiont you have a valuable resource spending time collecting, formatting, and organizing data (something much better handeled by IT when given proper requirements) rather than doing the true analysis. From an Business Continuity (BCP) standpoint you are also now subject to a single point of failure since the analyst will most likely save the reports to their desktop

My portion of the presentation covered the Microsoft Business Intelligence stack and how we are using that to help Methodists IT group focus more on delivering the analysis tools to the users rather than doing the reports themselves. This falls into that category of “BI for the masses” and helping create that reality where IT’s main focus is to integrate the systems and present the analytical tools to the business users to do the analysis. This is how I believe BI works best and provides the most value to an organization. Of course, all of this is delivered in SharePoint as this is where all end-users are being driven to store, manage, and find any and all information.

I am really excited about where Methodist is going with their Business Intelligence initiative and hope to follow up with some more great details at the next Microsoft Healthcare User Group Conference.

Cheers!
Ben

References:

MS HUG Breakout Sessions: http://www.mshug.org/events/exchange_Sept2009.aspx

Presentation Slides: http://www.mshug.org/docs/exhange2009/Garcia_LeveragingSharePointBI.pdf
Methodist Hospital System: http://www.methodisthealth.com
Courtyard Group: http://www.courtyard-group.com/
SharePoint360: http://sharepoint360.com

Democratizing Business Intelligence using Gemini

Posted on by
5

If you’ve not heard of Gemini yet you’re in for a treat. I found these videos on youtube yesterday of the Gemini demo from the BI conference last year that you can view below.

Gemini is a new part of the MS BI stack that is an add-in for Excel that allows power-users the ability to essentially create their own cubes using virtually any data they can get their hands on.

Being the MS BI geek that I am, this opens up all sorts of new opportunities for prototyping solutions and enabling the business to build their own BI applications.

The key here, that I see, is that these solutions are not managed by IT. So, as soon as one of these Gemini cubes becomes business critical, it will need to be built using formal BI methods and tools so that the proper support procedures can be implemented. This is what scares me :-S

I can foresee smaller businesses that are resistant to change, or are scared of the term “Data Warehouse” from prior failed projects, that would want to implement Gemini solutions as business critical applications. Simply because it’s cheaper to implement in the short term, ignoring the lack of sustainability that a more traditional BI solution yeilds.

Enough rambling for now, watch the videos and let me know what you think…

Using Excel 2007 files as a Source in SSIS 2005

Posted on by
20

Thanks to “jaegd” for his post here: http://forums.microsoft.com/… on how to accomplish this. Below please find some more details along w/ a example which can be downloaded here (ssis-2005-excel-2007-source-example).

Steps

  1. Create new OLEDB Connection by right clicking on the Connection Manager tray
    2. New OLEDB Connection in SSIS

    New OLEDB Connection in SSIS

  2. Chose any valid type or value for the connection and click okay
    3. OLEDB Connection Manager

    OLEDB Connection Manager

  3. Rename the Connection Manager to something that makes sense, like “Excel 2007″
  4. Now that the the connection exists right click on it and select properties
  5. Edit the Connection String property IN THE PROPERTIES WINDOW to match below, updating with your Excel File Location
    • Data Source=C:\MyExcelFile.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0;HDR=YES;”

    6. manually-edit-connection-string

    manually-edit-connection-string

You can now use this connection by referencing it with any task/object that would normally connect to an OLEDB source. To query it use [Sheet1$] for the table name (eg. SELECT * FROM [Sheet1$])

Do you have anything to add? Please add your comments below!

Cheers,
Ben

SQL Server Analysis Services (SSAS) Connection Properties

Posted on by
9

Recently I was having some issues getting a new SQL Server Analysis Services (SSAS) cube to process on a new development (dev) server. The error I kept receiving was:

[Analysis Services Execute DDL Task] Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.; 08001; SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ; 08001.

After much toying around I was able to determine what was causing this. In my case I had my shared data source connection for my data warehouse using Windows Authentication and the Impersonation Info set to Default. The problem appears to stem from the translation from Windows Authentication to the Impersonation Info option.

To fix this I changed my shared data source connection to use a SQL Server account and left the Impersonation Info option as default. This then must have allowed SSAS to translate the username and password info when doing the impersonation. I could be wrong but it is now working.

If you have any comments on this or a deeper explination please share? If you too are experiencing this issue o I hope this helps!