I spotted a 'Convert to PerformancePoint Matrix' menu option on the context menu
of an Excel PivotTable recently.
I'd not noticed it before - it is documented within the PPS Excel Add-In help but only once, and subtly at that. I'm glad to report, on first impressions, it appears to do a fairly decent job.
It's a one-way process that cannot be undone and it creates a 'User defined' MDX style matrix. You are therefore limited to making subsequent edits using the Report Properties window but suffice to say it's a handly little feature that I'll no doubt use more and more now I've found it.
What seems like a lifetime ago I posted about MDX Stored Procedures and how we've built a helper class that provides a wrapper to an MDX query.
The helper class has been stable for quite sometime now and a few people have been asking me when I'm going to release it. Well, today is the day.
You can download a Visual Studio 2005 C# Quick Start project from here.
The idea behind the class is that text files containing parameterised MDX queries can be executed against a cube with just a couple of lines of code.
Here are some notes to complement the quick start to get you going:
Step 1: Create an instance of the helper class
MDXHelper helper = new MDXHelper
("localhost",
"Adventure Works DW Standard Edition",
"Adventure Works",
MDXHelper.MDXResourceFormat.RelativeURL,
"MDX/Product Category List.txt");
There are a couple of overloads on the constructor. The one above specifies all the options. The first three parameters set the Analysis Services Server, the database, the cube. The last two parameters specify what format the MDX query. In this case it's a Relative URL meaning that the next parameter must specify a relative URL pointing to a text file containing the desired query. The alternative to a Relative URL is 'Text' which means the last parameter must be the query itself rather than a path to a file.
Step 2: Add the parameters to the parameter collection.
helper.Parameters.Add(
"@ProductCategory",
CategoryList.SelectedValue);
Parameters are simply name/value pairs and are held internally as a generic dictionary<string,object>. Parameters are optional but you will get an error if you invoke a parameterised query without defining a value for every parameter.
Step 3: Execute the Query
DataTable categoryList = helper.ExecuteDataTable();
A DataTable or a CellSet can be returned, again various overloads exist to provide additional flexibility, for example you can set the resource format and query path on the ExecuteDataTable e.g.
DataTable result = helper.ExecuteDataTable(MDXHelper.MDXResourceFormat.RelativeURL, "MDX/Internet Sales by Country.txt");
Additional Notes
The query definitions are cached in the ASP.NET application cache so if you make any changes to the query definition files you'll need to recycle the application pool.
The helper class will only work for Web Projects. There is no support for WInForms/SmartClient deployments.
If you are returning a data table you can specify some additional options.
- Untyped is the fastest and default, returning a plain data table based on a flattened CellSet object.
- Typed brings back a Data Table with the Schema column data types set to match the CellSet object it was derived from. This can be slow for large datasets.
- UntypedFormatted brings back a plain data table but using the formatting e.g currency formats etc held in the cube.
There are some additional properties containing meta data debugging information that have been useful. ExecutedMdxQuery contains the actual MDX that was executed after the parameter replacements have occurred. DatabaseServerVersion, CubeDescription, CubeLastProcess and CubeLastSchemaUpdate are pulled from the server when a query is executed.
You can use parameters to define any aspect of the query. E.g. You could use parameters to swap rows and columns, define measures, pretty much anything you can achieve with search and replace. On one hand this provides no end of flexibility but on the other provides diddly squat compile time debugging!
The same database connection is maintained between query execution calls.
All feedback gratefully received.
There is a useful spreadsheet (what other format would it be!?) to assist in the requirement gathering and the planning of the implementation for a PerformancePoint Planning Application.
It's a strange mix of high and lower level detail but it does ensure you capture and at least think about each element of your application and the business requirements it should satisfy. It's a useful starting point/stake in the ground although some of the questions will require much more thought and analysis behind the scenes than others.
The main topics it covers are:
-
Completing an Impact Assessment
-
Application, Model, and Model Site Planning Considerations
-
Model Type Planning Considerations
-
Dimension Planning Considerations
-
Currency Translation Planning Considerations
-
Data Loading Planning Considerations
-
Business and Process Planning Considerations
-
Reporting Planning Considerations
-
Business Rule Planning Considerations
-
Model-to-Model Association Planning Considerations
-
Diagramming the Application
The PPS Operations Guide has a good procedure for upgrading from a pre-release version:
http://technet.microsoft.com/en-us/library/bb838730.aspx
It's relatively straightforward to follow but if you want a quick heads up, see below:
- Backup all the databases, both server databases and all application staging databases
- Uninstall all the PPS binaries
- Re-install the PPS binaries selecting 'Distributed Installation' to allow you to unselect the PPS Database Installation
(You'll upgrade, keeping all applications/server config in place this way)
- Run ppscmd upgrade /server < PlanningServer URL > from the command prompt
(This upgrades the database schemas for all applications to the new version)
- Connect to the Administration console and 'Take online' the applications
It's a similar but simpler process for Monitoring Server too.