Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How do I connect PDI to read SSAS cubes? From Analysis Services.

  1. #1
    Join Date
    Sep 2014
    Posts
    175

    Default How do I connect PDI to read SSAS cubes? From Analysis Services.

    Hey maybe it's bizarre, but in multiple BI systems where you want to prevent redundant calculations ---


    Anyway let's say I want to pull info/ MDX/ Mondrian query some existing Microsoft SSAS cubes and pull it into Pentaho PDI.


    Uhm .... how do I configure that connection? Is it even possible out-of-the-box without heavy customization?

    I have all the proper connection credentials/ know the connection string. Just not sure if it quite works in Pentaho PDI. SSIS of course has a method for it.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You mean like the OLAP Input step?

  3. #3
    Join Date
    Sep 2014
    Posts
    175

    Default

    I guess that makes sense. Not sure why this isn't a type of connection, but I guess that makes sense.

    I'm just confused .... is the SSAS server address essentially the "XML/A Url" ?

    And why does it have to start with http://?

    I'm wondering if the SSAS database needs to be specially configured for http:// access or something like that.

    I see there's a spot for URL (I put the server address here, BlahBlahData.com)
    I put in my username and password, and the Catalog at the bottom. It's not exactly connecting, help guide doesn't have much info either.

    This would be its own small research project ...

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Probably because...
    Microsoft wrote the XML/A standard, and promptly failed to adhere to it.

    It might take some time to get it working, but it should work -- unfortunately, I don't have SSAS available to work against.

  5. #5
    Join Date
    Aug 2016
    Posts
    290

    Default

    This is a heavy topic! I actually did many Process commands against SSAS using Kettle/Spoon. It required massive amounts of customizations. You need http access, which is pretty much only achievable using ISS.

    After setting up ISS to get http access, I pretty much copied xmla code from the various Microsoft programs, don't remember all those names (development programs, plugins for visual studio and database management programs). I was then able to hardcode and manipulate these xmla strings as java String. Using this crude string manipulation, I was able to modify the elements and attributes of the xmla so that I could do the Process operations needed. This was mostly to trigger SSAS to read new data or update existing data from the underlying MySQL server. One bonus was the ability to manually create some Process commands that not even the Microsoft GUI programs could auto-generate.

    Most of the work was on the Microsoft/ISS/SSAS side. Once that worked, and I knew what the xmla Strings should look like, it was quite easy using Spoon/Kettle.

    The whole solution became so cumbersome and SSAS did not support deleting existing data or update existing data, so we ended up dropping the entire solution. The various process commands, MDX language and the technology in its entirety was so confusing. Never looked back since.
    Last edited by Sparkles; 01-18-2019 at 03:00 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.