Samb Business Intelligence Blog: August, 2006

August 16

Visio 2007 Pivot Diagrams and Analysis Services

Visio 2007 has a cool new feature for visualizing Analysis Services Hierarchies and measures. It's called a Pivot Diagram.

Here's an example of what can be built....more details can be found at Visio Insights

August 15

Cheap Sparkline-like Data Bars for Excel 2003 and lower...

So, some of the new conditional formatting techniques in Excel 2007 sure are cool. But, did you know you can approximate some of these visualizations in early versions of Excel using the REPEAT (REPT) function?

Here's how...
use the REPT function and reference any whole or floating point number. By specifying a character like the Pipe (|) you can make some pretty interesting "word-sized" graphics (the defining factor of Ed Tufte's Sparklines). I found it useful to use ABS and also to divide these references. You can't do it all in the same cell, like 2007 does, still it's kind of a neat way to get cheap visualization.

Some ideas for improving this - using right and left justification for positive and negative and adding color...

August 05

How do I deploy multiple instances of BSM (Useful for a Single Instance too)?

Found this over on Ian's blog in a nasty big font (direct from Powerpoint) - I cleaned it up a little for your viewing pleasure...

Moving Your BSM Database

· Create a SQL Server Database

· Backup your test BSM database

· Create a new database in your production environment

· Restore the test BSM database

· Choose to overwrite the database in Restore Options

· Repeat backup and restore for any cubes used

· Backup and restore will set security permissions

· In a web farm environment you only need one BSM database

Create a new BSM IIS Site

· Create a new BSM IIS Website

· Create a directory for IIS to use

· Copy the three files from your test BSM website

· Modify web.config with Notepad

· Change the "Data Source" to point to your new BSM SQL Server

Use the IIS Admin to create a new site

· Point to the file location you just created

· Select a unique web port

· Remove Anonymous access

· Select "Execute" website execute permissions

· Change the Application Pool to use the SharePoint Application Pool (Home Directory tab)

Create an IIS site for SharePoint

· Create an IIS Site for SharePoint to use

· Create a file system directory for the site

· Create an new website in the IIS Admin

· Use a unique port number

· Point to the empty file system directory

· Select "Execute" web site permissions

· Remove Anonymous access

Extend SharePoint

· Extend SharePoint to your new site

· Use the SharePoint Administrator

· Virtual Server Configuration

· Extend Virtual Server to the IIS Site

· Use the existing SharePoint Application Pool

· Start your SharePoint site and apply your SharePoint Template

Add the Webparts

· Locate the IIS files on your test SharePoint directory

· Locate the folder in the root called "WPCATALOG"

· Contains two DDL’s

o Microsoft.PerformanceMangement.Scorecards

· Copy the folder and files to your new SharePoint directory

SharePoint Web.Config

· Open on your test SharePoint system the web.config file

· Copy the entire line under <SafeControls>

· Assembly Micrososft.PerformanceManagement.Scorecards.Webpart

· Copy the entire line under <appSettings>

· Key BPM.ConnectionString

· Paste both into the Production web.config

Add to SharePoint Web Gallery

· In IE, open the new SharePoint site

· Select "Site Settings"

· Select "Upload Web Parts"

· Select "Upload multiple file"

· Add the two Micrososft.PerformanceManagement.Scorecards.Webpart

Helpful Reminders

· To publish updates you must point the Business Scorecard Builder to your new BSM IIS Site

· Your BSM Site Application Pool needs to use the same Application Pool as SharePoint

· Ensure that Databases and Cubes have the correct security permissions

· Typically Network Services

· Database Roles: BPDeveloper & BpmViewer

· Look in the Event Viewer Log for any errors

August 02

Buy This Book if you build Portals or Dashboards

OK, so I admit, the cover of Information Dashboard Design by Stephen Few is a Snoozer - it doesn't look very interesting.

Wow - was I ever wrong!

I have been on the lookout for a book like this for quite a while. This book is a cross of Ed Tufte and the 1990's version of Web Pages that Stink. They have great color examples of what to do and, most importantly, what not to do in design.

http://www.amazon.com/gp/product/0596100167/sr=8-1/qid=1154549765/ref=pd_bbs_1/002-7104836-6212804?ie=UTF8

BUY IT NOW!!!!!!!!!!!!!!!

4:21 PM | Add a comment | Permalink | Blog it

August 01

Getting Contacts from your Inbox (or any folder)

Here's some nifty code for pulling contacts from your Inbox, or any folder you point to in VB.Net

Private Sub cmdGetContacts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGetContacts.Click

' YOU NEED TO:

' - add references: Outlook 11 Type Library

' - add a Button called cmdGetContacts (put this code there)

' - add a list box called lstContacts

' - add a progress bar called prgProgress

' - add a label called lblContacts

' - add a chkbox called chkExclude

Dim objOL As Outlook.Application

Dim objNS As Outlook.NameSpace

Dim objFolder As Outlook.Folders

Dim objInnerFolder As Outlook.MAPIFolder

Dim iInnerFolders As Integer

Dim item As Object

Dim MyItems As Outlook.Items

Dim x As Int16

Dim sResult As String

Dim iCount As Integer = 0

Dim iProgress As Integer

Dim i As Integer

objOL =

New Outlook.Application

objNS = objOL.GetNamespace("MAPI")

Dim olFolder As Outlook.MAPIFolder

' Pick a folder to get started with

olFolder = objOL.GetNamespace("MAPI").PickFolder

' Reset everything

iCount = 0

prgProgress.Visible =

True

lstContacts.Items.Clear()

' Is there any mail in the *root* of the picked folder?

MyItems = olFolder.Items

For i = 1 To MyItems.Count

' Handle the output

sResult = ""

sResult = olFolder.Name

Try

If MyItems(i).senderName Is Nothing Then

sResult = sResult & " (No Sender Found) ,"

Else

sResult = sResult & "," & MyItems(i).senderName & ","

End If

sResult = sResult & MyItems(i).SenderEmailAddress

' Is this an internal email?

' Nope, add 'em

If InStr(sResult, "/O=MICROSOFT") = 0 Then

lstContacts.Items.Add(sResult)

Else

' Yep, are we allowed to add 'em?

If chkExclude.Checked = False Then

lstContacts.Items.Add(sResult)

End If

' next record

iCount = iCount + 1

Catch

End Try

' Get a new message

' This does not recurse through multiple folders...

For iInnerFolders = 1 To olFolder.Folders.Count

' Set the target folder iteratively as we move through the folder list

objInnerFolder = olFolder.Folders(iInnerFolders)

MyItems = objInnerFolder.Items

' Look through the messages...

For i = 1 To MyItems.Count

' Handle the output

sResult = ""

sResult = objInnerFolder.Name

Try

If MyItems(i).senderName Is Nothing Then

sResult = sResult & " (No Sender Found) ,"

Else

sResult = sResult & "," & MyItems(i).senderName & ","

End If

sResult = sResult & MyItems(i).SenderEmailAddress

' Is this an internal email?

' Nope, add 'em

If InStr(sResult, "/O=MICROSOFT") = 0 Then

lstContacts.Items.Add(sResult)

iCount = iCount + 1

Else

' Yep, are we allowed to add 'em?

If chkExclude.Checked = False Then

lstContacts.Items.Add(sResult)

iCount = iCount + 1

End If

Catch

End Try

' Show progress as we look through the folders

iProgress = iInnerFolders / olFolder.Folders.Count

prgProgress.Value = iProgress

prgProgress.Refresh()

' Next Message

prgProgress.Visible =

False

lblContacts.Text = "Contacts: " & Format(iCount)

End Sub