Post reply

Lookup transformation between a cube and an excel file

  • November 17, 2011 at 10:37 pm

    #390900

    Hi all...

    I have an ssas cube and an excel file.

    I have the qty in ssas cube and rate in excel for a said item.

    I want to lookup these two in ssis and have an output like this..

    Item Qty Rate

    Is there any way or any other workaround??

  • November 18, 2011 at 1:25 am

    #1409719

    Read your Excel file like you normally do, and then use SQL to read from the SSAS cube.

    This is possible, according to the following article:

    http://cwebbbi.wordpress.com/2006/06/14/can-i-run-sql-against-an-analysis-services-cube/

    However I'm not sure you can use an OLE DB connection manager for this. And the Lookup component can only work with an OLE DB connection manager. So, if this doesn't work, you'll have to use the MERGE JOIN to do your lookup.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply