April 2011 ITA BI Roundtable – So what can I do with this “BI” stuff anyway?

April 2011 ITA BI Roundtable  – So what can I do with this “BI” stuff anyway?

Here are the slides from today’s session.


Posted in Business Intelligence, Community, Modeling/Architecture | Leave a comment

Creating Dashboards WITHOUT SharePoint

As a business intelligence consultant, a frequent request I receive is to “create a dashboard”. One of the first things I do is verify that I understand what the client is actually asking for. Many times by “dashboard”, what they mean is “lots of stuff all at once”. That’s when I pull out the tried and true “dashboard analogy” which is of a car dashboard. A car dashboard provides only a handful of key indicators that indicate the high-level status of the most important metrics of the operating car (speed, remaining fuel, RPM). There may be some other items, but these are relegated to smaller supporting dials. So it should be with dashboards.

Information Dashboard Design is a great resource. Key ideas from this book: “Less is more” “Keep it simple”.

Another related issue is “can I have a dashboard if I don’t have and don’t plan to use SharePoint”. In many cases I think it can be better to start the dashboard discussion with no immediate plans to use SharePoint. It narrows the focus and keeps things on target (pun intended). SharePoint can do a lot, but it’s also a considerable investment in time and infrastructure. Finally, the use of a dashboard may help to prioritize the investment in SharePoint (or not).

Frequently the tools at hand are simply those in the basic Microsoft SQL Server stack, (SQL Server, Analysis Services, Reporting Service, Integration Services). I’ve frequently created v1.0 of a Dashboard using only these tools.

Scenario #1: Integrate in existing portal:

In this case we had a simple intranet portal in place. By creating a page with an iFrame region, we were able to embed the report. You can opt to either show the Report control (which would give you the little spin-y green circle) or have the report appear as an HTML output by changing the flags on the URL

Scenario #2: Use a master report and subreports to create the dashboard. In this scenario we had several user-selectable parameters. The “outer” report was simply a shell to collect the parameters and provide layout positions of the sub-reports. The sub-reports were a collection of small tables, graphs, etc.

Rob Fisch created a nice tutorial that describes many of the techniques I used. I don’t have access to examples I worked on since these were for previous consulting engagements. I am working on one right now, but it’s not available publicly.

One last tip: even before stoplight indicators were integrated in Reporting Services, it was possible to have red/green/yellow indicators. Simply use the switch function to substitute which icon file to display in your report. So you can have effective dashboarding (with KPI indicators, going all the way back to the first version of SSRS)

In my experience it’s far better to get early and simple dashboards off the ground using the technology at hand than to wait until the “next big thing” in software comes along. Feature-rich software is great if you need and use the features, but the dialog with the business stakeholders needs to take place to determine which features are needed. The best way to start that dialogue is with working examples of dashboards and KPI’s.

Posted in Business Intelligence, Reporting Services, SQL Server | Leave a comment

Microsoft SQL Server 2008 R2 Update for Developers Training Course “Dependency Check Exception” “AuthorizationManager check failed”

Like many of us, it’s a constant struggle, er, — opportunity — for me to keep up-to-date on the latest and greatest techniques. One of the things that Microsoft and its partner ecosystem do particularly well is make plenty of materials available for self-directed study and skills renewal.

If you haven’t already, check out the Microsoft SQL Server 2008 R2 Update for Developers Training Course and corresponding description, This kit was written by friends and colleagues Stacia Misner of DataInspirations and Greg Low and Peter Myers of Solid Quality Mentors, as well as Roger Doherty and Mariano Converti.

I’ve actually had a bit of trouble using this resource, based on not having all the requirements present.

One last error that always tripped me up was this one:

It turns out the error has nothing to do with prerequisites but rather to do with whether the file has been unblocked (if it’s been downloaded from the internet). Described in David Yack’s blog .

Back in business (intelligence)!

Posted in Business Intelligence, Microsoft | Leave a comment

Is BI ready for Cloud Computing? Is Cloud Computing ready for BI?

The discussion today at the ITA BI Roundtable was “Is BI ready for Cloud Computing?  Is Cloud Computing ready for BI?”

It was a lively discussion, with participants from both BI Backgrounds and Cloud Backgrounds.

We used this following materials to support the discussions:

We did look at research other than Gartner, those links are in the PowerPoint PDF

Posted in Business Intelligence, Community, Modeling/Architecture, Virtualization | Leave a comment

MDX Query to show number of child members in a Dimension

I was validating some assumptions about my dimensions’ data distribution and hierarchies and wrote some MDX to confirm the number of members. You could also do this by writing a SQL query against the relational table, but in this case I definitely wanted to see exactly what was coming from the OLAP dimensions.

Here’s the same query morphed to work with Adventure Works:

  MEMBER [mbrcnt] AS [Date].[Calendar].Children.Count
  MEMBER [mbrcnt2] AS Descendants([Date].[Calendar].CurrentMember, 2).Count
  {[mbrcnt],[mbrcnt2]} ON 0
  , FILTER([Date].[Calendar].Members, [mbrcnt] > 0) ON 1
  [Adventure Works]

This only returns rows for members that have more than 1 child member.

Posted in Analysis Services, Business Intelligence, MDX, SQL Server | Leave a comment

Business Intelligence Roundtable: Enrich your analytics with Open Data

Tuesdays topic was Business Intelligence Roundtable: Enrich your analytics with Open Data.

Slide deck is posted here.

Posted in Business Intelligence, Community, Uncategorized, Web | Leave a comment

Solution to “Very strange results from MDX Query”

The solution to “Very strange results from MDX Query

It turns out that there was both a psychological component and a technical one. The sleuth who help me discover these issues was none other than my friend, former and occasionally current colleague, Scot Reagin.

  • The technical issue: I was using FORMAT_STRING to format the numbers I was using as my test case (primarily for presentation convenience). It turns out that this formatting had enough of an impact to present rounding errors.
  • The psychological issue: It turns out that the rounding errors affected the numbers that I was using to test my results but not the results themselves. In other words, I assumed that MonthProjection should match (MTD / PCComplete) after I exported these figures to a spreadsheet and performed equivalent calculations. However, since I introduced rounding errors in my test figures, the numbers I was using to test for a correct result were in fact incorrect. The MonthProjection figure in my original post was in actual fact, correct. I was trying to get a correct answer to match an incorrect test case, based on the assumption that the test case was perfect.

I assumed my testing code was perfect. << sigh >>

I still think that this method of forecasting the month is very slick. In fact today at the client I complimented the analyst that came up with it.

The good news – today I presented a prototype of this forecasting technique against production sales data. There are actually more refinements developed, and more to come, so the end result is pretty cool.

I am really fortunate to be able to work with smart and innovative people.

Posted in Analysis Services, Business Intelligence, MDX, OLAP | Leave a comment

Strange results from MDX Query

I am currently working on adding forecasting capabilities to an existing customer cube. The MDX to do this is based on calculations in an existing spreadsheet model. The spreadsheet works very well in terms of modeling the business accurately, but it’s a manually intensive process and does not recalculate automatically as needed. Perfect candidate for implementation as part of an Analysis Services cube.

This query contains the following calculations:

  • Sales_0YP – Sales for the current period
  • Sales_1YP – Sales for 1 year prior
  • Sales_2YP – Sales for 2 years prior
  • Sales_3YP – Sales for 3 years prior
  • Sales_4YP – Sales for 4 years prior
  • PCComplete – (per cent complete) Within a month, how complete are we for this day. Calculated by dividing MTD sales by total for the month. Last day of month gets us to 100%
  • PCofMonth – (per cent of month) Within a month, what does this day represent for the month’s Sales activity. Calculated by dividing Current day’s sales by total for the month.
  • MonthProjection – Calculated by dividing MTD by PCComplete. Pretty slick (I did not come up with the technique – it was a very smart business analyst I am working with, so I can say that).
  • WhatPeriod – For debugging, I have a calculated member that shows me which period is in scope
  • ShouldBe – an empty string to create a place for calcs when I paste this into Excel for testing

The cube already contains an MTD calculation, which hangs off of a utility (calculation) dimension known as [Time Scope].

The script for the database is here, and the query below (with and without SOLVE_ORDER) are zipped here.

Here’s the whole query, morphed to run against Adventure Works:

MEMBER [Time Scope].[Time Scope].[WhatPeriod] AS (ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Month]).Name)

MEMBER [Sales_0YP] AS ([Measures].[Internet Sales Amount]),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Sales_1YP] AS ([Internet Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year],1)),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Sales_2YP] AS ([Internet Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year],2)),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Sales_3YP] AS ([Internet Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year],3)),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Time Scope].[Time Scope].[PCComplete] AS
([Time Scope].[Time Scope].[MTD]) /
([Time Scope].[Time Scope].[Current], ANCESTOR([Date].[Fiscal], [Date].[Fiscal].[Month]))

MEMBER [Time Scope].[Time Scope].[PCofMonth] AS
([Time Scope].[Time Scope].[Current]) /
([Time Scope].[Time Scope].[Current], ANCESTOR([Date].[Fiscal], [Date].[Fiscal].[Month]))

MEMBER [Time Scope].[Time Scope].[MonthProjection] AS
([Time Scope].[Time Scope].[MTD]) /
([Time Scope].[Time Scope].[PCComplete])
, FORMAT_STRING = "#,##0.00;-#,##0.00"
MEMBER [Time Scope].[Time Scope].[ShouldBe] AS ""
{[Sales_0YP], [Sales_1YP], [Sales_2YP], [Sales_3YP]} ON 0
,{DESCENDANTS([Date].[Fiscal].[Fiscal Year].&[2005], [Date].[Fiscal].[Date] )}
[Time Scope].[Time Scope].[Current]
,[Time Scope].[Time Scope].[Previous]
,[Time Scope].[Time Scope].[WhatPeriod]
,[Time Scope].[Time Scope].[MTD]
,[Time Scope].[Time Scope].[PCComplete]
,[Time Scope].[Time Scope].[PCofMonth]
,[Time Scope].[Time Scope].[MonthProjection]
,[Time Scope].[Time Scope].[ShouldBe]
} ON 1
[Adventure Works]

Here’s the strange result:

  • All of the component calcualtions resolve correctly, but the PCComplete always reflects the calculation for the last day of the month, regardless of which day is in scope

Forecast Seed Query

If you’re reaching this via my twitter post, feel free to leave a comment (140 chars might be a bit too short for an explanation).

Posted in Analysis Services, MDX, OLAP | 1 Comment

Aug 17th, 2010 – ITA BI Roundtable Materials

On Tuesday, August 17, 2010 I had the privilege of presenting at the Business Intelligence Roundtable. The topic was Incorporating Geospatial Data in your Analytics.
This session was a general purpose overview on all how to incorporate mapping data into your analytic systems. In other words, you’ve got analytics in place, but no geospatial data or components – how do you get started?
Most of the presentation was live demo.
Posted in Business Intelligence | Leave a comment

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

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 EvidenceVisual Explanations: Images and Quantities, Evidence and NarrativeEnvisioning InformationThe 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 11×17 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. (Dr. Tufte did this with a 1570 edition of Euclid’s Geometry and a 1610 edition of Galileo’s sunspot observations)
  • 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 7 (or so) 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)
Posted in Business Intelligence | 1 Comment