Solution to “Very strange results from MDX Query”

Posted on November 4, 2010 by Tom Huguelet

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

Posted on October 25, 2010 by Tom Huguelet

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:

WITH
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]))
, FORMAT_STRING = "Percent", SOLVE_ORDER = 2

MEMBER [Time Scope].[Time Scope].[PCofMonth] AS
([Time Scope].[Time Scope].[Current]) /
([Time Scope].[Time Scope].[Current], ANCESTOR([Date].[Fiscal], [Date].[Fiscal].[Month]))
, FORMAT_STRING = "Percent", SOLVE_ORDER = 2

MEMBER [Time Scope].[Time Scope].[MonthProjection] AS
([Time Scope].[Time Scope].[MTD]) /
([Time Scope].[Time Scope].[PCComplete])
, SOLVE_ORDER = 13
, FORMAT_STRING = "#,##0.00;-#,##0.00"
MEMBER [Time Scope].[Time Scope].[ShouldBe] AS ""
SELECT
{[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
FROM
[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

Posted on August 21, 2010 by Tom Huguelet
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

Posted on July 23, 2010 by Tom Huguelet

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

Interesting social media experiment: Fast Company, the influence project

Posted on July 22, 2010 by Tom Huguelet

Interesting social media experiment: Fast Company, the influence project:

Posted in Uncategorized | Leave a comment

June 15th, 2010 – ITA Roundtable slides

Posted on June 28, 2010 by Tom Huguelet

Slides are linked here.

Topic was “late-breaking news from the MS BI Conference”

Posted in Uncategorized | Leave a comment

Microsoft Business Intelligence Conference Recap – Day 1

Posted on June 14, 2010 by Tom Huguelet

Well, the grandiose plan was to blog each session as they happened, but between keeping my existing clients happy and keeping up with all the events of TechEd/MSBIC, that did not happen.

I was really on the fence about attending the Microsoft Business Intelligence Conference, based in part on existing client demand, but largely on the fact that there have been conferences that I attended where I didn’t learn very much and felt it was a waste of time. This generally was due to the fact that I had been using a particular product in beta as part of a TAP program or was somehow pretty plugged in to the goings-on, and the conference didn’t raise the bar very much. I definitely did NOT feel that way about the recent MS BI Conference in New Orleans; I was very glad I chose to attend.

Here’s a chronological recap of my experiences, kept as brief as possible. If available, I’ll include links to the presentations I attended.

Day 1

06 June 2010 – 23:30 – Touch down at MSY airport

07 June 2010 – 01:00 – Arrive at Hotel, which turned out to be nowhere near any of the events. Expedia told me 2.5 blocks. Turned out to be a mile away. It was a really nice converted mid-1800′s house. Quiet, with a good bed.

07 June 2010 – 07:45 – Registration. Went smoothly. Glad the lines for BI were shorter than for TechEd.

07 June 2010 – 8:30 – coffee at the excellent LaDivina Gelateria stand in the convention center.  I could not believe people were passing this place up to wait in line at Starbucks. This became a morning ritual.

07 June 2010 – 9:00 – Keynote, which I blogged already here. Short assessment: A reasonably effective, but uninspired (and uninspiring) keynote. All head and no heart. I did find myself engaged by the section in which Ariel Netz demoed the BI technologies. I was especially pleased by the use of the term “wickedly fast” to describe PowerPivot.

07 June 2010 – 11:00 – There were no sessions for BI folks to attend, even though there were for other tracks. Upon review of the schedule, there were some “foundational” sessions, but these were not publicized or communicated very clearly.

07 June 2010 – 12:00 – Lunch.

07 June 2010 – 1:00 – 2:15 – Attempting to attend the Interactive Session “BIE04-INT Building Custom Extensions the the PowerPivot Management Dashboard” with Dave Wickert. Here’s where the trouble started:

I was told the session was full. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 2nd choice:

“BIO101 – Business Intelligence Overview: Decisions, Decisions, Decisions”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 3nd choice:

“BIE201 – Configuring PerformancePoint Services in Microsoft SharePoint Server 2010″. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 4th choice:

“BIU202 – Business User Productivity via Report Part Galleries in SQL Server Reporting Services”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 5th choice”

“BIP03-INT – Creating an EPM Environment Using Microsoft SQL Server 2008 R2 and SharePoint 2010″. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in. OK, I could understand how this might be a popular session. OK, I’ll just go to my 6th choice”

“BIC205 – How Partners Healthcare, a Worldwide Leader in Radiology Medicine, Uses Microsoft BI to Improve Patient Quality”. I was told this session was full, too. The red-shirted people wranglers were not letting anyone in.

So, here I am, after planes, trains, and automobiles, a several thousand dollar outlay, standing in the hallway of the New Orleans Morial Convention Center, not learning about Business Intelligence. Clearly not acceptable. The capacity planning for the BI sessions was clearly not effective. While the event planners did take some measures to address the issue, this remained an issue throughout the rest of the conference.

In the end, I was able to get in to the last 25 minutes of Dave Wickert’s presentation, in which he demonstrated extending the PowerPivot Management dashboards, using PowerPivot. The business case was to add a priority category to data sources. So in effect, he demoed using PowerPivot to manage PowerPivot. Very cool. He predicted that there would be a community-created set of extensions as we have seen with various artifacts on CodePlex like the Slowly Changing Dimension component or the SSRS Management reporting.

The interactive sessions do not appear to be posted. Dave’s blog site is here, the MS whitepaper describing the technique is here

07 June 2010 – 2:45 – 4:00 “BIE203 – Introducing Microsoft SQL Server 2008 R2 Master Data Services” The product seemed like  it’s not ready for prime time, and the presenter seemed like he was not ready for the session. I walked out.

07 June 2010 – 4:30 – 5:45 – “BIP301 – So Many BI Tools, So Little Time” This session was awesome. Dan Bulos laided out all the different options for implementation available with the MS BI toolset. It was clear from his presentation that Dan had deep experience and had given a lot of thought to what works well where. Definitely in my Top 5 sessions of MSBIC. I definitely recommend watching the video; session linked here:

07 June 2010 – 5:45 - 9:00 Partner Expo – I enjoyed browsing the booths, and have been to enough of these that I don’t play the “gimme swag” game very much. It seems like the mood was upbeat and commerce, or at least the potential for commerce, was in full swing.

07 June 2010 – 9:00 – ?? Take care of emails, take care of the  ”day job”, then collapse. No night-time socializing today – the late-night flight & arrival took its toll.

Posted in Uncategorized | Leave a comment

#MSBIC Keynote

Posted on June 8, 2010 by Tom Huguelet

Ted KummertMS Uses BI to ship, track defects.

Recollection of BI Conference 2008 – the fairy tale.

Managed self-service business intelligence.

State of BI today

20% have BI.

There are many things that end users should be able to solve on their own. e.g. Add some data to a report.

BI practitioners can then focus on strategic development and implementations.

  • Familiar tools
  • Collaboration
  • Managed

Scenario #1: HR Professional, doing salary analysis, a mixture of internal data and data from the wild. IT is too busy, so it’s built in Excel.

Solution: PowerPivot, powerful in-memory columnar database. Can also render in SharePoint without additional download. Sharepoint is both point of collaboration and management

Scenario #2: Self-service reporting

Solution: Report Builder 3.0, Grab and go Reporting – component library.

Q: “Hey Microsoft, how do I get BI?” A:  Office, SharePoint, & SQL Server. This should turn bi into Utility, like electricity.

Micheal Tejedor

  • Demo of SharePoint
  • Demo of Search
  • Demo of SilverLight report catalog viewer.

“PowerPivot extends Excel with an in-memory database add-in”

DAX – similar to Excel expression language

Now showing SharePoint workflow

Now showing the management workbooks that track system usage activity. Example, a report that has grown in frequency can be moved into DW.

Ayad shammout

Case study – Healthcare Harvard University’s Teaching Hospital – 4 hospitals
PowerPivot removes the lines between users and IT
Medical data needs to be protected. Used SQL2008 audit feature
44 million rows in excel

“Dashboard” built in about 6 hours. Also uses mapping. Uses power pivot data as a source. Uses mapping control and report builder 3.0

Analysis: readmission – find out who should was released too early. 2 days manual work, new Powerpivot report takes about 4 hours, 30 gig compresses down to 5 Meg

Kummert: manual process convert to efficient automation

Donald Farmer

alpha geek challenge

Competition
Entry 1-employee morale Dan coming ore
Entry 2-flights
Entry 3-personality characteristics
Great visualization
Entry 4-(joke) applause by

A look into the future
“not announcing features or schedule”
The cloud (say it: cloud, cloud, cloud)
Where are you headed in terms of providing BI in the cloud?
Intent is to offer all the capabilities of SQL in the cloud, including reporting.
Commoditization of IT into the cloud. Search, social media.
Mapping
Compliance- data quality. Problem: what is the key reference data
Data cleansing- machine learning
Lineage, impact analysis. Requires thinking deeply about a metadata strategy
Increasing data volumes. More transactions, more detail from those transactions
Parallel data warehouses coming soon
Working with hardware vendors on reference implementations
Codename Dallas. Reference content.

Amir Netz

Alpha code demo:
How were unifying the models
New data visualization & powerpivot
EPG Waterfall analysis – redacted data
Professional BI
KPI’s in PowerPivot. They’ve been in the engine, but not exposed via UI
@amirnetz just demoed PowerPivot based KPI’s at #msbic keynote. They look slick, better than performancepoint

Way cool! PowerPivot Calculation impact analysis tracing diagram demoed by @amirnetz at #msbic http://tweetphoto.com/26228397

2 Billion rows in Excel. sub-second response.

Wow. Awesome keynote. More later…

Recording is now available

Posted in Uncategorized | Leave a comment

TechEd Keynote – brief comments & impressions

Posted on June 7, 2010 by Tom Huguelet

I am keeping these brief on purpose – just my impressions in 5 sentences or less (I hope).

Bob Muglia

Cloud

Apparently there is this thing called the cloud . . . Say it:  Cloud, cloud, cloud, cloud. In the cloud, magic elves create software that works automagically. Oooo … Cloud, cloud, cloud, cloud.

Seriously though, this is a big emphasis for MS. My sense is that both the technology and implementations are early and still somewhat immature.

The Chicago Tribune case study piqued my interest (coming from Chicago), but didn’t provide huge details.

Testing

Demo of an automated testing app that pinpoints error, captures screenshots, and allows you to step backwards. Cool demo. I wonder what softwares need to be in place for this to work,

Virtualization

It’s hard to demo virtualization, since it’s all, well, virtual.

Application management

The main concept I came away with is the idea of saving state, provisioning updates and dropping the preserved application state on top of the patched virtual OS.

Danger phrase during this demo: “It’s just a check-box”
Translation of key phrase: “Available for download” ≠ “released”

Office Communication server

720 videoconferencing using commodity hardware
Cool demo, but demo gremlins messed up the effect. The background floor plan took forever to load, so the collaborative white-boarding took place on an empty white rectangle.

Windows Phone 7

Way too many animations in Windows Phone 7 OS.
Cool auto-suggest feature
Sync w/ Sharepoint demo was cool.

IE 9 -Mentioned, not demoed

Windows intune – New security suite? Mentioned, not demoed

BI in the cloud ?

(I think that’s what Mr. Muglia said, but the demo really didn’t have anything to do with the cloud)
Great demo by Amir Netz, who called PowerPivot’s sorting 100 Million rows “wickedly fast”
Code name Dallas
Odata format to integrate data from the cloud (ahh, there’s where cloud come in. Say it: cloud, cloud, cloud)
Demo of uploading to SharePoint
Demo of reporting on this with SSRS
PivotViewer – very cool demo of stop-light color-coded thumbnails in a silverlight based power-pivot like display.
Also demoed Mapping linked with geo-tagged photos

Avatar – wow ms software used to create Avatar?

Gaia asset tracking application – Used SQL, tracked production assets.

Mr. Muglia’s closing thought

Cloud creates opportunities and responsibilities

Edit: Link to the actual keynote is here

Posted in Uncategorized | 1 Comment

Inexpensive iPad holder – less than $5

Posted on May 28, 2010 by Tom Huguelet

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

Posted in Hardware | Tagged | Leave a comment