[wdmmg-discuss] OLAP analysis

Donovan Hide donovanhide at gmail.com
Tue Jun 8 13:53:19 UTC 2010


If anyone is interested, any view that can be constructed in Tableau
can be easily reproduced in a web query:

An example would be to go to:

http://coins.sur.ly/xmla4js/samples/execute-synchronous.html

Enter:

http://coins.sur.ly/xmla/msmdpump.dll

and click Discover Datasource. Then paste the following into the MDX
statement textbox

SELECT
{[Measures].[Corrected Value]} DIMENSION PROPERTIES
[MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,
NON EMPTY CROSSJOIN(
  [Budget Boundary].[Budget Boundary].[Budget Boundary].AllMembers,
  CROSSJOIN(
    [Department].[Department Code].[Department Code].AllMembers,
    [Time].[Accounting Period].[Accounting Period].AllMembers))
DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS
FROM [COINS]
WHERE
({[Data Type].[Data Type].&[Forecast Outturn March]})

Note the escaped ampersand in the last line. Choose Tabular as the
format and click execute statement.

The point of all this is to demonstrate on the fly ad-hoc reporting
and aggregation by any dimension. It would be painful to generate MDX
queries using xmla4js, but once a satisfactory query has been found,
then it can be reused in any web application by including the
javascript library. It's not REST-ful, but it is very powerful!

On 8 June 2010 11:44, Donovan Hide <donovanhide at gmail.com> wrote:
> If you install Tableau, you will also need to install either:
>
> http://www.tableausoftware.com/files/drivers/SQLSERVER2008_ASOLEDB10.msi
>
> if you are using 32-bit Windows or:
>
> http://www.tableausoftware.com/files/drivers/SQLSERVER2008_ASOLEDB10_x64.msi
>
> if you are using 64-bit Windows.
>
> I know, Microsoft Horror!
>
> On 8 June 2010 00:07, Donovan Hide <donovanhide at gmail.com> wrote:
>> http://coins.sur.ly/coins.pdf
>>
>> Numbers may not be correct yet, but the hierarchies of departments and
>> programme objects is certainly interesting!
>>
>> Cheers,
>> Donny.
>>
>> On 7 June 2010 22:13, Donovan Hide <donovanhide at gmail.com> wrote:
>>> Some options!
>>>
>>> Download and install this on Windows:
>>>
>>> http://www.tableausoftware.com/products/downloading-desktop
>>>
>>> Go to Data->Connect To Data ..
>>>
>>> Choose Microsoft Analysis Services
>>>
>>> Click Next
>>>
>>> Do the same as diagram attachment
>>>
>>> Have fun!
>>>
>>> Alternatively, you can play with
>>>
>>> http://coins.sur.ly/xmla4js/samples/index.html
>>>
>>> specifying:
>>>
>>> http://coins.sur.ly/xmla/msmdpump.dll
>>>
>>> as the XML/A URL
>>>
>>> Less fun and very buggy though!
>>>
>>> Cheers,
>>> Donny.
>>>
>>>
>>>
>>> On 7 June 2010 11:10, Alistair Turnbull <apt1002 at goose.minworks.co.uk> wrote:
>>>> On Mon, 7 Jun 2010, Donovan Hide wrote:
>>>>
>>>>> I've put together a rough package of the COINS data using Analsysis
>>>>> Services and a demo of a Silverlight OLAP viewer. It can be seen here:
>>>>>
>>>>> http://coins.sur.ly
>>>>>
>>>>> Silverlight is required and Moonlight doesn't work, unfortunately.
>>>>
>>>> May I second Francis's request for an alternative front-end? Or even just a
>>>> public API I can use? I'd really like to have a look at this.
>>>>
>>>>        Alistair
>>>>
>>>
>>
>




More information about the openspending mailing list