Ben Sullins Tech

Recently I was on a call with a client and one of their IT guys asked if the Microsoft Business Intelligence stack can do everything Exclesius can do. In short, the answer is sorta. Through reporting services the MS BI stack can provide similar functionality however, it cannot provide the flash animations you get in Excelsius. The good news is however, that Excelsius can use the MS BI stack as a source for its data. This means if a client has already invested in Excelsius they can still reap the benefits of using the Microsoft Business Intelligence stack and are not locked in to using Business Objects for their entire Business Intelligence platform. I have included some links below of Excelsius demo’s you can view if you’re unfamiliar. Hope this helps!

Excelsius Demos – link

Business Objects SharePoint Integration Options – PDF

Just had some ideas from class tonight regarding Earned Value Analysis (EVA). If you’re unfamiliar with EVA google it and you’ll find it is basically a way to measure a projects performance. Microsoft has a small ‘about’ page found here that might help as well – http://office.microsoft.com/en-us/project/HP010342581033.aspx

The three key elements of EVA are:

1. Budgeted Cost of Work Scheduled
2. Actual Cost of Work Performed
3. Budgeted Cost of Work Performed

These are the ‘Measures’ of the project. The Key Performance Indicators you can derive based on these measures are:

1.  Cost Variance
2.  Schedule Variance

Sounds just like a dashboard right? Anyone interested in coming up with a PerformancePoint dashboard with these metrics based on Visual Studio Team Foundation Server? I know I am…

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).

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!

Sometimes files are received such as client feeds that contain values in columns that exceed the maximum number of characters allowed in the destination database. When these records are imported an error occurs when trying to fit this value into the column with the character limitation. In these cases it is helpful to have a quick way to identify which fields and specific records are causing the issue. Ideally the ETL process that loads the data would provide this information to us but, in the spirit of catching errors before they occur, here is a quick way to accomplish this in Excel.

Assumptions

1. The file is in a format that can be parsed by Excel (tab delimited, CSV, etc…)
2. The number of rows does not exceed the maximum allowed by the version of MS Excel being used.
   • Excel 2003 = 65,536 rows
   • Excel 2007 = 1,048,576 rows

Steps

1. Open the file in excel (parse it if it’s text)
2. Insert a new row above the header row
3. Enter the following formula in every column of the new row that is being imported
   • =MAX(LEN(A2:A65636))
   • The range inside the forumla should cover all of the rows in the column (eg. A2:A100) for records 2 – 100
   • In order for this formula to work properly you must press ctrl+shft+enter after typing in the formula.
   • The best way to get this into all the additional columns would be to simply copy and paste.

At this point you will be able to see the max length of the values in the columns with the formula entered. To make this more readable and even easier to identify try completing these additional steps below.

Additional Steps

1. Select both the row with the formula in it and the header row for all columns with values and press ctrl+c (copy)
2. In a new sheet under the Home > Paste > Paste Special
3. From the paste special dialogue box check ‘Values’ and ‘Transpose’ then click ok

You will now see the fields and their max lengths stored vertically instead of horizontally as they were on the original sheet which I’ve found to be the easiest to read.

Hope this helps!