Ben Sullins Tech

“The truth is, there’s a good way to model data in a database… but the way business users want to see the data is far different than that.” (Bansal, 2010)

I disagree. This is the exact reason why Data Warehouses need to be modeled using dimensional modeling techniques that are designed around how the business wants to see the data and are setup for fast query performance. With the data already transformed into the format users are expecting to see it with natural hierarchies and relationships built in, queries are simple and much faster.

There is also the potential to pre-calculate all or most of the aggregations into a multi-dimensional database structure. I have done this countless times with Microsoft’s SQL Server Analysis Services and routinely am able to query fact tables with 20M+ rows in milliseconds.

In my opinion BI interfaces trying to make queries faster is a valiant effort however, without a solid dimensional data structure modeled after the way users want to see the data there is only so much they will be able to accomplish.

My approach as a BI professional has always been to organize the data into conformed structures, present that to end users for consumption in their ad-hoc analysis, and only build formal reports and dashboards as necessary. I think the conformed structures provide great consistency for BI across the enterprise no matter who is building it and allow users to get answers to questions faster.

Of course, there is no way IT can keep up with the onslaught of report requests so it makes sense to provide users with the tools to do these themselves. Typically, I would offer up a connection to a cube through Excel and now with the addition of PowerPivot I believe this type of process is going to become even more prevalent.

Some I have talked with have expressed concern of decentralization of BI by tools like PowerPivot however; I see these tools as giving users a sort of proving ground for their BI applications. If an information worker successfully creates a BI application in PowerPivot that becomes widely used across the enterprise IT does not have to guess that formalizing it into a more formal BI application will have value.

Other methods exist that I think are worth for determining the value of a BI application (eg. BI Pathway Method) however I believe this is for more formal BI versus the ad-hoc type of analysis that users clamor for on a day-to-day basis.

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…

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!