Information in Context

self-exemplify - Dr. Edward Tufte's seminar in Chicago July 23

2010-07-23T23:27:00.000-05:00

In his book, The Visual Display of Quantitative Information, 2nd edition, Edward Tufte describes how he wanted to make the book, self-exemplifying. In other words, the book should use the techniques about the effective display of information to describe techniques about the effective display of information; it should be an example of itself. Dr. Tufte's Seminar today in Chicago did the same thing.

I often find myself experiencing a great sense of relief and identification when encountering a thought leader who has wrestled with the same issues as I have in my work, and aspired towards some effective execution, whether this is with technology issues or softer issues like managing complexity, personal productivity, etc. I felt the same sense of relief in Dr. Tufte's extremely concise and coherent exposition on effective information display.

This was a fairly dense 5 hours. I found myself taking some notes, but for the most part trying to pay attention and absorb what was presented. I was noticing that Dr. Tufte was not shy about opinions and frequently would illustrate points with succinct and boldly-worded statements about best practices (or lack thereof).

I am reproducing some of these statements and impressions here, as hastily noted. If you do anything with the presentation of information (whether that be PTA newsletters or CPM Dashboards), please do yourself a favor and participate in these wonder learnings if you have the chance.

Here are Dr. Tufte's ideas and statements in roughly chronological order. Also, it's possible I have paraphrased him inaccurately. I'll claim any mistakes as the heat of the moment. Dr. Tufte is definitely more lucid than my note-taking ability. This is intentionally left in "brain-dump" format. I think many of these statements are provocative on their own, but I hope these might intrigue anyone reading to either seek out Dr. Tufte's books (Beautiful Evidence, Visual Explanations: Images and Quantities, Evidence and Narrative, Envisioning Information, The Visual Display of Quantitative Information, 2nd edition) and/or his seminar.

Presenting (to others) is a moral act.
Examination of the Music Animation Machine. Especially how there is no legend or guidelines yet it's a very dense and intuitive presentation of information
Dr. Tufte used this as an example that refutes the idea of "information overload" and he cited several examples of dense presentation throughout the seminar (e.g. 800,000 data points on 2 sides of an 11x17 page)
- "There's no such thing as information overload, just lousy design"
A worthwhile diagram deserves the same amount of intention as the text that would impart the equivalent amount of information
Graphics are frequently used to depict causality
- Policy and prevention missions both need to analyze causality
Every linking line should be annotated
The map is the gold standard for effective presentation
"Chart junk" should always be replaced by information
In a graphic presentation, are you using the results of evidence, or "evidence selection" i.e. are you cherry-picking favorable data?
One should assume that presenters have similar motivations as the audience (and vice-versa)
You want an open mind, but not an empty head.
Maximize content reasoning time; minimize content interpreting time.
Paper has 10 times the resolution as a computer display. Paper has 100 times the resolution of projected slides
Authoritarian presenters tend to distrust their audience. This creates the tendency to stint information. (3 points per slide - sound familiar?)
Rather than "know your audience", "know your content". Respect your audience instead.
Do whatever it takes to impart the content. e.g. Sock Puppets, real objects, physical models. Don't be constrained by convention
Every time you can get a real object in a presentation, do so.
If possible, see how data is originally collected.
- Example of water being collected from the cleanest part of the river in a pollution impact study
1 + 1 = 3 - describes the phenomenon that 2 graphic elements create 3 effects - the effect of each, and the effect of the juxtaposition of the 2 objects
Local Optimization = Global Pessimization
The goal is to zero out the interface
Omit grids. Good typography supplies enough guidelines.
Tufte then went through 8 fundamental principles which are discussed in his book, Beautiful Evidence
1. show comparisons - "Compared to what?"
2. Illustrate causality
3. show multivariate data. Translation: enrich your data with dimensional attributes
4. integrate all content. There shouldn't be different modes to view the comprehensive presentation
5. Document all sources, scales, and any missing data. It enables the credibility of your presentation.
6. Content counts most of all. Over presentations, style, formatting
7. Locate imporant comparisons in a common space. Use small multipliers.
The point of information display is to assist thinking.
- Most design can be placed in its decade because it is based on fashion. This is not necessary a complete evil
Design is based on human factors.
After 2D drawing is just that: 2D. Perspective drawing is something like 2.33 dimensions
Navigation instruction is a 4D presentation:
- 3 physical dimensions
- and time (the 4th dimension)
Information resolution = the ability to communicate more bits per area unit and/or per time unit
Galileo's telescope was the first increase of information resolution beyond the capabilities of the human eye
Since 1610, information resolution has increased 10 million to 100 million-fold
Make displays worthy of the the human eye/brain system
- The human eye/brain system was measured to a capacity of 10 megapixels per second per optic nerve
- Tufte asked, "Why are we looking at these moronic displays? (PowerPoint)
Label directly; don't use legends
How do you solve the flat-land problem (i.e. displaying 3 dimensional artifacts on a 2-dimensional surface [screen, paper, iphone])
- Use a model
The principles of analytic design come from the principles of analytic thinking
Quoting Steve Jobs (?), "Real artists ship"
Interface design
- (Quoting someone, not sure who - Alan Cooper) "No matter how beautiful your interface is, it would be better if there were less of it."
Forming your summary:
- State what the problem is
- State who cares
- State the solution
Other key pieces of advice
- Show up early
- Finish early (Which Tufte did)

Inexpensive iPad holder - less than $5

2010-05-28T07:28:00.000-05:00

I just needed a temporary solution to hold my iPad at my desk. It's worked out pretty well. Thought I'd post the tip:

It's an Office Depot Plate Holder, Clear Item # 544474

Resetting "Windows XP Mode" to initial settings

2010-05-27T08:39:00.001-05:00

Under Windows 7 64-bit ultimate edition, I've had the need to reset my "Windows XP Mode" to initial settings several times over the past few days. Initially I did this because as I used the XP virtual environment, the VHD file grew to around 10 Gig. Not huge, but I wasn't using it for anything except to run a client's 32-bit only VPN software, so it was wasting a fair amount of space. My main machine has a 256 Gig SSD for the main drive, so I am conservative with space consumption.

The second reason was that I was helping my fiancée (a high-school teacher) obtain some (public domain) video for use in her classroom. Unfortunately this video seemed only to be available in a heavily watermarked version on YouTube (ugh) or in a Real Media stream (double-ugh). So I wound up needing to use a mixture of open source and very old software to convert this video to a format that was playable in the classroom. As a rule I don't install software like that on my production machine - a virtual environment is a great "sandbox" for momentary needs like this one. After I converted this video, I no longer needed all these (rather buggy) utilities, so I wanted to start clean.

I didn't see much on the web on how to reset the "Windows XP Mode" to initial settings. I tried this experiment, which worked pretty well.

To reset Windows XP to factory new condition:

Right click the vmcx file. Select Settings. (On my machine the file is called Windows XP Mode.vmcx and is located in C:\Users\Tom\Virtual Machines)
Note the location of Hard Disk 1 (On my machine this location is called C:\Users\Tom\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\Windows XP Mode.vhd)
Exit the Settings Dialog
Delete the files associated with your "Windows XP Mode" environment. On my machine, these are located in C:\Users\Tom\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\Windows XP Mode.vhd and are called

Windows XP Mode.vhd
Windows XP Mode.vmc
Windows XP Mode.vmc.vpcbackup
Windows XP Mode.vsv

Now, find the original Start Menu shortcut for Windows XP Mode. As long as you've left the Parent Disk in place, the system will prompt you to recreate a new environment:

voilà! - New XP environment (after a few other dialogs and a few minutes building)

Standard cautionary notes:

I haven't researched whether this is the "proper" way to do this. But I've done this multiple times on 2 different machines and it takes less than 10 minutes to reset, install anti-virus, and reinstall VPN software
Obviously when I suggest you delete files on your system, I am assuming you know the implications of this and have everything backed up or have determined that you no longer need anything from the files you are deleting

Burn the Ships

2010-05-25T07:39:00.000-05:00

Although it's probably an apocryphal story, legend has it that Cortez instructed his men to "Burn the Ships" upon encountering the New World. This has become (in my parlance, anyway) a catchphrase for being irrevocably committed to a particular course of action.

In that spirit, yesterday I blew away v1 of my website and started up this version. This will remain a work in progress for a while, since other client commitments will have me quite busy through June.

Anyone using WordPress who has themes or plugins that they love, please leave a comment.

Time (Not Date) Dimension Table SQL code

2010-04-14T12:51:00.000-05:00

I am seeing more call for Time Dimensions. By that I mean Time-of-Day Dimensions. So now I have to retrain myself to call Date Dimensions "Date" and Time Dimensions "Time".

As a follow up to this post, here's some DDL and a quick routine to generate a time of day table. The resolution is to the second, which so far has proved sufficient for my clients' purposes.

Enjoy

SET nocount ON
/*
CREATE TABLE [dbo].[dim_Time](
  [TimeId] [int] NOT NULL,
  [Time] [time](7) NULL,
  [Hours] [tinyint] NULL,
  [Minutes] [tinyint] NULL,
  [Seconds] [tinyint] NULL,
CONSTRAINT [PK_dim_Time] PRIMARY KEY CLUSTERED
(
  [TimeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

TRUNCATE TABLE [dbo].[dim_Time]
GO
SELECT * FROM dbo.dim_time
*/

DECLARE @second1 INT = 0
DECLARE @currtime TIME
DECLARE @msg VARCHAR(30) = ''

WHILE @second1 < 86400
  BEGIN
   SELECT @second1 = @second1 + 1
   SELECT @currtime = Dateadd(ss, @second1, '00:00.00')
   --  SELECT
   --DatePart(hh, @currtime) * 10000 +
   --DatePart(mi, @currtime) * 100 +
   --DatePart(ss, @currtime) AS TimeID
   --, @currtime AS [Time]
   --, DatePart(hh, @currtime) AS [Hours]
   --, DatePart(mi, @currtime) AS [Minutes]
   --, DatePart(ss, @currtime) AS [Seconds]
   INSERT dbo.dim_time
     ([TimeID],
   [Time],
   [Hours],
   [Minutes],
   [Seconds])
   VALUES (
   Datepart(hh, @currtime) * 10000 +
   Datepart(mi, @currtime) * 100 +
   Datepart(ss, @currtime),
     @currtime,
     Datepart(hh, @currtime),
     Datepart(mi, @currtime),
     Datepart(ss, @currtime) )
   IF @second1 % 1000 = 0
   BEGIN
   SELECT @msg = 'Now Processing ' + CAST(@second1 AS VARCHAR(12))
   PRINT @msg
   END
  END

Adventures with Virtualization

2010-04-04T14:40:00.000-05:00

Those of us needing to run BI software in virtual environments are well familiar with the challenges of getting things running smoothly, especially if you are using a laptop as your lab or demo environment. We have a new set of challenges with Windows 7, and with SharePoint 2010.

Challenge #1 - SharePoint 2010 requires 64 bit OS, and the only MS virtualization software that allows for 64-bit is Hyper-V.

Only problem with running Hyper-V is that you cannot sleep a machine with Hyper-V, so if you’re using you laptop as a lab machine, you need to retain an OS that will run productivity apps and sleep. I set up my old laptop using the technique described here and it works well. I haven’t repeated the process for my new laptop yet, but I plan to.

Challenge #2 - ~~Windows 7 Virtual PC requires Hardware Assisted Virtualization~~

Apparently this is no longer true, but I've wrestled with this one for months, in fact in early March I returned a machine that didn’t support hardware assisted virtualization because I needed to run Windows 7 VPC. . MS just released a version of Virtual PC that does not require hardware assisted virtualization.

Other tips that have worked well for me:

Use a fast disk - I was using a 7200 RPM eSATA external drive - faster than the 5400 disk commonly found on laptops. Now I am using an SSD drive- even better.
Store the VHD in a compressed folder. This one struck me as counter-intuitive but anecdotally seems to work.

Brilliantly stupid MDX debugging technique

2010-03-22T12:33:00.001-05:00

Whenever I do something really stupid, rather than keep it to myself, I prefer to blog about it for the entire world to see. Even better - I came up with a debugging technique to save myself from my own, er, stupidity.

The scenario is this: I am developing some SQL Server Analysis Services Date Calculations in MDX for a client. They have three different Date Hierarchies, and wanted the standard, Previous Period, Period to Date, etc calculations. (For a very good explanation of the techniques involved, read this PDF first, then study this refinement from Mosha Pasumansky. )

The MDX looks like this:

Scope([Calculation].[Calculation].[Previous Period]);
   ([Calendar].[Calendar].[Date]) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[Calendar].[Calendar Year],1));
   ([Calendar].[Week].[Calendar Week].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Week].Lag(52));
   ([Calendar].[Calendar].[Calendar Month].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Month].Lag(12));
   ([Calendar].[Calendar].[Calendar Quarter].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Quarter].Lag(4));
   ([Calendar].[Calendar].[Calendar Year].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Year].Lag(1));
   ([Calendar].[NRF].[NRF Week].MEMBERS) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[NRF].[NRF Week],1));
   ([Calendar].[NRF].[NRF Period].MEMBERS) = ([Calculation].[Current Period], [Calendar].[NRF Period].Lag(13));
   ([Calendar].[NRF].[NRF Quarter].MEMBERS) = ([Calculation].[Current Period], [Calendar].[NRF Quarter].Lag(5));
   ([Calendar].[NRF].[NRF Year].MEMBERS) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[NRF].[NRF Year],1));
End Scope;

Pretty standard stuff (sorry about the line wraps):

The symptom was that my Calendar Data Hierarchies were working fine, but the other two (Week and NRF [National Retail Federation]) were not. I worked my way through the data, and it all looked correct. Even a dynamic MDX query in SSMS worked fine. But it was wrong in the cube.

When debugging MDX, I find that it pays to go in small increments and to do patently obvious things like setting Scoped statements to constant values, as in:

Scope([Calculation].[Calculation].[Previous Period]);

([Calendar].[Calendar].[Date]) = 7777;

End Scope;

I had done this with this MDX code in an attempt to see what was going on, and it showed me that the scoped overwrites were working, but the calculations they were over-writing with were not. Then I got an idea to change how I was debugging:

Scope([Calculation].[Calculation].[Previous Period]);

([Calendar].[Calendar].[Date]) = 1;

([Calendar].[Week].[Calendar Week].MEMBERS) = 10;

([Calendar].[Calendar].[Calendar Month].MEMBERS) = 100;

([Calendar].[Calendar].[Calendar Quarter].MEMBERS) = 1000;

([Calendar].[Calendar].[Calendar Year].MEMBERS) = 10000;

([Calendar].[NRF].[NRF Week].MEMBERS) = .0001;

([Calendar].[NRF].[NRF Period].MEMBERS) = .001;

([Calendar].[NRF].[NRF Quarter].MEMBERS) = .01;

([Calendar].[NRF].[NRF Year].MEMBERS) = .1;

End Scope;

Essentially I am using a bit-mask, one position for each level. I split the two different hierarchies at the decimal. This allowed me to see which scoped overwrite statement was in effect as I browsed the cube, and it led me to the solution.

It turns out I had defined my scoped overwrite assignment statements in reverse order. (i.e. from highest level (Year) to lowest (day)) Once I switched them so the highest level statement was evaluated last, everything worked as desired. This was a very stupid mistake, and once it was defined as part of the cube MDX script, was a difficult one to spot. Luckily the debugging technique described above made it pretty obvious what was happening.

Devils and Angels of BI Architecture

2010-03-10T07:42:00.002-06:00

Devil: You don't need to set up a separate star schema database

Angel: Yes you do. We don't want to interfere with the operational systems

Devil: It's sooo much work

Angel: We want to create a simplified schema to benefit our end users

Devil: Phooeh. ETL is BORING

Devil: Just query the source system directly

Devil: What could be better than fresh data

Devil: C'mon the reports not that big

Devil: Everybody's doing it

Intrepid BI Architect: sigh...

Case in point:

This is from an actual email exchange. Names have been changed to protect the innocent.

From: Brave and True BI Developer
Sent: Friday, March 05, 2010 9:33 AM
To: BI Technical Staff
Subject: SSRS Not Responding

Well, this is a new one. I’m on-site at [Dunder-Mifflin] today to do a quick fix on a report and the SSRS instance seems to be refusing to actually render a report. I click on a report that’s been untouched for months with an ultra simple SELECT behind it and the browser just spins with the “Reporting being generated” message in the middle of the browser.

From: Brave and True BI Colleague
Sent: Friday, March 05, 2010 9:34 AM
To: BI Technical Staff
Subject: RE: SSRS Not Responding

Check SQL Profiler as you run the report, verify that it is actually reaching the database (with the correct user information, etc.)

From: Brave and True BI Developer
Sent: Friday, March 05, 2010 10:00 AM
To: BI Technical Staff
Subject: RE: SSRS Not Responding

Got it. There was a transaction open on a table that’s part of a filter on most of the reports. That’s all it was.

______________________________

[My emphasis]

This is why we go to the trouble of setting up an entirely separate system. There should never be a "transaction open on a table that’s part of a filter on most of the reports."

There are times even now when the voices of temptation ring really loud. Usually this occurs in the form of a client or colleague looking at me like I've got two heads when I tell them that we need to spend time building a separate redundant database and ETL to replace a currently running (but poorly performing or problematic) report.

So to all the intrepid BI Architects out there: fight the good fight!

The "Million Dollar" BI Architecture Diagram

2010-02-05T09:35:00.001-06:00

Anyone who has worked with me or taken training from me has seen some variant of this diagram. It's not an original concept, although this version has been refined to include my personal experience with many Business Intelligence implementations, as well as my bias towards both the Kimball Business Dimensional methodology and using Microsoft SQL Server (especially Analysis Services) as part of successful deployments.

This diagram is conceptual, although I have designed systems that did have physical servers for each of the servers represented, and these worked very, very well.

I have lots to say about this, and will continue to do so on this blog. Perhaps the most significant concept to mention at the outset is that this is a recipe for success with Business Intelligence. Put another way, the level of success I've had with BI environments corresponds pretty closely with how well the architecture of these environments matched this diagram. (Also note that I said "a" recipe for success - not "the" recipe for success. I acknowledge that there are other approaches, but this is the one I've used successfully for over a decade.)

Two important concepts inherent in this diagram are:

a) each layer in this diagram has a primary purpose. From an architecture standpoint, designing these functional layers to do only a single thing very well creates a robust, yet simple, environment.

b) There are well-defined interfaces between each functional layer. Having clearly defined boundaries allows the processes to be moved from server to server (which enables important operational benefits like high-availability, disaster recovery, and performance tuning)

What are these primary purposes mentioned above, and how do they benefit the overall architecture?

Source Systems OLTP, Line of Business, ERP systems, and other sources - All BI systems are 100% dependent upon being able to source data from somewhere. The systems that run the business are our sources. All the rich data that we analyze and/or aggregate comes from there.

Data Quality Extraction, Transformation, and Loading - Because the systems in #1 are generally concerned with running the business and not with pristine and perfect data, there must be intentional processes that clean and consolidate data from these sources. That happens in layer 2

Single Vision Enterprise Data Warehouse - This is what an organization "knows" about itself, and enables a single vision of the truth. The is the consolidated, consistent historical data store, ideally becoming the "system of record" after the operational processes are no longer active. Note that under this model, this layer does not have to be tuned for interactive query performance - the primary purpose here is to act as a sound repository of enterprise information.

Business Intelligence - Unified Dimensional Model - Nearly all successful implementations include a layer like this, either informally or formally. In my view, formally is the superior approach. In this layer, performance is improved by creating locally (or proximally) cached copies of information. This is where "Business Intelligence" is exposed - i.e. the metrics, KPI's that provide useful information. All presentation tools should source from this database.

Presentation - By keeping the Business Intelligence in layer 4, the presentation tools really just become about delivering information to the various audiences in the format and vehicle most convenient for each. It allows different tools to be used as needed.

BTW - I call this the "Million Dollar" BI Architecture Diagram because:

if you're an organization using BI this effectively, it should represent (at least) a million dollars in increased revenue or reduced cost.
If you're a BI practitioner, it should be worth that in revenue or perhaps even more significantly, a million dollars in reduced stress - because it represents a plan that is proven and works.

Example Code to build and populate a Date Dimension

2010-01-25T13:15:00.000-06:00

OK, I've found myself searching for this code a half dozen times over as many weeks. That tells me that I :

a) Need to keep better track of my source code

b) Need to share it on my blog

Caveats & Comments:

This is "warts and all" utility code. It could be optimized, but for the few rows that a Date Dimension represents, why?
One of the few cases where Dr. Kimball and his recommends meaningful surrogate keys is in the case of the date dimension. Our keys here use a YYYYMMDD or YYYYQQ format.
This builds a Date Snowflake, which provides correctly ordered keys at any level in the dimension.
I usually rejoin these tables in either the DSV or a SQL View to create the structure SSAS actually consumes.
There is a stub for a Week Hierarchy, which sometimes is called for, and sometimes not.
This was built in and is compatible with SQL 2005. It runs in SQL 2008 as well. In 2008, you could change the date type from DateTime to Date and save yourself some formatting overhead down the road.

/* DDL Begin */

/****** Object: Table [dbo].[dim_CalendarYear] Script Date: 11/02/2009 12:34:56 ******/

CREATE TABLE [dbo].[dim_CalendarYear](

[CalendarYearID] [int] NOT NULL,

[CalendarYearName] [varchar](10) NULL,

CONSTRAINT [PK_dim_CalendarYear] PRIMARY KEY CLUSTERED

(

[CalendarYearID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[dim_CalendarQuarter] Script Date: 11/02/2009 12:34:56 ******/

CREATE TABLE [dbo].[dim_CalendarQuarter](

[CalendarQuarterID] [int] NOT NULL,

[CalendarQuarterNumber] [int] NULL,

[CalendarQuarterName] [varchar](10) NULL,

[CalendarYearID] [int] NULL,

CONSTRAINT [PK_dim_CalendarQuarter] PRIMARY KEY CLUSTERED

(

[CalendarQuarterID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[dim_CalendarMonth] Script Date: 11/02/2009 12:34:56 ******/

CREATE TABLE [dbo].[dim_CalendarMonth](

[CalendarMonthID] [int] NOT NULL,

[CalendarMonthNumber] [int] NULL,

[CalendarMonthName] [varchar](20) NULL,

[CalendarQuarterID] [int] NULL,

CONSTRAINT [PK_dim_CalendarMonth] PRIMARY KEY CLUSTERED

(

[CalendarMonthID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[dim_CalendarDay] Script Date: 11/02/2009 12:34:56 ******/

CREATE TABLE [dbo].[dim_CalendarDay](

[TimeID] [int] NOT NULL,

[SQLDate] [datetime] NULL,

[CalendarMonthID] [int] NULL,

[WeekID] [int] NULL,

CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED

(

[TimeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/* DDL End */

/* Populate Begin */

DECLARE @StartSeedDate DATETIME

DECLARE @EndSeedDate DATETIME

SET @StartSeedDate = '01 Jan 2007'

SET @EndSeedDate = '31 Dec 2009'

-- Set defaults if none are supplied

IF @StartSeedDate IS NULL

SET @StartSeedDate = DATEADD(YYYY,-1,CONVERT(CHAR,GETDATE(),112))

IF @EndSeedDate IS NULL

SET @EndSeedDate = DATEADD(YYYY,1,CONVERT(CHAR,GETDATE(),112))

CREATE TABLE #TMP_DIMDATE (

SQLDATE DATETIME NULL)

WHILE @StartSeedDate <= @EndSeedDate

BEGIN

INSERT #TMP_DIMDATE

(SQLDATE)

VALUES(@StartSeedDate)

SELECT @StartSeedDate = DATEADD(DD,1,@StartSeedDate)

END

-- Year

INSERT dbo.dim_CalendarYear

SELECT DISTINCT

DATEPART(YEAR,SQLDATE) AS [CalendarYearID]

, DATENAME(YEAR,SQLDATE) AS [CalendarYearName]

FROM #TMP_DIMDATE

-- SELECT * FROM dbo.dim_CalendarYear

-- Quarter

INSERT dbo.dim_CalendarQuarter

SELECT DISTINCT

DATEPART(YYYY,SQLDATE) * 100 + DATEPART(QUARTER,SQLDATE) AS [CalendarQuarterID]

, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(QUARTER,SQLDATE) AS [CalendarQuarterNumber]

, CAST(DATEPART(YYYY,SQLDATE) AS char(4)) + ' Q' + CAST(DATEPART(QUARTER,SQLDATE) AS char(1)) AS [CalendarQuarterName]

, DATEPART(YEAR,SQLDATE) AS [CalendarYearID]

FROM #TMP_DIMDATE

-- SELECT * FROM dbo.dim_CalendarQuarter

-- Month

INSERT dbo.dim_CalendarMonth

SELECT DISTINCT

DATEPART(YYYY,SQLDATE) * 100 + DATEPART(MONTH,SQLDATE) AS [CalendarMonthID]

, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(MONTH,SQLDATE) AS [CalendarMonthNumber]

, DATENAME(YYYY,SQLDATE) + ' ' + DATENAME(MM,SQLDATE) AS [CalendarMonthName]

, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(QUARTER,SQLDATE) AS [CalendarQuarterID]

FROM #TMP_DIMDATE

-- SELECT * FROM dbo.dim_CalendarMonth

-- Day

INSERT dbo.dim_CalendarDay

SELECT DISTINCT

DATEPART(YYYY,SQLDATE) * 10000 + DATEPART(MONTH,SQLDATE) * 100 + DATEPART(DAY,SQLDATE) AS [TimeID]

, SQLDATE

, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(MONTH,SQLDATE) AS [CalendarMonthID]

, NULL as [ShopLocalMonthID]

FROM #TMP_DIMDATE

-- SELECT * FROM dbo.dim_CalendarDay

/* Populate End */

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

2010-01-22T14:36:00.000-06:00

(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'

2010-01-09T15:50:00.000-06:00

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

2009-12-14T22:50:00.001-06:00

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

2009-12-10T11:36:00.000-06:00

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

2009-11-07T19:23:00.000-06:00

( 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

2009-10-26T17:53:00.001-05:00

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

2009-10-16T22:18:00.000-05:00

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

Do as I say, not as I do

2009-08-21T16:24:00.006-05:00

Perhaps that should be - Do as I say, not as Adventure Works and Project Real do

SSAS modeling question: What's the best approach to handling header/detail records?

Approach #1) Use Measure Groups

Approach #2) Consolidate via either ETL or relational operations

Most clients I deal with have some form of Header/Details modeled in some of their source systems (for example Invoices/Line Items). In looking at the way MS addresses this in sample databases (AdventureWorks and Project Real), I discovered that these examples solve this problem by not solving it. Both contain a single SalesFact that presents values from what would be the header record, but also include Item measures and attributes.

Conventional SSAS wisdom would have us create 2 measure groups, 1 for Header and 1 for Details. The approaches used in the MS example cubes consolidate the two differently grained tables relationally and thus use a single measure group. (More correctly, they simply present a single Sales table, but it's reasonable to assume that a real-world system would have both Header and Detail)

I definitely know how to model this relationally, and I know how to implement this using either measures groups or a single table. I can see pros and cons to each approach:

Using Measure Groups Approach

Stays "pure" each fact has correct grain
Calculations must be modeled in MDX
Provides most efficient movement of relational data
Minimizes null or N/A columns
Most orthodox

Consolidate via either ETL or relational operations

Allows row-wise calculations
Simplifies cube schema
Removes need for "SalesOrder" dimension (needed to relate details to headers)

My current opinion would be to use proper measure groups because creating a relational structure with multiple grains violates one of Dr. Kimball’s fundamental tenets.

As is often the case with BI modeling, there may well be cases where the second approach proved more advantageous. But absent of a specific requirement that highlights any advantage, I will stick with established best practices, which to my thinking is Kimball. I can understand why Adventure Works takes a short-cut, but it seems a shame that "Project Real" isn't really "Real" so-to-speak. It's supposed to be a real world model.

Ballmer and Muglia have it wrong

2009-07-17T12:28:00.002-05:00

"It's a poor carpenter who blames his tools."

I've been monitoring the twittersphere and blogosphere about the new BI tools coming from Microsoft. While I am excited about these new capabilities, the "buzz" around these things also creates some concerns.

Over the past ten years, I've been fascinated by the persistence of the idea that all BI needs is better tools. I am not arguing against better tools by any means - who doesn't love a great piece of software to accomplish a given task?

The issue is that it's not simply better tools that lead to successful use of analytics. Possessing Excel doesn't turn me into an CPA, nor does having a copy of Illustrator turn me into a graphic artist.

I am not suggesting that BI should only be the domain of the privileged few. But making better tools available to business stakeholders is putting the cart before the horse. Which of course begs the question, "What's the horse?"

The "horse" is of course, much harder to identify than a software purchase, which I think is one of the reasons why tools tend to get the focus over other more nebulous issues. In my opinion, the critical piece that allows effective use of whatever tools are at hand is when an organization embraces an "analytic mindset". In other words, peoples perceptions, beliefs, and capabilities must be the first change agent. Only then can tools become part of the solution.

The analytic mindset looks like:

Intolerance for siloed data, "spreadmarts", do it yourself ETL
Belief in the value of analytics for Corporate Performance Management, that goes all the way to the CxO level. This includes

sponsorship of priorities
creation of cross departmental teams
allocation of budgets.

Acknowledgement that the whole problem won't be solved in a single deployment, but it's better to evolve towards a long term vision, than to do nothing
Emphasis on communication and education
Deep involvement of the business stakeholders, making them partners (with IT) in the development of the BI systems within the organization

Call me cynical, but I am bracing myself for the questions that come after Office 2010 and Gemini are released and in wide use: “We’re now able to look at 100 million rows in Excel, but our people are still arguing over what “the truth” is, and we still have a debate over what success looks like for our organization. These tools aren’t any good.”

I am working with one organization that is embracing the "Analytic Mindset" right now. The C-level sponsors have intentionally not upgraded tools but have instead devoted resources to education and building a sound data infrastructure. I have much better hopes for this organization that is using 4 year old tools, than for any organization that pins its hopes simply on better tools.

The question I am left wondering is "Who is responsible for promoting the message of the 'analytic mindset'?" TDWI does a pretty good job with their "BI Maturity Model". It would be great if some of the tool vendors started to reinforce this idea. Ironically if they would do so, they'd wind up with increased adoption rates (i.e. more sales).

This is why I formed my company to address not only BI Consulting, but also Training and Mentoring. In hopes of helping organizations get the horse before the cart when it comes to success with BI.

The unlikely entrepreneur

2009-07-06T22:35:00.004-05:00

Have you ever found yourself doing something and suddenly realized, "Now I am a grown-up"? Like when you closed on your first home, bought something like a sump pump . . .

I had the "Now I am an entrepreneur." moment yesterday when I was ordering a toll-free number for my office line and had to research which ones supported conference calling.

I almost hate to use the "e-word" because I always envision some super-organized, hyper-motivated, impossibly educated go-getter with an impeccable pedigree. - and that seems like a hard image to fill. I am just a guy with a fair amount of BI experience that realized that certain people and roles were being ignored by traditional consulting models.

I really posted this to document that I went with Kall8 for the toll free number with conference calling. I have only used them a bit, but they seem decent. (If you're looking and would like to knock 5 clams off of my monthly bill, click here and reference eight-eight-eight-678-4279)

Also (very related to the previous post), after researching on the web, I ultimately searched one of my LinkedIn networks to see who had solved this problem already. So that's : Friends - 2 / Web - 0, and counting.

Analysis Paralysis at Web (hosting) speed

2009-05-29T09:38:00.005-05:00

Today I was doing research for the optimal hosting provider for my new business (ContextQ). I had fairly modest needs, but with anything that requires a commitment of time, or perhaps even more important, that technology works as intended, I am a stickler for research and especially customer reviews or testimonials. Perhaps I even go a bit overboard. (My daughter and fiancée would say I definitely go overboard.)

Like anyone reading this (I imagine), I am fairly used to getting around the web, and fairly used to sorting the wheat from the chaff. However, I was not quite prepared for the chaff-to-wheat ratio of this particular type of search. The level of bogus pages to actually useful reviews was astounding.

In hindsight, it makes sense. The web hosting sites are all going to use whatever tools they have in their arsenal to make sure that their entries appear top-most in any search results. These folks are perfectly equipped to spin up as many “sites” as needed to saturate the search engines.

Many of these pages purport to be unbiased, but are clearly propped up shills for various hosts.

I guess I should say at this point that I don't know for a fact whether any of these pages are shills. I suppose it's possible that they could be very badly-written and horribly incomplete attempts at unbiased reviews, but I doubt it. What follows then is a recounting of my searching efforts and my opinion about the sites I found. I did find some good pages, and I will link to those in the end.

I should also say that I think the proportion of negative reviews to positive is pretty high. The energy to fuel a review that comes from frustration is much higher than the energy that comes from complacent satisfaction. A sad but commonplace fact of human nature. This is what steered me away from my original plan of having my page hosted by the original registrar of my domain.

1) The first phenomenon I encountered was the pseudo comparison page:

http://www.webhostingpadreview.org/webhostingpad-vs-lunarpages-budget-cpanel-hosting-comparison

http://www.lunarpagesreview.org/lunarpages-vs-webhostingpad-lunarpages-webhostingpad-comparison

What’s interesting is that each page promotes the service that is contained in the domain name. It’s not that they are biased toward a particular provider; it’s just that they are almost information-free. My guess is that these pages exist simply to support referral revenue.

2) So, what were the mediocre sites? (By mediocre I mean: has what may be legitimate reviews, but only for selected hosting providers).

http://www.alreadyhosting.com/

http://webhosting.reviewitonline.net/

Amazingly enough, two of my “go-to” sites for reviews (PCMag & CNet) had next to nothing to offer for web hosting.

3) What were the “bad” sites? (By bad I mean, deceptively or poorly organized and clearly biased). [Note that I am not linking to the bad sites ...]

webhostingjudge.com

tophosts.com

thetop10bestwebhosting.com

hostreview.com

4) So, what were the decent sites? (By decent I mean not stellar, but a good faith attempt at real reviews and balanced coverage).

webhostinggeeks.com

webhostingjury.com

webhostingsecretrevealed.com (good supplemental info)

webhostingstuff.com

serioushostingreviews.com

hosting-review.com

5) So, what were the good sites?

http://whreviews.com/

http://www.webhostingtalk.com/

What did I end up doing?

I went with a hosting provider that a trusted friend had recently selected. I did also vet the reviews for this provider, but that was a secondary, confirming effort. The decisions and opinions of friends count for a lot!

A tangent: What is needed?

It’s a safe statement that Google’s behavior-derived valuation of pages and their robust web-crawling revolutionized the web. I remember (pre-google) when the problem was that the page was there, but there was no catalog or spider that linked to it so if you didn't already know the URL you were SOL.

We seem to have solved the indexing problem, but now there is too much garbage in the search results. And the “chaff” is smart enough to generate sufficient clicks to appear in top search results.

I am hopeful that the additional value offered by search engines like Kumo/Bing will help us humans find the wheat again. Let’s hope for a chaff-factor to make its way into the bigger search engines.

Final note: if I had known the hassle and delay involved in transferring a domain, I probably would have just let my original registrar host my site. Live and learn. (I can hear my daughter and fiancée chuckling now...)

Welcome! - and what this Blog is about

2009-05-19T10:46:00.004-05:00

I chose "Information in Context" as the name of this blog for two primary reasons:

It's a central concept behind Business Intelligence, which is one of my main passions and endeavors. This is the idea that information really only becomes "Business Intelligence" when it has been imbued with the appropriate context. One might say that the true art of BI rests not with the technology or the methodology, but rather with the accomplishment of creating context for a given audience.
It's sufficiently ambiguous (i.e. flexible) to allow me to post about nearly anything that might be interesting or useful.

The main focus of these postings will be around Business Intelligence, but other areas that might find their way on to the blog could well be personal productivity, cool software, philosophy, music, motorcycles, cooking, photography.