Is Business Intelligence your company's "Tree of Souls" ?

(Minor Avatar spoilers below - in case you're one of the 3 people who hasn't seen it yet)


I was driving to a clients this past week and just free-associating, listening to an NPR segment about the movie Avatar and also thinking about this upcoming Business Intelligence implementation.


In the movie, "Avatar", the "Tree of Souls" is a nexus where sentient beings can connect and understand the will of that planet's deity. At some point the "AllMother", through the "Tree of Souls", acts on the different populations to coordinate their efforts and accomplish something truly remarkable.


It occurred to me that that is what we are trying to do with Business Intelligence: allow people to connect to information and by virtue of the information revealed, align and influence their actions to accomplish something truly remarkable.


So you see, the movie Avatar is really about Business Intelligence.


Is Business Intelligence your company's "Tree of Souls" ?

Windows 7's 'GodMode'

Weird but cool - nice way to shortcut steps in W7:

http://news.cnet.com/8301-13860_3-10423985-56.html?tag=rtcol;pop 

or video  

http://cnettv.cnet.com/enable-godmode-windows-7/9742-1_53-50081662.html?tag=mncol

Dec 8th, 2009 ITA BI Roundtable Presentation

On Dec 8th, I was fortunate to present an overview of SQL Server 2008 Integration Services to the Illinois Technology Association's Business Intelligence Roundtable. The actively-involved participants had many questions. Here are links to all of the prerequisite, supplemental, and follow-on resources we discussed:

Dimension Design (aka Star Schema) Methodology:

• The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition), Kimball/Ross - This is the book that describes many of the best techniques to model "star" or "snowflake" schemas. As I mentioned, this book should be on any BI practitioner's bookshelf, regardless of platform.
• The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset - This book contains a some of the content of the above book, but focuses on the specific implementation with Microsoft tools. It also describes techniques to handle many of the BI-related issues we discussed such as
  
  
  
  • logging
  • restarting long running processes
  • bookmarking
  • configurations
  • late arriving dimensions
  • lineage/auditing
  
  
  
  

Code Examples:


While these examples are in SSIS 2005, they can be easily ported to SQL 2008. Most of the main techniques employed are still valid in SSIS 2008.

• Project REAL: Technical Overview
• Project REAL Reference Implementation
• The white paper dealing with ETL practices specifically is here

Development Utilities:

• Please see this blog entry

The Presentation:

• There were only a few slides since I wanted to focus primarily on demonstrations, but you can find the PDF linked here:

If you attended and I missed posting something, please add a blog comment or email me via the contact information in the PDF.

Tom

Creating great courseware

I was recently surveyed by Microsoft about the Microsoft Learning: Microsoft Certified Trainer program. One of the questions was about the Microsoft Official Curriculum (MOC). I've been using MOC in one form or another for about 15 years. Although I was only answering a question in the moment, I grabbed my comment about how to create courseware into a text file, and it seemed worth keeping. Rather than keep it in a text file, I'll "keep it" on my blog. My comment was: 

Creating great courseware is hard work, but not impossible: 1) Decide on learning objectives 2) Vet learning objectives with MCT's and experienced practitioners 3) CREATE LAB EXERCISES FIRST 4) Test lab exercises 5) Test them again 6) With lab exercises complete and FROZEN, composed supporting slides and course materials

I actually started writing courseware a few years before my first exposure to MOC. The method described above was how we did it. It makes sense because you want the practical exercises to support the learning objective and these are the hardest to get correct. Isn't it irritating when you are learning a new technology and the lab exercises don't work as designed?


I have noticed that there seem to be more problems with MOC courseware lately, especially with how labs work. I wonder if they follow the above methodology.


Don't get me wrong - the MOC courseware is not terrible - I still think there are a lot of good things to be learned and valuable experiences to be had in the courses I teach. But it seems to me that the overall quality used to be higher, and I'd really like to see the quality of the MOC improve. 


BTW, I don't teach every single MOC course. The above comments apply to course: 2778, 2779, 2780, 2784, 2790, 2791, 2792, 2793, 6158, 6231, 6232, 6234, 6235, 6236, 6317 and maybe a few others.

Fixing "Unexpected error occurred. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

( If you don’t do hard-core Analysis Services, Reporting Services, or Integration Services development work with Microsoft Business Intelligence Development Studio, you can safely ignore this blog. )

On my workstation there was an incompatibility between Visual Studio and the SQL Server Binaries. Which led to this error:

Symptom: Certain operations from Business Intelligence Development Studio work as normal, and other return a message: “Unexpected error occurred. Attempted to read or write protected memory. This is often an indication that other memory is corrupt.” (See attached)

This error can occur if there are incompatibilities, between the versions of msmdlocal.dll and msmgdsrv.dll in these two locations:
C:\Program Files\common files\system\ole db
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

Issue: Make sure the build number of your local binaries matches the build number of the server you are working with. In this case the target server is at 9.0.4035.0, however on my system we were actually running at 2 other build numbers:
In C:\Program Files\common files\system\ole db msmdlocal.dll and msmgdsrv.dll was 9.0.3042.0, but in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies, msmdlocal.dll and msmgdsrv.dll were at 9.0.1399.0

Solution
My solution was to apply both SQL Server SP3 and Visual Studio SP1 (or whichever versions match your target environment).

References:

• http://dbaspot.com/forums/sqlserver-olap/162563-error-attempted-read-write-protected-memory-after-hotfix.html

Add-ons no MS Business Intelligence Developer should be without

The main two applications that are used for Microsoft-centric development are Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS). There are a few add-on utilities that increase the power and usefulness of the out of the box applications. I find these invaluable:

• MDXStudio - the brilliant Mosha Pasumansky's take on how MDX development should take place. Tons of functionality. Worth the download if you only use it to format your MDX (but check out the other capabilities, too):
• BIDSHelper - An add-in for BIDS that provides great capabilities for validating dimensions, seeing which SSIS objects are affected by configurations, and much more. There are versions for both SQL 2005 and SQL 2008
• SSMS Tools Pack - (Thanks to Steve for showing me this one) Best features I've used so far are:
  
• Window Connection Coloring
• Query Execution History and Current Window History
• Generate Insert statements from tables

Quick SQL Server tip : Find references to table or column names

Many times I have to look within a database and find all the programmable objects that refer to a particular column or table name (any string really).

There's probably a slicker, more "politically correct" way to do this, but this is what I generally use to accomplish this:

SELECT DISTINCT 'EXEC sp_helptext [' + OBJECT_NAME(id) + ']' FROM syscomments WHERE charindex('soughtfortoken', text) > 0

I've also got the output formatted to include the call to sp_helptext so that I can just paste this into a query window to see the source code that created the object. I suppose I could enhance this so it just executes via another call to EXEC, but this seems to do the job pretty well and requires no additional setup or installation.

Technically it is bad practice to query the sys* tables. Here's the equivalent query using approved systems views:

SELECT DISTINCT 'EXEC sp_helptext [' + OBJECT_NAME(object_id) + ']' FROM sys.sql_modules WHERE charindex('soughtfortoken', definition) > 0 ORDER BY 1