April 5, 2011 at 2:24 am
SQL 2008 R2
We're building a reporting solution based on a database which has been deployed to multiple customers, each with their own customisations and business rules, for example each one may calculate a measure such as commission in different ways based on configuration settings. To ease the support and deployment process, our aim is to produce a standard cube for all customers, but find a way of altering various MDX queries to suit each customer's requirements, but we're not sure of the best way to proceed. In theory we've come up with the following:
Create a small fact table to hold the various configuration settings (which are mainly a series of true/false selections) and write a lengthy series of CASE...ELSE statements to take account of every possible combination.
Distribute a standard cube with a separate config file containing the MDX queries and find some way of loading them into the cube.
Find some way of calling an external program/subroutine which would prepare the MDX for each specific customer.
It may well be that there's a more obvious solution that we've overlooked, and we're not sure that the ones we have come up with are feasible. If anyone has tackled a problem like this before I'd be grateful for any pointers. Our cube could be accessed from either Excel or SSRS, so in an ideal world we wouldn't be looking for an SSRS-only solution (I have seen various posts about dynamic MDX in SSRS so know this is an avenue we could pursue).
Thanks
--
Scott
May 6, 2011 at 12:50 am
Your approach would be overly cumbersome.
The piece you're missing is that SSRS really sucks as means for viewing cubes. You need to get your hands on a better application that exposes the full power of cube analysis and use it conjunction with native cube security to deliver the functionality you are after.
For instance, Pyramid Analytics has a brilliant web based client tool for cubes that will allow you to construct cube reports that reflect custom MDX elements (members, sets and calculations) and secure them by user (or user role). Combining that with Analysis Service's own security model (which is super granular), it is entirely possible to deliver something close to what you are after without any of the headaches you will encounter in SSRS.
Excel's pivot table cube viewer offers the same cube access as the Pyramid application, but gives you no control over the content/reports and the custom elements (which cannot be added in Excel). Further, its not a web based solution. It works for limited internal models. It doesn't come close to the other tools on the market.
Hope this helps
Avi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply