You've been badgering away at a PerformancePoint Server Planning Model all day, updating objects, writing complex MDX scope statements to satisfy obscure business requirements and you've only got to add a new view to the time dimension before you can deploy and start testing when you find that your colleague - the one that had to leave early to pick up the kids - has it checked out !
What's the problem? Surely you can undo the checkout from the admin console like you can in VSS Admin? Okay, he could lose his isolated changes but you've been at the whole model for the whole day. Well, there is some good news and some rather irritating bad news !
There is indeed an admin level undo checkout feature that allows you to unlock the objects so you can check them out to you, and this feature is easily found in the Planning Admin Console.
It's one drawback is the fact it forces you to undo the checkout for ALL checked out objects in an application. There is no ability to select an individual planning object !
In my scenario it was not that much of a big deal as I could check all my objects in and then force the undo checkout on the only remaining checked out object but I'm not always going to be that lucky.
On the face of it, it would be little effort for the Redmond gang to add that additional functionality to the Admin Console. You can obviously check-in/check-out individual objects from within the Business Modeler so the stored procs already exist, right? hmm, actually, that gives me an idea ...
Looks like someone, somewhere agreed with my PPS Admin Console CTP2 aesthetic objections. CTP4 has undergone a subtle facelift; gone are the oversized labels, buttons and wiry fonts.
It's much more inline with my increasingly fussy tastes :o)
My job is pretty varied these days :o)
Today I've been looking at satisfying some basket analysis type requirements where, for example, we need to determine what other products customers have bought in addition to a known product.
We could have used data mining for this but decided not to as, in this case, it literally was a few mouse clicks away from our existing AS2005 cube.
The implementation was surprisingly straight forward and query results (admittedly on a subset of the full dataset) very impressive.
In an attempt to outline the implementation steps I will add some basket analysis to the Adventure Works sample database to demonstrate how easy it was !
List all products (and associated profit margin) that have also been purchased by customers who have bought a road bike from the 550-W range.
Rather than using data mining to handle queries of this type I want to extend my current cube. In order for the performance to be acceptable we will adopt a variation on the many-to-many distinct count solution discussed in numerous blogs, white papers and articles.
This result of this approach is a new measure group, we'll call it 'Customer Cross Purchase' and a new reference dimension based on product for our 'known product', we'll call this 'Product Filter'
From a relational perspective, the product filter dimension provides a lookup for customers (in Customer Cross Purchase) that have purchased that product.
This in turn provides the ability to locate all orders (Internet Sales Fact) for that subset of customers. Once we know all the orders for that subset of customers we can simply list the distinct products that make up those orders. (Product)
The measure group contains a distinct set of customers and products that will act as the junction table of our many-to-many. This is a view over the main fact and customer dimension.
CREATE VIEW [dbo].[vCustomerCrossPurchase]
SELECT DISTINCT f.CustomerKey, f.ProductKey
FROM dbo.FactInternetSales AS f INNER JOIN
dbo.DimCustomer AS c ON f.CustomerKey = c.CustomerKey
Next, the fact table [view] is added to the Data Source View, ensuring the relationships to the customer and product dimension are set up.
With the DSV updated, the cube itself can be extended. A new measure group is created together with a Product Filter reference dimension. The dimension usage looks like the diagram below. This ensures the appropriate relationships exist as outlined above
The new measure group is mapped to the product filter and customer dimensions, as per our dsv. Note, this is not done automatically as the real (non referenced) product dimension is selected instead.
To complete the picture, the Customer Cross Purchase measure group is used to create a many-to-many relationship between the Product Filter and the main Internet Sales measure group.
Once deployed and processed we can test out our modifications to check for some reasonable results.
The following MDX query returns a list of all products that have been bought by customers buying a road bike from their 550-W range in Reading, England.
non empty [Product].[Product].members on rows,
[Measures].[Internet Gross Profit] on columns
[Product Filter].[Product Model Categories].[Model].&[Road-550-W],
The query is simple, it lists products on the rows and profit on the columns, the 'where' clause slices by Reading, England and employs the new Product Filter dimension. The Product Filter dimension has the effect of slicing the main fact table by customers that have bought a bike from the Road 550-W range.
So, we can see that apart from the road bikes, a few other accessories have been purchased too. A quick couple of queries confirm the results.
Three customers (above) have bought a road bike from the 550-W range and the other products these customers have bought (below) match our results !
UPDATE: You can find part 2 here, along with a link to download a C# quick start.
A long time ago I realised that inevitably, however well specified the requirements or however tight the deadlines are, clients always throw a curve ball. To that avail, like many, I adopt an agile approach to project delivery with a keen eye on building frameworks and generic components that improve productivity and flexibility for current and future projects.
My current project is no different to any other, we knew the requirements would change as, even from the outset, they were high level and vague. With little budget to flesh them out, a controlled, iterative approach was essential. It's a typical BI project, various data sources require consolidating and processing to allow analytics and reporting to assist in strategy and revenue generation.
The reporting element was the vaguest of them all, although what did exist immediately eliminated using Reporting Services as the reporting/delivery mechanism. The required reports were highly interactive and aesthetically challenging enough to stretch Reporting Services functionality to the limit, even without the vagueness busting curve balls out of sight but already primed for deployment.
With all this in mind, we decided to use ASP.NET 2.0 web forms for the reporting as this gave us a little more freedom with regard to layout, a high degree of flexibility surrounding interactivity and the ability to quickly react to the ever changing requirements. Now, with an eye on productivity we decided to build an MDX Helper class (mentioned previously here and here) that would streamline the development process and enable us to react quickly to those inevitable changes.
Our own requirements for such a component were as follows:
- Allow text files containing parameterised MDX to be executed.
- Must support the execution of multiple queries over a single connection
- Return a conventional ADOMD cell set object containing the query results
- Return a typed/untyped ADO.NET DataTable object for binding to native and third party .NET controls
- Allow the inspection of various server, database and cube properties
- Lightweight, re-usable and simple to use
The public interface of the resultant component looks like this:
In it's simplest form, usage is straight forward.
Take the following parameterised query stored in file UKSales.txt
[Product].[Product Categories].[Category].members on rows,
[Measures].[Internet Sales Amount] on columns
You return an ADOMD cell set object using the following C# code snippet:
// Create an instance of the helper class, setting cube and query information
MDXHelper helper = new MDXHelper("ASTestServer", "Adventure Works DW", "Adventure Works", MDXHelper.MDXResourceFormat.RelativeURL, "UKSales.txt");
// Add in the country parameter
helper.Parameters.Add("@Country", "[Geography].[Geography].[Country].&[United Kingdom]");
// Execute the query
CellSet result = helper.ExecuteCellSet();
// Tidy up
helper = null;
There are several overrides on the constructor and 'Execute' methods to allow for flexible usage and it's been used in anger now for a good couple of weeks supporting every type of MDX query we've managed to throw at it so far. It still needs a little refinement and some full-on testing but it has achieved its goal and has helped us easily digest changes to the original requirements. It has also given us some great ideas for version 2 where the query definitions are stored in the Analysis Services cube definition itself.
In a future post I'll delve into the approach and implementation of the helper class to see how it works under the hood.