[Mondrian] Microsoft Excel with Mondrian through MS OLEDB provider for analysis services driver

Tiago Ferreira tiago.ferreira at webdetails.pt
Thu Jan 23 12:04:31 EST 2014


Figured that ugly workaround would turn some heads..
It's a weird issue where member ordinals would always return -1.

To replicate it need to start with these two queries via xmla on a clean
server

#1
SELECT {[Measures].[Sales], [Measures].[Quantity]} ON COLUMNS, NON EMPTY
[Time].Children ON ROWS FROM [SteelWheelsSales]

#2
select Subset([Time].[Years].AllMembers, 0, 2) DIMENSION PROPERTIES
MEMBER_NAME, MEMBER_TYPE ON COLUMNS,
  {} ON ROWS
from [SteelWheelsSales]

and then attempting to get the 2003 member via adomd will send this

#3
    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
      <RequestType>MDSCHEMA_MEMBERS</RequestType>
      <Restrictions>
        <RestrictionList>
          <CUBE_NAME>SteelWheelsSales</CUBE_NAME>
          <CUBE_SOURCE>3</CUBE_SOURCE>
          <MEMBER_UNIQUE_NAME>[Time].[2003]</MEMBER_UNIQUE_NAME>
          <TREE_OP>72</TREE_OP>
        </RestrictionList>
      </Restrictions>
      <Properties>
        <PropertyList>
          <Catalog>SteelWheels</Catalog>
          <DataSourceInfo>Pentaho</DataSourceInfo>
          <LocaleIdentifier>1033</LocaleIdentifier>
          <Content>SchemaData</Content>
          <Format>Tabular</Format>
        </PropertyList>
      </Properties>
    </Discover>

and always return -1 on the member_ordinal property.

There would be two year hierarchies of rolap members sharing the same [All
Years] parent.
The member in the MondrianOlap4jMember returned for the xmla request would
be from one and checkMemberOrdinal would always fill the ordinals for the
other.

That change tries to replace the underlying member with the one having the
ordinal.
Couldn't find a clean way to fix it.
If someone has time to jump on this that'd be great (think you'll need to
cherry-pick this
https://github.com/tgf/mondrian/commit/f80f7549a1e93e17d104548626f4940f72dc3b74to
try it in regular mondrian)







On 23 January 2014 16:38, Luc Boudreau <lucboudreau at gmail.com> wrote:

> Will,
>
> Great job guys. I love where this is going. I'm a little curious about
> this change:
>
>
> https://github.com/tgf/mondrian/blob/evergreen/src/main/mondrian/olap4j/MondrianOlap4jExtra.java?source=c#L232
>
> We have been progressively removing all ordinals from the specs since the
> cost to compute them is too expensive. Even MSFT is doing the same. The
> replacement for ordinals is an order key. This allows the client to figure
> out the ordering, if needed, while sparing the effort server-side.
>
> What's the reason for the change? Maybe we can fix the issue some other
> way.
>
> Luc
>
>
>
> On Thu, Jan 23, 2014 at 11:22 AM, Will Gorman <wgorman at pentaho.com> wrote:
>
>> Hi Lakshmi,
>>
>>
>>
>> That is great news!  We are working on something similar, so it would be
>> nice if we could collaborate.  We are in the process of adding ADOMD.NETand SSRS support for Mondrian in the master branch.  The work right now is
>> going on in our personal github accounts.
>>
>>
>>
>> Here is the link to the JIRA case:
>>
>> http://jira.pentaho.com/browse/MONDRIAN-1836
>>
>>
>>
>> Here is a link to a branch of Mondrian with our ADOMD.NET changes:
>>
>> https://github.com/tgf/mondrian/commits/evergreen
>>
>>
>>
>> We’ve added a couple Mondrian Properties to enable Microsoft
>> compatibility:
>>
>> •             mondrian.xmla.AlwaysIncludeDefaultSlicer=true
>>
>> o             always output the default members in the slicer axis for
>> unconstrained dimensions in xmla queries. if not true we get exceptions
>> reading members on adomd client;
>>
>> •             mondrian.xmla.CustomProviderVersion=10.0.1600.22
>>
>> o             'spoofs' the ProviderVersion XMLA property to this value;
>> there are fields for which the adomd client will throw a
>> NotSupportedException if the version isn't recognized as supporting them,
>> regardless of the actual value in xmla
>>
>> o             if set to empty would show the mondrian version
>>
>>
>>
>> Here is a link to our ADOMD.net test suite, which is a command line C#
>> app that exercises various ADOMD.NET calls:
>>
>> https://github.com/portisheadaff/adomdtest
>>
>>
>>
>> It would be great to see the changes you made, if you have a github
>> account you can just point us to your fork of Mondrian.
>>
>>
>>
>> Hope we can share efforts!
>>
>> Will
>>
>>
>>
>> *From:* mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
>> *On Behalf Of *Dhanalakshmi N
>> *Sent:* Thursday, January 16, 2014 10:43 PM
>> *To:* mondrian at pentaho.org
>> *Subject:* [Mondrian] Microsoft Excel with Mondrian through MS OLEDB
>> provider for analysis services driver
>>
>>
>>
>> Hi,
>>
>> I was working on integrating Microsoft Excel with mondrian through MS
>> OLEDB provider for analysis services driver. I have made some changes in
>> Mondrian 3.5.0 source code. Now Excel is working fine for me with Mondrian
>> through this driver. Please let me know where can I submit these changes.
>>
>>
>>
>> --
>>
>> Thanks & Regards,
>>
>> Lakshmi
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


-- 
Tiago Gomes Ferreira
Webdetails Dev

www.webdetails.pt
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20140123/5f327b63/attachment.html 


More information about the Mondrian mailing list