Nick Barclay: BI-Lingual

PerformancePoint Services - What’s Deprecated

nick.barclay@gmail.com (Nick Barclay) — Thu, 05 Nov 2009 04:47:00 PST

Continuing on from the What’s New and What’s Changed / Improved / Different posts, here are some of the things that will be going away in PerformancePoint Services.

OWC Support

No more OWC-based PivotCharts, PivotTables, Trend Charts and Excel Spreadsheets. Good.

ASP.NET Dashboard Preview Site

One of my favorite features of PPS 2007 is now gone. Because the storage and management of elements are now almost entirely MOSS based you will need a complete installation of MOSS 2010 to be able to play with the new stuff. The silver lining here is that MOSS 2010 will be supported in a Developer configuration on Vista and Win7 PCs. So now developers will be able run their own sandbox environment locally.

Support for SSAS 2000 databases

Anyone who still has an SSAS 2000 DB running in production ought to be ashamed of themselves.

ODBC Tabular Data Sources

ODBC data sources were a rarely used feature of PPS 2007, and BSM for that matter. Although you could connect to just about any data source you wanted to, you could only bring return a scalar value per data source element which made these data source types tedious and of little real value.

32 bit Architecture

Because PPS is now part of the MOSS 2010 furniture it goes without saying that it only supported on 64 bit platforms. Hello better scalability.

PerformancePoint Services - What’s Changed / Improved / Different

nick.barclay@gmail.com (Nick Barclay) — Thu, 05 Nov 2009 04:46:00 PST

Continuing on from the What’s New post, here are some of the changes and improvements to the product. This post covers some of the more subtle changes and improvements to the product. You could argue that some of these belong in the “what’s new” post, but let’s not split hairs here.

Again this is by no means an exhaustive list, but I think I’ve got most of major ones in here. As before I’d love to include screenshots but cannot because they’re from the Beta 1 build and MS have asked me not to include these. Beta 2 should hopefully be out some time this month.

Analytic Charts & Grids

The overall improvements to the Analytic report engine both from a designer and end user perspective continues to get better. Here are some of the major improvements.

Improved chrome - Charts now look shinier. Like pie charts this is something that many users will appreciate but doesn’t really add any significant analytical value. Nonetheless pretty things tend to impress some users.

Select measures - Users can add or remove individual measures from chart or grid using a set of checkboxes.

Interactive chart labels - In PPS 2007 the graphical elements themselves i.e. the bars or lines within the graphs were the interactive parts. The labels on the X and Y axes of graphs can now also be right-clicked to expose interactive functionality too.

Filtering - Top / Bottom N filtering capabilities are available for both end users and developers.

SSAS cell formatting surfaced - Cube-based cell formats will be brought through and displayed in analytic grids. [Big round of applause]

Per measure formatting – The format of individual members can be altered in the designer. Nice feature but because the analytic reports are pulling data from a cube the formats should be correctly applied therein to begin with.

No more design time browse button - In order to test the interactivity of a particular analytic report there is no need to launch a separate window via the Browse button. Designers can interact with the charts and grids directly.

Better cube object browser experience - The cube metadata in the Details Browser is properly organized in the way we are used to within SSMS, Excel and others. Dimension attributes, attribute hierarchies and folders are grouped within their parent dimension containers. You can also filter the content of the pane for a specific measure group.

Scorecards

In general there are several new interactivity & layout capabilities that have made their way into scorecards. I haven’t had much of a chance to explore the changes to this element fully. Suffice it to say that there will be more opportunities for people to try and make scorecards into reports by treating the scorecard element as a pivot table and then being disappointed when it doesn’t deliver the exact functionality they expect. Hopefully the new scorecard power will be used with appropriate responsibility.

Dynamic dimensional axes – scorecards support user interactivity with dimension hierarchies. In PPS 2007 you had to add individual members or sets and craft the axis hierarchy by hand. Now you can add, say, the All member of the Product Categories hierarchy and you will have full interactive access to all its descendants within the scorecard.

New target metric display settings - have a new set of dialogs with several new options including the ability to calculate and display the variance between the target and its associated actual. You can also configure the variance calculation to show either a Percentage of Variance or just a number. Within the Percentage of variance there is an option to show either a Difference from value or Progress toward value.

Data Sources

Security per data source element - This is a really good one. Security context can now be configured on each individual data source element. In PPS 2007 data source security was a server level setting and was a source of many questions in the PPS forum. In the latest version you can choose what security scenario you’d like to apply to each data source definition within Dashboard Designer. The two primary methods being the use of the Unattended Service Account or Per-User Security. The only difference between the SSAS and Tabular data source configuration options respect is the SSAS data sources offer the ability to make use the CustomData connection string property as well.

KPIs

Multiple actuals per KPI – You can now create more than one Actual within an individual KPI. This one sounds trivial but it’s not, it opens up the door to much richer KPIs. BSM and PPS 2007 supported exactly one actual metric per KPI. Because you can now have more than one actual the designer provides the ability to link each target metric to the appropriate actual metric.

Metric cloning - When configuring the data source of a specific metric you can point towards another metric and clone its settings. So you’ve already created a metric that has a data source, calculation or setting that you want to clone you can point the data source property of your new metric to the existing metric. Under the covers Dashboard Designer copies the settings of the target metric into your new metric. A nice time saver. Note however that this is a one time clone of settings, the element definitions will not remain synced thereafter.

Calculated metrics – When configuring the data source property of a metric there are some new dialogs to help configure calculations. Using them you can create calculations that reference other KPI metrics. Some pre-built calculation will be available such as Growth as Percentage, all you need to do is fill in the formula to reference the appropriate metrics. The PPS team posted some details on this new feature the other day. This may come in handy for KPIs that source metric data from different places. On the other hand, if your KPI sources data from a single cube these kind of calculations should be baked into the cube itself whenever possible.

Time Intelligence Formulas

Two new functions have been added to the STPS. They are the <PeriodName>ToDate and the Full<PeriodName> functions. They have been created to enable better period-to-date functionality. But couldn’t we already to that kind of stuff with the original PPS TI formulas? Sort of. If you wanted to do a YTD using PPS 2007’s TI functionality you would define a range of values with an STPS formula. This would return a set of appropriate period members. For example Year.FirstMonth : Year.Month would return a set of month members from Jan-Nov for the current year. This was great if you wanted to apply the set of members across a scorecard or report axis but it did not help if you wanted to do something like provide a YTD column on a Scorecard using TI. The new functions fill that need. When used together the …ToDate and Full… functions return an aggregation object of sorts as opposed to a set of members. For example the STPS formula YearToDate.FullMonth returns a TI aggregation object for the year-to-date value up to the last full month. QuarterToDate.FullDaty returns an aggregation object for the current quarter up to the last full day.

PerformancePoint Services - What’s New

nick.barclay@gmail.com (Nick Barclay) — Thu, 05 Nov 2009 04:46:00 PST

Now that the NDA has been lifted it’s time to start talking about PerformancePoint Services. I plan to expand on many of these points in upcoming posts. For now I’ve put together a series of summary posts on some of the things that are

There is plenty to talk about in the new release, these lists are by no means comprehensive in their coverage of PerformancePoint Services. I also expect that the PPS team has a whole stack of useful posts loaded and ready to go, so keep and eye on their blog for a lot of new information in the near future.

UPDATE: Here is a detailed post from the PPS team (including screenshots) about all the new stuff

I wish I could provide screenshots of all that is contained in these posts but I am unable to post shots of Beta 1. More visuals will come as Beta 2 is released.

SharePoint Integration

OK, we all know this one, PerformancePoint Services is now part of the Enterprise CAL of MOSS 2010, it is no longer a separate product. Element definitions are stored and managed within SharePoint lists and libraries and will be recognized as first class citizens in the MOSS world. This is going to bring a number of advantages in the MOSS 2010 world and will be the subject of quite a few posts in the future. Lots to talk about here.

Unfortunately, the MOSS integration strategy that was announced in January this year seems to have taken a big slice of the dev team’s time and hence the amount of new and changed features in PPS are not huge in number. That being said I do not want to trivialize the time and effort that went into the MOSS integration of PPS. The benefits of this re-architecture effort will make themselves known as we all begin to explore the new MOSS platform and all the other BI goodness (outside of PPS) that is baked into it.

The 7th Element: Filters

Filters are now an element unto themselves. In PPS 2007 filters were part of dashboard element definitions and therefore could not be shared. In PPS 2010, if you’ve built a useful filter you can share it with all of your dashboards just as you would any of the other elements. This is a welcome architectural change that many were hoping for (not sure whether this really falls under the “new” category…).

The filter types that are available to us and their functionality have not changed very much in this release, they’re just a separate element now. With this new architectural change many will ask whether this means that we can pass values from one filter to another i.e. cascading filters. The answer: no, not yet. I wouldn’t be surprised if this feature was one that got cut in lieu of time required for the MOSS integration work.

Decomposition Tree Drilldown

I’ve always liked the ProClarity’s decomp tree and am really glad to see this new Silverlight-based version as part of drilldown interactivity within analytic reports. The decomp is not a report type unto itself but a “Analyze > Decomposition Tree” option from the right-click menu within the analytic chart and grid reports. Unfortunately the decomp tree is about the only recognizable ProClarity bit that made it into this release.

New Chart Type: Pie Charts (groan)

One of the things I liked about PPS 2007 was the fact that Analytic reports didn’t support pie charts. I think this was a feature that the MS sales team had a hand in. If you, or anyone you know, still believe that pie chart has any real analytical value please refer yourself (or the people you know) to Stephen Few’s excellent Save the Pies for Dessert whitepaper. If after reading it you still think pie charts are worth using on a dashboard then there is no hope for you.

KPI Details Report

As its name suggests this report supplies all the details about a particular KPI. This is a welcome addition. It’s a simple display of KPI metadata that can be hooked to any scorecard. Clicking on a KPI within the related scorecard will display its information in this report. When configuring the report you can choose which pieces of KPI metadata to display.

The lack of this feature in PPS 2007 was one of the driving reasons behind why I built the MAUDF project a while back; to provide simpler access to this kind of data. My customers wanted to see banding thresholds, descriptions and other metadata pertaining to a KPI. The new report does all of this.

Un-cooking the books with Benford’s Law

nick.barclay@gmail.com (Nick Barclay) — Mon, 26 Oct 2009 10:44:00 PDT

So, if you take a set of real life numeric data (e.g. sales figures, customer sat scores, baseball game attendance figures) stripped the first digit off each number and counted those up what would the distribution of numbers be? i.e how many 7s would there be? How many 2s? Would there be a pattern to the distribution? The answer is actually yes. It’s known to many number crunchers as Benford’s Law.

Here's the Wikipedia definition: Benford's law, also called the first-digit law, states that in lists of numbers from many (but not all) real-life sources of data, the leading digit is distributed in a specific, non-uniform way. According to this law, the first digit is 1 almost one third of the time, and larger digits occur as the leading digit with lower and lower frequency, to the point where 9 as a first digit occurs less than one time in twenty.

Basically the law means that in many real-life (i.e. non-made-up or random) data the distribution of the first digit in a series of numbers will often look very similar to the graph below (also from Wikipedia).

My CPA brother-in-law refers to “running the Benfords” when on auditing gigs in order to perform a quick acid test on pertinent sets of numbers to see if there is something that warrants further investigation. If the distribution doesn’t look similar to the graph above then he looks a little closer; maybe someone’s been cooking the books.

As DBAs, BI pros (and maybe some former accountants) we have plenty of real world data at our fingertips. Why not test Mr. Benford out to see if our data conforms. Benford’s law states that the data has to be real-life so let’s test it by applying the theory to three different data sets:

Randomly generated numbers
Made up numbers (AdventureWorksDW2008 sales figures)
Real data from a real life data source

Random Numbers

I’ve created a simple script to create set of randomly generated numbers and perform a basic Benford analysis of the results. Here’s what it does:

Creates and populates a table variable with 65,536 random numbers
Populates CTE with the random numbers and a separate column holding the first digit of each number
Counts the instances of each digit and returns the results in a table including a simple histogram column for numbers 1-9 (excluding any 0 digit values sometimes returned by my simplistic random number algorithm)

USE tempdb;
GO

DECLARE @RandomNumbers TABLE
(
    RandomNumber INT
);

-- Use Itzik Ben Gan's technique to quickly generate 65536 records
WITH
n5(x) AS (SELECT 1 UNION SELECT 0),
n4(x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),
n3(x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),
n2(x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),
n1(x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x)

-- Create 65536 random numbers
INSERT INTO @RandomNumbers
SELECT
    ABS(CHECKSUM(NEWID())) % 100000 AS RandomNumber
FROM n1;

-- CTE containing each random number and its first digit
WITH BenfordTest (FirstDigit, RandomNumber) AS
(
    SELECT
         SUBSTRING(CAST(RandomNumber AS VARCHAR(MAX)), 1,1) AS FirstDigit
        ,RandomNumber
    FROM @RandomNumbers
)

-- Count how many instances of each number there is from 1-9
SELECT
     FirstDigit
    ,COUNT(*) AS InstanceCount
    ,REPLICATE('|', 100. * (CAST(COUNT(*) AS NUMERIC(5,0)) / b2.Total)) AS PctDistribution
FROM BenfordTest b1,
    (SELECT CAST(COUNT(*) AS Numeric(5,0)) Total FROM BenfordTest) b2
WHERE FirstDigit > 0 -- exclude any zero digit records
GROUP BY FirstDigit, b2.Total
ORDER BY FirstDigit ASC;

And here are the results. Because these are random numbers you can see that the PctDistribution column, and the Excel graph I created is pretty much uniformly distributed between about 10.8% and 11.5% for all leading digits.

Made Up Numbers (AdventureWorksDW2008)

Now we all know that AdventureWorks data is not real, but is there any “realness” to it at all? Probably not. We’ll test against the SalesAmount column in the FactInternetSales table.

BTW, analysis like this is a great excuse to use the TABLESAMPLE clause in order to avoid querying the entire table.

USE AdventureWorksDW2008;
GO

WITH BenfordTest (FirstDigit) AS
(
    SELECT
         SUBSTRING(CAST(SalesAmount AS VARCHAR(MAX)), 1,1) AS FirstDigit
    FROM FactInternetSales
    TABLESAMPLE (20 PERCENT)
)

SELECT
     FirstDigit
    ,COUNT(*) AS InstanceCount
    ,REPLICATE('|', 100. * (CAST(COUNT(*) AS NUMERIC(5,0)) / b2.Total)) AS PctDistribution
FROM BenfordTest b1,
    (SELECT CAST(COUNT(*) AS Numeric(5,0)) Total FROM BenfordTest) b2
GROUP BY FirstDigit, b2.Total
ORDER BY FirstDigit ASC;

OK, I guess that proves just how real AdventureWorks data really is. This provides some pretty good evidence that AW numbers were not generated randomly and were almost certainly not based on real sales figures. On the other hand maybe the regional cycling gear reps are fudging the numbers a bit….

Real Numbers

Here are results of a query I ran on some real data I have available to me. Trust me, it is real data but, naturally, I can’t share it. The distribution looks about right, too. Kinda cool, eh?

You can try the sample code out yourself on some data that you have access to.

Here’s the code stub:

WITH BenfordTest (FirstDigit) AS
(
    SELECT
         SUBSTRING(CAST(<NumberFieldName> AS VARCHAR(MAX)), 1,1) AS FirstDigit
    FROM <SourceTableName>
    TABLESAMPLE (20 PERCENT)
)

SELECT
     FirstDigit
    ,COUNT(*) AS InstanceCount
    ,REPLICATE('|', 100. * (CAST(COUNT(*) AS NUMERIC(5,0)) / b2.Total)) AS PctDistribution
FROM BenfordTest b1,
    (SELECT CAST(COUNT(*) AS Numeric(5,0)) Total FROM BenfordTest) b2
GROUP BY FirstDigit, b2.Total
ORDER BY FirstDigit ASC

The complete code from this post can be downloaded here.

Conditionally Hiding Axes for Trellis Displays

nick.barclay@gmail.com (Nick Barclay) — Wed, 21 Oct 2009 10:37:00 PDT

Over the last year MVP Tim Kent has put out a series of really useful posts showing how various data visualizations can be created using SSRS.

The latest post on Trellis displays got me thinking on how I could tweak a few of the settings in Tim’s very useful sample report just a bit more.

In order to show more sample data, I changed the top axis of Tim’s sample report to show sales Bike subcategories because as we all know AdventureWorks sells waaaay more bikes than anything else. Below is a shot of the original report after that change.

I made a few more tweaks and changes and came up with the report below

The main ink-saving tip is to conditionally hide / show labels on the X and Y axes based on the items at the top left and bottom left of the trellis. In this case it is Road Bikes and Northeast. All that is needed here is a small amount of extra MDX to ORDER and RANK members in both the Region and Subcategory sets to provide the right meta data required to perform the conditional hide / show. Here is the MDX for the report showing the ordering and ranking of the appropriate sets.

WITH
SET [SalesOrderedSubcategories] AS
    Order
    (
      [Product].[Product Categories].[Category].[Bikes].Children
     ,[Measures].[Sales Amount]
     ,BDESC
    )
SET [SalesOrderedRegions] AS
    Order
    (
      [Sales Territory].[Sales Territory].[Region].MEMBERS
     ,[Measures].[Sales Amount]
     ,BDESC
    )
MEMBER [Measures].[SubcategoryRank] AS
    Rank
    (
      [Product].[Product Categories].CurrentMember
     ,[SalesOrderedSubcategories]
    )
MEMBER [Measures].[RegionRank] AS
    Rank
    (
      [Sales Territory].[Sales Territory].CurrentMember
     ,[SalesOrderedRegions]
    )
SELECT
{
    [Measures].[Sales Amount]
   ,[Measures].[SubcategoryRank]
   ,[Measures].[RegionRank]
} ON COLUMNS
,(
    [SalesOrderedSubcategories]
   ,[SalesOrderedRegions]
   ,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
) ON ROWS
FROM [Adventure Works];

Set the SORT property of each group to its respective GroupNameRank calculated member, this way we can be sure that in our example the top ranked Subcategory (Road Bikes) will be the left-most item and the lowest ranked Region (Southwest) will be the bottom-most item in the trellis.

The real trick here is to use an expression to conditionally hide / show the axis labels so that we only see the X-axis labels at the bottom of the trellis and Y-axis labels on the left side of the trellis.

Y-axis uses an expression that only shows the axis for the MIN ranked member for Subcategory

=IIF(
    Fields!SubcategoryRank.Value = MIN(Fields!SubcategoryRank.Value, "Trellis")
    , false
    , true
)

and X-axis uses the MAX ranked member for Region

=IIF(
    Fields!RegionRank.Value = MAX(Fields!RegionRank.Value, "Trellis")
    , false
    , true
)

It also helps to check the Hide first and last labels along this axis for the Y-axis as the zero value tends the throw off the alignment with the other charts where the X-axis is hidden.

The sample .rdl file can be downloaded here.

Windows Problem Steps Recorder

nick.barclay@gmail.com (Nick Barclay) — Fri, 16 Oct 2009 09:35:00 PDT

Been meaning to write about this one for some time. This year’s TechEd USA keynote was centered around Windows Server 2008 R2. The speakers demoed plenty of good features but the one that stood out (and easily got the most spontaneous applause) was the Problem Steps Recorder. It does exactly as its name suggests: it records problem steps.

The PSR is a really simple app that is baked into both Windows Server 2008 R2 and Windows 7. It’s actually not that easy to find unless you know what you’re looking for – the Start menu doesn’t even list the app by its name.

The UI couldn’t be simpler as seen below.

The user opens the app, presses Start Record, performs the steps that reproduce a problem they’re experiencing (including their own comments if needed), then presses Stop Record. PSR then asks where they want to save the results. The output produced is a zip archive containing a single MHT file. The user then emails the zip archive to the helpdesk or whoever is trying to help them.

The generated MHT file is a navigable, screenshot-by-screenshot, annotated document of each step the user performed while recording. Below is an example step explanation with screenshot.

Here is a text summary of each step without screenshots that is found at the bottom of each MHT file.

OK, so why does PSR have to be just a helpdesk tool? Like many others in the audience I thought of this as a potential quick & dirty documentation tool. Admittedly out of the box the text produced describes every step as a “Problem” but once you look past that this could be a great little time saver in a number of situations.

Here’s an example output file that walks through creating an SSRS project and adding a new report in BIDS.

Data Dude Error TSD03006 – Explicit Database Reference

nick.barclay@gmail.com (Nick Barclay) — Fri, 16 Oct 2009 07:07:00 PDT

Problem

You’ve just created a new Data Dude project and imported your DB schema, or you’ve just synchronized schemas with an existing project having added several new views / UDFs / SPROCs etc. When you try to build your project you find that there are tons of TSD03006 errors that are stopping you. Why?

TSD03006: View: [dbo].[vFactResellerSales] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[DimProduct].[s]::[ProductKey], [dbo].[FactResellerSales].[ProductKey] or [dbo].[FactResellerSales].[s]::[ProductKey]

Solution

Ensure any explicit database name references do not exist in your code. If you need to explicitly reference a database do it by using variables.

Removing the explicit DB references from the code should make the TDS03006 errors disappear. Admittedly you should be cleaning up any explicit DB references within your code, but when whipping up a new script in the early hours of the morning this may be something that you’d miss. And suddenly getting a whole heap of build-blocking errors at 3am can be incredibly annoying.

Sometimes explicit DB references can creep into code without you catching it. For example when creating views from large tables I often start by scripting out a SELECT statement within SSMS. The code that is generated by SSMS includes an explicit reference to the database.

SELECT [ProductKey]
       ,[OrderDateKey]
       ,[DueDateKey]
       ,[ShipDateKey]
       ,[ResellerKey]
       -- Shortened for brevity
       ,[CustomerPONumber]
FROM   [AdventureWorksDW2008].[dbo].[FactResellerSales]

It’s the explicit [AdventureWorksDW2008] reference that causes the TSD03006 errors. If you add to the SSMS generated code and join other tables but keep even one explicit DB reference you will continue to receive TSD03006 errors for each field in the view definition.

Data Dude is indeed bringing a valid problem to our attention but I think there should be a more elegant way to communicate the situation. One view containing 37 fields with 1 explicit DB reference returns 37 errors. In the end the fix is easy, but figuring out the fix takes more time than it should based on the content and volume of the error messages.

BTW make sure that you’re using Data Dude GDR R2 version 9.1.40413.00. I found an MS support document that detailed a similar issue pertaining to using Server and Database aliases in referenced projects that is fixed in this release. This issue is similar but different in that it’s not a direct problem with aliases or referenced projects, just careless coding on my part.

Cloud-based Tabular Data Sources

nick.barclay@gmail.com (Nick Barclay) — Thu, 15 Oct 2009 08:16:00 PDT

I realize there are many out there who are sick of the term “the cloud”. Larry Ellison's rant on this topic is great.

Nonetheless I got my Azure invitation yesterday and for no other reason other than it’s geeky, tried to access it from PPS. As I’d hoped, it was simple (as was connecting using SSMS). I set up a sample DB in my allotted condensed water vapor storage area and created a tabular data source using the appropriate connection string. Easy!

…and here’s the data in it

Hopefully cloud-based SSAS is not far away.

Windows 7 and PerformancePoint M&A Setup Gotcha

nick.barclay@gmail.com (Nick Barclay) — Thu, 15 Oct 2009 07:32:00 PDT

For those who have, or are about to, upgrade to Windows 7 and reinstall a local instance of PPS for the purposes of demos / experimentation / learning etc. here are a couple of quick tips on getting things up and running.

Before installing the M&A Server be sure to read this post and ensure all your pre-reqs are in place.
Install the latest hotfix (I understand that PPS SP3 coming out in a couple of days)

UPDATE: SP3 is now available so skip step 2 above and install SP3 instead

x86: http://www.microsoft.com/downloads/details.aspx?FamilyID=90c596a5-aca4-4ded-9072-facf834bc0c6&displaylang=en

x64: http://www.microsoft.com/downloads/details.aspx?FamilyID=3ad75ae5-d2cd-4953-87cf-5f74d79804c6&displaylang=en

And now the Win7 twist. Once you have installed, configured the M&A server and fired up Dashboard Designer (DD) you receive the following message when trying to create a data source:

OK, so this is an application pool account problem? Nope. The problem is you’re not an administrator on this Monitoring Server yet. So you go into the administrative section in DD and add your account. When you to the options section and try to administer the server you get this error:

And herein lies the problem. You’re not a monitoring server administrator yet, but how can you make yourself one if you can’t get into the admin screen?

The solution:

You need explicitly run Dashboard Designer as an administrator (“but I AM an administrator!”). By default you’re not running this application as an admin. This is related to the UAC settings in Win7. Sure you can alter (turn off) the UAC settings but you may not want to, or be allowed to, in some cases.

How do you run DD as an admin? The Start menu item that is created for DD is only a link to the ClickOnce launch URL so you won’t find the appropriate “Run as administrator” option on the context menu if you SHIFT + Right-click on it. In order to run DD as an administrator find the DD executable (PSCBuilder.exe) in the file system. On a default install it will be located in %Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\DesignerInstall\3.0

Right-click the executable and “Run as administrator” from there.

In this administrator instance of Dashboard Designer go into the Server Options section and add yourself as an administrator.

You should be good to go from there.

Book Review: Expert Cube Development with Analysis Services 2008

nick.barclay@gmail.com (Nick Barclay) — Sun, 11 Oct 2009 17:00:00 PDT

Full disclosure: the authors of this book provided me with a free review copy of this book.

As the title suggests Expert Cube Development with Microsoft SQL Server 2008 Analysis Services is not a book for SSAS beginners. This book fills a need that has been out there for a while when it comes to Analysis Services publications: a concentrated volume focused on enhancing the knowledge of the experienced SSAS pro. From the outset the authors assume the reader already has experience with the product, cover a few ground rules and get right down to business.

The amount of real world SSAS implementation experience shared between Alberto, Marco & Chris shines through indicating just how much work they’ve collectively done with Analysis Services. Many technical books have advanced sections or whole chapters dedicated to more advanced development techniques and tips. Being pitched as an expert book enables this level of content to pretty much fill the entire publication.

There is a lot of goodness jammed into the book’s 320-ish pages. You can sense that the authors tried hard to fit as many tips, tricks and techniques into each chapter as possible without bloating the text. They do not waste page space explaining the simple stuff because, if you’re reading this book, you should know the simple stuff. Each chapter remains concise and tells you what you need to know and where to go if you want to find out more by means of links to blog posts, white papers and other books as well as downloadable sample code.

My only criticism is a somewhat superficial one and is probably directed more at the book’s editor than its authors. There were no reference numbers and caption text underneath any of the screenshots, tables and figures at all. The non-textual items seemed naked without them and this made the end product seem a little less polished. As a reader I prefer it when the text points specifically to “Figure 1.2” instead of “the screenshot”. On some occasions the text didn’t even make direct reference to the item that appeared on the page with it, the relationship was implied by proximity.

If you have not worked much with SSAS yet then this is not a book you should be starting with. If, however, you’ve been working with the product and want to ensure you’re squeezing every last bit of performance out of your OLAP databases, this is a book you’ll want to read cover to cover. Even the most seasoned SSAS experts will come across material or techniques they did not know of or had forgotten about.

The Microsoft Virtual Catch 64

nick.barclay@gmail.com (Nick Barclay) — Tue, 22 Sep 2009 08:46:00 PDT

How does it go again, Steve? “Developers, developers, developers”? As we move further and further into the 64 bit world, Microsoft’s virtualization path on non-server operating systems will leave some developers out in the cold.

MOSS 2010 is going to be a huge release and will only be available in 64 bit. Naturally PerformancePoint Services and all the other good stuff that’s going to be baked into this next version will be 64 bit too. The pure 64 bit direction is a good one, I am looking forward to the solid baseline scalability and power that will come from not offering the 32 bit option. All of this is good news.

Now here’s the catch for developers looking to get up to speed with the latest and greatest. Virtual PC 2007 and the soon-to-be-released Windows Virtual PC do not support 64 bit guest operating systems. The only Microsoft supported way to run a 64 bit virtual environment is through Hyper-V. The inability to support a virtual 64 bit OS on a Vista / XP / Win7 box means that the usually trivial task of spinning up a VPC and kicking the tires with the beta bits is just not possible. This a pain point that is already shared by many, just take a look at this newsgroup thread.

If you’re a consultant like me your laptop is your life. You do a great deal of your learning, development, testing, demos, conference presentations on a variety of VPC images. You store these images on an external HDD and run them within your primary, non-server OS. If you want to continue doing this kind of thing (on a 64 bit platform) you will need to seek out non-MS virtualization technologies to support your efforts.

So what are your options if you want to retain a pure Microsoft environment? The only MS answer to a 64 bit virtual environment is Hyper-V, and that means running Server 2008. If the cost of licensing Server 2008 is not an issue here are some of your options:

Run Server 2008 as your primary OS
Dual boot your laptop using Server 2008 as a secondary OS
Buy another laptop and load Server 2008 on it

None of these choices really do it for me. I have a good laptop with plenty of RAM and a 64 bit processor that supports virtualization technology. Nonetheless I am unable to run a MS-based 64 bit virtual guest OS without using Server 2008. So in order to continue to do things the way I am used to I have to go with a non-Microsoft virtualization technology that supports 64 bit guests. Here are the two frontrunners in the desktop 64 bit virtualized guest world that I have found.

VirtualBox (free)
VMWare Workstation (around US $189)

I recently reimaged my laptop with the RTM build of Windows 7 and have been using VirtualBox for a few days now. I’m used to the VPC way of doing things but I’ll just have to adapt, I guess. That being said, VirtualBox is a pretty good alternative.

As MOSS RTM draws nearer in H1 2010 I have a feeling we may begin to see virtual demo environments that are not hosted in MS technology. MS is pushing virtualization more and more (and so they should) but how’s it going to look if the person presenting at a user group / conference / customer site is using non-MS virtualization technology to host their demo? The next time I present that may well be the case. Disappointing.

UPDATE (Oct 21 2009): It has just been announced that MOSS 2010 will be supported on both Vista and Win7 for developers (not production deployments, of course). Although this doesn’t completely eliminate the problem that is the subject of this post it very definitely helps. The ability to run your own local developer version of MOSS should be great. I had heard about this possibility of this kind of support under NDA some time ago but it was never confirmed until now.

Anyone else got an opinion, alternative to the situation we will soon find ourselves in? Feel free to comment.

Excel 2010 gets sparklines, finally

nick.barclay@gmail.com (Nick Barclay) — Wed, 15 Jul 2009 10:37:00 PDT

There are a number of different Office 2010 features and functions we’re getting wind of now that the covers have come off at the WPC in New Orleans.

One feature that is of particular interest is sparklines. I would think that anyone who subscribes to this blog will know what a sparkline is and how useful native Excel support for this visualization will be.

If you’re looking some more visual information about this new feature take a look at the following:

There’s a good video on TechNet. The commentary is in German but the demo is pretty self-explanatory. Make sure you view it in full screen as you’ll get a good close-up view of the various display and formatting options available in the new Sparkline Tools ribbon tab.
Scoble has a post containing six great Office 2010 videos including one that demos the creation of sparklines (it’s the last video out of the six). In that same video there’s a demo of the new pivot table slicer functionality. Many would already be aware of slicer feature via the various Gemini demo videos that have been available for the last few months.

UPDATE: Here is the official Sparkline post from the Excel 2010 team blog

If you build it… just don’t call it PerformancePoint

nick.barclay@gmail.com (Nick Barclay) — Thu, 18 Jun 2009 12:47:00 PDT

Here we go. Now things are going to get interesting. Or are they? MS have just announced that they will make the PPS Planning source code available to customers and partners as the FPA (Financial Planning Accelerator). Is this just a good will gesture to quell the angry mob (even though their torches have probably burned out between January and now).

“Oh well, they discontinued the product, but at least they made the source code available.” I wonder how much use the FPA code will really be. Let’s remember, we’re not talking about a small codebase here. This took a reasonable sized Microsoft team a considerable amount of time to develop. A small consulting firm (the ones hardest hit by the sudden retirement of Planning) is just not going to be able to hand this stuff over to a junior developer and simply say “here, see what you can do with this.”

So, will there be a race among larger, resource-awash partners to see who can further develop and repackage the newly available code first? I’m unsure as to whether the no-cost individual license expressly precludes making an actual saleable product out of the code. Or has everyone already moved on? Some partners are probably rolling their own planning tools, others have sought alliances with alternative vendors, some have probably given up on the idea of a planning, budgeting & forecasting service line altogether.

There were many partners both large and small who invested significant time and money into the future of PPS Planning, and rightfully so – it was strong v1 product and was heading in the right direction. Some of the smaller players were left high and dry by the January news. The announcement also didn’t do much for the customers of varying sizes who were about to start, in the middle of, or just completing a Planning project. I was speaking to a customer the other day who asked me specifically not to mention the name “PerformancePoint” internally for that very reason. They’re looking forward to using the M&A functionality that will be baked into MOSS 2010 but want to be sure that we just don’t use “the PP word”.

I’m sorry to say that I’m seeing more and more customers recoil in fear at the mention of the name PerformancePoint. Microsoft did themselves no favors with the way the PPS roadmap was so suddenly changed earlier this year. As a result PerformancePoint has almost become a dirty word. This is truly unfortunate for the remaining part of the product that will soon become PerformancePoint Services. In the meantime any opportunities for M&A work need extra TLC to convince potential customers that the “other half” of PPS 2007 is still a safe bet for the future.

Whatever new or different products come out of the FPA code I would strongly advise those working on it to ensure they give it a completely different name. I would also hope that MS will recognize the damage they have done to their PerformancePoint brand and come up with a new name for the M&A features coming in MOSS 2010.

Anyone got any suggestions for an alternate name for PerformancePoint Services? In line with all the products it should probably be <InsertNameHere> Services.

Book Review: Now you see it

nick.barclay@gmail.com (Nick Barclay) — Thu, 04 Jun 2009 04:45:00 PDT

Full disclosure: the author provided me with a free, pre-release copy of this book.

Anyone who has worked with me knows that I am big fan of Stephen Few’s work. I consider Information Dashboard Design compulsory reading for anyone who calls themselves a BI professional. In his new book, Now you see it: Simple visualization techniques for quantitative analysis, Stephen casts a wider net addressing anyone whose job it is to analyze data to discover trends, patterns and exceptions hidden within it.

The book centers around developing the reader’s skills particular to the organization, analysis and interpretation of graphical data. Few continues his quest to help readers harness the power of visual perception by presenting data in such a way that information, and the story behind it, becomes clear. Consider once more the book title and cover art: Now you see it.

The author provides a brief history of the discipline of data visualization, its importance and potential. He breaks down the attributes that make a good analytical data set, the traits of a good analyst and functionality a good analytical software package should support. Once readers are familiar with these concepts they learn how the data, analyst and software are best brought together.

Time is spent explaining how data visualization best practices and techniques can be employed in order to extract maximum value out of the massive amounts of data we find ourselves immersed in these days. Logical explanations and relevant examples are provided showing when to use particular visualizations for specific data in order to achieve maximum analytical value. Whole chapters are dedicated to individual analysis types such as time series, distribution, deviation, correlation among others. Each chapter teaches how to effectively present the data graphically as well as how to recognize, interpret and understand patterns specific to a particular analysis type.

I always enjoy Stephen’s books, I am smarter as a result of reading his work and I like his easy-going writing style. In this book he takes on the role of teacher but I never really felt like I was being “taught”. Like his other books Now you see it is full of relevant, colorful figures, illustrations and diagrams that bring the written content to life.

These days more and more people classify themselves as “analysts” - those who work with data in some way, shape or form to help a business achieve its goals. Now you see it teaches analysts, or any BI professional for that matter, how to take their data visualization and interpretation skills to the next level. Yet another highly recommended book to add to the BI library.

Good to be an MVP (again)

nick.barclay@gmail.com (Nick Barclay) — Wed, 01 Apr 2009 16:48:00 PDT

I was really happy to receive the (non-April Fools) email this morning informing me that I’m an MVP for another year. Very flattering :)

Installing and Troubleshooting M&A on Win / SQL 2008

nick.barclay@gmail.com (Nick Barclay) — Tue, 03 Mar 2009 07:53:00 PST

With some upcoming presentations on the horizon it was time to build a new Win 2008 VPC. I found out a few gotchas that are worth noting in order to get your fresh, standalone M&A instance up and running quickly. Please note I haven’t tried all the different install scenario permutations. I found some of the points below helped me to get things working as they should.

Pre-Install

First some pre-install tips. Don’t worry if you’re reading this after you’ve installed, just keep the following in mind for next time.

UPDATE: here is a MS knowledgebase article that detailing steps to perform SQL 2008 / PPS installs

Install SP2 before you configure M&A

I don’t know of a specific reason why you shouldn’t configure the M&A server and then load SP2, but it’s probably a good practice on a fresh install. So install M&A, but don’t run the configuration wizard yet. Next install M&A SP2 (remember, you only need to execute PSCSrv.msp, the others are for Planning). Once you’re done, run the configuration wizard.

Install SQL 2005 CU 9 Updates

You may be given a pre-req warning signal while configuring the M&A server telling you that “SQL ADOMD.NET 9.0 (SP2) is not installed”. Note that this is not a red hard-stop, just a yellow warning; you can actually go ahead without these bits if you wish. I actually ignored it on one install and didn’t find any real difference in my ability to connect to both SQL 2005 and 2008 SSAS cubes, nonetheless it’s nice to have greens all the way down the checklist when you install.

Load the following CU 9 updates for SQL 2005 (no, you don’t need to have SQL 2005 installed). You can download these here.

SQLNCLI
SQLServer2005_ADOMD
SQLServer2005_ASOLEDB9
SQLServer2005_XMO

Post Install

The following assumes you have installed and configured SQL 2008, MOSS/SharePoint and PPS M&A and are having problems getting things going.

.NET Framework 3.5 & AJAX Extensions 1.0

Once you’ve run through the M&A configuration steps and subsequently try to deploy, publish or connect to the server you may come into some trouble in the form of the (very descriptive) “Unable to connect to server” error.

If you try and browse to the M&A web service (http://MyServerName:40000/WebService/PmService.asmx) you might be faced with a stack trace that looks similar to this:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

Source Error:

Line 23:     <compilation defaultLanguage="c#" debug="false">
Line 24:       <assemblies>
Line 25:         <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Line 26:       </assemblies>
Line 27:     </compilation>

Source File: C:\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\web.config Line: 25

Assembly Load Trace: The following information can be helpful to determine why the assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' could not be loaded.

The .NET Framework 3.5 is a pre-req for SQL 2008. Excellent. AJAX is now a built in component of 3.5, so when we ran the M&A Configuration Manager we no longer get a warning flag asking us to install the AJAX 1.0 extension (when configuring M&A on earlier versions of the framework we were warned that we needed to install the AJAX 1.0 components). However, this appears be a red herring – it seems installing v1.0 of AJAX will actually be more of a help than a hindrance.

From my testing these problems seem to be caused by M&A still looking for version 1.0.61025.0 of AJAX but not finding it. Simply changing the value in the Version attribute in various web.config files as I referenced in this post some time ago seems to help but did not resolve the problem completely for me this time.

Solution: If your M&A server is already installed and configured don’t worry. Download and install the AJAX Extensions 1.0. After an IISRESET you should be good to go.

Ensure the Application Pools are Started

I found that when I did a fresh install on Server 2008 some of the M&A application pools sometimes did not get started automatically. This can result in “503 Service is Unavailable” and / or “Unable to connect to server” messages. Go into the IIS manager and ensure both the Preview Site and Monitoring Service application pools are started.

Speaking at TechEd USA 2009

nick.barclay@gmail.com (Nick Barclay) — Tue, 03 Mar 2009 05:16:00 PST

I always knew living in the USA would give me a much better chance of finally attending the big daddy of TechEds. This year’s event will be taking place in Los Angeles from May 11th - 15th. I got the word recently that one of my session submissions has been accepted.

I’ll be presenting a session on PerformancePoint Monitoring and Analytics - the part of the product that is not going away. The session will cover ways to provide more dashboard interactivity, using web page reports and simplifying access to element metadata.

There are a lot of people I’m looking forward to catching up with there. See you all in LA!

Now that’s a fast browser!

nick.barclay@gmail.com (Nick Barclay) — Wed, 25 Feb 2009 06:47:00 PST

One of the bigger stories on Techmeme right now about the latest beta version of Apple’s Safari browser and how fast it is. But just how fast is it? In CNET News’ article a 3d chart is used to illustrate the point.

(ref: CNET News - Safari 4 benchmarked: 42x faster than IE 7, 3.5x faster than Firefox 3)

There are several things wrong with the graph above from a data visualization perspective but there is one that is glaringly obvious and absolutely relevant to the topic at hand. As a result of the data-obfuscating 3d perspective of the graph it looks like the Safari 4, Minefield and Chrome browsers performed their tests in less than 0 milliseconds! Are we talking pre-cognition here? Maybe these browsers know the web page you want to view before you do; a pretty cool feature.

I think I may have heard somewhere that Microsoft research has been trying to crack the browser pre-cognition market too with a new development platform named ESP.NET :)

Here are two attempts I had using the same data.

…and another in grayscale

R.I.P. PerformancePoint Server

nick.barclay@gmail.com (Nick Barclay) — Fri, 23 Jan 2009 12:45:00 PST

I just love a good premature leak, don’t you? Let’s spell it together… N-D-A.

Anyhoo the announcement has now been made officially on The BI Blog along with an explanatory video from Guy Wiesmantel. PerformancePoint Server, as a standalone product, is being retired. There are two main takeaways from this announcement:

1. Monitoring & Analytics will live on and is to be incorporated into future versions of MOSS Enterprise. So M&A is staying but will be considered a part of MOSS, to be known as “PerformancePoint Services”. I would assume this embedded version of M&A will completely replace the current (and very ordinary) BI/dashboarding capabilities that are bundled with MOSS Enterprise.

2. The Planning part of the PPS product is being retired. Naturally, it will still be supported up until its end-of-life but that will be it. Aside from a to-be-released SP3 no further PPS Planning development will happen. Budgeting & forecasting functionality as is currently provided by Planning will no longer be part of the MS BI stack.

Interesting times…

SSAS 2008 Dynamic Sets in Analytic Reports

nick.barclay@gmail.com (Nick Barclay) — Fri, 12 Dec 2008 07:33:00 PST

The primary aim of PPS SP2 is really platform support, particularly the addition of SQL 2008 as a data source. One of the subtle new features of Analysis Services 2008 is the support for dynamic named sets. Now that we can connect to 2008 cubes we can take advantage of them.

In 2005 we could create named sets but the members they contained were static did not change once the cube had been processed. This is probably why the 2005 AdventureWorks cube doesn't contain any TOP / BOTTOMCOUNT named sets - they were static and not of much use. In 2005 if we want a dynamic TOP n result set we need to write an MDX statement containing a session-scoped WITH SET statement.

WITH
SET [Top 10 Selling Products] AS
    TopCount
    (
      [Product].[Product].[Product].MEMBERS
     ,10
     ,[Measures].[Sales Amount]
    )
SELECT
[Measures].[Sales Amount] ON 0
,[Top 10 Selling Products] ON 1
FROM [Adventure Works]
WHERE
[Date].[Calendar].[Calendar Year].&[2002];

If we wanted a PPS analytic report displaying TOP / BOTTOM n data from an SSAS 2005 data source we had to plug an MDX statement into the analytic report definition like the one above. Because we had to write manual MDX in order get the desired result the user lost their right-click interactivity in the deployed dashboard.

Enter SSAS 2008 dynamic named sets. By setting a named set's Type property to Dynamic we now have a set that will recalculate its members based on the context of the query it is referenced in.

CREATE DYNAMIC SET CurrentCube.[Top 10 Selling Products] AS
    TopCount
    (
      [Product].[Product].[Product].MEMBERS
     ,10
     ,[Measures].[Sales Amount]
    ) ;

Because the named set is an object within the cube we can now build out an analytic report using drag & drop and don't have to touch the MDX to get what we need. As a result users have full access to the right-click menu.

Demo

I set up 3 analytic grid reports using a named set called Top 10 Selling Products and embedded them in a dashboard.

References a Top 10 Selling Products dynamic named set I defined in the AdventureWorksDW2008 cube
Manual MDX implementation of Top 10 Selling Products completely defined in the query tab of the Analytic View Designer (using the MDX code above)
References a Top 10 Selling Products named set that I defined in AdventureWorks for SSAS 2005 (a static named set)

I created a single date filter and hooked each of the grids up to it. As you can see from the shot below. At the All Periods level all three grids show the same 10 products and the same Sales Amount totals. All is well.

When we filter on a year, say CY2002, we see several things.

The SSAS 2008 grid is noticeably faster and completes rendering first with accurate results
The manual MDX implementation results match those returned by the SSAS 2008 version, as it should, but it was slower.
The members that make up the SSAS 2005 named set do not change. We get gaps for the products that we didn't sell in 2002 - like the Mountain-200... bikes. We must have sold plenty of them in later years as the static set put them at the top of the list for all time sales.

Now, let's try interacting with our two accurate grids. As expected the Manual MDX version has no drilldown capabilities. Remember that this limited drill functionality is by design - if you change even one character of the MDX on an Analytical report user interactivity features are disabled.

Now here's the good bit. Because the SSAS 2008 grid was built using drag & drop with no hand-altered MDX we have full right-click access to the data behind it.

So, if you're using (or about to start using) SSAS 2008 get stuck into Dynamic Named Sets and put them to use in your cubes.

The main advantages:

Drag & drop analytic report design = faster, simpler development
Enhanced user experience in the dashboard
More centralized logic in the cube, less code in the element definitions
Faster performance

PerformancePoint SP2 is Here

nick.barclay@gmail.com (Nick Barclay) — Tue, 09 Dec 2008 06:47:00 PST

The main takeaways from this release: full support for Windows Server 2008, SQL Server 2008, .NET 3.5 and virtualization with Hyper-V. Great news! There are some bug and performance fixes but not really any new 'features' per se.

The SP can be downloaded from the following locations:

For a more detailed list of what's inside SP2 have a look at these ReadMe files:

M&A
Planning

How to Win Favor with Australians in the USA

nick.barclay@gmail.com (Nick Barclay) — Fri, 05 Dec 2008 05:57:00 PST

...OK a bit off topic.

Do you work with, or know an Australian who lives in the USA? Don't know what to get them this holiday season? Here's a simple suggestion.

I have just found out that every Australian's favorite cookie, the Tim Tam, is now available in the USA until March next year! You can find them Pepperidge Farm and Target stores. If you've never tried them, you should. Trust me. If you want to make an ex-pat Australian weep with joy or even propose marriage then this is something you can surprise them with.

Some American Tim Tam perspectives:

http://nycfoodguy.com/2008/11/17/nyc-cookies-tim-tams-are-finally-in-the-us-we-want-them-to-stay/

http://nymag.com/daily/food/2008/11/tim_tam_chocolate_cookies_land.html

http://www.cnbc.com/id/27663260/site/14081545

Two more BI blogs to subscribe to

nick.barclay@gmail.com (Nick Barclay) — Fri, 05 Dec 2008 05:37:00 PST

More and more people are starting up BI blogs these days.

Richard Lees - finally Richard is blogging, sharing his very considerable experience with the world. Coupled with www.richardlees.com which houses, live PPS dashboards, SSRS reports, Excel Services pivot tables, data mining examples and more for all to see and interact with.

Boyan Penev - I really enjoyed I working with Boyan for some months on a customer engagement and am very glad he has started blogging too. I can see that he has published an article on SQL Server Central recently as well.

Book Review: Applied Microsoft SQL Server 2008 Reporting Services

nick.barclay@gmail.com (Nick Barclay) — Wed, 03 Dec 2008 05:44:00 PST

Full disclosure: the author supplied me with a free copy of this book.

Teo Lachev's been hard at it again, toiling to get yet another full-featured technical book out hot on the heels of the actual product RTM. When I read & reviewed his SSAS 2005 book a couple of years ago I remember being impressed not only at the amount of content in the book but also the depth of content so close to RTM. Those who are familiar with Teo's previous books will not be disappointed with his latest work - Applied Microsoft SQL Server 2008 Reporting Services.

By now I think it's fair to say that most people out there have at least some experience using Reporting Services since the initial release some years ago. If you've had experience with SSRS this book is an excellent reference on the product's new features, a reminder on the older (and updated) ones, and how to go about customizing and extending in a number of different ways. For the complete newbies this book is also a great place to begin and then take some advanced steps soon thereafter.

On average the split between the "this is how you use the product" and the advanced content in technical books seems to be about 80 : 20, sometimes more. Teo's books consistently tip that scale in favor of the advanced stuff, coming out to about 60 : 40, maybe more. There's still plenty of starting-from-scratch foundational content but there's also plenty advanced stuff to satisfy those hungry for a bit more. This is where those already experienced in SSRS will see a lot of value.

Like other Prologika books there are plenty of documented real-world stories & lessons learned to save you finding out the hard way. Each chapter contains plenty of references to pertinent white papers, blog posts and useful applications to assist in administration or development of solutions. Naturally there is a ton of downloadable sample source code too. There's even sample code for creating a Silverlight reporting UI.

Like the late Ken Henderson did in his brilliant Guru's Guide books Teo makes a noticeable effort not to simply repeat information that can be found in BOL. Isn't that what we all want from a technical book?

Worthy of a place on the MS BI geek bookshelf.

Microcharts Visualizations Supported in SSRS 2005

nick.barclay@gmail.com (Nick Barclay) — Mon, 01 Dec 2008 09:26:00 PST

Kudos to the Microcharts team who have just released a product fully supporting sparklines, bullet graphs and more within SSRS 2005. If you're a fan of Stephen Few's work and are longing for more efficient and effective data visualization options in SSRS (yes, including Dundas) then you might want to have a serious look at this technology. Naturally I'm wondering when they're going to support SSRS 2008. I'm sure they're working on it.

Early last year I managed to get some sparklines into a BSM and PPS CTP2 scorecard using one of the earlier versions of Microcharts. Since that time BonaVista Systems (the makers of Microcharts) was acquired by XLCubed. The original Microcharts used custom fonts to render its objects and hence I was able embed the resultant "text" in a scorecard to display the sparklines. As a result of some architectural changes Microcharts now uses images to produce their visualizations hence our ability to use them in a scorecard is not possible. Nonetheless, this image-based architecture now enables quite a few other potential opportunities that I hope to explore (and blog about) in the near future.

As a side note, the MC lead developer / architect, Andreas Lipphardt, maintains a data visualization blog that is well worth a subscription.

UPDATE (2 Dec 2008): Andreas has confirmed that they are working on a SSRS 2008 version (see comments). Apparently there is quite of lot of rework that needs to be done because of changes made to custom report items in the new release.