Join me for a Webinar: “Ad-hoc Scenario Analysis Using Existing Infrastructure”

Tom Huguelet Please join me Tuesday for “Ad-hoc Scenario Analysis Using Existing Infrastructure” April 19, 2011 12:00 p.m. ET / 9:00 a.m. PT http://bit.ly/gpyXpr

Hosted by SQL Server Magazine, sponsored by Tableau Software.

I really had fun creating this webinar. Gave me an opportunity to organize and clarify a lot of previous experience from Business Intelligence implementations I have done.

Hope you join me!

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

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:

WITH
  MEMBER [mbrcnt] AS [Date].[Calendar].Children.Count
  MEMBER [mbrcnt2] AS Descendants([Date].[Calendar].CurrentMember, 2).Count
SELECT
  {[mbrcnt],[mbrcnt2]} ON 0
  , FILTER([Date].[Calendar].Members, [mbrcnt] > 0) ON 1
FROM
  [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:

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

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