OracleBIBlog Search

Friday, August 7, 2009

Importing SQL Server Analysis Services Cubes (MSAS) To OBIEE

OBIEE communicates with MSAS using XMLA via http. One of the first steps in trying to import the data into the Administration Tool requests the URL for the data source. If the database has never been enabled for XMLA access (i.e. there is no URL) you will need to configure that before connecting to MSAS with OBIEE. To enable access, the best resource is to follow the instructions provided by Microsoft which are located at

Testing XMLA communication to MSAS (outside of OBIEE)

To test the configuration go through the following steps:

1. Right click on My Computer -->Manage --> Services and Applications --> Internet Information Services (IIS) Manager --> Web Sites --> Default Website.
2. Select the olap directory under Default Website (or whatever you called your virtual directory during the configuration)
3. Select the msmdpump.dll --> Right Click --> Browse

4. If XMLA communication to SQL Server Analysis Services is working you will get back XML that looks like this:

Do not worry about the error message in the XML. As long as you get XML back then SQL Server is properly configuring for access via http. If this is not working, go back and check your Configuration steps for enabling XMLA Access on MSAS.


Issue: Although you have completed the configuration and confirmed all steps you still cannot get the XML to return.

Resolution: Consider what type of environment you are on and if you are using the correct dll. IIS will install with a 32 bit dll by default, if MSAS is on a 64 bit operating system you may need to reconfigure IIS to use the 64 bit dll.

Issue: When putting in the URL while trying to import the database in OBIEE you get the following error:

[nQSError: 64203] “XML/A error returned from the server: Fault code: ‘XMLAnalysisError.Oxc1010000”. Fault string: “The following system error occurred: No connection could be made because the target machine actively refused it…”

Resolution 1: Check to make sure the Database is running
Resolution 2: Check to make sure all of the appropriate Ports are not blocked

Issue: When selecting ‘OK’ after putting in the URL while trying to import the database to OBIEE you getting one of the following errors:

[sQSError: 46100] Sax Parser Failed with Error Invalid document structure.

[nQSError 46100] Sax Parser failed with error Unterminated entity declaration ‘Content Type’

Resolution 1: IIS is not properly configured. Double-check the IIS configuration steps for enabling XMLA access to MSAS, and validate the configuration by getting the XML displayed when browsing the .dll (above)

Issue: When trying to Import the database you are able to put in the URL (http://machinename/olap/msmdpump.dll) with the username and password and select ‘OK’. The MSAS Server name is then displayed however you cannot see any of the Cubes underneath the Server Name. If you select the ‘Import’ Option the Administration tool crashes with no errors.

Resolution 1: Ensure the user you are putting on the first Import screen is a valid user on the domain and has access to the cubes.

Resolution 2: Depending on your security settings, ensure the IUSR_MACHINENAME System Account has the ability to view and read the cubes. To see the Security Settings and Account Name right click on My Computer -->Manage --> Services and Applications --> Internet Information Services (IIS) Manager --> Web Sites --> Default Website --> Properties. Go to the Directory Security Tab --> Authentication and Access Control --> Edit.

Note: In the security configuration you may check or uncheck the 'Enable anonymous access' box, however do not ever change the default user. IUSR_MACHINENAME is an automatically generated system account with an unknown password so you can never change it back without having to completely reinstall or reconfigure IIS.


Tovit said...

Thank you very much,
it is very helpfull,
Can you install the IIS and the msmdpump.dll on other machine? not on the MSAS server.