Adatis BI Blogs

New e-Learning MS Business Intelligence Course

Tim Kent — Thu, 11 Mar 2010 11:12:14 GMT

I’m sure a number of you know of UK SQL Server MVP Chris Testa-O’Neill from his many presentations at user groups and conferences. Chris has just authored a new e-learning course for Microsoft which covers the BI stack. From Chris:

"I am pleased to announce the release of the Author Model eCourseCollection 6233 AE: Implementing and Maintaining Business Intelligence in Microsoft® SQL Server® 2008: Integration Services, Reporting Services and Analysis Services

This 24-hour collection provides you with the skills and knowledge required for implementing and maintaining business intelligence solutions on SQL Server 2008. You will learn about the SQL Server technologies, such as Integration Services, Analysis Services, and Reporting Services

This collection also helps to prepare for Exam 70-448 and can be accessed from: http://www.microsoft.com/learning/elearning/course/6233.mspx “

Well worth a look I reckon!

Allocations in PowerPivot Using DAX

Jeremy Kashel — Sat, 27 Feb 2010 14:56:56 GMT

Although I didn't mention it, the inspiration for my last post was the excellent MDX Solutions 2nd Edition. Whilst flicking through the book a while back, I though it would be interesting to see how DAX and PowerPivot could handle the so called 'Common Calculations and Selections in MDX'.

This post continues that theme, focusing on one calculation in particular that's in the aforementioned MDX book, namely 'Unweighted Allocations down the Hierarchy'. Essentially this business problem is dealing with allocating data that is entered at a higher level (e.g. Quarter) down to a lower level (e.g. Day). It varies from business to business what the users actually want to see at the day level in this situation. Some expect to see the Quarter amount, some want to see a blank value and then some users want the amount in the quarters allocated down to the day level.

If the expectation is that data should be allocated, then one way of doing the allocation is to use a ratio. In the MDX book, this is achieved by the following MDX:

1.0 / 
Descendants(
            Ancestor(
                    [Date].[Calendar].CurrentMember,
                    [Date].[Calendar].[Calendar Quarter]
                    ),
            [Date].[Calendar].CurrentMember.Level,
            SELF
            ).Count

If we put this into a query and run it against the Calendar hierarchy in AdventureWorksDW we get the following results:

Therefore our quarter-entered value can simply be multiplied by this ratio to get the correct value at each level in the Calendar hierarchy.

PowerPivot

So the challenge is how to be able to do this in PowerPivot. I started off by looking for some data in AdventureWorksDW that would fit the bill. A good candidate seems to be the FactSalesQuota table, which I imported into PowerPivot along with its related dimensions:

Once in the pivot table, I wanted to be able to calculate the correct ratio at the daily and monthly levels. For the daily level this is 1 / 90, as there are 90 days in the Q1 that year, and for the month level it is 1/3 as there are 3 months in the quarter.

Given that there's no MDX style Ancestor() function in DAX, I ended up having to have a different calculation at different levels. Something that I learnt from this forum post is that you need to use IF() in order to isolate a calculation at a different level. Therefore the DAX that I came up with was:

=IF( COUNTROWS(VALUES('DimDate'[FullDateAlternateKey])) = 1,

    1 / CALCULATE( COUNTROWS('DimDate'), ALL('DimDate'[FullDateAlternateKey], 'DimDate'[EnglishMonthName]) ),
    1 / CALCULATE( COUNTROWS( DISTINCT( 'DimDate'[EnglishMonthName]) ), ALL('DimDate'[EnglishMonthName]) )
   )

The COUNTROWS(VALUES('DimDate'[FullDateAlternateKey])) = 1 allows me to check that we're at the day level, and if we are at the day level, then count the number of days in the quarter. Otherwise, if we're at the month level we calculate the number of months in the current quarter. When this DAX is put into a new measure we get the following results:

This produces the same results as the MDX statement. The next step was then to apply this ratio to the Sales Amount Quota for the current quarter. This was achieved using the following DAX:

=IF(
      COUNTROWS( VALUES('DimDate'[FullDateAlternateKey]) ) < 90,
      CALCULATE( Sum('FactSalesQuota'[SalesAmountQuota]),
      All('DimDate'[FullDateAlternateKey], 'DimDate'[EnglishMonthName]) ) * 'FactSalesQuota'[Ratio],
      Sum('FactSalesQuota'[SalesAmountQuota])
   )

The < 90 at the start of the statement allows me to check if we're at the day level or the month level. If we're at either of these levels, then we want to pick up the Quarter-level SalesAmountQuota and multiply by the ratio. Otherwise, at the Quarter and Year levels, we just apply the original SalesAmountQuota value. This produces the following results:

Conclusion

The use of the MDX Scope statement would have been nice to pinpoint exactly where I wanted the calculation to occur, but then again, the use of IF() keeps it quite simple, which is better for an Excel power user. Whilst MDX is very very powerful, can you imagine a business user getting to grips with MDX statements such as Scope or Freeze?

The bottom line is that the DAX calculation produces exactly what I want - the new AllocatedSalesQuota measure is correct at all levels in the Calendar hierarchy. Even with an IF() statement, like everything else in PowerPivot, the calculation performs very quickly.

SQLBits VI comes to London

Tim Kent — Mon, 22 Feb 2010 10:59:55 GMT

Just in case you hadn’t already heard from Chris or Simon the next SQLBits has been confirmed for 16th April at Church House Conference Centre in Westminster. Simon also explains why the tight timescales and only single day event this time.

If you need to find a reason to convince your boss why he should give you a day off to attend just look at the session list from the last SQLBits!

And if you fancy presenting yourself, session submission is open now. You'll need to Login/Join first then complete your Speaker Profile before submitting a Session

See you on the 16th!

DAX Closing Balances

Jeremy Kashel — Thu, 11 Feb 2010 08:03:03 GMT

One of the often required calculations for users in reports is to be able to calculate a point-in-time closing balance, which is especially relevant when dealing with stock levels or financial data.

In the Analysis Services world, at least in the Enterprise Edition, we're lucky that we have a variety of Aggregation Functions that can deal with semi-additive measures such as a closing balance. Or we can always turn to MDX if we need to which will achieve the same thing.

In PowerPivot, we don't have Aggregation Functions, but there is DAX, which has a variety of time intelligence functions up its sleeve. The idea behind this post is to see how PowerPivot would deal with the requirement of reporting a closing balance measure.

DAX Time Intelligence Functions

One of the DAX functions that we can use for our closing balance is called ClosingBalanceMonth(), which will simply evaluate an expression at the end of a given month. There's also the similar ClosingBalanceQuarter() and ClosingBalanceYear().

Having noticed these functions within PowerPivot, my first port of call was to set up a simple PowerPivot model by taking data from the FactInternetSales table in AdventureWorksDW, plus all it's related dimensions of course. What I ended up with was the following simple pivot table in Excel 2010:

Sales Amount obviously isn't a semi-additive measure, but I'm treating it as one in this example. My first port of call was to use ClosingBalanceMonth() to try and get the value for the month to be equal to the value for last day in the month. I managed to come up with the following formula:

=CLOSINGBALANCEMONTH(Sum([SalesAmount]), 'DimDate'[FullDateAlternateKey], ALL('DimDate'[FullDateAlternateKey]))

This produces the results that are shown below, in the measure called ClosingBalanceFormula:

It does a good job of calculating a closing balance for each month, but it presents that closing monthly balance on each day. Therefore, I started looking for an alternative approach and found the LastDate() function. It returns the last date that's in context for the passed date column. The last date that's in context at the month level will be the last date in the month, but thankfully the last date that's in context at the day level will be the current day. Therefore I came up with the following formula:

='FactInternetSales'[Sum of SalesAmount](LASTDATE('DimDate'[FullDateAlternateKey]))

This produced the results that I wanted - the daily closing balance at the day level and the monthly closing balance at the month level:

A bit more digging lead me to find out that a combination of the Calculate() and LastDate() functions gets to the same result, as an alternative. That formula is:

=CALCULATE(Sum('FactInternetSales'[SalesAmount]), LASTDATE('DimDate'[FullDateAlternateKey]))

Conclusion

It's a shame that the ClosingBalanceMonth() formula didn't quite work, but perhaps getting that approach to work will come out in the wash with more DAX experience. However, as with MDX or any kind of expression language, there's always going to be a load of different ways to get to the same result. It's much more efficient to work knowing that you've got several ways around a particular problem, rather than being forced down a particular path, and in fact, I think the range of calculation functions available shows the richness of the DAX language.

Technorati Tags: PowerPivot,Gemini,DAX

PowerPivot and Data Warehousing

Jeremy Kashel — Fri, 05 Feb 2010 09:57:58 GMT

A consultant that I know has recently been through the process of reviewing his company's management information requirements, and also the process of evaluating what technology platform can deliver those requirements.

Naturally, a data warehouse was one of the options. A data warehouse, in my opinion, will provide the enterprise-level platform for any organisation to deliver their management information.

In this case though, due diligence flagged up some alternative approaches to a data warehouse, namely the raft of self service BI tools that are out there, including PowerPivot. Other tools that can take data in from a variety of data sources are Tableau (which is also a good front end for Analysis Services) and Spotfire for example.

I think that all of these tools have their merits. PowerPivot is incredibly fast and easy to use, whereas Tableau and Spotfire for example have some handy data visualisation capabilities. Some vendors actually claim that self service tools can replace a data warehouse. Can this really be the case? Whereas these tools do a good job of presenting data in a dimensional model, my personal view is that a data warehouse of course also carries this out, but in addition delivers the following benefits to name a few:

Data Cleansing - The power of SQL in the backend and also features like Fuzzy Matching in SSIS mean that you can address the huge problem of data quality. The bonus here is that all data quality issues could be logged, meaning that you can go back to the owners of source systems and let them know that their data is in a mess.
Control - If you do encounter data quality issues, you may decide that they are actually too bad to load into the data warehouse and present to your users.
Transparency - if you want, you can choose to expose the data quality level of your facts to users, via the Kimball-style audit dimension. E.g. we may have noticed that within our retail data, one store has abnormally low sales. Therefore, we can assign this fact a lower data quality score than the other sales by store for the day, which lets the users know that the data quality steward is aware of the issue, and is looking into it.
Slowly Changing Dimensions - A lot of source systems don't behave. E.g. if we're tracking a customer's Marital Status of 'single' or 'married', you often find that source systems only record the latest change to a given field. Without addressing slowly changing dimension issues in a data warehouse, we may never know that Customer X is now single, but once was married.
Conformed Dimensions - When we have multiple data sources for a single dimension, the goal is to ensure that each dimension available to end users represents a single, consistent view of that dimension. Due to the often complex matching and de-duplication that's required, it's difficult to see how this would be possible without a data warehouse.
Late Arriving Dimension Rows - Going back to the 'Marital Status' attribute, we can get the situation where a source system only updates a particular field a long time after the event actually occurred. E.g, if we think that a customer called John Doe is single, but then today we're told he's married, but has actually been married for the last two years. If Marital Status is an important piece of management information to the business, then we need to update all data associated with John Doe over the last two years, in order to reflect his correct Marital Status over the last two years.
Complex Transformations - I recently encountered a source system where one of the columns in a SQL table held data in a varying length format of ‘#Product Type#Currency#Value, #Product Type#Currency#Value’. So what you basically have here is potentially say 25 records held in one row in a SQL table, whereas another table might have just 7 records within one row. Rows could also be badly formed! We ended up using a bit of C# within SSIS to split this data out so that the sub-elements could be analysed correctly.

I could go on, but I think as a few simple examples the above will do.

Whilst I'm an advocate of data warehousing, I also think that the self service tools, such as PowerPivot, can be complementary to a data warehouse. You'll often find, for example, that there's some data that's not in the data warehouse yet - after all some organisations have a lot of data, and it will take time before all the business analysis etc can be carried out to get this data into the warehouse. Another example is proto-typing, analysts can very quickly build a proto-type model, which can then be production-ised at a later stage. I think this video from Chris Webb is really worth watching - it covers these examples and a few more.

In summary there's always going to be a need for users to go digging for data and producing their own reports - the data warehouse can't always deliver everything immediately. PowerPivot is going to be great in this situation, but it will work best when it's used alongside cleaned and conformed data, which is exactly what a data warehouse provides.

Technorati Tags: Data Warehousing,PowerPivot,Gemini,Kimball,Self Service BI

Master Data Services - Reversing Transactions

Jeremy Kashel — Tue, 15 Dec 2009 22:17:41 GMT

MDM tools give the control of the enterprise master data over to the data stewards and power users, rather than relying on automated data integration alone.

Master Data Services is no exception to the above. One of the ways that this is true for MDS is that it allows users to inspect the transactions that have occurred (either internal to MDS or from a source system) and choose if they want to reverse them.

In order to achieve this MDS has a useful log of all transactions that's viewable by users. Here's an example of some transactions that have occurred in my test system - some are from data that I've loaded up via the staging tables, some are from manual member additions that I've carried out in the front end, and some are from business rules that have automatically run:

In the model that this example is taken from, I've got some business rules that look to address data quality issues. Taking the Kimball view on data quality issues in a data warehousing context - many can, and should, be addressed in the source system, then re-loaded. That isn't always possible, which is one of the reasons why we have business rules in MDS. However, as good any sort of automated rule is - there are always exceptions.

In the transactions shown above, an automatic business rule has run that checks a Customer's overdraft limit, then sets it to 10,000 if its over 10,000. Therefore, when a value of 50,000 was encountered for Member Code 10311, the MDS business rules kicked in and quite correctly did their job. This was not what I wanted in this particular case.

Thankfully we can click on the undo button that's shown above the grid, and reverse a chosen transaction, whether its come from a source system, a business rule or a manual edit. It doesn't seem possible to reverse many transactions at once, but that may be just due to the CTP. In my example, by selecting the first transaction in the list, then clicking the undo button, I've reversed my automatic business rule. Therefore, the user Kaylee Adams (10311) shown below now has her original overdraft limit:

In conclusion, when some sort of manual intervention is needed to successfully manage master data, MDM tools allow that intervention to come from the power users, rather than having to wait for someone technical to address the issue.

Technorati Tags: MDM,Master Data Services,Kimball,Data Warehousing

Shapefiles for SSRS 2008 R2 Maps

Tim Kent — Wed, 09 Dec 2009 15:22:51 GMT

Great free resource for pretty much every shapefile map you’ll need :)

Shapefiles for Epi Info

Plug them into your SSRS Map and off you go.

SSRS 2008 – Icon Charts

Tim Kent — Tue, 24 Nov 2009 19:29:41 GMT

In my last post I had a bit of a moan about the lack of new data visualisation features in PerformancePoint Services but to be fair MSFT have really turned up the dial on the data visualisation functionality with the release of Excel 2007 and SSRS 2008. SQL 2008 R2 sees further advances with features such as Sparklines, Data Bars and indicators being far easier to create in SSRS. There’s still a few things missing from the stack though when compared against some of the pureplay visualisation vendors such as Tableau and Spotfire.

Icon Charts (I’m not actually sure that’s their real name!) are another form of Tufte’s “small multiple” that combines the concepts of lattice/trellis charts and heatmaps. This type of visualisation utilises the user’s perception of colour (hue) and form (size) to allow analysis of multiple categories with multiple quantitative measures.

I was hoping that it might be possible to do something with the new indicators in R2 and though you can use custom images there’s no simple way to hook them up to the data in terms of size and colour.

In this solution (workaround/hack?) I’m using the matrix object again but rather than using an inline chart I’m simply using a square symbol (letter n) from the wingdings font and then setting the colour (with a sequential palette) and size according to the data. My example uses hard-coded values in a switch statement in the expression but you could be cleverer than this and make the values percentages of the maximum in your dataset for example.

Add a couple of legends so that the viewer can clearly see what they are looking at. It’s easy to pick out that SouthWest in Q3 had good revenue but low profit. Is there a Problem? Add a drillthrough to detail report to allow the user to find out.

As always the sample report can be downloaded from here. Alternatively, e-mail devteam(at)adatis(dot)co(dot)uk for a copy.

PowerPivot CTP3 Released

Jeremy Kashel — Wed, 18 Nov 2009 22:08:56 GMT

Following last week's release of the November CTP for SQL Server 2008 R2, the latest CTP for PowerPivot is now also released!

The links for the downloads are:

32 Bit PowerPivot Download

64 Bit PowerPivot Download

PowerPivot Main Download Page

Included in the new CTP3 features are several new DAX functions, especially quite a few around time intelligence. Some look quite comparable to MDX, whereas others possibly give even more functionality than MDX. This is something I hope to blog about soon.....

Technorati Tags: PowerPivot,Gemini,Excel

Stop Reporting Services (SSRS) 2008 from overwriting custom Parameter Datasets

sachatomey — Mon, 16 Nov 2009 21:29:59 GMT

Frustrating little quirk when building reports in SSRS 2008 using Visual Studio 2008. If you write a custom query against a parameter dataset, and then change a query that references the parameter, the custom query used by the parameter is overwritten and reset to the default.

Now, you could keep a copy somewhere and replace it after every update, or, alternatively, use the <rd:SuppressAutoUpdate> tag by editing the rdl file directly.

I was close to raising this as a bug, when I found someone had beaten me to it:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=417209

Master Data Services - Business Rules

Jeremy Kashel — Sun, 15 Nov 2009 20:28:13 GMT

I've been keeping an eye on the SQL Server 2008 R2 CTPs over the past few months, but have been compelled to start blogging again following the release of Master Data Services (MDS) in the November CTP.

The idea of a Microsoft MDM tool first caught my attention with the acquisition of Stratature, and since then I've seen a few talks on the subject, such as Kirk Haselden's talk on the subject back at the BI Conference last year.

Now that I've got my hands on it, I've decided to cover the set up of business rules in MDS. Business rules are key to an MDM solution. If we want to use MDM to load data from disparate source systems, we will definitely have to carry out a lot of cleansing and confirming in order to ensure that the end users only consume clean and accurate data.

To set the scene a bit, I've created several entities in my MDM model, namely Customer, Country and City. These could form a Customer Geography hierarchy for example, but for the moment I'm going to focus on Customer. The following shows the Customers that I've entered manually:

When I add a Customer (via the button that is circled) or when I edit a customer, the third column of the grid for the relevant member will change from a tick to a question mark, indicating that data validation has not taken place.

For this example, what I want to happen is for the Overdraft Limit attribute to validate that it is within normal boundaries that have been set by the business, e.g. a bank. To do this, I'm going to set up a simple business rule.

Selecting Manage->Business Rules will take you to the Business Rules Maintenance screen, where the 'plus' icon will create you a new business rule. Editing the new blank rule will give a screen with a basic IF....THEN GUI to produce a basic business rule. On the IF part you pick conditions such as greater than, less than etc, alongside an all important dimension attribute. You do this by dragging and dropping conditions, in the screen below:

In my case I've picked that the overdraft limit can't be greater than £10,000, and if it is greater, then set it back to £10,000. This will do for now, but I could have prevented validation from succeeding, or caused MDM workflow to start. Clicking the MDS back button will take us back to the business rules maintenance screen, where the rule is not active until we publish it:

Once we do publish the rule, it will kick in whenever validation runs or when you manually run the business rules. In my grid of Customers above, I have an overdraft which is a mistake. When I validate the Customer entity, the 5555555 for the second customer automatically reverts to £10,000, as shown below:

This is quite a simple example of course, and via manual editing. The real power of these business rules will come when loading masses of data from source systems, with the added power of workflow to prompt business users to deal with the validation issues that may occur. I'll aim to post about integrating from other systems via my next post in due course....

Technorati Tags: MDM,MDS,Master Data Services,SQL Server

PerformancePoint Services 2010 – First impressions

Tim Kent — Thu, 05 Nov 2009 18:01:07 GMT

We’ve been lucky enough (or we rather hassled enough people in MSFT for long enough!) to have been participating in the Office 2010 technical preview for the last couple of months but as it’s all been under NDA haven’t been able to blog about it. This also means that we’ve had a chance to look round SharePoint 2010 and, in particular for me, PerformancePoint Services. Nick Barclay has just done a series of posts about what’s new/improved/different in the new version so go there for the full list. Here’s a quick round up of our first impressions:

What looks good so far:

SharePoint integration – Whilst Dashboard designer is still pretty much the same product for doing your, errr, dashboard design it’s no longer the admin and security tool as well. This is all carried in SharePoint and in fact you have to set up a specific PPS site to do this. A great deal of effort has obviously gone into this and which looks to have paid off.

Security is all through SharePoint – no need to set up permissions twice!
AS Conditional formatting now works.
Decomp tree is back!!

Measures can now be formatted independently.
Workspace browser is now much more intelligently organised.
Filter by value – you can now restrict rows\columns by value

Dynamic dimension measures on scorecards- this was a bit of a workaround in 2007 as I’ve posted about previously. This now works properly
Re-usable filters – Filters can now be shared and re-used across dashboards

Disappointments:

Lack of improvements for data visualisation – Very disappointing - other than the decomp tree, the visualisation side of PPS has changed little. Still no real control over how your graphs look. The only other new item to be introduced is the Pie Chart!!!! oh dear. Still no bar charts (and I mean Bars not Columns), no chart formatting options or (controllable) second y axis options that I can see :(

Decomp tree is not a chart type but a right click option from a deployed report. i Like the option to do this from any point in a report but would be nice to have both options.
It’s still called PerformancePoint! – I have to admit I when I read another blog of Nick’s following the demise of Planning I didn’t entirely agree with him that it should be renamed. Having spent the last ten months trying to explain to various IT departments that PerformancePoint is not the devil and that the Monitoring side has not been affected (usually to no avail) has changed my opinion completely.
As per Chris’s blog – ProClarity just seems to have disappeared – I know that was never what Monitoring was supposed to be but the lack of an ad-hoc cube browser is a huge oversight.
Did I mention the lack of data visualisation improvements????

There’s lots more to discuss and there will be more to come over the next few weeks time allowing. SharePoint 2010 looks pretty impressive…

Creating a Custom Gemini/PowerPivot Data Feed – Method 1 – ADO.NET Data Services

sachatomey — Tue, 20 Oct 2009 15:43:31 GMT

There are already a few good Gemini/PowerPivot blogs that provide an introduction into what it is and does so there is no need for repetition. What I haven’t seen are examples of how existing investments can be harnessed for Gemini/PowerPivot based self-service analytics.

This series of posts focuses on various ways of creating Custom Data Feeds that can be used by Gemini/PowerPivot natively – Providing a direct feed from otherwise closed systems opens up new channels of analytics to the end user.

Gemini/PowerPivot supports reading data from Atom-based data feeds, this post looks at a quick way of creating an Atom-based feed that can be consumed by Gemini/PowerPivot. By far the simplest way to develop an Atom-based data feed is to employ ADO.NET Data Services in conjunction with ADO.NET Entity Framework. With very few (in fact one and a bit!) lines of code, a data source can be exposed as a feed that Gemini/PowerPivot can read natively.

I am going to use the AdventureWorksDW sample hosted by a SQL Server 2008 R2 instance for this – obviously Gemini/PowerPivot natively reads SQL Server databases, so creating a custom feed over the top may seems a little pointless. However, this technique may be useful for quick wins in several scenarios, including:

- Preventing the need for users to connect directly to the underlying data source.
- Restricting access to various elements of the data source (tables/columns etc)
- Applying simple business logic to raw data.

ADO.NET Data Services are a form of Windows Communication Foundation (WCF) services, and therefore can be hosted in various environments. Here, I will simply host the ADO.NET Data Service inside an ASP.NET site.

To create a Native Gemini/PowerPivot feed, you take seven steps:

1 - Create ASP.NET Web Application
2 - Create Entity Data Model
3 - Create the Schema
4 - Create the Data Service
5 - Load From Data Feed
6 - Create Relationships
7 - Test

Step 1) Create ASP.NET Web Application

I’m using Visual Studio 2008 here to create an ASP.NET Web Application.

Step 2) Create Entity Data Model

Add an ADO.NET Entity Data Model item to the project, these files have a .edmx extension and allow us to create a schema that maps to the underlying database objects.

Step 3) Create the Schema

We simply require a 1:1 mapping so will ‘Generate from Database’. Incidentally, the ‘Empty Model’ option allows you to build a conceptual model of the database resulting in custom classes that can be optionally mapped to the database objects later.

Create a Microsoft SQL Server connection to AdventureWorksDW2008.

Select the appropriate database objects, I’ve selected the following tables:

- DimCurrency
- DimCustomer
- DimDate
- DimProduct
- DimPromotion
- DimSalesTerritory
- FactInternetSales

Once the wizard has completed, a new .edmx and associated cs file is created that respectively contain an Entity Relationship Diagram and a set of Auto Generated Classes that represent the database objects.

Due to the way the Entity Framework handles Foreign Key Constraints we have to apply a workaround to ensure the Foreign Keys on the FactInternetSales table are exposed and brought into Gemini/PowerPivot. A previous post Exposing Foreign Keys as Properties through ADO.NET Entity Framework walks through the workaround.

Step 4) Create the Data Service

Add an ADO.NET Data Service item to the project.

The service class inherits from a generic version of the System.Data.Services.DataService object, so we need to inform the compiler what class to base the generic object on. We essentially want to base our Data Service on the class representing our newly created Entity Data Model. The class name is derived from the database name, unless changed when the Entity Data Model was created, so in our case the class name is AdventureWorksDW2008Entities.

The auto generated service class contains a ‘TODO’ comment that asks you to ‘put your data source class name here’. The comment needs replacing with AdventureWorksDW2008Entities.

The final step is to expose the resources in the Entity Data Model. For security reasons, a data service does not expose any resources by default. Resources need to be explicitly enabled.

To allow read only access to the resources in the Entity Data Model the InitializeService method needs updating with a single line of code. The code snippet below details the final class implementation, notice the AdventureWorksDW2008Entities reference at line 1 and the the explicit resource enablement at line 6.

Code Snippet
  public class GeminiDataService : DataService<AdventureWorksDW2008Entities>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(IDataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        }
    }
 

That’s all that’s needed, by default, ADO.NET Data Services conform to the Atom standard, so in theory the Service is ready to be consumed by Gemini/PowerPivot.

Before we try, it’s worth giving the service a quick test, building and running the solution (F5) launches Internet Explorer navigating to the Service hosted by the ASP.NET Development Server.

You are first presented with an XML document containing elements that represent database objects, you can further drill into the objects by amending the URL. For example, if you want to see the contents of the DimPromotion table then append DimPromotion to the end of the URL: http://localhost:56867/GeminiDataService.svc/DimPromotion (Case sensitive)

Note: You may need to turn off Feed Reader View in Internet Explorer to see the raw XML (Tools->Internet Options–>Content->Settings–>Turn On Feed Reader View – make sure this is unchecked)

As a slight aside, the URL can be further enhanced to, filter, top n rows, extract certain properties etc etc. Here are a couple of examples:

URL	Effect
http://localhost:56867/GeminiDataService.svc/DimCustomer?$top=5	Return the top 5 Customers
http://localhost:56867/GeminiDataService.svc/DimCustomer(11002)	Return Customer with id 11002
http://localhost:56867/GeminiDataService.svc/DimCustomer(11002)/FirstName	Return the First Name of Customer 11002
http://localhost:56867/GeminiDataService.svc/DimProduct(310)?$exapnd=FactInternetSales	Returns Product with id 310 and all related Internet Sales Records

Confident that the feed is working, we can now deploy the service, and start using the feed in Gemini/PowerPivot.

Step 5) Load From Data Feed

Open up Excel 2010, launch the Gemini/PowerPivot Client (by selecting ‘Load & Prepare Data’)

Select ‘From Data Feed’ from the ‘Get External Data’ section of the Gemini/PowerPivot Home Ribbon to launch the Table Import Wizard.

Specify the Url from the ADO.NET Data Services feed created earlier, in my case: http://localhost:56867/GeminiDataService.svc as the 'Data Feed Url’ and click Next.

Incidentally, you can use the majority of the enhanced Urls to, for example only select the DimProduct table should you so wish, however by specifying the root Url for the service you have access to all objects exposed by the service.

From the Table Import Wizard Select the required tables, in my case I’ll select them all. (You can optionally rename and filter the feed objects here too).

Following the summary screen, the Gemini/PowerPivot Client then gets to work importing the data from the ADO.NET Data Service:

Once completed, Gemini/PowerPivot displays all the data from all of the feed objects as if it came directly from the underlying database.

Step 6) Create Relationships

There is one final step before we can test our model using an Excel Pivot Table. We need to create the relationships between the tables we have imported. The Gemini/PowerPivot Client provides a simple, if a little onerous way of creating relationships, the ‘Create Relationship’ action on the Relationships section of the Home Ribbon launches the Create Relationship wizard:

Each table needs relating back to the primary Fact table which results in the following relationships:

Step 7) Test

We are now ready to start our analysis, selecting PivotTable from the View section of the Gemini/PowerPivot Client Home ribbon creates a pivot table in the underlying Excel workbook attached to your custom fed Gemini/PowerPivot data model.

So, to allow fast access to, for example, potentially sensitive data, through Gemini/PowerPivot you can quickly build a custom data feed that can be consumed natively by the Gemini/PowerPivot Client data feed functionality.

HACK: Exposing Foreign Keys as Properties through the ADO.NET Entity Framework

sachatomey — Fri, 16 Oct 2009 15:39:20 GMT

First post for months; the PerformancePoint Planning announcement forced some redirection and rebuilding. We’ve grieved, we’ve moaned, but at some point, you just have to move on.

-----------------

I’m not a fan of hacks – it normally means you are doing something wrong, but in this case, where I’m after a quick win, I’ve had to work out and resort to a bit of a hack. It actually looks like the issue I’m facing maybe addressed in Entity Framework v2 (Microsoft .NET 4.0) – so maybe it’s more of a workaround than a hack after all ;o)

I’m using the ADO.NET Entity Framework and ADO.NET Data Services to expose a subsection of a database for consumption by Gemini. In order to relate the exposed database objects together in Gemini, I need to apply this hack to ensure I have Foreign Keys available in my Gemini models to support creating the relationships. By default, the Entity Framework exposes Foreign Keys as Navigation Properties rather than Scalar Properties. Gemini does not consume Navigation Properties.

Lets take the scenario where I want to create an Entity Framework Model based on the following tables from the AdventureWorksDW2008 sample database:

-FactInternetSales
-DimCustomer
-DimProduct
-DimSalesTerritory

Step 1) Identify the table(s) that contain Foreign Keys.

In this case FactInternetSales.

Step 2) Load those table(s) into the Entity Framework Model on their own.

This ensures the Foreign Keys are set as Scalar Properties. If you load in all the tables at once, the Foreign Keys are not exposed as Scalar Properties.

Step 3) Load in the related tables. (DimCustomer, DimProduct, DimSalesTerritory)

At this point a bunch of Navigation Properties would have been set up, along with relationships between the related tables but the trouble now is the project will no longer build. If you try you receive the following error for each relationship:

Error 3007: Problem in Mapping Fragments starting at lines 322, 428: Non-Primary-Key column(s) [CustomerKey] are being mapped in both fragments to different conceptual side properties - data inconsistency is possible because the corresponding conceptual side properties can be independently modified.

Step 4) Manually remove the relationships between tables.

Clicking on the relationship line on the diagram and hitting delete, removes the relationship.

Step 5) Remove all Association Sets

By editing the edmx file manually in a text or XML editor you need to remove all <AssociationSet>…</AssociationSet> occurrences from the <EntityContainer> section:

The project should now build, with the foreign keys exposed as Scalar Properties. Obviously no inherent relationships exist, so this could be dangerous in certain applications. For Gemini however, providing you setup the relationships manually, it works a treat.

Boyan Penev – Data Mystification

Tim Kent — Fri, 16 Oct 2009 13:51:53 GMT

This is a great post from Boyan Penev which I’m sure will be familiar to many of you who design reports for customers. Sometimes you just have to give them what they want even when it goes against what you believe :(

SSRS 2008 – Lattice Charts

Tim Kent — Wed, 14 Oct 2009 14:43:09 GMT

Update: Nick Barclay has made some nice tweaks to the sample here

As I’ve mentioned before, we always try and design reports, dashboards and charts for our customers taking into consideration data visualisation best practices as promoted by Stephen Few, Edward Tufte et al.

Whenever I’m reading Visualisation books and in particular the best practice examples, I always find myself wondering whether:

a) it’s possible with Reporting Services? and

b) it can look as good with Reporting Services?

and this was the case whilst I finally got round to starting to read Few’s new book Now You See It (which I hope to do a review of soon). One of the key principles of data visualisation is “Compared to What?”. It’s all very well presenting the information in a clear fashion but without context it’s hard to argue it’s worth.

The use of a matrix of charts (lattice charts, trellis charts) allows the eye to very easily compare data both horizontally and vertically. This method of display is also known as “Small Multiples” – a term coined by Tufte in his book - Envisioning Information. These charts also allow you to easily add additional dimensions to a two-dimensional display.

I’m sure many of you will have used SSRS inline-charts before in a table (my bullet chart examples use this method) but it’s also possible to do this with the matrix object as well. I’m not going to explain in detail the method to produce in-line charts in SSRS as it’s well documented already in this msdn article but in summary you simply set up your matrix with the appropriate row and column groupings, design your chart outside of the matrix then when it’s ready simply drop it in the data cell.

One thing to make sure you do is to change the maximum setting for your value axis to be the maximum value from your entire dataset using a scoped field expression (=MAX(Fields!Sales_Amount.Value, "DataSet1")). Without this change you will be visually comparing apples with oranges!

With a bit of work on the formatting, the result looks pretty reasonable (though the spread of AdventureWorks data isn’t that great). You can easily see which region is performing best and which category has the best sales.

As usual you can download the sample

Installing SharePoint 2007 on Windows Server 2008 R2

Martyn Bullerwell — Sat, 10 Oct 2009 22:07:42 GMT

Obviously when we get a new development machine, we install the latest Microsoft software in order to test the latest applications. All in all Windows Server 2008 R2, is basically the server version on Windows 7, therefore it shares many drivers, and many common issues with its counterpart. Therefore its no surprise that SharePoint 2007 does not install from disk to a Windows 7, or Windows Server 2008 R2 installation.

In order to install SharePoint, you need to create what is called a “Slipstream” version, which in essence means rolling up all the latest service packs into the installation. Something that Microsoft I'm sure will get round to releasing in due course. However in the meantime you can follow these simple instructions.

Download the following Service packs:

Copy all installation files from the installation disc to your local hard disk.
Remove EVERYTHING from the Updates Folder, from within the following directory: [ExtractedPath]\X64 or [ExtractedPath]\X32
Run the following from a command prompt:

wssv3sp2-kb953338-x64-fullfile-en-us.exe /extract:[ExtractedPath]\Updates /quiet
officeserver2007sp2-kb953334-x64-fullfile-en-us.exe /extract:[ExtractedPath]\Updates /quiet

Now Install SharePoint 2007 as you would have done before.

I would advise burning that back to a disk, so you don't have to do it in the future. You may still get a few compatibility issues, but it seems to install and run as expected.

Please support a good cause

Tim Kent — Mon, 23 Mar 2009 15:37:00 GMT

As Mark mentions, the one and only Sutha Thiru is climbing Kilimanjaro later this year to raise money for a very good cause - The Iain Rennie Hospice looked after our colleague Jim Wright who sadly died after on Xmas eve last year.

Sutha is also paying for the trip himself rather than using the sponsorship to subsidise, so please dig deep and give what you can. http://www.justgiving.com/sutha

XLCubed Version 5 Released

Jeremy Kashel — Fri, 13 Mar 2009 12:18:32 GMT

Interesting to see yesterday that XLCubed have released version 5 of their popular OLAP reporting tool. As I've mentioned before, I've always found XLCubed to be very powerful and easy to use, and therefore, in my opinion, it's definitely one of the best Excel OLAP reporting tools out there.

Version 5 sees the introduction of some new features in the Excel/web editions, namely:

Enhanced Excel integration with 'MicroCharts';
An improved user interface in the Web Edition;
Print to PDF and enhanced save to Excel options in the Web Edition;
Fine grain control over publishing Excel reports to the web.

Personally, I'm used to using the Excel Edition to slice and dice cubes to get what I want quite easily. Although it's not entirely new to V5, you can carry out the same kind of ad-hoc analysis in the Web Edition, as shown in the following video. The end result is that you are able to build the kind of dashboards as shown below, whether in the web or the Excel edition:

As usual, the evaluation editions for the Excel and Web versions can be downloaded here

Technorati Tags: Excel,Dashboards,Analysis Services,XLCubed

Dynamic Dimension members on a PerformancePoint KPI

Tim Kent — Thu, 05 Mar 2009 15:46:51 GMT

One of our customers had read Nick Barclay's post on dynamic sets in SSAS 2008 and was hoping that this would mean that you could create KPI's with dynamic dimension members. Well the answer is yes and no. It's already possible to do this in Monitoring (or should I say PerformancePoint Services) using custom sets in the scorecard designer (more on this below). However in PPSM these sets are resolved at the point the Scorecard is rendered in the browser.

This is fine as long as the members of your sets are not affected by the filters applied to your scorecard (member.children for example) - unfortunately the set does not get re-queried when you change a filter. For instance if you were to create a set of your top 10 customers and drag that onto the rows of your scorecard, changing a time filter will not cause the KPI dimension members to change even if you've used time.currentmember in your set definition. So you may end up displaying the Top 10 customers for the current month which may be different to the Top 10 for the selected time period.

Update: Please see Nick Barclay's comment below for a very neat solution to this issue using filter link formulas. (Wish I'd thought of that!)

Custom sets in the scorecard designer aren't the most obvious thing to use nor are they very user-friendly. Your best bet is to use a tool like SQL Management Studio/Mosha MDX Studio to design a query that you know works then paste out the MDX for the set into the custom set formula editor. You access this by dragging the Custom item in the Details pane onto the relevant position on your scorecard:

Paste your set query in to the pop-up dialog. For Example:

TOPCOUNT(
    [Product].[Product Model Categories].[Subcategory].members,
    10,
    [Measures].[Internet Sales Amount]
    )

You can then use the update button on the edit tab of the ribbon to see the results. Unfortunately there's no way to edit the custom set once you've added it. You have to delete the dimension members and then add a new custom set.

Win\Loss Graphs in Reporting Services 2008

Tim Kent — Wed, 11 Feb 2009 21:31:27 GMT

As I mentioned previously, SSRS 2008 allows us to use some nice visualisation techniques in our reports. Win\Loss graphs are used to show (wait for it......) the trend of wins and losses over time. The usual example is the performance of a sports team during a season, but they are also useful for showing trends over time such as the performance of a KPI. Here's another quick how-to on building this type of chart in SSRS 2008.

So first we need a dataset with three fields: Series, Time and Value. The first two are fairly self-explanatory, for the third you need to transform your value so it appears as either a 1, -1 or 0 (win, lose, no result/tie). The AdventureWorks built-in cube KPI's work well here so I'm going to shortcut slightly and use these. If you're going to use a relational source you may need to do something a little cleverer. For example:

SELECT
    [Month]
    ,[Country]
    ,CASE 
        WHEN [SalesAmount] > 10000
        THEN 1
        WHEN [SalesAmount] < 10000
        THEN -1
        WHEN [SalesAmount] IS NULL
        THEN 0
    END AS Winlose
FROM 
    [AdventureWorksDW].[dbo].[FactInternetSales]

We'll be using AdventureWorks (SSAS) "Internet Revenue" KPI. Don't forget that when using an AS data source in SSRS you basically have to trick it into thinking it's relational and make sure you have your measures on Columns (as Chris Webb discusses here). Here's our very simple MDX statement:

SELECT 
    KPITrend("Internet Revenue")
ON COLUMNS, 
    [Date].[Calendar].[Month].members 
    * 
    [Customer].[Customer Geography].[Country].members
ON ROWS FROM 
    [Adventure Works]

Now lets add a chart - pick the standard column chart and format as follows:

Right click and delete the chart title and legend;
Right click on both the axis titles uncheck Show Axis Title;
Right click on the Y (vertical) axis and select Axis Properties. Under Axis Options set Minimum to -1, Maximum to 1 and Cross At property to 0 (this makes sure our baseline runs between the "wins" and "losses"). Click OK;
Right click again on the Y axis and uncheck Show Axis;
Right click on the X axis and select Axis Properties. Under Labels check Hide Axis Labels and do the same for Major and Minor Tick Marks. Under Line set the width to 0.5 and the colour to light grey or similar. Click OK.

You should now have something like this:

Now lets add some data:

Drag your Time field from the Report Data pane into the category area;
Drag your Value field into the Data Field area;
Right click on your series and select Series Properties. Under Fill set the colour suitably. In our example we're going to show wins as black and losses as red using a switch statement in an expression:

=Switch
(
    Fields!Internet_Revenue_Trend.Value = 1, "Black",
    Fields!Internet_Revenue_Trend.Value = -1, "Red"
)

If you preview the chart now it should look something like this:

To finish off we need to add the chart inline to a table:

Add a table and delete the third column;
Set the DataSetName property to the same as your chart;
Right click on the second row and add a group (using the adjacent above option). Select your Series value (in our case: Country) from the drop down;
Delete the bottom row;
Drag your Series field into the First column, second cell;
Drag your chart into the second column, second cell.

Finally add a bit of formatting to your table and you're done!

As usual the sample report can be downloaded from the downloads section (don't forget to login).

This also works in SSRS 2005 - though doesn't render quite a cleanly.

PerformancePoint Monitoring - Hide empty rows in a grid or chart by design

Tim Kent — Mon, 09 Feb 2009 23:16:00 GMT

So as the dust settles and everyone calms down, it's time to move on and get back to what we're good at! Just in case anyone was worried, we'll continue to be at the cutting edge of PerformancePoint Services and the MS BI stack as well as looking at SharePoint in more detail.

I thought I'd post this one as it's come up with a couple of our customers recently and isn't entirely clear at first glance. When you design an Analytic Chart or Grid in Dashboard Designer by default empty rows/columns (or series on a chart) are shown. If you want them to be hidden when the user first opens the chart/grid then click the browse button at the bottom right of the design pane:

The preview window will appear. Now click either of the two end icons on the toolbar (marked by the red rectangle below) to hide the empty rows/columns (or both)

When you click OK the setting will be saved into your design - you can verify this by going into the MDX view of the query and checking that "Non Empty" now appears in your MDX.

You can use the same method to apply sorting by right-clicking on a column in the browse window and selecting the relevant sort function.

Of course if you are using an MDX query then use the NonEmpty/Non Empty functions directly.

As I've pointed out before, don't forget the built-in Dashboard Designer help which is pretty comprehensive

RIP PerformancePoint Planning

sachatomey — Thu, 29 Jan 2009 10:55:03 GMT

It's nearly a week since the announcement that shook the (PPS) world ! It's been a bit difficult to report on; generally the Adatis blogs try and offer solutions to problems we have encountered out in the real-world. Now I could say something crass here about the real-world and the decision makers involved...but that would be childish right?

If I was to offer up my feelings, they wouldn't be that far from Alan Whitehouse's excellent post on the subject. If I had an ounce of class about me, it would be much more aligned with Adrian's poignant discussion opener, the one with the sharp-witted title, but alas....

We've spent the best part of the week speaking to customers, partners and Microsoft about what to do next. The timing was choice - would you believe, we actually had three new PerformancePoint Planning phases kicking off this week, according to my project plan - I should be setting up Kerberos as we speak.. [There is always a positive right?]

Some customers are carrying on regardless, they...

...already have planning deployments and are too far invested and dependent to back out at this stage or,

...have a short-term view (That's not a criticism) and need a "quick" fix with a low TCO to get them through some initial grief. (Typically these customers are going through rapid organisational change, or form part of a recent acquisition and, to help them see the wood from the trees during the transition, require short/sharp solutions)

Other customers, with longer-term views, feel the product, or more importantly, the suitably skilled resource pool, will drain away far quicker than the life-span of the much touted Microsoft product support. I have to agree - Fact - Adatis will not be employing or training anymore PerformancePoint Planning Consultants. I doubt many other consulting firms will either.

It's those customers with the longer-term view that are the ones currently in limbo - they are experiencing pain, they need pain relief, what should they do - wait and see what Office 14/15 offers? (There is talk of some planning functionality appearing in future Office versions - what truth there is in that..?).

The Dynamics customers could wait for the resurrection of Forecaster - I do have information on good authority that they will be developing Forecaster to be closer, in terms of flexibility, to PPS Planning. I had originally heard the opposite view in that Forecaster will be replaced with a cut down version of PPS Planning. Either way, I'm sure some of the PPS Planning code-base will be utilised, which could end rumours of PPS Planning being 'given' to the community as some form of community/open-source arrangement. An arrangement that is, in my opinion, a non-starter anyway, "Hey, Mr FD, We've got this great open-source budgeting and forecasting product we think you should implement!" - yeah right !

Another rumour (and mixed message) is that Service Pack 3 will contain some of the requested features that were earmarked for version 2 (After all, the code has already been written, right?) this rumour was actually started by Guy Weismantel in his Announcement Video. However, the information I have since received, clearly states that Service Pack 3 will contain stability and bug fixes only - so which is it to be? It's unlikely for a service pack to contain new features, but it's not unheard of; anyone remember the original release of Reporting Services? That arrived as part of a service pack for SQL Server 2000.

The burning question I cannot get answered is, have Microsoft actually stepped out of the BPM market for good? We are told that Excel, Sharepoint and SQL Server provide BPM - I can't see, without Planning, how they can. Short of hard-coded values, renewed Sharepoint/Excel hell, another vendor or bespoke planning solution, businesses can't set plans which have further reaching implications; effectively Planning's demise is also, effectively, shelving the Scorecard/KPI functionality from the M&A toolset too ! It will be interesting to see the new Monitoring & Analytics Marketing, will they still demo Strategy Maps and Scorecards, or will they now focus on Decomposition trees and Heat maps? Monitoring & Analytics may, in practice, just become Analytics..

I would have thought the cost of continuing to develop the product (even if it were a lemon, which Planning certainly wasn't) is far less than the potential loss of revenue that Microsoft will face due not only to the loss of confidence by its customers (who are going to think twice about investing in any Microsoft product now, let alone a V1) but perhaps more significantly, the doors it opens to it's competitors who can offer a complete BI\BPM stack.

Planning was foot in the customer's door for BI - once you put planning in, the customer had already bought the full BI stack, and in most cases, our customers were wowed by what they could now achieve.

I suspect Cognos and SAP are still partying now!

How to: Bullet Charts in Reporting Services 2008

Tim Kent — Wed, 21 Jan 2009 23:51:32 GMT

Unlike SSRS 2005 where creating bullet graph takes a bit of jiggery-pokery, Reporting Services 2008 almost has them built-in (thanks the the acquisition of Dundas technology). I say almost as it's actually using a linear gauge object which in the wrong hands allows you to put the likes of a thermometer on your reports to show just how "hot" your business is! Luckily, there is a built-in bullet graph template for the gauge but it still requires a bit of customisation to make it look like a bullet graph as per Stephen Few's specifications. However it's almost all formatting/setup. Here follows a quick(-ish) how-to.

In most cases you'll be using this type of chart inline in a table, however it's easier to develop the graph object outside of the table then drop it in once you've got the formatting right. To start with create yourself a datasource and build a dataset that has the following fields and one row for each bullet graph you want to display:

Category Name
Actual Value
Target Value
Bad Zone Max
Satisfactory Zone Max
Good Zone max
Optionally you may need a bad zone min if you have negative values

Next drop a gauge object onto your report and select the bullet graph template.

You'll notice that the default is lots of jazzy shading and spangly borders. We particularly need to get rid of these if we are using the graph inline as they will make the graph very hard to read when scaled down (and of course add no value in terms of visualising the key information anyway). We'll also want to remove the scale if we are using inline - we can add it back into our column header later.

Right click on the graph and select gauge properties. Select the Back fill section and set Gradient to solid and the colour to no colour. Then select the Frame section and set the style to none. Close the properties pane. Looking better already:

Next lets remove the scale - right click on the graph again, this time selecting Gauge and then Scale Properties. Select the Labels section then check the Hide Scale Labels checkbox. Then select the Major Checkmarks section and check the Hide Major checkmarks checkbox. At this point also set the start and end margins to 1 in the Layout section and set the Maximum value of the scale to your good zone max field. Close the properties panel again.

Now lets deal with each component of the graph. You'll notice that the orange bar is actually made up of two objects known as linear pointers. Lets tidy them up and assign our Actual and Target values to those pointers. First make sure you've set the relevant datasetname property for the graph. Then right click on your graph again, gauge sub-menu and Pointer(LinearPointer1) properties. Set the value to be your actual field from your dataset. The go to the Pointer Fill section, set the fill style to solid and pick a suitable colour for your "bullet". Set the Pointer width to 30 and finally select the shadow section and set the shadow offset to 0. Do the same for your target pointer (LinearPointer2) but this time setting a width of 8 and an length of 80.

Now set the relevant properties for each of the zones. This time select Range(LinearRange1) properties from your Gauge sub-menu. Set the Start Range to 0 and the End range to your Bad Zone Max field. Whilst you have the properties pane open, get rid of the range borders by setting the border style to None and the start and end widths to 60. Repeat for range two but this time setting the Start Range to Bad Zone Max and the End Range to your Satisfactory Zone Max. The same for zone three but this time with Satisfactory Zone Max and Good Zone Max.

Now add a table to your report, set the datasetname to the same dataset as your graph. Delete the third column and drop your Category field into the second row, first column and then add a grouping on the Category Field (right click on second row, group properties, add). Then drop your chart into the second column, second cell. You should have something that looks like this in design view:

Almost there. One of the quirks of any inline chart is that it overrides the border of the cell it's placed in so you need to set the border for the GaugePanel to the same as your cell borders. Finally lets add a scale to the header of column two. Copy your chart from the cell and then paste it the header. Remove all the components except the scale from the gauge panel by carefully highlighting and deleting. Right click the Gauge in your header and select scale properties from the sub-menu. Uncheck the Hide Scale Labels checkbox and the Hide Major checkmarks checkbox. For the max value of the scale you need to make sure you use the max grouping for you good zone max rather than the default sum (=Max(Fields!Zone3.Value)). In the layout section, set the position in gauge to 90, in the labels section set the placement to inside and distance from scale to 30. Set the major tick mark placement to inside. Phew!

Tidy up your fonts and general layout and you're good to go:

That's about it - if all that seems like a lot of work download the sample report and use that instead :)

PerformancePoint 2007 SP2 and SQL 2008 (Continued)

Martyn Bullerwell — Mon, 19 Jan 2009 23:36:04 GMT

Following on from my previous post prior to Christmas – it looks like I have been beaten to it. For a pure installation of SQL 2008 and PPS have a look here, it details the installation process to ensure a smooth installation. I have now tried this and it seems that installation works seamlessly, and all set up on SQL 2008. Hopefully I will get chance to continue testing.